solution

A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of the information about its inventory.

CPR has several warehouses across the country. Each warehouse is uniquely named. CPR also wants to record the location, city, state, zip, and space (in cubic meters) of each warehouse. There are several warehouses in any single city.

CPR stores its products in the warehouses. A product may be stored in multiple warehouses. A warehouse may store multiple products. The quantity of a product in a warehouse needs to be recorded.

Every product has a unique UPC number. Other information about a product includes a name, a buying price, an approximate selling price, and a size (in cubic meters).

CPR also keeps track of the information about the manufacturers of products. Every product has a single manufacturer, but a manufacturer may manufacture multiple products. Each manufacturer has a unique name, an address (street, city, state, zip), and a contact phone number.

The requirements of CPR also indicate that there are the following full Functional Dependencies:

UPC -> Name, Buying_Price, Selling_Price, Size, Manufacturer_Name Manufacturer_Name -> MStreet, MCity, MState, MZip, MPhone Warehouse_Name -> WLocation, WCity, WState, WZip, WSpace UPC, Warehouse_Name -> Quantity

A consulting company named Database Experts has designed the following relation data model for CPR.

Product (UPC, Name, Buying_Price, Selling_Price, Size, Manufacturer_Name, MStreet, MCity, MState, MZip, MPhone, Warehouse_Name, WLocation, WCity, WState, WZip, WSpace, Quantity)

Although the designers at Database Experts claim that their design is superior in all aspects, CPR gives you a fair chance to justify your position. Now it’s your time to do the following.

1. Show them what normal form their relation is in and why.

2. Rescue their “bad” design using normalization. Decompose their relation Product into multiple smaller relations that are all in 3NF. Underline the primary key of each of your relations.