6, 7, 8주차 - DB와 JDBC

1. SQL 

1-1. SQL 의 정의 

• SQL (Structured Query Language) : 데이터베이스를 제어하는 언어
• DDL (Data Definition Language) : 데이터 정의어, CREATE, ALTER, DROP
• DML (Data Manipulation Language) : 데이터 조작어, INSERT, DELETE, UPDATE, SELECT 
  • CRUD : CREATE, READ, UPDATE, DELETE
• DCL (Data Control Language) : 데이터 제어어, GRANT, REVOKE
• TCL (Transaction Control Language) : 트랜잭션 제어어, COMMIT, ROLLBACK

1-2. SQL 기본 문법

• DDL : 데이터 정의어
  • CREATE : 테이블 생성
  • TABLE : 데이터를 저장하는 공간
  • COLUMN : 컬럼 (속성)
  • DATA TYPE : 자료형
  • CONSTRAINT : 제약 조건
  • VARCHAR : 가변 문자열 타입으로 사용하는 문자열의 데이터 양 만큼 메모리에 잡힌다. 메모리를 효율적으로 사용한다. 제약 조건 중 PK (PRIMARY KEY) 로 식별
    • MY SQL에서는 VARCHAR을 주로 사용하고 Oracle 에서는 VARCHAR2를 많이 사용한다. 
    • PRIMARY KEY : 유일성, NOT NULL 보장
  • AUTO_INCREMENT : 주로 PRIMARY KEY를 생성하려는 목적으로 만든다. 고유하고 순차적인 숫자를 자동으로 부여하는 기능이다. 
    • 목적 : 고유 식별자 자동 생성
    • 데이터 타입 : 정수형만 가능 (INT, BIGINT 등)
    • 특징 : 고유성 보장, 순차적 증가, 중복 없음
    • 사용 조건 : PRIMARY KEY 또는 UNIQUE 제약 조건 필요
    • 주의점 : 삭제/작업 실패한 값은 재사용하지 않는다. (GAP 발생)
    • 시작점을 변경할 수 있다. 이때 최댓값의 MAX + 1한 값 이후만 가능하다. -> 유일성 보장
      • ALTER TABLE car AUTO_INCREMENT = 7;
    • 테이블 삭제
      • DROP TABLE member : member 테이블을 삭제한다.
      • TRUNCATE TABLE car; : 테이블을 삭제하고 다시 생성한다. ROLLBACK 이 불가하므로 신중하게 사용해야 한다. 속도가 DELETE 보다 빠르다.
      • 테이블 변경
        • ALTER TABLE member RENAME TO member2 : member 테이블명을 member2로 변경한다.
        • ALTER TABLE member RENAME COLUMN hit TO count : 컬럼명을 hit 에서 count 로 변경한다.

/*
  DDL로 테이블 생성
  테이블 명 : product
  컬럼 명 : id, name, maker, price
  데이터 타입 : id 와 price 를 int, name 과 maker 를 varchar(100)
  제약 조건 : id PRIMARY KEY, name 과 maker 은 NOT NULL, price 는 DEFAULT 0
*/
DROP TABLE IF EXISTS product; -- 만약 product 가 이미 있으면 삭제
CREATE TABLE product (
	id INT PRIMARY KEY, 
    name VARCHAR(100) NOT NULL,
    maker VARCHAR(100) NOT NULL,
    price INT DEFAULT 0
);

• DML : 데이터 조작어
  • SELECT 구문
    • SELECT 실행 순서 : FROM -> WHERE -> GROUP BY -> HAVING -> SELECT COLUMN .. -> ORDER BY -> LIMIT
  • Alias (별칭) : 주로 산술 연산이 적용된 컬럼에 적용한다. Inline view 등에 주로 사용한다.
    • 컬럼명 AS Alias 또는 컬럼명 Alias 또는 컬럼명 "Alias"
    • 별칭에 공백이 있을 때 " "
  • LIMIT : 결과 행의 수를 제한할 때 사용한다.
  • IN : 비교 연산자, 여러 값 중에서 어느 하나가 일치하는 지 비교한다.
  • NOT IN : IN 의 반대
  • COUNT() : 해당 컬럼의 데이터 개수를 센다.
  • LIKE : 특정 패턴에 속하는 값을 조회할 때 사용한다. WildCard 를 이용한 문자열 부분 매칭을 한다.
    • % : 0개 또는 1개 이상 문자와 대응
    • _ : 한 개 문자에 대응
  • CONCAT(  ,  ) : 문자열 또는 컬럼을 연결
  • SUBSTR(문자열, 시작 인덱스) : 문자열의 시작 인덱스부터 추출한다. (인덱스는 1부터 시작한다.)
  • SUBSTR(문자열, 시작 인덱스, 길이) : 문자열의 시작 인덱스부터 길이만큼 추출한다.
  • trim( ) : 문자열의 공백을 제거한다.
  • ROUND() : 반올림 함수
  • FLOOR() : 내림 함수 (소수점 버림)
  • CEIL() : 올림 함수
  •  DELETE FROM : 특정 행 또는 모든 행 삭제, 트랜잭션의 ROLLBACK 복구 가능
    • auto_increment 는 유지되며, 속도가 느리다.
    • DELETE FROM car;
  • UPDATE : 
    • UPDATE 테이블명 SET 컬럼명  = 업데이트 값 WHERE 조건절
