[Solved] CSCI466-Assignment 2 Normalization

We discussed in class that a relational database designed in a poor way will allow for anomalies to occur. This is undesirable, so we use normalization to prevent them. Several relations are provided below, along with their functional dependencies. Answer the questions provided and fix what is broken. Perform only the current step for each question, i.e. when fixing 1NF, fix only 1NF, leaving the 2NF and 3NF violations alone until the question that asks about them.

TheQuestions

For each of the below, part (b) refers to the results of part (a), and part (c) refers to the results of part (b) any changes made during the previous steps should be considered in the steps that follow. Each question is

worth 12 points.

1. Pharmacy(patient_id, patient_name, address, (Rx_num, trademark_name, generic_name, (filldate, num_refills_left), num_refills))

FunctionalDependencies:

• patient_id patient_name, address
• patient_id, Rx_num trademark_name, generic_name
• Rx_num num_refills
• Rx_num, filldate num_refills_left

1. Is this relation in 1NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
2. Is this relation in 2NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
3. Is this relation in 3NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.

2. Company(EmpID, EmpName, EmpAddr, (ProjID, ProjName, MgrID, MgrName, HoursWorked)) FunctionalDependencies:
   • EmpID EmpName, EmpAddr
   • ProjID ProjName, MgrID, MgrName
   • EmpID, ProjID HoursWorked
   • MgrID MgrName

1. Is this relation in 1NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
2. Is this relation in 2NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
3. Is this relation in 3NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.

3. Property(id, county, lotNum, lotArea, price, taxRate, (datePaid, amount)) FunctionalDependencies:
   • id county, lotNum, lotArea, price, taxRate
   • lotArea price
   • county taxRate
   • id, datePaid amount

1. Is this relation in 1NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
2. Is this relation in 2NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
3. Is this relation in 3NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.

4. StockExchange(Company, Symbol, HQ, Date, ClosePrice) FunctionalDependencies:
   • Symbol, Date Company, HQ, ClosePrice
   • Symbol Company, HQ
   • Symbol HQ

1. Is this relation in 1NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
2. Is this relation in 2NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.
3. Is this relation in 3NF? If not, write an explanation of why it isnt, then make the necessary changes to fix it.