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.)