Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

a . Draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. b . Remove all partial dependencies, and draw the

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 3NF.(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 Crows 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 (nTable 1:
\table[[\table[[Attribute],[Name]],Sample Value,Sample Value,Sample Value,Sample Value,Sample Value],[INV_NUM,1001,1002,1003,1004,1005],[PROD_NUM,\table[[123-21UUY,SRE-],[657UG]],QER-34256,\table[[SRE-657UG,],[ZZX/3245Q]],\table[[SRE-657UG,],['001278-AB']],ZZX/3245Q],[SALE_DATE,'2022-3-8','2022-3-8','2022-3-8','2022-3-9','2022-1-16'],[PROD_DESC,\table[[Houselite chain],[saw, Rat-tail file]],Sledge hammer,\table[[Rat-tail file,],[Steel tape]],\table[[Rat-tail file, claw],[hammer]],Steel tape]]
Page 2 of 5
ICT201 Database Systems
Assessment 2
\table[[VEND_CODE,235,232,231,232,235,232,231,235],[VEND_NAME,\table[[Shelly],[Smithson,],[Annelise Crystall]],James Johnson,\table[[Annelise],[Crystall,]],\table[[Shelly],[Smithson, James],[Johnson]],Arthur Jones],[QUANT_SOLD,1,3,1,1,2,1,2,1],[PROD_PRICE,$189.99,$2.99,18.63,$6.79,$2.99,$12.95,2.99,$2.99]]ame the column as Tot_worth.
v. Display name of VENDOR who supplied product '123-21UUY'.
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?
image text in transcribed

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

SQL Instant Reference

Authors: Gruber, Martin Gruber

2nd Edition

0782125395, 9780782125399

More Books

Students also viewed these Databases questions

Question

=+3. List the touchpoints where you'd reach your audience.

Answered: 1 week ago