Exercises on Normalization

Given the relation and functional dependencies;

Example: STUDENT $($StudentNo$,$ Name, PrimaryMajor, School$)$

FDs: StudentNo $->$ Name, PrimaryMajor, PrimaryMajor $->$ School

$1.$ Candidate key is StudentNo, as if we know StudentNo we can find all other attributes from it

$2.$ The original relation is in $2$NF$.$ There are no partial FDs so it is in $2$NF at least. It is not in $3$NF as

there is a transitive FD StudentNo $->$ PrimaryMajor $->$ School

$3.3$NF relations:

STUDENT $($StudentNo$,$ Name, PrimaryMajor$)$

MAJOR $($PrimaryMajor$,$ School$)$

$2$

Given the following relation and functional dependencies:

a$)$ Identify the candidate key$($s$)$ and partial functional dependencies among attributes $($If any$)$

b$)$ Explain which NF the relation is in$.$

c$)$ Convert the relation into a relation or set of relations in at least $3$NF

Q$1.$ ENROLS $($StudentNo$,$ Name, UnitCode, UnitName, Grade$)$

FDs: StudentNo $->$ Name

UnitCode $->$ UnitName

StudentNo, UnitCode $->$ Grade

Q$2.$ Each loan application is by one customer but each customer may make many applications

ApplicNo Customer Address Date Appr

X$97$ JoeBlog Perth $2/3$

X$99$ Vicki Sydney $3/3$

Y$72$ JoeBlog Perth $3/3$

Converting the table to a relation

Q$3$: Given the following table:

Part No Description Vendor Address UnitCost

$1234$ Logic Chip Fast Chips Perth $10\mathrm{.}00$

Smart Chips Sydney $5\mathrm{.}00$

$5678$ Memory chip Fast Chips Perth $3\mathrm{.}00$

Quality Chips Sydney $2\mathrm{.}00$

Smart Chips Sydney $5\mathrm{.}00$

$3$

a$)$ Convert this table to a single relation $($called PART$-$SUPPLIER$)$ in first normal form. Illustrate the

relation with the same sample data.

b$)$ List the functional dependencies in PART$-$SUPPLIER and identify the candidate key$($s$).$

c$)$ Convert PART$-$SUPPLIER to a set of relations in at least third normal form.