데이터베이스 (Database) 관계데이터베이스의함수적종속성과정규화 문양세강원대학교 IT특성화대학컴퓨터과학전공
강의내용 릴레이션스키마를설계하는몇가지개략적인지침 함수적종속성 (functional dependencies, FDs) 기본키를기반으로한정규형 제 2 정규형과제 3 정규형의일반적인정의 BCNF (Boyce Codd Normal lf Form) Page 2
릴레이션스키마설계를위한개략적지침 (1/2) 관계형데이터베이스설계란? 좋은 릴레이션스키마를생성하기위하여애트리뷰트들을묶는 ( 그룹핑하는 ) 과정 좋은 릴레이션에대한기준은? 릴레이션스키마의두가지수준 논리적인 사용자뷰 (user view) 수준 저장이되는 기본릴레이션 (base relation) 수준 데이터베이스설계는주로기본릴레이션을대상으로함 Page 3
릴레이션스키마설계를위한개략적지침 (2/2) 먼저좋은릴레이션설계에관한개괄적인지침을논한후, 함수적종속성과정규형개념에관해논의함 정규형의종류 1NF ( 제 1 정규형 ) 2NF ( 제 2 정규형 ) 3NF ( 제 3 정규형 ) BCNF (Boyce Codd 정규형 ) Page 4
릴레이션애트리뷰트들의의미 릴레이션스키마를형성하기위해애트리뷰트들을집단화하는경우, 한릴레이션에속하는애트리뷰트는실세계에서어떤의미를가져야한다. 여러엔티티 (EMPLOYEE, DEPARTMENT, PROJECT) 의애트리뷰트들이 하나의릴레이션에혼합되면의미가불명확해지므로좋지않음. 하나의릴레이션은하나의엔티티나관계를나타내는것이바람직함 다른엔티티를참조하기위해서는외래키만을사용해야한다. 릴레이션설계의예 : 그림 9.1 잘설계된경우 그림 92 9.2 그림 91 9.1 의데이터베이스인스턴스 그림 9.3 잘설계되지않은예 Page 5
[ 그림 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 기본키 Page 6
[ 그림 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. 123456789 999887777 987654321 666884444 453453453 987987987 888665555 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 5 4 4 5 5 4 1 DEPARTMENT DNAME DNUMBER DMGRSSN DEPT_LOCATIONS DNUMBER DLOCATIONS Research Administration 5 4 987654321 1 4 Headquarters 1 888665555 5 5 5 Houston Stafford Bellaire Sugarland Houston Page 7
[ 그림 9.2] 그림 9.1 스키마를위한 DB 상태 (2/2) WORKS_ON SSN PNUMBER HOURS 123456789 1 32.5 123456789 2 7.5 666884444 3 40.0 453453453 1 20.0 453453453 2 20.0 2 10.0 3 10.00 10 10.0 20 10.0 999887777 30 30.0 999887777 10 10.0 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 987654321 20 15.0 888665555 20 null PROJECT PNAME PNUMBER PLOCATIONS DNUM ProductX 1 Bellaire 5 ProductY 2 Sugarland 5 ProductZ 3 Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford 4 Page 8
[ 그림 9.3] 좋지않은설계예 갱신이상발생 ( 그림의선들은 FD 설명시사용할것이므로, 현재는무시해도됨 ) 여러엔티티의속성들이하나의릴레이션에혼합되어문제 (a) EMP_DEP 릴레이션스키마 (EMPLOYEE + DEPARTMENT) EMP_DEPT 사원엔티티 + 부서엔티티 ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN (b) EMP_PROJ PROJ 릴레이션스키마 (EMPLOYEE + PROJECT) EMP_PROJ 사원엔티티 + 프로젝트엔티티 SSN PNUMBER HOURS ENAME PNAME PLOCATIONS fd1 fd2 fd3 Page 9
투플에서중복된정보와갱신이상 (1/3) 하나의릴레이션에하나이상엔티티의애트리뷰트들을혼합하는것은여 러가지문제를일으킨다. ( 그림 94) 9.4) 정보가중복저장되며, 저장공간을낭비하게된다. ( 그림 92 9.2 의 EMPLOYEE 와 DEPARTMENT 93 9.3 및 94 9.4 의 EMP_DEPT 비교 ) 갱신이상이발생하게된다 : 동일한정보를한릴레이션에는변경하고, 나머지릴레이션에 서는변경하지않은경우어느것이정확한지알수없게된다. Page 10
투플에서중복된정보와갱신이상 (2/3) 갱신이상의종류 삽입이상 (insertion anomalies): EMP_DEPT 에객체를삽입할때부서가정해지지않은직원이나직원이없는부서를 insert 하는데문제가발생함 삭제이상 (deletion anomalies): 부서의마지막직원을삭제하면부서정보도없어짐 수정이상 (modification anomalies): 부서정보를변경하면부서의모든직원투플에서동일하게변경해야함 Page 11
투플에서중복된정보와갱신이상 (3/3) [ 그림 9.4] 그림 9.3 의스키마에대한릴레이션예 ( 그림 9.2 의릴레이션들을자연조인한결과 ) EMP_DEPT 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. 123456789 999887777 987654321 666884444 453453453 987987987 888665555 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. Blli Bellaire, TX 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 731 Stone, Houston, TX 5 5 4 4 5 5 4 1 데이터중복발생 데이터중복발생 DNAME Research Research Administration Administration i ti Research Research Administration Headquarters EMP_PROJ SSN PNUMBER HOURS ENAME PNAME PLOCATIONS 123456789 123456789 666884444 453453453 453453453 999887777 999887777 987987987 987987987 987654321 987654321 888665555 1 2 3 1 2 2 3 10 20 30 10 10 30 30 20 20 32.5 7.5 40.00 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.00 15.0 null Smith, John B. Smith, John B. Narayan, aya Ramesh K. English, Joyce A. English, Joyce A. Wong, Franklin T. Wong, Franklin T. Wong, Franklin T. Wong, Franklin T. Zelaya, Alicia J. Zelaya, Alicia J. Jabbar, Ahmad V. Jabbar, Ahmad V. Wallace, Jennifer S. Wallace, Jennifer S. Bong, James E. ProductX ProductY PodutZ ProductZ ProductX ProductY ProductY ProductZ Computerization Reorganization Newbenefits Computerization Computerization Newbenefits Newbenefits Reorganization Reorganization Bellaire Sugarland Houston Bellaire Sugarland Sugarland Houston Stafford Houston Stafford Stafford Stafford Stafford Stafford Houston Houston DMGRSSN 987654321 987654321 987654321 888665555 Page 12
투플의널값 (1/2) 릴레이션의투플들이 ( 가급적 ) 널값을가지지않도록설계해야함 널값은저장단계에서공간을낭비하게되고 논리적차원에서는조인연산들을지정하기힘들고 애트리뷰트들의의미를이해하기어려움 COUNT 나 AVG 와같은집단함수들이적용되었을때널값의해석이모호함 널값은다음과같이여러가지로해석이가능함 그애트리뷰트가이투플에는적용되지않는다. ( 존재여부를모른다 ) 이투플에서애트리뷰트의값이아직알려져있지않다 ( 존재하지만모른다 ). 애트리뷰트값이알려져있지만 DB 에기록되지는않았다. 모든널값을동일하게표현하면널값이갖는여러의미를훼손하게된다. Page 13
투플의널값 (2/2) 널값의방지기법 릴레이션의분리 널값이많이나타나는애트리뷰트들은별도릴레이션으로분리함 예 : 사원들중 10% 만이자기의사무실을가지고있는경우, 사원레코드의 90% 는널값으 로채워짐 Employee ssn ename age office_no 분리 Employee ssn ename age Emp_Office ssn office_no 90% 가사무실을가지고널값으로채워짐있는사원만기록널값이존재 X Page 14
가짜투플 (Spurious Tuple) 관계데이터베이스설계를잘못하게되면, 조인연산들이틀린결과를생성할수 있다. 조인연산의결과가올바르기위해서는, 릴레이션들이 무손실조인 (lossless join) 조건을만족하도록설계되어야한다. 무손실조인특성 : 원래의릴레이션을분해하여두릴레이션을생성하는경우, 분해된두릴레이션을조인하면원래의릴레이션이복원되어야한다. 무손실조인특성이만족되지않으면조인시원래의릴레이션에없던가짜투플이 발생함. 분해시 ( 기본키, 외래키 ) 조합을이용하는것이바람직함 키가아닌애트리뷰트를매개로분해하면조인시가짜투플이발생할수있음 Page 15
가짜투플이나타나는예 (1/2) [ 그림 9.5] EMP_PROJ 를다르게표현 (a) 그림 9.3(b) 의 EMP_PROJ를두개릴레이션스키마 (EMP_LOCS와 EMP_PROJ1) 로표현 (b) 그림 9.4의 EMP_PROJ 릴레이션을 EMP_LOCS 와 EMP_PROJ1 릴레이션의애트리뷰트들상에프로젝트한결과 (a) EMP_LOCS EMP_PROJ1 ENAME PLOCATIONS SSN PNUMBER HOURS PNAME PLOCATIONS (b) EMP_LOCS ENAME 기본키 PLOCATIONS 기본키 EMP_PROJ1 SSN PNUMBER HOURS PNAME PLOCATIONS Smith, John B. Bellaire 123456789 1 32.5 ProductX Bellaire Smith, John B. Sugarland 123456789 2 7.5 ProductY Sugarland Narayan, Ramesh K. Houston 666884444 3 40.0 ProductZ Houston English, Joyce A. Bellaire 453453453 1 20.0 ProductX Bellaire English, Joyce A. Sugarland 453453453 2 20.0 ProductY Sugarland Wong, Franklin T. Sugarland 2 10.0 ProductY Sugarland Wong, Franklin T. Wong, Franklin T. Zelay, Alicia J. Jabbar, Ahmad V. Wallace, Jennifer S. Wallace, Jennifer S. Houston Stafford Stafford Stafford Stafford Houston 999887777 999887777 987987987 Borg, James E. Houston 987987987 987654321 987654321 888665555 Page 16 3 10 20 30 10 10 30 30 20 20 10.00 10.0 10.0 30.0 10.0 35.0 5.0 20.0 15.0 null ProductZ Computerization Reorganization Newbenefits Computerization Computerization Houston Stafford Houston Stafford Stafford Stafford Newbenefits Stafford Newbenefits Stafford Reorganization Houston Reorganization Houston
가짜투플이나타나는예 (2/2) [ 그림 9.6] EMP_PROJ1 과 EMP_LOCS 을자연조인한결과 ( 는가짜투플을나타냄 ) SSN PNUMBER HOURS PNAME PLOCATIONS ENAME 123456789 *123456789 123456789 *123456789 *123456789 666884444 *666884444 *453453453 453453453 *453453453 453453453 *453453453 * * * * 1 1 2 2 2 3 3 1 1 2 2 2 2 2 2 3 3 10 20 20 32.5 32.5 7.5 7.5 7.5 40.0 40.00 20.0 20.0 20.0 20.0 20.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0 10.0 ProductX ProductX ProductY ProductY ProductY ProductZ ProductZ ProductX ProductX ProductY ProductY ProductY ProductY ProductY ProductY ProductZ ProductZ Computerization Computerization Reorganization Bellaire Bellaire Sugarland Sugarland Sugarland Houston Houston Bellaire Bellaire Sugarland Sugarland Sugarland Sugarland Sugarland Sugarland Houston Houston Stafford Houston Houston Smith, John B. English, Joyce A. Smith, John B. English, Joyce A. Wong, Franklin T. Narayan, Ramesh K. Wong, Franklin T. Smith, John B. English, Joyce A. Smith, John B. English, Joyce A. Wong, Franklin T. Smith, John B. English, Joyce A. Wong, Franklin T. Narayan, Ramesh K. Narayan, Ramesh K. Narayan, Ramesh K. Wong, Franklin T. Narayan, Ramesh K. Page 17
강의내용 릴레이션스키마를설계하는몇가지개략적인지침 함수적종속성 (functional dependencies, FDs) 기본키를기반으로한정규형 제 2 정규형과제 3 정규형의일반적인정의 BCNF (Boyce Codd Normal lf Form) Page 18
함수적종속성 함수적종속성 (FD: functional dependency) 은좋은릴레이션설계의 정형적기준으로사용된다. FD 와키는릴레이션의정규형을정의하기위해사용된다. FD 는데이터애트리뷰트들의의미와애트리뷰트들간의상호관계로부터 유도되는제약조건 (constraints) 의일종이다. 이절의구성 함수의종속성 (functional dependency) 의정의 함수적종속성의추론규칙 함수적종속성집합의동등성 함수적종속성의최소집합 Page 19
함수적종속성의정의 (1/2) 함수적종속성 X 와 Y 를임의의애트리뷰트집합이라고할때, X 의값이 Y 의값을유일하게 (unique) 결정 한다면 X 는 Y 를함수적으로결정한다 (functionally determines) 라고함 X Y 로표기하고, Y 는 X 에함수적으로종속된다 라고함 함수적종속성은모든릴레이션인스턴스 r(r) 에대하여성립해야함 함수적종속성의검사방법 릴레이션인스턴스 r(r) 에속하는어떠한임의의두투플에대해서도속성들의집합 X 에 대해동일한값을가질때마다 Y 에대해서도동일한값을가진다면 X Y 라는함수적종 속성이성립한다. 즉, r(r) 에서의임의의두투플 t 1 과 t 2 에대해 t 1 [X] = t 2 [X] 이면, t 1 [Y] = t 2 [Y] 이다. FD 는특정릴레이션인스턴스보다는실세계에서존재하는애트리뷰트들 사이의제약조건으로부터유도된다. Page 20
함수적종속성의정의 (2/2) FD 제약조건의예제 주민등록번호는사원의이름을결정한다. SSN ENAME 프로젝트번호는프로젝트이름과위치를결정한다. PNUMBER {PNAME, PLOCATION} 사원의주민등록번호와프로젝트번호는그사원이일주일동안그프로젝트을위해서 일하는시간을결정한다. {SSN, PNUMBER} HOURS FD 는스키마 R 에있는애트리뷰트들의특성이며, 모든릴레이션인스턴 스 r(r) 에서성립해야하는성질이다. K 가 R 의키이면 K 는 R 의모든애트리뷰트들을함수적으로결정한다. (t 1 [K] = t 2 [K] 인서로다른두투플이존재하지않기때문에 ). Page 21
함수적종속성의추론규칙 (1/3) 설계자는주어진 ( 알려진 ) 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 로부터유도된모든함수적종속성은모든릴레이 션상태에대해성립한다. Page 22
함수적종속성의추론규칙 (2/3) 추가적으로유용한추론규칙들 ( 분해규칙 ) X YZ 이면, X Y 이고 X Z 이다. ( 합집합규칙 ) X Y 이고 X Z 이면, X YZ 이다. ( 의사이행성규칙 ) X Y 이고 WY Z 이면, WX Z 이다. 완전성특성 : 위의세규칙을포함한다른추론규칙들은 A1, A2, A3 만으로 부터추론가능하다. Page 23
함수적종속성의추론규칙 (3/3) 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 하에서다음과같은폐포집합들을구할수있다. 알고리즘 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 + ); SSN + = {SSN, ENAME} PNUMBER + = {PNUMBER, PNAME, PLOCATION} {SSN, PNUMBER} + = {SSN, PNUMBER, ENAME, PNAME, PLOCATION, HOURS} 결국키 (K) 는자신의폐포가모든애트리뷰트를포함하는애트리뷰트집합을의미, K + = {ALL} Page 24
함수적종속성집합의동등성 정의 : Cover G 의모든 FD 가 F 로부터추론될수있다면 ( 즉, G + F + 가성립한다면 ), F 가 G 를덮는다 (cover 한다 ) 라고말한다. 두 FD 집합의동등성 FD 의집합 F 와 G 에대하여, F 의모든 FD 가 G 로부터추론될수있고, G 의모든 FD 가 F 로 부터추론될수있으면 FF 와 G 는동등하다 (equivalent) 라고한다 F 와 G 가다르더라도 F + = G + 이면 F 와 G 는동등하다. F 가 G 를 cover 하고, G 가 F 를 cover 하면 F 와 G 는동등하다. Page 25
함수적종속성의최소집합 (1/3) 함수적종속성들의집합 F 의최소 (Minimal) 다음세조건을만족하는 FD 집합을최소라고함 1. F 의모든함수적종속성들의오른쪽편애트리뷰트가하나이다. 2. F 로부터어떤함수적종속성을제거했을때, F 와동등한함수적종속성들의집합이될수 없다. 3. F 에서 X A 를 X 의진부분집합 Y 에대하여 Y A 로교체했을때, F 와동등한함수적종속 성들의집합이될수없다. 함수적종속성들의집합 F 의최소덮개 (minimal i cover) 는 F 와동등한 함수적종속성들의최소집합 Fmin 을의미함 함수적종속성들의최소덮개는여러개존재할수있다. 또한, 임의의함수적종속성들의집합 F 에대해, 알고리즘을사용하여적어도하나의최소 덮개를구할수있다 Page 26
함수적종속성의최소집합 (2/3) 조건 1 만족 조건 2 만족 조건 3 만족 Page 27
함수적종속성의최소집합 (3/3) 예 : 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} Page 28
강의내용 릴레이션스키마를설계하는몇가지개략적인지침 함수적종속성 (functional dependencies, FDs) 기본키를기반으로한정규형 제 2 정규형과제 3 정규형의일반적인정의 BCNF (Boyce Codd Normal lf Form) Page 29
기본키를기반으로한정규형 이절은다음의내용으로구성됨 정규화소개 제 1 정규형 (First Normal Form ; 1NF) 제 2 정규형 (Second Normal Form ; 2NF) 제 3 정규형 (Third Normal Form ; 3NF) Page 30
정규화소개 정규화 (normalization) 불만족스러운 나쁜 릴레이션의애트리뷰트들을나누어서더작은 좋은 릴레이션으로 분해하는과정 정규형 (normal form) 특정조건을만족하는릴레이션스키마의형태 제 1 정규형, 제2 정규형, 제3 정규형, BCNF 릴레이션스키마의 FD 와키에기반하여정의됨 일반적으로업계에서는제 3 정규형또는 BCNF 형까지고려 주요애트리뷰트 : 키 ( 기본키, 후보기모두포함 ) 에속하는애트리뷰트 비주요애트리뷰트 : 주요애트리뷰트가아닌애트리뷰트 Page 31
제1정규형 (1NF) 제 1 정규형 애트리뷰트의도메인이오직원자값만을포함하고, 투플의모든애트리뷰트가도메인에 속하는하나의값을가져야함 복합애트리뷰트 (composite attribute), 다치애트리뷰트 (multivalue attribute), 그리고중 첩릴레이션 (nested relation) 등비원자적 (non atomic) 애트리뷰트들을허용하지않은 릴레이션의형태 제 1 정규형은릴레이션내의릴레이션또는투플의애트리뷰트값들로서의 릴레이션을허용하지않음 Page 32
[ 그림 9.8] 다치애트리뷰트를 1NF로정규화 (a) 제 1 정규형이아닌릴레이션스키마 ( 부서는여러위치가있을수있다.) (b) 릴레이션인스턴스의예 ( 다치애트리뷰트를갖기때문에 1NF 가아니다.) (c) 중복이포함된제 1 정규형릴레이션 DEPARTMENT (a) DNAME DNUMBER DMGRSSN DLOCATIONS (b) DEPARTMENT DNAME DNUMBER DMGRSSN DLOCATIONS Research 4 {Bellaire, Sugarland, Houston} Administration 5 987654321 {Stafford} Headquarters 1 888665555 {Houston} DEPARTMENT (c) () DNAME DNUMBER DMGRSSN DLOCATIONS Research Research Research Administration Headquarters 4 4 4 5 1 987654321 888665555 Bellaire Sugarland Houston Stafford Houston Page 33
[ 그림 9.9] 중첩된릴레이션을 1NF로정규화 (a) 중첩릴레이션 PROJS 를포함하는릴레이션 EMP_PROJ 의스키마 (b) 각투플안에중첩릴레이션을포함하고있는릴레이션 MP_PROJ 의외연의예 (c) 기본키를복사함으로써 EMP_PROJ 를제 1 정규형릴레이션들로분해 (a) (b) EMP_PROJ PROJS SSN ENAME PNUMBERS HOURS EMP_PROJ SSN ENAME PROJS PNUMBERS HOURS (c) EMP_PROJ1 SSN ENAME EMP_PROJ2 SSN PNUMBER HOURS 123456789 666884444 453453453 999887777 987987987 987654321 Smith, John B. Narayan, Joyce K. English,,Joyce A. Wong, Franklin T. Zelaya, Alicia J. Jabbar, Ahmad V. Wallace, Jennifer S. 888665555 Bong, James E. 1 2 3 1 2 2 3 10 20 30 10 10 30 30 20 20 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.0 15.0 null Page 34
제2정규형 (2NF) 제 2 정규형은기본키와완전함수적종속성의개념에기반을둔다. 완전함수적종속성 (full functional ldependency): d FD Y Z 에서 Y 의어떤애트리뷰트라도제거하면더이상함수적종속성이성립하지 않는경우 예제 : {SSN, PNUMBER} HOURS 는 SSN HOURS 와 PNUMBER HOURS 가성립하지 않기때문에완전함수적종속성이다. {SSN, PNUMBER} ENAME 은 SSN ENAME 이성립하기때문에완전함수적종속성 이아니다 ( 이는부분함수종속성 (partial function dependency) 이라고부름 ). 제 2 정규형의정의 : 릴레이션스키마 R 의모든비주요애트리뷰트들이기본키에대해서완전함수적종속이면, R 은제 2 정규형 (2NF) 에속한다. Page 35
[ 그림 9.10(a)] EMP_PROJ를 2NF으로정규화 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 완전함수적종속성 완전함수적종속성 완전함수적종속성 Page 36
제3정규형 (3NF) 제 3 정규형은이행함수적종속성의개념에기반을둔다. 이행함수적종속성 (transitive functional ldependency): d 두 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 정규형을갖는다고한다. Page 37
[ 그림 9.10(b)] EMP_DEPT 를 3NF으로정규화 EMP_DEPT ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN 이행함수적종속성 3NF 정규화 ED1 ED2 ENAME SSN BDATE ADDRESS DNUMBER DNUMBER DNAME DMGRSSN Page 38
강의내용 릴레이션스키마를설계하는몇가지개략적인지침 함수적종속성 (functional dependencies, FDs) 기본키를기반으로한정규형 제 2 정규형과제 3 정규형의일반적인정의 BCNF (Boyce Codd Normal lf Form) Page 39
2NF와 3NF의일반적정의 ( 여기부터는 ) 여러후보키를가진릴레이션의정규화를고려한다. 제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) 의경우를허락치않는정규형을의미한다 Page 40
[ 그림 9.11] 2NF 와 3NF로정규화 (1/2) (a) LOTS 릴레이션스키마와함수적종속성 fd1 부터 fd4 (b) LOTS 를 2NF 릴레이션 LOTS1 과 LOTS2 로분해 (a) LOTS 후보키 PROPERTY_ID# COUNTY_NAME LOT# AREA PRICE TAX_RATE 완전함수적종속성 완전함수적종속성 fd1 fd2 부분함수적종속성 fd3 (b) fd4 LOTS1 PROPERTY_ID# COUNTY_NAMENAME LOT# AREA PRICE fd1 LOTS2 COUNTY_NAME fd3 fd2 TAX_RATE fd4 이행함수적종속성 Page 41
[ 그림 9.11] 2NF 와 3NF로정규화 (2/2) (c) LOTS1 을 3NF 릴레이션 LOTS1A 와 LOTS1B 로분해 (d) LOTS 의정규화요약 (c) LOTS1A LOTS1B PROPERTY_ID# COUNTY_NAMENAME LOT# AREA AREA PRICE fd1 fd4 fd2 (d) LOTS 1NF LOTS1 LOTS2 2NF LOTS1A LOTS1B LOTS 3NF Page 42
강의내용 릴레이션스키마를설계하는몇가지개략적인지침 함수적종속성 (functional dependencies, FDs) 기본키를기반으로한정규형 제 2 정규형과제 3 정규형의일반적인정의 BCNF (Boyce Codd Normal lf Form) Page 43
BCNF (Boyce-Codd Normal Form) 릴레이션스키마 R 에서성립하는임의의 FD X A 에서 X 가 R 의슈퍼키 이면 R 은 Boyce Codd 정규형 (BCNF) 을갖는다고한다. 각정규형은그의선행정규형보다더엄격한조건을갖는다. 즉, 모든제 2 정규형릴레이션은제 1 정규형을갖는다. 모든제 3 정규형릴레이션은제 2 정규형을갖는다. 모든 BCNF 릴레이션은제 3 정규형을갖는다. 제 3 정규형에는속하나 BCNF 에는속하지않는릴레이션이존재한다. 관계데이터베이스설계의목표는각릴레이션이 BCNF( 또는 3NF) 를 갖게하는것이다. Page 44
BCNF으로정규화 (a) BCNF로정규화하는과정에서종속성 fd2가없어지는경우 ( 정보의손실이발생하는경우임 ) (b) 3NF이나 BCNF가아닌릴레이션 R (a) LOTS1A 제 3 정규형 후보키 ( 슈퍼키 ) PROPERTY_ID# COUNTY_NAME LOT# AREA fd1 fd2 이행함수적종속성 fd5 BCNF 정규화 이행종속성의대상이슈퍼키이므로제 3 정규형을만족함 BCNF LOTS1AX PROPERTY_ID# AREA LOT# LOTS1AY AREA COUNTY_NAME (b) R A B C fd1 fd1 Page 45
요약 릴레이션스키마를설계하는몇가지개략적인지침 갱신이상, 널값발생, 가짜투플 함수적종속성 (functional dependencies, FDs) 정의, 추론규칙, 동등성 정규형 기본키를기반으로한정규형 제 2 정규형과제 3 정규형의일반적인정의 BCNF (Boyce Codd Normal Form) Page 46