[DB] 데이터베이스 설계

관계 데이터 모델을 기반으로 두고 데이터베이스를 설계할 때는 두 가지 방법을 주로 이용한다.

(두 방법의 설계 결과물은 유사하기 떄문에 상황에 따라 선택하면 된다.)

1. [ER 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계]
2. [정규화를 이용한 데이터베이스 설계]

---

ER 모델과 릴레이션 변환 규칙을 이용한 데이터베이스 설계 과정

 

 

1단계: 요구 사항 분석

• 데이터베이스를 사용해 실제 업무를 처리하는 사용자에게서 필요한 데이터의 종류와 처리 방법 같은 다양한 요구 사항 수집
• 수집한 요구 사항을 분석하여 요구 그 결과를 요구 사항 명세서 작성

 

2단계: 개념적 설계

• 사용자의 요구 사항을 개념적 데이터 모델을 이용해 표현
• 개념적 데이터 모델은 개발에 사용할 DBMS의 종류에 독립적이면서, 중요한 데이터 요소와 데이터 요소 간의 관계를 표현
• 일반적으로 개념적 모델은 E-R 모델을 많이 사용함.
• 사용자의 요구 사항을 분석한 결과를 E-R 다이어그램으로 표현하는 것이 개념적 설계 단계에서 수행하는 주요 작업
• 요구 사항 명세서를 개념적 데이터모델로 변환하는 일을 개념적 모델링이라고 함.
• E-R 다이어그램과 같이 개념적 데이터 모델로 표현한 결과물을 개념적 구조 또는 개념적 스키마라고 함.

개념적 설계 단계

1. 개체와 속성 추출: 요구 사항 명세서에 있는 명사에 주목
2. 관계 추출: 요구 사항 명세서에 있는 동사에 주목
3. E-R 다이어그램 작성

 

3단계: 논리적 설계

• 개발에 사용할 DBMS에 적합한 논리적 데이터 모델을 이용해 전 단계에서 생성한 개념적 구조를 기반으로 논리적 구조를 설계
• DBMS의 종류에 따라 네트워크 데이터 모델, 계층 데이터 모델, 관계 데이터 모델, 객체지향 데이터 모델 등이 사용될 수 있음
• 일반적으로 관계 데이터 모델을 많이 사용함.
• 논리적 설계 단계에서 E-R 다이어그램을 릴레이션 스키마로 변환하는 작업을 논리적 모델링 또는 데이터 모델링이라 한다.
• 릴레이션 스키마와 같이 논리적 데이터모델로 표현된 결과물을 논리적 구조 또는 논리적 스키마라고 함.

릴레이션 변환 규칙

1. 모든 개체는 릴레이션으로 변환한다.
2. 다대다 관계는 릴레이션으로 변환한다.
3. 일대다 관계는 외래키로 표현한다.
4. 일대일 관계는 외래키로 표현한다.
5. 다중 값 속성은 독립 릴레이션으로 변환한다.

 

4단계: 물리적 설계

• 데이터베이스의 물리적 구조는 데이터베이스를 저장 장치에 실제로 저장하기 위한 내부 저장 구조와 접근 경로 등을 의미
• 저장 장치에 적합한 저장 레코드와 인덱스의 구조 등을 설계하고, 저장된 데이터와 인덱스에 빠르게 접근하게 할 수 있는 탐색기법 등을 정의
• 데이터베이스를 실제로 구축할 컴퓨터 시스템의 저장 장치와 운영체제의 특성을 고려하여, 효율적인 성능을 지원하면서도 사용할 DBMS로 구현이 가능한 물리적인 구조를 설계하는 것
• 물리적 설계의 결과물인 물리적 구조를 내부 스키마 또는 물리적 스키마라고 함.

5단계: 구현

• 이전 설계 단계의 결과물을 기반으로 DBMS에서 SQL로 작성한 명령문을 실행하여 데이터베이스를 실제로 생성

---

정규화를 이용한 데이터베이스 설계

데이터베이스를 설계한 후 설계 결과물을 검증하기 위해 사용하기도 한다. 데이터베이스를 잘못 설계하면 불필요한 데이터 중복이 발생하여 릴레이션에 대한 데이터의 삽입/삭제/수정 연산을 수행할 때 부작용이 발생할 수 있다. 이런 부작용을 이상 현상이라 한다. 이상 현상을 제거하면서 데이터베이스를 올바르게 설계해 나가는 과정이 정규화이다. 즉, 정규화는 이상 현상이 발생하지 않도록, 릴레이션을 관련이 있는 속성들로만 구성하기 위해 릴레이션을 분해하는 과정이다.

 

