Hands-on Assignment 3

You are hired to help the employer to build a database to keep track of their inventory system.

Inputs: Online Retail Spread Sheet named "Online Retail.xlsx" (top 200 rows)

The specific tasks for your are:

1) Review the existing spreadsheet, and their sample data and make any assumptions you need about the company;

2) Design a data model(ERD, logical data model) using Erwin Modeler after reviewing the existing spreadsheet. Add any attributes(including data range) that you think could be useful. Identify primary keys, foreign keys, and relationships;

3) Perform a functional dependency analysis. Make sure all the tables are at least in 3NF. Make changes to the ERD if necessary;

4) Create a database using SSMS. Use SQL statements to create tables, insert rows, and create constraints;

5) The required constraints are:

1. the date of Invoice must be after '2010-Jan-01'
2. the customer id must be a five-digit number
3. the default value of quantity is 0
4. quantity is between 0 and 200
5. use ON DELETE CASCADE and ON UPDATE CASCADE for the foreign key of the weak entity

6) Add a column into table Product to indicate the inventory status(If the column has not been created in previous steps). Create a trigger named quantity_update that checks the quantity of each product

Trigger timing and event: After Updating rows in the product table

Trigger action: If the quantity is below 5, then change the inventory status to "Need attention"

7) Run two queries:

1. Find all the customers(ids) who purchased "CREAM CUPID HEARTS COAT HANGER" and "SAVE THE PLANET MUG"
2. Find out which customer spent most money