LD7084 Database and Analytics Principles.

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.