1 / 34

Chapter 2

Chapter 2. The Relational Model 1: Introduction, QBE, and Relational Algebra. Objectives. Describe the relational model Understand Query-by-Example (QBE) Use Criteria in QBE Create Calculated Columns in QBE Calculate Statistics in QBE. Objectives (con’t.). Sort data in QBE

kale
Download Presentation

Chapter 2

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 2 The Relational Model 1: Introduction, QBE, and Relational Algebra Concepts of Database Management, 4th Edition, Pratt & Adamski

  2. Objectives • Describe the relational model • Understand Query-by-Example (QBE) • Use Criteria in QBE • Create Calculated Columns in QBE • Calculate Statistics in QBE Concepts of Database Management, 4th Edition, Pratt & Adamski

  3. Objectives (con’t.) • Sort data in QBE • Join Tables in QBE • Update data using QBE • Understand relational algebra Concepts of Database Management, 4th Edition, Pratt & Adamski

  4. Relational Databases • Collection of tables • Each entity in own table • Attributes are fields (columns) in table • Relationships are common columns in two or more tables • Order of rows and columns is immaterial • Repeating groups are not permitted • Entries with repeating groups are unnormalized Concepts of Database Management, 4th Edition, Pratt & Adamski

  5. Relations • Two dimensional table in which: • Entries are single-valued • Each column (field or attribute) has a distinct name • All values in a column represent the same attribute • Order of columns is immaterial • Each row (record or tuple) is distinct • Order of rows is immaterial Concepts of Database Management, 4th Edition, Pratt & Adamski

  6. Query-by-Example (QBE) • Query • Questions represented in a way the DBMS can recognize and process • QBE • Visual approach to writing queries • Used in MS-Access Concepts of Database Management, 4th Edition, Pratt & Adamski

  7. Simple Queries Figure 2.3 Concepts of Database Management, 4th Edition, Pratt & Adamski

  8. Simple Queries (con’t.) Figure 2.4 Concepts of Database Management, 4th Edition, Pratt & Adamski

  9. Query that Includes All Fields Figures 2.5 – 2.6 Concepts of Database Management, 4th Edition, Pratt & Adamski

  10. Query with Simple Criteria Figures 2.7 – 2.8 Concepts of Database Management, 4th Edition, Pratt & Adamski

  11. Query Using AND Criteria Figures 2.9 – 2.10 Concepts of Database Management, 4th Edition, Pratt & Adamski

  12. Query Using OR Criteria Figures 2.11 – 2.12 Concepts of Database Management, 4th Edition, Pratt & Adamski

  13. Query Using Two Conditions on a Single Field Figures 2.13 – 2.14 Concepts of Database Management, 4th Edition, Pratt & Adamski

  14. Query Using Computed Field Figures 2.15 – 2.16 Concepts of Database Management, 4th Edition, Pratt & Adamski

  15. Query to Count Records Figures 2.17 – 2.18 Concepts of Database Management, 4th Edition, Pratt & Adamski

  16. Query to Calculate an Average Figures 2.19 – 2.20 Concepts of Database Management, 4th Edition, Pratt & Adamski

  17. Query to Sort Records Figures 2.23 – 2.24 Concepts of Database Management, 4th Edition, Pratt & Adamski

  18. Query to Sort on Multiple Keys Figure 2.27 Concepts of Database Management, 4th Edition, Pratt & Adamski

  19. Query to Sort on Multiple Keys (con’t.) Figure 2.28 Concepts of Database Management, 4th Edition, Pratt & Adamski

  20. Query to Join Tables Figure 2.29 Concepts of Database Management, 4th Edition, Pratt & Adamski

  21. Query to Join Tables (con’t.) Figure 2.30 Concepts of Database Management, 4th Edition, Pratt & Adamski

  22. Query to Restrict Records in a Join Figures 2.31 – 2.32 Concepts of Database Management, 4th Edition, Pratt & Adamski

  23. Update Query Figure 2.35 Concepts of Database Management, 4th Edition, Pratt & Adamski

  24. Delete Query Figure 2.36 Concepts of Database Management, 4th Edition, Pratt & Adamski

  25. Make-Table Query Figure 2.37 Concepts of Database Management, 4th Edition, Pratt & Adamski

  26. Make-Table Query (con’t.) Figure 2.39 Concepts of Database Management, 4th Edition, Pratt & Adamski

  27. Relational Algebra • Theoretical way of manipulating a relational database to produce new tables • Major commands • SELECT • Retrieves certain rows • PROJECT • Include certain columns • JOIN • Pull data from more than one table Concepts of Database Management, 4th Edition, Pratt & Adamski

  28. Customer & Sales Rep Tables Figure 2.40 Concepts of Database Management, 4th Edition, Pratt & Adamski

  29. Join of Customer and Sales Rep Figure 2.41 Concepts of Database Management, 4th Edition, Pratt & Adamski

  30. Outer Join of Customer and Sales Rep Figure 2.42 Concepts of Database Management, 4th Edition, Pratt & Adamski

  31. Normal Set Operations • Union of two tables • Result contains all rows that are in either the first table, the second table, or both • Intersection of two tables • Result contains all rows common to both • Difference of tables • Result is the set of rows in one table but not the other Concepts of Database Management, 4th Edition, Pratt & Adamski

  32. Normal Set Operations (con’t.) • Product of two tables • Result contains Cartesian product • Obtained by concatenating every row in first table with every row in second table • Division Process • Result contains quotient Concepts of Database Management, 4th Edition, Pratt & Adamski

  33. Product of Two Tables Figure 2.43 Concepts of Database Management, 4th Edition, Pratt & Adamski

  34. Dividing One Table by Another Figure 2.44 Concepts of Database Management, 4th Edition, Pratt & Adamski

More Related