Problem
Archie's Golden Supply is a distributor of supplies to the fast food industry. The owner of the company, Archie McKing, has hired you to design a Web-based database management system to track inventory and sales. Presently, the data for inventory and sales tracking are stored in one file with the following format:
Store_Name, Supply_Description, Price, Cost, Manufacturer_Name, Shipping_Time_From_Manufacturer,Quantity_on_Hand, Date_of_Last_Delivery, Quantity_of_Last_Delivery, Delivery_Date1, Quantity_of_Delivery1,Delivery_Date2, Quantity_of_Delivery2, Delivery_Date3, Quantity_of_Delivery3,Delivery_Date4, Quantity_of_Delivery4, Reorder_Level
Assume that we want to track all purchase and inventory data, such as what was delivered, how many were delivered, etc. The present file format allows only the tracking of the last purchase and delivery as well as four prior purchases and deliveries.
1. Given this file structure, draw its dependency diagram. Label all transitive and/or partial functional dependencies.
2. Break up this dependency diagram to produce dependency diagrams that are in third normal form. You might need to create a few new attributes.
3. Draw the relational schema.