Reference no: EM132437077

LD7084 - Database and Analytics Principles - Northumbria University

Learning Outcomes

This assignment will assess the following learning outcomes:

- A critical knowledge and understanding of data and data analytics theories and methodologies
- Ability to apply data analytics skills and knowledge to a complex business challenge or project
- Ability to evaluate your data analytics skills.
- Critical reflection of your implementation, recommendations for improvement and the subsequent implications for your learning development in data and data analytics domain.

Scenario

BuyUs is an online retail company, which is looking to develop an effective Relational Database Management System (RDBMS) and data warehouse to cater the needs of their growing business. As a data analyst, you have been asked to develop a required system for BuyUs. Which should satisfy the following information requirements:
• The database should contain information about Customers, Employees, Products, and Orders.
• For each customer, the database should store Customer ID, Customer Name, Customer Address, and Customer Phone number.
• For each employee, the company wants to store Employee ID, Employee Name, Employee Address, Employee Salary, Employee Job Title and Employee Qualification.
• An employee may serve one or more orders but exactly one employee can serve one order.
• For customer orders, it is required to store Order ID, Order Date, Quantity Ordered, and Products Ordered. A customer can have one or more orders.
• An order can have more than one product and one product can be ordered multiple times.
• The BuyUs has many products and each product has Product ID, Product Name, Product Price and Product Quantity, which needs to be stored in the database.

Section 1 - Database

Task 1: Modelling and implementation

1. Using Crow's foot notation, design a logical data model (ERD) for the AutoSale's database in a modelling tool of your choice. All entity types, attributes and the relationships between them must be labelled clearly. Your design should show all cardinality and participation constraints accurately. Discuss and justify your design choices in detail.

2. Produce a script using appropriate SQL commands (DDL) to create a set of database tables using MySQL relational database. Your tables should also show all constraints applied at either the column or the table level. You are expected to implement the database so that it must meets the requirements described in the scenario.

3. By using appropriate SQL statements (DML), populate all the tables in the database you created with some data (At least 10 records in each table). The data should be meaningful but does not need to be extensive.

4. Critically evaluate following data analytics technologies/tools and explain how these can be utilised by the BuyUs company to derive value from the data

I. Hadoop
II. Data warehouse
III. Data Mining

Section 2 - Data warehouse

Task 2: Designing Star Schema

Using Kimball's four-step dimensional design process, Draw/design a Star Schema of BuyUs that will allow the company to investigate about why some cities have good sales whilst others have not. Discuss and justify your design choices. You may make any reasonable assumptions to support your design (state any assumptions you make).

Section 3 - Data Analytics

In this scenario, you are hire as a Data Analyst by a reputable marketing firm. Your client is a telecom company based in the USA and operating in various States. The telecom company is going to launch a new service. Since existing customers are more likely to require new services from the company, they would like to find out in which State a new service can be advertised and sold. The company needs to determine that the services with high benefits could be sold.
To solve this problem, you will use publicly available data sets that have been prepared for you and are available in the appendices.
The given dataset has 3334 entries and 21 attributes, which show the following customer information.

Task 3 - Data Analysis

Question 1. You must use Tableau to analyse and disclose various associations within the data set provided. Using Tableau, find answers to the following questions based on your analysis. You will need to provide a rationale and justification for your solution.

I. Using Tableau perform data integrity checks on each individual column of the given data set [5]
II. Using Tableau produce the summarized statistics on number of voice mail messages from the data set e.g. Distinct in all states [5]
III. Using Tableau find the average value of total night calls in minutes [5]
IV. Using Tableau find out the maximum number of voice mail messages for OH, OK and RI states [5]
V. Using Tableau find out the sum of total day calls for customers that have not churned and belong to OH and NY states [5]

Each answer of the above question must have a separate worksheet with a chart, graph or a table that shows your findings.

Question 2. Critically reflect on your learning and evaluate the strengths and weaknesses of the data analytics tool used and recommend other data analytics tools which could have been used for the further enhancement.