[DB] 데이터 정합성과 무결성 그리고 데이터베이스 정규화

안녕하세요. 바른호랑이입니다.

이번 게시글에서는 데이터 정합성과 무결성 그리고 데이터베이스 정규화에 대해서 알아볼 예정입니다.

정규화(Normalization)란 데이터 모델링의 핵심 중의 하나라고 할 수 있으며, 중복데이터와 이상현상을 제거함으로써 데이터 정합성(Consistency)과 데이터무결성(Integrity) 확보하기 위해 테이블을 분해하는 것입니다. 이와 관련된 보다 자세한 내용은 아래와 같습니다.

 

1. 데이터정합성과 무결성

① 정의

ㆍ 데이터 정합성(Data Consistency) : 데이터들이 서로 일치하는 상태

- 데이터가 서로 모순없이 일치해야 함을 의미하며 중복데이터가 제거되지 않은 비정규형을 사용하여 이상현상(Anomaly)가 발생할 경우 정합성이 깨지게 됨. 예를 들어 사원정보 테이블의 사원번호를 1에서 -1로 변경하였는데 부서정보 테이블의 사원번호가 변경되지 않았을 경우 데이터 정합성이 깨졌다고 할 수 있음. 

- 일상생활 속에서는 '술을 마시고(음주여부 : Y) 운전을 했지만(운전여부 : Y) 음주운전을 하지 않았다.(음주여부와 운전여부가 모두 Y이면 음주운전여부도 Y가되야함)'가 정합성이 지켜지지 않은 대표적인 사례임

사원정보 테이블			부서정보 테이블
사원번호	이 름		부서코드	부서이름	사원번호
-1	Bob		A01	재무	1
2	Steve		A01	재무	2
3	John		A02	영업	3

ㆍ 데이터 무결성(Data Integrity) : 데이터 값이 정확한 상태

- 개체무결성(Entity Integrity) : 모든 인스턴스는 고유한 값(유일성) 또는 Null값을 가지지 않은(Not Null) 속성이나 속성그룹을 가져야한다는 것으로 식별자에 의해 지켜질 수 있음. 만약 사원번호(주식별자로 사용한다고 가정)가 중복되는 값이 있거나 Null값이 있는 인스턴스가 있다면 개체 무결성이 훼손된 것으로 볼 수 있음.

- 도메인무결성(Domain Integrity) : 엔터티의 특정속성은 사전의 정의된 범주의 값(동일한 데이터 타입, 길이, Null 허용여부 등과 같은 규칙)만 존재해야함. 만약 위의 예시에서 사원번호의 도메인이 1~INF였다고 한다면 -1의 값이 있으므로 도메인 무결성이 훼손된 것으로 볼 수 있음.

- 참조무결성(Referential Integrity) : 참조되는 엔터티의 주식별자 값과 일치하거나 Null값이여야함.(만약 두 엔터티가 식별관계라고하면 Null값이면 개체 무결성이 훼손되는 것이므로 이 때에는 Null값이 들어갈 수 없음). 만약 사원정보 테이블에서 John이 해고되었는데 부서정보 테이블에는 사원번호 3이 남아 있다면 참조무결성이 훼손된 것이므로 제약조건을 통해 제한을 걸어야 참조무결성을 지킬 수 있음.

- 업무무결성(Business Integrity) : 업무를 수행하는 방법이나 데이터를 처리하는 규칙으로 '특정금액 이상 주문시 배송비 무료', '보험 가입 후 3개월 납부 이력이 있어야 보험 보장 가능'과 같은 사례들이 이에 해당하는 사항이며, 강제하는 대표적 방법으로 '트리거(Trigger)'가 있음.

 

2. 정규화와 이상현상

① 정의

ㆍ 정규화(Normailzation) : 중복데이터와 이상현상을 제거하기 위해 테이블(릴레이션)을 함수종속성 기반으로 분해하는 과정

ㆍ 이상현상(Anomaly) : 데이터 중복성에 의해서 발생하는 데이터 불일치현상

- 갱신이상(Modification Anomaly) : 반복된 데이터 중 일부를 갱신할 경우 데이터 불일치가 발생하는 것으로 아래의 그림에서 3번째 인스턴스의 이름 속성값을 임의로 갱신하게 되면 데이터 불일치가 발생하게 되는데 이 경우를 갱신 이상이 발생했다고 함.

