데이터베이스 (Database) 관계 데이터베이스의 함수적 종속성과 정규화 문양세 강원대학교 IT대학 컴퓨터과학전공.

Presentation on theme: "데이터베이스 (Database) 관계 데이터베이스의 함수적 종속성과 정규화 문양세 강원대학교 IT대학 컴퓨터과학전공."— Presentation transcript:

1 데이터베이스 (Database) 관계 데이터베이스의 함수적 종속성과 정규화 문양세 강원대학교 IT대학 컴퓨터과학전공

2 강의 내용 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침
관계 DB의 함수적 종속성과 정규화 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침 함수적 종속성 (functional dependencies, FDs) 기본 키를 기반으로 한 정규형 제2정규형과 제3정규형의 일반적인 정의 BCNF (Boyce-Codd Normal Form)

3 릴레이션 스키마 설계를 위한 개략적 지침 (1/2)
관계 DB의 함수적 종속성과 정규화 관계형 데이터베이스 설계란? “좋은” 릴레이션 스키마를 생성하기 위하여 애트리뷰트들을 묶는(그룹핑하는) 과정 “좋은” 릴레이션에 대한 기준은? 릴레이션 스키마의 두 가지 수준 논리적인 “사용자 뷰(user view)” 수준 저장이 되는 “기본 릴레이션(base relation)” 수준 데이터베이스 설계는 주로 기본 릴레이션을 대상으로 함

4 릴레이션 스키마 설계를 위한 개략적 지침 (2/2)
관계 DB의 함수적 종속성과 정규화 먼저 좋은 릴레이션 설계에 관한 개괄적인 지침을 논한 후, 함수적 종속성과 정규형 개념에 관해 논의함 정규형의 종류 1NF (제1정규형) 2NF (제2정규형) 3NF (제3정규형) BCNF (Boyce-Codd 정규형)

5 릴레이션 애트리뷰트들의 의미 관계 DB의 함수적 종속성과 정규화 릴레이션 스키마를 형성하기 위해 애트리뷰트들을 집단화 하는 경우, 한 릴레이션에 속하는 애트리뷰트는 실세계에서 어떤 의미를 가져야 한다. 여러 엔티티(EMPLOYEE, DEPARTMENT, PROJECT)의 애트리뷰트들이 하나의 릴레이션에 혼합되면 의미가 불명확해지므로 좋지 않음.  하나의 릴레이션은 하나의 엔티티나 관계를 나타내는 것이 바람직함 다른 엔티티를 참조하기 위해서는 외래키 만을 사용해야 한다. 릴레이션 설계의 예: 그림 9.1 – 잘 설계된 경우 그림 9.2 – 그림 9.1의 데이터베이스 인스턴스 그림 9.3 –잘 설계되지 않은 예

6 [그림 9.1] 단순화된 COMPANY 관계 DB 스키마
외래키 EMPLOYEE ENAME SSN BDATE ADDRESS DNUMBER 기본키 DEPARTMENT 외래키 DNAME DNUMBER DMGRSSN DLOCATIONS 기본키 DEPT_LOCATIONS 외래키 PROJECT 외래키 DNUMBER DLOCATIONS PNAME PNUMBER PLOCATIONS DNUM 기본키 기본키 WORKS_ON 외래키 외래키 SSN PNUMBER HOURS 기본키

7 [그림 9.2] 그림 9.1 스키마를 위한 DB 상태 (1/2) EMPLOYEE ENAME SSN BDATE ADDRESS
DNUMBER Smith, John B. Wong, Franklin T. Zelaya, Alicia J. Wallace, Jennifer S. Narayan, Ramesh K. English, Joyce A. Jabbar, Ahmad V. Bong, James E. 09-JAN-55 08-DEC-45 19-JUL-58 20-JUN-31 15-SEP-52 31-JUL-62 29-MAR-59 10-NOV-27 731 Fondren, Houston, TX 638 Voss, Houston, TX 3321 Castle, Spring, TX 291 Berry. Bellaire, TX 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 731 Stone, Houston, TX 5 4 1 DEPARTMENT DEPT_LOCATIONS DNAME DNUMBER DMGRSSN DNUMBER DLOCATIONS Research Administration Headquarters 5 4 1 1 4 5 Houston Stafford Bellaire Sugarland

