DB 설계 PART.정규화

서론

※ 이 포스트는 다음 강의의 학습이 목표임을 밝힙니다.

https://www.youtube.com/playlist?list=PL9hiYwOHVUQduJN7Pf_kOR8htpJU7K1H8 

2020 데이터베이스

 

본론

정규화란 ERD에서 중복 요소를 찾아 제거해 나가는 과정입니다. 제 1, 2, 3차 정규화 정도까지만 알면 됩니다.

 

1. 1차 정규화

1차 정규화는 속성의 데이터는 단일 값을 가져함을 의미합니다. 그렇다고 몇 개인지도 모를 취미의 개수를 여러 컬럼으로 만드는 것도 안됩니다.

 

이럴 땐 행으로 늘립니다. 행으로 늘리면 사원 번호가 중복 될 수 있기에 사원번호와 취미를 pk로 갖는 테이블을 만들어야 합니다. 이 모양은 M:N 관계에서 많이 본 매핑 테이블의 형태입니다. 

 

따라서 사원 테이블과 취미 테이블이 양쪽에 있을 수 있습니다. 

 

이는 수강 과목의 모습과 유사합니다. 과목과 취미는 과목명이나 취미 이름 외에 다른 속성이 없습니다.

 

정리를 해보면 양쪽에 M:N 관계에서 양쪽에 속성이 다양하면 중앙에 맵핑 테이블이 생기고 한쪽이라도 컬럼이 맵핑 테이블에 들어갈 컬럼이 전부라면 한쪽이 맵핑 테이블이 됩니다. 이렇게 같은 속성이 반복될 때 바꾸는 것을 제 1정규화라고 합니다.

 

만약 양쪽에 테이블이 없는 오직 1개의 테이블에서 제 1정규화를 해야하는 순간이라면 이렇게 오직 1개의 테이블로 점심과 저녁 금액을 열이 아닌 행을 추가하여 나눌 수 있습니다.

 

2. 제 2 정규화

2차 정규화는 종속관계를 제거하는 것입니다. 판매부서와 부서명는 판매 부서가 정해지면 부서명은 저절로 정해집니다. 이러한 관계가 완전 종속 관계로 부서명 속성이 판매 부서 속성에 종속되어 있습니다. 이때는 부서명이 판매 테이블에 있을 필요가 없으므로 따로 빼야합니다.

 

EX) 예시

학생 테이블을 만듭니다. 학과코드와 (학과명, 학과장)은 학과명이 학과 코드에 종속되어 있습니다.

 

그러면 학생 테이블 밖에 학과 테이블을 별도로 만들고 학생 테이블의 학과 코드를 참조하여 학과명, 학과장 속성을 제거해야 하는 것입니다.

 

이런 종속관계를 제거하는 것은 2가지 정규화가 있습니다. 부서명이 참조하는 판매부서는 판매 테이블의 기본키로 종속되는 속성이 pk일 때 종속하는 속성을 제거하는 것이 제 2정규화입니다.

 

3. 제 3 정규화

종속되는 속성이 기본키가 아닐 때 종속하는 속성을 제거하는 것이 제 3정규화입니다.

 

사원명은 사원번호에 종속이고 고객명은 고객번호에 종속, 제품명은 제품번호에 종속입니다. 따라서 판매 테이블에는 판매 사원번호, 고객번호만 있으면 되고 사원명, 고객 번호는 별도의 테이블로 빼야합니다. 

둘 다 빼면 원래 있던 테이블이 N, 새로 생긴 테이블이 1입니다. 또한 이 때 사원번호는 pk가 아니라서 제 3정규화이고 부서번호는 pk라 제 2정규화이며 제 3정규화를 하면 실선이 생기고 제 2 정규화를 하면 원래 있던 테이블의 pk가 별도로 생긴 테이블을 참조하는 fk가 되어 실선이 됩니다.

 

※ 정규화 엔터티 분리

이렇게 제 1, 2, 3 정규화를 함으로써 엔터티를 분리했고 중복 정보를 없앨 수 있었습니다. 하지만 애초에 판매 정보와 제품 번호는 시작부터 테이블이 따로 있는 것을 당연하게 생각할 수 있습니다. 당연하게 생각한대로 엔터티 설계를 했는데도 중복이 발생한다면 정규화를 진행해야 합니다.

 

또한 이론적으로는 합쳐진 테이블에서 정규화를 진행하며 분리하는 것 같은데 실제로 설계해보면 제품과 판매, 고객과 판매 등 각각 관계를 생각하기에 설계를 하고 나면 정규화가 되어있는 경우가 대부분입니다.

 

-> 개선

1단계

이를 더 개선해보겠습니다. 고객, 제품이라는 일반적인 관계 틀을 만들고 판매 맵핑 테이블을 만듭니다. 전혀 관계가 없던 고객과 제품이 판매를 하여 관계가 생깁니다.

 

2단계

1단계에서 제 2, 3정규화를 합니다. 판매 테이블에 중복되었던 데이터들이 분리됩니다.

최종단계

이를 더 개선하면 제품과 판매는 고객과 제품의 주문 내역처럼 판매 내역 맵핑 테이블을 가집니다. 수량이 제품에 있는 것은 사람 입장으로 제품은 엔터티 객체로 보았을 때 수량을 가지지 않습니다. 판매와 제품의 관계를 보아서 M:N인 것을 확인하고 맵핑 테이블을 만들어줍니다.

 

- 실습

1번) 