• DCL : 데이터 제어어

1-3. 날짜와 시간

• NOW() : 현재 날짜와 시간을 DATETIME 타입으로 반환
  • 형식 : YYYY-MM-DD HH:MM:SS
  • YEAR() : 년도 추출
  • MONTH() : 월 추출
  • DAY() : 일 추출
  • MINUTE() : 분 추출
  • SECOND() : 초 추출
  • DATE() : 날짜만 추출
• 날짜와 시간 포맷 변경 : Web UI 에서 일반 사용자가 날짜 시간 정보를 입력하면 Web 특성상 문자열로 전송, DB에는 DATE OR DATETIME 등 ... 으로 형변환해서 저장해야 한다. 반대로 DB에 있는 DATE 타입 정보를 Web 상에서 문자열로 표현해야 한다. 
  • DATE_FORMAT() : %Y 4자리 연도 %m 2자리 월 %H 24시간제 시 ...
• INTERVAL : 날짜 및 시간 연산

SELECT date_add(NOW(), INTERVAL 1 DAY); -- 하루 뒤 
SELECT date_sub(NOW(), INTERVAL 1 HOUR); -- 한시간 전

• TIMESTAMPDIFF() : 두 날짜 차이 계산

SELECT timestampdiff(DAY, '2025-01-01', NOW());

1-4. Case 

• When 조건 then '반환 값' Else 'When 조건에 해당하지 않는 경우 반환값'
  • When 과 Then 은 한 쌍이어야 한다.
  • Else 가 존재하면 모든 조건에 해당하지 않는 경우에 반환값을 설정할 수 있다.
  • Else 가 존재하지 않고 조건에 맞지 않으면 NULL을 반환한다.
  • 예) 모든 직원 EMP의 이름 ename, 직무 job, 급여 sal, 커미션 comm, 보너스 bonus 를 조회한다. 보너스는 직무가 MANAGER 이면 급여 sal 의 150%, 그 외 직원은 급여 sal 의 130% 이다.

SELECT ename, job, sal, comm,
		CASE job WHEN 'MANAGER' THEN sal * 1.5
		ELSE sal * 1.3
        END 보너스
FROM emp;

1-5. SubQuery

• 서브 쿼리 (SubQuery) : SQL 내의 SQL로, 부쿼리, 하위 쿼리라고도 한다.
  • GROUP BY : 하나 또는 여러 컬럼을 기준으로 결과 행을 그룹으로 분할하도록 지정한다. 특정 컬럼들을 기준으로 데이터를 그룹화하여 조회할 때 사용한다.
  • HAVING : GROUP BY와 함께 사용되는 조건절이다.

CREATE TABLE item(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 maker VARCHAR(100) NOT NULL,
 price INT NOT NULL
);

INSERT INTO item(name,maker,price) VALUES('불닭볶음면','삼양',1500);
INSERT INTO item(name,maker,price) VALUES('진라면','오뚜기',1100);
INSERT INTO item(name,maker,price) VALUES('테라','하이트진로',1800);
INSERT INTO item(name,maker,price) VALUES('참이슬후레쉬','하이트진로',1300);
INSERT INTO item(name,maker,price) VALUES('열라면','오뚜기',1300);

SELECT COUNT(*) FROM item;
-- 요구 사항 : item table 에 저장된 item 정보 중 가격 비싼 item 의 name 과 maker, price 를 알고 싶다.
SELECT max(price) FROM item; -- 1800
SELECT name, maker, price FROM item WHERE price = 1800;
-- SubQuery 를 이용하면 한번에 조회 가능
SELECT name, maker, price FROM item WHERE price = (SELECT max(price) FROM item);
-- 요구 사항 2 : 전체 item 평균가 avg() 보다 높은 item 중에 가장 낮은 price를 가진 item의 name, price 를 조회

-- 1.평균가 avg(price)
	SELECT AVG(price) FROM item;
-- 2. 평균가보다 높은 상품들
	SELECT name, price FROM item WHERE price > 1400 ORDER BY price ASC;
-- 3. 평균가보다 높은 상품들 중 가장 낮은 가격?
	SELECT min(price) FROM item WHERE price > (SELECT AVG(price) FROM item);
-- 4. 3까지 SQL 을 이용해 요구 사항 2를 sql 한번의 실행으로 조회하게 한다
	SELECT name, price FROM item
    WHERE price = (
    SELECT min(price) FROM item WHERE price > (SELECT AVG(price) FROM item)
    );

-- 복습 : item 중 price가 가장 낮은 min() 가격의 name은?
SELECT name FROM item 
WHERE price = (SELECT min(price) FROM item);

-- 요구 사항 3 maker 가 하이트 진로인 item 중 가장 낮은 price 의 item name 과 price 를 조회
-- step1 maker 가 하이트 진로인 item 중 가장 낮은 price
SELECT min(price) FROM item WHERE maker='하이트진로';

-- step1 을 subquery로 이용
-- 한번 더 maker='하이트진로' 로 걸러내야 한다.
SELECT name, price FROM item WHERE maker='하이트진로' AND price = (SELECT min(price) FROM item WHERE maker='하이트진로');