8 [그림 9.2] 그림 9.1 스키마를 위한 DB 상태 (2/2) WORKS_ON PROJECT SSN PNUMBER HOURS
PNAME PNUMBER PLOCATIONS DNUM 1 2 3 10 20 30 32.5 7.5 40.0 20.0 10.0 30.0 35.0 5.0 15.0 null ProductX ProductY ProductZ Computerization Reorganization Newbenefits 1 2 3 10 20 30 Bellaire Sugarland Houston Stafford 5 4 1

9 [그림 9.3] 좋지 않은 설계 예 – 갱신 이상 발생 (그림의 선들은 FD 설명 시 사용할 것이므로, 현재는 무시해도 됨)
관계 DB의 함수적 종속성과 정규화 (그림의 선들은 FD 설명 시 사용할 것이므로, 현재는 무시해도 됨) 여러 엔티티의 속성들이 하나의 릴레이션에 혼합되어 문제 (a) EMP_DEP 릴레이션 스키마 (EMPLOYEE + DEPARTMENT) 사원 엔티티 + 부서 엔티티 EMP_DEPT ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN (b) EMP_PROJ 릴레이션 스키마 (EMPLOYEE + PROJECT) 사원 엔티티 + 프로젝트 엔티티 EMP_PROJ SSN PNUMBER HOURS ENAME PNAME PLOCATIONS fd1 fd2 fd3

10 투플에서 중복된 정보와 갱신 이상 (1/3) 관계 DB의 함수적 종속성과 정규화 하나의 릴레이션에 하나 이상 엔티티의 애트리뷰트들을 혼합하는 것은 여러 가지 문제를 일으킨다. (그림 9.4) 정보가 중복 저장되며, 저장 공간을 낭비하게 된다. (그림 9.2의 EMPLOYEE와 DEPARTMENT  9.3 및 9.4의 EMP_DEPT 비교) 갱신 이상이 발생하게 된다: 동일한 정보를 한 릴레이션에는 변경하고, 나머지 릴레이션에서는 변경하지 않은 경우 어느 것이 정확한지 알 수 없게 된다.

11 투플에서 중복된 정보와 갱신 이상 (2/3) 갱신 이상의 종류
관계 DB의 함수적 종속성과 정규화 갱신 이상의 종류 삽입 이상 (insertion anomalies): EMP_DEPT에 객체를 삽입할 때 부서가 정해지지 않은 직원이나 직원이 없는 부서를 insert 하는데 문제가 발생함 삭제 이상 (deletion anomalies): 부서의 마지막 직원을 삭제하면 부서 정보도 없어짐 수정 이상 (modification anomalies): 부서 정보를 변경하면 부서의 모든 직원 투플에서 동일하게 변경해야 함

12 투플에서 중복된 정보와 갱신 이상 (3/3) 데이터 중복 발생
관계 DB의 함수적 종속성과 정규화 [그림 9.4] 그림 9.3의 스키마에 대한 릴레이션 예 (그림 9.2의 릴레이션들을 자연조인한 결과) ENAME SSN BDATE ADDRESS DNUMBER EMP_DEPT Smith, John B. Wong, Franklin T. Zelaya, Alicia J. Wallace, Jennifer S. Narayan, Ramesh K. English, Joyce A. Jabbar, Ahmad V. Bong, James E. 09-JAN-55 08-DEC-45 19-JUL-58 20-JUN-31 15-SEP-52 31-JUL-62 29-MAR-59 10-NOV-27 731 Fondren, Houston, TX 638 Voss, Houston, TX 3321 Castle, Spring, TX 291 Berry. Bellaire, TX 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 731 Stone, Houston, TX 5 4 1 DNAME DMGRSSN Research Administration Headquarters PNUMBER HOURS PNAME PLOCATIONS EMP_PROJ 2 3 10 20 30 32.5 7.5 40.0 20.0 10.0 30.0 35.0 5.0 15.0 null ProductX ProductY ProductZ Computerization Reorganization Newbenefits Bellaire Sugarland Houston Stafford 데이터 중복 발생