이상 현상의 개념

• 불필요한 데이터 중복으로 인해 발생되는 부작용들
• 함수 종속 관계 여러 개를 하나의 릴레이션에 표현하는 경우에 주로 발생한다.

이상 현상의 종류

1. 삽입 이상: 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제
2. 갱신 이상: 중복 투플 중 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제
3. 삭제 이상: 투플을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 데이터 손실의 문제

함수 종속

• 일반적으로 릴레이션에 함수적 종속성이 하나 존재하도록 정규화를 통해 릴레이션을 분해한다.
• 속성들 간의 관련성을 함수적 종속성이라고 한다.
• 함수 종속 관계는 X →Y로 표현하고 X를 결정자, Y를 종속자라고 한다.

함수 종속 관계 판단

현재 시점에 릴레이션에 포함된 속성 값만으로 판단하면 안된다. 릴레이션에서 속성 값은 계속 변할 수 있기 때문에 속성 자체가 가지고 있는 특성과 의미를 기반으로 판단해야 한다. 일반적으로 투플을 유일하게 구분하는 기본키와 후보키는 그 특성 때문에 릴레이션을 구성하는 다른 모든 속성들을 함수적으로 결정한다. 하지만 이러한 특성으로 인해 기본키나 후보키만 결정자가 될 수 있는 것은 아니다. 

 

완전 함수 종속

• 릴레이션에서 속성 집합 Y가 속성 집합 X에 함수적으로 종속되어 있지만, 속성 집합 X 전체에 종속된 것이지 일부분에 종속된 것이 아님을 의미

부분 함수 종속

• 속성 집합 Y가 속성 집합 X의 전체가 아닌 일부분에도 함수적으로 종속됨을 의미

---

기본 정규형과 정규화 과정

정규화의 개념

• 함수 종속성을 이용하여 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해해서, 이상 현상이 발생하지 않는 올바른 리레이션으로 만들어나가는 과정
• 정규화의 기본 목표는 관련이 없는 함수 종속성을 별개의 릴레이션을 표현하는 것

정규화의 종류

 

• 릴레이션이 정규화된 정도는 정규형(NF; Normal Form)으로 표현함.
• 정규형은 크게 기본 정규형과 고급 정규형으로 나뉜다.
• 기본 정규형에는 제 1정규형, 제 2정규형, 제 3정규형, 보이스/코드 정규형이 있다.
• 고급 정규형에는 제 4정규형, 제 5정규형이 있다.

 

정규형의 특징

• 정규형마다 만족시켜야 하는 제약조건이 존재한다.
• 릴레이션이 특정 정규형의 제약조건을 만족하면 릴레이션이 해당 정규형에 속한다고 표현한다.
• 정규형의 차수가 높아질수록 요구되는 제약조건이 많아지고 엄격해짐.
• 모든 릴레이션이 제 5정규형에 속해야 하는 것은 아니므로 릴레이션의 특성을 고려해서 적합한 정규형을 선택해야 함.
• 일반적으로 기본 정규형에 속하도록 릴레이션을 정규화하는 경우가 대부분이다.

---

제 1정규형(1NF)

릴레이션에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있으면 제 1정규형에 속함.

고객아이디	이벤트번호	당첨여부	등급	할인율
apple	E001, E005, E010	Y, N, Y	gold	10%
banana	E002, E005	N, Y	vip	20%
orange	E004	N	silver	5%

• 릴레이션에 속한 모든 속성이 더는 분해되지 않는 원자 값만 가져야 한다.
• 한 명의 고객이 여러 이벤트를 참여할 수 있는 이벤트번호와 당첨여부 같은 경우는 다중 값 속성으로 표현된 것
• 즉, 각 컬럼에는 하나의 값만 저장되어야 한다.
• 릴레이션이 제 1정규형을 만족하도록 하려면 테이블에서 중복되는 값의 그룹이나 배열 형태 데이터를 제거한다.

제 1정규형에 속하는 릴레이션

고객아이디	이벤트번호	당첨여부	등급	할인율
apple	E001	Y	gold	10%
apple	E005	N	gold	10%
apple	E010	Y	gold	10%
banana	E002	N	vip	20%
banana	E005	Y	vip	20%
orange	E004	N	silver	5%

