[데이터베이스] 26. 정규화 (1NF ~ 5NF)

정규화는 테이블에 여러 정보가 중복해서 저장될 때 발생하는 문제를 해결하기 위해 테이블을 바꾸는 것을 말한다.

여러 정보가 하나의 테이블에 중복해서 (이때의 중복은 정말 동일한 데이터가 여러개 들어있는 중복만을 말하지는 않는다.) 들어있는 경우 다음과 같은 문제가 발생할 수 있다.

 

1. 중복 저장 (redundant store)

동일한 데이터가 여러 번 저장되는 것

 

2. 갱신 이상

데이터를 수정할 때 중복 저장된 데이터 중 일부만 수정하는 것

 

3. 삽입 이상

데이터를 삽입할 때, 이 데이터의 삽입을 위해 상관없는 다른 데이터도 함께 삽입되는 것

 

4. 삭제 이상

데이터를 삭제할 때, 이 데이터의 삭제를 위해 상관없는 다른 데이터도 함께 삭제되는 것

 

정리하면

 

1번은 데이터가 중복저장되는 문제

2번은 1번으로 인해 수정할 때 일관성이 깨질 위험이 존재하는 문제

3번, 4번은 삽입 삭제시 다른 데이터에도 영향을 주는 문제

 

가 있기 때문에 정규화를 통해 이런 문제가 발생하지 않도록 테이블을 수정한다.

정규화는 테이블을 특정한 정규형을 만족하도록 여러 단계를 거친다.

이 단계는 1NF, 2NF, 3NF, BCNF, 4NF, 5NF 가 있다.

 

 

1NF

first normar form, 제 1 정규형은 하나의 attribute에 하나의 데이터만 들어가면 만족한다.

즉, 레코드가 atomic 하면 된다.

 

 

예를 들어, 위와 같은 테이블이 있다고 해보자.

현재 CID attirubte 에는 어떤 레코드는 2개의 값, 어떤 레코드는 3개의 값이 저장되어있다.

이렇게 저장되어있으면 CID 필드의 값을 읽었을 때, 이 값에 들어있는 의미있는 데이터의 개수를 알 수 없으므로 직접 파싱해야 한다.

 

(그렇다면 모든 레코드가 CID 필드에 동일하게 2개식 들어있다면 어떨까? 모두 같은 규격을 갖추었으니 이때도 1NF를 만족할까?

그때도 CID에는 CE101 이라는 하나의 의미를 갖는 데이터와 CE202라는 하나의 의미를 갖는 데이터가 2개 들어있으니 atomic 하지 않은 것으로 보는 게 맞는 것 같다.)

 

 

그래서 이렇게 데이터를 쪼개서 별개 레코드에 저장하면 1NF를 만족한다.

 

2NF

second normar form (제 2 정규형)을 만족하려면 아래 2가지 조건을 만족해야 한다.

 

1. 1NF를 만족한다.

2. primary key에 대해 partial FD 가 존재하지 않는다.

 

FD에 대해 정리하기 전에 먼저 1NF를 만족했던 위 테이블을 보면서 어떤 문제점이 남아있는지 생각해보자.

 

1NF는 글 서두에서 정리한 4가지 문제 중 1번, 중복 문제를 해결하려는 시도였다.

중복 저장이라는 뜻은, 똑같은 데이터를 여러번 저장한다(duplicate)는 의미 외에도, 같은 도메인에 속하는 데이터를 하나의 공간에 몰아서 저장하는 것(redundant)을 의미하기도 한다.

 

1NF를 만족하는 테이블은 이제 명시적으로 중복된 데이터는 존재하지 않는다.

하지만 아직 2~4번 문제가 남아있다.

 

위의 1NF가 만족된 테이블을 보면, 테이블 하나에 학생과 학생이 수강하는 수업 정보가 모두 들어있음을 알 수 있다.

이때 만약 새로운 학생 또는 수업을 삽입하려고 한다면, 각각 수업 / 학생 쪽 데이터는 채울 수 없으므로 null 을 채워두어야 한다.

이것이 바로 삽입 이상이다.

 

삽입은 그래도 null로 채워서 삽입하면 되므로 어떻게 해결은 되지만, 삭제의 경우 문제가 더 심각하다.