13 투플의 널값 (1/2) 릴레이션의 투플들이 (가급적) 널 값을 가지지 않도록 설계해야 함
관계 DB의 함수적 종속성과 정규화 릴레이션의 투플들이 (가급적) 널 값을 가지지 않도록 설계해야 함 널 값은 저장 단계에서 공간을 낭비하게 되고 논리적 차원에서는 조인 연산들을 지정하기 힘들고 애트리뷰트들의 의미를 이해하기 어려움 COUNT나 AVG와 같은 집단 함수들이 적용되었을 때 널 값의 해석이 모호함 널 값은 다음과 같이 여러 가지로 해석이 가능함 그 애트리뷰트가 이 투플에는 적용되지 않는다. (존재 여부를 모른다) 이 투플에서 애트리뷰트의 값이 아직 알려져 있지 않다 (존재하지만 모른다). 애트리뷰트 값이 알려져 있지만 DB에 기록되지는 않았다. 모든 널 값을 동일하게 표현하면 널 값이 갖는 여러 의미를 훼손하게 된다.

14 투플의 널값 (2/2) 널 값의 방지 기법 – 릴레이션의 분리 Employee Employee Emp_Office
관계 DB의 함수적 종속성과 정규화 널 값의 방지 기법 – 릴레이션의 분리 널 값이 많이 나타나는 애트리뷰트들은 별도 릴레이션으로 분리함 예: 사원들 중 10%만이 자기의 사무실을 가지고 있는 경우, 사원 레코드의 90%는 널 값으로 채워짐 Employee ssn ename age office_no Employee ssn ename age Emp_Office ssn office_no 분리 사무실을 가지고 있는 사원만 기록 널값이 존재 X 90%가 널 값으로 채워짐

15 가짜 투플 (Spurious Tuple) 관계 데이터베이스 설계를 잘못하게 되면, 조인 연산들이 틀린 결과를 생성할 수 있다.
관계 DB의 함수적 종속성과 정규화 관계 데이터베이스 설계를 잘못하게 되면, 조인 연산들이 틀린 결과를 생성할 수 있다. 조인 연산의 결과가 올바르기 위해서는, 릴레이션들이 “무손실 조인(lossless join)” 조건을 만족하도록 설계되어야 한다. 무손실 조인 특성: 원래의 릴레이션을 분해하여 두 릴레이션을 생성하는 경우, 분해된 두 릴레이션을 조인하면 원래의 릴레이션이 복원되어야 한다. 무손실 조인 특성이 만족되지 않으면 조인 시 원래의 릴레이션에 없던 가짜 투플이 발생함. 분해 시 (기본키, 외래키) 조합을 이용하는 것이 바람직함  키가 아닌 애트리뷰트를 매개로 분해하면 조인 시 가짜 투플이 발생할 수 있음

16 가짜 투플이 나타나는 예 (1/2) 관계 DB의 함수적 종속성과 정규화 [그림 9.5] EMP_PROJ를 다르게 표현 (a) 그림 9.3(b)의 EMP_PROJ를 두 개 릴레이션 스키마 (EMP_LOCS와 EMP_PROJ1)로 표현 (b) 그림 9.4의 EMP_PROJ 릴레이션을 EMP_LOCS 와 EMP_PROJ1 릴레이션의 애트리뷰트들 상에 프로젝트 한 결과 ENAME PLOCATIONS EMP_LOCS 기본키 SSN PNUMBER EMP_PROJ1 HOURS PNAME Smith, John B. Narayan, Ramesh K. English, Joyce A. Wong, Franklin T. Zelay, Alicia J. Jabbar, Ahmad V. Wallace, Jennifer S. Borg, James E. Bellaire Sugarland Houston Stafford 1 2 3 10 20 30 32.5 7.5 40.0 20.0 10.0 30.0 35.0 5.0 15.0 null ProductX ProductY ProductZ Computerization Reorganization Newbenefits (a) (b)

17 가짜 투플이 나타나는 예 (2/2) 관계 DB의 함수적 종속성과 정규화 [그림 9.6] EMP_PROJ1과 EMP_LOCS을 자연조인한 결과 (는 가짜 투플을 나타냄) SSN PNUMBER HOURS PNAME PLOCATIONS ENAME * * * * 1 2 3 10 20 32.5 7.5 40.0 20.0 10.0 ProductX ProductY ProductZ Computerization Reorganization Bellaire Sugarland Houston Stafford Smith, John B. English, Joyce A. Wong, Franklin T. Narayan, Ramesh K.

18 강의 내용 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침
관계 DB의 함수적 종속성과 정규화 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침 함수적 종속성 (functional dependencies, FDs) 기본 키를 기반으로 한 정규형 제2정규형과 제3정규형의 일반적인 정의 BCNF (Boyce-Codd Normal Form)

