ITM 450 – Week Three Assignment
Enter Your Name Here:
4
Instructions
Before submitting, enter your name into the header and cover page. Save the file with your last
name in the filename in place of the word TEMPLATE.
This assignment is taken from the text, chapter 13 Problems 1 through 10 (pages 639-645). It is
designed to teach various database concepts such as query formation, fact tables and star
schemas, and use logs.
Use this template to submit your answers. Deliverables are this template and modified versions
of the databases and spreadsheet embedded below. Zip all necessary files together for the final
submission.
Double click each Icon to open the embedded file. Or extract these files from
Ch13_Assignment_Tools.zip.
Ch13_P1.mdbCh13_P3.mdbCh13_P4.mdbCh13-SaleCo-DW.m
db
Ch13_P2.xls
P. 1 P. 2 P. 3 P. 4 P. 5 P. 6 P. 7 P. 8 P. 9 P. 10
Assign
Total
50 8 8 8 8 3 3 3 3 3 3
Studen
t
Total
Grading Rubric < 1 >
Note sections are in light blue boxes. These contain important information for you to read.
Questions and exercises are in bold italics.
Your answers are to be input into the green boxes.
Continued on the next page
1 ASYNC note: this grading rubric to be aligned with a Bb style rubric, see page 22.
ITM 450 – Week Three Assignment
Enter Your Name Here:
5
Problem 1
The university computer lab’s director keeps track of lab usage, as measured by the number of
students using the lab. This function is important for budgeting purposes. The computer lab
director assigns you the task of developing a data warehouse to keep track of the lab usage
statistics. The main requirements for this database are to:
• Show the total number of users by different time periods.
• Show usage numbers by time period, by major, and by student classification.
• Compare usage for different majors and different semesters.
Use the Ch13_P1.mdb database, which includes the following tables:
• USELOG contains the student lab access data.
• STUDENT is a dimension table that contains student data.
Given the three preceding requirements, and using the Ch13_P1.mdb data, complete the
following problems:
a. Define the main facts to be analyzed. (Hint: These facts become the source for the
design of the fact table.)
Delete this text and enter your answer here
b. Define and describe the appropriate dimensions. (Hint: These dimensions become the
source for the design of the dimension tables.)
Delete this text and enter your answer here
Continued on the next page
ITM 450 – Week Three Assignment
Enter Your Name Here:
6
c. Draw the lab usage star schema, using the fact and dimension structures you defined
in Problems 1a and 1b. Paste a screenshot below.
Delete this text and paste your screenshot here
d. Define the attributes for each of the dimensions in Problem 1b.
Delete this text and enter your answer here
Continued on the next page
ITM 450 – Week Three Assignment
Enter Your Name Here:
7
e. Recommend the appropriate attribute hierarchies.
Delete this text and enter your answer here
f. Implement your data warehouse design, using the star schema you created in Problem
1c and the attributes you defined in Problem 1d.
g. Create the reports that will meet the requirements listed in this problem’s
introduction.
End of Problem 1. Continue for Problem 2.
ITM 450 – Week Three Assignment
Enter Your Name Here:
8
Problem 2
Victoria Ephanor manages a small product distribution company. Because the business is
growing fast, she recognizes that it is time to manage the vast information pool to help guide
the accelerating growth. Ephanor, who is familiar with spreadsheet software, currently employs
a sales force of four people. She asks you to develop a data warehouse application prototype
that will enable her to study sales figures by year, region, salesperson, and product. (This
prototype will be used as the basis for a future data warehouse database.)
Using the data supplied in the Ch13_P2.xls file, complete the following seven problems:
a. Identify the appropriate fact table components.
Delete this text and enter your answer here
b. Identify the appropriate dimension tables.
Delete this text and enter your answer here
Continued on the next page.
ITM 450 – Week Three Assignment
Enter Your Name Here:
9
c. Draw a star schema diagram for this data warehouse. Include a screenshot below.
Delete this text and paste your screenshot here
d. Identify the attributes for the dimension tables that will be required to solve this
problem.
Delete this text and enter your answer here
Continued on the next page.
ITM 450 – Week Three Assignment
Enter Your Name Here:
10
e. Using the embedded Excel Spreadsheet, generate a pivot table to show the sales by
product and by region. The end user must be able to specify the display of sales for any
given year. The sample output is shown in the first pivot table in Figure 1 below.
Figure 1 – Pivot Table Example
f. Using the above problem as your base, add a second pivot table (see Figure 1) to show
the sales by salesperson and by region. The end user must be able to specify sales for a
given year or for all years, and for a given product or for all products.
Continued on the next page.
ITM 450 – Week Three Assignment
Enter Your Name Here:
11
g. Create a 3D bar graph to show sales by salesperson, by product, and by region. (See
the sample output in Figure 2).
Figure 2 – 3D Bar Graph Showing the Relationships Among Agent, Product, and Region
End of Problem 2. Continue to Problem 3.
ITM 450 – Week Three Assignment
Enter Your Name Here:
12
Problem 3
David Suker, the inventory manager for a marketing research company, wants to study the use
of supplies within the different company departments. Suker has heard that his friend, Victoria
Ephanor, has developed a spreadsheet-based data warehouse model that she uses to analyze
sales data (see Problem 2). Suker is interested in developing a data warehouse model like
Ephanor’s so he can analyze orders by department and by product. He will use Microsoft Access
as the data warehouse DBMS and Microsoft Excel as the analysis tool.
a. Develop the order star schema.
b. Identify the appropriate dimension attributes (enter them below).
Delete this text and enter your answer here
c. Identify the attribute hierarchies required to support the model (enter them below).
Delete this text and enter your answer here
Continued on the next page.
ITM 450 – Week Three Assignment
Enter Your Name Here:
13
d. Develop a crosstab report in Microsoft Access, using a 3D bar graph to show orders by
product and by department. (The sample output is shown in Figure 3)
Figure 3 – Crosstab Report: Orders by Product and Department
End of Problem 3. Continue to Problem 4.
ITM 450 – Week Three Assignment
Enter Your Name Here:
14
Problem 4
ROBCOR, whose sample data is contained in the database named Ch13_P4.mdb, provides “on-
demand” aviation charters using a mix of different aircraft and aircraft types. Because ROBCOR
has grown rapidly, its owner has hired you as its first database manager. The company’s
database, developed by an outside consulting team, is already in place to help manage all
company operations. Your first critical assignment is to develop a decision support system to
analyze the charter data. (Review the company’s operations in Problems 24–31 of Chapter 3,
The Relational Database Model.) The charter operations manager wants to be able to analyze
charter data such as cost, hours flown, fuel used, and revenue. She also wants to be able to drill
down by pilot, type of airplane, and time periods.
Given those requirements, complete the following:
a. Create a star schema for the charter data.
b. Define the dimensions and attributes for the charter operation’s star schema. (Enter
them below)
Delete this text and enter your answer here
Continued on the next page.
ITM 450 – Week Three Assignment
Enter Your Name Here:
15
c. Define the necessary attribute hierarchies. (Enter them below)
Delete this text and enter your answer here
d. Implement the data warehouse design using the design components you developed in
the first three parts of Problem 4.
e. Generate the reports to illustrate that your data warehouse meets the specified
information requirements.
End of Problem 4. Continue to Problem 5.
ITM 450 – Week Three Assignment
Enter Your Name Here:
16
Use the data in the embedded Ch13-SaleCo-DW database to solve Problems 5 – 10.
(Hint: Use the ROLLUP command.)
Problem 5
What is the SQL command to list the total sales by customer and by product, with subtotals by
customer and a grand total for all product sales? Figure 4 shows the abbreviated results of the
query.
Figure 4 – Problem 5 Abbreviated Result
Delete this text and enter your answer here
End of Problem 5. Continue to Problem 6.
ITM 450 – Week Three Assignment
Enter Your Name Here:
17
Problem 6
What is the SQL command to list the total sales by customer, month, and product, with
subtotals by customer and by month and a grand total for all product sales? Figure 5 shows
the abbreviated results of the query.
Figure 5 – Problem 6 Abbreviated Result
Delete this text and enter your answer here
End of Problem 6. Continue to Problem 7.
ITM 450 – Week Three Assignment
Enter Your Name Here:
18
Problem 7
What is the SQL command to list the total sales by region and customer, with Subtotals by
region and a grand total for all sales? Figure 6 shows the result of the query.
Figure 6 – Problem 7 Result
Delete this text and enter your answer here
End of Problem 7. Continue to Problem 8.
ITM 450 – Week Three Assignment
Enter Your Name Here:
19
Problem 8
What is the SQL command to list the total sales by month and product category, with
subtotals by month and a grand total for all sales? Figure 7 shows the result of the query.
Figure 7 – Problem 8 Result
Delete this text and enter your answer here
End of Problem 8. Continue to Problem 9.
ITM 450 – Week Three Assignment
Enter Your Name Here:
20
Problem 9
What is the SQL command to list the number of product sales (number of rows) and total
sales by month, with subtotals by month and a grand total for all sales? Figure 8 shows the
result of the query.
Figure 8 – Problem 9 Result
Delete this text and enter your answer here
End of Problem 9. Continue to Problem 10.
ITM 450 – Week Three Assignment
Enter Your Name Here:
21
Problem 10
What is the SQL command to list the number of product sales (number of rows) and total
sales by month and product category, with subtotals by month and product category and a
grand total for all sales? Figure 9 shows the result of the query.
Figure 9 – Problem 10 Result
Delete this text and enter your answer here
End of Week Three Assignment. See next page for the rubric.
ITM 450 – Week Three Assignment
Enter Your Name Here:
22
RUBRIC
ITM 450 – Week Three Assignment
Enter Your Name Here: