LAB 02: Relational Algebra

Basic Relational Algebra Expression

NOTE: Write down the answer of the following problems in MS word and converting
it to a pdf file.
Your final submission file must be a pdf file.
Due date: Oct. 5, 24:00
Submission: Database System course in Superstar

We will understand how the relational algebra works, but it is more like logic in mathematics so it
doesn’t show you the result (esp. intermediate ones) of what you tried to do. For the reason, we will
utilize RelaX (Relational Algebra calculator), which enables us to see the exact result of the
expressions in relational algebra.

RelaX

What you need is just a web browser, nothing else for this LAB.

So, go to the RelaX website: http://clotho.uom.gr/relax/index.htm .

1. How to use RelaX: Before you use it, you’d better read the tutorial for RelaX. (‘help’ button on
the top right side of menu or http://clotho.uom.gr/relax/help.htm ). The syntax of RelaX is not
exactly same as expressions in relational algebra.

2. Creating a dataset: In the main page, press ‘calculator’ button then you can see the
current dataset on the left side of editor. Press the current dataset (red circled one in the
below image).
3. Now you can see ‘create a new dataset’ button, then press it. Then now you can create a
dataset in Group editor. (In fact, you can directly use ‘Group editor’ instead of step 2.)

4. In the Group editor, erase everything and copy and paste ‘employee schema’, which is
given in the APPENDIX 1 (the last page of this document).
5. Press ‘preview’ button and if you can see the tables of the employee dataset, then press ‘use
Group in editor’ button.

6. To check your dataset, press ‘Relational Algebra’ tab and type π person_name employee
and press ‘execute query’. If you see the result of the projection operation properly, then you
are ready to do relational algebra.

7. The RelaX website doesn’t save the dataset you used after you close your web browser. So if
you access there later the dataset won’t be there. If you don’t like to copy and paste the given
dataset in this file every time you access RelaX, you can save it in Github Gist.

More information: http://clotho.uom.gr/relax/help.htm#tutorial-maintainer-share-dataset

Tip: RelaX shows the intermediate result of your expression. So, If you want to see intermediate
results, then click the derived relation on the expression tree, e.g. if you click ‘⨝’ on the tree as shown
in the below image, then you can see the table of only natural join result.

Final result of expression

Intermediate result of expression

NOTE: when you answer the following questions, you check your answer using RelaX. however, the
syntax of RelaX is not always the same as the conventional expression in relational algebra (i.e some
are the same and some are not same as what we’ve leaned in class). Therefore, you must check the
syntax of RelaX in advance.

Accordingly, your submitted file must include two answers (i.e. in the conventional expressions in
relational algebra and expressions in Relax) for the question and screenshot of result relation,.

Example: Find how many customers own one account and assign the result to temporary t1 relvar.

Your answer must include:

ReAlg: t1  account_number G COUNT(customer_name) AS num_acct (depositor)

RelaX: t1 = γ depositor.account_number; count(depositor.account_number)-> num_acct (depositor)

As you can see the syntax of RelaX is not exactly same as conventional relational algebra expressions.
(however, most expressions are similar except for aggreation, assignment operation, etc.)
Problems

The first dataset is ‘employee schema’ and its schema is as shown in Fig. 1.

employee ( person_name, street, city)

works (person_name, company_name, salary)
company ( company_name, city)
manages (person_name, manager_name)

Figure 1. employee schema

█ General Expression: Write an expression in the relational algebra (also RelaX) based on
‘employee schema’ in Fig.1.
1. Find the names of all employees who work for “FirstBankCorporation”.

2. Find the names and cities of residence of all employees who work for “FirstBankCorporation”.

3. Find the names, street address, and cities of residence of all employees who work for
“FirstBankCorporation” and earn more than $10,000 per annum.

4. Find the names of all employees in this database who live in the same city as the company for
which they work.

5. Find the names of all employees who live in the same city and on the same street as do their
managers.

6. Find the names of all employees in this database who do not work for “FirstBankCorporation”.

7. Find the names of all employees who earn more than every employee of “SmallBankCorporation”.

8. Assume the companies may be located in several cities. Find all companies located in every city
in which “SmallBankCorporation” is located.
APPENDIX 1: employee schema

group: employee schema

employee = {person_name street city

adam kenwood chicago
bob kimbark chicago
joe michigan chicago
kate woodlawn boston
noelle woodlawn boston
veronique kimbark warsaw
luke dorchester warsaw
bartek marszalkowska warsaw
}

works = {person_name company_name salary

adam FirstBankCorporation 2500
bob FirstBankCorporation 11000
joe FirstBankCorporation 1000
kate hospital 20000
noelle hospital 19000
veronique office 4000
luke it 10000
bartek it 15000
}

company = { company_name city

FirstBankCorporation chicago
FirstBankCorporation warsaw
FirstBankCorporation new_york
hospital warsaw
hospital chicago
hospital chorzow
office baltimore
it warsaw
SmallBankCorporation warsaw
SmallBankCorporation chicago
}

manages = {person_name, manager_name

adam bob
luke bartek
noelle kate
}