chapter 2 l.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 2 PowerPoint Presentation
play fullscreen
1 / 34
Download Presentation

Chapter 2 - PowerPoint PPT Presentation

kale
494 Views
Download Presentation

Chapter 2

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