OUESTION $5$

a$)$ Define the following terms:

$($i$)$ Normalization.

$($ii$)2NF$

$($iii$)$ Functional dependency

$($i$)$ Identify and discuss each of the indicated dependencies.

$($ii$)$ Create a database whose tables are at least in $2NF,$ showing the dependency diagrams for each table.

$($iii$)$Create a database whose tables are at least in $3NF,$ showing the dependency diagrams for each table.

c$)$ Explain why the relation EMPLOYEE below does not satisfy $3NF$ and decompose it into relations, which do satisfy $3NF.$ What advantages are gained by this decomposition?

$(6)$

EMPLOYEE

$\backslash$table$[[$EMP$\_$NO$,$NAME,ADDRESS,DEPARTMENTS,DEPARTMENT$\_$MANAGER$],[$E$1,$Smith,Chez Nous,D$1,$E$1],[$E$2,$Brown,Mon Repose,D$1,$E$1],[$E$3,$Smith,Mon Repose,D$1,$E$1],[$E$4,$Jones,Sea View,D$2,$E$9],[$E$9,$Bloggs,The Nook,D$2,$E$9]]$