Submission: Type all your SQL code for each step AND output, then save it in a $.$pdf file. Submit the PDF file to the dropbox on D$2$L$.$

All your work should be unique and done by yourself. DO NOT copy answers from other students or from the internet. plagiarism is NOT tolerated.

Normalization

The table below represents employees working on various contracts.

empID contractNo hours lastName

firstName

John Harry Maggie Linda John Linda Harry Harry

state branchNo

Illinois $1$ Illinois $1$ Illinois $2$ Maryland $3$ Maryland $3$ Maryland $3$ New York $4$ Maryland $3$

$1135$ C$102416$

$1057$ C$102416$

$1058$ C$000620$

$1068$ C$1025201135$ C$1025201068$ C$5321401234$ C$6651151234$ C$532140$

Smith Smith Simpson Peters Smith Peters Potter Potter

$1-$ What is the primary key?

$2-$ Please list all functional dependencies that violate $2$NF$?$

$3-$ Please list all functional dependencies that violate $3$NF$?$

$4-$ Fix any $2$NF violations and draw the new tables you have and their attributes. Fill in the rows. Remember to remove redundant rows. Specify the primary key by underlining the appropriate attribute$($s$)$ for each table.

$5-$ Using the latest updated tables, fix any $3$NF violations and draw the new tables you have and their attributes. Fill in the rows. Remember to remove redundant rows. Specify the primary key by underlining the appropriate attribute$($s$)$ for each table.