위 테이블에서 B 학생이 M3303 수업을 듣고 있었는데, B 학생이 자퇴를 했다고 해보자.

그러면 B학생 관련 정보를 모두 지울 때 M3303 이라는 수업 데이터도 사라지면서, 학생 데이터 뿐만 아니라 수업 데이터도 함께 삭제되는 문제가 발생한다.

이것이 삭제 이상이다.

 

수정의 경우에도 문제가 발생할 수 있따.

만약 CE101 수업의 이름을 C1에서 Class 1 로 바꾼다고 해보자.

그러면 현재 테이블에 CE101 수업이 2개가 존재하므로 두 레코드 모두 TITLE 필드 값을 Class 1로 변경해야 한다.

하지만 만약 하나라도 빠뜨린다면 CE101 수업의 이름이 서로 다르게 저장되므로, 일관성이 깨지게된다.

 

2NF 에서는 이 문제를 해결하기 위해 partial FD가 없어야 한다는 조건을 추가했다.

그렇다면 FD는 무엇이고 partial FD일까?

 

FD (Functional Dependency)

FD(함수 종속성)는 일종의 무결성 제약 조건으로, '키'의 개념을 일반화한 것이다.

테이블에 있는 어떤 속성 값 집합 a, b 에 대해, a가 b의 값을 결정하는 경우, a → b 로 표기하며, 함수 종속성이 있다고 말한다.

 

 

 

다시 이 테이블로 돌아왔을 때, 현재 이 테이블의 PK는 밑줄이 그어져있는 (SID, CID) 이다.

이 말은 (SID, CID) 를 통해서 하나의 레코드를 유일하게 식별할 수 있음을 의미하며, 이는 곧 (SID, CID) 라는 속성 집합이, 나머지 속성들의 집합을 유일하게 결정하는 것을 말한다.

즉, (SID, CID) → (SNAME, ADDR, ..., GRADE) 이다.

위에서는 PK로 예시를 들었지만, a → b 는 a가 레코드를 식별하기만 하면 되는 관계이므로, a는 super key와 같은 의미를 갖는다.

 

이제 partial FD에 대해 생각해보면, a의 일부가 b의 일부를 결정하는 경우 partial FD가 존재한다고 말한다.

위 테이블의 예시를 보면 SID는 현재 SNAME, ADDR, MAJOR 를 각각 유일하게 식별하고, CID는 TITLE, INAME, ILOC를 유일하게 식별한다. (GRADE의 경우 CE202와 M3303 모두 B를 가리키므로 유일하게 식별하지 않는다.)

따라서 이런 경우 SID → (SNAME, ADDR, MAJOR) 과 같이 표현하는 partial FD가 존재하므로 2NF를 만족하지 않는다.

 

2NF를 해결하려면 (partial FD가 존재하지 않게 하려면) 테이블을 나누면 된다.

SID가 결정하는 집합끼리 묶고, CID가 결정하는 집합끼리 묶고, (SID, CID)가 결정하는 집합끼리 묶으면 된다.

 

 

그러면 위와 같이 테이블을 분리하여 2NF를 만족시킬 수 있다.

 

 

3NF

3NF는 다음 조건을 만족해야 한다.

 

1. 2NF를 만족한다.

2. transitive FD가 존재하지 않는다.

 

여기서 transitive FD는 a → b 일 때, b → c 이고 a → c 한 관계를 말한다.

 

 

 

그림으로는 위와 같은 관계가 그려지면 transitive FD가 존재한다고 말한다.

 

 

 

위의 2NF를 만족하도록 쪼갠 테이블 중 위 테이블을 보자.

현재 CID → TITLE, INAME, ILOC 의 함수 종속성을 갖는다.

그런데 이 관계를 살펴보면 INAME → ILOC 관계가 있음을 알 수 있다.

INAME = 교수 이름, ILOC = 교수 연구실 이므로, 한 명의 교수가 여러 연구실을 갖는 것은 이상하기 때문이다.

 

즉 a = CID, b = INAME, c = ILOC 로 대입하면 a → b 일 때, b → c 이고 a → c 인 관계가 존재하는 상황이다.

이럴 때는 INAME 과 ILOC를 분리해야 한다.

 

