I need assignment based on the attached questions below.

Prof. Hazem Farra IS 443/543 – 54 – Database Design, Implementation, and Administration

Hazem Farra Information System Department [email protected] St. Cloud State University

1

Assignment 5

1. Create an example of a relation (containing several [5-10] records) that is prone to update

anomalies. The relation can be about one of the following:

• University keeping track of records related to classes or students, etc.

• A hospital keeping track of patients, prescriptions, doctors, rooms, etc.

• An auto manufacturer keeping track of factories, raw material, makes, etc.

2. Using the relation created in #1 above, do the following:

a) Describe an example that illustrates the insertion anomaly.

b) Describe an example that illustrates the deletion anomaly.

c) Describe an example that illustrates the modification anomaly.

3. Depict full key functional dependencies, partial functional dependencies (if any), and

transitive functional dependencies (if any) in the relation you created above.

4. Show the result of normalizing the relation you created in #1 to 2NF.

5. Show the result of normalizing the relation you created in #1 to 3NF.

6. Consider the following tables definitions and data:

CUSTOMER:

Prof. Hazem Farra IS 443/543 – 54 – Database Design, Implementation, and Administration

Hazem Farra Information System Department [email protected] St. Cloud State University

2

PRODUCT

VENDOR

Prof. Hazem Farra IS 443/543 – 54 – Database Design, Implementation, and Administration

Hazem Farra Information System Department [email protected] St. Cloud State University

3

Write the SQL queries that accomplish the following tasks:

7. Display all records in the table CUSTOMER

8. Display the CusFName, CusLName and CusAreaCode for all customers, sorted alphabetically by

CusLName

9. Display the ProdCode, ProdDescript, and ProdPrice for products with a Product Price of $100 or

higher.

10. Display the ProdCode, ProdDescript, and ProdPrice for Products whose VendorName is Gomez Bros.

Sort the results by ProdPrice.

11. Display ProdCode and ProdDescript, ProdPrice, and VendorName for all products with ProdDescript

containing “saw”.

Submission:

Submit a MS Word document by the due date listed on D2L, with the following requirements :

1) File name: Assignment5_LastName

2) Header:

a) Full Name

b) Class-Section

c) Assignment Title

d) Due Date

3) Page number in the footer