[Database] 데이터베이스 정규화
토이 프로젝트를 하다가 데이터베이스 정규화에 대한 토픽이 나와 이참에 나름대로 정리를 한번 해보려고 한다.
데이터베이스 구조를 잘 짜는 것은 참 중요하긴 하다. 전에 다녔던 회사에서 엄청 오래된 프로젝트의 데이터베이스 구조 변경을 하는데에 엄청 애먹었던 것이 생각난다. 한 테이블에 모든것을 때려박아서 명칭 하나 변경되어야 할 뿐이었는데 밤새서 리팩토링했던 기억이..ㅎㅎ
통용적으로 사용되는 데이터베이스 정규화 과정을 한번 정리해보면 나중에 프로젝트를 진행할 때에 정규화에서 얻을 수 있는 장점을 적용시키는 능력과, 필요시에 오히려 비정규화를 적용시킬 판단기준을 기르는 데에 도움이 될 것 같아서 한번 정리해보자! 하고 맘먹었다.
데이터베이스 정규화
정규화는 테이블의 중복 데이터를 최소화하여 데이터베이스를 설계하여, SQL의 특징인 체계적인 관계를 조직하는 것을 목표로 한다.
잘 알려진 정규화에는 제 1, 2, 3 정규화와 (1NF, 2NF, 3NF라고 말한다.) BCNF, 4NF, 5NF, 최근에 소개된 6NF까지도 있다.
일반적으로 3정규화(3NF)까지 이루어지면 데이터베이스가 상당히 정규화되었다고 평가되며, 그 이상도 이루어질 경우 더 높은 수준의 정규화가 이루어졌다고 할 수 있다.
해당 포스팅에서는 1~3 정규화까지의 단계를 나름대로 다루어 보려고 한다.
정규화의 기대효과
1. 이상 현상의 제거
- 갱신 이상 : 같은 데이터 묶음이 복수의 행에서 나타날 때, 해당 데이터 묶음의 일부가 변경될 시, 모든 행에서의 갱신이 이루어져야 한다. 이 때 일부가 갱신되지 않으면 데이터 무결성에 위배된다.
- 삽입 이상 : 행을 추가할 때, 일부 컬럼에 대한 값을 가질 수 없는 데이터를 추가하려 한다면 테이블은 모순인 상태가 되며, 삽입할 수 없는 경우가 생긴다. 예를 들어 한국 이름이 NOT NULL인 회원 테이블에 외국인을 추가하려 할 때 이상이 생긴다.
- 삭제 이상 : 일부 데이터를 삭제해야 하는 상황이 올 때, 같은 행의 삭제되면 안되는 데이터들까지 삭제될 수 있다.
2. 데이터베이스 확장과 구조 변경시
- 정규화를 거친다면, 중복된 데이터가 최소화되므로 확장과 구조 변경에 용이하다. 큰 테이블이 작은 구조로 분리되어 있으므로 비교적 적은 양의 구조 변경으로 확장에 용이하다.
3. 질의의 속도가 빨라질 수도, 질의의 속도가 느려질 수 있다.
테이블의 데이터 용량이 줄어 질의 시간이 빨라질 수도 있지만, 질의 시 연관 테이블의 JOIN연산이 증가함에 따라 질의 응답시간이 빨라질 수도, 느려질 수 있다. 만약 성능 저하가 발생한다면 반정규화(정규화하지 않는 것)를 고려할 수 있다.
반정규화가 나쁜 것은 아니다, 필요에 따라 적용해야 한다.
제 1 정규화
모든 속성의 도메인이 더이상 분해되지 않는 원자값으로만 구성된 정규형을 의미한다.
각 컬럼은 유일한 속성만을 표현해야 하며, 모든 로우들은 해당 컬럼에 일관된 데이터(속성, 타입)를 가져야 한다.
쉽게 말해서 한칸에 이것도 넣고 저것도 넣고 하지 말고 표현하고자 하는 것 단 하나씩만 넣으라는 것이다.
아래의 표는 학생들의 수강신청 내역을 나타낸 테이블이다. 이것을 가지고 제 1 정규화를 해보자.
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 | 수강과목정보(과목번호/과목명/교수명/강의실/교수이메일/수강신청일) |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S01 C언어 김철수 G303 chlu@gmail.com 2022-08-27 // S02 R언어 김유리 G402 uur@gmail.com 2022-08-27 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S03 C언어 김맹구 G301 mmang@gmail.com 2023-01-02 // S02 R언어 김유리 G404 uur@gmail.com 2023-01-04 |
다가속성 분리
수강과목 컬럼에 수강과목 데이터가 여러개가 들어있다.(다가속성)
이것을 한개씩만 표현되도록 분리해주자.
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 | 수강과목정보(과목번호/과목명/교수명/강의실/교수이메일/수강신청일) |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S01 C언어 김철수 G303 chlu@gmail.com 2022-08-27 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S02 R언어 김유리 G402 uur@gmail.com 2022-08-27 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S03 C언어 김맹구 G301 mmang@gmail.com 2023-01-02 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S02 R언어 김유리 G404 uur@gmail.com 2023-01-04 |
복합속성 분리
수강과목 컬럼은 여러개의 속성으로 이루어져 있다. 한 칸에는 표현하고자 하는 것 하나씩만 넣어야 한다는 점.
복합속성도 분해해주자.
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 | 과목번호 | 과목명 | 교수명 | 교수이메일 | 강의실 | 수강신청일 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S01 | C언어 | 김철수 | chlu@gmail.com | G303 | 2022-08-25 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S02 | R언어 | 김유리 | uur@gmail.com | G402 | 2022-08-27 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S03 | C언어 | 김맹구 | mmang@gmail.com | G301 | 2023-01-02 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S02 | R언어 | 김유리 | uur@gmail.com | G404 | 2023-01-04 |
위의 과정까지 마쳤으면 하나의 칸에 하나씩만 표현한 제1정규화가 이루어진 상태이다.
제 2 정규화
제 1정규화를 만족시키며, 모든 속성이 기본키에 완전 함수 종속되어야 한다.
기본키는 하나일수도, 여러개(복합 키)일 수도 있다. 중요한것은 모든 테이블의 일원들은 기본키 전체에 완전하게 의존해야 하며, 부분집합에 의해 의존해서는 안된다.(부분 함수 종속이 이루어져서는 안된다.)
- 완전 함수 종속: 비-기본 속성이 기본키 전체에 종속되는 경우. 모든 속성이 기본키의 일부가 아닌 전체에 의존해야 한다.
- 부분 함수 종속: 기본키가 여러 속성(복합 키)으로 구성된 경우, 모든 속성이 그 중 일부 속성에만 종속되는 경우. 제2정규형에서 제거해야 하는 부분이다.
기본키 찾기
해당 테이블은 기본키로 수강정보번호가 있고, 하나의 수강정보번호에는 종속되어 동일한 학생이 존재한다.
또한 하나의 수강정보번호에 여러개의 과목번호가 존재하여 연관되어 있으므로 수강정보번호+과목번호의 복합키의 PK가 있다.
제 2 정규화 원칙에 따르면 나머지 속성들은 이 둘중 하나에만 종속되면 안되며, 만약 그렇다면 테이블을 분리시켜 중복을 없애주어야 한다.
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 | 과목번호 | 과목명 | 교수명 | 교수이메일 | 강의실 | 수강신청일 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S01 | C언어 | 김철수 | chlu@gmail.com | G303 | 2022-08-25 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S02 | R언어 | 김유리 | uur@gmail.com | G402 | 2022-08-27 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S03 | C언어 | 김맹구 | mmang@gmail.com | G301 | 2023-01-02 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S02 | R언어 | 김유리 | uur@gmail.com | G404 | 2023-01-04 |
테이블 분해
수강학기와, 학번, 이름, 나이는 완전히 수강정보번호에 종속되어 있으며 과목번호에는 종속되어 있지 않다.
따라서 해당 테이블을 분리시켜준다.
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 |
| 수강정보번호 | 과목번호 | 과목명 | 교수명 | 교수이메일 | 강의실 | 수강신청일 |
| T001 | S01 | C언어 | 김철수 | chlu@gmail.com | G303 | 2022-08-25 |
| T001 | S02 | R언어 | 김유리 | uur@gmail.com | G402 | 2022-08-27 |
| T002 | S03 | C언어 | 김맹구 | mmang@gmail.com | G301 | 2023-01-02 |
| T002 | S02 | R언어 | 김유리 | uur@gmail.com | G404 | 2023-01-04 |
테이블 분해 2
남은 테이블을 보면 강의실과 수강신청일은 수강정보번호와 과목번호 양쪽에 따라 달라지므로 복합키에 의존하고 있다.
그렇지만 과목명, 교수명, 교수이메일은 과목번호에만 의존하고 있다. 따라서 다시 과목번호에만 의존하는 속성들을 모아 테이블을 분리시켜준다.
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 |
| 수강정보번호 | 과목번호 | 강의실 | 수강신청일 |
| T001 | S01 | G303 | 2022-08-25 |
| T001 | S02 | G402 | 2022-08-27 |
| T002 | S03 | G301 | 2023-01-02 |
| T002 | S02 | G404 | 2023-01-04 |
| 과목번호 | 과목명 | 교수명 | 교수이메일 |
| S01 | C언어 | 김철수 | chlu@gmail.com |
| S02 | R언어 | 김유리 | uur@gmail.com |
| S03 | C언어 | 김맹구 | mmang@gmail.com |
해당 과정이 완료되면 테이블들이 분리되었으며, 각각의 테이블의 속성들은 기본키에만 의존하는 상태가 되어 제 2 정규화가 이루어졌다.
제 3 정규화
제 1, 2 정규화를 만족시키며, 이행적 함수적 종속을 제거해야 한다. 즉, 모든 기본 키가 아닌 속성들은 오로지 기본 키에만 직접적으로 종속되어야 한다. (= 다른 기본 키가 아닌 속성에 이행적 종속이 없어야 한다.)
- 이행적 함수 종속 : A -> B (함수적 종속), B -> C (함수적 종속)일 때, A -> C (함수적 종속)인 관계.
C는 A에 직접 종속된 것이 아니라, B를 통해 간접적으로 종속된 것이다. (속성 A가 속성 B를 결정하고, 속성 B가 속성 C를 결정)
BCNF
BCNF는 3정규형(3NF)의 강화된 형태로, 모든 결정자가 후보 키여야 한다. 모든 함수적 종속에서 왼쪽의 속성은 반드시 후보 키여야 한다.
즉 모든 속성이 기본 키에만 의존해야 하며, 다른 후보 키에 의존해서는 안 된다. BCNF는 더 엄격한 기준을 가지는 3정규화라고 볼 수 있다.
다른 속성(기본키 제외)에 대해서 의존하는 속성 찾기
수강학기와, 학번, 이름, 나이는 완전히 수강정보번호에 종속되어 있으며 과목번호에는 종속되어 있지 않다.
따라서 해당 테이블을 분리시켜준다.
수강학기는 수강정보번호에만 의존하지만 이름과 나이는 수강정보에 종속된 것이 아닌 다른 구성원인 학번에 종속되어 있다.
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 |
마찬가지로 교수 이메일은 과목번호와는 상관없이 교수와 관련이 있는 것 같다. 여기서 추가로 동명이인 등의 문제로 교수명에 따른 교수이메일의 구분이 어려울 수 있으므로 Unique한 키를 지정해주어야 할 것 같다는 생각이 든다.
| 과목번호 | 과목명 | 교수명 | 교수이메일 |
| S01 | C언어 | 김철수 | chlu@gmail.com |
| S02 | R언어 | 김유리 | uur@gmail.com |
| S03 | C언어 | 김맹구 | mmang@gmail.com |
테이블 분해
학번에 의존하는 이름과 나이는 학번을 PK로 삼는 테이블로 분리시켜주었다. 이제 학번은 학생 테이블의 PK이자 수강정보 테이블의 FK로 사용된다.
| 수강정보번호 | 수강학기 | 학번 |
| T001 | 22년 2학기 | H001 |
| T002 | 23년 1학기 | H002 |
| 학번 | 이름 | 나이 |
| H001 | 노진구 | 21 |
| H002 | 퉁퉁이 | 23 |
마찬가지로 교수에 의존했던 이메일을 다른 테이블로 분리시켜주었으며 Unique Key인 교수번호를 생성하여 기존 테이블과 연결시켜주었다. 해당 과정을 거쳐 모든 테이블의 구성원들은 PK 이외의 다른 속성에 의존하지 않게 되었다.
| 과목번호 | 과목명 | 교수번호 |
| S01 | C언어 | P001 |
| S02 | R언어 | P002 |
| S03 | C언어 | P003 |
| 교수번호 | 교수명 | 교수이메일 |
| P001 | 김철수 | chlu@gmail.com |
| P002 | 김유리 | uur@gmail.com |
| P003 | 김맹구 | mmang@gmail.com |
정규화 정리
원본
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 | 수강과목정보(과목번호/과목명/교수명/강의실/교수이메일/수강신청일) |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S01 C언어 김철수 G303 chlu@gmail.com 2022-08-27 // S02 R언어 김유리 G402 uur@gmail.com 2022-08-27 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S03 C언어 김맹구 G301 mmang@gmail.com 2023-01-02 // S02 R언어 김유리 G404 uur@gmail.com 2023-01-04 |
제 1 정규화
모든 속성의 도메인이 더이상 분해되지 않는 원자값으로만 구성
다가속성 분해, 복합속성 분해
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 | 과목번호 | 과목명 | 교수명 | 교수이메일 | 강의실 | 수강신청일 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S01 | C언어 | 김철수 | chlu@gmail.com | G303 | 2022-08-25 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 | S02 | R언어 | 김유리 | uur@gmail.com | G402 | 2022-08-27 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S03 | C언어 | 김맹구 | mmang@gmail.com | G301 | 2023-01-02 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 | S02 | R언어 | 김유리 | uur@gmail.com | G404 | 2023-01-04 |
수강과목번호는 다가속성이자 복합속성이었으므로 분리
제 2 정규화
완전 함수적 종속을 만족시켜야 함.
기본키 찾기 -> 기본키가 복합키라면 나머지 속성들은 한 개의 키에만 종속되어서는 안됨 -> 한 개의 키에만 의존하는 속성들을 따로 테이블로 분리
| 수강정보번호 | 수강학기 | 학번 | 이름 | 나이 |
| T001 | 22년 2학기 | H001 | 노진구 | 21 |
| T002 | 23년 1학기 | H002 | 퉁퉁이 | 23 |
| 수강정보번호 | 과목번호 | 강의실 | 수강신청일 |
| T001 | S01 | G303 | 2022-08-25 |
| T001 | S02 | G402 | 2022-08-27 |
| T002 | S03 | G301 | 2023-01-02 |
| T002 | S02 | G404 | 2023-01-04 |
| 과목번호 | 과목명 | 교수명 | 교수이메일 |
| S01 | C언어 | 김철수 | chlu@gmail.com |
| S02 | R언어 | 김유리 | uur@gmail.com |
| S03 | C언어 | 김맹구 | mmang@gmail.com |
("과목번호"+"수강정보번호")는 복합키로서 작용, "과목번호", "수강정보번호" 각각에만 의존하는 속성들은 테이블 분리
제 3 정규화 (BCNF)
이행적 함수적 종속을 제거
속성들은 기본키 이외의 다른 속성에 의존해서는 안됨 -> 이행적 함수적 종속을 제거하기 위해 테이블 분리하기
| 수강정보번호 | 수강학기 | 학번 |
| T001 | 22년 2학기 | H001 |
| T002 | 23년 1학기 | H002 |
| 학번 | 이름 | 나이 |
| H001 | 노진구 | 21 |
| H002 | 퉁퉁이 | 23 |
| 수강정보번호 | 과목번호 | 강의실 | 수강신청일 |
| T001 | S01 | G303 | 2022-08-25 |
| T001 | S02 | G402 | 2022-08-27 |
| T002 | S03 | G301 | 2023-01-02 |
| T002 | S02 | G404 | 2023-01-04 |
| 과목번호 | 과목명 | 교수번호 |
| S01 | C언어 | P001 |
| S02 | R언어 | P002 |
| S03 | C언어 | P003 |
| 교수번호 | 교수명 | 교수이메일 |
| P001 | 김철수 | chlu@gmail.com |
| P002 | 김유리 | uur@gmail.com |
| P003 | 김맹구 | mmang@gmail.com |
기본키가 아닌 속성에 종속된 속성들이 보임. 후보키는 "교수번호", "학번"
테이블 분리 후 의존하고 있던 속성들을 기본키로 설정
제 3 정규화를 마친 데이터베이스에서 이전과 비교했을때의 장점을 살펴보자.
1. 학생의 나이가 바뀌었을 때, 교수의 이메일이 바뀌었을 때, 해당 과목의 담당 교수가 바뀌었을 때 등 기존에는 모든 중복되는 데이터들을 갱신해주어야 했는데 현재는 일부만 변경해주면 된다.
2. 퉁퉁이가 수강신청을 취소해서 데이터를 없앨 때 기존에는 퉁퉁이의 정보, 김맹구 교수의 정보까지 삭제해야 하는 상황이었다면, 이제 학생 데이터와 교수 데이터는 유지한 채 수강신청 데이터만 삭제할 수 있다.
3. 학생의 이메일 등 추가적인 데이터를 삽입하고 싶을 때 거대한 테이블을 변경하는 것 보다 작은 테이블을 변경하는 것이 용이하다.
4. 데이터 집합간의 관계가 명확해졌다.
Reference
데이터베이스 정규화 - 위키백과, 우리 모두의 백과사전
위키백과, 우리 모두의 백과사전. 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화(Normalization)라고 한다. 데이터베이스 정규화의 목표는 이상이 있
ko.wikipedia.org
https://code-lab1.tistory.com/48
[DB] 정규화(Normalization)란? 정규화 예시, 1NF, 2NF, 3NF, BCNF
정규화(Normalization)란? 정규화는 이상현상이 있는 릴레이션을 분해하여 이상현상을 없애는 과정이다. 이상현상이 존재하는 릴레이션을 분해하여 여러 개의 릴레이션을 생성하게 된다. 이를 단계
code-lab1.tistory.com
정규화 (제1 정규화 ~ 제3 정규화)
DB를 공부하다 보면 가장 이해하기 어려웠던 부분이 정규화 였습니다.- 도메인 원자값 (1NF)- 부분적 함수 종속 제거 (2NF)- 이행적 함수 종속 제거 (3NF)- 결정자이면서 후보키가 아닌 것 제거 (BCNF)-
mr-dan.tistory.com
https://hongcoding.tistory.com/147
데이터베이스 정규화(Normalization)란
데이터베이스 정규화(Normalization) 란? 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다. 정규화의 기본 목표는 관련이 없는 함수 종속성은
hongcoding.tistory.com
'Database > 데이터베이스' 카테고리의 다른 글
| [Database] 낙관적 락과 비관적 락 (0) | 2023.05.10 |
|---|---|
| [Database] 트랜잭션(Transaction), ACID 및 Isolation Level (0) | 2023.02.25 |
| [Database/Oracle] 데이터베이스 동의어 (0) | 2022.06.27 |
| [Database/Oracle] 스키마 이름과 데이터베이스 링크 (0) | 2022.06.27 |
