Database Design for Clothing Retail Store Project

Retail chosen: Clothing retail store

Choose a retail business that sells products to its customers and create a database to store data and register different daily transactions.  Consider the following in your design:

 

1. The retail business should register information about the products it sells.

2. The details of its customers including their billing and delivery address and the product suppliers. 

3. A product can be on special offer. A special offer is valid for a period, which has a start date and an end date. A special offer refers to exactly one product.

4. The business, at minimum should store information about the products(stock) it sells, the suppliers, the customers, the sales transactions and the employees who handle the transaction. 

1. Describe/introduce the chosen retail business and its objectives (200-400 words).

2. Identify the relevant entities of the business with their respective attributes, entity types (strong or weak) and primary keys. You should include all the core entities of the business.

3. Identify the relationships, cardinalities and participation constraints with supporting business rules and assumptions.

4. Draw an ER diagram for the system depicting the entities, relationships, cardinalities, participations using your preferred ERD notation.

5. Convert the conceptual design into a relational model.  Make sure that the tables are in a 3rd normal form.

1. Create the corresponding database using DDL 

2. Create all the necessary tables identified above using DDL

3. Populate at least three of your tables with some data using DML (insert into statement)

4. Populate your database with a large data set representing a one-year transaction (01/01/2021 - 31/12/2021) on each table.  

(Use online data generators such as Mockaroo or generate data to generate synthetic data.)

1. Show all the details of the products that have a price greater than a specific value. 

2. Show all the products along with the supplier detail who supplied the products.

3. Create a stored procedure that takes the start and end dates of the sales and display all the sales transactions between the start and the end dates.
4. Create a view that shows the total number of items a customer buys from the business in October 2020 along with the total price (use group by)

5. Create a trigger that adjusts the stock level every time a product is sold.

6. Create a report of the annual sales (2020) of the business showing the total number of products sold and the total price sold every month (use A group by with roll-up)

1. A pdf/word document containing the answers to each of the above questions. Please add all your answers including descriptions, SQL Scripts and diagrams (ERD, report charts or graphs in this file) for each of the questions where applicable. 
2. An SQL script file that contains all the SQL statements used for part 2 and part 3. Each answer should have a clear label indicating the question number.
3. The dump file (self-contained SQL script) of the full database, including triggers and stored procedures.

1. Submit your word document file separately using Turnitin 
2. The SQL script file and the dump (self-contained) file in a folder and compress to a single zip folder. All your file names should use the following file name pattern.

[YourID]_[YourProgramCode]_[YourLastName]_[YourFirstName]_Description.doc/pdf
[YourID]_[YourProgramCode]_[YourLastName]_[YourFirstName]_SQL.sql 
[YourID]_[YourProgramCode]_[YourLastName]_[YourFirstName]_Dump.sql 
[YourID]_[YourProgramCode]_[YourLastName]_[YourFirstName]_Project.zip