MSITM6301 Business Data Warehousing
MSITM-6301 Business Data Warehousing
Homework 5 – Data Modeling
Exercise 1
Consider the following scenario involving the City Police Department.
The City Police Department wants to create an analytical database to analyze its ticket revenue. The two available data sources, Source 1 and Source 2, are described below.
The data warehouse must enable an analysis of ticket revenues by:
· date, including:
· full date
· day of week
· day of month
· month
· quarter
· year
· officer, including:
· officer ID
· officer name
· officer rank
· payer of the ticket, including:
· payer DLN
· payer name
· payer gender
· payer birth year
· vehicle, including:
· vehicle LPN
· vehicle make
· vehicle model
· vehicle year
· vehicle owner DLN
· vehicle owner name
· vehicle owner gender
· vehicle owner birth year
· ticket type, including:
· ticket category (driving or parking)
· ticket violation
· ticket fee
Figure 1: Source 1 The City Police Department Ticketed Violations Database
Figure 2: Source 2 The DMV Vehicle tration Table
Figure 3:Ticket Revenue Database Tables
Figure 4: Ticket Revenue Populated DB Tables
Questions:
1. Create a dimensional model containing an aggregated fact table of the summary of daily revenue amount for each officer.
2. Populate the tables created in 2 with the data from the tables in Figure 4.
Exercise 2
Consider the following scenario involving Big Z Inc., an automotive products wholesaler.
Big Z Inc. wants to create the analytical database (data warehouse) to analyze its order quantities. The two available data sources, Figure 5: Source 1 The Big Z Inc. Orders Database and Figure 6: Source 2 The Big Z Inc Human Resources Department Table, are described below.
The data warehouse must enable an analysis of order quantities by:
· date, including:
· full date
· day of week
· day of month
· month
· quarter
· year
· time
· product, including:
· product ID
· product name
· product type
· product supplier name
· customer, including:
· customer ID
· customer name
· customer type
· customer zip
· depot, including:
· depot ID
· depot size
· depot zip
· order clerk, including:
· order clerk ID
· order clerk name
· order clerk title
· order clerk education level
· order clerk year of hire
Figure 5: Source 1 The Big Z Inc. Orders Database
Figure 6: Source 2 The Big Z Inc Human Resources Department Table
Questions:
1. Based on the sources and requirements listed above, create a dimensional model that will be used for the dimensionally modeled data warehouse for Big Z Inc.
Source: Juki?, N., Vrbsky, S., Nestorov, S., & Sharma, A. (n.d.). Database Systems. Retrieved from https://platform.virdocs.com/read/1582944/16/#/4/2[ch08]/44/4/14[fig0841]/2
image4.png
image5.png
image6.png
image1.png
image2.png
image3.png
-
Rating:
5/
Solution: MSITM6301 Business Data Warehousing