database

Practice makes perfect! In this assignment you will practice normalizing a table. You can choose to either start on this assignment and then work on the discussion , or work on the discussion first and then this activity. It is recommended that you finish both this assignment and discussion before moving to the M4A2 assignment, Normalize Your Database.

Given the relation below (note that there is a composite primary key in this relation):

(PrescriptionNumber, PatientID, Date, Drug, Dosage, PrescriberID, PrescriberName, PatientName)

Answer the following questions:

1. List the functional dependencies for the relation (refer to page 112 of Harrington (2009) for the notation for functional dependencies).

2. Depending on your assumption for the relation (for example, assume that the value of PrescriberID can uniquely determine the value of PrescriberName), your answer might be different from your classmates’ . Then, write down the assumptions you have for the functional dependencies you identify.

3. Is this relation in at least 3rd normal form? Explain your reasoning.

4. If your answer to the 2nd question is ‘No,’ normalize this relation to 3rd normal form (or above). Write down the final relation(s) after normalization. Keep in mind that during normalization, new foreign keys may be introduced to the relation(s).

5. Specify the relationships between the normalized tables and highlight the foreign keys.

6. List the functional dependencies for the normalized relation(s).