SQL 정리(제 1정규화, 제 2정규화, 제3정규화, BCNF정규화)

제 1 정규화

### 제1정규화 (1NF) ###

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

# cf) 원자값
# : 테이블의 각 컬럼에 저장된 값이 더 이상 분해될 수 없는 가장 작은 단위의 데이터를 의미
# > 데이터의 단순성과 명확성을 보장

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

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

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

 

제2 정규화

### 제2정규형 ###

# 2NF의 정의
# : DB 정규화의 두 번째 단계
# : 1NF(제1정규형)을 만족하면서 모든 비기본 속성이 기본키에 완전히 함수적 종속인 상태

# cf) 완전 함수적 종속
# : 속성이 기본키 전체에 종속되어 있으며 기본키의 일부분만으로는 결정할 수 없는 경우

# cf) 부복 종속
# : 속성이 기본키의 일부에만 종속되는 경우
# EX) 기본키가 복합키인 경우 그 중 일부 키에만 종속된다면 부분 종속

-- 부서 정보를 저장할 테이블
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', 'busan', 's2'); -- human resources 인사 관리 부서 

insert into employees 
values
('e1', 'sales'),
('e2', 'sales'),
('e3', 'HR');

 

제3정규화

/*
제 3 정규형
    
    3NF의 개념
    : 이미 2NF를 만족하는 테이블에서 모든 비기본 속성이 기본키에만 함수적으로 종속
    > 비기본 속성이 다른 비기본 속성에 종속되지 않아야 함
    
    cf) 이행적 종속성
    : 어떤 속성 A가 다른 속성 B에 종속되고, B가 또 다른 속성 C에 종속된 경우
    >> A가 C에 이행적으로 종속됨
    
    > 제 3정규형의 경우 '이행적 종속성을 제거'
    
    학번(PK) | 이름 | 학과ID | 학과명| 학과위치
*/
-- 학과 테이블(학과 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)
);

insert into departments_3 values (101, '컴퓨터공학과', '부산'), (201, '전자공학', '대구');
insert into students_3 values (1, 'AAA', 101), (2, 'BBB', 201), (3,'CCC', 101);

# 학생번호> 강의 ID
# 강의 ID> 강의 위치

select * from departments_3;
select S.student_id, S.name, D.department_name, D.location from 
Students_3 S
join departments_3 D
on S.department_id= D.department_id;

BCNF정규화

/*
보이스-코드 정규화
    : 모든 결정자가 후보키가 되어야 하는 DB 정규화 형태
     3NF의 조건을 충족하며 추가로 모든 결정자가 후보키인 조건을 만족
     
     cf) 후보키
     : 테이블에서 각 행을 유일하게 식별할 수 있는 최소한의 속성의 집합
     cf) 결정자
     : 하나의 속성 또는 속성 집합이 다른 속성을 결정할 수 있는 경우
     > 해당 속성 집합을 '결정자'라고 표현
*/

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);