Ch13. RDB의 함수적 종속성과 정규화 이론

1. 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침

좋은 관계형 스키마

논리적/개념적 레벨 : 릴레이션 안의 데이터를 얼마나 잘 표현하고 있는가

구현/저장 레벨 : 기본 릴레이션의 스키마가 어떻게 구성되어 있는가

 

릴레이션 스키마 설계의 수준(퀄리티)을 결정하기 위한 척도로 사용할 수 있는 네 가지 지침

1. 애트리뷰트들의 의미가 스키마에서 명확한지 확인함
2. 튜플들에서 중복되는 값들을 줄임
3. 튜플들에서 널 값들을 줄임
4. 가짜 튜플을 허용하지 않음

 

첫번째 가이드라인(지침)

1. 의미가 쉽게 전달되도록 릴레이션 스키마를 설계한다.
2. 여러 개의 엔티티 타입과 관계 타입의 애트리뷰트들을 섞어서 하나의 릴레이션으로 구성하면 안된다.
   -> 하나의 엔티티 타입이나 관계 타입을 하나의 스키마로 만들어야함

 

2. 튜플에서 중복된 정보와 갱신 이상

두번째 가이드라인

1. 릴레이션의 삽입, 삭제 또는 수정할 때 이상이 생기지 않도록 기본 릴레이션 스키마를 설계한다.
2. 만약 어떤 이상이 존재하면 그것을 잘 이해하여 데이터베이스를 수정하는 프로그램들이 올바르게 동작할 수 있도록 작성한다.
3. 이 가이드라인은 어떤 질의들을 효율적으로 수행하기 위해서 위반이 되는 경우가 존재한다.

 

스키마 설계의 목표중 하나는 기본 릴레이션들에 의해 사용된느 저장공간을 최소화하는 것임

• 어떻게 애트리뷰트들을 묶어서 릴레이션 스키마로 만드느냐에 따라 필요한 저장 공간의 크기가 크게 달라짐

데이터 낭비(중복)로 일어나는 큰 문제는 갱신 연산이 일어나는 갱신 이상이 나타남

갱신 이상 종류

• 삽입 이상
  • EMP_DEPT에서 6번 부서를 추가하려고 하는데(EMP에 해당하는 값은 NULL로 설정), EMP의 Ssn이 NULL이라 개체 무결성 제약조건에 위반되어 삽입을 못하기 때문에 이상이 생김
  • 삽입할 때 관련없는 정보도 같이 들어가는 현상
• 삭제 이상
  • Borg, James E.를 삭제하려고 하면 Headquarters에 있는 직원이 한 명도 없어서 Dmgr_ssn이 참조 무결성 제약조건이 위반되어 이상이 생김
  • 삭제할 때 관련없는 정보도 같이 삭제되는 현상
• 수정 이상
  • Dmgr_ssn이 333445555가 있는 값을 123456789로 변경할 때, 값을 변경해야할 사원들은 모두 변경되야 한다. 하지만 변경되지 않는 것이 있으면 이상이 생김(일관성을 잃음)

 

3. 튜플의 널 값

 

세번째 가이드라인

1. 널 값을 자주 가질 수 있는 애트리뷰트를 가능한 한 기본 릴레이션의  애트리뷰트로 포함하지 않는다
2. 만약 널값을 포함할 수 밖에 없다면, 예외적인 경우에만 포함하고 대다수 튜플들에 대해서는 널 값이 없도록 한다.

애트리뷰트중 상당수가 널 값을 가지게 된다면..

1. 저장공간 낭비
2. 애트리뷰트들의 의미를 이해하기 어렵고 조인 연산을 명시하기 힘듦
3. 집단 함수(aggregate operations)들이 적용됐을 때 널 값을 어떻게 처리할지 문제
4. 널 값은 여러가지 해석이 가능함: 값이 적용되지 않는다, 값이 알려져 있지 않다, 값을 알고 있지만 아직 기록되지 않았다

 

4. 가짜 튜플의 생성

