엔터프라이즈솔루션데이터베이스 2006. 0. 00 2007년상반기 홍길동정보처리기술사 webmaster@inforever.com -0-
논리적모델검증필요성 함수적종속 (Function Dependence) 어떤릴레이션 R 에서속성 x 의값각각에대해다른속성 y 의값이오직 하나만연관되어있을때 Y 는 X 에함수적으로종속된다 라고하며 x -> y 로표현함. 함수적종속성추론규칙 1) 재귀규칙 (Reflexive Rule) - x 가 Y 에포함되어지면 x -> y 가성립한다. 2) 증가규칙 (Augmentation Rule) -x -> y 가성립하고 w 가에트리뷰트 Set 이면 wx-> wy 가성립한다 3) 이행규칙 (Transitive Rule) - x -> y 가성립하고 y -> z 가성립하면 x -> z 가성립한다. -1-
논리적모델검증필요성 ( 계속 ) 이상현상 (Anomaly) - 데이터베이스설계과정에서속성들간의관계성인함수종속관계에대하여무시할경우데이터의중복저장으로인해데이터의일관성이깨지고여러가지관리상에치명적인문제가발생하는현상 - 갱신이상 (Update Anomaly), 삽입이상 (Insert Anomaly), 삭제이상 (Delete Anomaly) 이상현상해결을위한방법 - 정규화 (Normalization) : 관계형데이터모델링작업의검증수단제공 정규화단계제 1 정규화 (1NF) 제 2 정규화 (2NF) 제 3 정규화 (3NF) Boyce/Codd 정규화제 4 정규화 (4NF) 제 5 정규화 (5NF) 주요내용테이블에서반복되는컬럼들제거 1NF 된테이블의복합키관련부분적함수종속성제거 2NF 된기본키아닌다른속성에의한이행적함수종속성제거 3NF 된테이블의결정자 (Determinant) 제거다치 (Multiple Valued Dependency) 함수종속성제거죠인 (Join) 종속성제거 -2-
Case Study #1 1NF 반복적속성엔터티로분리 학번 성명 학과 교과번호 과목명 교수명 교수연구실 학점 김현숙 교육 UA0001 유아발달 김애경 A001 A+ UA0002 유아심리 김숙희 A002 B+ 소프트웨어공학 강감찬 A004 C+ 200 김철수 영어 ES0001 미국영작소설 이순신 E001 C 300 양도화 컴퓨터 SC0001 수치해석 이퇴계 B003 A- 소프트웨어공학 강감찬 B008 A+ [ 학생 ] 반복영역 << Anomaly Check >> 학번 인학생의지도교수가이퇴계라는사실삽입할경우? 학번 200인학생의튜플이하나만있는상황에서교과목 ES0001 등록취소경우? 학번 300인학생의소프트웨어공학지도교수가강감찬에서이미남으로변경할경우? -3-
Case Study #1 1NF 반복적속성엔터티로분리 [FDD] 성명 학번 M 교과번호 학과 학점 [ 학생 ] [ 수강 ] <- 정규화대상 학번 성명 학과 학번 교과번호 과목명 교수명 교수연구실 학점 김현숙 교육 UA0001 유아발달 김애경 A001 A+ 200 300 김철수양도화 영어컴퓨터 200 UA0002 ES0001 유아심리소프트웨어공학미국영작소설 김숙희강감찬이순신 A002 A004 E001 B+ C+ C 300 SC0001 수치해석 이퇴계 B003 A- 300 소프트웨어공학 강감찬 B008 A+ -4-
Case Study #1 2NF 복합키속성중일부에종속되는속성제거 [ 수강 ] <- 정규화대상 학번 교과번호 과목명 교수명 교수연구실 학점 UA0001 유아발달 김애경 A001 A+ UA0002 유아심리 김숙희 A002 B+ 소프트웨어공학 강감찬 A004 C+ 200 ES0001 미국영작소설 이순신 E001 C 300 SC0001 수치해석 이퇴계 B003 A- 300 소프트웨어공학 강감찬 B008 A+ << Anomaly Check >> 어떤교수가특정과목을지도한다는정보를입력하고자할경우? 특정교과목 ( 교과목번호 ) 이삭제될경우? 특정교수가학과가바뀔경우? -5-
Case Study #1 2NF 복합키속성중일부에종속되는속성제거 [FDD] 과목명학번교수명교과번호 학점 교수연구실 [ 성적 ] [ 개설교과목 ] <- 정규화대상 학번 교과번호 UA0001 UA0002 학점 A+ B+ C+ 교과번호 UA0001 UA0002 PK 과목명유아발달유아심리소프트웨어공학 교수명김애경김숙희강감찬 교수연구실 A001 A002 A004 200 ES0001 C ES0001 미국영작소설 이순신 E001 300 300 SC0001 A- A+ SC0001 수치해석소프트웨어공학 이퇴계강감찬 B003 B008-6-
Case Study #1 3NF 기본키가아닌일반속성에의존적인관계 ( 이전종속 ) 제거 [ 개설교과목 ] <- 정규화대상 교과번호 과목명 교수명 교수연구실 UA0001 UA0002 PK 유아발달유아심리 김애경김숙희 A001 A002 소프트웨어공학 강감찬 A004 ES0001 미국영작소설 이순신 E001 SC0001 수치해석 이퇴계 B003 소프트웨어공학 강감찬 B008 << Anomaly Check >> 신규임용된교수가다른교수와중복되는교과목을강의할경우? 한교수가여러과목을강의할경우? -7-
Case Study #1 3NF 기본키가아닌일반속성에의존적인관계 ( 이전종속 ) 제거 [FDD] 교과번호 과목명 교수명 교수연구실 [ 개설교과목 ] [ 교수 ] PK PK 교과번호 UA0001 UA0002 ES0001 SC0001 SC0001 과목명 유아발달유아심리미국영작소설수치해석소프트웨어공학미국영작소설 교수명 김애경김숙희이순신이퇴계강감찬강감찬 교수명 김애경김숙희이순신이퇴계강감찬 교수연구실 A001 A002 E001 B003 B008-8-
Case Study #1 성적표에대한최종논리설계결과 [ 교수 ] [ 개설교과목 ] 교수명 (PK) 강의 교과번호 (PK) 교수연구실 1:M 1:M 과목명교수명 (FK) [ 학생 ] 학번 (PK) 성명학과 1:M 시험 평가 학번 (PK) 교과번호 (PK) 학점 [ 성적 ] 주 ) 정규화과정에서파생되었던 [ 수강 ] 테이블은 ->[ 수업과정 ] 테이블 -> [ 교수 ] 및 [ 교과목 ] 테이블로분리되어최종 3NF 설계되었음 정규화는궁극적으로엔터티간관계를 1 : M으로.. 논리모델은사용자와함께실제값을가지고자료의의존성과관계를 검토해야정확한 모델링을기대할수있음 -9-
Case Study #1 BCNF 3NF 된테이블의결정자 (Determinant) 제거 << 업무규칙 >> 학생은여러과목을전공할수있다. 학생은전공별로오직한사람지도교수를갖음 한전공분야에는여러명의지도교수배정, 각지도교수는오직한전공만지도함 BCNF 조건 : 후보키가여러개인경우, 복합속성, 후보키들이서로 [ 학생-전공-지도교수 ] 겹치는경우 ( 적어도하나의속성이공동인경우 ) 학번 200 300 400 << Anomaly Check >> 전공물리학음악생물학물리학 지도교수김애경김숙희강감찬김애경 학번 200 의전공음악을수학으로바꾸면전공 음악 정보손실 김현숙교수가전산학을지도한다는정보를삽입시키고자할경우 -> 먼저학생배정필요 -10-
Case Study #1 BCNF 3NF 된테이블의결정자 (Determinant) 제거 [FDD] 학번 지도교수 전공 [ 학생 - 지도교수 ] [ 전공 - 지도교수 ] 학번 지도교수 지도교수 전공 김애경 김애경 물리학 200 김숙희 김숙희 음악 300 강감찬 강감찬 생물학 400 김애경 지도교수 : 결정자 (Determinant) -11-
Case Study #2 관계형데이터베이스설계시에부분종속성으로인하여발생되는이상현상 (anomaly) - 입력이상, 삭제이상, 갱신이상 - 을, 아래에주어진테이블과함수종속성 (Functional Dependency) 을근거로하여 " 예 " 를들어서설명하고, 부분종속성을제거함으로써이상현상이해소되는과정을설명하시오 -12-
Case Study #2 반복그룹존재여부확인 - 1 정규화 테이블에서속성간의관계를보면 [ 학번 ] 과 [ 지도교수명 ], [ 교수전공 ] 간에는 1:1 관계가있으며 [ 학번 ] 과나머지속성간에는 1:N 관계가있으므로학번은후보키가아니며모든속성들을유일하게결정하지못함 1:1 관계 학번지도교수명교수전공과목번호성적 실데이터내용을보면 1 정규화된수강테이블임, 데이터중복존재 ( 지도교수명, 교수전공 ) 하므로테이블분리필요 1: N 관계 참고 : 문제에서는이미제 1 정규화된내용이므로위의내용은해당없음 -13-
Case Study #2 복합키속성의일부에종속속성제거 - 2 정규화 FD : 학번 과목번호 -> 성적 지도교수명 교수전공 학번 과목번호 성적 - 부분함수적종속성제거하기위해 [ 성적 ], [ 교수배정 ] 테이블분리 [ 교수배정 ] [ 성적 ] (2NF) 학번 (PK) 지도교수명교수전공 지도한다 학번 (PK) 과목번호 (PK) 성적 (3NF) - [ 교수배정 ] 테이블의경우기본키아닌지도교수명속성에도교수전공속성이종속됨 -14-
Case Study #2 기본키아닌일반속성에종속되는속성 ( 이행적종속 ) 제거 3 정규화 FD : 학번 -> 지도교수, 지도교수 -> 교수전공 학번지도교수명교수전공 - [ 교수배정 ] 테이블에서지도교수관련내용분리 데이터무손실원칙주의해야함 [ 교수배정 ] [ 교수 ] (3NF) 학번 (PK) 지도교수명 (PK) 배정한다 지도교수명 (PK) 교수전공 (3NF) -15-
Case Study #2 3 정규화까지반영된전체 ERD [ 교수 ] 지도교수명 (PK) 교수전공 배정한다 [ 교수배정 ] [ 학생 ] 학번 [ 성적 ] [ 강좌 ] 과목번호 학번 (PK) 지도교수명교수전공 지도한다 학번 (PK) 과목번호 (PK) 성적 점선으로표시된 [ 학생 ], [ 강좌 ] 엔터티는문제에서요구된사항은아니며, 엔터티간의관계는보는관점마다다르게표현될수있다. -16-
Case Study #3 하나의제품에대해여러개의주문서가접수된내용을보여주는 주문목록 테이블에 대하여정규화를하라 -17-
Case Study #3 반복그룹존재여부확인 - 1 정규화 FD : 제품번호 -> 제품명, 재고수량, FD : 제품번호 주문번호 -> 고객번호, 사업자번호, 수출여부, 우선순위, 주문수량 제품명 1: M 관계 제품번호 1:1 관계 사업자번호수출여부 재고수량 주문번호 주문목록 고객번호우선순위주문수량 [ 제품현황 ] 제품번호 (PK) 제품현황제공제품번호 (PK) 주문번호 (PK) (3NF) 제품명고객번호 (2NF 대상 ) 재고수량사업자번호수출여부우선순위 [ 주문목록 ] 주문수량 -18-
Case Study #3 복합키속성의일부에종속속성제거 - 2 정규화 FD : 제품번호 주문번호 -> 주문수량 FD : 주문번호 -> 고객번호, 사업자번호, 수출여부, 우선순위 주문수량 1: 1 관계 제품번호 고객번호수출여부 주문번호 사업자번호 주문목록 우선순위 [ 주문목록 ] (3NF) 주문번호 (PK) 제품번호 (PK) 고객별제품주문고객번호주문번호 (PK) 사업자번호주문수량수출여부우선순위 [ 주문고객 ] (3NF 대상 ) -19-
Case Study #3 기본키아닌일반속성에종속되는속성 ( 이행적종속 ) 제거 - 3 정규화 FD : 주문번호 -> 고객번호, 사업자번호, 수출여부, 우선순위 FD : 고객번호 -> 사업자번호, 수출여부, 우선순위 주문번호 고객번호 수출여부 사업자번호 우선순위 [ 주문목록 ] [ 주문고객 ] (3NF) 제품번호 (PK) 주문번호 (PK) 주문수량고객번호 (FK) 고객별제품주문 고객번호 (PK) 사업자번호수출여부우선순위 (3NF) -20-
Case Study #3 3 정규화까지반영된전체 ERD (3NF) [ 제품현황 ] 제품번호 (PK) 제품명재고수량 제품현황제공 (3NF) 제품번호 (PK) 주문번호 (PK) 주문수량고객번호 (FK) 고객별제품주문 고객번호 (PK) 사업자번호수출여부우선순위 (3NF) [ 주문목록 ] [ 주문고객 ] -21-