FIT3003 - W4 - Multi-fact star schema

type

Post

Created date

Sep 21, 2022 06:19 AM

What is the definition of “Subject-oriented” ?

A data warehouse has four basic features: (i) Integrated, (ii) Subject Oriented, (iii) Time Variant, and (iv) Non-volatile. A Subject-Oriented data warehouse means that one star schema focuses on one subject only.

A subject refers to a topic of analysis. For example, a star schema might be built to analyse property sales. If we want to have a data warehouse that focuses on property rentals, it has to be a separate star schema because one star schema focuses on one subject only. In this example, property sales is one subject, and property rentals is another subject. The first star schema focuses on property sales, whereas the second star schema focuses on property rentals. These two star schemas should not be combined because they focus on different subjects. The input operational database for these star schemas might be one operational database.

Summary

Because each Fact focuses on a subject or on a different granularity, a Star Schema with Multi-Fact or Multiple Star Schemas are needed.

Star schemas with different dimensions can be said to have different subjects.

When multiple star schemas have different subjects (or different dimensions), they cannot be merged into one star schema.

Case study: book

This case study is about a bookshop that has several stores and they sell books. The E/R diagram of an operational database is shown

The system stores information about books, including the authors, publishers, book categories, as well as the reviews that each book has received. The ”stars” attribute in the Review entity records the star rating for each review (e.g. 5 stars for excellent to 1 star for poor, etc). One book may receive many reviews. For simplicity, it is assumed, as also shown in the E/R diagram, that a book will only have one category.

The E/R diagram also includes entities related to sales of books, and the stores which sale the books. Each store has many sales transactions (i.e. the Sales entity), and each sales transaction may include several books (i.e. the SalesDetails entity). The Total Price attribute in the Sales Details entity is basically Quantity multiplied by the Unit Price, whereas Total Price in the Sales entity is the total price for each sales transaction

Solution

Implementation in SQL

What is the definition of “Subject-oriented” ?

Summary

Case study: book

A Private Taxi Company