1 / 34

Chapter 2 - PowerPoint PPT 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