1 / 44

Chapter 1

Chapter 1. Introduction to Database Management Spring 2010. Problem 1-2. Datasheet view. Design/QBE View. Objectives. Introduce Premiere Products , the company that is used as the basis for many of the examples throughout the text Introduce basic database terminology

anahid
Download Presentation

Chapter 1

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. Chapter 1 Introduction to Database Management Spring 2010 Concepts of Database Management, 6th Edition, Pratt & Adamski

  2. Problem 1-2 Datasheet view Design/QBE View Concepts of Database Management, 6th Edition, Pratt & Adamski

  3. Objectives • Introduce Premiere Products, the company that is used as the basis for many of the examples throughout the text • Introduce basic database terminology • Describe database management systems • Explain the advantages and disadvantages of database processing • Introduce Henry Books, the company that is used in the case that runs throughout the text Concepts of Database Management, 6th Edition, Pratt & Adamski

  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 Concepts of Database Management, 6th Edition, Pratt & Adamski

  5. Premiere Products Required Information • Sales Reps • Sales rep number, last name, first name, address, total commission, commission rate • Customers • Customer number, name, address, current balance, credit limit, customer sales rep • Parts Inventory • Part number, description, number units on hand, item class, warehouse number, unit price Concepts of Database Management, 6th Edition, Pratt & Adamski

  6. Premiere Products Sample OrderFigure 1.2 Concepts of Database Management, 6th Edition, Pratt & Adamski

  7. Premiere Products Customer Order • Order • Order number, order date, customer number • Order line • Order number, part number, number units ordered, unit price • Overall order total • Not stored since it can be calculated Concepts of Database Management, 6th Edition, Pratt & Adamski

  8. Database Background • Database • Structure to store information about multiple types of entities, attributes, and relationships • Entity • Person, place, thing, or event • Premiere Products has sales reps, customers, orders, and parts • Attribute • Property of an entity • Customer has name, street, city, etc. Concepts of Database Management, 6th Edition, Pratt & Adamski

  9. Entities and Attributes Figure 1.3 Concepts of Database Management, 6th Edition, Pratt & Adamski

  10. Database Background (cont.) • Relationship • Association between entities • Rep is related to many customers • Customer is related to a single rep • Data file • File used to store data • Computer counterpart to ordinary paper file Concepts of Database Management, 6th Edition, Pratt & Adamski

  11. One-to-Many (1:N) Relationship Figure 1.4 1:N Concepts of Database Management, 6th Edition, Pratt & Adamski

  12. One-to-Many (1:N) Relationship Figure 1.4 Primary Key 1:N Foreign Key Concepts of Database Management, 6th Edition, Pratt & Adamski

  13. Rep and Customer Tables Figure 1.5 Concepts of Database Management, 6th Edition, Pratt & Adamski

  14. & Part Table Orders and OrderLine Tables Figure 1.5 (con’t.) 2 lines -[ Concepts of Database Management, 6th Edition, Pratt & Adamski

  15. Part Table Figure 1.5 (con’t.) Concepts of Database Management, 6th Edition, Pratt & Adamski

  16. Database Management Systems [DBMS] • Program(s) through which users interact with database • Popular DBMSs include Access, Oracle, DB2, SQL Server • Premiere Products decides to use Access • Relational database  TABLES! • Peter Chen invented ERDs in 1970 • Entity-Relationship Diagrams Dr. Chen (LSU) & Dr. Becker share a moment at AMCIS Conference NYC August, 2004 Concepts of Database Management, 6th Edition, Pratt & Adamski

  17. 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, 6th Edition, Pratt & Adamski

  18. Using DBMSs in Different Ways Figures 1.7 and 1.8 Database Server: Raw Data Extraction Stored Applications “Apps” Concepts of Database Management, 6th Edition, Pratt & Adamski

  19. 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, 6th Edition, Pratt & Adamski

  20. Part and Order Forms Figures 1.9 and 1.10 Concepts of Database Management, 6th Edition, Pratt & Adamski

  21. Parts Report Figure 1.11 Concepts of Database Management, 6th Edition, Pratt & Adamski

  22. Exercises #1 • Due: Jan 28 • Points: 10 points • Pratt & Adamski; End of Chapter 1; pp 27-28 • Assignments must have cover sheet, table of contents, index tabs. Use 3-hole punch notebook (1/2” or smaller). Put your name on the spine of the notebook. • 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, 6th Edition, Pratt & Adamski

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

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

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

  26. Homework Solution Sample Set 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

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

  28. Switchboards Figure 1.A Level- 1 Concepts of Database Management, 6th Edition, Pratt & Adamski

  29. Switchboards(con’t.) Figure 1.B Level-2 Concepts of Database Management, 6th Edition, Pratt & Adamski

  30. Switchboard Hierarchical Design Concepts of Database Management, 6th Edition, Pratt & Adamski

  31. Advantages of Database Processing Figure 1.14 Concepts of Database Management, 6th Edition, Pratt & Adamski

  32. Disadvantagesof Database Processing Figure 1.15 • Larger file size • Overhead:Raw data ratio  10:1!! • Increased complexity & costs initially • Additional Training requirements • Greater impact of failure • Due to increased integration • More difficult recovery (depends) • Decreased security due to “ease of access” Concepts of Database Management, 6th Edition, Pratt & Adamski

  33. 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 Concepts of Database Management, 6th Edition, Pratt & Adamski

  34. Access Relationship DiagramHenry Books Book: Entry Object “Intersection” Table Association Table Author Publisher • KEY • Master File • Transaction File • Intersection or • Association File Branch Location Concepts of Database Management, 6th Edition, Pratt & Adamski

  35. Access Relationship DiagramHenry Books Book: Entry Object “Intersection” Table Association Table Author Publisher • KEY • Fact Table • Dimension Table • Intersection or • Association Table Branch Location Concepts of Database Management, 6th Edition, Pratt & Adamski

  36. Sample Branch Data Figure 1.16 Concepts of Database Management, 6th Edition, Pratt & Adamski

  37. Sample Publisher Data Figure 1.16 (con’t.) Concepts of Database Management, 6th Edition, Pratt & Adamski

  38. Sample Author Data Figure 1.17 Concepts of Database Management, 6th Edition, Pratt & Adamski

  39. Sample Book Data Figure 1.18 Concepts of Database Management, 6th Edition, Pratt & Adamski

  40. Wrote Table Relates Authors to Books Figure 1.19 Concepts of Database Management, 6th Edition, Pratt & Adamski

  41. Inventory Table Relates Branches to Books Figure 1.19 (con’t.) Concepts of Database Management, 6th Edition, Pratt & Adamski

  42. Concepts of Database Management, 6th Edition, Pratt & Adamski

  43. Exercises #1 • Due: Jan 28 • Points: 10 points • Pratt & Adamski; End of Chapter 1; pp 27-28 • Assignments must have cover sheet, table of contents, index tabs. Use 3-hole punch notebook (1/2” or smaller). Put your name on the spine of the notebook. • 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, 6th Edition, Pratt & Adamski

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

More Related