Tasks to do

$1.$ Using the INVOICE table structure shown in Table $1($note that in this table rows are attributes and columns

are data records$),$ draw its dependency diagram and identifyall dependencies $($including all partial and

transitive dependencies$).$ You can see that an invoice has more than one product and a product is in more

than one invoice. From the business rules, we know that a product is supplied by one vendor only, but a

vendor can supply many products.

Note that you first need to determine the Primary Key to identify partial and transitive dependencies. What

do you think can be the Primary Key in this table? Note that it is a minimal candidate key that can uniquely

identify every records.

$2.$ Using the initial dependency diagram drawn for Question $1,$ remove all partial dependencies and

draw dependency diagrams of new table structures. Note that your actions may result in multiple tables.

$3.$ Using the tables structures you created for Question $2,$ remove transitive dependencies in all tables

Page $2$ of $2$

and draw dependency diagrams of all tables.

$4.$ Your might have multiple tables by now. For each table, please check if they satisfy conditions for $1$NF$,$

$2$NF$,$ and $3$NF and justify why they do or do not satisfy.

$5.$ Using the table structures from Question $3,$ draw a complete Entity Relationship Diagram $($ERD$)$ of the

database using Crow Foot notations in your choice of professional tool $($Visio$,$ Lucidchat or draw.io$).$

NOTE: Because the dependency diagrams cannot show the nature $(1$:$1,1$:M$,$ M:N$)$ of the relationships, the

ERD remains crucial to the design effort. Complex design is impossible to produce successfully without

some form of modelling such as ERD. Yet, as the preceding decompositions demonstrate, the dependency

diagrams are a valuable addition to the designer's toolbox. Normalization is likely to add tables$/$entities that

may not have been considered during the initial $($conceptual$)$ modelling process. Therefore, initial ERD may

change after normalizationTable $1.$ Sample INVOICE Records

$\backslash$table$[[$Attribute Name,Sample Value,Sample Value,Sample Value,Sample Value,Sample Value$],[$INV$\_$NUM,$211347,211347,211347,211348,211349],[$PROD$\_$NUM,AA$-$E$3422$QW$,$QD$-300932$X$,$RU$-995748$G$,$AA$-$E$3422$QW$,$GH$-778345$P$],[$SALE$\_$DATE,$15-$Jan$-2004,15-$Jan$-2004,15-$Jan$-2004,15-$Jan$-2004,16-$Jan$-2004],[$PROD$\_$DESCRIPTION,Rotary sander,$0\mathrm{.}25-$in$.$ drill bit,Band saw,Rotary sander,Power drill$],[$VEND$\_$CODE,$211,211,309,211,157],[$VEND$\_$NAME,NeverFail, Inc.,NeverFail, Inc.,BeGood, Inc.,NeverFail, Inc.,ToughGo, Inc.$],[$QUANT$\_$SOLD,$1,8,1,2,1],[$PROD$\_$PRICE,$$49\mathrm{.}95,$$$3\mathrm{.}45,$$$39\mathrm{.}99,$$$49\mathrm{.}95,$$$87\mathrm{.}75]]$