DBeaver)제3정규형 (3NF) & Foreign Key

제1, 제2에 이어서 제3정규형도 있는데 만드는 법을 알아봅시다. 

 

 

 

 

 

제3정규형 (3rd normal form)

 

제2정규형을 만족하는 테이블에서 primary key 또는 composite primary key 컬럼에 종속된게 아닌

하찮은 잔챙이 컬럼에 종속된 컬럼을 다른 테이블로 빼면

그게 제3정규형 테이블입니다. 

 

 

 

[체육센터 프로그램 table]

프로그램	가격	강사	출신대학
스쿼시	5000	김을용	서울대
헬스	6000	박덕팔	연세대
골프	8000	이상구	고려대
골프 중급	9000	이상구	고려대
개인피티	6000	박덕팔	연세대

 

▲ 대충 제2정규화해놓은 테이블을 가져왔습니다. 

제2정규형을 만족하기 때문에 partial dependency는 없어보이는군요. 

하지만 '출신대학'이라는 컬럼에 주목해봅시다. 

'프로그램'이라는 primary key와는 전혀 상관이 없고 그냥 '강사' 컬럼에만 상관이 있습니다. 

('강사' 컬럼에 종속되어있다고 표현함)

이 경우 '출신대학' 컬럼을 다른 테이블로 빼면 

제3정규형을 만족하는 테이블 완성입니다. 

 

 

 

 

 

[체육센터 프로그램 table]

프로그램	가격	강사
스쿼시	5000	김을용
헬스	6000	박덕팔
골프	8000	이상구
골프 중급	9000	이상구
개인피티	6000	박덕팔

 

[강사정보 table]

강사	출신대학
김을용	서울대
박덕팔	연세대
이상구	고려대

 

▲ 강사정보 table을 만들어서 거기에 출신대학 컬럼을 저장했습니다.

이러면 이제 박덕팔 강사의 학벌세탁으로 인해 출신대학이 달라져도 한 곳만 수정하면 됩니다.

그래서 결국 제3정규화까지 해놓으면 수정작업이 간편해집니다. 

 

 

 

 

 

 

 

 

제3정규형 - 어려운버전

 

여러분은 배운 사람이니까 정확한 정의도 알려드리자면

제2정규형 테이블에서 transitive dependency도 제거해버리면 그게 제3정규형입니다. 

transitive dependency는 

 

 

 

1. composite primary key 또는 primary key 역할을 하는 컬럼이 있고  

2. 거기에 직접 종속된 컬럼A가 있고 

3. 또 거기에 직접 종속된 하찮은 컬럼B가 있으면 

컬럼B가 primary key 컬럼에 transitive dependency 가 있다고 표현합니다. 

그래서 그 컬럼B를 다른 테이블로 옮기라는 소리입니다. 

옮기면 제3정규형을 만족하는 테이블이 완성됩니다. 

 

 

 

실은 제4, 5, 6 정규형도 있는데 대부분은 3까지만 하면 대부분의 중복문제는 해결할 수 있기 때문에

심심하면 찾아보도록 합시다. 

 

 

 

 

 

 

primary key, foreign key 항상 넣으면 좋음 

 

정규화하려고 테이블을 쪼갤 때가 많습니다. 그 경우 주의점은 

① 테이블마다 항상 primary key 역할을 하는 컬럼을 넣어두는게 좋습니다. 'id' 컬럼 이런거요 

② 다른 테이블에 있는 컬럼내용을 가져다쓸 때도 primary key를 사용하는게 좋습니다. 

 

 

 

 

▲ 예를 들어서 아까처럼 제3정규화 하느라 이렇게 테이블을 분리해놨다고 칩시다.

이 테이블을 쓸만하게 고쳐보면 

 

 

 

 

 

① 테이블마다 primary key 역할을 할 수 있는 id 컬럼같은거 하나 만들어두면 좋습니다.

그래서 만들어봤습니다. 

에구 보기좋다 

 

Q. composite primary key가 이미 있는데요?

A. 그거써도 되는데 id 컬럼 만들어두면 나중에 SELECT로 원하는 행만 찾을 때 편리합니다. 

 

 

 

 

 

 

 

② 다른 테이블에 있는 컬럼내용을 가져다쓸 때도 primary key를 사용하는게 좋습니다.

지금 왼쪽 테이블보면 '강사' 컬럼이 있습니다.

거기에 강사 이름이 들어가야하는데 강사 정보는 오른쪽 테이블에 들어있군요.

그럴 경우 강사 이름을 그대로 적는것 보다는 강사의 id를 적는게 좋습니다.

이러면 나중에 동명이인 강사가 생겨도 명확히 구분할 수 있으니까요.

 

저기서 강사id 컬럼을 전문용어로 foreign key 라고 합니다.

어려운건 아니고 다른 테이블에 있던 primary key를 다른 테이블에 사용할 때는 foreign key라고 부릅니다. 

참고로 알아둡시다. 

 

 

 

 

 

 

Foreign key 등록하는 법

 

실제 컬럼에다가 "이 컬럼은 foreign key 역할이다"라고 등록할 수도 있습니다. 

그럼 장점이 몇개 있어서 그거 하는 법을 알아봅시다. 

 

 

 

 