19 함수적 종속성 함수적 종속성(FD: functional dependency)은 좋은 릴레이션 설계의 정형적 기준으로 사용된다.
관계 DB의 함수적 종속성과 정규화 함수적 종속성(FD: functional dependency)은 좋은 릴레이션 설계의 정형적 기준으로 사용된다. FD와 키는 릴레이션의 정규형을 정의하기 위해 사용된다. FD는 데이터 애트리뷰트들의 의미와 애트리뷰트들 간의 상호 관계로부터 유도되는 제약조건(constraints)의 일종이다. 이 절의 구성 함수의 종속성(functional dependency)의 정의 함수적 종속성의 추론 규칙 함수적 종속성 집합의 동등성 함수적 종속성의 최소집합

20 함수적 종속성의 정의 (1/2) 함수적 종속성 함수적 종속성의 검사 방법
관계 DB의 함수적 종속성과 정규화 함수적 종속성 X와 Y를 임의의 애트리뷰트 집합이라고 할 때, X의 값이 Y의 값을 유일하게(unique) 결정한다면 “X는 Y를 함수적으로 결정한다(functionally determines)”라고 함 X → Y로 표기하고, “Y는 X에 함수적으로 종속된다” 라고 함 함수적 종속성은 모든 릴레이션 인스턴스 r(R)에 대하여 성립해야 함 함수적 종속성의 검사 방법 릴레이션 인스턴스 r(R)에 속하는 어떠한 임의의 두 투플에 대해서도 속성들의 집합 X에 대해 동일한 값을 가질 때마다 Y에 대해서도 동일한 값을 가진다면 X → Y라는 함수적 종속성이 성립한다. 즉, r(R)에서의 임의의 두 투플 t1과 t2에 대해 t1[X] = t2[X]이면, t1[Y] = t2[Y]이다. FD는 특정 릴레이션 인스턴스보다는 실세계에서 존재하는 애트리뷰트들 사이의 제약조건으로부터 유도된다.

21 함수적 종속성의 정의 (2/2) FD 제약조건의 예제
관계 DB의 함수적 종속성과 정규화 FD 제약조건의 예제 주민등록번호는 사원의 이름을 결정한다. SSN → ENAME 프로젝트 번호는 프로젝트 이름과 위치를 결정한다. PNUMBER → {PNAME, PLOCATION} 사원의 주민등록번호와 프로젝트 번호는 그 사원이 일주일 동안 그 프로젝트을 위해서 일하는 시간을 결정한다. {SSN, PNUMBER} → HOURS FD는 스키마 R에 있는 애트리뷰트들의 특성이며, 모든 릴레이션 인스턴스 r(R)에서 성립해야 하는 성질이다. K가 R의 키이면 K는 R의 모든 애트리뷰트들을 함수적으로 결정한다. (t1[K] = t2[K]인 서로 다른 두 투플이 존재하지 않기 때문에).

22 함수적 종속성의 추론규칙 (1/3) 관계 DB의 함수적 종속성과 정규화 설계자는 주어진(알려진) FD의 집합 F를 가지고, 추가로 성립하는 FD들을 추론할 수 있다. 암스트롱의 추론 규칙들 A1. (재귀성 규칙) Y ⊆ X이면, X → Y이다. A2. (부가성 규칙) X → Y이면, XZ → YZ이다. (표기: XZ는 X∪Z를 의미) A3. (이행성 규칙) X → Y이고 Y → Z이면, X → Z이다. A1, A2, A3는 sound하고 complete 추론 규칙 집합을 형성한다. 건전성 특성: A1, A2, A3로부터 유도된 모든 함수적 종속성은 모든 릴레이션 상태에 대해 성립한다.

23 함수적 종속성의 추론규칙 (2/3) 추가적으로 유용한 추론 규칙들
관계 DB의 함수적 종속성과 정규화 추가적으로 유용한 추론 규칙들 (분해 규칙) X → YZ이면, X → Y이고 X → Z이다. (합집합 규칙) X → Y이고 X → Z이면, X → YZ이다. (의사이행성 규칙) X → Y이고 WY → Z이면, WX → Z이다. 완전성 특성: 위의 세 규칙을 포함한 다른 추론 규칙들은 A1, A2, A3만으로부터 추론 가능하다.

