Database & Data Warehouse AssignmentsBCIS 4660 – Spring 2012 Exercise #1ObjectivesPremiere ProductsIntroduction to Henry Books Database CaseAccess Relationship Diagram [ERD] Premiere ProductsSlide 7Building a DatabaseSlide 9Slide 10Problem 1-1 Helpful Hints:Exercises #1BCIS 4660 Decision Systems Design Homework Assignment #1 Score sheet (10 points)Previewing and Printing the Contents of a TableUsing Simple Query Wizard to Create a QueryExample of A Homework Solution Sample Set (3 parts)Pause: Q&AExercise #2Exercise #3Database & Data WarehouseAssignmentsBCIS 4660 – Dr. Nick EvangelopoulosSpring 2012BCIS 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.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 textPremiere 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 limitationsIntroduction 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–BooksConcepts of Database Management, 7th Edition, Pratt & AdamskiAccess Relationship Diagram [ERD]Premiere ProductsOrder TransactionCustomer TableProduct/PartTableSalesRep Table“Intersection” TableM:N1:M1:N1:NN:1Concepts of Database Management, 7th Edition, Pratt & AdamskiUsing DBMSs in Different WaysFigures 1.8 and 1.9Database Server: Raw Data ExtractionStored Applications“Apps”Concepts of Database Management, 7th Edition, Pratt & AdamskiBuilding 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., ACCESS2. Queries or Views (Schema or Subset of Database)!!3. Forms (Input/Output Data Application)4. Reports (Report Application)5. Switchboards (Menu Application)Concepts of Database Management, 7th Edition, Pratt & AdamskiPart and Order FormsFigures 1.10 and 1.11Concepts of Database Management, 7th Edition, Pratt & AdamskiParts ReportFigure 1.12Problem 1-1Helpful Hints:Datasheet view Design/QBE ViewConcepts of Database Management, 7th Edition, Pratt & Adamski1.1 List the names of all customers that have a credit limit less than $10,000.Concepts of Database Management, 7th Edition, Pratt & AdamskiExercises #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 & AdamskiBCIS 4660 Decision Systems Design Homework Assignment #1 Score sheet(10 points)Previewing and Printing the Contents of a TableMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition 14Office ButtonHelp ButtonShutter BarRuler BarUsing Simple Query Wizard to Create a QueryMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition15Example 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;Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition16QBE/Design View (alt-PrintScrn)Datasheet View (print)Pause: Q&AConcepts of Database Management, 7th Edition, Pratt & AdamskiAccess …SQL ViewOr QBE ViewAlt-PrintScrnCtrl-V (paste)PowerPoint orPaint (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