CSE $4503/6503$

Total $52$ points

$1.$ Write the definitions of $1$NF$,2$NF$,3$NF and BCNF$.(4*2\mathrm{.}5=10$ points$)$

$2.$ Consider the universal relation R $=\{$A$,$ B$,$ C$,$ D$,$ E$,$ F$,$ G$,$ H$,$ I, J$\}$ and the set of functional dependencies

F $=\{\{$A$,$ B$\}->\{$C$\},\{$A$\}->\{$D$,$ E$\},\{$B$\}->\{$F$\},\{$F$\}->\{$G$,$ H$\},\{$D$\}->\{$I$,$ J$\}\}.$

What is the key for R$?$ Decompose R into $2$NF$,3$NF and into BCNF relations. $(15$ points$)$

$3.$ Repeat Question $2$ for the following different set of functional dependencies

G $=\{\{$A$,$ B$\}->\{$C$\},\{$B$,$ D$\}->\{$E$,$ F$\},\{$A$,$ D$\}->\{$G$,$ H$\},\{$A$\}->\{$I$\},\{$H$\}->\{$J$\}\}.(15$ points$)$

$4.$ Consider the following relation: $(10$ points$)$

Which of the following dependencies may hold in the above relation? Select all that apply.

i$.$ A $->$ B$,$

ii$.$ B $->$ C

iii. C $->$ B

iv$.$ B $->$ A

v$.$ C $->$ A

$5.$ Consider a relation R$($A$,$ B$,$ C$,$ D$,$ E$)$ with the following dependencies: $(15$ points$)$

AB $->$ C$,$ CD $->$ E$,$ DE $->$ B

Is AB a candidate key of this relation? If not, is ABD? Explain your answer.

$6.$ Consider the following relation: $(10$ points$)$

CAR$\_$SALE$($Car#$,$ Date$\_$sold, Salesperson#$,$ Commission$\%,$ Discount$\_$amt$)$

Assume that a car may be sold by multiple salespeople, and hence $\{$Car#$,$ Salesperson#$\}$ is the

primary key.

Additional dependencies are Date$\_$sold $->$ Discount$\_$amt and Salesperson# $->$ Commission$\%$

Based on the given primary key, is this relation in $1$NF$,2$NF$,$ or $3$NF$?$ Why or why not? How would

you successively normalize it completely into BCNF$?$