왜냐하면 특정 교수님의 ILOC의 값이 변화하는 경우, 이 테이블에서 해당 교수님을 찾아 ILOC 값을 수정해야 하므로 갱신 이상이 발생할 여지가 있기 때문이다.

 

 

그래서 이렇게 테이블을 분리하면 3NF를 만족하게 된다.

 

 

BCNF (3.5 NF)

BCNF (Boyce-Codd Normal Form) 은 3.5 NF로도 불린다.

BCNF는 다음과 같은 특성을 만족해야 한다.

 

1. 3NF를 만족한다.

2. 테이블에 존재하는 모든 FD f: a → b 에 대해 다음 조건 중 하나를 만족한다.

- a 는 슈퍼키이다.

- f는 trivial dependency 를 갖는다. (자명한 의존관계, a → b 에서 b가 a의 부분집합인 경우를 말한다.)

 

 

위와 같은 학생과 지도 교수 관계 테이블을 보자.

이때, sid = 1 인 학생은 컴공과 산공을 복전하기 때문에, 2명의 지도 교수가 있는 상태이다.

 

먼저 결론을 말하면 이 테이블은 학생을 삭제하면, 교수-major 정보가 함께 삭제되는 삭제 이상이 존재하는 테이블이다.

따라서 정규화가 필요한 상황이다.

 

이 테이블의 정규화 필요 여부를 위 정의에 따라서도 판별해보자.

이 테이블에는 다음과 같은 FD가 존재한다.

지도 교수는 하나의 전공에 대해 한 명만 존재하므로 (sid, major) 는 한 명의 지도교수를 결정한다.

거꾸로 한명의 교수는 하나의 전공에 속해있으므로 advisor는 major를 결정한다.

 

sid, major → advisor

advisor → major

 

이제 이 테이블이 BCNF에 속하는지 검사해보자.

먼저 FD 중에서 trivial dependency는 존재하지 않는다. (화살표 오른쪽의 속성이 화살표 왼쪽에 포함되는 경우)

그렇다면 화살표 왼쪽에 있는 부분이 각각 슈퍼키인지 따져보자.

 

먼저 (sid, major) 는 슈퍼키이다. 학생과 전공의 조합이 여러 개 등장할 수 없으므로 하나의 레코드를 식별하기 때문이다.

하지만 advisor → major 에서 advisor는 그 자체로 슈퍼키가 될 수 없다.

 

위 테이블에서는 교수가 모두 서로 다르게 나왔는데, 9번 학생의 지도교수가 '윤 영'인 경우를 생각해보면 된다.

따라서 모든 FD에 대해 위 두 가지 조건 중 하나가 만족하지 않으므로 이 테이블은 BCNF를 만족하지 않는다.

 

BCNF를 만족하는 테이블은 역시 위 조건을 만족하도록 테이블을 쪼개주면 된다.

 

 

위 그림과 같이 advisor 가 슈퍼키가 되도록, 단독 테이블을 따로 분리해주면 BCNF를 만족한다.

왼쪽 테이블에서 advisor로 키가 바뀐 이유는 왼쪽 테이블의 목적이 (학생, 지도교수) 정보를 저장하는 것이 목적이었기 때문이다.

각 지도교수가 어떤 전공에 대한 지도 교수인지는 오른쪽 테이블을 통해 확인해야 한다.

(그러면 기존 테이블에서 애초부터 sid, advisor로 PK를 잡으면 되지 않았을까?

이 경우에는 advisor가 major를 결정하는 관계 때문에 partial FD가 존재하여 2NF를 만족하지 않게 된다.)

 

수업 중에는 'non-primary key attribute 가 primary key attribute를 결정하는 상황을 방지해야 한다'고 말씀하셨다.

 

 

GPT는 위와 같은 예시를 주었다.

교수가 강의하는 강의실이 고정되어 있다고 가정할 때,

 

교수 → 강의실

강의 → 교수

 

관계가 성립한다. (데이터베이스 수업은 교수에 따라 여러 강의실에서 열릴 수 있으므로 강의 → 강의실은 없다.)

이 FD에서 trivial FD는 없으므로 슈퍼키를 따져보면

 

교수는 슈퍼키가 아니고 (교수가 여러 강의를 맡는다면 레코드가 식별되지 않음)

