database course homework 2

the notation i used is more similar to the one in elmasri's book than the one used in practice lectures, but it shouldnt matter (hopefully to you too).

using the same schema as in homework 1

write the following queries in relational algebra

retrieve id and phone number of the customers whose first name is "almog"

relational algera version
$\text{[math]}$ sql version

SELECT id, phone_number FROM customer WHERE first_name = "almog"

retrieve the id and location (city, street, and house number) of the customers that have cars

we use a semi join and then a project operation
$\text{[math]}$ assuming the customer id attribute has the same name in all relations.
sql version:

SELECT id, hometown, street, house_number
FROM   customer c
WHERE  c.id IN (SELECT customer_id
                FROM   car_owner co
                WHERE  c.id = co.customer_id
                AND    ((CURRENT_DATE() < co.end_date) OR co.end_date IS NULL)
                AND    CURRENT_DATE > start_date)

retrieve the id and customer name (first and last name) of customers that have more than 2 cars

$\text{[math]}$

retrieve the ids and customer names of all customers and the numbers of cars that belong to them, if the end date of the car ownership has ended it shouldnt be retrieved.

i assume the goal is to retrieve the entries of the car numbers and not the number of cars, hopefully thats what you meant because the original text is ambiguous.
an outer join is needed here to make sure all customers are considered even if they dont own cars
$\text{[math]}$

retrieve the garage name, car number, maintenace date and maintenance type in which the part "plug" or "gear oil" was replaced

$\text{[math]}$

retrieve car number, car type, car manufacturing company, and production date of cars that have gone through every kind of maintenance

this is the naive solution, i cant think of a better one
$\text{[math]}$

retrieve the first name, last name and phone number of the customers that have garages in their local town

$\text{[math]}$

retrieve the name of the garage that has done the most maintenances

$\text{[math]}$

retrieve the details of customers (first name, last name and phone number), the number and manufacturing company name of the motorcycle for the customers that have a motorcycle and have done maintenance in tel aviv

$\text{[math]}$

retrieve the id, first name and last name of all customers. in addition add a field called "ownership" with the following value:

for a customer with no car, "no car"
for a customer with a car that they dont own, "old owner" (hint: there is an end date in the ownership table)
for a customer with a car that they own, "car owner" (there is no end date in the owners table).

we use outer joins
$\text{[math]}$