Transcript chapter02

Concepts of Database
Management, Fifth Edition
Chapter 2:
The Relational Model 1:
Introduction, QBE, and
Relational Algebra
2
Objectives
 Describe
the relational model
 Understand
 Use
Query-by-Example (QBE)
Criteria in QBE
 Create
Calculated Columns in QBE
 Calculate
Statistics in QBE
Concepts of Database Management, 5th Edition
2
2
Objectives (con’t.)
 Sort
data in QBE
 Join
Tables in QBE
 Update
data using QBE
 Understand
relational algebra
Concepts of Database Management, 5th Edition
3
Relational Databases
 Collection
2
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
 Entries
groups are not permitted
with repeating groups are unnormalized
Concepts of Database Management, 5th Edition
4
Premiere Products Sample Data
Concepts of Database Management, 5th Edition
2
5
Premiere Products Sample Data
Concepts of Database Management, 5th Edition
2
6
Relations
 Two
2
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
7
2
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
8
2
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
key – the column or collection of columns
that uniquely identifies a given row in a table
 Primary
Concepts of Database Management, 5th Edition
9
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, 5th Edition
10
Figure 2.3: An Example of Simple Queries
Concepts of Database Management, 5th Edition
2
11
Figure 2.4: Simple Queries (con’t.)
Concepts of Database Management, 5th Edition
2
12
2
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
13
2
Figures 2.7-2.8:
Query with
Simple Criteria
Concepts of Database Management, 5th Edition
14
2
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
15
2
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
16
Figures 2.9-2-10: Query Using AND Criteria
Concepts of Database Management, 5th Edition
2
17
Figures 2.11 – 2.12: Query Using OR Criteria
Concepts of Database Management, 5th Edition
2
18
2
Computed Fields
 You
can include calculated fields that are not in
the database in queries
field (calculated field) – a field that is
the result of a calculation using one or more
existing fields
 Computed
 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
19
Figures 2.15-2.16: Query Using Computed Field
Concepts of Database Management, 5th Edition
2
20
2
Calculating Statistics
 Built-in statistics (called aggregate functions in
Access) include:

Count

Sum



Avg (average)
Max (largest value)
Min (smallest value)
Concepts of Database Management, 5th Edition

StDev (standard
deviation)

Var (variance)

First

Last
21
2
Figures 2.19-2.20:
Query to Calculate
an Average
Concepts of Database Management, 5th Edition
22
2
Grouping
– creating groups of records that share
some common characteristic
 Grouping
 Functions
can be used in combination with
grouping where statistics are calculated for
groups of records
Concepts of Database Management, 5th Edition
23
2
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
 Sorting
Concepts of Database Management, 5th Edition
24
2
Figures 2.23-2.24:
Query to Sort Records
Concepts of Database Management, 5th Edition
25
Figure 2.27: Query to Sort on Multiple Keys
Concepts of Database Management, 5th Edition
2
26
2
Figure 2.28:
Query to Sort on Multiple Keys (con’t.)
Concepts of Database Management, 5th Edition
27
2
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
28
Figure 2.29: Query to Join Table
Concepts of Database Management, 5th Edition
2
29
Figure 2.30: Query to Join Tables (con’t.)
Concepts of Database Management, 5th Edition
2
30
2
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
31
Figure 2.35: Update Query
Concepts of Database Management, 5th Edition
2
32
2
Delete Query
 Queries
can be used to delete one or more
records at a time
query – deletes all the records satisfying
the criteria entered into the query
 Delete
 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
33
Figure 2.36: Delete Query
Concepts of Database Management, 5th Edition
2
34
2
Make-Table Query
 Queries
can be used to create a new table in
either the current database or in a separate
database
query – creates a new table using
the query results
 Make-table
 The
data added to the new table is separate from
the original table in which it appears
Concepts of Database Management, 5th Edition
35
Figure 2.37: Make-Table Query
Concepts of Database Management, 5th Edition
2
36
Figure 2.39: Make-Table Query (con’t.)
Concepts of Database Management, 5th Edition
2
37
Relational Algebra
2
 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
38
2
Major commands
 SELECT
– retrieves certain rows from existing
table
– causes only certain columns to be
included in the new table
 PROJECT
 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
39
2
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
join – joins the records from each original
table that is common to both tables
 Natural
join – joins the records from each original
table including the records not common to both
tables
 Outer
Concepts of Database Management, 5th Edition
40
2
Normal Set Operations
 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
41
2
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
42
2
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
43
2
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
44
2
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
45