데이터베이스 정규화 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

  • 이전 댓글 더보기
  • 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 신고

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

    • Deepplay 2019.01.04 17:07 신고

      네 출처 남겨주시면 괜찮습니다 :)

    • 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

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

  • 진짜 토할거같애요 너무 어려움 2019.12.14 17:19

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

  • 너무 좋습니다 2020.04.24 19:04

    1차 정규화는 여러개의 튜플로 분리함으로써, 2,3차 정규화와 BCNF는 테이블을 분리함으로써 만족할 수 있군요. 감사합니다. 이해에 정말 큰 도움이 됐습니다. 특히 예시가 정말 좋네요!

  • 질문이여 2020.05.24 16:11

    3차 정규화 설명하실때 예제 테이블이 2차 정규형을 만족한다고 하셨는데 애초에 Zip에게 종속되어있는 세개의 컬럼은 기본키에 종속되어있지 않기때문에 제 2정규화를 만족하지 않는것 아닌가요?

    2차 정규화에서는 모든 컬럼이 기본키와 완전 종속되어야 한다고 하셨는데,
    스트릿, 시티, 스테이트는 기본키인 스튜던트ID에 완전 종속 관계가 아닌것같아서요.
    제가 잘못된 방향으로 이해를 한건가요?? ㅠㅠ

  • 라이프리 2020.11.17 17:27 신고

    SQLD공부하는데 도움됐어요 감사합니다!

  • 질문 2021.01.01 20:27

    BCNF의 예시에서 기본키인 (학생, 과목)중 과목 만으로 교수를 알 수 있으므로 2 정규형을 만족하지 못하는것 아닌가요?
    부분 함수적 종속인거 같은데요
    그럼 자연히 3 정규형도 만족하지 못하게 되니 적절하지 않은 예시같습니다

  • adover 2021.01.19 13:22

    이상에 대한 설명이 좀 안 맞는 것 같아요.

    삽입 이상의 경우, 수강과목 명은 굳이 안 넣어도 괜찮다고 봅니다. 실생활에서 휴학하는 학생의 경우가 해당됩니다. 학생번호를 넣어야하는데 넣지 않는 경우가 삽입이상이라 봅니다.

    삭제 이상의 경우, Alex의 레코드가 없어지는 것이 문제가 아니라, 그가 수강 중인 과목 2개가 모두 혼자만 수강하고 있다는 점이 문제입니다. 즉, Alex의 레코드가 없어지면서 Bio와 Physics가 수강 과목 목록에서 사라지기 때문에 이상이 생기는 것입니다. 만약 Alex도 다른 학생들처럼 Maths과목만 수강했다면 문제가 생기지 않습니다.

  • 우리들 2021.07.29 14:42

    잘봤습니다.
    정리 감사합니다.

  • 정처기 공부중 2021.07.29 17:48

    이 글잉 제일 이해 잘 되는거 같아요ㅠㅠ 공부 이론 정리겸 일부 스크랩해도 될까요? 그냥 혼자 공부하는 용이고, 출처 남기겠습니다

  • 대연. 2021.09.01 23:23 신고

    이 글 진짜 찰지네요 감사합니다