Relational algebra

1
Relational algebra
2
Relational algebra Notation
3
Unary 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

4
Binary Operations/Joins

• Cartesian Product Students X Courses
• In SQL
• Select
• From Students, Courses

5
Rename

• 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

6
Renaming
Example

• TEMP ? ? DNO5(EMPLOYEE)
• R(FIRSTNAME, LASTNAME, SALARY) ?
• ? FNAME, LNAME, SALARY (TEMP)

7
Union 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.

8
Set 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

9
Set Operators

• ? Name (FACULY) ? ? Name (STUDENT)
• ? Address (FACULY) ? ? Address (STUDENT)
• ? CrsCode (CLASS) - ? CrsCode (TRANSCRIPT)

10
Union

S1
S2
11
Set Difference

S1
S2 S1
S2
12
Joins

• Three new join operators are introduced
• Left Outer Join (denoted as )
• Right Outer Join (denoted as )
• Full Outer Join (denoted as )

13
Join

• Students ? ltstud 200gt Courses
• In SQL
• Select
• From Students, Courses
• Where stud 200

14
Left 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

15
Left 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

16
Right 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

17
Right 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

18
Combination 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
19
Full Outer Join

• Full Outer Join A ltjoin conditiongt B
• ensures that all tuples of A and B are present in
  the result set

20
Exercise 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.

21
Answers

• 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)

22
Exercise 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
23
1.Find names of sailors whove reserved boat 103

• Solution 1

24
2.Find names of sailors whove reserved a red boat

• Information about boat color only available in
  Boats so need an extra join

25
3.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

26
4.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)

27
5. 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

.....
28
Aggregate 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

29
Aggregate 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
30
Aggregate 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)
31
End?