[Web_DBMS] 06

● 조합키(복합키)

- 두 개 이상의 컬럼을 하나의 PK로 설정

 

(1) PRIMARY KEY

CONSTRAINT [제약조건명] PRIMARY KEY(컬럼명1, 컬럼명2, ...)

 

(2) FOREIGN KEY

CONSTRAINT [제약조건명] FOREIGN KEY(컬럼명1, ...) REFERENCES [부모 테이블명](부모컬럼명1, 부모컬럼명2, ...)

 

 

---

 

● TCL(Transaction Control Language)

- 트랜젝션 제어어

 

1. 트랜젝션

- 하나의 작업 단위

 

2. COMMIT

- 모든 작업을 확정하는 명령어

 

3. ROLLBACK

- 이전 커밋한 지점(시점)으로 이동

 

※ TRUNCATE는 테이블 내용을 전체 삭제하므로, DELETE보다 빠르게 처리할 수 있다.

   대용량 데이터 처리에 유리하지만 복구가 TCL로 불가능하기 때문에 복구가 가능한 DELETE를 사용하는 것이 좋다.

 

 

 

● 정규화

- 삽입/수정/삭제의 이상현상을 제거하기 위한 작업

- 데이터의 중복을 최소화하는 데에 목적이 있다.

- 5차 정규화까지 있으나 3차 정규화까지만 진행한다.

 

 

 

● 1차 정규화(1NF)

- 하나의 컬럼에 값이 1개씩만 있어야 한다.

 

※ 1차 정규화 진행 전

   상품명

   바지1, 바지2, 바지3

   → 검색이 힘들다.

 

 

- 1차 정규화 진행

   상품명

   바지1

   바지2

   바지3

 

 

 

● 2차 정규화(2NF)

- 테이블의 모든 컬럼이 서로 관계가 있어야 한다.

 

※ 2차 정규화 진행 전

 

- 2차 정규화 진행

 

 

 

● 3차 정규화(3NF)

- 하나의 컬럼이 다른 컬럼을 결정해서는 안된다.

 

※ 3차 정규화 진행 전

- 우편번호로 시, 구, 동을 알 수 있다.

- 중복된 데이터가 생길 가능성이 있다.

 

- 3차 정규화 진행

 

 

 

● 데이터베이스에서 정규화가 필요한 이유

- 데이터베이스를 잘못 설계하면 불필요한 데이터 중복으로 인해 공간이 낭비된다.

- 이런 현상을 이상(Anomaly)현상이라고 한다.

 

---

 

 

● 이상 현상의 종류

1. 삽입 이상

- 새 데이터를 삽입하기 위해 불필요한 데이터도 삽입해야하는 문제

 

=> 담당 프로젝트가 정해지지 않은 사원이 있다면,

     프로젝트 코드에 NULL을 작성할 수 없으므로 이 사원은 테이블에 추가될 수 없다.

     따라서 '미정'이라는 프로젝트 코드를 따로 만들어서 삽입해야 한다.

 

2. 갱신 이상

- 중복 행 중에서 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제

 

=> 한 명의 사원은 반드시 하나의 부서에만 속할 수 있다.

     만약 "김철수"가 보안팀으로 부서를 옮길 시 3개 모두 갱신해주지 않는다면 개발팀인지 보안팀인지 알 수 없다.

 

3. 삭제 이상

- 행을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 문제

 

=> "이순신"이 담당한 프로젝트를 박살내서 드랍된다면 이순신 행을 모두 삭제하게 된다.

     따라서 프로젝트에서 드랍되면 정보를 모두 드랍하게 된다.

 

 

 

● 이상 현상이 발생하는 이유

- 테이블이 정규화가 되어 있지 않기 때문에 발생한다.

- 정규화를 진행하기 위해서는 각 컬럼간의 관련성을 파악해야 하고,

   이 관련성을 "함수적 종속성"(Functional Dependency)이라고 한다.

- 하나의 테이블에서는 반드시 하나의 함수적 종속성만 존재하도록 정규화를 한다.

 

 

 

