Transcript Homework #3
Relational Algebra
The mathematical foundation for SQL
Basic operators
select
project
union
intersect
set difference
cross product (i.e. Cartesian product)
rename
Select Operation
Notation:
p(r)
Select only the records satisfying the condition p
The condition p is composed of
one or more terms connected by : (and), (or), (not)
each term is one of the following two forms:
<column> op <column>
<column> op <constant>
where op is one of: =, , >, . <.
Question 1
• Relation r
A
B
C
D
1
7
5
7
12
3
23 10
What is the result of the following operation?
C>D A= B (r)
Project Operation
Notation:
A1, A2, …, Ak (r)
where A1, A2 are column names and r is a relation name.
The result is defined as a new relation of k columns obtained by
erasing the columns that are not listed
Duplicate rows removed from result
Question 2
Relation r:
A
B
C
10
1
20
1
30
1
40
2
What is the result of
A (r) ?
Union Operation
Notation: r s
Meaning: The set composed of records appearing in r or s or
both.
r s is well defined
if and only if they are compatible in the following sense:
1. r, s have the same number of columns
2. The corresponding columns have same types
(e.g., i-th column of r deals with the same type of values
as does the i-th column of s)
Question 3
Relations r, s:
A
B
A
B
1
1
3
3
1
r
What is the result of r s?
s
Set Difference Operation
Notation r – s
Meaning: The set composed of records appearing in r but not in
s.
r s is well defined if and only if they are compatible
in the following sense:
1. r, s have the same number of columns
2. The corresponding columns have same types
(e.g., i-th column of r deals with the same type of values
as does the i-th column of s)
Question 4
Relations r, s:
A
B
A
B
1
1
3
3
1
r
What is the result of r – s?
What is the result of r – (r – s)?
s
Set-Intersection Operation
Notation: r s
Meaning: The set composed of records appearing both in r or s.
r s is well defined
if and only if they are compatible in the following sense:
1. r, s have the same number of columns
2. The corresponding columns have same types
(e.g., i-th column
of r deals with the same type of values
as does the i-th column of s)
Questions 5
Relation r, s:
A
B
1
2
1
r
A
B
2
3
s
What is the result of r s?
What is the result of r – (r- s)?
What is the result of s – (s- r)?
Are the three expressions above always equivalent to one
another no matter what r and s are?
Cross-Product Operation
Notation r x s
Defined as:
r x s = {t q | t is a record in r and q is a record ins}
In other words, r x s
Is the set of all possible pairs of records with one from r and
the other one from s to form a pair.
Question 6
Relations r, s:
A
B
C
D
E
1
2
10
10
20
10
X
X
Y
Y
r
s
What is the result of r x s?
Rename Operation
Allows us to name a resulting relation and its columns.
x (E)
returns the relation E under the name X
If a relation E has arity n, then
x (A1, A2, …, An) (E)
returns the result of expression E under the name X, and with the
columns renamed to A1, A2, …., An.
Composing Expressions in Relational
Algebra
A basic expression in the relational algebra consists of :
Constants or A table as a relation in the database
With any relational-algebra expressions E1 and E2 ,you can
recursively compose more complex relational-algebra
expressions as follow:
p (E1), P is a logic condition on columns in E1
s(E1), S is a list consisting of some of the columns in E1
E1 x E2
x (E1), x is the new name for the result of E1
E1 E2
E1 E2
E1 - E2
Relation: Students
columns
sid
sname
state
01
02
03
04
05
Jones
Smith
Curry
Linda
Fugitive
CA
CA
OR
WA
RI
Students
16
records
Relation : Courses
courses
columns
cid
cname
required
CS105
CS440
CS320
CS480
C++
Databases
IC Design
AI Research
Yes
No
Yes
No
17
records
Relation: Enrollment
enrollment
columns
cid
sid
grade
CS105
CS320
CS440
CS105
CS440
CS105
CS320
CS440
CS480
CS320
CS440
CS480
01
01
01
02
02
03
03
03
03
04
04
04
5
5
5
3
4
4
1
3
3
4
4
5
18
records
Question 7
What are the results of the following relational-algebra expressions
with respect to the three relations: courses, enrollment, and
students
1. sid (grade<2 (enrollment))
2. cname (required=“Yes” (courses) )
3. sid ( enrollment.cid = courses.cid and required=“Yes” (
enrollment x courses ))
Question 8
What are the results of the following relational-algebra expressions
with respect to the three relation instances: courses, enrollment,
and students
1. sid (enrollment.grade<2 and enrollment.cid = courses.cid and
enrollment.required=“Yes”
(enrollment x courses ) )
2. sid ( (enrollment.cid = courses.cid (
grade<2 (enrollment) x required=“Yes” (courses) ) ))
3. sid ( grade<2 (enrollment)x required=“Yes” (courses) )
Question 9
Assume that a student fails in a course if the grade is less than 2.
Identify what expressions (if any) in questions 7 and 8 can provide
the following information (given the relation instances: courses,
enrollment, and students)
:
1.
The list of sids of students who fail in some required courses
2.
The list of sids of students who fail in some courses
3.
The list of sids of students who take some required courses
Question 10
What are the results of the following relational-algebra expressions with respect to
the three relation instances: courses, enrollment, and students
1. courses.cname ( enrollment.sid =“04”
( enrollment.cid = courses.cid(enrollment x courses)))
2.
cname(courses) –
courses.cname ( enrollment.sid =“04”
( enrollment.cid = courses.cid(enrollment x courses)))
3.
cname(required=“Yes” (courses)) –
courses.cname (
enrollment.sid =“04” and courses.required=“Yes”
( enrollment.cid = courses.cid(enrollment x courses)))
Question 11
Assume that a student fails in a course if the grade is less than 2.
Identify what expressions (if any) in question 10 can provide the
following information (given the relation instances: courses,
enrollment, and students)
:
1.
The list of courses taken by the student with the sid ’04’
2.
The list of courses that the student with the sid ’04’ hasn’t taken yet
3.
The list of required courses that the student with the sid ’04’ hasn’t
taken yet