RDB 정규화

관계형데이터베이스의 테이블을 효율적으로 설계하기위한 일련의 규칙, 즉 정규화를 말합니다. 데이터 중복을 최소화하고, 데이터 무결성을 향상시키며, 불필요한 데이터 변경을 방지하는것을 목표로 합니다.

일반적으로 3NF까지 도달하면 정규화가 됐다고 하고 있습니다. 이후 단계를 하는것은 복잡성이 증가하고 비효율적일 가능성이 높습니다.

예제테이블 설명

수강신청한 학생, 강의, 교수의 정보를 담은 정규화 되지 않은 테이블을 만들어 생성합니다.

CREATE TABLE student_courses_0nf (
    student_id INT,
    student_name VARCHAR(50),
    student_phone VARCHAR(20),
    student_major VARCHAR(50),
    course_id TEXT,  
    course_name TEXT,
    professor TEXT,
    department TEXT,
    professor_phone TEXT,
    grade TEXT
);

1NF: 원자값 보장

수강신청한 course_id ,course_name , professor 들이 여러개의 값을 가지고 있습니다. 1NF는 이를 원자값으로 가지게하는 작업을 말합니다.

CREATE TABLE student_courses_1nf (
    student_id INT,
    student_name VARCHAR(50),
    student_phone VARCHAR(20),
    student_major VARCHAR(50),
    course_id VARCHAR(10),
    course_name VARCHAR(50),
    professor VARCHAR(50),
    department VARCHAR(50),
    professor_phone VARCHAR(20),
    grade CHAR(1)
);

1NF 문제점: 삽입/삭제/수정 이상

미적분학 김교수가 갑작스럽게 퇴직하여 다른 교수로 업데이트한다고 했을 때 강의를 듣는 김철수, 이영희 학생에 대한 ROW 2개를 업데이트를 하게됩니다. 이러한 과정에서 업데이트가 원활하게 이루어지지 않는다면 데이터 일관성이 깨지게 됩니다.

2NF: 부분 함수 종속성 제거

현재 프라임키 student_id , course_id 에는 non-attribute key들이 부분 종속되어 있는걸 확인 할 수 있습니다.

부분 함수 종속 관계들:

• student_name, student_phone → student_id (student_id에만 종속)
• course_name, professor, department → course_id (course_id에만 종속)

완전 함수 종속:

• grade → (student_id, course_id) (복합키 전체에 종속)

-- 학생 테이블 (student_id에만 종속되는 속성들)
CREATE TABLE students_2nf (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    student_phone VARCHAR(20),
    student_major VARCHAR(50)
);

-- 과목 테이블 (course_id에만 종속되는 속성들)
CREATE TABLE courses_2nf (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(50),
    professor VARCHAR(50),
    department VARCHAR(50),
    professor_phone VARCHAR(20)
);

-- 수강 성적 테이블 (프라임키 전체에 종속되는 속성들)
CREATE TABLE enrollments_2nf (
    student_id INT,
    course_id VARCHAR(10),
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students_2nf(student_id),
    FOREIGN KEY (course_id) REFERENCES courses_2nf(course_id)
);

2NF문제점: 이행적 함수 종속성

과목테이블의 기본키 course_id 가 professor_phone , department 결정되는게 아니라 교수의 이름 professor 이 professor_phone , department 결정하게 됩니다.

🤔 이행적 함수 종속성이 뭔가요?
A → B, B → C 관계가 성립할 때, A → C가 성립하는걸 의미합니다.데이터 베이스에서는 기본키(A)가 아닌 일반 컬럼(B)이 다른 일반 컬럼(C)을 결저하는 것을 말합니다.

3NF: 이행적 함수 종속성 제거

-- 교수 테이블
CREATE TABLE professors_3nf (
    professor_name VARCHAR(50) PRIMARY KEY,
    professor_phone VARCHAR(20),
    department_name VARCHAR(50)
);

-- 과목 테이블 (3NF)
CREATE TABLE courses_3nf (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(50),
    professor_name VARCHAR(50),
    FOREIGN KEY (professor_name) REFERENCES professors_3nf(professor_name)
);

-- 학생 테이블 (변경 없음)
CREATE TABLE students_3nf (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    student_phone VARCHAR(20),
    student_major VARCHAR(50)
);

-- 수강 성적 테이블 (변경 없음)
CREATE TABLE enrollments_3nf (
    student_id INT,
    course_id VARCHAR(10),
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students_3nf(student_id),
    FOREIGN KEY (course_id) REFERENCES courses_3nf(course_id)
);