😀저번 정규화와 비정규화 포스팅에서는 둘 간의 특징과 비교점에 대해서 포스팅했었다.
이번 포스팅에서는 정규화에 대해서 좀 더 자세하게 알아보자.
정규화란 저번 포스팅에도 기록되 있는 것처럼 데이터베이스의 정규화는 데이터의 중복을 줄이고, 무결성을 지키기 위해 사용되는 데이터베이스 구성 방법이다.
관계형 데이터베이스의 설계에서 중복을 최소화 하기 위한 작업이며 릴레이션을 분리함으로써 데이터의 삽입, 삭제, 갱신에서의 이상현상 발생을 줄여 해결하기 위한 과정이다.
정규화는 구성 방법이기 때문에 해당 방법을 진행하는 규칙같은 것들이 존재한다.
이를 정규형이라고 한다.
정규형의 종류
-
일반적으로 실무에서 주로 사용되는 정규형
1정규형, 2정규형, 3정규형, BCNF정규형, 4정규형, 5정규형 -
좀 더 고차원적인 연구목적의 정규형
6정규형, DKNF정규형 등
정규형에서 용어 정리
| 학번 | 이름 | 학과 | 학년 | 성별 |
|---|---|---|---|---|
| 20230001 | 권** | 컴퓨터공학과 | 4 | 남 |
| 20230002 | 이** | 철도신호시스템학과 | 1 | 남 |
| 20230003 | 서** | 건축공학과 | 3 | 여 |
-
함수적 종속성(Functional Dependency)
한 속성 또는 속성 집합의 값이 주어지면 다른 속성 또는 속성 집합의 값이 유일하게 결정되는 제약 조건을 의미한다. -
결정자(Determinant)
결정자는 함수적 종속성에서 왼쪽에 있는 속성(Attribute)이나 속성들의 집합을 말한다.
즉, A->B에서 A가 결정자가 되며, B가 이에 종속되는 속성이 된다.
속성들 간의 함수적 종속성에서 어떤 속성이 다른 속성을 결정하고 있는지를 나타내는 용어이다.
위 예시에서 학번의 값을 알면 다른 속성을 결정할 수 있기 때문에 결정자가 될 수 있다.
- 후보키(Candidate Key)
테이블에서 ✔각 로우를 유일하게 식별할 수 있는 속성들의 집합을 말한다.
특히 기본키(Primary Key)로 지정되지 않은 속성 집합들을 후보키라고 하는데, 후보키들은 줄여서 CK(candidate keys)로 표기하기도 한다.
위 예시에서 학번이 단독으로 함께 있어도 모든 칼럼을 참조할 수 있기 때문에 학번은 후보키가 될 수 있다.
각 정규형의 규칙
제1정규형(1NF)
- 1정규형의 규칙은
모든 속성은 반드시 하나의 원자값만 가져야 한다.
아래의 표로 예시를 들어보자. 여러가지 원자값을 가지게 되어 1정규형을 위반한 경우의 테이블을 구현해 보았다.
| id(PK) | 이름 | 이메일 | 전화번호 |
|---|---|---|---|
| 1 | 권** | KwonTaeHyeong12@velog.com | 010-1234-5678 |
| 2 | 이** | LeeJS@velog.com | 010-4321-4321, 010-4141-5883 |
| 3 | 서** | mimiS2@velog.com | 010-2234-7575 |
위의 테이블에서 이**은 2개의 전화번호를 가지고 있다. 따로 객체하나로 묶거나, 문자열 하나로 묶거나, 배열 하나로 묶인 프로퍼티를 가진게 아니라 2개의 전화번호를 가지고 있기 때문에 다중값을 가진 이 경우는 1정규형에 위반되는 사항이다. 이러한 사항의 경우 테이블을 분리해주고 관계를 설정해 주어야한다.
| id(PK) | 이름 | 이메일 |
|---|---|---|
| 1 | 권** | KwonTaeHyeong12@velog.com |
| 2 | 이** | LeeJS@velog.com |
| 3 | 서** | mimiS2@velog.com |
--
| id(Pk) | memberId(Fk) | 전화번호 |
|---|---|---|
| 1 | 1 | 010-1234-5678 |
| 2 | 2 | 010-4321-432 |
| 3 | 2 | 010-4141-5883 |
| 4 | 3 | 010-2234-7575 |
하나 더 예시를 들어보자.
| id(PK) | 이름 | 이메일 | 전화번호 | 사용 프로그래밍 언어1 | 사용 프로그래밍 언어2 | 사용 프로그래밍 언어3 |
|---|---|---|---|---|---|---|
| 1 | 권** | KwonTaeHyeong12@velog.com | 010-1234-5678 | javascript | typescript | python |
위의 경우 권**은 사용 프로그래밍 언어 1, 2, 3을 각각 가지고 있다. 언뜻 보면 1정규형의 정의만으로는 위반한 것 같아 보이지 않는다. 왜냐 모든 속성은 하나의 프로퍼티만 가지고 있기 때문이다. 하지만 이 경우 또한 1정규형을 위반한 경우이다.
위 경우 사용 프로그래밍 언어 1, 2, 3이라는 반복그룹을 가지고 있기 때문에 이는 하나의 그룹과 마찬가지의 기능을 가지고 하나의 속성에 여러 값을 가지고 있는 것으로 볼 수 있다.
| id(PK) | 이름 | 이메일 | 전화번호 |
|---|---|---|---|
| 1 | 권** | KwonTaeHyeong12@velog.com | 010-1234-5678 |
-- 따라서 위와 아래의 두개의 테이블로 나눠서 설계해야한다.
| menberId(FK) | 사용 프로그래밍 언어 |
|---|---|
| 1 | javascript |
| 1 | typescript |
| 1 | python |
내가 설계한 테이블이 제1정규형을 위반하는지 확인하는 것은 그냥 하나의 속성에 다중값을 가지는지 or 반복그룹을 가지는 속성이 생기는지만 확인하면 되서 간단하다.
제2정규형(2NF)
- 2정규형의 규칙은
"부분적 함수 종속 제거"라고 부르는데, 쉽게 말해서 모든 속성이 반드시 모든 기본키에 종속되어 있어야 한다는 말이다.
일반적으로 N:M관계 사이의 중간테이블에서 발생할 수 있다. 말로 설명하면 애매하니 예시를 들어보자.
- 주문정보(order) 테이블
| memberId(Pk,Fk) | goodsId(Pk,fk) | 주문일자 | 상품명 | 주문수량 |
|---|---|---|---|---|
| 1 | 3 | 23-06-22 | 제품3 | 2 |
| 1 | 1 | 23-06-22 | 제품1 | 12 |
| 2 | 2 | 23-06-25 | 제품2 | 10 |
위 경우 memberId goodsId 두개의 key를 복합적으로 PK로 사용하고 있다. 이러한 복합키를 가진경우에 그 외의 다른 속성 또한 양쪽에 모두 해당하여햐 한다. 하지만, 주문일자나 주문수량은 PK에 종속적이나, 상품명 같은 경우는 memberId에는 종속되지 않고, goodId에만 종속된다. goodsId만 봐도 상품명을 알 수 있기 때문이다. 따라서 위 테이블은 제2정규형을 위반한다.
| memberId(Pk,Fk) | goodsId(Pk,fk) | 주문일자 | 주문수량 |
|---|---|---|---|
| 1 | 3 | 23-06-22 | 2 |
| 1 | 1 | 23-06-22 | 12 |
| 2 | 2 | 23-06-25 | 10 |
--위 테이블과 아래 테이블로 나눌 수 있다.
| goodsId(Pk) | 상품명 |
|---|---|
| 1 | 제품1 |
| 2 | 제품2 |
| 3 | 제품3 |
내가 설계한 테이블이 제2정규형을 위반하는지 확인하기 위해서는 일반적인 기본키(id[PK])를 제외하고 복합키가 될 수 있는 컬럼이 존재하는지 확인하고, 만약 있다면 양쪽의 복합키에 다른 속성들이 속하는지 확인해 볼 필요가 있다.
- 제2정규형부터 뒤의 정규형들은 자기자신보다 앞선 정규형을 모두 이미 만족한다는 전제로 한다.
ex) 2정규형의 규칙은 1정규형을 만족하고 모든 속성이 반드시 모든 기본키에 종속되어 있어야 한다.
제3정규형(3NF)
- 3정규형의 규칙은
"이행적 함수 종속 제거"라고 부르는데, 기본키를 제외하고 나머지 속성들이 서로 종속이 될 수 없다.
| goodsId(Pk) | 상품명 | 구매처 | 최소구매수량(마트별) |
|---|---|---|---|
| 1 | 제품1 | k마트 | 20 |
| 2 | 제품2 | t마트 | 30 |
| 3 | 제품3 | h마트 | 20 |
| 4 | 제품4 | k마트 | 20 |
| 상품=>구매처 | 구매처=>최소수량 | 상품=>최소수량 |
위와 같은 경우 기본키를 제외한 상품명, 구매처, 마트별 최소구매수량이 있다. 문제는 구매처, 마트별 최소구매수량의 관계이다. 제품별 최소구매수량이 아니라, 마트별 구매수량이라 해깔릴 수 있는데, 각 마트에 고정적인 최소 구매수량이 정해져 있기 때문에, 제품1을 k마트에서 산다면 최소 20개를 구매해야한다. 제품4또한 k마트에서 구매한다면 최소 20개를 구매해야한다. 어떤제품이든 k마트는 최소20개의 구매를 가진다. 따라서 마트별 최소수량은 구매처에 종속되는 관계가 되어 제3정규형을 위반하는 경우가 된다.
| goodsId(Pk) | 상품명 | 구매처 |
|---|---|---|
| 1 | 제품1 | k마트 |
| 2 | 제품2 | t마트 |
| 3 | 제품3 | h마트 |
| 4 | 제품4 | k마트 |
-- 따라사 위와 아래로 테이블을 나누어야 한다.
| 구매처 | 최소구매수량(마트별) |
|---|---|
| k마트 | 20 |
| t마트 | 30 |
| h마트 | 20 |
제3 정규형의 경우 기본키가 아닌 다른 속성에서 A => B, B => C 임으로 A => C 이다. 와 같은 3단 논법이 적용되면 위의 제3정규형을 위반한 경우가 된다고 생각할 수 있다.
Boyce-Codd정규형(BCNF)
🙄규칙전에 의미를 알아보자 왜 BC가 Boyce-Codd인가?
Boyce와 Codd는 둘 다 사람 이름에서 따온 키워드 이다. Boyce'는 BCNF를 만족시키기 위한 함수 종속성(FD, Functional Dependency)에서 X->Y 일 때, X가 슈퍼키(superkey)인 경우, 이를 만족시키는 것을 발견하고 제안한 Boyce와 그의 동료인 Codd에 의해 지어진 용어로, 관계형 데이터베이스 모델을 고안한 컴퓨터 과학자들의 이름은 따온 정규형 방식이다.
- Boyce-Codd정규형의 규칙은
"결정자가 후보키가 아닌 함수 종속 제거"이다. 테이블 내에서 후보키와 결정자를 파악해서 결정자가 후보키가 될 수 없다면 분리 해야한다.
| 학번 | 과목 | 교수 |
|---|---|---|
| 001 | 프로그래밍 | A |
| 001 | 운영체제 | B |
| 002 | 프로그래밍 | A |
| 002 | 데이터베이스 | A |
| 003 | 네트워크 | C |
| 003 | 프로그래밍 | D |
위 테이블은 조건이 따로 없다면 BC정규형을 만족할 수 있다. 일단 후보키를 찾아보자
학번 => 학번만 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
과목 => 과목만 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
교수 => 교수만 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
(학번, 과목) => 학번+과목을 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ⭕
(과목, 교수) => 과목+교수를 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
(학번, 교수) => 학번+교수를 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
아무 조건이 없는 경우 후보키는 (학번, 과목) 만 될 수 있다.
결정자를 찾아보자. 속성별의 관계에서 A=>B가 만족할 때 A가 결정자가 된다.
학번 => 과목 ❌ 학번이 같아도 과목이 달라질 수 있다.
과목 => 학번 ❌ 과목이 같아도 수강학생 학번은 달라질 수 있다.
과목 => 교수 ❌ 과목이 같아도 교수가 달라질 수 있다.
교수 => 과목 ❌ 교수가 같아도 과목은 달라질 수 있다.
학번 => 교수 ❌ 학번이 같아도 교수가 달라질 수 있다.
교수 => 학번 ❌ 교수가 값아도 수강학생 학번은 달라질 수 있다.
(학번,과목) => 교수 ⭕ 수강학생 학번+과목이 같으면 교수는 고정된다.
(과목,교수) => 학번 ❌ 과목+교수가 같아도 수강학생 학번은 달라질 수 있다.
(학번,교수) => 과목 ❌ 학번+교수가 같아도 과목은 달라질 수 있다.
따라서 후보키는 (학번,과목)만 가능하고 결정자 또한 (학번,과목)이기 때문에 결정자가 후보키가 아닌 함수 종속이 존재하지 않아서 BCNF정규형을 만족한다.
🙄그렇다면 만족하지 않는 경우는 어떻까?
| 학번 | 과목 | 교수 |
|---|---|---|
| 001 | 프로그래밍 | A |
| 001 | 운영체제 | B |
| 002 | 프로그래밍 | A |
| 002 | 네트워크 | C |
| 003 | 네트워크 | C |
| 003 | 프로그래밍 | D |
비슷한 테이블이나 한가지 조건이 추가된다.
- 위 테이블에서 교수는 반드시 하나의 과목만을 가르칠 수 있다.
위의 조건을 가지고 다시 후보키부터 찾아보자.
학번 => 학번만 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
과목 => 과목만 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
교수 => 교수만 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
(학번, 과목) => 학번+과목을 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ⭕
(과목, 교수) => 과목+교수를 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ❌
(학번, 교수) => 학번+교수를 가지고 한 줄의 데이터를 모두 식별할 수 있는가? ⭕
이번 테이블의 후보키는 (학번, 과목), (학번, 교수) 두 개이다.
학번 => 과목 ❌ 학번이 같아도 과목이 달라질 수 있다. / 과목 => 학번 ❌ 과목이 같아도 수강학생 학번은 달라질 수 있다.
과목 => 교수 ❌ 과목이 같아도 교수가 달라질 수 있다. / 교수 => 과목 ⭕ 교수가 같으면 과목은 고정적 이다.
학번 => 교수 ❌ 학번이 같아도 교수가 달라질 수 있다. / 교수 => 학번 ❌ 교수가 값아도 수강학생 학번은 달라질 수 있다.
(학번,과목) => 교수 ⭕ 수강학생 학번+과목이 같으면 교수는 고정된다.
(과목,교수) => 학번 ❌ 과목+교수가 같아도 수강학생 학번은 달라질 수 있다.
(학번,교수) => 과목 ❌ 학번+교수가 같으면 교수는 고정된다. 하지만 2NF를 위배하게 된다.
따라서 결정자는 교수, (학번,과목) 두 가지가 된다.
후보키는 (학번, 과목), (학번, 교수) 이고, 결정자는 교수, (학번,과목)
결정자 중에 후보키가 될 수 없는 항목인 교수가 존재함으로 이는 BCNF정규형을 위반하게 된다.
| 학번 | 교수 |
|---|---|
| 001 | A |
| 001 | B |
| 002 | A |
| 002 | C |
| 003 | C |
| 003 | D |
-- 예시의 테이블은 위, 아래의 두 테이블로 릴레이션을 분할해 줘야 BCNF정규형을 만족하게 된다.
| 교수 | 과목 |
|---|---|
| A | 프로그래밍 |
| B | 운영체제 |
| C | 네트워크 |
| D | 프로그래밍 |
참고자료(출처)
기사퍼스트 권우석 유튜브 동영상 정보처리산업기사 필기1과목.데이터베이스제3정규형, BCNF, 제4정규형, 제5정규형
Rebro의 코딩 일기장 [DB] 8. 정규형 (1NF, 2NF, 3NF, BCNF)
박연호의 개발 블로그 [데이터베이스] 정규화(Normalization)
DBA 커뮤니티 구루비 관계형 데이터 모델링 프리미엄 가이드 DB구축 (2017년)
