Title: Relational algebra
1Relational algebra
2Relational algebra Notation
3Unary Operations
- Selection
- ? course Computing Students
- In SQL
- Select
- From Students
- Where course Computing
Projection ? stud, name Students In
SQL Select stud, name From Students
Selection Projection ? stud, name (? course
Computing Students) In SQL Select stud,
name From students Where course Computing
4Binary Operations/Joins
- Cartesian Product Students X Courses
- In SQL
- Select
- From Students, Courses
5Rename
- RENAME operator (?) Renames the input relation
and attributes with a new relation name
attributes specified. - ?S(B1, B2, , BN) (R)
- Example,
- TEMP ? ?NAME, MAJOR(STUDENT)
- ?STUD_INFO (FULL_NAME,M_DEPT ) ? TEMP
6Renaming
Example
- TEMP ? ? DNO5(EMPLOYEE)
- R(FIRSTNAME, LASTNAME, SALARY) ?
- ? FNAME, LNAME, SALARY (TEMP)
7Union and Set-Difference
- All of these operations take two input relations,
which must be union-compatible - Same number of fields.
- Corresponding fields have the same type.
8Set Operators
- Given two relations R1, R2 that are
union-compatible, we have that - R1 ? R2 returns the set of tuples that are in R1
or R2. UNION - R1 ? R2 returns the set of tuples that are both
in R1 and R2. INTERSECTION - R1 - R2 returns the set of tuples that are in R1,
but not in R2. SET DIFFERENCE
9Set Operators
- ? Name (FACULY) ? ? Name (STUDENT)
- ? Address (FACULY) ? ? Address (STUDENT)
- ? CrsCode (CLASS) - ? CrsCode (TRANSCRIPT)
10Union
S1
S2
11Set Difference
S1
S2 S1
S2
12Joins
- Three new join operators are introduced
- Left Outer Join (denoted as )
- Right Outer Join (denoted as )
- Full Outer Join (denoted as )
13Join
- Students ? ltstud 200gt Courses
- In SQL
- Select
- From Students, Courses
- Where stud 200
14Left Outer Join
- Left Outer Join A ltjoin conditiongt B
- ensures that all tuples in the in the relation A
are present in the result set. - The tuples in A without matching tuples in B are
filled with null values for Bs attributes
15Left Outer Join - Example
- Students Courses
- stud name course course name
- 100 Fred PH PH Pharmacy
- 200 Dave CM CM Computing
- 400 Peter EN CH Chemistry
- Students ltcourse coursegt Courses
- stud Students.name course course Courses.name
- 100 Fred PH PH Pharmacy
- 200 Dave CM CM Computing
- Peter EN NULL NULL
16Right Outer Join
- Right Outer Join A ltjoin conditiongt B
- Reverse of left outer join. Retrieves all tuples
of B and null values for attributes of A in
non-matching tuples of B
17Right Outer Join - Example
- Students Courses
- stud name course course name
- 100 Fred PH PH Pharmacy
- 200 Dave CM CM Computing
- 400 Peter EN CH Chemistry
- Students ltcourse coursegt
Courses - stud Students.name course course Courses.name
- 100 Fred PH PH Pharmacy
- 200 Dave CM CM Computing
- NULL NULL NULL CH Chemistry
18Combination of Unary and Join Operations
- Students Courses
- stud name address course course name
- 100 Fred Aberdeen PH PH Pharmacy
- 200 Dave Dundee CM CM Computing
- 300 Bob Aberdeen CM
- Show the names of students (from Aberdeen) and
the names - of their courses
- R1 Students ? ltcoursecoursegt Courses
- R2 ? ltaddressAberdeengt R1
- R3 ? ltStudents.name, Course.namegt R2
Students.name Courses.name Fred Pharmacy Bob Com
puting
19Full Outer Join
- Full Outer Join A ltjoin conditiongt B
- ensures that all tuples of A and B are present in
the result set
20Exercise 1
Example Customer
- Query 1 List customers whose cred_lim is greater
than 500. - Query 2 List customers whose cred_lim is greater
than 500 and lives in London.
21Answers
- Query 1 List customers whose cred_lim is greater
than 500. - ?(cred_lim gt 500)(customer)
- Query 2 List customers whose cred_lim is greater
than 500 and lives in London. - ?(cred_limgt500) AND (cityLondon) (customer)
22Exercise 2
Reserves
Sailors
Boats
1.Find names of sailors whove reserved boat
103 2.Find names of sailors whove reserved a
red boat 3.Find sailors whove reserved a red or
a green boat 4.Find sailors whove reserved a red
and a green boat 5. Find the names of sailors
whove reserved all boats
231.Find names of sailors whove reserved boat 103
242.Find names of sailors whove reserved a red boat
- Information about boat color only available in
Boats so need an extra join
253.Find sailors whove reserved a red or a green
boat
- Can identify all red or green boats, then find
sailors whove reserved one of these boats
264.Find sailors whove reserved a red and a green
boat
- Previous approach wont work! Must identify
sailors whove reserved red boats, sailors whove
reserved green boats, then find the intersection
(note that sid is a key for Sailors)
275. Find the names of sailors whove reserved all
boats
- Uses division schemas of the input relations to
/ must be carefully chosen
- To find sailors whove reserved all Interlake
boats
.....
28Aggregate Functions and Operations
- Aggregation function takes a collection of values
and returns a single value as a result. - avg average value min minimum value max
maximum value sum sum of values count
number of values - Aggregate operation in relational algebra
- G1, G2, , Gn g F1( A1), F2( A2),, Fn( An)
(E) - E is any relational-algebra expression
- G1, G2 , Gn is a list of attributes on which to
group (can be empty) - Each Fi is an aggregate function
- Each Ai is an attribute name
29Aggregate Operation Example
- Relation account grouped by branch-name
branch-name
account-number
balance
Perryridge Perryridge Brighton Brighton Redwood
A-102 A-201 A-217 A-215 A-222
400 900 750 750 700
branch-name g sum(balance) (account)
branch-name
balance
Perryridge Brighton Redwood
1300 1500 700
30Aggregate Functions
- Result of aggregation does not have a name
- Can use rename operation to give it a name
- For convenience, we permit renaming as part of
aggregate operation
branch-name g sum(balance) as sum-balance
(account)
31End?