How would Problems and Exercises 21 and 22 change if a type of fish could be supplied by...

How would Problems and Exercises 21 and 22 change if a type of fish could be supplied by multiple suppliers?

Reference Exercises 21 and 22

A pet store currently uses a legacy flat file system to store all of its information. The owner of the store, Peter Corona, wants to implement a Web-enabled database application. This would enable branch stores to enter data regarding inventory levels, ordering, and so on. Presently, the data for inventory and sales tracking are stored in one file that has the following format:

Assume that you want to track all purchase and inventory data, such as who bought the fish, the date that it was purchased, the date that it was delivered, and so on. The present file format allows only the tracking of the last purchase and delivery as well as four prior purchases and deliveries. You can assume that a type of fish is supplied by one supplier.

a. Show all functional dependencies.

b. What normal form is this table in?

c. Design a normalized data model for these data. Show that it is in 3NF.

For Problem and Exercise 21, draw the ER diagram based on the normalized relations.

A pet store currently uses a legacy flat file system to store all of its information. The owner of the store, Peter Corona, wants to implement a Web-enabled database application. This would enable branch stores to enter data regarding inventory levels, ordering, and so on. Presently, the data for inventory and sales tracking are stored in one file that has the following format:

Assume that you want to track all purchase and inventory data, such as who bought the fish, the date that it was purchased, the date that it was delivered, and so on. The present file format allows only the tracking of the last purchase and delivery as well as four prior purchases and deliveries. You can assume that a type of fish is supplied by one supplier.

a. Show all functional dependencies.

b. What normal form is this table in?

c. Design a normalized data model for these data. Show that it is in 3NF.