-- GROUP BY 
-- MAKER 별 상품 수를 조회 
SELECT * FROM item;
-- GROUP BY 적용
SELECT maker, COUNT(*) AS 상품수 
FROM item
GROUP BY maker
ORDER BY 상품수 DESC;
-- GROUP BY 의 조건절 HAVING 
-- MAKER 별 상품 수를 상품수 내림차순으로 조회하되 상품수가 1을 초과하는 MAKER 들만 조회
SELECT maker, COUNT(*) AS 상품수 
FROM item
GROUP BY maker
HAVING COUNT(*) > 1 
ORDER BY 상품수 DESC;

-- MAKER 별 상품의 평균가가 1300 이상인 MAKER와 상품 평균가를 조회하되 평균가 내림차순으로 정렬한다.
-- avg(price)
SELECT maker, round(AVG(price)) AS 평균가
FROM item
GROUP BY maker
HAVING AVG(price) >= 1300
ORDER BY 평균가 DESC;

CREATE TABLE food(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    maker VARCHAR(100) NOT NULL,
    price INT NOT NULL
);

INSERT INTO food(name, maker, price) VALUES('후라이드', '또래오래', 15000);
INSERT INTO food(name, maker, price) VALUES('소곱창', '대한곱창', 20000);
INSERT INTO food(name, maker, price) VALUES('양념치킨', '또래오래', 16000);
INSERT INTO food(name, maker, price) VALUES('참치회', '이춘복참치', 35000);
INSERT INTO food(name, maker, price) VALUES('파닭', '또래오래', 17000);
INSERT INTO food(name, maker, price) VALUES('미니전골', '대한곱창', 18000);
SELECT * FROM food;
-- TRUNCATE TABLE food;

-- MAKER 가 대한곱창 이거나 또래오래 인 FOOD 를 모두 조회 name, maker, price : or 또는 in
SELECT name, maker, price FROM food WHERE maker IN('대한곱창', '또래오래');
SELECT name, maker, price FROM food WHERE maker = '대한곱창' OR maker ='또래오래';
-- MAKER 가 대한곱창과 또래오래가 아닌 FOOD 를 모두 조회 : <> 또는 NOT IN 
SELECT name, maker, price FROM food WHERE maker NOT IN('대한곱창', '또래오래');
SELECT name, maker, price FROM food WHERE maker <> '대한곱창' AND maker <> '또래오래';

-- 연습) 전체 FOOD 평균가 AVG보다 MAKER 별 평균가가 낮은 MAKER 의 FOOD 정보 ( NAME, MAKER, PRICE)
-- 		PRICE 내림차순으로 조회 
-- step1 전체 FOOD 평균가
SELECT AVG(price) FROM food; -- 20166.6
-- step2 maker 별 평균가
SELECT maker, AVG(price) FROM food 
GROUP BY maker;
-- step3 위의 SQL 을 SUBQUERY 로 합친다.
-- 전체 평균가보다 MAKER 별 평균가가 낮은 MAKER 을 조회
SELECT maker
FROM food 
GROUP BY maker HAVING AVG(price) < (SELECT AVG(price) FROM food) ;
-- step4 
-- 전체 FOOD 평균가 AVG보다 MAKER 별 평균가가 낮은 MAKER 의 FOOD 정보 ( NAME, MAKER, PRICE)
SELECT name, maker, price FROM food WHERE maker IN ( 
	SELECT maker FROM food 
	GROUP BY maker HAVING AVG(price) < (SELECT AVG(price) FROM food) 
);
-- maker IN() : in 안에 존재하는 모든 maker 을 선택하는 것

/*
	item table 의 maker 을 기준으로 그룹화 GROUP BY ~ HAVING
    요구 사항 : maker 별 상품총액 (SUM_PRICE) 과 maker 별 상품수량 (ITEM_COUNT) 를 집계하여 
			 상품총액 내림차순으로 정렬해서 조회, 조건은 maker 별 상품 총액이 2000 이상인 maker 를 대상으로 한다.
*/
SELECT * FROM item; 

SELECT maker, SUM(price) AS SUM_PRICE, COUNT(*) AS ITEM_COUNT
FROM item GROUP BY maker
HAVING SUM(price) >= 2000
ORDER BY SUM(price) DESC;

-- CHECK 제약조건 CONSTRAINT : 데이터 저장 시에 특정 조건을 부여 (예 : 성별 남 아니면 여 ) 
-- 아래 제약조건은 계좌 타입 (여행 또는 주택 또는 자기개발) 중 하나로 등록되어야만 하도록 제약조건 
--			   가격은 0을 초과
-- MYSQL 의 INT 4byte, java long -> MYSQL BIGINT 8byte 로 동일
CREATE TABLE test_check(
	account_type VARCHAR(10) CHECK(account_type IN ('여행', '주택', '자기개발')) NOT NULL,
    money BIGINT CHECK(money > 0) NOT NULL 
);
INSERT INTO test_check(account_type, money) VALUES('여행', 100);

-- 제약조건 위배 error
INSERT INTO test_check(account_type, money) VALUES('음주가무', 100);
-- 제약조건 위배 error
INSERT INTO test_check(account_type, money) VALUES('주택', 0);

