Draw the fully labelled and implementable Crow’s Foot ERD based on your findings above and include all entities, relationships.

Database Design and Implementation

Database Design and Implementation (M31994 & M32275)
Coursework 2: Modelling and querying in SQL
Unit coordinator: Frank Nguyen
Accounting & Financial Management Subject Group

This is an individual assignment and carries [50%] of the module mark and the
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 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. 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)

Draw the fully labelled and implementable Crow’s Foot ERD based on your findings above and include all entities, relationships.