Test your knowledge on relational algebra operations

Evaluated skills

Manipulate data using relational algebra operations

Question 1

You are the administrator of your company’s database. This database contains the relation "employees," which lists everyone who works for the company. The first few rows of this table are as follows:

last_name	first_name	work_phone	office_number	identifier	department
Dawoud	Malik	019829	27	2893	human resources
Ericson	John	019499		2983	marketing
Stevens	Aisha	019283		1829	marketing
Costanza	George	null	51	2993	maintenance
[...]	[...]	[...]	[...]	[...]	[...]

You receive a call from the Human Resources Department. They tell you, “Our software isn’t working. I need a list with first and last names of all the employees who work in the Marketing Department as soon as possible." What operations will you execute on the employee relation in order to generate this list?

[Projection (employee, department = "marketing")] then

[Restriction (employee, last name, first name)]
[Restriction (employee, last name, first name)], then

[Projection (employee, department = "marketing")]
[Restriction (employee, department = "marketing")], then

[Projection (employee, last name, first name)]
[Projection (employee, last name, first name)], then

[Restriction (employee, department = "marketing")]

Question 2
New employees have been hired and began working this very morning. Since their software program is still not working, the Human Resources Department sends you a spreadsheet file containing information about the new employees. You import this file into a temporary table called "new_employee_temp" that is of the same schema as the “employees” table. How can you make sure that the file they sent you does not contain employees who are already present in the database?
- Execute each of the four groups of operations proposed below.
- Find the answer to this question: "How can you make sure that the file they sent you does not contain employees who are already present in the database?"
Which three of the four answers below will answer this question?
Careful, there are several correct answers.
- Test whether employee union new_employee_temp contains at least one row.
- Test whether employee intersection new_employee_temp contains no rows.
- Test whether employee difference (employee difference new_employee_temp) contains no rows.
- Test whether new_employee_temp difference (new_employee_temp difference employee) contains no rows.

Question 3

You also have access to information about employee offices in the “office” table, whose first few rows are as follows:

id	computer	nb_monitors	type_screen	chair_type	plant	floor
13	False	0	cathode ray tube	old chair	True	attic
01	True	3	LCD	executive chair	True	2
03	True	2	LCD	executive chair	True	1
12	False	0	LCD	armchair	True	1
51	True	1	cathode ray tube	stool	False	basement

The manager says, “It’s been a while since we heard from our intern, George Costanza. Can you tell me what floor he works on?"

Execute each of the groups of operations proposed below. One of them will give you the answer to your manager’s question. Which one?

[Restriction(employee, last_name = "Costanza" and first_name = "George") Intersection office]
[Restriction(Join(employee, office, employee.office_number = office.id), last_name= "Costanza" and first_name = "George")]
[office Intersection Restriction(employee, last_name= "Costanza" and first_name = "George" and employee.office_number = office.id)]