1-6. 데이터 타입

분류	데이터 타입	크기/범위	권장 사용처	실무 활용도
문자형	VARCHAR(n)	1~655,535 문자	이름, 제목, 설명 등	5
	CHAR(n)	고정 길이 (최대 255)	고정 길이 코드	2
	TEXT	최대 655,535 문자	긴 내용, 게시글 본문	4
숫자형	INT	-2,147,483,648 ~ 2, 147,483,647	ID, 수량, 나이 등	5
	BIGINT	매우 큰 정수	큰 ID, 통계 수치	3
	DECIMAL(M,D)	정확한 소수점 연산	금액, 정밀한 수치	5
	DOUBLE	부동 소수점 (근사치)	과학적 계산	2
날짜시간형	DATETIME	1000-01-01~9999-12-31	생성일시, 수정일시	5
	DATE	날짜만(YYYY-MM-DD)	생년월일, 입사일	4
	TIMESTAMP	UTC 기준 시간	자동 업데이트 시간	4

• DECIMAL : 실제 금융권에서 사용하는 금액 관련 데이터 타입
  • decimal(18, 2) : 18은 총 자릿수, 2는 18자리 숫자 중에서 소수점 이하 2자리를 저장한다는 의미

CREATE TABLE joy_account(
	account_no INT PRIMARY KEY AUTO_INCREMENT, 
	name VARCHAR(50) NOT NULL,
	password VARCHAR(50) NOT NULL,
	account_type VARCHAR(50) NOT NULL CHECK(account_type IN('여행', '주택', '자기개발')),
	balance DECIMAL(18, 2) NOT NULL CHECK(balance>=0), 
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
); -- DECIMAL(18, 2) 로 실제 금융권에서 사용하는 타입으로 설정, CURRENT_TIMESTAMP 계좌 생성 시 자동 시간 저장
-- DECIMAL 타입 공부를 위한 INSERT 구문
SELECT * FROM joy_account;
SELECT account_no, name, password, account_type, balance, created_at 
FROM joy_account WHERE account_no = 11;
-- 1. 기본 
   INSERT INTO joy_account(name, password, account_type, balance)
   VALUES('손흥민','a','여행',1234.56);

-- 2. 소수점 이하가 없는 경우 (자동으로 .00 으로 저장됨)
   INSERT INTO joy_account(name, password, account_type, balance)
   VALUES('이강인','a','자기개발',1000);
   
-- 3. 소수점 이하가 한자리인 경우 (자동으로 .0 으로 채워짐)
   INSERT INTO joy_account(name, password, account_type, balance)
   VALUES('황인범','a','여행', 1000.5);

-- 4. 소수점 이하가 두자리보다 많은 경우 (세번째 자리에서 반올림됨 .125->.13)
   INSERT INTO joy_account(name, password, account_type, balance)
   VALUES('황희찬','a','여행', 100.125);

   INSERT INTO joy_account(name, password, account_type, balance)
    VALUES('조규성','a','여행', 200.984); -- 세번째 자리에서 반올림됨 200.984 -> 200.98

-- 5. 큰 숫자를 넣어 자리수 확인
 INSERT INTO joy_account(name, password, account_type, balance)
    VALUES('김진규','a','여행', 1234567890123456.99); -- 16자리 정수부 + 2자리 소수부
 INSERT INTO joy_account(name, password, account_type, balance)
    VALUES('김진규','a','여행', 12345678901234567.99); -- error : 범위를 넘어 에러
    
-- 6. 매우 작은 수
INSERT INTO joy_account(name, password, account_type, balance)
    VALUES('이승우','a','여행', 0.001); -- 0.00 으로 저장 
    
INSERT INTO joy_account(name, password, account_type, balance)
    VALUES('조승우','a','여행', 0.009); -- 0.01로 저장, 반올림

• DATETIME : 날짜와 시간 정보를 저장
  • YYYY-MM-DD HH:MI:SS 형식으로 저장됨
  • TIMESTAMP 와 달리 시간대 변환이 일어나지 않고 입력값 그대로 저장
  • 생성 일시와 같은 불변의 시점을 기록할 때 매우 적합

1-7. 제약 조건

• CONSTRAINT fk_k_emp FOREIGN KEY(deptno) REFERENCES k_department(deptno)
•       제약 조건    제약조건명       외래키(현 테이블 컬럼)     참조            부모 테이블 (부모 테이블 컬럼)
• Foreign Key(FK) : 참조 무결성을 보장하는 참조 키 또는 외래 키 제약조건에 사용한다.
  • 참조 무결성 : 관계형 데이터베이스에서 테이블 간의 관계가 일관되도록 유지하는 규칙
    • 잘못된 데이터가 입력되는 것을 방지 -> 데이터의 일관성 유지 (현 회사에 없는 부서번호로 사원이 등록되는 현상을 막음)
    • 고아 데이터 생성을 방지 -> 데이터 무결성 보장
  • CASCADE : FK 제약 조건에 사용되는 옵션이다.
    • ON DELETE CASCADE : 부모 테이블에서 정보가 삭제될 때 참조하는 자식 테이블의 정보도 자동으로 삭제되게 하는 옵션이다. -> 주로 사용된다.
    • ON UPDATE CASCADE : 부모 테이블의 정보가 수정될 때 자식 테이블의 정보도 함께 수정한다.

