데이터베이스 정규화 1NF, 2NF, 3NF, BCNF



데이터베이스 정규화란 데이터베이스의 설계를 재구성하는 테크닉입니다. 정규화를 통해 불필요한 데이터(redundancy)를 없앨 수 있고, 삽입/갱신/삭제 시 발생할 수 있는 각종 이상현상(Anamolies)들을 방지할 수 있습니다. 



데이터베이스 정규화의 목적은 주로 두 가지입니다.


1. 불필요한 데이터(data redundancy)를 제거한다.

2. 데이터 저장을 "논리적으로" 한다.


여기서 2번 데이터 저장을 논리적으로 한다는 것은 데이터 테이블의 구성이 논리적이고 직관적이어야한다는 것입니다.


우선 정규화를 안 했을 때의 문제점에 대해서 알아보겠습니다.




위와 같이 정규화가 되지 않은 구조의 테이블(Adam이라는 학생이 두 번 들어가 있습니다.)의 경우, 데이터 핸들링시 다양한 이상현상이 발생하게 됩니다.


1. Update : Adam의 Address가 변경되었을 때, 여러줄의 데이터를 갱신해야합니다. 이로인해 데이터의 불일치(inconsistency)가 발생할 수 있습니다.


2. Insert : 만약 학생이 아무 과목도 수강하지 않는다고 하면, Subject_opted 컬럼에는 NULL이 들어갈 것입니다.


3. Deletion : 만약 Alex 학생이 과목 수강을 취소한다면 Alex의 레코드가 아예 테이블에서 지워져버립니다.


위와 같이 정규화가 제대로 되지 않은 테이블의 경우 갱신/삽입/삭제 시 다양한 문제점이 발생할 수 있습니다. 이를 테이블의 구성을 논리적으로 변경하여 해결하고자 하는 것이 바로 정규화입니다.


정규화의 법칙(Normalization Rule)은 1차정규화, 2차정규화, 3차정규화, BCNF, 4차정규화, 5차정규화로 나눌 수 있는데, 실무적으로 4차, 5차 정규화까지 하는 경우는 많지 않다고 합니다. 따라서 이 포스팅에서도 BCNF까지만 알아보겠습니다.



1. 1차 정규화


1차 정규형은 각 로우마다 컬럼의 값이 1개씩만 있어야 합니다. 이를 컬럼이 원자값(Atomic Value)를 갖는다고 합니다. 예를 들어, 아래와 같은 경우 Adam의 Subject가 Biology와 Maths 두 개 이기 때문에 1차 정규형을 만족하지 못합니다.



위의 정보를 표현하고 싶은 경우 이렇게 한 개의 로우를 더 만들게 됩니다. 결과적으로 1차 정규화를 함으로써 데이터 redundancy는 더 증가하였습니다. 데이터의 논리적 구성을 위해 이 부분을 희생하는 것으로 볼 수 있습니다.



2. 2차 정규화


2차 정규화부터가 본격적인 정규화의 시작이라고 볼 수 있습니다. 2차 정규형은 테이블의 모든 컬럼이 완전 함수적 종속을 만족하는 것입니다. 이게 무슨 말이냐면 기본키중에 특정 컬럼에만 종속된 컬럼(부분적 종속)이 없어야 한다는 것입니다. 위 테이블의 경우 기본키는 (Student, Subject) 두 개로 볼 수 있습니다. 이 두 개가 합쳐져야 한 로우를 구분할 수가 있습니다. 근데 Age의 경우 이 기본키중에 Student에만 종속되어 있습니다. 즉, Student 컬럼의 값을 알면 Age의 값을 알 수 있습니다. 따라서 Age가 두 번 들어가는 것은 불필요한 것으로 볼 수 있습니다.


Student Table



Subject Table


이를 해결하기 위한 방법은 위처럼 테이블을 쪼개는 것입니다. 그러면 두 테이블 모두 2차 정규형을 만족하게 됩니다. 위 테이블의 경우 삽입/갱신/삭제 이상을 겪지 않게됩니다. 하지만 조금 더 복잡한 테이블의 경우, 갱신 이상을 겪기도하는데 이를 해결하는 것이 바로 3차 정규화입니다.


3. 3차 정규화



이와 같은 데이터 구성을 생각해봅시다. Student_id가 기본키이고, 기본키가 하나이므로 2차 정규형은 만족하는 것으로 볼 수 있습니다. 하지만 이 데이터의 Zip컬럼을 알면 Street, City, State를 결정할 수 있습니다. 또한 여러명의 학생들이 같은 Zip코드를 갖는 경우에 Zip코드만 알면 Street, City, State가 결정되기 때문이 이 컬럼들에는 중복된 데이터가 생길 가능성이 있습니다. 정리하면 3차 정규형은 기본키를 제외한 속성들 간의 이행적 함수 종속이 없는 것 입니다. 풀어서 말하자면, 기본키 이외의 다른 컬럼이 그외 다른 컬럼을 결정할 수 없는 것입니다.


3차 정규화는 2차정규화와 마찬가지로 테이블을 분리함으로써 해결할 수 있는데, 이렇게 두 개의 테이블로 나눔으로써 3차 정규형을 만족할 수 있습니다. 이를 통해 데이터가 논리적인 단위(학생, 주소)로 분리될 수 있고, 데이터의 redundancy도 줄었음을 알 수 있습니다.




4. BCNF


