Page $2$ of $5$

ICT$201$ Database Systems

Assessment $2$

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

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?