1 / 45

Concepts of Database Management, Fifth Edition

Concepts of Database Management, Fifth Edition. 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.

leal
Download Presentation

Concepts of Database Management, Fifth Edition

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. Concepts of Database Management, Fifth Edition Chapter 2: The Relational Model 1: Introduction, QBE, and Relational Algebra

  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, 5th Edition

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

  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, 5th Edition

  5. Premiere Products Sample Data Concepts of Database Management, 5th Edition

  6. Premiere Products Sample Data Concepts of Database Management, 5th Edition

  7. 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, 5th Edition

  8. An Unnormalized Relation • Relational Database – a collection of relations • Unnormalized relation • When a structure satisfies all the properties of a relation except for the first item • Some of the entries contain repeating groups and thus are not single-valued Concepts of Database Management, 5th Edition

  9. Database Structure Representation • Write the name of the table followed by a list of all columns within parentheses • Each table should appear on its own line • Use the notation, tablename.columnname, with duplicate column names within a database • Using this combination qualifies column names • Primary key – the column or collection of columns that uniquely identifies a given row in a table Concepts of Database Management, 5th Edition

  10. 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, 5th Edition

  11. Figure 2.3: An Example of Simple Queries Concepts of Database Management, 5th Edition

  12. Figure 2.4: Simple Queries (con’t.) Concepts of Database Management, 5th Edition

  13. Simple Criteria • Criteria – conditions that data must satisfy • Criterion – a single condition • To display specific query results, enter the condition in the appropriate column in the design grid Concepts of Database Management, 5th Edition

  14. Figures 2.7-2.8: Query with Simple Criteria Concepts of Database Management, 5th Edition

  15. Comparison (Relational) Operators • Finds something other than an exact match • Comparison operators are: • = (equal to) • > (greater than) • < (less than) • >= (greater than or equal to) • <= (less than or equal to) • NOT (not equal to) Concepts of Database Management, 5th Edition

  16. Compound Criteria • Combines comparison operators • Many languages use AND or OR between the separate criteria • In an AND criterion, both criteria must be true • In an OR criterion, the overall criterion is true if either of the individual criteria is true Concepts of Database Management, 5th Edition

  17. Figures 2.9-2-10: Query Using AND Criteria Concepts of Database Management, 5th Edition

  18. Figures 2.11 – 2.12: Query Using OR Criteria Concepts of Database Management, 5th Edition

  19. Computed Fields • You can include calculated fields that are not in the database in queries • Computed field (calculated field) – a field that is the result of a calculation using one or more existing fields • In a query that uses computed fields, if a field name contains spaces you must enclose it in square brackets Concepts of Database Management, 5th Edition

  20. Figures 2.15-2.16: Query Using Computed Field Concepts of Database Management, 5th Edition

  21. Count Sum Avg (average) Max (largest value) Min (smallest value) StDev (standard deviation) Var (variance) First Last Calculating Statistics • Built-in statistics (called aggregate functions in Access) include: Concepts of Database Management, 5th Edition

  22. Figures 2.19-2.20: Query to Calculate an Average Concepts of Database Management, 5th Edition

  23. Grouping • Grouping – creating groups of records that share some common characteristic • Functions can be used in combination with grouping where statistics are calculated for groups of records Concepts of Database Management, 5th Edition

  24. Sorting • Sorting – listing records in a query’s results in a particular way • It is possible to sort using more than one field • Sort Key – the field on which records are sorted • Major sort key (primary sort key) – the more important field • Minor sort key (secondary sort key) – the less important field • Major sort key is on the left of the grid and the minor sort key is on the right Concepts of Database Management, 5th Edition

  25. Figures 2.23-2.24: Query to Sort Records Concepts of Database Management, 5th Edition

  26. Figure 2.27: Query to Sort on Multiple Keys Concepts of Database Management, 5th Edition

  27. Figure 2.28: Query to Sort on Multiple Keys (con’t.) Concepts of Database Management, 5th Edition

  28. Joining Tables • Queries to select data from more than one table • Join the tables based on matching fields in corresponding columns • In an Access query, a join line between matching fields in the two tables will be created indicating how the tables are related • When joining multiple tables • Add all the tables involved to the upper pane • Add the query results grid in the desired order Concepts of Database Management, 5th Edition

  29. Figure 2.29: Query to Join Table Concepts of Database Management, 5th Edition

  30. Figure 2.30: Query to Join Tables (con’t.) Concepts of Database Management, 5th Edition

  31. Update Query • Update query – a query that changes data • Makes a specified change to all records satisfying the criteria in the query • In Access, a new row is created that is used to indicate how to update the data selected by the query Concepts of Database Management, 5th Edition

  32. Figure 2.35: Update Query Concepts of Database Management, 5th Edition

  33. Delete Query • Queries can be used to delete one or more records at a time • Delete query – deletes all the records satisfying the criteria entered into the query • When you change the Query type to Delete Query, an extra row, called a Delete row, is added to the design grid Concepts of Database Management, 5th Edition

  34. Figure 2.36: Delete Query Concepts of Database Management, 5th Edition

  35. Make-Table Query • Queries can be used to create a new table in either the current database or in a separate database • Make-table query – creates a new table using the query results • The data added to the new table is separate from the original table in which it appears Concepts of Database Management, 5th Edition

  36. Figure 2.37: Make-Table Query Concepts of Database Management, 5th Edition

  37. Figure 2.39: Make-Table Query (con’t.) Concepts of Database Management, 5th Edition

  38. Relational Algebra • Theoretical way of manipulating a relational database to produce new tables • Relational algebra includes operations that act on existing tables to produce new tables • Retrieving data using relational algebra involves issuing relational algebra commands to operate on existing tables to form a new table containing the desired information Concepts of Database Management, 5th Edition

  39. Major commands • SELECT – retrieves certain rows from existing table • PROJECT – causes only certain columns to be included in the new table • JOIN • Allows extraction of data from more than one table • Rows in new table will be the concatenation (combination) of a row from the first table and a row from the second Concepts of Database Management, 5th Edition

  40. The Join Command Continued • If there is a row in one table that does not match any row in the other table, that row will not appear in the result of the join • PROJECT command can be used to restrict the output from the join • Natural join – joins the records from each original table that is common to both tables • Outer join – joins the records from each original table including the records not common to both tables Concepts of Database Management, 5th Edition

  41. Normal SetOperations • Union of two tables • Result contains all rows that are in either the first table, the second table, or both • Union compatible – tables are union compatible if they have the same number of columns and their corresponding columns represent the same type of data • Intersection of two tables • Result contains all rows common to both • Use the INTERSECT command Concepts of Database Management, 5th Edition

  42. Normal Set Operations (con’t.) • Difference of tables • Result is the set of rows in one table but not the other • Performed by using the SUBTRACT command • Product of two tables • Mathematically called the Cartesian product • Obtained by concatenating every row in first table with every row in second table Concepts of Database Management, 5th Edition

  43. Normal Set Operations (con’t.) • Division Process • Best illustrated by considering the division of a table with two columns by a table with a single column • Result contains quotient Concepts of Database Management, 5th Edition

  44. Summary • Relation: two-dimensional table in which the entries are single-valued • Relational database: collection of relations • Field name: qualified by preceding it with the table name and a period • Table’s primary key: field or fields that uniquely identify a given row within the table • Query-By-Example (QBE): visual tool for manipulating relational databases • Created by completing on-screen forms Concepts of Database Management, 5th Edition

  45. Summary (con’t.) • Queries can be used to select specific records based on certain criteria • A make-table query creates a new table using the query results • Relational Algebra - a theoretical way of manipulating a relational database to produce new tables • The normal set operations include the union, intersection, difference and product of two tables and the division process Concepts of Database Management, 5th Edition

More Related