1-8. JOIN

• 하나 이상의 여러 테이블들의 정보를 결합하여 조회하기 위한 SQL
  • ANSI SQL (표준 SQL) 방식으로 조인을 공부함
  • 형식 (테이블 1과 테이블 2를 조인, ON 이후부터는 조인 조건)
    • SELECT 별칭 1.컬럼명, 별칭1.컬럼명, 별칭2.컬럼명
    • FROM 테이블명1 별칭
    • INNER JOIN 테이블명2 별칭 2 ON 별칭1.컬럼명 = 별칭2.컬럼명
    • 조인 조건
  • JOIN : 하나 이상의 여러 테이블을 결합해 조회할 때 사용
    • INNER JOIN : 양쪽 테이블에 모두 존재하는 데이터만 조회한다. 테이블 간에 매칭되는 컬럼 정보가 존재할 때 사용하는 조인이다.
      • ex) 부서 테이블의 부서 번호와 사원 테이블이 부서 번호를 기준으로 조인한다.
    • OUTER JOIN : 여러 테이블 중 한 쪽에는 데이터가 있고 다른 한 쪽에는 데이터가 없는 경우에 사용한다. 테이블 간의 매칭되는 컬럼의 정보가 존재하지 않을 때에도 조인한다.
      
      • ex) 부서 테이블의 부서 번호가 10, 20, 30, 40번 부서로 구성이 되어 있다.
        • 이 때 사원 테이블의 사원 정보 중 사원이 속한 부서번호는 10, 20, 30번으로 등록되어 이런 상태에서 INNER JOIN을 하면 사원이 없는 부서 번호는 조회되지 않는다.
        • 사원이 없는 부서 테이블의 부서 정보까지 모두 조회하고자 할 때 OUTER JOIN을 사용한다.
      • LEFT OUTER JOIN : 왼쪽 테이블의 모든 데이터 + 오른쪽 테이블의 매칭 되는 데이터
        • 왼쪽 테이블 기준으로 오른쪽 테이블 정보를 결합한다.
      • RIGHT OUTER JOIN : 왼쪽 테이블의 매칭 되는 데이터 + 오른쪽 테이블의 모든 데이터
        • 오른쪽 테이블 기준으로 왼쪽 테이블 정보를 결합한다.
    • SELF JOIN : 하나의 테이블을 마치 두개의 독립된 테이블처럼 사용하여 조인한다.

1-9. 복합키

• 두 개 이상의 컬럼이 조합되어 Primary Key를 구성한다. 

CREATE TABLE order_detail(
order_id INT,
product_id INT,
order_count INT NOT NULL,
CONSTRAINT pk__order_detail PRIMARY KEY(order_id, product_id)
); -- primary key => NOT NULL + UNIQUE : 존재하고 유일해야 함 (별도로 NOT NULL 잡지 않아도 된다.)

INSERT INTO order_detail(order_id, product_id, order_count) VALUES(1, 7, 5); -- ok
INSERT INTO order_detail(order_id, product_id, order_count) VALUES(1, 8, 9); -- ok
INSERT INTO order_detail(order_id, product_id, order_count) VALUES(1, 7, 4); -- fail

-- 복합 PK 제약조건에 의해 ERROR 발생 : Duplicate entry '1-7' for key 'order_detail.PRIMARY'

COMMIT;

2. 정규화

2-1. 정규화란?

• 데이터 중복을 최소화하고 이상 현상을 방지하여 무결성을 보장하기 위해 테이블을 분리하는 과정이다.
• 데이터 중복을 최소화하여 저장 공간 효율성을 높이고, 이상 현상 (삽입, 삭제, 수정 이상 현상)을 방지하여 데이터 ㅇ무결성을 보장하기 위해 테이블을 체계적으로 분리한다.
• 정규화가 필요한 이유 - 이상 현상 (Anomaly)

주문번호	고객명	고객전화	상품번호	상품명	단가	수량
1	손흥민	010-12	7,8	진라면, 새우깡	1500, 1300	3,2
2	아이유	010-13	7	진라면	1500	7
3	손흥민	010-12	9	코카콜라	2000	2

• 삽입 이상 (Insertion Anomaly)
  • 새 상품을 등록하려면 반드시 주문이 있어야 한다.
  • 고객 정보만 등록하고 싶어도 주문 없이는 불가능하다.
• 수정 이상 (Update Anomaly)
  • 새우깡 가격이 2000원으로 변경되면 모든 관련 데이터를 전부 수정해야 한다.
  • 손흥민 전화번호가 바뀌면 관련 데이터 (레코드) 를 모두 수정해야 한다.
• 삭제 이상 (Deletion Anomaly)
  
  • 주문번호 2를 삭제하면 아이유 고객 정보도 함께 사라진다.
  • 특정 상품 마지막 주문을 삭제하면 해당 상품 정보도 영구적으로 손실된다.

2-1. 제 1 정규화

• 규칙 : 컬럼 값은 원자값이어야 한다. 
  • 원자값 : 더 이상 쪼갤 수 없는 값
• 컬럼 값, 속성 값은 반드시 원자값이 되어야 한다. 즉 값이 여러개 이면 제 1 정규화에 위배된다.

