1. On the following page are the data and data descriptions for the TAL Distributors company. TAL sells various toy items to customers. Each customer must be assigned one and only one sales representative. A customer may place only one order on a given date but may place orders on different dates. Each order must contain a least one of the items (toys) and may contain several of a particular item (e.g. a customer may place an order for 5 Wood Block Sets). One order may contain many different items. All customers are charged the same unit price for each item. There are no quantity discounts. TAL Distributors would also like to know the total revenue (Price times Number Ordered) for each line item. Complete and turn in the following concerning TAL. a. Draw the Dependency Diagram for the report identifying the primary key of the relationship. Show all of the relevant dependencies. b. Is the relation in INF? If not, convert the relationship to INF and identify all keys. Draw the revised Dependency Diagram. c. Is the relation in 2NF? If not, convert the relationship to 2NF and identify all keys. Draw the revised Dependency Diagram. d. Is the relation in 3NF? If not, convert the relationship to 3NF and identify all keys. Draw the revised Dependency Diagram. e. Is the relation in 4 NF? If not, convert the relationship to 4NF and identify all keys. Draw the revised Dependency Diagram. f. Draw an (Chen) E-R model to represent the entities associated with the final TAL Distributors design. Inelude the following in the diagram. i. Type of Relationship ii. Whether the relationship is optional or mandatory. iii. Show the cardinality. iv. Identify the composite attributes, if any. v. Identify any derived attributes, if any. OS 3060 Database Management Systems (instructor led) Garman Second Assignment Page 2 TAL Distributors Sales Data TAL Distributors Data Column Descriptions 1. On the following page are the data and data descriptions for the TAL Distributors company. TAL sells various toy items to customers. Each customer must be assigned one and only one sales representative. A customer may place only one order on a given date but may place orders on different dates. Each order must contain a least one of the items (toys) and may contain several of a particular item (e.g. a customer may place an order for 5 Wood Block Sets). One order may contain many different items. All customers are charged the same unit price for each item. There are no quantity discounts. TAL Distributors would also like to know the total revenue (Price times Number Ordered) for each line item. Complete and turn in the following concerning TAL. a. Draw the Dependency Diagram for the report identifying the primary key of the relationship. Show all of the relevant dependencies. b. Is the relation in INF? If not, convert the relationship to INF and identify all keys. Draw the revised Dependency Diagram. c. Is the relation in 2NF? If not, convert the relationship to 2NF and identify all keys. Draw the revised Dependency Diagram. d. Is the relation in 3NF? If not, convert the relationship to 3NF and identify all keys. Draw the revised Dependency Diagram. e. Is the relation in 4 NF? If not, convert the relationship to 4NF and identify all keys. Draw the revised Dependency Diagram. f. Draw an (Chen) E-R model to represent the entities associated with the final TAL Distributors design. Inelude the following in the diagram. i. Type of Relationship ii. Whether the relationship is optional or mandatory. iii. Show the cardinality. iv. Identify the composite attributes, if any. v. Identify any derived attributes, if any. OS 3060 Database Management Systems (instructor led) Garman Second Assignment Page 2 TAL Distributors Sales Data TAL Distributors Data Column Descriptions