수강과목 중복을 제 1 정규화로 없애면 동일한 학번이 여러개 있을 수 있으니 수강과목도 pk로 해야합니다.

 

2번)

동일하게 제 1 정규화를 하면 학번이 여러개 있을 수 있습니다. 이때 수강과목, 명, 학점 중 수강과목 코드를 pk로 해야합니다.

 

버전 1) 수강학점이 '과목이 몇 학점인지'를 나타낼 경우

학생의 수강과목에서 학번에 종속되는 이름을 빼서 학생 테이블로 분리하고 과목 코드에 종속되는 과목명과 과목 학점을 빼서 과목 테이블로 분리합니다. 결과를 보면 전혀 관계가 없던 학생과 과목이 수강을 하므로써 수강이라는 관계가 생기고 M:N의 관계에 맵핑 테이블 역할을 합니다.

이전에 과목 테이블에 과목명만 있을 때는 과목 테이블이 맵핑 테이블을 했는데 이번에는 여러 속성이 있으니 중간에 맵핑 테이블이 별도로 필요합니다.

 

버전 2) 수강학점이 '학생의 수강 학점'을 나타낼 경우

학생과 과목 테이블의 컬럼만 다를 뿐 같은 맵핑 테이블 결과가 나옵니다. 역시 처음 설계를 할 때 모아 두고 정규화로 분리하는 것보다 각각 M:N 관계로 보는게 더 자연스럽습니다.

 

4번)

위 문제에 전공코드와 전공명을 추가합니다. 그러면 전공과 학생의 관계를 생각하면 됩니다. 전공과 학생의 관계는 한 전공에 여러 학생이 있는 것입니다. 전공이 부모의 관계를 가지게 됩니다. 정규화의 법칙에 얽매일 필요없이 상식적으로 푸는 것이 더 자연스럽고 풀다보면 정규화가 되어있을 수 있고 그럼에도 남아있는 중복은 정규화로 제거합니다.

 

5번)

견적서 DB를 만듭니다. 이렇게 복잡해 보이는 관계는 테이블을 잡고 pk잡고 관계만 잡으면 끝난다고 생각하면 쉽습니다. 여기서는 공급자와 견적서, 품목 3 테이블이 필요합니다.

 

공급자와 견적서는 한 명의 공급자가 여러개의 견적서를 작성합니다. 하나의 견적서는 무조건 한명의 공급자가 작성합니다. 따라서 공급자가 1, 견적서가 N입니다. 견적서와 품목은 M:N으로 속하지 않는 관계입니다. 따라서 맵핑 테이블을 만듭니다. 이렇게 하면 90% 완성된 것으로 남은 속성을 넣고 정규화를 생각하면 됩니다. 

공급자를 보면 견적서에 상호, 종목, 전화번호 등이 있어야할 것 같은데 그런 건 다 공급자가 가지고 견적서에는 공급자 등록번호를 하나 가지고 있으면 됩니다. 이 또한 사람의 입장이 아닌 시스템의 입장으로 봐야하고 테이블 각각을 엔터티라고 보면 당연히 공급자에 있어야합니다.

+ 수량은 품목에 들어가는게 아닙니다. 잘 생각해보면 품목에는 수량이 속하지 않습니다. 사람 입장에서 봤을 때 '품목이 3개다'라고 하는 것이지 시스템 입장에서는 수량은 견적서_품목에 들어가야합니다. 

 

- 실습

1번)

회원과 상품의 주문 관계를 구합니다.

 

1단계

회원, 상품, 주문 테이블을 가집니다. 상품은 책과 음반은 나눌 수 있는데 겹치는 게 많으면 합치는게 좋고 다르면 나누는게 좋습니다. 회원과 주문은 1:N, 주문과 상품은 M:N 관계를 가집니다.

2단계

회원과 주문이 주문 내역에 해당하고 주문과 상품은 중간에 맵핑 테이블을 가지는데 이때 맵핑 테이블의 목적을 알아야합니다. 매핑 테이블은 업체가 어떤 제품을 생산하는지 사원이 어떤 취미를 가지는 지 등 단순히 리스트만 나열하는 것이 목적입니다. 따라서 중요한 것은 주문(주문 내역)에 넣고 수량, 금액 등만 매핑 테이블에 넣습니다.

 

2번)

회원과 주문처럼 중간에 주문 내역을 둘 필요없는 M:N 관계입니다.

 

만약 어플리케이션에서 분류로 카테고리를 먼저 나누고 나뉜 카테고리에서 동아리를 선택하게 하려면 분류를 별도의 테이블로 빼면 됩니다. 위의 버전은 동아리가 먼저 나오고 그 동아리가 어떤 분류인지 보여주는 순서이고 일반적인 방법은 카테고리를 먼저 보여주고 동아리를 보여주니 이렇게 적용합니다. 보통 하나의 동아리는 하나의 분류를 가지므로 1:N 포함 관계입니다. 영화 장르의 경우 하나의 영화가 여러 장르를 가지고 장르가 여러 영화를 가져서 M:N입니다.

 

6번)

고객이 물품을 사는 문제로 ERD는 회원이 상품을 주문하는 것과 비슷합니다. 고객이 주문 목록을 확인하는 것은 테이블들을 조인해서 조회하면 됩니다.

 

7번)

인수증에 상호 소속이 들어있을 거라고 생각할 수 있는데 인수자에 들어가고 인수증에는 인수자의 번호가 들어있어 인수자 정보를 접근합니다.