오늘은 데이터 정규화에 대해 정리해보려고 한다.
데이터베이스 정규화의 목적은 주로 두 가지이다.
- 불필요한 데이터(data redundancy)를 제거한다.
- 데이터 저장을 "논리적으로" 한다.
만약 정규화를 안 하고 행마다 같은 값이 들어가 있다면 어떻게 될까? 아래의 예시를 보자.
- Adam의 Address가 변경되었을 때, 여러줄의 데이터를 갱신해야한다. 이로인해 데이터의 불일치(inconsistency)가 발생할 수 있다.
- 데이터 삽입 시 만약 학생이 아무 과목도 수강하지 않는다고 하면, Subject_opted 컬럼에는 NULL이 들어갈 것다.
- 만약 Alex 학생이 과목 수강을 취소한다면 데이터 삭제 시 Alex의 레코드가 아예 테이블에서 지워져버린다.
그러므로 정규화는 데이터베이스를 다룰 때 필요한 작업인데, 보통 정규화는 1차정규화, 2차정규화, 3차정규화, BCNF, 4차정규화, 5차정규화로 나눈다고 한다.
실무적으로 4차, 5차 정규화까지 하는 경우는 많지 않다고 하니 우리는 BCNF까지만 다뤄보자.
1차 정규화(1NF)
Rules
- Each table cell should contain a single value.
- Each record needs to be unique.
테이블의 각 행마다 컬럼의 값이 1개씩만 있어야 한다.(이를 두고 컬럼이 원자값 (Atomic Value)을 갖는다고 한다).
아래의 테이블 예시를 보면, Adam의 Subject가 Biology, Maths로 두 개가 같이 들어가 있는 것을 볼 수 있다.
만약 두개를 모두 표현하고 싶은 경우 한 개의 행을 더 만들면 된다. (1차 정규화)
1차 정규화를 함으로써 data redundancy는 더 증가했지만, 데이터의 논리적 구성을 충족했다.
이제 2차 정규화를 살펴보자.
2차 정규화를 보기 전에 Primary Key에 대한 개념 설명을 하나 하고 가겠다.
Primary Key란(PK)
- 테이블의 한 행을 유니크하게 만들어주는 키
- 값이 NULL이 될 수 없는 키
- 반드시 유니크해야 한다.
- 키의 값이 거의 변화되면 안된다.
- 새로운 레코드가 삽입될 때 특정 값이 주어져야 한다.
2차 정규화(2NF)
Rules
- Rule 1- Be in 1NF
- Rule 2- Single Column Primary Key
테이블의 Primary Key가 단일 컬럼이 되어야 한다.
위 테이블의 경우 Primary Key는 (Student, Subject) 라고 볼 수 있다. 두 개의 컬럼으로 한 행을 구분할 수 있기 때문이다.
그러나 Age의 경우 사실 Primary Key 중에 Student에만 종속되어 있다. 즉, Student 컬럼의 값을 알면 Age의 값을 알 수 있는 것이다. 따라서 Age가 두 번 들어가는 것은 불필요하다고 볼 수 있다.
이때 정규화를 위해서 테이블을 2개로 쪼갤 수 있다. 그러면 두 테이블 모두 2차 정규형을 만족하게 된다.
아래의 예시도 보면, 원래 MOVIES RENDTED까지 같이 있던 하나의 테이블을 2개의 테이블로 2차 정규화를 시킨 것이다. PK는 Membership ID가 된다.
테이블을 쪼갤 때, Table 1의 id를 Table 2에 넣으면(Table 2의 Foreign Key) 2개의 테이블은 id를 이용해서 유니크한 record를 유지할 수 있다.
Foreign Key란(FK)
다른 테이블의 Primary Key(PK)를 참조하는 키이다.
- 보통 해당 테이블의 PK와 다른 이름을 가져야 한다.
- 다른 테이블에 FK와 상응 하는 행이 있는걸 보장해야 한다.
- 유니크 한 키일 필요는 없다.
- 값이 NULL이 될 수 있다.
이제 3차 정규화를 알아보자.
3차 정규화를 보기 전에 Transitive Functional Dependencies에 대한 개념 설명을 하나 하고 가겠다.
Transitive Functional Dependencies(TFD)란?
- 특정 non-key column을 변경할 때, 다른 non-key column이 변경될 수 있는 여지가 있는 것을 말한다.
위의 예시처럼 Full Name을 바꾸면 Salutation이 바뀔 여지가 있다. 이것을 TFD라고 한다.
위에서 나온 non-key column이란?
- non-key를 이해하기 위해선 key를 이해해야 한다. key가 아닌 column이 non-key column이기 때문이다.
- key는 INDEX와 동의어인데, key는 column에 index를 만들어주는 개념이다. index는 테이블에 어떤 제한을 걸지 않지만 테이블을 훑는 쿼리를 더 빠르게 만들어준다.
- 이는 값마다 index가 있기 때문에 쿼리가 필요한 부분만 찾아서 검색결과를 내놓을 수 있기 때문이다.(사람이 전화번호부에서 사람을 찾을 때 그냥 일일이 찾는게 아니라 가나다 혹은 0-9 순으로 찾는게 더 빠른 것과 동일한 원리이다.)
- 물론 불필요한 index를 많이 만들면 성능이 안 좋아질 수도 있기 때문에 무조건 index를 추가하는게 좋은 것은 아니다.
- 결론 : key column은 테이블의 행을 유니크하게 식별해주는 컬럼을 의미한다. key column의 값은 각 행마다 모두 달라야 한다.(Primary Key Column을 생각하면 되겠다.)
3차 정규화
Rules
- Rule 1- Be in 2NF
- Rule 2- Has no transitive functional dependencies
즉, transitive functional dependencies가 없어야 한다.
예를 들어 위의 테이블을 보면 Student_id가 PK이고, PK는 하나이므로 2차 정규형은 만족하는 것을 알 수 있다. 그러나 문제가 있는데, 이 테이블의 Zip컬럼을 변경하면 Street, City, State도 변경 될 수 있다는 것이다.
또한 여러명의 학생들이 같은 Zip 값을 갖는 경우, Zip, Street, City, State 컬럼들이 중복이 될 가능성이 있다.
그러므로 3차 정규화를 통해 transitive functional dependencies를 없애주어야 한다.
3차 정규화 또한 테이블을 분리해서 해결할 수 있다.
이제 BCNF를 알아보자.
BCNF(Boyce and Codd Normal Form)
Rules
- It should be in the Third Normal Form.
- And, for any dependency A → B, A should be a super key.
- that for a dependency A → B, A cannot be a non-prime attribute, if B is a prime attribute.
BCNF는 3차 정규화를 강화한 버전으로 볼 수 있다.
BCNF는 모든 결정자가 후보 키 집합에 속한 정규형이며, A 컬럼과 B 컬럼의 의존관계가 생겼을 때 두 컬럼을 분리하는 작업이라고 볼 수 있다. 설명이 어려우니 좀 더 풀어보자.
일단 슈퍼 키, 후보 키, prime attribute 개념부터 알아보자.
슈퍼 키란 무엇인가?
- Super key는 테이블에 행을 유니크하게 식별할 수 있는 키(단일 키 혹은 키들)의 집합이다.
- Super key에는 유니크하게 식별하는데 필요하지 않은 값도 포함 될 수는 있다.
- Primary Key는 각 테이블에 한 개만 있어야 한다는 점에서 Super key와 다르다.
후보 키란 무엇인가?
- 후보 키(Candidate key)는 테이블 레코드를 유니크하게 식별할 수 있는 값들의 집합이다.
- 후보 키는 슈퍼 키 범주에 포함되지만, 최소한으로 유지될 수 있는 슈퍼 키라고 보면 된다. (후보 키 집합 요소 중 어느 요소라도 제거되면 유니크함을 잃어버리기 때문이다.)
- 예시 참조 :
아래와 같은 테이블이 있다고 해보자.year month date major minor 2008 01 13 0 1 2008 04 23 0 2 2009 11 05 1 0 2010 04 05 1 1 ```
- 위에서 (year, major, minor) 혹은 (year, month,date, major) 집합은 슈퍼 키이다. (행을 유니크하게 식별할 수 있기 때문이다.)
- 그러나 저 집합이 후보 키는 아니다. 예를 들어 각 집합에서 year를 빼도 아직 유니크함이 보장되는 슈퍼 키이기 때문이다.
- 따라서 (year, month, date) 혹은 (major, minor)는 후보 키가 된다. 이 집합 요소 중에 어느 하나라도 빼면 유니크함을 보장할 수 없기 때문이다.
- (물론 일반적으로 year,month, date는 후보 키가 되기 어려울 수 있다. 동일한 날짜를 가질 수 있는 케이스가 많기 때문이다.)
후보 키의 특징:
- Primary Key는 후보 키 중에서 선택된다고 볼 수 있다.
- 모든 테이블은 적어도 하나의 후보 키를 가져야 한다.
- 후보 키는 여러 값을 가질 수 있다.
- NULL 값을 포함할 수 없다.
참고로 아래의 예시를 보면 Primary Key와 Candidate Key 관계 또한 파악할 수 있을 것이다.
prime attribute(PA), non-prime attribute(NPA)란?
- PA는 후보키의 부분 요소로서 유니크함을 보장하는 attributes를 의미한다.
- NPA는 당연히 PA가 아닌 요소를 의미한다.
- (요소는 쉽게 테이블의 컬럼이라고 이해해도 될 것 같다.)
자 이제, 다시 BCNF로 돌아와보자.
| student_id | subject | professor |
|---|---|---|
| 101 | Java | P.Java |
| 101 | C++ | P.Cpp |
| 102 | Java | P.Java2 |
| 103 | C# | P.Chash |
| 104 | Java | P.Java |
위의 테이블은 BCNF가 적용되지 않은 테이블이다.
위 테이블 특징:
- 한명의 학생은 여러 과목을 수강신청 할 수 있다.
- 한 명의 교수는 각 과목을 듣는 학생에게 할당된다.(Java를 듣는 학생 101은 P.Java 교수만 할당된다.)
- 한 개의 과목은 여러 교수가 가르칠 수 있다.(Ex. P.Java, P.Java2)
이 테이블에서 어떤 키가 PK로 되어야 하는가?
과목과 학생 id를 묶으면 PK가 될 수 있다. 두 컬럼을 묶으면 모든 행을 유니크하게 판별할 수 있기 때문이다.
허나 이 테이블에서 짚고 넘어가야 할 게 있는데, 한 명의 교수는 오직 하나의 과목만 가르치지만 한 개의 과목은 여러 교수를 가질 수 있다는 점이다.
따라서 과목과 교수의 의존관계가 생겨버린다. 교수가 바뀌거나 교수가 과목을 바꾸면, 과목 컬럼도 바뀔 수 있다. 즉, 이 테이블의 경우 교수가 결정자 이다. (교수가 한 과목만 강의할 수 있다고 가정) 그러나 교수 컬럼은 후보 키 요소가 아니다.
즉, 과목-교수 의존관계가 있지만, 과목 컬럼은 prime attribute인데, professor는 non-prime attribute이기 때문에 BCNF가 허용되지 않는 것이다.
BCNF로 정규화를 하기 위해서는 테이블을 분리해야 한다. 학생 테이블과 교수 테이블로 말이다.
Student Table
| student_id | p_id |
|---|---|
| 101 | 1 |
| 101 | 2 |
| and so on... | and so on... |
Professor Table
| p_id | professor | subject |
|---|---|---|
| 1 | P.Java | Java |
| 2 | P.Cpp | C++ |
| and so on... | and so on... | and so on... |
마지막으로
4차, 5차 정규화를 알고 싶다면 이 링크를 참고 하길 바란다.
참고 :
https://www.guru99.com/database-normalization.html
https://www.studytonight.com/dbms/boyce-codd-normal-form.php#