BCNF는 (Boyce and Codd Normal Form) 3차 정규형을 조금 더 강화한 버전으로 볼 수 있습니다. 이는 3차 정규형으로 해결할 수 없는 이상현상을 해결할 수 있습니다. BCNF란 3차정규형을 만족하면서 모든 결정자가 후보키 집합에 속한 정규형입니다. 아래와 같은 경우를 생각해보면, 후보키는 수퍼키중에서 최소성을 만족하는 건데, 이 경우 (학생, 과목) 입니다. (학생, 과목)은 그 로우를 유일하게 구분할 수 있습니다. 근데 이 테이블의 경우 교수가 결정자 입니다. (교수가 한 과목만 강의할 수 있다고 가정) 즉, 교수가 정해지면 과목이 결정됩니다. 근데 교수는 후보키가 아닙니다. 따라서 이 경우에 BCNF를 만족하지 못한다고 합니다. 3차 정규형을 만족하면서 BCNF는 만족하지 않는 경우는 언제일까요? 바로 일반 컬럼이 후보키를 결정하는 경우입니다.


학생

과목

교수

학점

 1

AB123

 김인영

 A

 2

 CS123

 Mr.Sim

 A

 3

 CS123

 Mr.Sim

 A


 위와 같이 테이블이 구성된 경우에 데이터가 중복되고, 갱신 이상이 발생합니다. 예를 들어 Mr.Sim이 강의하는 과목명이 바뀌었다면 두 개의 로우를 갱신해야합니다. 이를 해결하기 위해서는 마찬가지로 테이블을 분리합니다.


교수 테이블


교수

과목

 김인영

AB123

 Mr.Sim

CS123


수강 테이블


학생

과목 

학점

 1

 AB123 

 A

 2

 CS123 

 A

 3

 CS123 

 A




참고

http://www.studytonight.com/dbms/database-normalization.php

http://pronician.tistory.com/922

  • 2018.06.14 18:09

    비밀댓글입니다

    • 사용자 Deepplay 2018.07.24 01:20 신고

      네 맞습니다 현실 상황에서는 말씀하신대로 그게 더 적절할거 같습니다만 본문에서는 교수가 한과목만 가르치는 걸 가정했습니다. 오해의 소지가 있네요. ㅎㅎ

  • 열코 2018.09.06 18:12 신고

    감사합니다 공부하는데 많은 도움이 됬어요 ^^

  • Code 2018.10.05 00:30

    마지막 BCNF 정규화 끝난 릴레이션이 논리적으로 조금 이상해서 의견씁니다.

    수강이라는건 과목이 들어가는게 맞는것 같습니다. 지금예제에서 과목과 교수가 1:1로 매칭되고 있으니 예제를 아래와 같은 식으로 바꾸는게 어떤가 해서 글 올립니다.
    과목 릴레이션
    과목 교수
    자바 교수1
    머신러닝 교수2
    컴퓨터일반 교수3
    수강 릴레이션
    학생 과목
    학생1 자바
    학생2 머신러닝
    학생3 컴퓨터일반
    학생4 자바

    • 사용자 Deepplay 2018.10.05 01:18 신고

      네 수강 테이블을 과목으로 바꾸었습니다. 의견 감사합니다.

  • 지나가는이 2018.10.11 18:21

    오 쏙쏙 들어오는 설명 감사합니다. 잘 배우고 가요^^

  • tester 2018.12.11 02:51

    이해가 잘되는 설명이었습니다. 잘보고갑니다.

  • 진심 2018.12.15 21:07

    진짜 토할거같애요 너무 어려움

  • 저커버그 2018.12.18 16:15

    와 진짜이해가 쏙쏙됩니다.

  • heeko 2019.01.04 13:53 신고

    감사합니다

    • heeko 2019.01.04 13:54 신고

      혼자 공부하는 중인데 제 블로그에 퍼가도될까요? ㅠㅠ 출처 꼭 남기겠습니다.. ㅠㅠ

    • heeko 2019.01.04 17:16 신고

      감사합니다ㅜㅜ 블로그 많이 이용할게요 ㅜㅜ새해 복 많이 받으세요!!!

  • 고디비 2019.01.25 06:09

    BCNF 부분에서 질문있습니다.

    (학생, 과목)을 후보키라고 하셨는데 (학생, 교수)의 조합도 후보키가 될 수 있나요?
    교수가 한 과목만을 가르친다고 가정을 했으면 후자도 가능한건가요?

    그리고 교수가 결정자라고 하셨는데
    결정자라는 게 결정자 (Determinant)를 통해서 Dependent의 값을 알 수 있는 개념인데
    관점에 따라서 결정자가 달라지나요?
    처음에는 학생이 결정자가 된다고 생각했습니다.

  • Sumit 2019.01.25 17:26

    진짜 토할거같애요 너무 어려움

  • ㅁㄴㅇ 2019.03.30 16:07

    토짜 진할거같애요 어무 너려움

  • 1234 2019.04.26 22:00

    인터넷에 BCNF를 이상하게 설명한 글이 많은데 이글은 제대로 설명되어있네요. 까먹었는데 좋은 글 감사합니다.

  • 예리한 사람 2019.06.11 15:53

    address(Zip Street city state) 테이블
    1차키는 Zip
    street city state는 일반 속성

    이 상태에서 street가 city를 결정하고 city는 state를 결정할 수 있으니 이 것도 분리해야 3차 정규화가 되나요?

  • 진짜 토할거같애요 너무 어려움 2019.06.24 12:43

    진짜 토할거같애요 너무 어려움

  • 조아요 2019.06.26 11:57

    이건 안달수가 없네요
    감사합니다
    이해하기 가장 좋은 글 같아요