1. Draw the fully labelled and implementable Crow’s Foot ERD based on your findings above. Include all entities, relationships, optionalities, connectivities, and cardinalities. (20 marks) 2. Implement your ERD in MySQL. (20 marks)

Database Design and Implementation

Maximum word count is 1,500 words. It covers the following learning outcomes for the module:

Understand the role of databases in an organisation

Understand relational databases & structured data

Critically design and implement relational database given dataset and requirements

Be able to create and query databases using SQL

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 SAA’s 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 Division Id has been invented years ago and no longer suitable to uniquely identify a division. You may need to use Division Name along with GroupName for that purpose.

Requirements:

1. Draw the fully labelled and implementable Crow’s Foot ERD based on your findings above. Include all entities, relationships, optionalities, connectivities, and cardinalities. (20 marks)

2. Implement your ERD in MySQL. (20 marks)

3. Write a management report which address the following questions:

3.1 Total revenue SAA made during the period from 01/01/2017 to 31/12/2017 (5 marks)
3.2 Monthly sales for the same period (5 marks)

3.3 The top 5 popular products/articles per:

(1) quantity and
(2) total revenue during the period. (10 marks)

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) (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