2. Identify the functional dependencies represented by the attributes shown in the above table. State any assumptions you make about the data and the attributes. (

Note: Assume that the primary key is identified as (Patient_ID, Lab_Test_ID, Lab_Test_Date) which fully functionally determine the Lab_Result. (Note that we need the values of these three fields to uniquely identify a particular Lab_Test_Date because a patient could have the same lab multiple times on different days. ) However, not all the other fields need three fields to be decided.)

Patient _ID

Name

Insuranc_Code

Insurance_Name

DOB

Lab_Test_ID

Lab_Test_Name

Lab_Result

Lab_Test_Date

3. Describe and illustrate the process of normalizing the table shown above to 3NF relations (tables). Identify the primary and foreign keys in your resulting relations after 3NF.

1NF: (Find any repeating group and process it if there is any.)

Relations after 1NF

2NF: (Find any partial dependency and process it if there is any.)

Relations after 2NF

3NF: (Find any transitive dependency and process it if there is any. Sure, you may not find some!)

Relations after 3NF (Show primary key and foreign key in each relation.)

2. Sketch an EntityRelationship model with attributes for the resulting relations in 3. Show all the entities, relationships, attributes, and multiplicity among entities. (Three entities should be enough.)