type
Post
Created date
Sep 21, 2022 06:19 AM
category
Data Science
tags
Data Warehousing
status
Published
Language
English
From
School
summary
slug
password
Author
Jason Ching Yuen Siu
Priority
Featured
Featured
Cover
Origin
Type
URL
Youtube
Youtube
icon
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
A Private Taxi Company
- Author:Jason Siu
- URL:https://jason-siu.com/article/88e36d4b-a792-40cf-a58a-0f3b439680a9
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts