2
Chapter 2
The Relational Model 1:
Introduction, QBE, and
Relational Algebra
Concepts of Database Management, 4th Edition, Pratt & Adamski 1
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 2
2
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 3
2
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 4
2
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 5
2
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 6
2
Simple Queries
Figure 2.3
Concepts of Database Management, 4th Edition, Pratt & Adamski 7
2
Simple Queries (con’t.)
Figure 2.4
Concepts of Database Management, 4th Edition, Pratt & Adamski 8
2
Query that Includes All Fields
Figures 2.5 – 2.6
Concepts of Database Management, 4th Edition, Pratt & Adamski 9
2
Query with Simple Criteria
Figures 2.7 – 2.8
Concepts of Database Management, 4th Edition, Pratt & Adamski 10
2
Query Using AND Criteria
Figures 2.9 – 2.10
Concepts of Database Management, 4th Edition, Pratt & Adamski 11
2
Query Using OR Criteria
Figures 2.11 – 2.12
Concepts of Database Management, 4th Edition, Pratt & Adamski 12
2
Query Using Two
Conditions on a Single Field
Figures 2.13 – 2.14
Concepts of Database Management, 4th Edition, Pratt & Adamski 13
2
Query Using Computed Field
Figures 2.15 – 2.16
Concepts of Database Management, 4th Edition, Pratt & Adamski 14
2
Query to Count Records
Figures 2.17 – 2.18
Concepts of Database Management, 4th Edition, Pratt & Adamski 15
2
Query to Calculate an Average
Figures 2.19 – 2.20
Concepts of Database Management, 4th Edition, Pratt & Adamski 16
2
Query to Sort Records
Figures 2.23 – 2.24
Concepts of Database Management, 4th Edition, Pratt & Adamski 17
2
Query to Sort on Multiple Keys
Figure 2.27
Concepts of Database Management, 4th Edition, Pratt & Adamski 18
2
Query to Sort on Multiple Keys (con’t.)
Figure 2.28
Concepts of Database Management, 4th Edition, Pratt & Adamski 19
2
Query to Join Tables
Figure 2.29
Concepts of Database Management, 4th Edition, Pratt & Adamski 20
2
Query to Join Tables (con’t.)
Figure 2.30
Concepts of Database Management, 4th Edition, Pratt & Adamski 21
2
Query to Restrict Records in a Join
Figures 2.31 – 2.32
Concepts of Database Management, 4th Edition, Pratt & Adamski 22
2
Update Query
Figure 2.35
Concepts of Database Management, 4th Edition, Pratt & Adamski 23
2
Delete Query
Figure 2.36
Concepts of Database Management, 4th Edition, Pratt & Adamski 24
2
Make-Table Query
Figure 2.37
Concepts of Database Management, 4th Edition, Pratt & Adamski 25
2
Make-Table Query (con’t.)
Figure 2.39
Concepts of Database Management, 4th Edition, Pratt & Adamski 26
2
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 27
2
Customer & Sales Rep Tables
Figure 2.40
Concepts of Database Management, 4th Edition, Pratt & Adamski 28
2
Join of Customer and Sales Rep
Figure 2.41
Concepts of Database Management, 4th Edition, Pratt & Adamski 29
2
Outer Join of Customer and Sales Rep
Figure 2.42
Concepts of Database Management, 4th Edition, Pratt & Adamski 30
2
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 31
2
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 32
2
Product of Two Tables
Figure 2.43
Concepts of Database Management, 4th Edition, Pratt & Adamski 33
2
Dividing One Table by Another
Figure 2.44
Concepts of Database Management, 4th Edition, Pratt & Adamski 34
