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.