24 함수적 종속성의 추론규칙 (3/3) FD의 집합 F의 폐포(closure): F+
관계 DB의 함수적 종속성과 정규화 FD의 집합 F의 폐포(closure): F+ F로부터 추론할 수 있는 모든 가능한 함수적 종속성들의 집합 F 하에서 속성 집합 X의 폐포(closure of X under F): X+ 함수적 종속성 집합 F를 사용하여 X에 의해 함수적으로 결정되는 모든 애트리뷰트의 집합 예 : F = {SSN→ENAME, PNUMBER→{PNAME, PLOCATION}, {SSN, PNUMBER}→HOURS} 알고리즘을 사용하여 F하에서 다음과 같은 폐포 집합들을 구할 수 있다. SSN+ = {SSN, ENAME} PNUMBER+ = {PNUMBER, PNAME, PLOCATION} {SSN, PNUMBER}+ = {SSN, PNUMBER, ENAME, PNAME, PLOCATION, HOURS} 알고리즘 9.1: F하의 X의 폐포 X+를 구하는 알고리즘 X+ := X; repeat oldX+ := X+; for each functional dependency Y→Z in F do if Y⊆X+ then X+ := X+∪Z; until (oldX+ = X+); 결국 키(K)는 자신의 폐포가 모든 애트리뷰트를 포함하는 애트리뷰트 집합을 의미, K+ = {ALL}

25 함수적 종속성 집합의 동등성 정의: Cover 두 FD 집합의 동등성
관계 DB의 함수적 종속성과 정규화 정의: Cover G의 모든 FD가 F로부터 추론될 수 있다면(즉, G+ Í F+가 성립한다면), “F가 G를 덮는다(cover한다)” 라고 말한다. 두 FD 집합의 동등성 FD의 집합 F와 G에 대하여, F의 모든 FD가 G로부터 추론될 수 있고, G의 모든 FD가 F로 부터 추론될 수 있으면 “F와 G는 동등하다(equivalent)” 라고 한다 F와 G가 다르더라도 F+ = G+이면 F와 G는 동등하다. F가 G를 cover하고, G가 F를 cover하면 F와 G는 동등하다.

26 함수적 종속성의 최소집합 (1/3) - 생략 함수적 종속성들의 집합 F의 최소(Minimal)
관계 DB의 함수적 종속성과 정규화 함수적 종속성들의 집합 F의 최소(Minimal) 다음 세 조건을 만족하는 FD 집합을 최소라고 함 1. F의 모든 함수적 종속성들의 오른쪽편 애트리뷰트가 하나이다. 2. F로부터 어떤 함수적 종속성을 제거했을 때, F와 동등한 함수적 종속성들의 집합이 될 수 없다. 3. F에서 X→A를 X의 진부분집합 Y에 대하여 Y→A로 교체했을 때, F와 동등한 함수적 종속성들의 집합이 될 수 없다. 함수적 종속성들의 집합 F의 최소 덮개(minimal cover)는 F와 동등한 함수적 종속성들의 최소 집합 Fmin을 의미함 함수적 종속성들의 최소 덮개는 여러 개 존재할 수 있다. 또한, 임의의 함수적 종속성들의 집합 F에 대해, 알고리즘을 사용하여 적어도 하나의 최소 덮개를 구할 수 있다

27 함수적 종속성의 최소집합 (2/3) - 생략 관계 DB의 함수적 종속성과 정규화 조건 1 만족 조건 2 만족 조건 3 만족

28 함수적 종속성의 최소집합 (3/3) - 생략 관계 DB의 함수적 종속성과 정규화 예: FD들의 집합 E = {B → A, D → A, AB → D}에 대해, E의 최소 커버는? 모든 FD가 조건 1(알고리즘의 단계 2)을 만족하므로, 조건 1은 완료한다. {B → A, D → A, AB → D} 조건 2(단계 3)에 의해 AB → D가 A → D 혹은 B → D로 대치될 수 있는지 결정한다. B → A에 의해 B → AB가 되고, B → AB와 AB → D에 의해 B → D가 성립한다. 따라서 AB → D는 B → D로 대치될 수 있다. 모든 FD의 왼편이 단일 애트리뷰트이므로, 조건 2(단계 3)은 완료한다. {B → A, D → A, B → D} 조건 3(단계 4)에서 중복 FD를 찾는다. B → D와 D → A에 의해 B → A가 성립한다. 따라서, B → A는 중복 FD이고, 이는 제거될 수 있다. 조건 3(단계 4)의 수행을 통해 최종적은 최소 커버는 다음과 같이 결정된다. {D → A, B → D}