네번째 가이드라인

1. 가짜 튜플들이 생성되지 않도록 하기 위해서, 기본키나 외래키 같이 적절하게 관련된 애트리뷰트를 가지고 동등 조건으로 조인할 수 있는 릴레이션 스키마를 설계함(= 조인할 때 최대한 PK-FK 관계로 조인해야함)
2. 무손실 조인 성질이라고도 부름(Nonadditive/losslesss join property)

 

일반적인 가짜 튜플

• 허용되지 않은 잘못된 정보

 

 

5. 함수적 종속성

함수적 종속성은 릴레이션 모델에서 가장 중요한 제약조건중 하나이고, 데이터베이스 스키마에서 이상이나 중복을 제거하는 새롭게 디자인하는 것에 대한 지식이다.

 

함수적 종속성은 X → Y로 표현

릴레이션 스키마 R의 릴레이션 상태 r을 구성할 수 있는 가능한 튜플들에 대한 제약조건을 지정하는 것

if $t_1[X]=t_2[X]$ for any $t_1,t_2 \in $ r, then $t_1[Y] =t_2[Y]$ 를 만족함

이때 임의의 튜플의 X부분의 값이 Y부분의 값을 고유하게(함수적으로) 결정한다. 이 경우에 X에서 Y로 함수적 종속성이 존재하다고 말하거나, Y가 X에 함수적으로 종속된다고 말한다.

 

함수적 종속성은 FD라고 부르기도 한다.

• X = LHS(left-hand side, 왼편), 결정자
• Y = RHS(right-hand side, 오른편), 종속자
• 결정자와 종속자 둘 다 하나 이상의 값을 가질 수 있음

 

ex) EMPLOYEE와 PROJECT가 합쳐진 테이블 EMP_PROJ

1. Ssn → Ename

2. Pnumber → {Pname, Plocation}

3. {Ssn, Pnumber} → Hours

 

함수적 종속성의 특징

• FD는 릴레이션 스키마 R의 특성이지,  R의 어떤 특정 적법한 릴레이션 상태 r의 특성은 아님
• FD는 주어진 릴레이션 외연 r로부터 자동적으로 추측할 수 있는 것이 아니라 R의 애트리뷰트들의 의미를 아는 사람이 명시적으로 정의해야함
• 모든 데이터가 준수해야함

 

사소한 종속성(Trivial Dependencies) 

함수적 종속성 $A_1A_2...A_n$ → B가 있을 때, B가 A에 속하는 경우

ex) {Ssn, Pnumber} → Pnumber

 

 

 

6. 기본키를 기반으로 한 정규형

기본키와 함수적 종속성이 주어지면 아래와 같은 정규형을 결정할 수 있음

• 1st normal form(1NF)
• 2nd normal form(2NF)
• 3rd normal form(3NF)
• Boyce-Codd normal form(BCNF)

다치 종속성에 의해 결정

• 4th normal form(4NF)

조인 종속성에 의해 결정

• 5th normal form(5NF)

 

데이터 정규화는 아래 내용을 위해 함수적 종속성과 기본키를 기반으로 주어진 릴레이션 스키마를 분석하는 과정임

• 중복을 최소화
• 삽입, 삭제, 수정 이상을 최소화

분해에 의한 정규화 과정은 릴레이션 스키마들이 함께 가져야 하는 아래 특성들을 만족하도록 해야함

• 비부가적 조인 / 무손실 조인 특성 : 가짜 튜플 문제가 발생하지 말아야함
• 종속성 보존 특성 : 각 함수적 종속성이 분해 후 생성된 릴레이션들의 일부에서 반드시 표현되야함

 

역정규화(비정규화, denormalization) : 더 높은 정규형 릴레이션들을 조인한 결과를 더 낮은 정규형에 속하는 곳에 저장하는 과정(과정이 정규화의 반대임)

 

7. 키의 정의와 키에 참여하는 애트리뷰트

후보키는 슈퍼키와 최소성을 만족해야함

