The Task: Normalization in computer science databases

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 $1NF,$ leaving the $2NF$ and $3NF$ 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.$

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

Functional Dependencies:

EmpID $$ EmpName, EmpAddr

ProjID $$ ProjName, MgrID, MgrName

EmpID, ProjID longrightarrow Hours Worked

MgrID longrightarrow MgrName

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

$($b$)$ Is this relation in $2NF?$ 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$.$

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

Functional Dependencies:

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

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

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

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

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

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

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

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

Functional Dependencies:

$AD,E$

$CG$

$A,CH,F$

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

$($b$)$ Is this relation in $2NF?$ 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)$ StockExchange$($Company$,$ Symbol, HQ$,$ Date, ClosePrice$)$

Functional Dependencies:

Symbol, Date longrightarrow Company, $H{Q}_2$ ClosePrice

Symbol $$ Company, HQ

Symbol $HQ$

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

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

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

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

Functional Dependencies:

id longrightarrow county, lotNum, lotArea, price, taxRate

lotArea longrightarrow price

county longrightarrow taxRate

id$,$ datePaid longrightarrow amount

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

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

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

WHAT TO TURN IN$?$

Answers to the questions should be submitted via Blackboard as a PDF with your name and section at the top of each page.