강의도 슈퍼키가 아니다. (하나의 강의에 대해 여러 교수가 진행하는 수업이 열릴 수 있음)

 

따라서 위 예시는 BCNF를 위반한다.

 

 

BCNF를 만족하도록 테이블을 나누면 위와 같이 나눌 수 있다.

 

 

4NF

4NF는 다음 조건을 만족해야 한다.

 

1. BCNF를 만족한다.

2. 모든 MVD a ↠ b 에 대해서, a가 슈퍼키여야 한다.

 

여기에서 MVD는 Multi Value Dependency 를 말하며, a ↠ b 는 하나의 a값이 여러개의 b 값을 결정하는 것을 말한다.

이때 a가 여러 개의 b를 결정한다는 말은, 하나의 a를 찍었다면, 그 a에 대해서 '값의 집합' 을 결정한다는 것과 같다.

(즉, FD는 a가 하나의 b를 결정하므로 MVD의 특수한 케이스이다.)

 

 

예시로 위와 같은 테이블을 보자.

먼저 이 테이블은 BCNF를 만족한다.

잠시 복습해보면 모든 레코드가 atomic 하므로 1NF를 만족하고, 세 속성의 조합이 PK 이므로 partial FD가 존재하지 않아 2NF도 만족하며, 같은 이유로 transitive FD가 존재하지 않아 3NF도 만족하고, 같은 이유로 FD가 존재하지 않으므로 3.5NF도 고려할 것이 없다.

(course, inst, text) 조합으로 결정할 다른 컬럼이 없기 때문이며, 그 내부 필드를 결정하는 것은 trivial 하므로 정규형을 만족한다.

 

이제 4NF를 판별하기 위해 MVD를 따져보자.

그러면 아래와 같은 MVD가 존재한다.

 

Course ↠ Instructor

Course ↠ Textbook

 

하나의 '데이터베이스' 라는 수업은 { 윤영, 김경창, 김은삼 } 이라는 값의 집합을 결정한다.

이 말은 어떤 교재를 고르더라도 항상 '데이터베이스' → { 윤, 김, 김 } 의 조합이 존재해야 함을 의미한다.

(따라서 새로운 교재를 추가하면 그 책에 대해 3분의 교수님을 추가해야 한다.)

 

마찬가지로 하나의 '데이터베이스' 라는 수업은 { 라마크리슈난, 실버셰츠 } 라는 값의 집합을 결정한다.

이 말은 어떤 교수를 고르더라도 항상 '데이터베이스' → { 라, 실 } 의 조합이 존재해야 함을 의미한다.

(따라서 새로운 교수님을 추가하면 해당 교수님에 대해 2권의 책 정보를 추가해야 한다.)

 

이때, 이 테이블에서 Course 는 그 자체로 슈퍼키가 될 수 없다.

Course 만으로 하나의 레코드를 유일하게 식별할 수 없기 때문이다.

따라서 위 테이블은 4NF를 만족하지 않고 있다.

 

4NF를 만족하지 않는 테이블은 다음과 같은 문제를 갖고 있다.

먼저 데이터베이스의 교재가 { 라, 실 } 에서 { 다른 책, 실 } 로 변경된다고 해보자.

그러면 위 컬럼에서 3개의 '라마크리슈난' 필드를 '다른 책' 으로 바꿔야 하므로 갱신 이상이 발생할 수 있다.

 

삽입과 삭제의 경우도 마찬가지다.

 

 

위 그림과 같이 새로운 교수님이 삽입된다고 하면, 이 테이블을 관리하는 입장에서는 이 형태만으로 데이터베이스에 MVD가 있는지 없는지 알 수 없다.

정말 우연히, 모든 교수님이 2권의 책을 가지고 수업하고 있을 뿐, 사실은 데이터베이스 수업에 1권의 책만 사용해도 될 수 있기 때문이다.

하지만 '데이터베이스는 지정된 2권의 책으로 수업해야 한다' 는 MVD 가 있었다면 위와 같은 삽입은 잘못되었다.

따라서 삽입 이상 문제를 야기한다.

 

또한 Textbook 에 의해서 instructor 가 중복되고, 다시 instructor에 의해서 textbook을 중복해서 저장하는 문제도 함께 발생하고 있다.

