e-r图范式题.docVIP

大学的注册办公室维护关于以下实体的数据：（a）课程，包括编号、名称、学分、课程提纲和选修条件；（b）课程提供，包括课程编号、年、学期、节数、教师（可能多个）、时间和教室；（c）学生，包括学生标识、名字和计划；（d）教师，包括标识号、名字、系和职称。此外，学生课程的登记和学生所选的每门课的成绩评定都要适当地建模。 请画出该系统的E-R图，将E-R图转化为关系模式并进行必要的模式合并 个 （courseofferings实体集，应用双矩形表示） Student(sid, name, program) Course(courseno, title, credits, syllabus) Instructor(iid, name, dept, title) Courseofferings(courseno, year, semester, secno, room, time) Enrols(sid, courseno, year, semester, secno, grade) Teaches(iid, courseno, year, semester, secno) Requires(maincourse_courseno, prerequisite_courseno) Compute the closure of the following set F of functional dependencies for relation schema R = (A, B, C, D, E). A→BC CD→E B→D E→A List the candidate keys for R. Starting with A → BC, we can conclude: A → B and A → C. Since A → B and B → D, A → D (decomposition, transitive) Since A → CD and CD → E, A → E (union, decomposition, transitive) Since A → A, we have (reflexive) A → ABCDE from the above steps (union) Since E → A, E → ABCDE (transitive) Since CD → E, CD → ABCDE (transitive) Since B → D and BC → CD, BC → ABCDE (augmentative, transitive). Therefore, any functional dependency with A, E, BC, or CD on the left hand side of the arrow is in F+, no matter which other attributes appear in the FD. Allow * to represent any set of attributes in R, then F+ is BD → B, BD → D, C → C, D → D, BD → BD, B → D, B → B, B → BD, and all FDs of the form A? → α, BC? → α, CD? → α, E? → α where α is any subset of {A, B, C, D, E}. The candidate keys are A, BC, CD, and E. a B+ = { BDACE} b A-BCD, A-ABCD,BC-DE, ABCD-ABCDE A-ABCDE AF-ABCDEF 所以 c A-BCD中D无关属性 BC-DE中C无关 D无关 A-BC B-D B-E D-A A-BC B-DE D-A D (ABC) (BDE)(DA)(AF) E (ABCD)(AF)(AE) 例：r(R)=r(A, B, C, D, G, H)，F={AB→GH, CD→GH, D→B}，r(R)是否属于BCNF范式？如果不是，则进行BCNF分解。 r(R)(BCNF，因为AB→GH的决定属性AB不是超码。r(R)可分解为： r1(R1)=r1(A, B, G, H)， F1={AB→GH} —— AB是候选码 r2(R2)=r2(A, B, C, D)， F2={D→B} —— ACD是候选码 —— 丢失函数依赖CD→GH！ r2(R2)(BCNF（D→B的决定属性D不是超码）。r