Lab2: Relational Algebra
Transcript of Lab2: Relational Algebra
Islamic University of Gaza Faculty of Engineering
Department of Computer Engineering
ECOM 3422: Database Systems [spring 2020] Abeer J. Al-Aydi & Abdallah H. Azzami
Lab2: Relational Algebra
Objectives
1. To be familiar with the relational data model.
2. To be familiar with RelaX relational algebra calculator.
3. Apply some relational algebra practically.
4. Be able to solve simple and complex queries by applying relational algebra expressions.
Table of Content
Objectives 1
Table of Content 1
2. 1 Terminology 2
2.2 RelaX Tool 2
2.3 Our database schema 3
2.4 Relational algebra operations 4
2.4.1 SELECT (σ) 4
2.4.2 Projection (π) 6
2.4.3 Cartesian product (X) 7
2.4.4 Join Operations (⋈) 8
2.4.5 Union operation (υ) 9
2.4.6 Intersection (∩) 10
2.4.7 Set Difference (-) 10
2.4.8 Assignment (←) 11
2.4.9 Rename (ρ) 11
2.4.10 Equivalent Quires 13
2.5 Lab work 14
2.5.1 LabWork1 14
2.5.2 LabWork2 14
2.5.3 LabWork3 14
2.6 Homework 15
2.7 Bonus 15
Lab2| Relational Algebra
Page 2 of 15
2. 1 Terminology
Relational algebra consists of a set of operations (functions) that take one or two relations as input
and produce a new relation as their result.
Basic Relational Algebra Operations:
● Select (σ)
● Project (Π)
● Cartesian product (×)
● Union (∪)
● Set difference (−)
● Rename (ρ)
Additional Relational Algebra Operations:
● Set intersection (∩)
● Natural join (⋈)
● Assignment (←)
● Division (÷)
2.2 RelaX Tool
RelaX is an online relational algebra calculator. We will use it to execute algebra expressions on our relational tables. You can find the tool on GitHub here There are two ways to provide a group of tables ( or what we know as “Database Schema + some data ”) to work with using the tool:
● Static groups: the groups and tables are loaded from a text file stored on the server.
● GitHub gist: the groups and tables are loaded from a GitHub gist via its unique id.
Before we dive into relational algebra and run some expressions let’s understand our database schema.
Our sample schema dataset is created and uploaded on RelaX, here.
Lab2| Relational Algebra
Page 3 of 15
2.3 Our database schema
Let’s take a look at our database schema.
And we can describe it like this:
classroom = { building, room_number, capacity }
department = { dept_name, building, budget }
course = { course_id, title, dept_name, credits }
instructor = { ID, name, dept_name, salary }
section = {course_id, sec_id, semester, year, building, room_number, time_slot_id}
teaches = { ID, course_id, sec_id, semester, year }
student = { ID, name, dept_name, tot_cred }
takes = { ID, course_id, sec_id, semester, year, grade }
advisor = { s_ID, i_ID }
time_slot = { time_slot_id, day, start_time, end_time }
prereq = { course_id, prereq_id }
Lab2| Relational Algebra
Page 4 of 15
2.4 Relational algebra operations
2.4.1 SELECT (σ)
SELECT operation is used for selecting a subset of the tuples according to a given selection condition.
Example 1: Get all instructors of the’ Physics’ department.
σ dept_name = 'Physics' (instructor)
let’s execute it using RelaX:
Lab2| Relational Algebra
Page 5 of 15
Example 2: Find all instructors who earn a salary greater than $70,000.
sigma salary > 70000 (instructor)
Example 3: Find all instructors of the ‘Physics’ department with salaries greater than $70,000.
σ dept_name = 'Physics' ∧ salary > 70000 (instructor)
Example 4: Find all departments whose names are the same as their building names.
σ dept_name = building (department)
Lab2| Relational Algebra
Page 6 of 15
2.4.2 Projection (π)
Projection function results in a relation that contains a vertical subset of input relation.
This operation allows you to keep specific columns from a relation and discards the other columns.
Example 5: Display the ID, name, and salary of all instructors
π ID, name, salary (instructor)
Example 6: Find the names of all instructors in the ‘Physics’ department.
π name (σ dept_name = 'Physics' (instructor))
Example 7 Find the list of course IDs of all courses taught in the spring 2009 semester.
Lab2| Relational Algebra
Page 7 of 15
π course_id (σ semester = 'Spring' ∧ year=2009 (section))
2.4.3 Cartesian product (X)
Cartesian product allows us to merge two relations. A Cartesian product is rarely a meaningful operation when it is
performed alone. However, it becomes meaningful when it is followed by other operations.
Example 8: Display all instructors with courses they teach.
σ instructor.ID = teaches.ID (instructor ⨯ teaches)
Example 9: list instructor name, and course ID for the course that they teach.
Lab2| Relational Algebra
Page 8 of 15
π instructor.name, teaches.course_id (σ instructor.ID = teaches.ID
(instructor ⨯ teaches))
2.4.4 Join Operations (⋈)
Natural join is a binary operation that allows us to combine certain selections and a Cartesian product into one
operation, it forms a Cartesian product of its two input relations, then performs a selection forcing equality on attributes
that appear in both relation schemas, and finally removes duplicate attributes from the resulting relation.
Theta Join is a Cartesian product and a condition theta that you specify directly after the join symbol
i.e. instructor ⋈ instructor.ID = teaches.ID teaches
Example 10: Display all instructors with all courses they teach.
instructor ⋈ instructor.ID = teaches.ID teaches (Theta Join)
or instructor ⋈ teaches (Natural join)
Here we applied (Theta Join)
Lab2| Relational Algebra
Page 9 of 15
2.4.5 Union operation (υ)
Results in a relation that includes all tuples that are in relation A or in B. It also eliminates duplicate tuples.
For a union operation to be valid, the following conditions must hold:
● Both relations must have the same number of attributes.
● Attribute domains need to be compatible.
Example 11: Find all the courses offered in either Fall 2009, Spring 2010, or both semesters.
π course_id (σ semester = 'Fall'∧ year = 2009 (section)) ∪
π course_id (σ semester = 'Spring' ∧ year = 2010 (section))
Lab2| Relational Algebra
Page 10 of 15
2.4.6 Intersection (∩)
Results in a relation consisting of the set of all tuples that are in both A and B.
However, A and B must be compatible.
Example 12: Find all the courses offered in both the Fall 2009 and Spring 2010 semesters.
π course_id (σ semester = 'Fall' ∧ year = 2009 (section)) ∩
π course_id (σ semester = 'Spring' ∧ year = 2010 (section))
2.4.7 Set Difference (-)
The result of A - B, is a relation which includes all tuples that are in A but not in B.
Example 13: Find all the courses taught in the Fall 2009 semester but not in Spring 2010 semester.
π course_id (σ semester = 'Fall'∧ year = 2009 (section)) –
π course_id (σ semester = 'Spring' ∧ year = 2010 (section))
Lab2| Relational Algebra
Page 11 of 15
2.4.8 Assignment (←)
Provides a convenient way to express complex queries. This operation does not provide any additional power to the
algebra. It is, however, a convenient way to express complex queries. You can imagine this operator as if it gives a text
value to a name, and when the expiration is executed it performs an automatic replacement of every occurrence of that
name to the left of the assignment by its associated text value (expiration).
Note: The assignment operator in RelaX is ( = ) not an arrow.
Example14: Find all the courses taught in Fall-2009 semester and assign it to “courses_fall_2009”
courses_fall_2009 = π course_id ( σ semester = 'Fall'∧ year=2009 (section))
courses_fall_2009
NOTE: An assignment (= definition of a variable) is invalid
relational algebra expression on its own.
If you miss the actual query in RelaX, an error is thrown
(Error: only assignments found; query is missing).
2.4.9 Rename (ρ)
Gives a name to the results of relational-algebra expressions.
Example15: Return the student relation and rename it to S.
ρ S student
Lab2| Relational Algebra
Page 12 of 15
Example16: Find all students with credits greater than the credit of the student named “Levy”.
σ student.tot_cred > S.tot_cred (σ S.name = 'Levy' ( ρ S student ) ⨯ student)
Lab2| Relational Algebra
Page 13 of 15
2.4.10 Equivalent Quires
Example17: Give two relational algebra expressions that find information about courses taught by all instructors in the ‘Physics’ department.
σ dept_name = 'Physics' (instructor ⨝ instructor.ID = teaches.ID teaches)
or
(σ dept_name = 'Physics' (instructor)) ⨝ instructor.ID = teaches.ID teaches
Lab2| Relational Algebra
Page 14 of 15
2.5 Lab work
2.5.1 LabWork1
1. Find the ID and name of each instructor in the ‘Physics’ department.
2. Find the ID and name of each instructor in a department located in the building ‘Watson’.
3. Find the Instructor who earns a salary greater than the budget of his department.
2.5.2 LabWork2
4. Find the ID and name of each student who has taken at least one course section in the year 2010.
5. Find the ID and name of each student who has not taken any course section in the year 2010.
2.5.3 LabWork3
6. Find the ID and name of all students who have taken all the courses offered by the Comp. Sc. department.
7. Find the ID and name of all students who have taken all the courses that are taken by student named 'Zhang'.
8. Find the name and salary of all instructors who earn the highest salary.
Lab2| Relational Algebra
Page 15 of 15
2.6 Homework
1. Set intersection operation is not a “fundamental” relational algebra operation, rewrite it using
fundamental operations.
2. Find the ID and name of each student who has taken at least one course in the ‘Comp. Sci.’
department.
3. Make a list of courses that involve someone named ’Shankar’, either as an instructor or as a student
who took that course.
4. Find the list of courses that have more credits than the course ‘Robotics’.
5. Find the names and salaries of instructors who earn a salary that is NOT the maximum nor the
minimum.
6. Find all students who have taken all courses taught by instructor 'Srinivasan'.
a. Using division operation.
b. Without using division operation.
2.7 Bonus
For 0 extra credit: If you finish the above early and are bored, try doing the following exercises. You get no extra marks,
except that if you mess up an earlier part, you may still get full marks on the lab if you do this right.
1. Find the number of courses in ‘Comp. Sc.’ department that was taken in 2009.
2. Write an expiration to display how many courses has every student taken.
3. Find the number of enrolled students in every section that was offered in the Spring of 2010.
4. Find the maximum enrollment across all sections in the Spring of 2010.
Hint:
Common functions include MAX, MIN, SUM, AVG, and COUNT are aggregate functions.
Aggregation operation is a very useful to apply a function to a collection of values in order to generate a single result, reduces a set of rows to
a single row, takes the value of a specific field (or an expression over multiple fields) for a set of rows and outputs a single value.
Aggregation operator in Relax is Gamma.