Consider the enclosed information on the stores and products sold at the stores of the Energy Star inc.

The product price is the same in all stores.

Quantity on hand differs from store to store since the demand may differ from store to store.

Each store is managed by a single manager, but the same manager may be managing several stores.

Each store offers discounts to move inventory. Note that Discount is solely determined by the quantity on hand, irrespective of store location or product. For example if the quantity on hand is 150, then the discount is 5%. If the quantity on hand is 180, then discount is 10%, irrespective of product or store location. Note that products that have a small quantity on hand do not have a discount, or discount = 0%.

Rules to follow.

o DO NOT assume Manager Names are Unique.

o DO NOT assume product names are unique. Ex: The store may be selling many different Televisions.

A. Create an Entity Relationship Diagram

B. Write down the database schema by identifying all primary keys and foreign keys

C. Write down the referential integrity constraints

D. Verify if each of the tables is in 3rd Normal Form and BCNF. If not, carry out normalization to arrive at a BCNF database design. For each table you must identify Primary Key, write down functional dependencies and then check for 3rdNF and BCNF