정규화 (Normalization)
•
정규화는 이상현상이 있는 릴레이션을 분해하여 이상현상을 없애는 과정
•
릴레이션을 분해하여 여러 개의 릴레이션을 생성하게 된다.
•
이를 단계별로 구분하여 정규화 단계가 높아질수록 이상현상은 줄어들게 된다.
제 1 정규형 (1NF)
아래와 같은 테이블이 있다.
회원번호 | 회원이름 | 프로그램 |
101 | 강호동 | 스쿼시초급 |
102 | 손흥민 | 헬스 |
103 | 김민수 | 헬스 |
103번 김민수가 골프초급 프로그램을 수강하려면 어떻게 표현할 수 있을까?
회원번호 | 회원이름 | 프로그램 |
101 | 강호동 | 스쿼시초급 |
102 | 손흥민 | 헬스 |
103 | 김민수 | 헬스, 골프초급 |
이렇게 표현할 수도 있지만,
제 1정규형을 활용
회원번호 | 회원이름 | 프로그램 |
101 | 강호동 | 스쿼시초급 |
102 | 손흥민 | 헬스 |
103 | 김민수 | 헬스 |
103 | 김민수 | 골프초급 |
•
각 컬럼이 하나의 값(속성)만을 가져야 한다.
•
하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 한다.
•
각 컬럼이 유일한(unique) 이름을 가져야한다.
•
컬럼의 순서가 상관없어야 한다.
•
제 1정규화를 간단하게 말하자면, 하나의 칸에는 하나의 데이터만 보관하게 하자.
제 2 정규형 (2NF)
조금 더 확장해서
회원번호 | 회원이름 | 프로그램 | 가격 | 납부여부 |
101 | 강호동 | 스쿼시초급 | 5000 | 0 |
102 | 손흥민 | 헬스 | 6000 | 1 |
103 | 김민수 | 헬스 | 6000 | 1 |
103 | 김민수 | 골프초급 | 8000 | 0 |
헬스 요금을 7000원으로 인상하려면?
•
모든 Row를 뒤져가며 헬스의 가격을 7000원으로 인상
•
비효율적
제 2정규형을 활용
•
제 1정규화까지 만족하는 상태
•
현재 테이블의 주제와 관련없는 걸럼을 다른 테이블로 뺀다
회원번호 | 회원이름 | 프로그램 | 납부여부 |
101 | 강호동 | 스쿼시초급 | 0 |
102 | 손흥민 | 헬스 | 1 |
103 | 김민수 | 헬스 | 1 |
103 | 김민수 | 골프초급 | 0 |
프로그램 | 가격 |
스쿼시초급 | 5000 |
헬스 | 6000 |
골프초급 | 8000 |
•
프로그램 테이블에서 헬스 Row에 해당하는 가겨 하나만 수정해도 싹 다 변경된다.
•
손흥민은 얼마를 내야할까? → 회원관리 테이블만 보고서는 알 수 없다. JOiN 해야 알 수 있다.
제 2정규형의 정확한 정의
•
partial dependency(부분적 종속)을 제거한 테이블
•
partial dependency?
◦
Composite primary key(복합키)에 대한 개념부터
▪
위 테이블에서 보면 하나의 속성에 대한 값으로 모든 열을 유니크하게 구분 못한다(primary key 없음)
▪
회원번호와 프로그램을 합치면 유니크하게 구분이 가능한 것을 알 수 있다 (이게 Composite primary key)
◦
가격은 프로그램에 종속 → 이 경우에 partial dependency가 있다고 한다.
◦
Composite primary key 중에 특정 컬럼에만 종속되는 것을 Partial Dependency가 있다고 하는 것.
제 3정규형 (3NF)
프로그램 | 가격 | 강사 | 출신대학 |
스쿼시 | 5000 | 김을용 | 서울대 |
헬스 | 6000 | 박덕팔 | 연세대 |
골프 | 8000 | 이상구 | 고려대 |
골프중급 | 9000 | 이상구 | 고려대 |
개인피티 | 6000 | 박덕팔 | 연세대 |
만약 이상구씨의 출신대학이 중앙대라면?
이것 또한 이상구씨에 해당하는 모든 열을 중앙대로 바꿔야한다.
제 3정규형을 활용
프로그램 | 가격 | 강사 |
스쿼시 | 5000 | 김을용 |
헬스 | 6000 | 박덕팔 |
골프 | 8000 | 이상구 |
골프중급 | 9000 | 이상구 |
개인피티 | 6000 | 박덕팔 |
강사 | 출신대학 |
김을용 | 서울대 |
박덕팔 | 연세대 |
이상구 | 고려대 |
박덕팔 | 연세대 |
•
출신 대학은 프로그램 테이블의 Primary key(프로그램)와 전혀 상관없다.
•
그저 강사라는 컬럼에만 종속되어 있을 뿐
•
이렇게 일반 컬럼에만 종속된 컬럼을 다른 테이블로 빼는 것이 제 3정규화이다.
제 3정규형의 정확한 정의
•
기본키를 제외한 속성들 간의 이행 종속성(Transitive Dependency)이 없어야 한다.
•
이행 종속성 이란?
◦
A→B, B→C 일때, A→C 가 성립하면 이행 종속!
◦
강사(A)는 프로그램(B)에 종속적이고(A→B), 출신대학(B)은 강사(C)에게 종속적(B→C)
◦
결국, 프로그램을 알면 출신대학까지 알 수 있는 것 → 이런게 없어야 한다..
BCNF (Boyce-Codd Normal Form) : 제 3정규형의 강화된 버전
회원번호 | 프로그램 | 강사 |
101 | 스쿼시 | 김을용 |
102 | 헬스 | 박덕팔 |
103 | 헬스 | 김종국 |
103 | 골프초급 | 이상구 |
•
회원번호, 프로그램이 복합키(기본키)로 강사를 알 수 있다.
•
같은 과목을 다른 강사가 가르칠 수도 있어서 프로그램- > 강사 종속은 성립하지 않는다 (제 2정규형 만족)
•
복합키를 제외한 속성에서 이행 종속성 없다 (애초에 복합키 제외하면 속성 한 개 밖에 없어) (제 3정규형 만족)
•
강사를 알면 그 강사가 어떤 과목을 가르치는지 알 수 있으므로 강사→과목 종속이 성립
◦
후보키 집합이 아닌 컬럼이 결정자가 되어버린 상황을 BCNF를 만족하지 않는다고 한다.
*결정자 : 어떤 속성의 값이 다른 속성의 값을 고유하게 결정할 수 있음
회원번호 | 강사 |
101 | 김을용 |
102 | 박덕팔 |
103 | 김종국 |
103 | 이상구 |
강사 | 프로그램 |
김을용 | 스쿼시 |
박덕팔 | 헬스 |
김종국 | 헬스 |
이상구 | 골프초급 |
•
강사가 기본키로 세팅된 테이블로 분해했다
•
후보키(강사)가 아닌 컬럼(프로그램)이 결정자가 되지 않았다 → BCNF 만족
제 4정규형 (4NF)
회원번호 | 프로그램 | 동호회 |
101 | 스쿼시 | 낚시 |
102 | 헬스 | 요리 |
103 | 헬스 | 낚시 |
103 | 헬스 | 요리 |
103 | 골프초급 | 낚시 |
103 | 골프초급 | 요리 |
•
103번을 주목해보면, 이 회원은 헬스, 골프초급을 수강하고, 낚시, 요리 동호회에 가입중이다.
•
이렇게 되면 103번 회원 번호 하나에 프로그램 여러개와 취미 여러개가 종속되어 있다.
•
프로그램과 동호회는 관계없는 독립적인 관계지만, 중복이 발생한다.
•
회원번호라는 컬럼에 독립적인 관계인 프로그램과 동호회가 다치 종속 되어 있는 상태.
제 4정규형을 활용
•
BCNF를 만족하는 상태
•
3개 이상의 컬럼을 지니는 상황에서 2개 이상의 컬럼이 하나의 컬럼에 다치 종속되지 않아야 한다.
•
다치 종속 이란?
◦
A→B 일 때, 하나의 A 값에 여러 개의 B 값이 존재하면, 다치 종속성을 가진다고 한다.
회원번호 | 프로그램 |
101 | 스쿼시 |
102 | 헬스 |
103 | 헬스 |
103 | 골프초급 |
회원번호 | 동호회 |
101 | 낚시 |
102 | 요리 |
103 | 낚시 |
103 | 요리 |
•
위 2개의 테이블은 여전히 다치 종속성을 가지지만
•
하나의 컬럼에 다치 종속되지는 않기 때문에, 제 4정규형을 만족한다
•
중복이 사라지는 효과를 얻을 수 있었다.
제 5정규형 (5NF)
중복을 제거하기 위해 분해할 수 있을 만큼 분해하는 것.
•
제 4정규형을 만족하는 상태
•
조인 종속이 없어야한다.
•
조인 연산을 했을 때 손실이 없어야한다 (원상복구 할 수 있도록 분리한 상태)
*조인 종속?
•
다치 종속의 좀 더 일반화된 형태
•
만약 하나의 릴레이션을 여러 개의 릴레이션으로 무손실 분해했다가 다시 결합할 수 있다면 조인 종속이라 한다
•
분해한걸 다시 원상복구 시킬 수 있느냐?
너무 이상적이어서 조인 종속이 존재하는 릴레이션이 사용하기 편하다.
정규화의 장단점
장점
•
데이터베이스 변경 시 이상현상(Anomaly)를 제거할 수 있다.
•
새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 된다.
•
데이터베이스와 연동된 응용 프로그램에 최소한 영향만을 미치게 되어 응용 프로그램의 생명을 연장시킨다.
단점
•
릴레이션의 분해로 인해 릴레이션 간의 JOIN 연산이 많아진다.
•
Query에 대한 응답 시간이 느려질 수도 있다.