따라서 4NF를 만족하도록 하려면, MVD가 존재하는 관계를 서로 다른 테이블로 분리해주면 된다.

 

 

그 결과는 위와 같다.

---

 

또 다른 예시를 살펴보자.

 

 

위에서는 course 필드에 데이터베이스라는 하나의 데이터만 존재했지만, 이번엔 여러 데이터가 존재한다.

먼저 위 테이블에는 다음과 같은 MVD가 존재한다.

 

Restaurant ↠ Pizza Variety

Restaurant ↠ Delivery Area

 

즉, 하나의 식당은 여러 종류의 피자 옵션 집합을 결정하고,

독립적으로, 하나의 식당은 여러 종류의 배달 구역을 결정한다.

 

A1 Pizza 는 (Thick Crust, Stuffed Crust) 를 결정하고

Elite Pizza 는 (Thin Crust, Stuffed Crust) 를 결정하고 있으므로, 서로 다른 식당에 대해서 같은 값을 결정할 필요는 없다.

 

 

이때 A1 Pizza 에서 새로운 메뉴로 하와이안 피자를 추가했다고 해보자.

그러면 Derlivery Area 는 MVD가 존재하기 때문에 하와이안 피자에 대해 3가지 Derivery area를 모두 저장해주어야 한다.

하지만 깜빡하고 Capital City를 생략하는 문제가 발생했다. (삽입 이상)

 

이런 문제를 예방하려면 이 테이블도 Restaurant를 기준으로 분할해주어야 한다.

(분할 결과는 생략)

 

만약 이렇게 삽입을 하는 것이 '문제가 없다' 라고 한다면 (즉, MVD가 없다면) 이 테이블은 자동으로 4NF를 만족한 테이블이 된다.

(그렇다면 MVD가 있는지 어떻게 알까? 조건에서 명시를 해주는 걸까? 일단 GPT는 테이블 데이터가 저장된 형태만으로는 MVD가 존재하는지 알 수 없으며, 비즈니스 규칙을 제시해야 한다고 한다.)

 

5NF

5NF는 분리된 테이블을 natural join 했을 때, 분리된 테이블에 있던 row가 사라지지 않아야 만족한다.

그래서 다른 이름으로는 (project-join normal form) 이라고도 한다.

 

 

예를 들어 위 테이블은 4NF까지 만족하는 테이블이다.

 

- 레코드가 atomic 하므로 1NF를 만족하고,

- 위 3개 컬럼의 조합이 곧 PK 이므로 3.5NF 까지는 체크할 것이 없다.

(partial FD도 없고, transitive FD도 없고, 모든 FD가 trival FD 또는 슈퍼키에 대한 FD 이다.)

- 마지막으로 위 관계에서 MVD는 존재하지 않는다. Acme 브랜드의 Lava Lamp 항목을 잭 슈나이더는 취급하지 않기 때문이다.

따라서 4NF도 만족하고 있다.

 

5NF를 만족하려면, 주어진 테이블을 어떤 식으로든 분해했다가 다시 합쳤을 때 원본 테이블이 복구가 되는 경우가 존재해야 한다.

만약 어떻게 분해해도 다시 합쳤을 때 항상 원본 데이터가 손실되면 그 테이블은 분해할 수 없는 테이블이므로 5NF를 만족하지 않는다.

 

 

 

만약 위 테이블을 이렇게 3개의 테이블로 쪼갰다고 해보자.

이때 오른쪽의 2개 테이블만 조인했다고 생각하면, 잭 슈나이더는 Acme의 3가지 상품을 모두 취급하는 것처럼 데이터가 나온다.

하지만 실제로 잭 슈나이더는 Acme 의 상품 중 Lava Lamp를 취급하지 않는다.

따라서 '없어야 하는 데이터'가 추가되었으므로 이런 분할을 가리켜 lossless decomposition 이라고 한다.

 

5NF는 lossless decomposition이 없어야 하므로 이렇게 분할하면 안된다.

하지만 위 그림과 같이 3가지로 쪼개는 경우에는 다시 기존의 테이블로 원복이 되므로 이 테이블은 4NF도 만족하고 5NF도 만족하는 테이블이다.