• 제 1정규형에는 속하지만, 불필요한 데이터 중복으로 인해 이상 현상이 발생하는 릴레이션이 있을 수 있다.
• 위 릴레이션도 제 1정규형을 만족하지만 이상 현상이 발생할 수 있기 때문에 바람직한 릴레이션이라고 볼 수는 없다.
• 등급과 할인율 속성의 값이 중복되어 나타는 경우가 많다.

함수 종속 관계 파악

고객아이디 → 등급

고객아이디 → 할인율

등급 → 할인율

{고객아이디, 이벤트번호} → 당첨여부

• {고객아이디, 이벤트번호}  속성 집합이 기본키 역할을 담당
• 고객 한 명이 하나의 등급과 할인율을 가질 수 있으므로 고객아이디가 등급과 할인율을 유일하게 결정
• 등급에 따라 할인율이 결정되며, {고객아이디, 이벤트번호} 속성 집합이 당청여부 속성을 유일하게 결정

 

삽입 이상

고객아이디	이벤트번호	당첨여부	등급	할인율
grape	NULL(삽입 불가)	NULL	silver	5%

• 기본키는 {고객아이디, 이벤트번호} 이므로 새 고객에 대한 데이터를 삽입하려면 그 고객은 이벤트에 무조건 참여해야 한다.
• 이벤트에 참여하지 않으면 기본 키를 구성하는 이벤트번호 속성이 널 값이 되므로 개체 무결성 제약조건을 위반하기 때문
• 예를 들어, 고객아이디가 grape인 새 고객의 등급이 silver이고, 할인율이 5%라는 데이터만 삽입할 수는 없다.

갱신 이상

고객아이디	이벤트번호	당첨여부	등급	할인율
apple	E001	Y	vip	10%
apple	E005	N	vip	10%
apple	E010	Y	gold	10%

• 고객아이디가 apple인 고객의 투플이 3개이므로 이 고객의 등급과 할인율 속성 값이 중복되어 있다.
• 만약 고객의 등급이 gold에서 vip로 변경되면 세 투플의 등급 속성의 값을 vip로 변경해야 한다.
• 만약 일부 투플만 등급 속성의 값을 변경하면 동일한 고객이 2개의 등급 값을 가져 데이터 일관성을 유지할 수 없게 된다.

삭제 이상

고객아이디	이벤트번호	당첨여부	등급	할인율
orange	E004	N	silver	5%

• 고객아이디가 orange인 고객에 관련된 투플은 단 하나다.
• 이 고객이 E004 이벤트에 참여한 기록을 삭제해달라고 요구하면 이 투플을 삭제해야 한다.
• 그런데 이 투플을 삭제하면 이벤트와 관련이 없는 데이터, 즉 orange 고객의 등급과 할인율 같은 고객 정보도 함께 삭제된다.

 

이상 현상이 발생하는 이유

• 부분 함속 종속을 포함하고 있기 때문이다.
• 즉, 기본키인 {고객아이디, 이벤트번호}에 완전 함수 종속되지 못하고 일부분인 고객아이디에 종속되는 등급과 할인율 속성 때문이다.
• 기본키에 완전 함수 종속되지 못한 등급과 할인율 속성의 값이 릴레이션에 중복되어 나타나는 것은 물론, 관련이 없는 이벤트번호/ 당첨여부 속성이 하나의 릴레이션에 존재하기 때문에 여러 이상 현상이 발생하고 있다.
• 이러한 문제를 해결하기 위해선 부분 함수 종속이 제거되도록 릴레이션을 분해해야 한다.
• 릴레이션을 분해하여 부분 함수 종속을 제거하면, 분해된 릴레이션들은 제 2정규형에 속하게 되고, 앞에 있던 이상 현상이 더는 발생하지 않는다.

---

제 2정규형(2NF)

릴레이션이 제 1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2정규형에 속함.

 

[고객 릴레이션]

고객아이디	등급	할인율
apple	gold	10%
banana	vip	20%
orange	silver	5%

 

[이벤트참여 릴레이션]

고객아이디	이벤트번호	당첨여부
apple	E001	Y
apple	E005	N
apple	E010	Y
banana	E002	N
banana	E005	Y
orange	E004	N

• 제 1정규형에 속하는 릴레이션이 제 2정규형을 만족하게 하려면, 부분 함수 종속을 제거하고 모든 속성이 기본키에 완전 함수 종속되도록 릴레이션을 분해하는 정규화 과정을 거쳐야 한다.
• 이벤트참여 릴레이션에도 기본키인 {고객아이디, 이벤트번호} 와 기본키에 완전 함수 종속된 당첨여부 속성만 존재한다.
• 릴레이션이 둘로 분해되면서 등급과 할인율 속성에 대한 데이터 중복이 줄어듦을 확인할 수 있다.

 

