23F-A5 (1)

COMP 3005B Assignment #5 Due: Nov 26 Instruction 1. You should do the assignment independently. If copying is found, the case will be reported to the office of the Dean of Science immediately. 2. Do this assignment directly on this document, rename it with your last name+first name, and submit it to brightspace . Make sure your uploaded file can be opened and is correct. Part 1 ER (10 marks) Map the generated ER diagram in A4 Part 3 into a relational database schema. (10) Part 2 EER (40 marks) Extend the ER Diagram created in A4 Part 3 with the following additional information. a) A person is either a student or an employee. b) A student is either an undergraduate student or a graduate student. A graduate student is either a Master’s student or Ph.D student. c) An employee is either a chair, instructor d) An instructor is either a professor or a lecturer. e) A course is either an undergraduate course or a graduate course f) An undergraduate course section is taught by a lecturer and is taken only by undergraduate students g) A graduate course section is taught only by a professor and is taken only by graduate students. h) A professor also supervises up to 3 Ph.D students and up to 6 Master’s students. 2. Draw the EER diagram for this information system that can represent the constraints specified above. (30) 3. Map the EER diagram into a relational database schema. (10) Part 3. FD (10 marks) 1. Consider the following relation. Which of the following functional dependencies may hold in this relation? If the dependency cannot hold, explain why by specifying the tuples that cause the violation. (5) （ 1 ） A → B （ 2 ） B → C （ 3 ） C → B （ 4 ） B → A A B C a1 b1 c1 a1 b2 c2 a2 b3 c3 a3 b3 c4 a1 b3 c1

G H F （ 5 ） C → A 2. Disprove the following inference rules. a. A → B, A → C ⊨ B → C (2) b. AB → C, B → D ⊨ C → D (3) Part 4. FD/MVD (10 marks) Given the following inference rules: (R0) X → XX (R1) if X Ê Y, then X → Y (R2) X → Y ⊨ XZ → YZ (R3) X → Y, Y → Z ⊨ X → Z Prove or disprove the following inference rules for functional/multi-valued dependencies. 1. A → B, A → C ⊨ C→ B (2) 2. A → C, AC → B ⊨ A → B (2) 3. A → B and C ⊆ B and D ⊆ C ⊨ A → D (2) 4. A ↠ B ⊨ A → B (2) 5. A → B, C → D ⊨ AC → BD (2) Part 5 ( 30 marks) Given a first normal form relation FIRST with attributes {A, B, C, D, E, F, G, H} with the dependency diagram as follows: Dependency Diagram A B E D B C FIRST A B C D E F G H