[MySQL] 정규화

정규화(Normalization)란 데이터베이스 테이블을 효율적으로 구조화하는 작업을 말한다.

중복 데이터를 줄이고 데이터가 꼬이지 않도록 테이블을 나누는 과정을 통해 데이터 무결성을 유지하고 유지보수의 편리성을 증가시킨다.

정규화의 단계

1. 제1정규형 (1NF)

- 하나의 칸(셀)안에는 하나의 값만 들어가야 한다. 한 칸에 쉼표로 여러 값을 넣으면 안된다.

 

2. 제2정규형 (2NF)

- 제1정규형을 만족하고, 기본키가 아닌 모든 속성이 기본키에 완전 종속되어야 한다.

 

3. 제3정규형 (3NF)

- 제2정규형을 만족하고, 기본키가 아닌 모든 속성이 기본키에 이행적 종속을 만족하지 않아야 한다.

* 이행적 종속 : A → B 이고 B → C 이면 A → C 가 성립하는 관계를 말한다.

CREATE TABLE student_course (
    student_id INT,
    student_name VARCHAR(50),
    course_name VARCHAR(50),
    professor_name VARCHAR(50),
    professor_phone VARCHAR(20)
);

위의 테이블로 정규화를 진행하면 다음과 같다.

 

학생 테이블

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(20)
);

 

교수 테이블

CREATE TABLE professor (
    professor_id INT PRIMARY KEY,
    professor_name VARCHAR(20),
    professor_phone VARCHAR(20)
);

 

과목 테이블

CREATE TABLE course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    professor_id INT,
    FOREIGN KEY (professor_id) REFERENCES professor(professor_id)
);

 

과목 테이블은 professor_id를 통해 교수 테이블과 연결된다.

 

수강 테이블

CREATE TABLE enroll (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    PRIMARY KEY (student_id)
);

외래키 (Foreign Key)

외래키는 관계형 데이터베이스에서 한 테이블의 컬럼 중 다른 테이블의 행을 식별할 수 있는 키를 말한다.

CREATE TABLE profile (
    idx INT NOT NULL,
    height DOUBLE,
    weight DOUBLE,
    mbti VARCHAR(10),
    FOREIGN KEY (idx) REFERENCES member(idx)
);

위와 같이 프로필 테이블을 작성한다면 profile.idx는 member.idx를 참조하게 된다. 즉 member에 있는 회원만 profile을 추가할 수 있다.

insert into profile values(1, 160, 50, 'ISTJ');
insert into profile values(3, 170, 70, 'ESTP');
insert into profile values(4, 175, 80, 'ENFP');
insert into profile values(6, 175, 80, 'ENFJ');
-- insert into profile values (7, 175, 80, 'ENFJ'); 외래키 제약조건 위배

이때 member테이블에 idx=7에 해당하는 회원이 없으므로 마지막 쿼리는 외래키 제약조건 위배로 인해 실패하게된다.

JOIN

JOIN은 두 개 이상의 테이블을 연결해서 조회하는 SQL문법이다. 정규화로 인해 여러개로 나눠진 테이블에서 필요한 정보를 한 번에 보기 위해 JOIN을 사용한다.

왼쪽 테이블 (member)

오른쪽 테이블 (profile)

inner join

 

두 테이블에 모두 연결되는 데이터만 조회한다. (교집합)

select m.idx, m.userid, m.name, m.gender, p.mbti from member as m inner join profile as p on m.idx = p.idx;

inner join의 결과

left join

 

왼쪽 테이블의 데이터는 모두 보여주고, 오른쪽 테이블에 연결되는 데이터가 있다면 함께 보여준다. 이때 프로필이 없는 회원은 NULL값으로 출력된다.

select m.idx, m.userid, m.name, m.gender, p.mbti from member as m left join profile as p on m.idx = p.idx;

left join의 결과

right join

 

오른쪽 테이블의 데이터는 모두 보여주고, 왼쪽 테이블에 연결되는 데이터가 있다면 함께 보여준다. inner join과 같은 결과가 나온다.

select m.idx, m.userid, m.name, m.gender, p.mbti from member as m right join profile as p on m.idx = p.idx;

right join의 결과

cross join

 

두 테이블의 모든 조합. 

select userid, name, gender, mbti from member cross join profile;

cross join의 결과