Staples and All Ltd. company (SAA) is an office supplies company. Their customers range from big companies to small and medium companies, and operate across the UK. SAA has been in the market for over 20 years and their business has seen tremendous growth recently. As the market is very competitive, it is vital for executives to have various performance reports quickly and accurately so that they can make decisions accordingly. Along with SAAs growth, the number of transactions and the size of data has been increased. This puts more and more pressure on the current reporting tool of SAA. The tool can still be used yet it is getting sluggish and unreliable. The Accounting and Finance department has discussed with the IT department the need to adjust the ERP system so that the data from the ERP system can be used more easily within the current reporting tool. There are, however, two problems with this approach. Firstly, the IT department said those adjustments will take around four to six months to implement. More importantly, requirements from executives change on a daily basis. New reports are required, also on daily basis. Some ad-hoc/one-off reports are required to be ready within extremely tight deadlines. The IT department, however, can upload raw data of sale transactions (the Raw) to a shared drive whenever it is needed by Accounting and Finance. You have just joined the Accounting and Finance department as , a new graduate from University of Portsmouth, and are very keen to apply your knowledge from the Data Concepts and Management unit to solve those problems. After days and nights in the office, you have found out the following: - Customers can purchase as many items within one order and make as many orders as they want per day. Therefore, an order can have many items within. - Each item/article (with its quantity and price) will be recorded as one row in the Raw For example, the first row of the Raw is the transaction number 0 and the item sold is article number 1026691 at the quantity 20 and price of 18.17. This transaction may belong to only one specific order, however the related order is not recorded in the Raw. The related order may have other transactions within and all other transactions are recorded in the Raw. It is not possible to group related transactions against their specific order. - In SAA, the term Division refers to a division of a group company. This is because each sale transaction is made to a specific geographical location of a group company and not the group company itself. In other words, a division refers to a lower level of a group company. The division is where the sales are made and to where the goods are delivered. - A group company can have many divisions. However, for one location/city (e.g. Bath) there is only one division of that group company.

- A group owner is a relationship manager (salesman) who is responsible for some group companies. There are approximtely 20 group owners responsible for over 200 group companies. - Similarly, a division owner is responsible for divisions. Typically a division owner serves divisions within one location/city. There are approximately over 100 division owners to serve customers spreading across the UK. A product owner is a salesman who is responsible for a specific line of product. For example, a customer can purchase papers and pens: there will be one product owner for papers and a different product owner for pens. - An employee can have multiple jobs: a group owner, division owner and/or product owner at the same time - There are three layers of products, the highest level is Product Section, followed by Product Category and down to the smallest is individual item (Article). - For each line in the Raw, there are: + quantity: number of the respective item ordered + price: price of the item + amount: multiplication of quantity and price. Note: the DivisionID has been invented years ago and no longer suitable to uniquely identify a division. You may need to use DivisionName along with GroupName for that purpose. Requirements: 1. DrawthefullylabelledandimplementableCrowsFootERDbasedon your findings above. Include all entities, relationships, optionalities, connectivities, and cardinalities. (20 marks) 2. ImplementyourERDinMySQL. (20marks) 3. Writeamanagementreportwhichaddressthefollowingquestions: 3.1Total revenue SAA made during the period from 01/01/2017 to 31/12/2017 3.2 Monthly sales for the same period 3.3 The top 5 popular products/articles per: (1) quantity and (2) total revenue during the period. 3.4 Total number of items (quantity) sold per quarter during the same period for: (1) the whole SAA and (2) per customer (group level only) (please only include the top five and bottom five group customers in your main report) (10 marks) 3.5 Top three biggest customers (Group level) purchased during the same period

