Database design and optimisation Graduate by admin

Mysql online data base

Must develop and ERD diagram for an Online retail business of choice. The retail business must be able to collect CC payments. At minimum the developed relational database must collect and store the following information;
    Stock
    Customer Details
    Orders
    Suppliers
    Payments
    Returns

Using the ERD diagram that was developed earlier in Part A now expand upon the original ERD and develop and populate a real life Relational Database to hold their business data and ALSO a Data Mart to support the reporting requirements of the retail company.

In order to implement this Data Mart students are required to automate the loading of the data from the live production database to the Data Mart. This should be done by scripting.

Project Report Document
a.    Background Information Document and relevant graphs connected via Excel to 2 reports of YOUR CHOICE
b.    Script File to Create the Data Mart database and tables and the following reports
c.    The Data Mart SHOULD ONLY CONTAIN results only from Views  (Questions 1, 3 and 4 ONLY)

1. Create a View showing all transactions for a given week in your business.
2. Create a trigger that stores stock levels once a sale takes place.
3. Create a View of stock (by supplier) purchased by you.
4. Create a View of Total stock sold to general public (group by supplier).
5. Detail and total all transactions (SALES) for the month-to-date. (A Group By with Roll-Up)
6. Detail and total all SALES for the year-to-date. (A Group By with Roll-Up)
7. Detail & total transactions broken down on a monthly basis for 1 year. (A Group By with Roll-Up)
8. Display the growth in sales/services (as a percentage) for your business, from the 1st month of opening until now.
9. Use Excel to create a graphical representation of any 2 of the queries of YOUR CHOICE, for example Q1 & Q4. (Any type of graph)
10. Add both the SQL statements and graphs into a Word document report (Your SQL code and the screenshots and relevant graphs & ERD Diagram). The Word document must show the results of your queries when run (Screen shot of the Output).