학 번	과목명	성 적	이 름
001	운영체제	A0	David
002	데이터베이스	A+	John
001	데이터베이스	B0	David → Alex
003	운영체제	C+	Steve

- 삽입이상(Insertion Anomaly) : 불필요한 정보를 같이 저장하지 않고서는 특정 정보를 저장하는 것이 불가능한 상태로 만약 위의 그림에서 학번, 과목명, 이름 속성에만 값을 넣고 싶은데 성적 속성에 값을 반드시 넣어야 삽입이 가능하다면 삽입 이상이 발생했다고 함.

- 삭제이상(Deletion Anomaly) : 필요한 정보를 함께 삭제하지 않고는 특정 정보를 삭제하는 것이 불가능한 상태로 위의 그림에서 Steve의 운영체제 성적이 C+이라는 것만 삭제하고 싶은데 Steve의 학번, 이름 속성도 같이 삭제하지 않으면 삭제가 불가능할 때 삭제 이상이 발생했다고 함.

 

② 장, 단점

ㆍ 장점

- 데이터베이스 변경시 이상현상(Anomaly)제거가 가능하고 새로운 데이터 형의 추가로 인한 확장 시 구조변경 소요가 줄어듦.

- 데이터베이스와 연동된 응용프로그램에 최소한의 영향을 주기 때문에 응용프로그램의 생명을 연장시킬 수 있음.

ㆍ 단점

- 릴레이션 분해로 인해 JOIN연산이 많아지고, 질의응답시간이 느려질 수 있음.

- JOIN이 과도하게 많이 발생하면 성능저하가 나타날 수 있음.

 

③ 목적과 종류

ㆍ 목적

- 중복을 배제하여 갱신, 삽입, 삭제 이상의 발생을 방지하고 각 테이블(릴레이션)에 중복된 종속성을 분할

- 어떠한 테이블(릴레이션)이라도 DB 내에서 표현가능하게 하고 데이터 삽입시 재구성 소요 감소

- 효과적인 검색알고리즘 생성이 가능하도록 환경 구성

IT위키

종 류	관련 내용	개 념
제 1정규화	원자성	도메인이 원자값이 될 수 있도록 분해
제 2정규화	부분함수 종속	부분함수 종속을 제거하여 완전함수 종속을 달성
제 3정규화	이행함수 종속	이행함수 종속을 제거
보이시코드 정규화	결정자, 후보키	결정자이면서 후보키가 아닌 것을 제거
제 4정규화	다치 종속	다치 종속을 제거
제 5정규화	조인 종속	조인 종속을 제거

ㆍ 해당 함수종속성을 제거하는 과정을 정규화(Normalization)라 하고, 완료된 상태를 정규형(Normal Form)이라고 함. 

ㆍ 일반적으로 3정규형 또는 보이시코드 정규화까지만 수행하고, 4,5정규화는 상황을 고려하여 추가적으로 실시함.

ㆍ 제 1정규화 : 테이블의 컬럼이 원자값(Atomic Value)를 갖도록 테이블을 분해하는 과정

부서이름	사원이름	→	부서이름	사원이름
인사	Bob, Steve		인사	Bob
			인사	Steve
재무	John		재무	John
영업	Alex		영업	Alex

ㆍ 제 2정규화 : 제 1정규형에 대해 완전함수 종속을 만족하도록 테이블을 분해하는 과정

- 일반적으로 2개 이상의 속성이 결합된 복합식별자를 사용할 때 자주 나타나며 이를 분해해주는 과정임.

- 예를들어 과목코드와 강사코드를 결합하여 복합식별자로 쓸 경우(제 2정규화가 필요한 경우)과목코드는 과목명만 강사코드는 강사명만 종속시키게 되기에 부분함수종속이 발생한 상태임. 이 경우 테이블을 분해하여 완전함수종속을 이루는 상태로 만들어줘야 함.

제 2정규화 전
과목코드	강사코드	과목명	강사명	강좌 개설날짜
A01	T001	데이터베이스	정약용	2023-03-01
B02	T002	운영체제	장영실	2023-02-27
C01	T003	역사와 문화	유성룡	2023-03-02

