MSITM6301 Business Data Warehousing

Question # 00852161 Posted By: wildcraft Updated on: 03/21/2024 12:56 AM Due on: 03/21/2024
Subject Business Topic General Business Tutorials:
Question
Dot Image

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

Dot Image
Tutorials for this Question
  1. Tutorial # 00847640 Posted By: wildcraft Posted on: 03/21/2024 12:57 AM
    Puchased By: 2
    Tutorial Preview
    The solution of MSITM6301 Business Data Warehousing...
    Attachments
    MSITM6301_Business_Data_Warehousing.ZIP (18.96 KB)

Great! We have found the solution of this question!

Whatsapp Lisa