정규화 과정에서 릴레이션을 분해할 때 주의할 점

• 분해된 릴레이션들을 자연 조인하여 분해 전의 릴레이션으로 다시 복원할 수 있어야 한다는 것
• 즉, 릴레이션이 의미상 동등한 릴레이션들로 분해되어야 하고, 릴레이션을 분해했을 때 정보 손실이 발생하지 않아야 한다.
• 이렇게 자연 조인을 하면 원래의 릴레이션으로 다시 복원할 수 있도록, 정보의 손실 없이 릴레이션을 분해하는 것을 무손실 분해라고 한다. 정규화 과정에서 수행되는 릴레이션의 분해는 무손실 분해여야 한다.

 

제 2정규형의 이상 현상

• 제 2정규형에 속하더라도 릴레이션에 이상 현상이 발생할 수 있다.
• 이벤트참여 릴레이션은 함수 종속성을 단 하나만 포함하므로 이상 현상은 더 이상 발생하지 않는다.
• 하지만 고객 릴레이션은 부분 함수 종속은 없지만 함수 종속성을 아직도 여러 개 포함하고 있어 이상 현상이 발생할 수 있다.

 

삽입 이상

고객아이디	등급	할인율
NULL (삽입 불가)	bronze	1%

• 할인율이 1%인 bronze 라는 등급이 생겼지만 아직 해당 등급에 속하는 고객이 없으면 고객 릴레이션에 삽입할 수 없다.
• bronze 등급에 속하는 고객이 없으면 기본키가 널 값이 되므로 개체 무결성 제약조건을 위반하기 때문이다.

갱신 이상

고객아이디	등급	할인율
apple	gold	15% (데이터 불일치)
banana	vip	20%
carrot	gold	10% (데이터 불일치)
orange	silver	5%

• 등급에 대한 할인율이 변경되면 해당 등급에 관련된 모든 투플에서 할인율 속성 값을 똑같이 변경해야 한다.
• 그렇지 않으면 같은 등급에 대해 할인율이 여러 개 존재하는 모순이 발생하게 된다.
• 한 투플만 할인율을 변경하면 데이터 불일치의 문제가 발생한다.

삭제 이상

고객아이디	등급	할인율
apple	gold	10%
banana	vip	20%
orange	silver	5%

• 고객 탈퇴로 인해 고객 릴레이션에서 투플이 삭제되면 등급과 할인율에 대한 정보까지 삭제된다.

 

이상 현상이 발생하는 이유

• 함수 종속 관계를 여러 개 포함하고 있어 결과적으로 이행적 함수 종속이 생기기 때문이다.
• 릴레이션을 분해하여 이행적 함수 종속을 제거하면, 분해된 릴레이션은 제 3규형에 속하게 되고 앞에 있던 이상 현상을 발생하지 않는다.

---

제 3정규형(3NF)

릴레이션이 제 2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3정규형에 속한다.

 

이행적 함수 종속

• 릴레이션을 구성하는 3개의 속성 집합 X, Z, Y에 대해 함수 종속 관계 X →Y와 Y → Z가 존재하면 논리적으로 X → Z가 성립한다. 이때 속성 집한 Z가 속성 집합 X에 이행적으로 함수 종속되었다고 한다.
• X와 Y 속성 집합의 릴레이션과 Y와 Z 속성 집합의 릴레이션으로 분해해야 한다.
• 즉, 할인율이 기본키인 고객아이디에 함수적으로 종속됨과 동시에 등급을 통해서 고객아이디에 이행적으로 종속된다.
• 고객 아이디 → 할인율, 등급→ 할인율  

 

[제 3정규형을 만족하도록 분리된 2개의 릴레이션]

고객아이디	등급
apple	gold
banana	vip
orange	silver

 

등급	할인율
gold	10%
vip	20%
silver	5%

• 고객아이디 → 등급, 등급 → 할인율의 함수 종속 관계 유지

---

보이스/코드 정규형(BCNF)

릴레이션의 함수 종속 관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속한다.

후보키를 여러 개 가지고 있는 릴레이션에 발생할 수 있는 이상 현상을 해결하기 위해 제 3정규형보다 좀 더 엄격한 제약조건를 제시

강한 제 3규형이라고도 한다. 

 

[강좌신청 릴레이션]

고객아이디	인터넷강좌	담당강사번호
apple	영어회화	P001
banana	기초토익	P002
carrot	영어회화	P001
carrot	기초토익	P004
orange	영어회화	P003
orange	기초토익	P004