제 2정규화 후
과목코드	과목명		과목코드	강사코드	강좌 개설일자		강사코드	강사명
A01	데이터베이스		A01	T001	2023-03-01		T001	정약용
B02	운영체제		B02	T002	2023-02-27		T002	장영실
C01	역사와 문화		C01	T003	2023-03-02		T003	유성룡

ㆍ 제 3정규화 : 제 2정규형에 대해 일반속성 간의 종속관계가 발생한 경우(이행함수 종속발생) 이를 해소해 주는 과정

- 예를 들어 배송업체명이 주 식별자인 고객번호나 주문일시에 종속되지 않고 일반속성인 배송업체코드에 종속되어 있는 경우 이행함수종속이 발생한 상태로 이를 해소시켜줘야 함.

제 3정규화 전
고객번호	주문일시	주문금액	배송업체코드	배송업체명
A001	20230101	10,000	D001	고구려택배
B002	20230102	40,000	D002	백제택배
C003	20230302	100,000	D003	신라택배

제 3정규화 후
고객번호	주문일시	주문금액	배송업체코드		배송업체코드	배송업체명
A001	20230101	10,000	D001		D001	고구려택배
B002	20230102	40,000	D002		D002	백제택배
C003	20230302	100,000	D003		D003	신라택배

ㆍ 보이시코드(Boyce-Codd) 정규화 : 제 3정규형에 대해 결정자이면서 후보키(Candidate Key : 기본키가 될 수 있는 속성들로 유일성과 최소성을 만족켜야함)가 아닌 값들을 제거해 주는 과정

- 예를 들어 한 학번, 과목, 교수로 이루어진 아래와 같은 테이블이 있다고 했을 때, (학번 + 과목)은 교수를 결정짓고, 교수는 과목을 결정지으나 학번을 결정짓지 못함. 즉, 교수는 과목에 대한 결정자이지만 후보키가 될 수는 없으므로 이를 분해하여 해소시켜줘야 함.

보이시코드 정규화 전
학 번	과 목	교 수
100	데이터베이스	정약용
100	네트워크	장영실
200	전쟁의 역사	유성룡
300	병법의 이해	이순신

보이시코드 정규화 후
학번	과목코드		과목코드	과목	교수
100	A001		A001	데이터베이스	정약용
100	A002		A002	네트워크	장영실
200	B001		B001	전쟁의 역시	유성룡
300	C001		C001	병법의 이해	이순신

ㆍ 제 4정규화 : 보이시코드 정규형(BCNF)에 대해 다치종속(Multi-valued Dependency)를 제거해 주는 과정

- 주 식별자 1개의 값에 대해 특정속성의 값이 여러 개 존재하고 해당 컬럼들을 제외한 다른 컬럼들이 있을 경우 다치종속이 발생할 수 있음.

- 예를 들어 사원번호, 교육신청 프로그램, 동아리 속성이(부서와 동아리 컬럼은 독립적이라 가정)존재하는 테이블이 있고 하나의 사원번호에 여러 개의 동아리 속성값이 존재하면 다치종속성을 가진다라고 할 수 있음.

IT위키

원본 테이블
사원번호	교육신청 프로그램	동아리
001	4차 산업혁명의 이해	밴드
001	리더십에 대하여	등산
002	리더십에 대하여	와인
002	대화의 기술	테니스

보이시코드 정규화 후
사원번호	교육신청 프로그램	동아리
001	4차 산업혁명의 이해	밴드
001	4차 산업혁명의 이해	등산
001	리더십에 대하여	밴드
001	리더십에 대하여	등산
002	리더십에 대하여	와인
002	리더십에 대하여	테니스
002	대화의 기술	와인
002	대화의 기술	테니스

제 4정규화 후
사원번호	교육신청 프로그램		사원번호	동아리
001	4차 산업혁명의 이해		001	밴드
001	리더십에 대하여		001	등산
002	리더십에 대하여		002	와인
002	대화의 기술		002	테니스

ㆍ 제 5정규화 : 제 4정규형에 대해 JOIN 종속을 제거해 주는 과정

- 하나의 테이블(릴레이션)을 여러 개의 테이블들(릴레이션)으로 무손실 분해 했다가 다시 결합이 가능하면 조인종속이 있다고 하며 이를 해소해줘야 함.

IT위키

 

 

P.S 더 나은 개발자가 되기위해 공부중입니다. 잘못된 부분을 댓글로 남겨주시면 학습하는데 큰 도움이 될 거 같습니다.