Assignment

Question 1

Sales

Using the above Sales table structure, perform the followings:

Exercise 1:

Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You may assume that an order number references more than one product and that the table does not contain repeating groups.

Exercise 2:

Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 3:

Write the relational schemas after removing all transitive dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 4:

Draw the Entity Relationship Diagram (ERD) according to the result of Exercise 3.

Question 2:

You are given the following table structure with sample data in it.

Exercise 1:

Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization.

Exercise 2:

Write SQL code to create the generated tables.