후보키에 속하는 애트리뷰트를 주요 애트리뷰트(prime attribute)라 하고, 그렇지 않은 애트리뷰트는 비주요 애트리뷰트(nonprime attribute)라고 함

 

 

8. 제1정규형

제1정규형은 애트리뷰트의 도메인이 오직 원자값(atomic value)들만 포함하고, 튜플의 모든 애트리뷰트 값은 그 애트리뷰트 도메인에 속하는 하나의 값이어야 함

 

ex1) p.414에서 (b)의 Dlocation은 {Bellaire, Sugarland, Houston}처럼 하나의 값이 아닌 여러 값을 가지고 있어서 원자값이 아니기 때문에 제1정규형이 아님

ex2) p.414에서 (c)는 모든 값이 전부 한 개이므로 제1정규형임

 

 

제1정규형이 아닌 것에서 제1정규형으로 변환하는 방법

1. 제1정규형의 규칙을 어기는 값을 분리하는데, 분리할 때 기본키를 옆에 붙여서 나가게 하면 모두 제1정규형에 해당할 수 있음
2. 한 값에 여러개 값을 가지고 있는 것을 하나씩 분리해서 새로 저장하여 원자값을 같게함
3. 한 값에 여러개 값을 가지고 있게 하지말고, 이어서 값을 붙여주게함.
   ex) [Research, 5, 333445555, {Bellaire, Sugarland, Houston}]을 [Research, 5, 333445555, Bellaire, Sugarland, Houston]으로 수정 -> 많은 널 값이 생길 수도 있음

 

9. 제2정규형

완전한 함수적 종속성이란?(FFD)

X → Y일 때, X로부터 임의의 애트리뷰트 A를 제거하면 함수적 종속성 X → Y가 성립되지 않는 경우를 뜻함

ex) WORKS_ON (Ssn, Pnumber, Hours)에서 {Ssn, Pnumber} → Hours이지만 Ssn/Pnumber → Hours는 성립하지 않음

 

부분 종속성(PD / PFD)

ex) EMPLOYEE(Ssn, Ename, Bdate)에서 {Ssn, Ename}→Bdate도 성립하고, Ssn→Bdate도 성립할 때

 

제2정규형 정의 :  릴레이션 스키마 R의 모든 비주요 애트리뷰트(nonprime attribute)들이 R의 기본키에 대해 완전한 함수적 종속성을 만족하면 제2정규형이라고 한다.

 

ex) EMP_PROJ = {Ssn, Pnumber, Hours, Ename, Pname, Plocation}이 있을 때

EP1 = {Ssn, Pnumber, Hours} , EP2 = {Ssn, Ename}, EP3 = {Pnumber, Pname, Plocation}으로 나누면 제2정규형을 만족

 

 

 

10. 제3정규형

이행적 종속성이란?(transitive dependency)

X→Z를 만족하고, Z→Y를 만족할 때,  X→Y를 만족하므로 이것을 이행적 종속성이라고 부름

 

ex) EMP_DEPT에서 Ssn은 Dnumber를 결정하는데, Dnumber는 Dname과 Dmgr_ssn을 결정하므로 Ssn이 Dnumber, Dname, Dmgr_ssn을 결정한다고 할 수 있음

 

제3정규형 정의 : 제2정규형을 만족하면서 모든 비주요 애트리뷰트가 기본키의 이행적 종속성을 만족하지 않을 때를 제3정규형이라고 한다. 즉, 비주요 애트리뷰트들이 완전한 함수적 종속이면서 이행적 종속성이 아니어야한다.

 

 

EMP_DEPT에서 Ssn과 Dname, Dmgr_ssn은 이행적 종속성을 만족하므로
ED1 = {Ename, Ssn, Bdate, Address, Dnumber}, ED2 = {Dnumber, Dname, Dmgr_ssn}으로 분해하면 둘 다 제3정규형을 만족함

 

 

 