● 함수

- X -> Y : X가 Y에 의존한다.

 

※ X : 결정자, X가 Y를 결정

※ Y : 종속자, Y가 X에 종속

 

 

---

 

실습(DML(INSERT))

1. TBL_FLOWER 테이블에 데이터 추가

- 장미, 빨간색, 1500원

INSERT INTO TBL_FLOWER
VALUES('장미', '빨간색', 1500);

 

 

- 장미, 핑크색, 1700원

INSERT INTO TBL_FLOWER
VALUES('장미', '핑크색', 1700);

 

 

- 해바라기, 노란색, 3500원

INSERT INTO TBL_FLOWER
VALUES('해바라기', '노란색', 3500);

 

 

 

2. TBL_POT 테이블에 데이터 추가

※ TBL_POT의 데이터를 추가하기 전에 POT_NUMBER의 자료형을 바꿔준다.

ALTER TABLE TBL_POT MODIFY(POT_NUMBER VARCHAR2(1000));

 

 

- APPLE001, GREEN, 사과모양, 장미

INSERT INTO TBL_POT
(POT_NUMBER, POT_COLOR, POT_SHAPE, FLOWER_NAME)
VALUES('APPLE001', 'GREEN', '사과모양', '장미');

 

 

- POT001, WHITE, 원기둥, 해바라기

INSERT INTO TBL_POT
(POT_NUMBER, POT_COLOR, POT_SHAPE, FLOWER_NAME)
VALUES('POT001', 'WHITE', '원기둥', '해바라기');

 

 

- POT002, BLACK, 직사각형, 해바라기

INSERT INTO TBL_POT
(POT_NUMBER, POT_COLOR, POT_SHAPE, FLOWER_NAME)
VALUES('POT002', 'BLACK', '직사각형', '해바라기');

 

 

 

 

실습(DML(DELETE))

※ 삭제를 하기 전에 무조건 데이터 조회부터 해야한다.

 

1. TBL_FLOWER 테이블의 데이터 삭제

- 장미 삭제

SELECT FLOWER_NAME FROM TBL_FLOWER
WHERE FLOWER_NAME = '장미';

DELETE FROM TBL_FLOWER
WHERE FLOWER_NAME = '장미';

 

 

- 1500원보다 비싼 꽃 삭제

SELECT * FROM TBL_FLOWER
WHERE FLOWER_PRICE > 1500;

DELETE FROM TBL_FLOWER
WHERE FLOWER_PRICE > 1500;

 

 

- 장미를 담을 수 있는 화분 삭제

SELECT * FROM TBL_POT
WHERE FLOWER_NAME = '장미';

DELETE FROM TBL_POT
WHERE FLOWER_NAME = '장미';

 

 

 

 

실습(DML(UPDATE))

※ 수정을 하기 전에 무조건 데이터 조회부터 해야한다.

 

1. TBL_FLOWER 테이블의 데이터 수정

- 빨간색 장미 가격 10% 인상

SELECT * FROM TBL_FLOWER
WHERE FLOWER_NAME = '장미' AND FLOWER_COLOR = '빨간색';

UPDATE TBL_FLOWER
SET FLOWER_PRICE = FLOWER_PRICE * 1.1
WHERE FLOWER_NAME = '장미' AND FLOWER_COLOR = '빨간색';

 

 

- 핑크색 장미 가격 100배 인상

SELECT * FROM TBL_FLOWER
WHERE FLOWER_NAME = '장미' AND FLOWER_COLOR = '핑크색';

UPDATE TBL_FLOWER
SET FLOWER_PRICE = FLOWER_PRICE * 100
WHERE FLOWER_NAME = '장미' AND FLOWER_COLOR = '핑크색';

 

 

 

2. TBL_POT 테이블의 데이터 수정

- 직사각형인 화분을 정사각형으로 변경

SELECT * FROM TBL_POT
WHERE POT_SHAPE = '직사각형';

