Assessment Description

$$Using the INVOICE table structure shown in Table

$1$

$,$

$$do the following:

Table

$1$

$$:

Attribute Name

Sample Value

Sample Value

Sample Value

Sample Value

Sample Value

INV

$\_$

NUM

$1001$

$1002$

$1003$

$1004$

$1005$

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

Page

$2$

$$of

$5$

$$ ICT

$201$

$$Database Systems Assessment

$2$

VEND

$\_$

CODE

$235$

$,$

$232$

$231$

$232$

$,$

$235$

$232$

$,$

$231$

$235$

VEND

$\_$

NAME

Shelly K

$.$

$$Smithson$,$

Annelise Crystall

James Johnson

Annelise Crystall,

Shelly K

$.$

$$Smithson$,$ James Johnson

Arthur Jones

QUANT

$\_$

SOLD

$1$

$,$

$3$

$1$

$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?

i need diagram also please