The Task

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 First Normal Form $(1$NF$),$ fix only $1$NF$,$ leaving the $2$NF and $3$NF violations untouched

until the question that asks about them.

The Questions

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 of these is worth $10$ points for a total of $50.$

$1$ R$($A$,$ B$,$ C$,$ D$,$ E$,$ F$,$ G$,$ H$)$

Functional Dependencies:

$$ A $$ D$,$ E

$$ C $$ G

$$ A$,$ C $$ H$,$ F

a Is this relation in $1$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

b Is this relation in $2$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

c Is this relation in $3$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

$2$ Property$($id$,$ county, lotNum, lotArea, price, taxRate, $($datePaid$,$ amount$))$

Functional Dependencies:

$$ id $$ county, lotNum, lotArea, price, taxRate

$$ lotArea $$ price

$$ county $$ taxRate

$$ id$,$ datePaid $$ amount

a Is this relation in $1$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

b Is this relation in $2$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

c Is this relation in $3$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

$3$ Pharmacy$($patient$\_$id$,$ patient$\_$name, address, $($Rx$\_$num, trademark$\_$name, generic$\_$name, $($filldate$,$ num$\_$refills$\_$left$),$ num$\_$refills$))$

Functional Dependencies:

$$ patient$\_$id $$ patient$\_$name, address

$$ patient$\_$id$,$ Rx$\_$num $$ trademark$\_$name, generic$\_$name

$$ Rx$\_$num $$ num$\_$refills

$$ Rx$\_$num, filldate $$ num$\_$refills$\_$left

a Is this relation in $1$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

b Is this relation in $2$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

c Is this relation in $3$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

$4$ Company$($EmpID$,$ EmpName, EmpAddr, $($ProjID$,$ ProjName, MgrID, MgrName, HoursWorked$))$

Functional Dependencies:

$$ EmpID $$ EmpName, EmpAddr

$$ ProjID $$ ProjName, MgrID, MgrName

$$ EmpID, ProjID $$ HoursWorked

$$ MgrID $$ MgrName

a Is this relation in $1$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

b Is this relation in $2$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

c Is this relation in $3$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

$5$ StockExchange$($Company$,$ Symbol, HQ$,$ Date, ClosePrice$)$

Functional Dependencies:

$$ Symbol, Date $$ Company, HQ$,$ ClosePrice

$$ Symbol $$ Company, HQ

CSCI $466$ Assignment $3($Fall $2024)2$ of $2$

$$ Symbol $$ HQ

a Is this relation in $1$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

b Is this relation in $2$NF$?$ If not, explain why not, then make the necessary changes to fix it$.$

c Is this relation in $3$NF$?$ If not, explain why not, then make the necessary changes to fix it

draw it i need diagram not the steps i need the diagram.