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:
1. Student ID is a unique identifier for student and serves as the determinant for Student Name and Major.
2. 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.
3. Instructor ID is a unique identifier for instructor and serves as the determinant for Instructor Name.
Answer the following questions:
a. In what normal form is this data structure? Justify your answer.
b. Draw the dependency diagram after identifying all dependencies in this data structure. See Lecture 6, Slide 27 for dependency diagram.
c. Convert this data structure to a set of 3NF relations. Clearly showing each step