[DataBase] ERD와 정규화 과정
1. ERD (Entity Relationship Diagram)란
- 데이터베이스를 구축할 때 가장 기초적인 뼈대 역할을 한다
- 릴레이션 간의 관계들을 정의한 것이다
- 만약 서비스를 구축한다면 가장 먼저 신경써야 할 부분이다
신경쓰지 않고 구축하면 단단하지 않은 골조로 건물을 짓는것과 같다
ERD는 시스템의 요구 사항을 기반으로 작성되며 이를 기반으로 데이터베이스를 구축한다
구축한 이후에도 디버깅 또는 비즈니스 프로세스 재설계가 필요한 경우에 설계도 역할을 담당한다
ERD는 관계형 구조로 표현할 수 있는 데이터를 구성하는데 유용하지만
비정형 데이터를 충분히 표현할 수 없다는 단점도 있다
여기서 비정형 데이터란 비구조화 데이터를 말하며
미리 정의된 데이터 모델이 없거나 미리 정의된 방식으로 정리되지 않은 정보를 말한다
2. ERD 예제
3. 정규화 과정
정규화 과정은 릴레이션 간의 잘못된 종속 관계로 인해 데이터베이스 이상 현상이 일어나서
이를 해결하거나 저장 공간을 효율적으로 사용하기 위해 릴레이션을 여러개로 분리하는 과정이다
데이터베이스 이상 현상이란 회원이 한 개의 등급을 가져야 하는데 세 개의 등급을 갖거나
삭제할 때 필요한 데이터가 같이 삭제되고 데이터를 삽입해야 하는데 하나의 필드 값이 null이 되면
안 되어서 삽입하기 어려운 현상을 말한다
이상현상의 종류는 아래와 같다
삽입 이상(Insertion Anomaly)
데이터 삽입 시 의도와 다른 값들도 삽입됨
삭제 이상(Delete Anomaly)
데이터 삭제 시 의도와 다른 값들도 연쇄 삭제됨
갱신 이상(Update Anomaly)
속성값 갱신 시 일부 튜플만 갱신되어 모순 발생
4. 정규화 원칙
정규화 원칙이란 같은 의미를 표현하는 릴레이션이지만 좀 더 좋은 구조로 만들어야 하고
자료의 중복성은 감소해야 하고 독립적인 관계는 별개의 릴레이션으로 표현해야 하며
각각의 릴레이션은 독립적인 표현이 가능해야 하는 것을 말한다
제 1정규형 (1NF)
릴레이션의 모든 도메인이 더 이상 분해될 수 없는 원자값(Atomic Value)만으로 구성되어야 한다
즉 릴레이션의 속성 값 중에서 한 개의 기본키에 대해 두개 이상의 값을 가지는
반복 집합이 있어서는 안되는 것이다 반복 집합이 있다면 제거해야 한다
- 1NF를 만족하지 않는 릴레이션
| 수강과목 | 수강자 |
| 국어 | 비숑 |
| 영어 | 비숑, 웰시코기 |
| 수학 | 웰시코기, 푸들 |
- 발생할 수 있는 이상의 예시
갱신이상
비숑이 영어과목을 한국사로 바꾸면 웰시코기도 덩달아 바꿔야한다
삭제이상
비숑이 영어과목을 수강취소하면 웰시코기도 덩달아 수강정보가 사라진다
- 1NF를 만족시키는 릴레이션
| 일련번호 | 수강과목 | 수강자 |
| 1 | 국어 | 비숑 |
| 2 | 영어 | 비숑 |
| 3 | 영어 | 웰시코기 |
| 4 | 수학 | 웰시코기 |
| 5 | 수학 | 푸들 |
제 2정규형 (2NF)
릴레이션이 제1정규형이며 부분 함수의 종속성을 제거한 형태를 말한다
부분 함수의 종속성 제거란 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속적인걸 말한다
릴레이션을 분해할 대 동등한 릴레이션으로 분해해야 하고
정보 손실이 발생하지 않는 무손실 분해로 분해되어야 한다
- 2NF를 만족하지 않는 릴레이션
| 학번 | 이름 | 소속학과 | 학과장 |
| 1901 | 비숑 | 컴퓨터과 | 김교수 |
| 1845 | 웰시코기 | 전자과 | 박교수 |
| 1706 | 푸들 | 기계과 | 천교수 |
| 1925 | 허이랜드 테리어 | 컴퓨터과 | 김교수 |
- 발생할 수 있는 이상의 예시
삽입이상
컴퓨터과, 전자과, 기계과 학생을 추가하고자 할 경우
더이상 불필요한 중복정보인 학과장 정보가 삽입되어야 한다
갱신이상
컴퓨터과의 학과장이 변경되었다
하나만 변경하면 모순이 발생하므로 다 찾아서 변경해야 한다
삭제이상
푸들이 자퇴해서 삭제하고자 한다
이 삭제로 인해 기계과 학과장의 정보가 사라져버린다
- 2NF를 만족시키는 릴레이션
| 학번 | 이름 | 소속학과 |
| 1901 | 비숑 | 컴퓨터과 |
| 1845 | 웰시코기 | 전자과 |
| 1706 | 푸들 | 기계과 |
| 1925 | 허이랜드 테리어 | 컴퓨터과 |
| 헉과 | 학과장 |
| 컴퓨터과 | 김교수 |
| 전자과 | 박교수 |
| 기계과 | 천교수 |
제 3정규형 (3NF)
재 2정규형이고 기본키가 아닌 모든 속성이 이행적 함수 종속을 만족하지 않는 상태이다
이행적 함수종속이란 A->B 와 B->C가 존재하면 논리적으로 A->C가 성립하는데
이때 집합 C가 집합 A에 이행적으로 함수 종속이 되었다고 한다
비숑이 컴퓨터과 이고 컴퓨터과가 공대이면, 비숑은 공대인 관계가 성립된다
- 3NF를 만족하지 않는 릴레이션
| 학번 | 이름 | 소속학과 | 대학 |
| 1901 | 비숑 | 컴퓨터과 | 공대 |
| 1845 | 웰시코기 | 전자과 | 공대 |
| 1706 | 푸들 | 경제 | 경상 |
| 1925 | 허이랜드 테리어 | 경영 | 경상 |
- 발생할 수 있는 이상의 예시
삽입이상
컴퓨터과, 전자과 학생을 추가하고자 할 경우
더이상 불필요한 중복정보인 대학 정보가 삽입되어야 한다
갱신이상
컴퓨터과의 소속 대학이 AI대학으로 변경되었다
하나만 변경하면 모순이 발생하므로 다 찾아서 변경해야 한다
삭제이상
푸들이 자퇴해서 삭제하고자 한다
이 삭제로 인해 경제과 대학 정보가 사라져버린다
- 3NF를 만족시키는 릴레이션
| 학번 | 이름 | 소속학과 |
| 1901 | 비숑 | 컴퓨터과 |
| 1845 | 웰시코기 | 전자과 |
| 1706 | 푸들 | 경제 |
| 1925 | 허이랜드 테리어 | 경영 |
| 소속학과 | 대학 |
| 컴퓨터과 | 공대 |
| 전자과 | 공대 |
| 경제 | 경상 |
| 경영 | 경상 |
보이스/코드 정규형 (BCNF)
제 3정규화이고 결정자가 후보키가 아닌 함수 종속 관계를 제거하여
릴레이션의 함수 종속 관계에서 모든 결정자가 후보키인 상태를 말한다
결정자란 함수 종속 관계에서 특정 종속자를 결정 짓는 요소 X->Y일 때 X는 결정자, Y는 종속자 이다
- BCNF를 만족하지 않는 릴레이션
| 학번 | 과목 | 교수 |
| 100 | 데이터베이스 | 김교수 |
| 100 | 자료구조 | 박교수 |
| 200 | 네트워크 | 천교수 |
| 300 | 인공지능 | 조교수 |
제약조건
- 한명의 학생은 동일한 과목에 대해 한명의 교수에게만 수강 가능하다
- 각 교수는 하나의 과목만 담당한다
- 하나의 과목은 여러 교수가 담당 가능하다
분석
- (학번+과목)은 교수를 결정 짓는다
- 교수는 과목을 결정 짓는다
- 즉 교수 또한 결정자 인데 교수는 학번을 결정 지을 수 없으므로 후보키가 아니다
- 발생할 수 있는 이상의 예시
삽입이상
200 학생이 데이터베이스를 수강하고자 할때
현재 불필요한 김교수 정보가 한번 더 삽입된다
갱신이상
김교수의 담당 과목이 알고리즘으로 바뀌었다
담당 과목을 변경할 경우 학생의 수강 과목이 변경되어 버린다
삭제이상
300 학생이 자퇴해서 삭제하고자 한다
이 삭제로 인해 인공지능 과목을 조교수가 담당하고 있다는 정보가 사라진다
- BCNF를 만족시키는 릴레이션
| 학번 | 과목코드 |
| 100 | DB01 |
| 100 | DS01 |
| 200 | NW01 |
| 300 | AI01 |
| 과목코드 | 과목 | 교수 |
| DB01 | 데이터베이스 | 김교수 |
| DS01 | 자료구조 | 박교수 |
| NW01 | 네트워크 | 천교수 |
| AI01 | 인공지능 | 조교수 |
제 4정규형 (4NF)
BCNF를 만족해야하며 다치 종속이 없어야한다
다치종속이란 A->B일 때의 하나의 A 값에 여러개의 B 값이 존재하면 다치 종속이라 한다
R(A,B,C)가 있을 때 A와 B 사이에 다치 종속이 있을때 B와 C는 독립적이다
- 4NF를 만족하지 않는 릴레이션
| 학생번호 | 과목 | 취미 |
| 101 | 자바 | 노래 |
| 101 | C++ | 게임 |
| 102 | 운영체제 | 노래 |
| 102 | DB | 게임 |
위와 같이 101번 학생이 두개의 과목과 두개의 취미를 다치 종속하고있을때
아래와 같이 중복이 발생한다
| 학생번호 | 과목 | 취미 |
| 101 | 자바 | 노래 |
| 101 | 자바 | 게임 |
| 101 | C++ | 노래 |
| 101 | C++ | 게임 |
- 4NF를 만족시키는 릴레이션
| 학생번호 | 과목 |
| 101 | 자바 |
| 101 | C++ |
| 102 | 운영체제 |
| 102 | DB |
| 학생번호 | 취미 |
| 101 | 노래 |
| 101 | 게임 |
| 102 | 노래 |
| 102 | 게임 |
위 2개의 테이블은 여전히 다치 종속성을 가지지만
2개 이상의 컬럼이 하나의 컬럼에 다치 종속되지 않는다
제 5정규형 (5NF)
중복을 제거하기 위해 분해할 수 있을 만큼 전부 분해하는 것이다
4NF를 만족해야하며 조인 종속이 없어야하고 조인 연산을 했을때 손실이 없어야한다
조인 종속은 다치 종속의 조금 더 일반화된 형태이다
만약 하나의 릴레이션을 여러 개의 릴레이션으로 무손실 분해했다가
다시 결합할 수 있다면 조인 종속이라고 한다
예를들어 A라는 릴레이션을 B와 C로 분해했다가
다시 조인해서 그대로 A가 된다면 A는 조인 종속성이 있다고 한다
일반적으로 현실의 데이터베이스에서는 5정규형을 사용하지 않는다