BIA B450F辅导、SQL编程设计辅导
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)