For all those three customers, which three divisions have the highest sales. (10 marks) 3.6 The top 5 best performance group owners and product owners during the same period (10 marks) 3.7 Presentation (clear headings, easy to understand to non-technical users e.g. use of charts) (10 marks) TOTAL MARKS (100 Marks) To address these three requirements, you should submit one document which includes three clearly separated parts with their headings: - Part 1: Crows Foot ERD - Part 2: SQL queries to create database & its tables to reflect your Crows Foot ERD in Part 1. - Part 3: A report to non-technical users (e.g. financial managers or CFOs etc.) to show results of all questions in question 3. You will need to include all SQL queries you use to answer those questions in appendix, not in the main body of the report. There - Small raw table: only contains 10,000 rows hence you can test your are two raw tables for you to use in this exercise: codes and not overwhelmed by data. This table is for TESTING purpose only. - Big raw table: it is the Raw table mentioned above and contains nearly 2 million rows of transactions, please use this real table for queries in part 2 and 3. - Both tables have identical structure and can be found in the below table

Name of attribute Example Data type TransactionID 0 int Transaction Date Week 2017-01-02 datetime 1 int 999 int HGF VARCHAR(100) GroupID GroupName NumberOfDivision 4 int Description Uniquely identify a transaction Date of transaction Number of week in the data set (domain: 1->78) ID of the group company Name of the group company Number of divisions group company has ID of the division of the group company. The field was created long time ago and no longer be able to uniquely identify a branch. Name of the division of the group company ID of the salesman who is responsible for the group company Name of the salesman who is responsible for the group company ID of the salesman who is responsible for the division company/branch of the group company DivisionID 999SHE VARCHAR(100) HGF in Sheffield VARCHAR(100) Division Name Group OwnerID 7 int Group Owner Leo Howell VARCHAR(100) Division OwnerID 94 int Division Owner Ibrahim Rogers VARCHAR(100) Product OwnerID 19 int ProductOwnerName Name of the salesman who is responsible for the division company/branch of the group company ID of the salesman who is responsible for the product supplied Name of the salesman who is responsible for the product supplied Section of product, the highest level of the product tree Category of product, the second level of the product tree SKU_Category of product, the third level of the product tree Arthur Kent VARCHAR(100) Paper & Printing | VARCHAR(100) Printer Paper VARCHAR(100) Product Section Category Item identification number SKU category Copier & Printer Paper VARCHAR(100) Article 1026691 VARCHAR(20) Viking Everyday Copy Paper A4 80gsm Product Description White 2500 VARCHAR(200) itemPrice 18.17 numeric(10.2) itemQuantity 20 numeric(10,2) Item description Price of item in the transaction Quantityumber of items in the order orderRevenue 363.4 numeric(20,2) Total order value Name of attribute Example Data type TransactionID 0 int Transaction Date Week 2017-01-02 datetime 1 int 999 int HGF VARCHAR(100) GroupID GroupName NumberOfDivision 4 int Description Uniquely identify a transaction Date of transaction Number of week in the data set (domain: 1->78) ID of the group company Name of the group company Number of divisions group company has ID of the division of the group company. The field was created long time ago and no longer be able to uniquely identify a branch. Name of the division of the group company ID of the salesman who is responsible for the group company Name of the salesman who is responsible for the group company ID of the salesman who is responsible for the division company/branch of the group company DivisionID 999SHE VARCHAR(100) HGF in Sheffield VARCHAR(100) Division Name Group OwnerID 7 int Group Owner Leo Howell VARCHAR(100) Division OwnerID 94 int Division Owner Ibrahim Rogers VARCHAR(100) Product OwnerID 19 int ProductOwnerName Name of the salesman who is responsible for the division company/branch of the group company ID of the salesman who is responsible for the product supplied Name of the salesman who is responsible for the product supplied Section of product, the highest level of the product tree Category of product, the second level of the product tree SKU_Category of product, the third level of the product tree Arthur Kent VARCHAR(100) Paper & Printing | VARCHAR(100) Printer Paper VARCHAR(100) Product Section Category Item identification number SKU category Copier & Printer Paper VARCHAR(100) Article 1026691 VARCHAR(20) Viking Everyday Copy Paper A4 80gsm Product Description White 2500 VARCHAR(200) itemPrice 18.17 numeric(10.2) itemQuantity 20 numeric(10,2) Item description Price of item in the transaction Quantityumber of items in the order orderRevenue 363.4 numeric(20,2) Total order value