29 강의 내용 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침
관계 DB의 함수적 종속성과 정규화 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침 함수적 종속성 (functional dependencies, FDs) 기본 키를 기반으로 한 정규형 제2정규형과 제3정규형의 일반적인 정의 (생략) BCNF (Boyce-Codd Normal Form)

30 기본키를 기반으로 한 정규형 이 절은 다음의 내용으로 구성됨 정규화 소개 제1정규형(First Normal Form: 1NF)
관계 DB의 함수적 종속성과 정규화 이 절은 다음의 내용으로 구성됨 정규화 소개 제1정규형(First Normal Form: 1NF) 제2정규형(Second Normal Form: 2NF) 제3정규형(Third Normal Form: 3NF)

31 정규화 소개 정규화(normalization) 정규형(normal form) 제1정규형, 제2정규형, 제3정규형, BCNF
관계 DB의 함수적 종속성과 정규화 정규화(normalization) 불만족스러운 “나쁜” 릴레이션의 애트리뷰트들을 나누어서 더 작은 “좋은” 릴레이션으로 분해하는 과정 정규형(normal form) 특정 조건을 만족하는 릴레이션 스키마의 형태 제1정규형, 제2정규형, 제3정규형, BCNF 릴레이션 스키마의 FD와 키에 기반하여 정의됨 일반적으로 업계에서는 제 3 정규형 또는 BCNF형까지 고려 주요 애트리뷰트: 키(기본키, 후보기 모두 포함)에 속하는 애트리뷰트 비주요 애트리뷰트: 주요 애트리뷰트가 아닌 애트리뷰트

32 제1정규형 (1NF) 제1정규형 제1정규형은 릴레이션 내의 릴레이션 또는 투플의 애트리뷰트 값들로서의 릴레이션을 허용하지 않음
관계 DB의 함수적 종속성과 정규화 제1정규형 애트리뷰트의 도메인이 오직 원자 값만을 포함하고, 투플의 모든 애트리뷰트가 도메인에 속하는 하나의 값을 가져야 함 복합 애트리뷰트(composite attribute), 다치 애트리뷰트(multivalue attribute), 그리고 중첩 릴레이션(nested relation) 등 비원자적(non-atomic) 애트리뷰트들을 허용하지 않은 릴레이션의 형태 제1정규형은 릴레이션 내의 릴레이션 또는 투플의 애트리뷰트 값들로서의 릴레이션을 허용하지 않음

33 [그림 9.8] 다치 애트리뷰트를 1NF로 정규화 (a) (b) (c)
관계 DB의 함수적 종속성과 정규화 (a) 제1정규형이 아닌 릴레이션 스키마 (부서는 여러 위치가 있을 수 있다.) (b) 릴레이션 인스턴스의 예 (다치 애트리뷰트를 갖기 때문에 1NF가 아니다.) (c) 중복이 포함된 제1정규형 릴레이션 DEPARTMENT (a) DNAME DNUMBER DMGRSSN DLOCATIONS DEPARTMENT (b) DNAME DNUMBER DMGRSSN DLOCATIONS Research Administration Headquarters 4 5 1 {Bellaire, Sugarland, Houston} {Stafford} {Houston} DEPARTMENT (c) DNAME DNUMBER DMGRSSN DLOCATIONS Research Administration Headquarters 4 5 1 Bellaire Sugarland Houston Stafford

34 [그림 9.9] 중첩된 릴레이션을 1NF로 정규화 (a) (b) (c)
관계 DB의 함수적 종속성과 정규화 (a) 중첩 릴레이션 PROJS를 포함하는 릴레이션 EMP_PROJ의 스키마 (b) 각 투플 안에 중첩 릴레이션을 포함하고 있는 릴레이션 MP_PROJ의 외연의 예 (c) 기본 키를 복사함으로써 EMP_PROJ를 제1정규형 릴레이션들로 분해 (a) (b) EMP_PROJ EMP_PROJ SSN ENAME PROJS SSN ENAME PROJS PNUMBERS HOURS PNUMBERS HOURS Smith, John B. Narayan, Joyce K. English, Joyce A. Wong, Franklin T. Zelaya, Alicia J. Jabbar, Ahmad V. Wallace, Jennifer S. Bong, James E. 1 2 3 10 20 30 32.5 7.5 40.0 20.0 10.0 30.0 35.0 5.0 15.0 null (c) EMP_PROJ1 SSN ENAME EMP_PROJ2 SSN PNUMBER HOURS

