정규화(1단계)

MySQL

정규화(1단계)

yoooon1212 2024. 6. 11. 17:39

정규화

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

정규화(Normalization)의 기본 목표

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

데이터의 무결성(Integrity)은 데이터베이스 관리 시스템에서 데이터의 정확성, 일관성 및 신뢰성을 유지하는 특성을 말합니다.

데이터베이스 내의 정보가 정확하고 일관된 상태로 유지되고 시스템에서 정의한 규칙, 제약 조건, 비즈니스 규칙을 준수하여 데이터의 품질을 보장하며, 응용 프로그램과 사용자가 신뢰할 수 있는 데이터에 접근할 수 있도록 하는것.

엔터티 무결성(Entity Integrity), 참조 무결성(Referential Integrity), 도메인 무결성(Domain Integrity), 사용자 정의 무결성(User-Defined Integrity) 등이 존재

이상 현상(Anomaly)이란

이상 현상이란 테이블에서 일부 속성들의 종속으로 인해 데이터의 중복이 발생하며 이 중복으로 인해 테이블 및 데이터 조작 시 문제가 생기는 현상을 의미한다. 아래 내용으로 이상현상에 대해서 좀 더 자세히 살펴 보도록 합시다.

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 직원;

이상 현상 설명

삽입 이상 (Insertion Anomaly)
새로운 부서가 추가되지만, 해당 부서에 아직 직원이 배치되지 않았다면, 부서와 위치 정보를 삽입할 방법이 없습니다. 예를 들어, 'IT' 부서가 ‘부산'에 새로 설립되었지만, 해당 부서의 직원이 아직 확정되지 않은 경우, 이 정보를 저장할 수 없습니다.
갱신 이상 (Update Anomaly)
특정 부서의 위치가 변경되는 경우, 해당 부서의 모든 행을 찾아 위치 정보를 갱신해야 합니다. 예를 들어, '디자인' 부서의 위치가 '서울'에서 '창원'으로 변경되면, 모든 '디자인' 직원의 행을 찾아 위치 정보를 갱신해야 하며, 이 과정에서 일부 행을 놓치면 데이터 불일치가 발생할 수 있습니다.
삭제 이상 (Deletion Anomaly)
특정 직원이 회사를 떠나 해당 직원의 정보를 삭제하는 경우, 그 직원이 속한 부서 정보도 함께 사라질 수 있습니다. 예를 들어, '마케팅' 부서에 속한 ‘박영희’가 퇴사하고 그녀의 정보를 삭제하면, '마케팅' 부서의 존재 자체가 데이터베이스에서 사라지게 됩니다.

정규화를 통한 해결 방안

이러한 이상 현상을 해결하기 위해 데이터를 정규화하여 두 개의 테이블로 분리할 수 있습니다

새로운 테이블 구조 설계 부서 테이블: 각 부서의 고유 정보를 저장합니다. 직원 테이블: 직원 정보를 저장하되, 부서 정보는 부서 테이블을 참조합니다.

-- 이상현상(갱신 이상)을 방지하고자 테이블을 분리해보자. 
create table tb_departments(
	부서ID int auto_increment primary key,
    부서명 varchar(30) not null,
    지역 varchar(10) not null
);

-- 부서 : 직원 = 1:N 관계 (N이 foreign key를 가지고 있어야 함.)
-- 참조 부서의 ID 값을 가지고 있다면 tb_departments 테이블의 정보만 수정 가능
-- => 갱신 이상 현상 해결됨.

create table tb_employees(
	 직원ID int auto_increment primary key,
     직원이름 varchar(100) not null,
     부서ID int,
     foreign key(부서ID) references tb_departments(부서ID)
);

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

select * from tb_departments;

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

select * from tb_employees;

-- 직원의 정보와 부서가 있다면 부서명을 함께 출력
-- inner, outer 중에 어떤 걸 선택해야 할까?
-- inner면 tb_employees 의 부서ID 의 null 값을 볼 수 없음. 
-- left join --> from 뒤의 테이블을 기준으로 null 값도 볼 수 있도록 함. 

select * from tb_employees as e
left join tb_departments as d
on e.부서ID = d.부서ID;

-- 마케팅 부서의 이름을 HR로 수정해주세요. 
update tb_departments set 부서명 = 'HR' where 부서명 = '마케팅';
-- tb_departments 테이블의 부서명만 수정됨.

정규화의 이점

중복 제거: 부서와 지역의 중복이 제거되어, 부서나 지역 정보의 변경이 필요할 때 한 곳만 수정하면 됩니다.
데이터 무결성 유지: 외래 키 제약 조건을 통해 직원이 속한 부서의 정보가 항상 유효하도록 보장합니다.
삭제 및 갱신 이상 방지: 부서 정보를 변경하거나 삭제해도 직원 정보에 자동으로 반영되므로 데이터의 일관성이 유지됩니다.