데이터베이스 정규화 메모
정규화
데이터베이스에서 정규화란 데이터의 중복을 최소화하고, 데이터 무결성을 개선하기 위해 데이터를 정규형에 맞도록 구조화하는 과정을 의미한다.
일반적(비공식적)으로 관계형 데이터베이스에서 제 3정규화를 완료하면 해당 데이터베이스가 정규화 되었다고 판단한다. 3NF 테이블의 대부분이 삽입, 변경, 삭제 이상이 없으며, 3NF 테이블의 대부분이 BCNF, 4NF, 5NF를 만족하기 때문이다.
이상 현상 (Anomaly)
불필요한 데이터 중복으로 인해 릴레이션에 대한 데이터 삽입, 수정, 삭제 연산에 발생하는 부작용
- 삽입 이상 : 데이터 삽입시 의도하지 않은 값까지 추가해야 데이터가 삽입되는 현상
- 갱신 이상 : 속성값 갱신 시 일부 튜플만 갱신되어 모순이 발생하는 현상
- 삭제 이상 : 데이터 삭제 시 의도와 다른 값들까지 삭제되는 현상
정규화 단계
제 1정규화 (1NF) : 하나의 속성에 하나의 값
각 속성에는 하나의 값(원자값)만 존재해야 하므로, 하나의 필드에 여러 값이 필요한 경우 각각 별도의 튜플로 나타내야 한다.
제 2정규화 (2NF) : 부분 함수 종속 제거
기본키(primary key)에 속하지 않은 속성 모두가 기본키에 완전 함수 종속이어야 한다.
복합키인 경우 기본키의 일부에만 종속된 속성을 제거(부분 함수 종속 제거)
모든 비기본 키 속성이 기본 키 전체에 종속되어야 한다.
제 3 정규화 (3NF) : 이행 함수 제거
기본키가 아닌 모든 속성이 기본키에만 직접적으로 종속되어야 하며, 다른 속성에 종속되지 않도록 해야 한다. 다른 속성을 거치는 간접적인 종속을 제거
BCNF (보이스-코드 정규화) : 후보키만 결정자
모든 결정자가 후보키여야 한다. 어떤 속성이 다른 속성을 결정할 때, 그 결정자는 반드시 후보키의 일부여야 한다.
제 4정규화 (4NF) : 다치 종속 제거
관계가 없는 둘 이상의 속성이 공통된 기본키에 대해 1:N관계를 가지지 않게 분리해야 한다.
제 5정규화 (5NF) : 조인 종속 제거(이용)
후보키로부터 유도되지 않는 조인 종속은 제거하기 위해 분리해야 한다.
제 1 정규화
각 속성에는 하나의 값만 존재한다.
결과
- 데이터 수조의 일관성 유지 -> 명확한 표현
- 검색∙정렬 유리
- 반복 속성 제거로 쿼리 단순화
예시
| 학번 | 이름 | 과목 | 점수 |
|---|
| 1001 | 홍길동 | 수학, 영어 | 95, 88 |
| 1002 | 김철수 | 수학 | 85 |
| 학번 | 이름 | 과목 | 점수 |
|---|
| 1001 | 홍길동 | 수학 | 95 |
| 1001 | 홍길동 | 영어 | 88 |
| 1002 | 김철수 | 수학 | 85 |
과정
- 하나의 튜플에서
과목속성이 여러 값을 가질 수 있기에 과목은 원자값을 가지지 않음
- 튜플을 증가 시키고,
과목속성의 값을 분리
제 2 정규화
제 1 정규형을 만족하면서, 기본키(primary key)에 속하지 않은 속성 모두가 기본키에 완전 함수 종속이여야 한다.
- 기본키가 복합키인 경우 기본키의 일부에만 종속된 속성을 제거(부분 함수 종속 제거)하고, 분리해 완전 함수 종속을 만족 시킨다.
결과
- 데이터 중복 감소
- 부분 종속에 의한 삽입∙삭제 이상 제거
- 테이블 구조 명확화 -> 각 테이블이 하나의 개념을 표현
예시
2NF 위배
| 학번 | 과목 | 이름 | 점수 |
|---|
| 1001 | 수학 | 홍길동 | 95 |
| 1001 | 영어 | 홍길동 | 88 |
2NF 만족
- 학생 정보
- 성적 정보
과정
- 정규화 전 테이블에서 기본키는
학번 + 과목
- 그 외의 속성 중
이름은 과목에는 독립적이면서 학번에만 종속적이기 때문에 중복이 발생
- 따라서
이름을 분리해 부분 함수 종속을 제거
제 3 정규화
제 2 정규형을 만족하면서, 이행적 함수 종속성이 없어야 한다.
- 기본키에 속하지 않은 모든 속성이 기본키에 이행적 함수 종속이 아니게, 즉 기본키 이외의 속성이 그 외 다른 속성에 결정자가 되지 않게 한다.
결과
- 속성 간 종속성 제거로 유지보수성 증가
- 수정 이상 감소
- 데이터 무결성 향상
예시
3NF 위배
| 사원ID | 부서ID | 부서명 |
|---|
| 1 | 101 | 인사부 |
| 2 | 102 | 개발부 |
| 3 | 101 | 인사부 |
사원ID → 부서ID → 부서명: 이행적 종속 존재
3NF 만족
- 사원 정보
- 부서 정보
과정
부서ID는 사원ID에 종속적이고, 부서명은 부서ID에 종속적- 사원이 없는 임시/신규 부서 개설이나,
부서명 변경시 불필요하게 사원ID가 필요해지는 상황 발생
- 이행적으로만 관계가 있는
사원ID와 부서명을 분리하여 이행적 종속 제거
BCNF (보이스 코드 정규화)
제 3 정규형이 강화된 형태로 테이블 내의 모든 결정자가 후보키가 되어야 한다.
- 제 3 졍규형에서 허용되는 후보키가 아닌 결정자에서의 함수적 종속을 제거
결과
- 비정규적 결정자 관계에 의한 이상 제거
- 비정상적인 데이터 삽입∙삭제 상황 방지
- 관계 구조의 논리 향상 -> 모든 결정자가 키 역할을 함
예시
BCNF 위배
| 학생ID | 강의명 | 교수명 |
|---|
| 1 | DB | 이교수 |
| 2 | OS | 김교수 |
| 3 | DB | 이교수 |
강의명 → 교수명이지만 강의명은 후보키가 아님
BCNF 만족
- 강의 정보
- 수강 목록
과정
-
교수명은 학생ID에 이행적 종속이 없으며, 강의명에 종속적
-
강의를 담당한 교수명이 수정되면 모든 튜플에 대한 수정이 일어나야 함 -> 데이터 불일치 가능성 : 수정 이상
-
학생이 모두 강의를 취소한 경우 강의-교수 관계 정보가 데이터베이스 내에서 사라지게 됨 -> 삭제 이상
-
테이블을 분리하여 후보키가 아닌 강의명 속성을 후보키로 설정
제 4 정규화
제 3정규형 또는 BCNF를 만족하면서, 다치 종속성이 없어야 한다.
- 관계가 없는 둘 이상의 속성이 공통된 기본키에 대해 1:N관계를 가지는 종속을 제거 A >> B | C
결과
- 중복되는 다치 종속 데이터 제거
- 데이터 삽입 시 불필요한 조합 방지
- 독립적인 항목을 분리해 데이터 일관성 유지
예시
4NF 위배
| 학생ID | 언어 | 자격증 |
|---|
| 1 | 영어 | 정보처리기사 |
| 1 | 일본어 | 정보처리기사 |
| 1 | 영어 | 컴퓨터활용능력 |
| 1 | 일본어 | 컴퓨터활용능력 |
- 한 학생이 여러 언어나 여러 자격증 소유 가능
- 언어∙자격증 간에는 직접적인 관계가 없음 -> 다치 종속으로 인한 데이터 중복
4NF 만족
- 보유 언어
- 보유 자격증
과정
- 하나의 학생ID에 여러 언어가 포함될 수 있음
- 하나의 학생ID에 여러 자격증이 포함될 수 있음
- 한 학생(ID 1번인 학생)이 추가적으로 여러 언어(불어)와 자격증(ERP, CAD)을 습득할 경우 8개의 튜플이 생성되어야 함
| 학생ID | 언어 | 자격증 |
|---|
| 1 | 영어 | 정보처리기사 |
| 1 | 영어 | 컴퓨터활용능력 |
| 1 | 영어 | ERP |
| 1 | 영어 | CAD |
| 1 | 일본어 | 정보처리기사 |
| 1 | 일본어 | 컴퓨터활용능력 |
| 1 | 일본어 | ERP |
| 1 | 일본어 | CAD |
| 1 | 불어 | 정보처리기사 |
| 1 | 불어 | 컴퓨터활용능력 |
| 1 | 불어 | ERP |
| 1 | 불어 | CAD |
- 독립적인 두 속성을 분리하여 다치 종속을 제거
- 다치 종속이 제거된 상태의 경우 위의 상황에서 3개의 튜플만 생성됨
- 보유 언어
- 보유 자격증
| 학생ID | 자격증 |
|---|
| 1 | 정보처리기사 |
| 1 | 컴퓨터활용능력 |
| 1 | ERP |
| 1 | CAD |
제 5 정규화
제 4정규형을 만족하면서, 조인 종속성이 없어야 한다.
- 하나의 테이블을 여러개로 분리한 후 다시 조인했을때 분리하기 전과 동일해야 한다.
결과
- 복잡한 조인에 의한 데이터 중복 제거
- 삽입∙삭제 이상 방지
- 데이터 관계의 분리와 정합성 유지
예시
5NF 위배
| 제품ID | 부품 | 공급업체 |
|---|
| A | 나사 | 철물상사 |
| A | 고무패킹 | 철물상사 |
| A | 나사 | 산업마트 |
| A | 고무패킹 | 산업마트 |
- 제품 A는 나사와 고무패킹 필요
- 공급업체는 부품별로 공급 가능
- 조인 종속으로 인해 모든 조합이 나타나야 함 -> 중복 행 발생
5NF 만족
- 제품-부품
- 제품-공급
- 부품-공급
| 부품 | 공급업체 |
|---|
| 나사 | 철물상사 |
| 나사 | 산업마트 |
| 고무패킹 | 철물상사 |
| 고무패킹 | 산업마트 |
과정
제품ID, 부품, 공급업체속성이 각기 관계가 있어 3개 이상의 관계로 표시가 가능
제품ID-부품부품-공급업체제품ID-공급업체
- 이 3개의 관계를 조인하면 분리하기 전의 상태가 나타남 + 후보키(
제품ID + 부품 + 공급업체)로부터 유도되지 않음(함수 종속 X) -> 조인 종속성 존재
정규화 하기 전 위험성
- 새로운 부품을 추가려면
제품ID와 공급업체 모두 필요 -> 일부만으로 추가 불가
- 실수로 일부만 수정 시 무결성이 깨질 위험
