Transcript Database & Data Warehouse Assignments

Database & Data Warehouse
Assignments
BCIS 4660 – Dr. Nick Evangelopoulos
Spring 2012
BCIS 4660 – Spring 2012
Exercise #1
• Due: Jan 26 for full credit, Feb 2 for 50% credit
• Points: 10 points
• Pratt & Adamski; End of Chapter 1; pp 29-30
• Assignments must have your name on the 1st page.
• Use ACCESS 2010 to write the appropriate query,
form, report or filter to answer even-numbered
questions for Premiere Products (2-10) and Henry
Books (2-12).
• Turn-in printouts of the datasheet, filtereddatasheet, or report and the related query (as
appropriate), which shows the answer to the query
posed. Screen prints may be used, if necessary.
• NOTE: For this assignment, use of Access is
optional. You can just do it manually using Excel.
Objectives
• Introduce Premiere Products, the
company that is used as the basis for
many of the examples throughout the text
• Introduce Henry Books, the company that
is used in the case that runs throughout
the text
Premiere Products
• Distributor of appliances, housewares, and
sporting goods
• Uses spreadsheet software to maintain
important data
• Recent growth has made the spreadsheet
approach problematic:
– Redundancy
– Difficulty accessing data
– Limited security
– Size limitations
Introduction to Henry Books
Database Case
• Book store chain operated by Ray Henry
• Henry decided to use database to gather
and store information on:
–
–
–
–
Branches
Publishers
Authors
Books
Access Relationship Diagram [ERD]
Premiere Products
Customer Table
“Intersection” Table
1:N
N:1
1:M
1:N
Product/Part
Table
M:N
Order Transaction
SalesRep Table
Concepts of Database Management, 7th Edition, Pratt & Adamski
Using DBMSs in Different Ways
Figures 1.8 and 1.9
Database Server: Raw Data Extraction
Stored Applications
“Apps”
Concepts of Database Management, 7th Edition, Pratt & Adamski
Building a Database
• Database design determines the structure of
a database
• Design entered into DBMS during
Construction/Implementation (sequence #)
1. Tables or Relations (Data)
– Application Programs (Apps)—e.g., ACCESS
2.
3.
4.
5.
Queries or Views (Schema or Subset of Database)!!
Forms (Input/Output Data Application)
Reports (Report Application)
Switchboards (Menu Application)
Concepts of Database Management, 7th Edition, Pratt & Adamski
Part and Order Forms
Figures 1.10 and 1.11
Concepts of Database Management, 7th Edition, Pratt & Adamski
Parts Report
Figure 1.12
Concepts of Database Management, 7th Edition, Pratt & Adamski
Problem 1-1
Helpful Hints:
1.1 List the names of all customers that have a credit limit less than $10,000.
Datasheet view
Design/QBE View
Concepts of Database Management, 7th Edition, Pratt & Adamski
Exercises #1
• Due: Sep 6 (Tu)
• Points: 10 points
• Pratt & Adamski; End of Chapter 1; pp 29-30
• YOU MUST USE 2010 ACCESS TO COMPLETE EXERCISE
• Assignments must have cover sheet below.
• Use ACCESS to write the appropriate query, form, report or
filter to answer ALL even-numbered questions for Premiere
Products and Henry Books.
• Turn-in printouts of the datasheet, filtered-datasheet, or report
and the related query (as appropriate), which shows the
answer to the query posed along with the QBE grid that
produced the result. Screen prints may be used, if necessary.
Concepts of Database Management, 7th Edition, Pratt & Adamski
BCIS 4660 Decision Systems Design
Homework Assignment #1 Score sheet
(10 points)
Concepts of Database Management, 7th Edition, Pratt & Adamski
Previewing and Printing the Contents
of a Table
Help Button
Office Button
Shutter Bar
Ruler Bar
Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition
14
Using Simple Query Wizard to
Create a Query
Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition
15
Example of A Homework
Solution Sample Set (3 parts)
SQL View (alt-PrintScrn)
•
•
•
SELECT Customer.CustomerName,
OrderLine.OrderNum, OrderLine.PartNum,
Part.Description, OrderLine.NumOrdered,
OrderLine.QuotedPrice, Part.Warehouse,
Rep.RepNum
FROM Rep INNER JOIN (Part INNER JOIN
((Customer INNER JOIN Orders ON
Customer.CustomerNum =
Orders.CustomerNum) INNER JOIN
OrderLine ON Orders.OrderNum =
OrderLine.OrderNum) ON Part.PartNum =
OrderLine.PartNum) ON Rep.RepNum =
Customer.RepNum
ORDER BY Customer.CustomerName,
OrderLine.OrderNum, OrderLine.PartNum;
Datasheet View (print)
QBE/Design View (alt-PrintScrn)
Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition
16
Pause: Q&A
Access …
SQL View
Or QBE View
Alt-PrintScrn

Ctrl-V (paste)
Concepts of Database Management, 7th Edition, Pratt & Adamski
PowerPoint or
Paint (to edit)
Exercise #2
• Due: Feb 2 for full credit, Feb 9 for 50% credit
• Points: 10 points
• Pratt & Adamski; pp 68-70 (end of chapter 2)
• Assignments must have cover sheet with your name.
• Use ACCESS to write the appropriate query, form, report or filter
to answer QBE questions 2, 4, 6, 8, 10, 12 for Premiere Products
and QBE questions 14, 16, 18 & 19 for Henry Books.
• NOTE: Make a copy of the Premiere Products and Henry Books
database from the end of Chapter 1 BEFORE starting this
assignment.
• Turn-in printouts of the datasheet, or report and the related
query (as appropriate), which shows the answer to the query
posed along with the QBE grid that produced the result. Screen
prints may be used, if necessary. Show only the attributes each
question asks for. Do not show any extra attributes!
Exercise #3
• Due: Feb 9 for full credit, Feb 16 for 50% credit
• Points: 20 points
• Pratt & Adamski (End of Chapters 3 & 4)
• Assignments must have cover sheet with your name.
• Chapter 3 [pp 115-116]: Use ACCESS to write the appropriate SQL
query to answer questions 2, 4, 6, 8, 14, 15 for Premiere Products
and questions 5, 7, 12, 19 for Henry Books.
• Chapter 4 pp 150-152]: Use ACCESS to write the appropriate SQL
query to answer questions 2, 4, 7 for Premiere Products and
questions 2, 4, 10 for Henry Books (1 bonus question included).
• NOTE: USE the copy of the Premiere Products and Henry Books
database from the end of Chapter 1 [the version you had BEFORE
starting Exercise 2].
• Turn-in printouts of the datasheet, or report and the related query
(as appropriate), which shows the answer [datasheet view, table
view, etc.] to the query posed along with the SQL query that
produced the result. Screen prints may be used, if necessary.
Exercise #4
• Due: Feb 23 for full credit, Mar 1 for 50% credit
• Points: 20 points
• Pratt & Adamski (End of Chapters 5 & 6)
• Assignments must have cover sheet with your name.
• Chapter 5 [pp 174-175]: Answer questions 1, 2, 3 for
Premiere Products and questions 2, 4 for Henry
Books. Omit 4NF, answer the questions for 3NF.
• Chapter 6 pp [221-222]: Answer questions 2, 4 for
Premiere Products and questions 1, 2, 3 for Henry
Books. Turn in ERD and copies of new tables and
attributes. (Alternatively, use DBDL.)
Exercise #5
•
•
•
Due: Mar 15
Points: 10 points
Pratt & Adamski: Premiere Products [PP] and Henry Books [HB] Databases
•
•
Use ACCESS, PP, and HB databases.
Redesign both PP and HB databases as they would be for a data
warehouse as described in Adamson & Venerable [Chapters 1 & 2] and
Marakas [Chapters 1 & 2]. Use the Star diagram as the basis for their
design. Be sure to include a meaningful Time dimension table. You are
not asked to implement the DBs, i.e., the tables may be completely empty.
Turn-in printouts of the REVISED relationship diagrams, i.e., the Star
Diagrams, for both databases (deliverable 1)
Turn in a list of 5 managerial questions for each case, that could be
addressed with the help of the star schema (deliverable 2)
On a separate page(s), clearly identify for each database: Fact tables,
dimension tables, primary keys, foreign keys, alternate keys, etc. Use
relational notation from Pratt & Adamski (deliverable 3).
Indicate the normal form [1NF, 2NF, 3NF, etc.] of each table (deliv. 4)
NOTE: Use the ORIGINAL copy of the Premiere Products and Henry
Books databases for this assignment.
•
•
•
•
•
Exercise #6
•Due: Mar 29
•Points: 20 points
• Pratt & Adamski: Premiere Products and Henry Books [PP & HB].
Use the original DB files. Three deliverables (d1-d3) for each DB.
•1. Generate the SQL Code needed to create the fact
tables for both the PP and HB databases. Turn in the SQL
statements (d1) BEFORE the make-table conversion.
•2. Execute your SQL code and populate the fact tables.
Turn in screen shots with the fact tables (d2) contents
AFTER the make-table conversion.
•3. Generate the ACCESS ERDs for both PP and HB.
Arrange the entities to minimize cross-over lines. Adjust
the primary and foreign keys to conform with our original
database models. Turn in screen shots of both star
schemas (d3) in Access relationship view.
Exercise #7 [TEAM PROJECT] p. 1 of 3
Due: April 5
Points: 30 points
Pratt & Adamski: Premier Products Star Diagram
Assignments must have cover sheet (deliverable 1), table of contents (d2), and indicate the NAMES
OF ALL TEAM MEMBERS and the TEAM NUMBER. Assignments must be typed using a word
processor (Word, WordPerfect) and have a professional look. Use of ACCESS is REQUIRED for this
assignment.
In this exercise you play the role of a regional DB Administrator in charge of your
assigned territory (see p. 3). You are asked to type original transactional data and
prepare them for a DW Load, to be performed in exercise #8.
•
Part 7.1: Generate data for each of the following tables:
– Load Original Premier Product data into your Original Transactional Database. For
your assigned territory: Create GOOD data for the following tables with at least as
many new records as indicated: Rep (2 records); Customer (20 records); Part
(10 records); Orders (60, all of them in 2013); OrderLine (120, at least one line
for every order)
– Make sure that data types agree with case requirements. Dates must fall between
1/1/2013 and 12/31/2013. Change any dates in the case to conform to this
standard (for example, if a date currently is 2010, change it to 2013.
– Turn in a printout of each table with data (d3). Note: Include the 5 tables listed
above, not the fact table.
Exercise #7 [TEAM PROJECT] p. 2 of 3
• Part 7.2: Revise your Design for the Premiere Products Data
Warehouse using a Star diagram (more than one Star OK but not
necessary). Include a meaningful Time dimension table.
– Create a NEW Access database .mdb file [PPex7TeamX.mdb], where
X is your team #.
– Make sure all field types in the Dimension Tables and in the
OrderDetail Fact Table agree with original case specifications, modified
to accommodate new data field type. This is critical.
– Use the TIME table that is posted on the course Website
(TimeTable2013).
– Turn in ACCESS ERD (deliverable 4) of revised Star Diagram
– Turn in Relation Listing (d5). On separate page, clearly identify: All
relations, i.e., Fact tables, dimension tables, primary keys, foreign keys,
alternate keys, etc. Use relational notation from Pratt & Adamski.
E.g.:
• CUSTOMER[CustomerNum, CustomerName, …, RepNum]
– Turn in .mdb file (d6) (CD, email attachment, bring flash disk to the
instructor, etc.)
Ex #7:Territory Assignments p. 3 of 3
Team
Territory
RepNum
PartNo
WH
Class
OrdNo
CustNo State
1 NW
90 HWC001
C
HW
10000
100 WA
2 NW
80 HWD001
D
HW
20000
200 OR
3 SW
70 HWF001
F
HW
30000
300 AZ
4 SW
60 SGA001
A
SG
40000
400 NM
5 MW
50 SGB001
B
SG
50000
500 MO
6 SE
40 APG001
G
AP
60000
600 FL
7 NE
30 APH001
H
AP
70000
700 NY
8 NE
20 APK001
K
AP
80000
800 NY
9 SE
10 SGI001
I
SG
90000
900 ME
10 MW
100 SGJ001
J
SG
100000
1000 CO
11 S
110 APE001
E
AP
110000
1100 AL
12 S
120 HWL001
L
HW
120000
1200 LA
13 S
130 HWR001
M
HW
130000
1300 TX
Exercise #8 [TEAM PROJECT] p. 1 of 2
Due: April 19
Points: 40 points
Pratt & Adamski: Premier Products Data Warehouse
Assignments must have cover sheet (deliverable 1), table of contents (d2), and indicate
the NAMES OF ALL TEAM MEMBERS and the TEAM NUMBER. Assignments must be
typed using a word processor (Word, WordPerfect) and have a professional look. Use of
ACCESS is REQUIRED for this assignment.
•
Perform a Data Warehouse Load for Premier Products. Use your Star
Diagram from Exercise #7 and populate the tables in the model with the
new data available at the course Web site, combining all territories.
•
Part 8.1 – Data Definitions: Use your Star Diagram from Exercise #7 and
other necessary data definition documentation.
– Turn in a List of Relations (deliv. 3). On one (1) separate page, clearly list your fact
and dimension tables using the simplified relational notation. E.g.,
• EMPLOYEE [Eno, LastName, FirstName, … deptno, …]
– Be sure to include the TERRITORY data and add this field for every record in the
ORDER_DETAIL table.
– Turn in a printed ACCESS ERD of your Star Diagram (deliverable 4). May be
from previous homework.
– Turn in a printed copy of Documenter output (d5) of Table definitions [HINT:
Tools/Analyze/Documenter/Tables] . Please sample the results and restrict this
printout to 1 page.
Exercise #8 [TEAM PROJECT] p. 2 of 2
• Part 8.2: Load data and print out your Fact and Dimension Tables.
– Load the data provided by the instructor into your tables.
• Note: Dates of all transactions should fall between 1/1/2004 and 12/31/2006
– Turn in Transformation Maps (d6): Carefully document any data transfer and data
cleansing activities that you performed. Note any data problems you encountered.
– Create the necessary DETAILED transaction Fact Table(s) that join all the dimension
tables. You may use QBE or your SQL query from Exercise #6, appropriately modified.
– Turn in printouts of the contents of your fact and dimension tables (d7). Import into
your word-processor Excel-type tables. Adjust their size and give them a professional
look. Use landscape orientation, when necessary. Make sure the data is in some
reasonable order, such as ID or date, whichever is most appropriate. Print only the first
page of each table.
• Part 8.3: Generate the following REPORTS:
– Use the your knowledge of SQL to create the following COMPUTED views and generate
the corresponding ACCESS REPORTS:
• Total Sales by Month (list each month, the corresponding year 2013, and the total sales)
• Total Sales by Customer by Month (order by Customer and Month)
• Total Sales by Rep by Month (list each month with its year 2013, then all reps within each month
with their total sales next to them)
• Total Sales by Month by Rep (list reps, then all months within year 2013)
• Total Sales by Part by Month by Territory (territory, months within territory, parts within month)
• Total Sales by Territory by Month by Part (list part, months within part, all territories with sales)
– Turn in printouts of the 6 reports listed above (d8).
– Turn in .mdb file (d9) with STAR models, data, views, reports (floppy, CD, e-mail
attachment, bring flash disk to the instructor’s office).
• Use this naming convention: PPex8TeamX.mdb, where “X” is your team#
Exercise #9 [FINAL TEAM PROJECT REPORT] p. 1 of 2
Due: Apr 26 (presentation), May 3 (report)
Points: 40 pts for the report (HW9), 15 pts for the Presentation (HW10)
Pratt & Adamski: Premiere Products
•
•
Assignments must be in a folder or binder, have cover sheet (d1) indicating
the names of all team members and the team # and a table of contents(d2).
Assignments must be typed using a word processor (Word, WordPerfect) and
have a professional look. Use of ACCESS is REQUIRED for this assignment.
Turn in PPTeamX.ppt file (d12) and individual peer evaluation forms (d13)
•
Part 9.1: Project Overview [Brief narrative for each section]
–
–
–
–
Detailed Table of Contents (d2)
Executive Summary (d3)
Overview of Premiere Products (d4)
Transactional Database Design
• Include original ERD (d5)
• Relation Listing of Transaction Database (d6)
– Data Warehouse Design
• Include ERD of the Star Schema (d7)
• Relation Listing of DW tables (d8)
• Table/Document/Analyzer print of SAMPLE tables in Data Warehouse (d9)
– Data Transformation Mapping Procedures used (d10)
Exercise #9 [FINAL TEAM PROJECT REPORT] p. 2 of 2
• Part 9.2: Data Warehouse Queries & Reports
– Create the following COMPUTED queries/views and generate the
corresponding ACCESS REPORT object [Turn in printouts of all
reports (d11), the beginning of the report (first half-page) is sufficient]:
• Total Sales by Month (list each month, the corresponding year 2013, and the total
sales)
• Total Sales by Customer by Month (order by Customer and Month)
• Total Sales by Rep by Month (list each month with its year 2013, then all reps
within each month with their total sales next to them)
• Total Sales by Month by Rep (list reps, then all months within year 2013)
• Total Sales by Part by Month by Territory (territory, months within territory, parts
within month)
• Total Sales by Territory by Month by Part (list part, months within part, all
territories with sales)
• Part 9.3: Data Warehouse Menu System (optional)
– Turn in a Hierarchy Diagram showing Menu System (optional)
– Turn in Screen Prints of your Switchboards in Menu System
(optional)
• Part 9.4: PowerPoint Presentation File [15 points]
•
– Use 10-12 slides. Turn in your PowerPoint file (d12)
Part 9.5: Peer Evaluation Forms (d13)
• TOTAL: 13 Deliverables!!! Count them and make sure you didn’t miss any.
Exercise #10 [FINAL TEAM PROJECT PRESENTATION]
Due: Apr 26 (presentation),
Points: 15 pts for the Presentation (HW10)
Pratt & Adamski: Premiere Products
• This presentation is based on HW8 or HW9 (remember that, in Spring
2012, HW9 is a simple repackaging of previous HW assignments)
• Prepare a PowerPoint (or other professional presentation format), ideally
consisting of 10-12 slides. Recommended content:
–
–
–
–
–
–
–
Cover slide, reporting Team # and names of all participating team members
Overview of your presentation (agenda)
Overview of Premiere Products
ERD of the transactional database
List of questions asked by managers, requiring the design of a Data Warehouse
Star Schema
Three report examples (present the results and relate them back to the “questions
asked by managers”
– Conclusions commenting on the usefulness of a Data Warehouse solution for
Premiere Products
– A prompt for questions from your audience
• At least two team members must present
• Be prepared to compete for a most entertaining presentation bonus
and/or a most technically complete presentation bonus. Best
presentations will be voted by your classmates
• Turn in your PP slides (as a .ppt, .pptx, .pdf, or equivalent file)