This coursework is designed to achieve the following PLOs:

PLOs $7\mathrm{.}2,7\mathrm{.}3,7\mathrm{.}4,7\mathrm{.}5,7\mathrm{.}9$

Develop abstract thinking and design ability to analytically demonstrate concepts relating to data science.

Use research$-$based knowledge for the design of experiments, analysis, and interpretation of data to provide valid results.

Critically evaluate and analyse advanced data science topics, and concepts, and implement them in workplace.

Identify and implement appropriate programming and software tools to critically analyse big data applications in workplace.

Critique legal, social, and ethical within the field of data science and Applicable ancillary sectors$,$ as applied to contemporary research and industry practice.

Assignment Description

You have been hired by a large e$-$commerce company to design and implement a new database system to manage their sales data. The company sells products across multiple categories, including electronics, clothing, home goods, and more. They have a large customer base and receive a high volume of orders each day.

The company wants to be able to track sales data by product category, customer demographics, and order history. They also want to be able to monitor inventory levels and receive alerts when inventory levels fall below a certain threshold.

Develop a DBMS for a small business that sells products online. The system should be able to handle orders, inventory, customer information, and product information.

Requirements:

The system should allow customers to browse and purchase products online.

York St John University London $-$ LDS$7002$M Assessment Brief $2023-24$

YORK

ST JOHN

UNIVERSITY

Est.

$1841$

The system should keep track of inventory levels and automatically update them as products are sold.

The system should be able to process and fulfil orders, including generating shipping labels and tracking information.

The system should store customer information such as name, address, email, and order history.

The system should store product information such as name, description, price, and availability.

The system should provide reporting capabilities, such as sales reports and inventory reports.

The system should be secure and protect sensitive data, such as customer payment information.

Deliverables:

data model including ER diagram to defines the tables and relationships needed for the DBMS$.$

SQL script to create the tables and populate them with sample data.

Write SQL queries to retrieve the following information:

Total sales for each product category for a given date range.

Top $10$ best$-$selling products for a given date range, including product name, category, and total sales.

Customer demographics, including age, gender, and location, for customers who have made a purchase in the last month.

Current inventory levels for each product, including product name, category, and quantity in stock.

Revenue generated by each customer in the last year, including customer name and total revenue.

Documentation should explain how to use the system, including user guides and technical documentation.

Assume that the following information is available:

Product information: product ID$,$ product name, product description, price, category ID$.$

Category information: category ID$,$ category name, category description.

Customer information: customer ID$,$ customer name, email, phone number, age, gender, location.

Sales data: transaction ID$,$ customer ID$,$ product ID$,$ transaction date, quantity sold, total price.

Inventory data: product ID$,$ warehouse location, quantity in stock.