UPDATE TBL_POT
SET POT_SHAPE = '정사각형'
WHERE POT_SHAPE = '직사각형';

 

 

 

 

실습(DML)

※ 기존에 있던 테이블(PLAYER) 및 데이터를 가지고 실습을 진행한다.

 

1. PLAYER 테이블에서 TEAM_ID가 'K01'인 선수 검색

SELECT * FROM PLAYER
WHERE TEAM_ID = 'K01';

 

 

 

2. PLAYER 테이블에서 TEAM_ID가 'K01'이 아닌 선수 검색

SELECT * FROM PLAYER
WHERE TEAM_ID <> 'K01';

 

 

 

3. PLAYER 테이블에서 WEIGHT가 70이상이고 80이하인 선수 검색

- 연산자 사용

SELECT * FROM PLAYER
WHERE WEIGHT >= 70 AND WEIGHT <= 80;

 

 

- BETWEEN 사용

SELECT * FROM PLAYER
WHERE WEIGHT BETWEEN 70 AND 80;

 

 

 

4. PLAYER 테이블에서 TEAM_ID가 'K03'이고 HEIGHT가 180미만인 선수 검색

SELECT * FROM PLAYER
WHERE TEAM_ID = 'K03' AND HEIGHT < 180;

 

 

 

5. PLAYER 테이블에서 TEAM_ID가 'K06'이고 NICKNAME이 '제리'가 아닌 선수 검색

SELECT * FROM PLAYER
WHERE TEAM_ID = 'K06' AND NICKNAME ^= '제리';

 

 

 

6. PLAYER 테이블에서 HEIGHT가 170이상이고 WEIGHT가 80이상인 선수 이름 검색

SELECT * FROM PLAYER
WHERE HEIGHT >= 170 AND WEIGHT >= 80;

 

 

 

7. STADIUM 테이블에서 SEAT_COUNT가 30000초과이고 41000이하인 경기장 검색

- 연산자 사용

SELECT * FROM STADIUM
WHERE SEAT_COUNT > 30000 AND SEAT_COUNT <= 41000;

 

 

- BETWEEN 사용

SELECT * FROM STADIUM
WHERE SEAT_COUNT BETWEEN 30001 AND 41000;

 

 

 

8. PLAYER 테이블에서 TEAM_ID가 'K02'이거나 'K07'이고 포지션은 'MF'인 선수 검색

- OR, AND 사용

/*우선 순위는 AND가 OR보다 높다.*/
SELECT * FROM PLAYER
WHERE (TEAM_ID = 'K02' OR TEAM_ID = 'K07') AND "POSITION" = 'MF';

 

 

- IN, AND 사용

/*IN이 AND보다 우선순위가 높다.*/
SELECT * FROM PLAYER
WHERE TEAM_ID IN('K02', 'K07') AND "POSITION" = 'MF';

 

 

 

 

실습(TCL)

※ 상단바의 {Auto}를 {None} 상태로 바꿔준 후 진행한다.

 

1. PLAYER 테이블에서 TEAM_ID가 'K01'인 선수 이름을 전부 홍길동으로 바꾸기

UPDATE PLAYER
SET PLAYER_NAME = '홍길동'
WHERE TEAM_ID = 'K01';

SELECT * FROM PLAYER
WHERE PLAYER_NAME = '홍길동';

 

 

 

2. PLAYER 테이블에서 POSITION이 'MF'인 선수 삭제하기

SELECT * FROM PLAYER
WHERE "POSITION" = 'MF';

DELETE FROM PLAYER
WHERE "POSITION" = 'MF';

 

 

 

3. PLAYER 테이블에서 HEIGHT가 180 이상인 선수 삭제하기

SELECT * FROM PLAYER
WHERE HEIGHT >=180;

DELETE FROM PLAYER
WHERE HEIGHT >= 180;

 

 

 

→ ROLLBACK, COMMIT

- 위의 코드 다음줄에 각각 작성 후 실행 시 데이터를 변경하기 이전의 상태로 돌아간다.

ROLLBACK;
COMMIT;