상품번호	7, 8
상품명	진라면, 새우깡
단가	1500, 1800

2-2. 제 2 정규화

• 규칙 : 제 1 정규화를 만족하고, 부분 함수 종속을 제거해야 한다.
  • 부분 함수 종속 : 복합 키의 일부에 의해 결정되는 속성, 복합 키의 일부 속성에만 종속된 속성
  • 완전 함수 종속 : 복합 키 전체에 의해 결정되는 속성

2-3. 제 3 정규화

• 규칙 : 제 2 정규화를 만족하고, 이행 함수 종속을 제거해야 한다.
  • 이행 함수 종속 : A -> B 이고, B -> C 이면, A -> C 이다 (이행적 관계)
• 비 주요 속성(기본 키가 아닌) 간의 종속 관계를 분리한다. 
• 일반 속성 (기본 키가 아닌) 에 종속된 속성을 별도의 테이블로 분리한다.
  • ex) 주문번호 -> 담당부서번호 -> 부서명, 부서지역, 부서Tel
  • 결론적으로 주문 테이블 에서는 담당 부서 번호만 저장하고 부서 테이블에서 부서명, 부서지역, 부서Tel 등을 분리하여 저장하는 것이 무결성 보장 및 저장 공간의 효율성 향상에 유리하다.

2-4. 교차 엔티티 (Association Entity, Intersection Entity)

• 다 대 다 관계를 해소하기 위한 엔티티 (또는 테이블), 논리 모델링에서는 엔티티, 물리 모델링에서는 테이블이다.
• 연관 엔티티, 제휴 엔티티라고도 한다.
  
  • ex) 다 대 다 관계를 해소하는 교차 엔티티(테이블) 인 주문 상세 테이블을 중간에 배치한다.
  • 주문과 주문 상세는 1 대 다 관계, 주문 상세와 상품은 다 대 1 관계 가 된다.

2-5. 반정규화

• 데이터베이스 성능 향상을 위해 의도적으로 정규화 원칙을 위배하는 것이다.
• 반정규화의 목적
  • JOIN 연산 감소로 성능 향상
  • 자주 조회되는 데이터를 하나의 테이블에 저장
• 반정규화 주의 사항
  • 데이터 중복으로 인한 저장 공간 증가
  • 데이터 일관성 유지의 어려움
  • 업데이트 시 여러 곳을 수정해야 하는 복잡성

[정규화 요약]
정규화란 데이터 중복을 피하고 이상 현상을 방지 및 무결성을 보장하기 위해 테이블을 체계적으로 분리하는 과정이다.
1NF : 원자값 원칙 적용 (다중값 컬럼 분리, 주문 상세 정보 분리)
2NF : 부분 함수 종속 제거 (복합키 일부에만 종속되는 상품 정보 분리)
3NF : 이행 함수 종속 제거 (일반 속성인 부서번호에 종속되는 부서 정보 분리)

3. JDBC

3-1. JDBC (Java Database Connectivity) 란?

• Java 와 DB 연동을 위한 기술이다.
• 응용프로그램과 DBMS 간의 통신을 중간에서 번역해주는 역할을 한다.
• 일반 사용자 <-> Java application (Frontend+BackEnd) <-> JDBC (interface, 각 DB Driver 구현체) <-> Database

3-2. JDBC 설계 구조

• JDBC 인터페이스를 설계하는 측 (Sun < Oracle)
• DB Vender(회사) 에서 JDBC Interface 의 구현 클래스들(mysql-connect.jar 또는 ojdbc8.jar ... )을 제공하는 측
• Java Application(응용 프로그램) 을 개발해 사용자에게 서비스하는 측

3-3. JDBC 특징

• 자바 응용 프로그램 개발자 측에서는 JDBC의 표준 명세 (Interface) 를 보고 개발
• 실제 동작은 DB Vender 사에서 제공한 구현체 클래스들이 실행한다.
  • 효과 : DB Vender 사에서 자신의 Driver class 틀을 업그레이드 해도 어플리케이션에서는 영향이 최소화 된다.
  • Loose Coupling, 느슨한 결합도 때문에 유지 보수성이 향상된다.
  • High Cohesion, 높은 응집도로 개발 생산성과 높은 품질을 보장한다. 따라서 응용 프로그램 개발자와 DB Vender은 각자의 역할에 집중하면 된다.
  • Abstraction 또는 Interface 가 어떤 장점을 가져다 주는가에 대한 사례도 JDBC로 설명할 수 있다.

3-4. JDBC 프로그래밍 구현 단계

• JDBC Driver Loading 단계 : Class.forName (패키지.클래스);
  • 패키지 클래스 정보는 DB에 따라 다르게 설정한다.
  • 생성자에서 한 번만 실행하면 된다. Java 상위버전 1.8 이상에서는 생략이 가능하며(내부적으로 처리), 명시한다면 생성자에서 한 번 실행한다.
• Connection : DB와 연결 정보를 저장하는 객체로, DB와의 소통을 위해 연결한다.
  • 사용자 별로 서비스마다 매번 실행한다. 메소드 단위에서 매번 만들어야 한다.
