BIA B450F代写、SQL编程设计代写

日期：2022-11-28 11:10

BIA B450F Assignment 2

Weighting: 20%

Due Date: 28 November 2022 (Monday)

Learning outcome:

Develop a dimensional model and describe the use of various data models in the data

warehouse and big data.

Manage a data warehouse and big data platform development project.

Important note

You should note that there might not be a single correct answer to the questions. Your

answers to these questions may be different from each other and could all be equally valid.

Question

In the first assignment, you have studied the foods and groceries of an online supermarket and

proposed a data warehouse architecture for the company. The CEO is pleased with your last

report and would like to develop the dimensional model of the data warehouse for evaluating the

sales and performing target marketing and also provide a project plan. The CEO was interested

in questions like, what is the sales amount and income by different types of products, by sales

regions, by time/date (daily, weekly, monthly, day of week, etc.), by promotional type, by

customer characteristics, etc., which products do the most business, how frequent do existing

customers place their orders on the websites, which products do customers would be together

with or next, etc., and so on. (Note: you may make assumptions about how the company keeps

track of the services time and also how it charges customers for their services but you have to

clearly explain your assumptions in your answer.)

You should revisit the work done in Assignment 1 and then carry out the following tasks:

1. Provide a relational database design of the online supermarket for the food and grocery’s

sales, including a schema description of the database and an ERD diagram showing the

relationships of tables. The schema description should be a description of the all the

tables in the database including (for each table):

the table name,

column/attribute names and

the length and data type of each attribute.

The database should hold sufficient data for product listing, product searching, online

ordering, and order delivery and tracking of the online supermarket. Specifically, the

database should have the following types of information:

Information of products including types, colours, styles, etc.

Customer registration information (if applicable)

2

Promotion information

Customer order information including delivery, the shipment cost, payment

method, etc.

You should note that the database design varies depending on the functionality provided

by the website and there might not be a single correct answer to the questions.

(30 marks)

2. Identify the dimensions and measures of the data warehouse that are required to satisfy

the analysis requested by the business. (Note: you only need bare minimum attributes for

dimensions, such as the name and ID of the entity members.) (10 marks)

3. Identify the source tables/columns (in your relational database design) for dimension

attributes and measures. Use a table to present the mapping between the source

tables/columns and dimension attributes/measures. (10 marks)

4. Design dimension tables and a fact table required to satisfy the analysis requested by the

business and draw an ERD diagram showing the relationship between the fact table and

dimensions. The dimensional modeling should handle the historical changes in the order

price and promotion of products. Explain and justify your design and the type of slowly

changing dimension being used for handling the required historical changes.

(25 marks)

5. Explain how the star schema can be used to perform the analysis requested by the

business. Use sample SQL statements to illustrate your answer. (10 marks)

6. Discuss how to successfully plan and manage the data warehouse project for the online

supermarket. (15 marks)