Using the INVOICE table structure shown in Table $1,$ do the following:

Table $1$ :

$\backslash$table$[[\backslash$table$[[$Attribute$],[$Name$]],$Sample Value,Sample Value,Sample Value,Sample Value,Sample Value$],[$INV$\_$NUM,$1001,1002,1003,1004,1005],[$PROD$\_$NUM,$\backslash$table$[[123-21$UUY, SRE$-],[657$UG$]],$QER$-34256,\backslash$table$[[$SRE$-657$UG$,],[$ZZX$/3245$Q$]],\backslash$table$[[$SRE$-657$UG$,],[\text{'}001278-$AB$\text{'}]],$ZZX$/3245$Q$],[$SALE$\_$DATE,$\text{'}2022-3-8\text{'},\text{'}2022-3-8\text{'},\text{'}2022-3-8\text{'},\text{'}2022-3-9\text{'},\text{'}2022-1-16\text{'}],[$PROD$\_$DESC,$\backslash$table$[[$Houselite chain$],[$saw$,$ Rat$-$tail file$]],$Sledge hammer,$\backslash$table$[[$Rat$-$tail$],[$Steel tape$]],\backslash$table$[[$Rat$-$tail file, claw$],[$hammer$]],$Steel tape$]]$

Page $2$ of $5$

ICT$201$ Database Systems

Assessment $2$

$\backslash$table$[[$VEND$\_$CODE,$235,232,231,232,235,232,231,235],[$VEND$\_$NAME,$\backslash$table$[[$Shelly$],[$Smithson$,],[$Annelise Crystall$]],$K$.$ James Johnson,$\backslash$table$[[$Annelise$],[$Crystall$,]],\backslash$table$[[$Shelly$],[$Smithson$,$ James$],[$Johnson$]],$Arthur Jones$],[$QUANT$\_$SOLD,$1,3,1,2,1,2,1,],[$PROD$\_$PRICE,$$189\mathrm{.}99,$ $$2\mathrm{.}99,18\mathrm{.}63,$$$6\mathrm{.}79,$$$2\mathrm{.}99,$$$12\mathrm{.}95,2\mathrm{.}99,$$$2\mathrm{.}99]]$

a$.$ Draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies.

b$.$ Remove all partial dependencies, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created.

c$.$ Draw the dependency diagrams that are in $3$NF$.($Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is$,$ make sure there are no multivalued attributes, that the naming conventions are met, and so on$.)$

d$.$ Draw the Crow's Foot ERD. ERD should be created starting from solution of part $b($normalized form$)$ and then further enhanced to reflect the correct business scenario.

e$.$ Create the database and insert data as per Table $1$ and additional data for the sake of completeness.

f$.$ Formulate and execute the following queries.

i$.$ Write a query to display product Prod$\_$code, Prod$\_$QOH and Prod$\_$PRICE for all the products where price is greater than $200.$

ii$.$ Write a query to display prod$\_$num of products that are supplied by VEND$\_$CODE between $230$ and $234.$

iii. List all the products in products table in descending order of the price

iv$.$ Using the 'PRODUCT' table as a reference, construct a SQL query to calculate the total value of the products currently in inventory $($name the column as Tot$\_$worth.

v$.$ Display name of VENDOR who supplied product $\text{'}123-21$UUY'.

vi$.$ Write a single query that will double the price of product supplied by vendor whose name is 'James Johnson'. $($Note: you have to strictly use SUBQUERY to extract vendor code based on vendor name and use vend$\_$code to update the price$)$

g$.$ If you are a database administrator, what measures you will apply to ensure the security of the data?