[SQL] 정규화 (Nomalization)

정규화(Nomalization)

• 테이블 간에 중복된 데이터를 허용하지 않는 것
• 중복된 데이터를 허용하지 않음으로 무결성을 유지하는 방법

 

정규화 종류

• 정규형: 테이블이 정규화된 정도, normal form(nf)
• 제 1정규형(1NF), 제 2정규형(2NF), 제 3정규형(3NF), BCNF(Boyce-Codd NF), 제 4정규형(4NF), 제 5정규형(5NF)

 

1. 제 1정규형 (1NF)

• DB의 정규화 과정 중 첫 단계
• 테이블에서 모든 필드(속성)가 원자값(분할 불가능한 값)을 가져야 한다는 규칙
• 각 컬럼에 반복되는 그룹이나 다중값을 허용하지 않음
• 각 행이 유일한 값을 가지도록 설계

제 1 정규형의 장점

• 데이터 중복 감소: 다중값이 구현된 컬럼을 개별 행으로 분리
• 데이터 무결성 보장: 각 행이 하나의 데이터만을 참조하도록 분리

제 1 정규화의 단점

• 조회 성능 저하: 데이터 분산으로 인한 조인 연산의 불필요한 사용 증가
• 공간 사용 증가: 데이터가 세분화되기 때문에 각각의 고유 식별자 필요

use `정규화`;
create table `제1정규형`(
	student_id int,
    student_name varchar(50),
    course_name varchar(100)
);

2. 제 2정규형(2NF)

• DB 정규화의 두 번째 단계
• 1NF(제 1정규형)을 만족하면서 모든 비기본 속성이 기본키에 완전히 함수적 종속인 상태
• 완전 함수적 종속 : 속성이 기본키 전체에 종속되어 있으며 기본키의 일부분 만으로는 결정할 수 없는 경우
• 부분 종속 : 속성이 기본키의 일부에만 종속되는 경우

use `정규화`;

-- 부서 정보를 저장할 테이블
create table departments (
	department varchar(50) primary key,
    location varchar(50),
    supervisor_id varchar(10)
);

create table employees (
	employee_id varchar(10) primary key,	-- 기본 키
    department varchar(50),		-- 기본 키에 완전 종속
    foreign key (department) references departments(department)
);

-- 부서 데이터 삽입
insert into departments
values
	('sales', 'seoul', 's1'),
	('hr', 'seoul', 's2');
    
insert into employees
values 
	('e1', 'sales'),
	('e2', 'sales'),
	('e3', 'hr');

 

3. 제 3정규화(3NF)

• 이미 2NF(제 2정규형)을 만족하는 테이블에서 모든 비기본 속성이 기본키에만 함수적으로 종속
• 비기본 속성이 다른 비기본 속성에 종속되지 않아야 함
• 이행적 종속성 : 어떤 속성 A가 다른 속성 B에 종속되고, B가 또 다른 속성 C에 종속된 경우 A가 C에 이행적으로 종속됨
• 제 3정규형의 경우 이행적 종속성을 제거

-- 학과 테이블(학과ID, 학과명, 학과위치를 별도로 분리)
create table departments_3 (
	department_id int primary key,
    department_name varchar(100),
    location varchar(100)
);

-- 학생 테이블 
create table students_3 (
	student_id int primary key,
    name varchar(100),
    department_id int,
    foreign key (department_id) references departments_3 (department_id)
);

-- drop table departments_3;	-- 외래 키로 연결되어 있으므로 삭제 불가 
insert into departments_3
values
	(101, '컴공', '서울'),
	(201, '전자공학', '부산');
    
insert into students_3
values 
	(1, '이기석', 101),
	(2, '박영준', 201),
	(3, '이승아', 101);
    
    # 학생번호 > 강의 ID
    # 강의 ID > 강의 위치
    
    select * from departments_3;
    
    select S.student_id, S.name, D.department_name, D.location
    from
		students_3 as S
        join departments_3 as D
        on S.department_id = D.department_id;

 

4. BCNF 정규화(Boyce-Codd NF)

• 모든 결정자가 후보키가 되어야 하는 DB 정규화 형태
• 3NF의 조건을 충족하며 추가로 모든 결정자가 후보키인 조건을 만족
• 후보키 : 테이블에서 각 행을 유일하게 식별할 수 있는 최소한의 속성 집합
• 결정자 : 하나의 속성 또는 속성 집합이 다른 속성을 결정할 수 있는 경우

create table `example_3nf` (
	std_id int,	-- 학생 번호
    std_name varchar(100),	-- 학생 이름
    course_id varchar(100),	 -- 과목 번호
    score int,	-- 점수
    course_name varchar(100),	-- 과목명
    primary key (std_id, course_id)
    # 학생 번호, 과목 번호가 복합 키로 사용
);

# 과목 번호는 과목명을 결정 
# 학생 번호 + 과목 번호가 테이블 행을 유일하게 식별 >> 복합키(후보키)

# > 과목 번호가 후보키는 아니지만 결정자 역할 
#	: 과목 번호만이 과목명을 결정 (BCNF 위반)

# BCNF에 맞는 테이블 구조
# 과목 정보를 분리하여 과목 번호가 후보키가 되는 새로운 테이블 생성 
create table 과목 (
	과목코드 varchar(10) primary key,
    과목명 varchar(50)
);

create table 성적 (
	학번 varchar(10),
    과목코드 varchar(10),
    점수 int,
    primary key (학번, 과목코드),
    foreign key(과목코드) references 과목(과목코드)
);	

insert into 과목
values
	('123', 'dbms'),
    ('234', '빅데이터');
    
insert into 성적
values
	('101', '123', 95),
	('102', '234', 85),
	('103', '123', 95);