In the hypothetical company, it has been decided to redesign a major part of the database that underlies company operations. Unfortunately, the manager assigned to solicit database design proposals is not very computer-literate and is unable to provide a very detailed specifications at the technical level. Here is what they were able to provide:

•There are many different products, grouped into a variety of (possibly overlapping)categories. Groupings can by type of product (cameras, phones, game consoles, etc.), by the manufacturer (Sony, Apple, Microsoft, etc.), or by other means (for example, an Alienware laptop might be packaged with a Visio monitor and a Canon printer and marketed as a package deal).

•Customers can pay with a credit or debit card. Card information may be stored for online customers since they can create an account.

• The company does not handle shipping/distribution itself. Online sales must be sent to a shipper. The company needs to store the tracking number for the shipping company so it can respond to customer inquiries.

• The company gets its products from various suppliers to service their customer base and their wide array of needs.

• Sales data are important for corporate planning. Marketers may want to look at sales data by time period, product, product grouping, season, region, etc.

• The company also wants to keep track of employees in the company. This is just so that information can be easily queried about individuals working in the company to pull up basic information about them (title, department, etc.).

Client Requests:

1. ERD Model
a. Construct an E-R diagram representing the conceptual design of the database.
b. Be sure to identify primary keys, relationship cardinalities, etc.

2. Database Design Model
a. After creating an initial relational design from your ERD, refine it based on the principles of relational design.
b. Create the relations in the DBMS of your choosing.
c. Create checks & constraints, as appropriate.
d. Make sure that your DBMS accurately reflects the documents created previously

3. Populate Relations
a. Create & insert data into your DBMS. I would aim for around 20-30 entries to make it to where you can pull some records based on your queries
b. Queries: The queries listed below are those that your client wants to be turned in. Could hint about database design.
i. Assume the package shipped by USPS with tracking number 123456 is reported to have been destroyed in an accident.
Find the contact information for the customer. Also, find the contents of that shipment and create a new shipment of replacement items.
ii. Find the customer who has bought the most (by price) in the past year.
iii. Find the top 2 products by dollar amount sold in the past year.
iv. Find the top 2 products by unit sales in the past year.
v. Find those products that are out-of-stock at every store in California.
vi. Find those packages that were not delivered within the promised time.
vii. Generate the bill for each customer for the past month.