This page contains exercises corrected in Algebra Relational.
Exercise 1
We will study relational algebra through an example database and a set of queries expressed on this database. The database considered is described by the following diagram:
PLAYER (Last name, First name, Age, Nationality)
MEETING (Winner Name, Loser Name, Tournament Location, Year, Score)
WIN(PlayerName, Tournament Location, Year, Rank, Bonus, Sponsor Name)
SPONSOR (Name,Tournament Location,Year,Address,MtContribution)
The PLAYER relationship contains all licensed players.
The MEETING relationship describes for each tournament, the set π of meetings between two players (a winner and a loser). The relationship also describes the score achieved at each meeting. The following assumptions are made about tournaments:
- two players only meet once in a tournament,
- a player can win against several other players in a tournament,
- a player can lose several times in a tournament,
- a player participates in one or more tournaments.
The GAIN relationship contains the bonuses and sponsorships of players who have already participated in a tournament. Whatever the result of a player, he receives a single bonus per tournament (so he only has one gain per tournament), the amount of which is correlated to the rank he occupies in this tournament.
The SPONSOR relationship contains the references of the sponsors and the amount of their contribution to each tournament.
Express the following queries on this database using relational algebra. Give the functional expressions and corresponding algebraic trees (i.e. graphs of relational operators).
R1: Name and bonuses of players sponsored by Peugeot between 1985 and 1990.
R2: Name and age of players who participated in the 1989 Roland Garros tournament.
R3: Name and nationality of players sponsored by Peugeot and having won at least one match at Roland Garros.
R4: Name and nationality of players who participated in both the Roland Garros tournament and the Wimbledon tournament in 1985.
R5: Name of players having all their bonuses from Roland Garros tournaments greater than 1MF.
R6: Name, first name, age and nationality of players who participated in all Roland Garros.
Exercise 2
Translate, using one or two sentences into French, the meaning of the following algebraic questions:
a) Join(Join(Div(MEETING{WinnerName,LoserName,TournamentPlace,Year}, MEETING{TournamentPlace,Year}), PLAYER / WinnerName = Name), PLAYER / LoserName = LastName){WinnerName,FirstName,LoserLastName,FirstName}
b) Diff(Join(MEETING{LoseName}, PLAYER{Last Name, First Name} / Last Name = LoserName), Join(MEETING {NameWinner}, PLAYER{Last Name, First Name} / Last Name = WinnerName)).
In French, the proposed algebraic queries are paraphrased as follows:
To) Name and first name of the pairs of players meeting each other at all tournaments and the first of whom has always won against the second.
b) Name and first name of players who have always lost.
Exercise 3
Give the algebraic expression for the division.
R(X,Y) / S(Y) = piX (R) – piX ((S x piX (R) – R).
Exercise 4
Consider the following base:
What are the following expressions:
Exercise 5
Exercise 3 We consider the following relationships:
PERSON (CIN, NAME, First name, Address)
Car (Grey Card, CIN, Model)
Motorcycle (NCarteGrise, CIN, Model)
Write the expressions representing:
1. Show people who own a car but not a motorcycle?
2. Show people who own a car and a motorcycle?
3. Show people who don't own a car or motorcycle?
Exercise 6
Consider the schema of the following Library database:
Student(NumEtd,LastNameEtd,FirstNameEdt,AddressAndd)
Book(BookNum,BookTitle,AuthorNum,PublisherNum,ThemeNum,EditionYear)
Author(AuthorNum,AuthorName,AuthorAddress)
Publisher(EditorNum,EditorName,EditorAddress)
Theme(ThemeNum,ThemeTitle)
Ready(EtdNum,BookNum,ReadyDate,ReturnDate)
Primary keys in bold and foreign keys in italics.
Write the following queries in algebraic language:
1. The name, first name and address of the student named 'Alami'
2. The author number 'Alami'
3. the list of books by the author number 121
4. books by author name 'Alami'
5. the number of the author of the book 'how to get 20 in BDD'
6. the name and address of the author of the book 'how to get 20 in BDD'
7. Books by the author 'Alami' published by the publisher 'Nul part'
8. books by the author 'Alami' or 'Belhadj'
9. books that have never been borrowed
Exercise 7
In the rest of the tutorial, we will consider the following relationship diagrams with their intuitive meanings:
CJH (CourseId, Day, Time) The course whose identifier appears in the first component of a tuple takes place on the day specified in the second component, at the time which appears in the third component.
CS (IdCours, IdSalle) The course of the first component takes place in the room indicated in the second component.
ENA (EtudiantId, Name, Address) Students whose identifier appears as the first component of a tuple have a name and an address which appear respectively in the second and third component.
CEN (IdCours, IdEtudiant, Note) The student in the second component obtained the grade specified in the third component in the course specified in the first component.
These four diagrams form the database diagram which will be used for the exercises. An example of a possible current value for the database is shown below.
1.7. Inspired by 1.5, show that division can be obtained from the difference, the Cartesian product and the projection.
Exercise 8
Using the same tables as exercise 7, express the queries below in algebraic language:
2.1. Give the names of the students taking the 'Algo' course.
2.2. Give the marks in 'Archi' for the students whose name is 'Titi'.
2.3. Give the pairs (day, time) for which room 'S1' is occupied by a course.
2.4. Give the IDs of students who only have 'A' grades
2.5. Give the room where 'Toto' is on Monday at 9am.
Exercise 9
On the same bases of exercise 7 and the queries of exercise 8.
3.1. Draw the expression tree for query 2.1, drag the restriction as low as you can.
3.2. Draw the expression tree for query 2.5, drag the restrictions and projections as low as you can.
Exercise 10
Let R and S be the relations, where the attributes A, B, C are defined on the domains of the letters of the alphabet. Give the result of the following queries:
Then check the following equations:
The three queries are at the top, the 2 equations are wrong, the result is at the bottom.
Exercise 11
Consider the two tables:
Employee Relations (EMP)
EMP(ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO)
ENO: employee number, key
ENOM: employee name
PROF: profession (director is not a profession)
DATEEMB: date of hire
SAL: salary
COMM: commission (an employee may not have commission)
DNO: department number to which the employee belongs
Relations of Departments (DEPT)
DEPT(DNO, DNOM, DIR, CITY)
DNO: department number, key
DNOM: name of department
DIR: department director
CITY: location of the department (city)
Express the following queries in relational algebra and then in SQL:
Query 1: Give all tuples of DEPT.
Query 2: Return all tuples of EMP.
Request 3: Give the names and salaries of employees.
Request 4: Give the employees' professions (after elimination of duplicates).
Request 5: Give the hiring dates of the technicians.
Query 6: Make the Cartesian product between EMP and DEPT.
Query 7: Give the names of the employees and the names of their department.
Request 8: Give the numbers of employees working in BOSTON.
Request 9: Give the names of the directors of departments 1 and 3. Warning: director is not a profession!
Request 10: Give the names of employees working in a department with at least one engineer.
Request 11: Give the salary and name of employees earning more than one (at least one) engineer.
Request 12: Give the salary and name of employees earning more than all engineers.
Request 13: Give the names of employees and the names of their directors.
Query 14: Find the names of employees with the same manager as JIM. Please note: an employee can be director of several departments.
EN 
FR 
ES