Transcript CH 8

Database Processing
Eighth Edition
Foundations of
Relational
Implementation
1
Chapter 8
David M. Kroenke
© 2002 by Prentice Hall
Review Relational Model
Terminology
• Relation is a two-dimensional table
• Attributes are single valued
• Each attribute belongs to a domain
– A domain is a physical and logical
description of permittable values
• No two rows are identical
• Order is unimportant
• The row is called a tuple
2
© 2002 by Prentice Hall
Logical Key versus
Physical Key
• The term ‘key’ is ambiguous
• During the design phase, ‘key’ is used to
describe candidate keys
– Design-phase keys are logical keys
• During the implementation phase, ‘key’ is
any column for which there is an index (this
allows Nulls and does not have to be
Unique)
– Implementation-phase keys are physical
keys
3
© 2002 by Prentice Hall
Data Definition Language
(DDL)
• In order to create the tables and
structures within a database, the DBMS
must provide (often using SQL) a data
definition language (DDL).
• The DDL is used to define (i.e., Create,
Drop, and Alter) everything in the
database…
– Tables
– Columns
– Indexes
– Users, etc.
4
© 2002 by Prentice Hall
Data Manipulation Language
(DML)
• When thinking about SQL, most people
think of the Data Manipulation Language
(DML) aspects of the language
• The DML allows users to insert, delete,
modify, and retrieve information
– Select
– Delete
– Insert
– Update
5
© 2002 by Prentice Hall
DML Alternatives
• A DBMS must provide at least one
DML. Several options exist…
– Query/Update language (e.g., SQL)
– Query-by-Example
– Query-by-Form
6
© 2002 by Prentice Hall
Query/Update Language
SELECT Name, Age
FROM PATIENT
WHERE Physician = ‘Levy’
7
© 2002 by Prentice Hall
Query-by-Example
8
© 2002 by Prentice Hall
Query by Form
9
© 2002 by Prentice Hall
Stored Procedures
• Some DML tasks are performed on a
routine or regular basis
• The DML procedure may be saved in the
DBMS and recalled at a later date
• Note… the DML-code is saved, not the
results of the query/update. As such,
when the procedure is retrieved the
results will be refreshed.
10
© 2002 by Prentice Hall
Application Program
Interface (API)
• Some applications provide an
Application Program Interface (API)
• The API is a DML typically used by
programmers
• To retrieve or update data contained
within the application, a programmer
submits requests to the application’s
API.
11
© 2002 by Prentice Hall
Relational Algebra
• Relational algebra defines a set of
operators that may work on relations.
• Recall that relations are simply data
sets. As such, relational algebra
deals with set theory.
• The operators in relational algebra
are very similar to traditional algebra
except that they apply to sets.
12
© 2002 by Prentice Hall
Relational Algebra
Operators
• Relational algebra provides several
operators:
– Union
– Difference
– Intersection
– Product
– Projection
– Selection
– Join
13
© 2002 by Prentice Hall
Union Operator
• The union operator adds tuples from
one relation to another relation
• A union operation will result in
combined relation
• This is similar to the logical operator
‘OR’
14
© 2002 by Prentice Hall
Union Operator
JUNIOR and
HONOR-STUDENT
relations and their
union:
(a) Example of JUNIOR
relation
(b) Example HONORSTUDENT relation
(c) Union of JUNIOR
and HONORSTUDENT relations
15
© 2002 by Prentice Hall
Difference Operator
• The difference operator produces a
third relation that contains the
tuples that appear in the first
relation, but not the second
• This is similar to a subtraction
16
© 2002 by Prentice Hall
Difference Operator
JUNIOR
relation
HONORSTUDENT
relation
17
JUNIOR
minus
HONORSTUDENT
relation
© 2002 by Prentice Hall
Intersection Operator
• An intersection operation will produce
a third relation that contains the
tuples that are common to the
relations involved.
• This is similar to the logical operator
‘AND’
18
© 2002 by Prentice Hall
Intersection Operator
JUNIOR
relation
HONORSTUDENT
relation
Intersection
of JUNIOR
and HONORSTUDENT
relations
19
© 2002 by Prentice Hall
Product Operator
• A product operator is a concatenation of
every tuple in one relation with every tuple
in a second relation
• The resulting relation will have n x m
tuples, where…
n = the number of tuples in the first
relation and
m = the number of tuples in the second
relation
• This is similar to multiplication
20
© 2002 by Prentice Hall
Projection Operator
• A projection operation produces a
second relation that is a subset of
the first.
• The subset is in terms of columns, not
tuples
• The resulting relation will contain a
limited number of columns. However,
every tuple will be listed.
21
© 2002 by Prentice Hall
Selection Operator
• The selection operator is similar to
the projection operator. It produces
a second relation that is a subset of
the first.
• However, the selection operator
produces a subset of tuples, not
columns.
• The resulting relation contains all
columns, but only contains a portion
of the tuples.
22
© 2002 by Prentice Hall
Join Operator
• The join operator is a combination of
the product, selection, and projection
operators. There are several
variations of the join operator…
– Equijoin
– Natural join
– Outer join
• Left outer join
• Right outer join
23
© 2002 by Prentice Hall
Data for Join Examples
SID
Name
Major
GradeLevel
123
Jones
History
JR
158
Parks
Math
GR
271
Smith
History
JR
105
Anderson
Management
SN
StudentNumber
ClassName
PositionNumber
123
H350
1
105
BA490
3
123
B490
7
24
© 2002 by Prentice Hall
Join Examples
Equijoin
Natural
Join
Left Outer
Join
25
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
1. What are the names of all students?
STUDENT [Name]
2. What are the student numbers of all
students enrolled in a class?
ENROLLMENT [StudentNumber]
26
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
3. What are the student numbers of all
students not enrolled in a class?
STUDENT [SID] – ENROLLMENT
[StudentNumber]
4. What are the numbers of students
enrolled in the class ‘BD445’?
ENROLLMENT WHERE ClassName
= ‘BD445’[StudentNumber]
27
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
5. What are the names of the students
enrolled in class ‘BD445’?
STUDENT JOIN (SID =
StudentNumber) ENROLLMENT
WHERE ClassName =
‘BD445’[STUDENT.Name]
28
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
6. What are the names and meeting
times of ‘PARKS’ classes?
STUDENT WHERE Name = ‘PARKS’
JOIN (SID=StudentNumber)
ENROLLMENT JOIN (ClassName
= Name) CLASS
[CLASS.Name, Time]
29
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
7. What are the grade levels and
meeting rooms of all students,
including students not enrolled in a
class?
STUDENT LEFT OUTER JOIN
(SID = StudentNumber)
ENROLLMENT JOIN (ClassName
= Name) CLASS [GradeLevel,
Room]
30
© 2002 by Prentice Hall
Summary of Relational
Algebra Operators
31
© 2002 by Prentice Hall
Database Processing
Eighth Edition
Foundations of
Relational
Implementation
32
Chapter 8
David M. Kroenke
© 2002 by Prentice Hall