[MySQL] 35. 정규화(1)

[MySQL] 35. 정규화(1)

1. 정규화란?

2. 이상 현상 설명

3. 시나리오 코드: 정규화를 통한 해결 방안

 

---

1. 정규화란?

(1) 정규화(Normalization)의 개념

 데이터베이스에서 정규화는 데이터를 조직화하고, 중복을 최소화하기위해 데이터베이스 테이블의 설계를 시스템적으로 개선하는 과정이다. 정규화를 통해 데이터의 무결성과 일관성을 유지하면서, 효율적인 데이터 관리가 가능해진다.

 

(2) 정규화의 목표

 테이블 내에서 발생할 수 있는 데이터의 중복을 최소화하여, 이상 현상을 방지하고, 데이터의 무결성을 유지하는 것이다.

 

*데이터의 무결성(Integrity): 데이터의 무결성은 데이터베이스 관리 시스템에서 데이터의 정확성, 일관성, 신뢰성을 유지하는 특성을 이야기한다. 데이터베이스 내의 정보가 정확하고 일관된 상태로 유지되고, 시스템에서 정의한 규칙, 제약 조건, 비즈니스 규칙을 준수해 데이터의 품질을 보장하며, 응용 프로그램과 사용자가 신뢰할 수 있는 데이터에 접근할 수 있도록 하는 것.

 

*이상 현상(Anomaly)는 테이블에서 일부 속성들의 종속으로 인해, 데이터의 중복이 발생하며 이 중복으로 인해 테이블 및 데이터 조작 시 문제가 생기는 현상을 의미한다. 

---

2. 이상 현상 설명

(1) 삽입 이상(Insertion Anomaly) : 새로운 부서가 추가되지만, 해당 부서에 아직 직원이 배치되지 않았다면, 부서와 위치 정보를 삽입할 방법이 없다. 예를 들어, 'IT' 부서가 ‘부산'에 새로 설립되었지만, 해당 부서의 직원이 아직 확정되지 않은 경우, 이 정보를 저장할 수 없다.

 

(2) 갱신 이상(Update Anomaly): 특정 부서의 위치가 변경되는 경우, 해당 부서의 모든 행을 찾아 위치 정보를 갱신해야 한다. 예를 들어 디자인 부서의 위치가 서울에서 창원으로 변경되면, 모든 디자인 직원의 행을 찾아 위치정보를 갱신해야 한다. 이 과정에서 데이터 불일치가 발생할 수 있다.

 

(3) 삭제 이상(Deletion Anomaly): 특정 직원이 회사를 떠나 해당 직원의 정보를 삭제하는 경우, 그 직원이 속한 부서 정보도 함께 사라질 수 있다. 예를 들어, '마케팅' 부서에 속한 ‘박영희’가 퇴사하고 그 사람의 정보를 삭제하면, '마케팅' 부서의 존재 자체가 데이터베이스에서 사라지게 된다.

 

*그러면 어떻게 이상 현상을 없앨 수 있을까?

=> 테이블을 나눈다! = 정규화

 

---

3. 시나리오 코드: 정규화를 통한 해결 방안

 

 

 위와 같은 이상 현상을 해결하기 위해, 데이터를 정규화하여 두 개의 테이블로 분리할 수 있다.

 

* 새로운 테이블 구조 설계

-부서 테이블: 각 부서의 고유 정보를 저장합니다

-직원 테이블:

 

---

 

(1) 샘플 데이터

create database b_demo1;
use b_demo1;

CREATE TABLE 직원 (
    직원ID INT AUTO_INCREMENT PRIMARY KEY,
    직원이름 VARCHAR(100) NOT NULL,
    부서 VARCHAR(30) NOT NULL,
    지역 VARCHAR(10) NOT NULL
);


INSERT INTO 직원 (직원ID, 직원이름, 부서, 지역) VALUES
(1000, '김철수', '영업', '부산'),
(1001, '박영희', '마케팅', '부산'),
(1002, '이민지', '인사', '서울'),
(1003, '최민수', '디자인', '서울'),
(1004, '야스오', '디자인', '서울');

select * from 직원;

 

 

(2) 부서/직원 테이블로 나누기

*직원이 Foreign key를 들고 있어야 타당하다. (직원:부서=N:1)

-- 이상현상을 방지하고자 테이블을 분리해보자.

-- 부서 테이블
create table tb_departments(
	부서ID int auto_increment primary key,
    부서명 varchar(30) not null,
    지역 varchar(10) not null
);

-- 직원
create table tb_employees(
	직원ID int auto_increment primary key,
    직원이름 varchar(100) not null,
    부서ID int,
    foreign key(부서ID) references tb_departments(부서ID)
    -- 참조 부서의 id(PK)를 가지고 있다면,
);

 

(3) 레코드 추가하기

left join을 이용해 부서명과 직원테이블을 함께 출력했다.

INSERT INTO tb_departments (부서명, 지역) VALUES
('영업', '부산'),
('마케팅', '부산'),
('인사', '서울'),
('디자인', '서울');

INSERT INTO tb_employees (직원이름, 부서ID) VALUES
('김철수', 1),
('박영희', 2),
('이민지', 3),
('최민수', 4),
('야스오', 4);

select e.직원ID, e.직원이름, e.부서ID, d.부서명
from tb_employees as e
left join tb_departments as d
on e.부서ID=d.부서ID;

 

 

(4) 연습 문제

-- 마케팅 부서의 이름을 HR로 수정해보자.
update tb_departments set 부서명 = 'HR' where 부서명='마케팅';

---

4. 정규화의 이점

(1) 중복 제거: 부서와 지역의 중복이 제거되어, 부서나 지역 정보의 변경이 필요할 때 한 곳만 수정하면 된다.

(2) 데이터 무결성 유지: 외래 키 제약 조건을 통해, 직원이 속한 부서의 정보가 항상 유효하도록 보장한다,.

(3) 삭제 및 갱신 이상 방지: 부서 정보를 변경하거나 삭제해도, 직원 정보에 자동으로 반영되므로 데이터의 일관성이 유지된다.