빨리 DBeaver 같은거 켜서

1. 새로운 Database 만들고

2. 그 안에 table 저렇게 2개 만들어오십시오. 

3. 데이터도 다 집어넣고요. 

 

 

CREATE TABLE program (
  id INT PRIMARY KEY,
  프로그램 VARCHAR(100),
  가격 INT,
  강사id INT
); 

CREATE TABLE teacher (
  id INT PRIMARY KEY,
  강사 VARCHAR(100),
  출신대학 VARCHAR(100)
); 

테이블 생성이 귀찮으면 이런 DDL 문법 실행하면 생성됩니다. 

이제 데이터도 사진과 동일하게 채웁시다. 

 

 

 

DBeaver에서 foreign key 등록하려면

 

 

 

▲ 1. 테이블마다 Foreign Keys 같은 메뉴가 있습니다.

그래서 program 테이블 들어가서 "새로운 foreign key 만들기" 같은게 있으면 눌러봅시다. 

 

 

 

▲ 2. Reference table 메뉴에선 현재 테이블에서 어떤 다른 테이블의 primary key를 참조할건지 선택하면 됩니다.

Column 메뉴에선 foreign key 제약 달아줄 program 테이블의 컬럼 선택하면 되고 

Ref Column 메뉴에선 참조당할 teacher 테이블의 컬럼을 선택하면 됩니다. 

그리고 저장버튼 누르면 끝입니다. 

 

 

 

 

▲ 3. program 테이블 가보면 뭔가 변해있습니다. 

foreign key 컬럼에 화살표가 생기는데 눌러보면 연결된 다른 테이블과 행을 보여줍니다.

그래서 나중에 강사id가 1인 사람이 누군지 궁금하면 클릭만 해보면 되는 것임 

그래서 foreign key 제약조건을 등록해두면 저런게 편리해집니다.

 

 

 

 

다른 장점도 있는데 

teacher 테이블로 이동해서 강사 한명을 삭제 & 저장해봅시다.

그럼 에러가 나면서 삭제를 막아줍니다. 

왜냐면 "다른 테이블에서 foreign key로 사용중인 데이터이기 때문에 함부로 삭제하지 말라"는 뜻입니다. 

이런 실수를 방지할 수 있어서 foreign key로 등록해놓는 것임 

하지만 삭제하나 할 때마다 귀찮아지고 나중에 테이블 구조변경도 귀찮아져서 그냥 foreign key 등록 안하는 곳들도 있습니다. 

 

 

[collapse]

SQL 코드로 foreign key 등록하려면

 

 

CREATE TABLE 테이블명 (
    id INT PRIMARY KEY,
    프로그램 VARCHAR(100),
    강사id INT REFERENCES 다른테이블(다른테이블컬럼)
)

컬럼만들 때 REFERENCES 다른테이블명(컬럼명) 잘 입력해주면 됩니다. 

 

CREATE TABLE 테이블명 (
    id INT,
    프로그램 VARCHAR(100),
    강사id INT,
    CONSTRAINT 제약조건작명 PRIMARY KEY (id), 
    CONSTRAINT 제약조건작명2 FOREIGN KEY (강사id) REFERENCES 다른테이블(다른테이블컬럼),
)

아니면 가장 하단에 CONSTRAINT 문법써도 된다고 했으니 그거 써도 됩니다. 

 

 

ALTER TABLE 테이블명 ADD 
CONSTRAINT 제약조건작명 FOREIGN KEY (강사id) REFERENCES teacher(id) 

이미 생성된 테이블을 수정하고 싶으면 ALTER TABLE 테이블명 ADD 입력하고

뒤에 CONSTRAINT 문법 쓰면 됩니다. 

그래서 위처럼 작성하면 이미 존재하는 테이블 컬럼에 foreign key 제약걸기 가능 

 

[collapse]

 

 

 

 

 

 

오늘의 숙제 :

 

 

▲ 방금 대충만든 쇼핑몰의 구매내역 테이블입니다. 

여러분이 알아서 테이블을 나눠서 제2, 제3 정규화를 해봅시다. 

- DBeaver에서 직접 테이블 만들어보거나 귀찮으면 종이에 그려봅시다 

- '상품카테고리'는 항상 '상품명'에 의해 결정됩니다. 

- '무료배송여부'는 항상 '상품카테고리'에 따라서 결정됩니다. 

- '가격'은 상품 1개의 가격임 

 

해보고 누르쇼 제발

 

 

전 이렇게 정리해봤습니다. 

- 구매내역 table에선 (구매자, 상품명, 수량, 날짜) 이런걸 합해야 composite primary key가 될 것 같군요. 

그래서 그것들 중 1개에만 종속된 컬럼은 다른 테이블로 뺐습니다. 

회원이름, 상품카테고리, 가격 이런 것들이요 

 

- 무료배송여부 컬럼은 무료배송여부 -> 상품카테고리 -> 상품명 이렇게 종속되기 때문에 

제3정규화를 하고 싶으면 다른 테이블로 빼면 됩니다. 

 

- F_key는 foreign key 역할 컬럼이라 작명할 때 넣어봤는데

실제 컬럼명 작명할 땐 F_key같은 단어는 귀찮으니 안넣어도 됩니다.