• Statement or PreparedStatement : Java Application이 SQL을 실행하기 위한 객체
  • 문자열로 SQL을 결합해서 실행하게 된다.
  • Statement 를 사용하면 입력 값을 SQL 문자열로 직접 결합하게 되어 보안 위험이 발생한다. 또한 실행 시점에 DB가 SQL을 매번 새롭게 인식 (내부 컴파일) 하므로 성능이 좋지 않다.
  • PreparedStatement 를 사용하면 ? 를 제외한 SQL 정보를 미리 로드하여 성능을 높인다. 즉 처음 실행한 후 이후에는 ? 만 갈아끼우면 된다. java의 setter/getter 과 비슷한 역할을 한다.
    • String sql = INSERT INTO post VALUES(?,?,?,?);
    • pstmt = connection.prepareStatement(sql : 실행SQL, Statement.RETURN_GENERATED_KEYS );
      • Statement.RETURN_GENERATED_KEYS : 자동 생성된 키를 JDBC가 반환하도록 지시한다.
    • pstmt.setString(1, "안녕"); : 1은 첫번째 물음표에 해당
    • pstmt.excuteUpdate() : int 형 반환 -> 값의 의미는 DML 이 영향을 준 ROW 수, 1명 등록, 3명 삭제, 5명 수정
      • insert가 되는 시점에 auto increment가 실행되어 key 값을 생성한다.
    • pstmt.executeQuery() : ResultSet, SQL 실행 후 조회 결과 정보 집합
    • rs = pstmt.getGeneratedKeys(); : 실행된 INSERT 문으로 생성된 키를 반환하는 ResultSet
• close : 자원 해제, 생성 역순으로 close 해준다.

3-5. 기본 JDBC 프로그램 클래스 

• Dao (Data Access Object) : 데이터 엑세스(제어) 로직을 담당하는 클래스
• VO (Value Object) : 데이터를 저장하는 클래스
• DTO (Data Transfer Object) : 데이터 전송을 담당하는 클래스
• Service : 비즈니스 로직을 담당하는 클래스, 트랜잭션(작업 단위) 처리
• Controller : 클라이언트의 요청을 전담, Model과 연동 후 결과를 응답

4. 트랜잭션(Transaction)

4-1. 트랜잭션이란? 

• 하나의 논리적 작업 단위로, 반드시 모두 성공하거나 실패 시 모두 취소해야 하는 세부 작업 묶음이다.
• 목적 : 신뢰할 수 있는 데이터 처리의 핵심으로 반드시 작업 단위, 즉 트랜잭션 단위의 전체 작업 성공 여부에 따라 COMMIT 또는 ROLLBACK 되어야 한다.
• TCL (Transaction Control Language)
  • COMMIT : 지금까지의 작업 내용(변경 사항)을 데이터베이스에 영구 저장
  • ROLLBACK : 변경 사항을 취소하고 트랜잭션(작업 단위) 이전 상태로 복구

4-2. 트랜잭션의 4가지 특성 - ACID

• 원자성 (Atomicity) : 모든 작업 전부 실행되거나 전혀 실행되지 않아야 한다.
• 일관성 (Consistency) : 트랜잭션 전후에 데이터 일관성이 보장되어야 한다.
• 격리성 (Isolation) : 동시에 실행되는 트랜잭션끼리는 서로 간섭하지 않아야 한다.
• 지속성 (Durability) : 커밋된 데이터는 영구 보존되어야 한다.

4-3. 트랜잭션 예제

package org.kosa.card.test.step2;

import org.kosa.card.model.CardDao;

/*
 * 트랜잭션 처리가 적절히 되어 있지 않는 경우 발생할 수 있는 문제점을 확인
 * 포인트 등록 과정에 문제가 발생하더라도 카드 등록은 진행되어 DB에 저장된다. -> 데이터 무결성 훼손
 * step1 의 테스트 과정에서 위 문제점을 확인
 * =======================================
 * 트랜잭션 처리를 통해 데이터 무결성을 보장하는 예제 
 * 
 * 아래의 황희찬 고객의 card 등록 후 point 등록시점에서 문제가 발생하면 
 * card 등록된 작업 내용까지 모두 작업 취소, 원상복귀 되는 것을 확인
 */
public class TransactionRegisterCardUnitTest {
	public static void main(String[] args) {
		CardDao dao = new CardDao();
		String id = "a";
		String name ="황희찬";
		String pointType = "홈플러스";
		//pointType = null; // 트랜잭션 테스트를 위해 일부러 문제를 발생시킨다. pointType 은 not null 제약조건
		int point = 300;
		try {
			dao.registerTransaction(id, name, pointType, point);
			System.out.println(name+"님 카드와 포인트가 정상적으로 발급되었습니다.");
			
		}catch(Exception e){
			System.out.println("카드 발급 과정 중 문제 발생");
			e.printStackTrace();
		}
	}
}

package org.kosa.card.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.kosa.card.common.DatabaseUtil;

public class CardDao {

