DW data modeling

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 Registration 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