Database Fundamentals Spring 2014 Homework Assignment Two Part I Convert an ERD to a relational schema Covert the Extended Entity Relationship Diagram (EERD) for Orthopedic Alliance of Plano (OAP) described in assignment one to relations. See Lecture 5, slide 22 for relational schema. Hint: Identify the foreign keys for referential integrity constraints. For example, use Nurse ID and Physician ID for the subtypes. “Interaction” relation will have a composite primary key with two attributes, both taken from the primary key of the “Drug” relation. “Offers” relation will also have a composite primary key. Part II Data Normalization Consider the following data structure GRADEREPORT (Student ID, Student Name, Major, Course ID, Course Title, Instructor ID, Instructor Name, Grade) Some clarifications: Student ID is a unique identifier for student and serves as the determinant for Student Name and Major. Course ID is a unique identifier for a specific section of a course and serves as the determinant for Course Title, Instructor ID and Instructor Name. Instructor ID is a unique identifier for instructor and serves as the determinant for Instructor Name. Answer the following questions: In what normal form is this data structure? Justify your answer. Draw the dependency diagram after identifying all dependencies in this data structure. See Lecture 6, Slide 27 for dependency diagram. Convert this data structure to a set of 3NF relations. Clearly showing each step Note: You may use any drawing tools. You can use short text statements or graphical representation for relations. Do not forget to show the referential integrity constraints using arrows when multiple relations involved. The submission should be a Word document. This assignment is due by 11:00 pm on April 02, 2014.
- 6 years ago
Purchase the answer to view it