	public void registerTransaction(String id, String name, String pointType, int point) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DatabaseUtil.getConnection();
			// 트랜잭션 처리를 위해 JDBC 기본인 Auto commit 을 해제한다. -> 수동 커밋 모드
			//이 try 가 트랜잭션 하나라고 생각하면 된다.
			con.setAutoCommit(false);
			String insertCardSql = "INSERT INTO card(id, name) VALUES(?,?)";
			String insertPointSql = "INSERT INTO point(id, point_type, point) VALUES(?, ?, ?);";
			pstmt = con.prepareStatement(insertCardSql);
			pstmt.setString(1, id); //첫번째 물음표
			pstmt.setString(2, name); //두번째 물음표
			int cardResult = pstmt.executeUpdate();
			System.out.println("card 등록 :"+cardResult);
			pstmt.close();
			pstmt = con.prepareStatement(insertPointSql);
			pstmt.setString(1, id);
			pstmt.setString(2, pointType);
			pstmt.setInt(3, point);
			int pointResult = pstmt.executeUpdate();
			System.out.println("point 등록:"+pointResult);
			con.commit(); // COMMIT 트랜잭션 내의 모든 세부 작업이 정상적으로 수행되어 실제 데이터베이스에 반영
			System.out.println("COMMIT 트랜잭션 내의 모든 세부 작업이 정상적으로 수행되어 실제 데이터베이스에 반영");
		}catch(Exception e){
			con.rollback(); // ROLLBACK 트랜잭션 내의 세부 작업에 문제가 발생하여 모든 작업을 취소시킨다.
			//카드는 등록 되는데 point 는 등록 안되는 경우, 카드도 등록하면 안된다.
			//따라서 문제가 생기면 둘 다 등록하면 안되므로, 수동 커밋 및 롤백 구문을 만들어준다.
			System.out.println("ROLLBACK 트랜잭션 내의 세부 작업에 문제가 발생하여 모든 작업을 취소시킨다.");
			throw e; //호출한 측(사용하는 측)으로 해당 예외를 다시 전파한다.

		} finally {
			DatabaseUtil.closeAll(pstmt, con);
		}
	}

}

5. UML (Use Case Diagram)

• 요구 분석을 위한 UML 로, 시스템에 요구되는 기능을 사용자 관점에서 나타낸 다이어그램이다. 
• Use Case : 사용자 관점의 기능 단위, 각각의 Use Case 는 시스템의 특정 기능을 사용자  관점에서 설명한다.
• Actor : 시스템과 상호작용하는 모든 외부 개체
  • 시스템 외부에 존재, 시스템의 Use Case를 시작하거나 Use Case 로부터 정보를 받는다.
  • Actor 은 사람일 수도 있고 다른 시스템일수도 있다.
• Include : 포함 관계, 기본 Use Case가 실행되기 위해서는 반드시 다른 특정 Use Case 의 행위를 포함해야 한다는 것을 의미한다. 하나의 Use Case가 다른 Use Case (포함된 Use Case)의 기능을 반드시 사용해야 할 때 적용한다.
• extend : 확장 관계, Use Case 간의 선택적인 확장 관계를 나타낸다. Use Case 가 특정 Use Case에 정의된 행위로 선택적으로 추가 확장될 수 있다.
• Generalization : 일반화 관계, Use Case 간의 상속 또는 일반, 특수 관계를 나타낸다. 특정 Use Case가 다른 Use Casse의 모든 행동을 상속하고, 그 위에 자신만의 고유한 행동을 추가할 때 사용한다. 

6. ERD (Entitly Relationship Diagram)

• 개쳬 관련 모델링, 즉 데이터 모델링을 위한 다이어그램이다.
  • 논리 데이터 모델링 
    • 논리적인 데이터 관리 및 관계를 정의한 모델
    • 전체 업무 범위와 업무 구성 요소를 정의하고 확인할 수 있다.
    • 비즈니스 중심의 구조화로 데이터 요구사항을 명확히 한다.
  • 물리 데이터 모델링
    • 논리 데이터 모델을 DBMS 특성에 맞게 구체화시킨 모델을 말한다.
      • DBMS 중심의 최적화로 실제 구현을 위한 구조를 정의한다. 
      • 부모 테이블 : 참조의 기준이 되는 테이블, 자식 테이블이 이 테이블의 데이터를 참조한다.
        • ex) 부서 테이블과 사원 테이블 - 부서 테이블의 부서 ID 가 기본 키가 되어서 부서를 고유하게 식별한다.
      • 자식 테이블 : 참조하는 테이블, 부모 테이블의 데이터를 참조하는 테이블이다.
        • ex) 부서 테이블과 사원 테이블 - 부서 테이블의 부서 ID가 기본 키가 되어서 부서를 고유하게 식별하고, 사원 테이블에서 이 부서 ID를 참조한다.

• 식별 관계 (identified relationship) : 부모 테이블의 기본키가 자식 테이블의 기본키 혹은 복합키의 구성원으로 전이되는 관계
  • 부모가 있어야 자식이 생기는 경우이다.
  • 부모 테이블의 기본키 혹은 복합키가 자식 테이블의 기본키 혹은 복합키의 구성원으로 전이되는 관계이다. (ex : 사원과 신체정보)
• 비식별관계 (non-identified relationship) : 부모 테이블의 기본키가 자식 테이블의 일반 속성으로 전이되는 관계
  • 부모 테이블의 기본키 혹은 복합키가 자식 테이블의 일반 속성으로 전이되는 관계이다.
  • (ex : 부서와 사원 정보)