1 / 18

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

tess
Download Presentation

Database & Data Warehouse Assignments

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database & Data WarehouseAssignments BCIS 4660 – Dr. Nick Evangelopoulos Spring 2012

  2. BCIS 4660 – Spring 2012Exercise #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, filtered-datasheet, 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.

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

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

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

  6. Access Relationship Diagram [ERD]Premiere Products Customer Table “Intersection” Table 1:N N:1 1:M Product/Part Table 1:N M:N Order Transaction SalesRep Table Concepts of Database Management, 7th Edition, Pratt & Adamski

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

  8. Building a Database • Database design determines the structure of a database • Design entered into DBMS during Construction/Implementation (sequence #) • Tables or Relations (Data) • Application Programs (Apps)—e.g., ACCESS • 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

  9. Part and Order Forms Figures 1.10 and 1.11 Concepts of Database Management, 7th Edition, Pratt & Adamski

  10. Parts Report Figure 1.12 Concepts of Database Management, 7th Edition, Pratt & Adamski

  11. Problem 1-1Helpful Hints: Datasheet view Design/QBE View 1.1 List the names of all customers that have a credit limit less than $10,000. Concepts of Database Management, 7th Edition, Pratt & Adamski

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

  13. BCIS 4660 Decision Systems Design Homework Assignment #1 Score sheet(10 points) Concepts of Database Management, 7th Edition, Pratt & Adamski

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

  15. Using Simple Query Wizard to Create a Query Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition

  16. Example of A Homework Solution Sample Set (3 parts) SQL View (alt-PrintScrn) Datasheet View (print) • 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; QBE/Design View (alt-PrintScrn) Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition

  17. Pause: Q&A Access … SQL View Or QBE View Alt-PrintScrn  Ctrl-V (paste) PowerPoint or Paint (to edit) Concepts of Database Management, 7th Edition, Pratt & Adamski

  18. 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!

More Related