ERD와 정규화 과정

면접을 위한 CS 전공지식 노트를 읽고 중요하다고 생각되는 부분을 정리합니다.

 

 

 

---

 

 

 

ERD 란 ?

ERD(Entity Relationship Diagram) 는 DB 를 구축할 때 가장 기초적인 뼈대 역할을 하며, 테이블 간 관계를 정의한 것이다.

서비스 구축 전에 꼼꼼히 구축해야하는 부분 ! 

시스템의 요구 사항을 기반으로 작성되며 작성된 ERD 를 기반으로 DB 를 구축하고, 재설계나 디버깅 시 설계도의 역할을 하기도 한다.

ERD 는 간단하게 이정도 정의만 알아보고 중요한 정규화 과정에 대해 알아보자.

 

 

1. 정규화 과정

정규화 과정이란 테이블 간 잘못된 종속 관계로 인해 DB 이상 현상이 일어나서 이를 해결하거나 저장 공간을 효율적으로 사용하기 위해

테이블을 여러 개로 나누는 과정을 말한다.

정규화 과정은 정규형 원칙을 통해 테이블을 나누고 정규화된 정도는 NF(Nomal Form) 으로 표현한다.

 

정규형 과정을 거쳐 테이블을 나누면 오히려 Join 이 많아지면서 성능이 느려질 수 있는 등 무조건 성능이 좋아지는 것은 아니다.

따라서 서비스에 따라 정규화 또는 비정규화 (반정규화) 과정을 진행해야 한다는 것을 알아두자.

 

기본 정규형인 1 ~ 3 / BCNF 정규형과 고급 정규형인 4 ~ 5 정규형이 있고 여기선 기본 정규형만 알아보자.

 

 

1-1. 제 1 정규형

테이블의 모든 속성이 더 이상 분해될 수 없는 원자 값으로만 구성되어 있는 것을 말한다.

즉, 테이블의 각 행마다 컬럼 값이 단 하나만 존재해야 하는 것이다.

아래와 같은 테이블은 제 1정규형 대상이라고 할 수 있다.

유저번호	유저 ID	수강명	성취도
1	홍철	{C++, Java}	{90%, 10%}
2	범석	{Docker, Python}	{7%, 8%}

↓ 수정 후

유저번호	유저 ID	수강명	성취도
1	홍철	C++	90%
1	홍철	Java	10%
2	범석	Docker	7%
2	범석	Python	8%

 

 

1-2. 제 2 정규형

제 1 정규형이면서 부분 함수의 종속성을 제거한 형태를 말한다.

이는 복합 기본 키를 가지는 테이블에서 발생할 수 있는 문젠데, 쉽게 말해서 기본 키의 부분집합인 속성이 기본 키가 아닌

다른 일반 속성의 결정자가 되는 형태를 말한다. 다시 테이블로 살펴보자

 

이 테이블의 기본 키를 유저 ID + 수강명 의 복합 기본키라고 하자.

성취도 속성은 복합 기본 키를 통해 결정되는데 유저번호는 유저 ID 라는 복합 기본 키의 부분 집합에 의해 결정되는 것을 볼 수 있다.

이걸 부분 함수 종속성이라고 하고, 때문에 이 테이블에 2 정규화 대상이 되는 것이다.

결정자인 유저 ID 를 기준으로 테이블을 둘로 나누자.

참고로 부분 함수 종속성이 있을 수 없는 단일 기본 키를 사용하는 테이블은 자동으로 2 정규형이 적용된 테이블이라고 할 수 있다.

유저번호	유저 ID	수강명	성취도
1	홍철	C++	90%
1	홍철	Java	10%
2	범석	Docker	7%
2	범석	Python	8%

↓ 수정 후

유저번호	유저 ID
1	홍철
2	범석

유저ID	수강명	성취도
홍철	C++	90%
홍철	Java	10%
범석	Docker	7%
범석	Python	8%

 

 

1-3. 제 3 정규형

제 2 정규형이면서 기본 키가 아닌 모든 속성이 이행적 함수 종속을 만족하지 않는 상태를 말한다.

이행적 함수 종속이란 A(결정자 / 기본 키) -> B(종속) 와 B(결정자) -> C (종속) 가 존재하면, 논리적으로 A -> C 가 성립하는데 이 때

C 가 A 에 이행적으로 함수 종속이 되었다고 하는 것을 말한다.

쉽게 말해 기본 키가 아닌 일반 속성이 다른 일반 속성을 결정하는 경우가 있다면 제 3 정규화 대상이 된다. 테이블로 살펴보자.

 