• 강좌신청 릴레이션은 고객이 인터넷강좌를 신청하면 해당 강좌의 담당강사에 대한 데이터를 저장한다.
• 강좌신청 릴레이션에서는 한 고객이 인터넷강좌를 여러 개 신청할 수 있지만 동일한 인터넷강좌를 여러 번 신청할 수는 없다.
• 그리고 강사 한 명이 인터넷강좌를 하나만 담당할 수 있고, 하나의 인터넷강좌는 여러 강사가 담당할 수 있다.
• 투플의 후보키로는 {고객아이디, 인터넷강좌}와 {고객아이디, 담당강사번호}가 있고, 이 중 기본키를 {고객아이디, 인터넷강좌}로 설정
• 강사 한 명이 인터넷강좌를 하나만 담당하므로 담당강사번호가 인터넷강좌를 함수적으로 결정한다고 볼 수 있다.
• 제 3정규형에 속하지만 담당강사번호 속성이 후보키가 아님에도 불구하고 인터넷강좌 속성을 결정하므로 BCNF에 속하지 않음

 

삽입 이상

고객아이디	인터넷강좌	담당강사번호
...	...	...
NULL (삽입 불가)	중급토익	P005

• P005 강사가 중급토익 강좌를 담당하게 되었지만 이 강좌를 신청한 고객이 없다면, 이 내용을 강좌신청 릴레이션에 삽입불가
• 강좌신청 릴레이션의 기본키가 {고객아이디, 인터넷강좌} 이므로 고객아이디 속성이 NULL 값을 가질 수 없다.

갱신 이상

고객아이디	인터넷강좌	담당강사번호
apple	영어회화	P001
banana	기초토익	P002
carrot	영어회화	P001
carrot	중급토익	P004
orange	영어회화	P003
orange	기초토익	P004

• P004 강사가 담당하는 인터넷강좌가 중급토익으로 변경되면 P004 강사와 관련된 2개의 투플에서 인터넷강좌 속성의 값을 모두 중급토익으로 동일하게 변경해야 한다.
• 만약 한 개만 변경하면 P004 강사가 인터넷강좌를 여러 개 담당하게 되어 강사 한명이 인터넷강좌를 하나만 담당해야 한다는 전제 조건에 모순되는 문제 발생

삭제 이상

고객아이디	인터넷강좌	담당강사번호
apple	영어회화	P001
banana	기초토익	P002
carrot	영어회화	P001
carrot	기초토익	P004
orange	영어회화	P003
orange	기초토익	P004

• 고객아이디가 banana인 고객이 인터넷강좌 신청을 취소해서 해당 고객에 대한 투플을 삭제하면, P002 강사가 기초토익 강좌를 담당하고 있다는 정보도 함께 삭제된다.
• 이 투플은 P002 강사에 대한 정보를 담고 있는 유일한 투플이므로 삭제하면 강좌신청 릴레이션에 P002 강사에 대한 데이터를 더는 유지할 수 없다.

 

이상 현상이 발생하는 이유

• 후보키가 아니면서 함수 종속관계에서 다른 속성을 결정하는 담당강사번호 속성이 존재하기 때문이다.
• 이상 현상을 발생하지 않도록 하려면 모든 결정자가 후보키가 될 수 있도록 강좌신청 릴레이션을 분해해야 된다.

 

BCNF를 만족하도록 분해된 2개의 릴레이션

 

[고객담당강사 릴레이션]

고객아이디	담당강사번호
apple	P001
banana	P002
carrot	P001
carrot	P004
orange	P003
orange	P004

 

[강좌담당 릴레이션]

담당강사번호	인터넷강좌
P001	영어회화
P002	기초토익
P003	영어회화
P004	기초토익

---

제 4규형과 제 5규형

제 4규형은 릴레이션이 보이스/코드 정규형을 만족하면서, 함수 종속이 아닌 다치 중속을 제거해야 만족할 수 있다.

그리고 제 5규형은 릴레이션이 제 4정규형을 만족하면서 후보키를 통하지 않는 조인 종속을 제거해야 만족할 수 있다.

 

특징

• 제 4규형과 5규형은 고급 정규형으로 분류된다.
• 데이터베이스를 설계할 때 모든 릴레이션이 무조건 제 5정규형에 속하도록 분해하는 것이 아니다.
• 오히려 제 5정규형을 만족할 때까지 분해하면 비효율적이고 바람직하지 않은 경우가 많다.

---