💻 코딩 일기 : [SQL] '정규화와 관계' 편

🔔 '정규화와 관계'에 대해서 알아보자!

💟 정규화(Normalization)

중복을 최소화하고 데이터의 일관성을 유지하기 위해 데이터를 구조화하는 과정입니다. 주로 관계형 데이터베이스에서 사용되며, 일반적으로 제1정규화(1NF)부터 제5정규화(5NF)까지의 단계가 있습니다. 보통 제1정규화~제3정규화를 사용합니다.

💟 제1정규화(1NF)

1) 각 행을 유일하게 구분하는 컬럼이 존재합니다.(Primary Key(PK))
2) 모든 데이터는 원자적으로 저장됩니다.

• 같은 형식의 데이터를 저장하는 여러 컬럼이 있지 않습니다.
• 한 컬럼에 여러 값이 저장되지 않습니다.

후보키(Candidate Key)는 PRIMARY KEY가 될 가능성이 있는 키라고 합니다.
1. 유일성(Unique): 후보키로 선택된 열의 값은 모든 행에서 유일해야 합니다.
2. 최소성(Minimal): 후보키로 선택된 열의 집합 중에서 더 이상 제거할 수 없는 최소한의 열로 구성되어야 합니다.

🟦 PRIMARY KEY의 특성
1. 유일성(Unique): PRIMARY KEY로 선택된 열의 값은 모든 행에서 유일해야 합니다.
2. NOT NULL 제약조건: PRIMARY KEY로 선택된 열은 NULL 값을 허용하지 않아야 합니다.
3. 변경 가능성이 낮음(Low Mutability): PRIMARY KEY로 선택된 열의 값은 변경되지 않는 것이 좋습니다. 이는 데이터베이스의 레코드를 일관성 있게 유지하기 위함입니다.

가장 이상적인 PRIMARY KEY는 의미 없는(column name)인 경우가 많습니다. 이렇게 하면 의미 있는 정보가 아닌 간단한 값으로 레코드를 고유하게 식별할 수 있습니다. 일반적으로 대부분의 데이터베이스 시스템은 자동으로 증가하는 정수 값(AUTO_INCREMENT)을 사용하여 PRIMARY KEY를 생성합니다.

CREATE TABLE customer(
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(3),
    last_name VARCHAR(3)
);

CREATE TABLE phone_number(
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    phone_number VARCHAR(10),
    FOREIGN KEY (customer_id) REFERENCES customer(id) # 외래키 제약사항(FOREIGN KEY)
);

INSERT INTO customer (first_name, last_name) VALUES ('son','hm'),('lee','ki');
INSERT INTO phone_number VALUES (1,'1234'),(3,'7890'); #3번이 존재하지 않기 때문에 에러
INSERT INTO phone_number (customer_id, phone_number) VALUES (1,'1234');
INSERT INTO phone_number (customer_id, phone_number) VALUES (1,'4321');
INSERT INTO phone_number (customer_id, phone_number) VALUES (2,'4321');

• 외래키(FOREIGN KEY) 제약사항을 사용하여 테이블 간의 관계를 나타냅니다.
• first_name과 last_name은 각각의 고객의 이름을 나타내는데, 각각의 값은 하나의 단일 값(원자적 값)입니다. phone_number 테이블의 phone_number 컬럼도 마찬가지로 각 고객의 전화번호를 나타내는데, 하나의 값으로 이루어져 있습니다.

💟 제2정규화(2NF)

1) 1NF 테이블를 준수합니다.
2) 키가 아닌 컬럼이 키 컬럼 일부분에 의해 결정되지 않아야 합니다. 테이블의 모든 속성이 기본 키 전체에 의해 결정되어야 합니다.
INT PRIMARY KEY AUTO_INCREMENT 컬럼 있으면 해결됩니다. 그 이유는 해당 컬럼은 다른 컬럼들과의 연관이 없기 때문입니다.

💟 제3정규화

1) 2NF 테이블을 준수합니다.
2) 키가 아닌 컬럼끼리 종속되면 안됩니다.(이행적 종속)

CREATE TABLE customer(
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(3),
    last_name VARCHAR(3),
    level INT,
    FOREIGN KEY (level) REFERENCES customer_benefit(level)
);

CREATE TABLE customer_benefit(
    level INT PRIMARY KEY,
    benefit VARCHAR(100)
);

INSERT INTO customer (first_name, last_name, level)
VALUES ('son','hm',1),('lee','ki',1),
       ('kik','hs',2),('lee','jh',2), ('cap','ste',3);

INSERT INTO customer_benefit (level, benefit)
VALUES (1,'무료배송'),(2,'할인'),(3,'라운지');

• customer 테이블에 id, first_name, last_name, level, benefit 컬럼을 가지고 있다면 benefit은 level과 연관되어 있어 컬럼끼리 종속되어 있습니다. 그래서 따로 customer_benefit 테이블을 만듭니다.
• customer_benefit 테이블의 level 컬럼 사이의 외래키 관계를 통해 두 테이블을 연결시킵니다.

💟 관계(Relationship)

1. N:N 관계

• 다대다 관계 : 한 엔티티가 다른 엔티티와 여러 대 여러의 관계를 가질 때 사용합니다.
• 예를 들어 한 회원이 여러 개의 게시글에 좋아요를 받을 수 있고, 한 게시글은 여러 회원에게 좋아요를 받을 수 있습니다.
• 다대다 관계를 표헌하기 위해서 중간 테이블을 사용합니다. 중간 테이블은 두 엔티티 사이의 관계를 나타내며, 일반적으로 두 엔티티 기본 키를 외래 키로 참조합니다.

CREATE TABLE board(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20),
    content VARCHAR(20)
);

CREATE TABLE member(
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(20),
    info VARCHAR(20)
);

# 다대다 관계의 테이블의 중간테이블 생성
CREATE TABLE board_like(
    board_id INT REFERENCES board(id),
    member_id INT REFERENCES member(id),
    PRIMARY KEY (board_id, member_id)
);

• 두 외래 키는 각각 board 테이블과 member 테이블의 기본 키를 참조합니다.

2. 1:N 관계

• 일대다 관계 : 한 엔티티가 다른 엔티티와 여러 대 하나의 관계를 가질 때 사용됩니다.
• 예를 들어 직원이 여러 개의 급여 정보를 가질 수 있지만, 각 급여 정보는 오직 한 명의 직원에게만 속합니다.

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    address VARCHAR(10),
    salary INT
);

CREATE TABLE employee_salary(
    employee_id INT PRIMARY KEY REFERENCES employee(id),
    salary INT
)

• employee_id는 employee 테이블의 id를 참조하는 외래키이며 각각의 급여가 어떤 직원에게 속하는지 나타냅니다.