Attribute

Name Sample Value Sample Value Sample Value Sample Value Sample Value

INV$\_$NUM $10011002100310041005$

PROD$\_$NUM $123-21$UUY, SRE$-657$UG QER$-34256$ SRE$-657$UG$,$

ZZX$/3245$Q 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 Houselite chain saw, Rat$-$tail file Sledge hammer Rat$-$tail

file,

Steel tape Rat$-$tail file, claw hammer Steel tape

VEND$\_$CODE $235,232231232,235232,231235$

VEND$\_$NAME Shelly

K$.$

Smithson,

Annelise Crystall James Johnson Annelise

Crystall, Shelly

K$.$

Smithson,

James

Johnson Arthur Jones

QUANT$\_$ SOLD $1,311,21,21$

PROD$\_$PRICE $$189\mathrm{.}99,$ $$2\mathrm{.}9918\mathrm{.}63$ $$6\mathrm{.}79,52\mathrm{.}99$ $$12\mathrm{.}95,2\mathrm{.}99$ $$2\mathrm{.}99$

$1.$ 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.

$2.$ 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$.)$

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

$4.$ 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$)$