아래 테이블에서 기본 키는 유저 ID 라고 가정한다.

기본 키인 유저 ID 는 등급 속성을 결정하고, 등급은 할인율 속성을 결정하는 이행적 함수 종속 상태이며 기본 키가 아닌 등급이 할인율을

결정하고 있는 것을 확인할 수 있다.

3 정규화 때는 일반 속성을 결정하는 결정자 즉, 여기서는 등급 속성을 기준으로 테이블을 나누면 된다.

유저 ID	등급	할인율
홍철	실버	30%
범수	골드	50%
가영	다이아	70%

↓ 수정 후

유저 ID	등급
홍철	실버
범수	골드
가영	다이아

등급	할인율
실버	30%
골드	50%
다이아	70%

 

 

1-4. BCNF(보이스 코드) 정규형 

제 3정규형을 만족하고 결정자가 후보 키가 아닌 함수 종속 관계를 제거하여 해당 테이블의 함수 종속 관계에서 모든 결정자가 후보 키인

상태를 말한다. 즉, 후보 키가 아닌 일반 속성 값이 결정자가 되어 기본 키나 기본 키의 부분집합을 결정하는 결정하는 경우를 말한다.

테이블로 살펴보자. 한 명의 강사는 한 개의 강의만 담당할 수 있다 라는 제약사항이 있다고 하자.

 

아래 테이블에서 기본 키가 될 수 있는 후보군은 {고객 ID, 강의} 또는 {고객 ID, 담당강사 번호} 2개 이다. 

기본 키는 하나만 선택해야하기 때문에 {고객 ID, 담당강사 번호} 를 기본 키라고 설정했다고 가정해보자. 

이 때 한 명의 강사는 한 개의 강의만 담당할 수 있다는 제약사항에 따라 강의라는 일반 속성은 후보 키가 아님에도 불구하고, 담당강사 번호

를 결정할 수 있게 된다. 이럴 때 BCNF 정규형 대상이 되고, 문제가 되는 결정자인 강의와 종속자 담당강사 번호를 따로 떼어내고 원래의 

기본 키인 고객 ID, 담당강사 번호를 떼어내면 된다.

 

이 예제의 제약 조건에 의해 이상 현상이 3개 발생할 수 있는데 간단히 정리하자면,

 

1. 삽입 이상

새로운 강사 A005 가 새로 고용되었다고 가정했을 때, 토익 스피킹 강의를 하나 담당한다고 해보자. 그렇다면 아직 고객이 없을 수도 있기

때문에 기본 키에 NULL 값이 삽입되게 되고, 이는 참조 무결성 법칙을 위반하는 것이기 때문에 삽입 이상이 발생하게 된다.

따라서 입력을 하기 위해선 새로운 데이터를 추가해야만 삽입이 가능할테고 이처럼 신규 데이터를 삽입하기 위해 불필요한 데이터를 입력

해야 하는 문제를 삽입 이상이라고 한다.

 

2. 갱신(수정) 이상

P001 강사가 영어 회화 강의를 그만두고 영어 문법 강의를 맡는다고 한다. 이 때 아래 테이블에서 2 개의 레코드를 수정해야 하는데, 

만약 하나의 레코드만 변경했다고 하면 A001 강사는 영어 회화와 영어 문법 2개의 강의를 맡게된다. 이는 제약사항을 위반하는 것이기

때문에 갱신 이상이 발생하게 된다. 이처럼 중복 데이터 중, 일부만 변경해 데이터가 불일치하게 되는 문제를 갱신 이상이라고 한다.

 

3. 삭제 이상

A002 또는 A003 강사는 딱 하나의 강의만 맡고 있는데, 해당 고객에 강의를 취소하게 된다면 어떻게 될까 ?

B_1 고객의 수강이 취소되었을 뿐이지만 담당 강사 A002 의 정보까지 함께 삭제되게 된다. 이처럼 삭제 되지 않아야 할 데이터 또한 같이

지워지는 문제를 삭제 이상이라 한다.

고객 ID	강의	담당강사 번호
A_1	영어 회화	A001
B_1	기초 토익	A002
C_1	영어 회화	A001
C_1	기초 토익	A004
D_1	영어 회화	A003
D_1	기초 토익	A004

↓ 수정 후

고객 ID	담당강사 번호
A_1	A001
B_1	A002
C_1	A001
C_1	A004
D_1	A003
D_1	A004

강의	담당강사 번호
영어 회화	A001
기초 토익	A002
영어 회화	A003
기초 토익	A004