Transcript Chapter 8: Foundations of Relational Implementation

Database Processing
Chapter 8
Foundations of Relational
Implementation
David M. Kroenke
© 2000 Prentice Hall
Chapter 8
Review of Terminology
Relation “two-dimensional table”
•
•
•
•
•
entries are single value
attributes (columns) contain same type of data
each attribute has a domain (allowed values)
no two rows are identical, order is unimportant
tuple (rows)
Page 193
© 2000 Prentice Hall
Occurrence of a relation
PATIENT(Name, DateOfBirth, Gender,
AccountNumber, Physician)
Page 194
Figure 8-1
© 2000 Prentice Hall
Chapter 8
Relational Schema
“The relation structure plus constraints
on allowable data values”
Page 194
© 2000 Prentice Hall
Chapter 8
Key
“Group of one or more attributes that
uniquely identifies a tuple in a
relation”
– logical
– physical (index)
Page 194
© 2000 Prentice Hall
Chapter 8
Implementing a Relational
Database
1. Define the database structure to the
DBMS
2. Allocate media space
3. Create the database data
Page 195
© 2000 Prentice Hall
Chapter 8
Data Definition Language
“The language used to describe the
database structure”
–
–
–
–
–
tables
columns
indexes
constraints
security
Page 196
© 2000 Prentice Hall
Chapter 8
Relational Data Manipulation
Language
•
•
•
•
Relational Algebra
Relational Calculus
Transform-Oriented Languages
Query-by-Example; Query-by-Form
Page 198
© 2000 Prentice Hall
DML Interfaces
• Forms
Figure 8-8
• Query/Update Language Interface
SELECT Name, Age
FROM PATIENT
WHERE Physician = ‘Levy’
Page 200
© 2000 Prentice Hall
Chapter 8
DML Interfaces
• Stored Procedure Interface
DO BILLING FOR BDATE = ‘9/15/1999’
• Application Program Interface
COBOL
BASIC
Perl
C++
Page 201
© 2000 Prentice Hall
Relation Definitions
Page 204
Figure 8-11a
© 2000 Prentice Hall
Attribute Domains
Page 204
Figure 8-11b
© 2000 Prentice Hall
Domain Definitions
Page 204
Figure 8-11c
© 2000 Prentice Hall
Example Data
Figure 8-12
© 2000 Prentice Hall
Union
“adding tuples from one relation to
those of a second” A+B
Page 205
Figure 8-12
© 2000 Prentice Hall
Difference
“adding tuples that occur in the first
relation but not the second” A-B
Page 206
Figure 8-13
© 2000 Prentice Hall
Intersection
“contains tuples in both the first and
second”
Page 206
Figure 8-14
© 2000 Prentice Hall
Product
“selects specified attributes (columns)
from a relation”
Page 206
Figure 8-17
© 2000 Prentice Hall
Selection
“selects tuples (rows) from a relation”
Page 206
Figure 8-18
© 2000 Prentice Hall
Chapter 8
Join
“combination of the product, selection,
and (possibly) projection operations”
– equijoin
– natural join
– left outer join
Page 207
© 2000 Prentice Hall
Equijoin, Natural, Left Outer
Page 209
Figure 8-19
© 2000 Prentice Hall
Summary of Relational
Algebra Operators
Page 209
Figure 8-20
© 2000 Prentice Hall
Chapter 8
Expressing Queries
1. What are the names of all students?
STUDENT [Name]
Page 210
© 2000 Prentice Hall
Chapter 8
Expressing Queries
2. What are the student numbers of all
students enrolled in a class?
ENROLLMENT [StudentNumber]
Page 211
© 2000 Prentice Hall
Chapter 8
Expressing Queries
3. What are the student numbers of all
students not enrolled in a class?
STUDENT [SID] - ENROLLMENT
[StudentNumber]
Page 211
© 2000 Prentice Hall
Chapter 8
Expressing Queries
4. What are the numbers of students enrolled
in the class ‘BD445’?
ENROLLMENT WHERE ClassName =
‘BD445’[StudentNumber]
Page 211
© 2000 Prentice Hall
Chapter 8
Expressing Queries
5. What are the names of the students
enrolled in class ‘BD445’?
STUDENT JOIN (SID = StudentNumber)
ENROLLMENT WHERE ClassName =
‘BD445’[STUDENT.Name]
Page 211
© 2000 Prentice Hall
Chapter 8
Expressing Queries
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]
Page 212
© 2000 Prentice Hall
Chapter 8
Expressing Queries
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]
Page 212
© 2000 Prentice Hall