제 2정규화(Second Normal Form, 2NF)

💡 제2정규형 (2NF): 1NF를 만족하며, 모든 비기본 키 속성이 전체 기본 키에 대해 완전 함수적 종속을 가져야 합니다. 즉, 기본 키의 일부에만 종속된 속성이 없어야 한다.

 

2NF를 만족하지 못하는 데이터 예제

학생들이 수강하는 과목과 그 과목의 담당 교수에 대한 정보를 포함하는 테이블을 설계.

여기서 pk는 {학생ID, 과목코드}의 복합 키이다.

 

수강 정보 테이블

학생ID	과목코드	과목명	담당 교수
1	MAT101	수학	김교수
1	SCI101	과학	이교수
2	MAT101	수학	김교수
2	ENG101	영어	박교수

위 테이블에서 '과목명'과 '담당 교수'는 과목코드에 부분적으로 종속되어 있다. 즉, 학생ID와는 직접 적인 관련이 없다. 이는 2NF를 위반하는 데이터의 형태 이다.

 

 

2NF 문제 해결

2NF를 만족시키기 위해 부분 종속성을 제거해야 한다. 이를 위해 위 테이블을 분해하여 부분적 종속성을 제거할 수 있다.

1. 수정 정보 테이블 : 학생ID, 과목코드

2. 과목 정보 테이블 : 과목코드, 과목명, 담당 교수

 

 

 

수강 정보 테이블

학생ID	과목코드
1	MAT101
1	SCI101
2	MAT101
2	ENG101

 

 

과목 정보 테이블

과목코드	과목명	담당교수
MAT101	수학	김교수
SCI101	과학	이교수
ENG101	영어	박교수

 

이제 수강 정보 테이블은 학생과 수강하는 과목의 관계만을 표현하며, 과목 정보 테이블은 각 과목의 세부 정보를 관리한다. 이러한 분해는 각 테이블이 2NF를 만족하도록 하며, 데이터 중복을 줄이고 유지보수를 쉽게 할 수 있다.

 

아래 테이블은 정규화를 만족했을까? -> 아니

영화ID	제목	감독 이름	감독 주소	개봉일	장르
1	인셉션	크리스토퍼 놀란	LA 엘름 스트리트 1234번지	2010-07-16	공상 과학
2	다크 나이트	크리스토퍼 놀란	LA 엘름 스트리트 1234번지	2008-07-18	액션
3	타이타닉	제임스 카메론	NY 메이플 애비뉴 5678번지	1997-12-19	로맨스
4	아바타	제임스 카메론	NY 메이플 애비뉴 5678번지	2009-12-18	공상 과학
5	인터스텔라	크리스토퍼 놀란	LA 엘름 스트리트 1234번지	2014-11-07	공상 과학

테이블은 감독 이름과 감독 주소가 제목에 부분적으로 종속되어 있어 2정규화를 만족하지 못한다. 이는 각 감독에 대한 주소 정보가 반복되어 데이터 중복 및 업데이트 이상(anomalies)을 초래할 수 있다. 예를 들어, 감독의 주소가 변경될 경우 여러 행을 수정해야 하는 문제가 발생한다.

 

 

정규화 실습하기

영화에 관련된 데이터 베이스를 구축하자.

 

영화 제목, 개봉 연도, 매출액, 평점, 관객수, 배우, 배우이름, 배우 정보, 영화에 누가 출현했는지 설계

영화, 배우, 출연 (정규화를 만족해서 설계해보자.)

create database movie;
use movie;

-- 영화, 배우, 출연
create table 영화(
	영화ID int primary key,
	영화제목 varchar(50) not null,
    개봉연도 varchar(20) not null,
    매출액 decimal(10,2),
    평점 int,
    관객수 int
);

create table 배우(
	배우ID int primary key,
	배우이름 varchar(50),
    배우나이 int,
    배우소속사 varchar(50)
);

create table 출연(
	영화ID int,
    배우ID int,
    foreign key(영화ID) references 영화(영화ID),
    foreign key(배우ID) references 배우(배우ID)
);

 

❗설계를 할 때 원자성(독립적인 무언가), 어떻게 엮을 지 생각
각각의 테이블을 설계할 때 그 테이블을 한 번에 수정 할 수 있는지
그리고 설계한 테이블을 이어주는 테이블 영화와 배우가 각각 원자성을 가지고 출연으로 영화와 배우를 연결

맨 처음 영화, 배우 먼저 테이플을 설계하고 그 뒤에 이 영화, 배우 테이블을 이어줄 출연이라는 테이블을 생성