35 제2정규형 (2NF) 제2정규형은 기본키와 완전 함수적 종속성의 개념에 기반을 둔다.
관계 DB의 함수적 종속성과 정규화 제2정규형은 기본키와 완전 함수적 종속성의 개념에 기반을 둔다. 완전 함수적 종속성(full functional dependency): FD Y→Z에서 Y의 어떤 애트리뷰트라도 제거하면 더 이상 함수적 종속성이 성립하지 않는 경우 예제: {SSN, PNUMBER} → HOURS는 SSN → HOURS와 PNUMBER → HOURS가 성립하지 않기 때문에 완전 함수적 종속성이다. {SSN, PNUMBER} → ENAME은 SSN → ENAME이 성립하기 때문에 완전 함수적 종속성이 아니다 (이는 부분 함수 종속성(partial function dependency)이라고 부름). 제 2 정규형의 정의: 릴레이션 스키마 R의 모든 비주요 애트리뷰트들이 기본키에 대해서 완전 함수적 종속이면, R은 제2정규형(2NF)에 속한다.

36 [그림 9.10(a)] EMP_PROJ를 2NF으로 정규화
관계 DB의 함수적 종속성과 정규화 기본키 EMP_PROJ SSN PNUMBER HOURS ENAME PNAME PLOCATIONS 완전 함수적 종속성 fd1 부분 함수적 종속성 fd2 부분 함수적 종속성 fd3 2NF 정규화 EP1 EP2 EP3 SSN PNUMBER HOURS SSN ENAME PNUMBER PNAME PLOCATIONS fd1 fd2 fd3 완전 함수적 종속성 완전 함수적 종속성 완전 함수적 종속성

37 제3정규형 (3NF) 제3정규형은 이행 함수적 종속성의 개념에 기반을 둔다.
관계 DB의 함수적 종속성과 정규화 제3정규형은 이행 함수적 종속성의 개념에 기반을 둔다. 이행 함수적 종속성(transitive functional dependency): 두 FD Y → X와 X → Z에 의해서 추론될 수 있는 FD Y → Z 예제 SSN → DMGRSSN은 SSN → DNUMBER과 DNUMBER → DMGRSSN이 성립하기 때문에 이행적 함수적 종속성이다. SSN → ENAME는 SSN → X이고 X → ENAME인 애트리뷰트 집합 X가 존재하지 않기 때문에 이행적 종속성이 아니다. 제3정규형의 정의: 릴레이션 스키마 R이 제2정규형을 갖고 R의 어떤 비주요 애트리뷰트도 기본키에 대해서 이행적으로 종속되지 않으면 R은 제3정규형을 갖는다고 한다.

38 [그림 9.10(b)] EMP_DEPT를 3NF으로 정규화
관계 DB의 함수적 종속성과 정규화 EMP_DEPT ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN 이행 함수적 종속성 3NF 정규화 ED1 ED2 ENAME SSN BDATE ADDRESS DNUMBER DNUMBER DNAME DMGRSSN

39 강의 내용 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침
관계 DB의 함수적 종속성과 정규화 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침 함수적 종속성 (functional dependencies, FDs) 기본 키를 기반으로 한 정규형 제2정규형과 제3정규형의 일반적인 정의 (생략) BCNF (Boyce-Codd Normal Form)

40 2NF와 3NF의 일반적 정의 (여기부터는) 여러 후보 키를 가진 릴레이션의 정규화를 고려한다. 제2정규형 정의
관계 DB의 함수적 종속성과 정규화 (여기부터는) 여러 후보 키를 가진 릴레이션의 정규화를 고려한다. 제2정규형 정의 릴레이션 스키마 R의 모든 비주요 애트리뷰트 A가 R의 모든 후보키에 완전 함수적 종속이면 R은 제2정규형(2NF)을 갖는다고 한다. 제3정규형 정의: 주요 애트리뷰트(prime attribute): 임의의 후보키 K의 멤버인 애트리뷰트 릴레이션 스키마 R의 슈퍼키(superkey): R의 후보키를 포함한 R의 애트리뷰트들의 집합 S 릴레이션 스키마 R의 FD X → A가 성립할 때마다 (a) X가 R의 슈퍼키이거나 (b) A가 R의 주요 애트리뷰트이면 R은 제3정규형(3NF)을 갖는다고 한다. Boyce-Codd 정규형 정의: 제3정규형의 조건 중 (b)의 경우를 허락치 않는 정규형을 의미한다

