● 조합키(복합키)
- 두 개 이상의 컬럼을 하나의 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차 정규화 진행 전
![](https://blog.kakaocdn.net/dn/lRlwl/btryezCj1sX/MnmuTE2wTa42z8N5NRtEkk/img.png)
- 2차 정규화 진행
![](https://blog.kakaocdn.net/dn/zpAZy/btryfiUd8LF/KDMbVVtKgCB7sxt0pTeiWK/img.png)
● 3차 정규화(3NF)
- 하나의 컬럼이 다른 컬럼을 결정해서는 안된다.
※ 3차 정규화 진행 전
- 우편번호로 시, 구, 동을 알 수 있다.
- 중복된 데이터가 생길 가능성이 있다.
![](https://blog.kakaocdn.net/dn/oxaXj/btrycwUpMnl/NAoKfoNhrQKWcLCiZbHeOK/img.png)
- 3차 정규화 진행
![](https://blog.kakaocdn.net/dn/bMtSOO/btrydfkiAC2/cdfSK5Gnwb2HW7j0qbOzAk/img.png)
● 데이터베이스에서 정규화가 필요한 이유
- 데이터베이스를 잘못 설계하면 불필요한 데이터 중복으로 인해 공간이 낭비된다.
- 이런 현상을 이상(Anomaly)현상이라고 한다.
![](https://blog.kakaocdn.net/dn/4iV6D/btryd9w701T/ES6fFnsISvUz1AAQz4bDM1/img.png)
● 이상 현상의 종류
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;
'웹 개발 > DBMS' 카테고리의 다른 글
[Web_DBMS] 08 (0) | 2022.04.06 |
---|---|
[Web_DBMS] 07 (0) | 2022.04.05 |
[Web_DBMS] 05 (0) | 2022.04.03 |
[Web_DBMS] 04 (0) | 2022.04.02 |
[Web_DBMS] 03 (0) | 2022.04.01 |