sing Modern Databases Management Taxbook, 12 Edition by Jeffrey Hoffer. Question
ID: 3689419 • Letter: S
Question
sing Modern Databases Management Taxbook, 12 Edition by Jeffrey Hoffer. Question:
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 store 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: StoreName, PetName, Pet Description, Price, Cost, SupplierName, ShippingTime, QuantityOnHand, DateOfLastDelivery, DateOfLastPurchase, DeliveryDate1, DeliveryDate2, DeliveryDate3, DeliveryDate4, PurchaseDate1, PurchaseDate2, PurchaseDate3, PurchaseDate4, LastCustomerName, CustomerName1, CustomerName2, CustomerName3, CustomerName4.
Assume that you want to track all purchase and inventory data, such as who bought the fish, the date that it was purchase, 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 the functional dependencies. b) What normal form is this table in? C) Design normalized data model for these date. show that it is in 3NF.
Explanation / Answer
The normalized data model for these data is given below. Here i have show the different normalized relations that should be used.
1. Pet(PetId, PetName, Pet Description, Price)
2. Customer(CustomerId, CustomerName, CustomerAddress, CustomerPhoneNo)
3. SalesOrder(OrderId, PetId, SupplierId, Cost, CustomerId, Quantity, PurchaseDate, ShippingTime, DeliveryDate)
3. Supplier(SupplierId, SupplierName)
The tables are in 3NF.
The functional dependencies are given below:
1) PetId -----> PetName, PetDescription
2) CustomerId -----> CustomerName, CustomerAddress
3) SupplierId -----> SupplierName
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.