41 [그림 9.11] 2NF와 3NF로 정규화 (1/2) 후보키 (a) (b)
관계 DB의 함수적 종속성과 정규화 (a) LOTS 릴레이션 스키마와 함수적 종속성 fd1부터 fd4 (b) LOTS를 2NF 릴레이션 LOTS1과 LOTS2로 분해 후보키 (a) LOTS PROPERTY_ID# COUNTY_NAME LOT# AREA PRICE TAX_RATE 완전 함수적 종속성 fd1 완전 함수적 종속성 fd2 부분 함수적 종속성 fd3 fd4 (b) LOTS1 PROPERTY_ID# COUNTY_NAME LOT# AREA PRICE fd1 fd2 LOTS2 fd4 COUNTY_NAME TAX_RATE 이행 함수적 종속성 fd3

42 [그림 9.11] 2NF와 3NF로 정규화 (2/2) (c) (d)
관계 DB의 함수적 종속성과 정규화 (c) LOTS1을 3NF 릴레이션 LOTS1A와 LOTS1B로 분해 (d) LOTS의 정규화 요약 (c) LOTS1A LOTS1B PROPERTY_ID# COUNTY_NAME LOT# AREA AREA PRICE fd1 fd4 fd2 (d) LOTS 1NF LOTS1 LOTS2 2NF LOTS1A LOTS1B LOTS 3NF

43 강의 내용 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침
관계 DB의 함수적 종속성과 정규화 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침 함수적 종속성 (functional dependencies, FDs) 기본 키를 기반으로 한 정규형 제2정규형과 제3정규형의 일반적인 정의 BCNF (Boyce-Codd Normal Form)

44 BCNF (Boyce-Codd Normal Form)
관계 DB의 함수적 종속성과 정규화 릴레이션 스키마 R에서 성립하는 임의의 FD X → A에서 X가 R의 슈퍼키이면 R은 Boyce-Codd 정규형(BCNF)을 갖는다고 한다. 각 정규형은 그의 선행 정규형보다 더 엄격한 조건을 갖는다. 즉, 모든 제2정규형 릴레이션은 제1정규형을 갖는다. 모든 제3정규형 릴레이션은 제2정규형을 갖는다. 모든 BCNF 릴레이션은 제3정규형을 갖는다. 제3정규형에는 속하나 BCNF에는 속하지 않는 릴레이션이 존재한다. 관계 데이터베이스 설계의 목표는 각 릴레이션이 BCNF(또는 3NF)를 갖게 하는 것이다.

45 BCNF으로 정규화 (생략) 관계 DB의 함수적 종속성과 정규화 (a) BCNF로 정규화하는 과정에서 종속성 fd2가 없어지는 경우 (정보의 손실이 발생하는 경우임) (b) 3NF이나 BCNF가 아닌 릴레이션 R 후보키(슈퍼키) (a) LOTS1A 제 3 정규형 PROPERTY_ID# COUNTY_NAME LOT# AREA fd1 fd2 이행 함수적 종속성 fd5 이행 종속성의 대상이 슈퍼키이므로 제 3 정규형을 만족함 BCNF 정규화 LOTS1AX LOTS1AY BCNF PROPERTY_ID# AREA LOT# AREA COUNTY_NAME (b) R A B C fd1 fd1

46 요약 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침 함수적 종속성 (functional dependencies, FDs)
관계 DB의 함수적 종속성과 정규화 릴레이션 스키마를 설계하는 몇 가지 개략적인 지침 갱신이상, 널값 발생, 가짜투플 함수적 종속성 (functional dependencies, FDs) 정의, 추론규칙, 동등성 정규형 기본 키를 기반으로 한 정규형 제 2 정규형과 제 3 정규형의 일반적인 정의 BCNF (Boyce-Codd Normal Form)