EIE$3343$

Question $1$: Functional Dependency and Normalization $(30$ marks$)$

The FD diagram below depicts relationships among attributes in an order entry database. It shows FDs with determinants CustNo, OrderNo, ItemNo, the combination of OrderNo and ItemNo, the combination of ItemNo and PlantNo, and the combination of OrderNo and LineNo. In the bortom FDs$,$ the combination of LineNo and OrderNo determines ItemNo and the combination of Onden No stad ltemiVo determines LineNo.

$($a$)$ Convert the dependency diagram into a list of dependencies organized by LHSs$.(6$ marks$)$

$($b$)$ Using the FD diagram and the FD list $($the solution of $($a$))$ as guidelines, make a table with sample data. There are two candidate keys for the underlying table: the combination of OrderNo, ItemNo, and PlantNo and the combination of OrderNo, LineNo, and PlantNo. Using sample data, identify insertion, update, and deletion anomalies in the table. $(6$ marks$)$

$($c$)$ Derive $2$NF tables starting with the FD list from the answer of $($a$)$ and $($b$).(4$ marks$)$

$($d$)$ Derive $3$NF tables starting with the FD list from the answer of $($a$)$ and the $2$NF tables from $($c$).(4$ marks$)$

$($e$)$ Find out the possible BCNF tables with the information in $($a$)$ to $($d$).(4$ marks$)$

$($f$)$ Modify your table design if the shipping address $($ShipAddr$)$ attribute determines customer number $($CustNo$).$ Do you think that this additional FD is reasonable? Briefly explain. $(6$ marks$)$

Question $1$: Functional Dependency and Normalization $(30$ marks$)$

The FD diagram below depicts relationships among attributes in an order entry database. It shows FDs with determinants CustNo, OrderNo, ItemNo, the combination of OrderNo and ItemNo, the combination of ItemNo and PlantNo, and the combination of OrderNo and LineNo. In the bottom FDs$,$ the combination of LineNo and OrderNo determines ItemNo and the combination of OrderNo and ItemNo determines LineNo.

$($a$)$ Convert the dependency diagram into a list of dependencies organized by LHSs$.(6$ marks$)$

$($b$)$ Using the FD diagram and the FD list $($the solution of $($a$))$ as guidelines, make a table with sample data. There are two candidate keys for the underlying table: the combination of OrderNo, ItemNo, and PlantNo and the combination of OrderNo, LineNo, and PlantNo. Using sample data, identify insertion, update, and deletion anomalies in the table. $(6$ marks$)$

$($c$)$ Derive $2$NF tables starting with the FD list from the answer of $($a$)$ and $($b$).(4$ marks$)$

$($d$)$ Derive $3$NF tables starting with the FD list from the answer of $($a$)$ and the $2$NF tables from $($c$).(4$ marks$)$

$($e$)$ Find out the possible BCNF tables with the information in $($a$)$ to $($d$).(4$ marks$)$

$($f$)$ Modify your table design if the shipping address $($ShipAddr$)$ attribute determines customer number $($CustNo$).$ Do you think that this additional FD is reasonable? Briefly explain. $(6$ marks$)$