정규형	검사	방법(정규화)
제1정규형	다치 애트리뷰트 혹은 중첩 릴레이션을 가지지 말아야함	다치 애트리뷰트나 중첩 릴레이션을 위한 새로운 릴레이션을 생성
제2정규형	비주요 애트리뷰트들이 기본키에 완전 종속되는지 확인	부분키와 이에 종속하는 애트리뷰트들에 대해 새로운 릴레이션으로 분해함. 분해되기 전의 원래의 기본키를 가진 릴레이션을 유지하고 그 릴레이션에서 다른 애트리뷰트들은 완전히 그 키에 종속하도록 함
제3정규형	비주요 애트리뷰트들이 다른 애트리뷰트에 함수적으로 종속해서는 안된다	비주요 애트리뷰트들을 함수적으로 결정하는, 키가 아닌 애트리뷰트들을 포함하는 새로운 릴레이션으로 분해함

 

 

제3정규형의 일반적 정의

릴레이션 스키마 R의 모든 함수적 종속성 X→A에 대해서

(a) X가 R의 슈퍼키이거나

또는

(b) A가 R의 주요 애트리뷰트(prime attribute)

하나를 만족하면 제 3정규형이다.

 

11. Boyce-Codd Normal Form(BCNF)

위의 제3정규형의 일반적 정의에서 조건(b)를 제외하면 BCNF이다.

즉, 릴레이션 스키마 R의 모든 함수적 종속성 X→A에 대해서 X가 R의 슈퍼키여야 BCNF라고 할 수 있다.

그래서 BCNF는 제3정규형에 속하지만 제3정규형보다 더 엄격한 정규형이다.

 

ex)

FD1 : {Student, Course} → Instructor

FD2 : Instructor → Course

이것은 제3정규형이지만 BCNF는 하니다. 그래서 BCNF를 만족하기 위해서 분해하는 방법은

(Instructor, Course), (Instructor, Student) 가 적합하다.

 

 

 

 

제4정규형과 제5정규형은 아주 특수한 경우에서만 발생함

12. 제4정규형

다치 종속성이란?(MVD : Multivalued Dependency)

결정자가 하나 주어지면, 종속자가 한 개가 아닌 하나의 집합이 대응되는 종속성

 

ex1) Smith 직원은 프로젝트 X와 Y에 참가함. Smith→{X, Y}

ex2) Smith 직원의 부양가족의 이름은 John과 Anna임. Smith → {John, Anna}

위 둘을 한 번에 표현한다면 Smith ->> Pname | Dname이라고 함

 

EMP

Ename	Pname	Dname
Smith	X	John
Smith	Y	Anna
Smith	X	Anna
Smith	Y	John

 

문제점 : 튜플을 삽입하거나 삭제할 때, 데이터 중복성이 있어서 여러 개를 삽입하거나 여러 개를 삭제해야함

 

제4정규형 정의 :  테이블에 다치 종속성이 존재하지 않으면 제4정규형이라고 함

 

위에 있는 표를 아래로 바꾸면 제4정규형을 만족함

 

EMP_PROJECTS

Ename	Pname
Smith	X
Smith	Y

EMP_DEPENDENTS

Ename	Dname
Smith	John
Smith	Anna

 

 

 

13. 제5정규형(5NF / PJNF - Projection-Join Normal Form)

조인 종속성 : 지금까지 우리는 어떤 문제점이 생기면 릴레이션을 두 개로 나눠서 문제를 해결 했는데, 이건 N개의 릴레이션으로 나눠서 해결해야함(다중 분해 multiway decomposition)

 

제5정규형 정의 : R을 N개의 릴레이션으로 분해할 때, 각 릴레이션이 R의 슈퍼키일 때 제5정규형이라고 함. 즉, 모든 릴레이션에 있는 애트리뷰트가 슈퍼키들로 구성되야함. 그리고 2개의 릴레이션을 해도 원래 릴레이션을 만들 수 없어야함

 

 

 

대부분의 테이블 설계는 제3정규형인지, BCNF인지 확인하면 끝임