Part $1$: Normalization

$1.$ Consider a table Alphabet $($A$,$ B$,$ C$,$ D$,$ E$)$ with the following functional dependencies:

$$

FD$1$: AB$$C

FD$2$: AC$$B

FD$3$: BC$$A

FD$4$: D$$E

$$

a$.$ Determine all the candidate keys for the table. Write none if you think there are no CK or PK$.$ Explain your answer $($or show your work$).(1\mathrm{.}5$ points$)$

$$

$$

$$

b$.$ Determine the current normal form of the table. Justify your answer. $(1\mathrm{.}5$ points$)$

$$

$$

c$.$ Normalize the table to at least $3$NF$.$ Identify the primary key and foreign key$($s$)$ for the new normalized tables. If a table does not have a foreign key, write none. Explain your answer $(3$ points$)$

$$

$2.$ Consider the relation schema:

R $=($A$,$ B$,$ C$,$ D$,$ E$,$ F$)$

and the set of functional dependencies FDs:

A $->$ B$,$

B $->$ A$,$

BC $->$ E$,$

BC $->$ D$,$

E $->$ F$,$

BC $->$ F

$$

a$.$ List the candidate key $($CK$)$ and primary key $($PK$)$ for R$.$ Write none if you think there are no CK or PK$.$ Explain your answer $($or show your work$).(4$ points$)$

$$

b$.$ Determine the normal form for R$.$ Explain your answer. $(2$ points$)$

$$

c$.$ Decompose R into tables that are at least in $3$NF$.$ Explain your answer. List all the PKs and foreign keys for the newly created tables $(5$ points$)$

$3.$ Given the Students table below, answer the following questions:

Note: FNAME $=$ faculty name; FOFFICE $=$ Faculty Office

a$.$ Write the functional dependencies and identify all dependencies in the table. You should clearly state the primary key, type of dependencies and any other assumptions that you may have. $(5$ points$)$

b$.$ Write the relational schema to meet the $3$NF requirements or to the greatest extent possible. Identify the normal forms for each new table structure you created. You should clearly state the primary key, foreign key $($if exist$),$ type of dependencies $($if exist$)$ and any other assumptions that you may have for the newly normalized tables. $(10$ points$)$