Relational algebra expression for queries

Relational algebra expression for queries

Problem Description:

How do I make these expressions of relational algebra?

These are the tables contained inside a bus driver database.

• driver( driver_id, driver_name, age, rating);
• bus( bus_id, bus_name, color);
• reserves( driver_id, bus_id, date);
  1. Find the colors of bus reserved by Mark.
  2. Find all IDs of drivers who have a rating of at least 7 or have reserved bus 112.
  3. Find the names of drivers who have not reserved a yellow bus.
  4. Find the IDs of drivers with age over 35 who have not reserved a blue bus.

Using the basic operators.

Solution – 1

Here’s my final code. I’ve searched all over the net but Relational Algebra is pretty tough subject so I just made the first problem only. Do the rest by researching. Goodluck!

I got the (⋈) symbol from Wikipedia. It is called Natural Join.
The other symbols used here are the Greek alphabets pi and delta.
The pi is for the column names. You know if it the column names (attributes) if it is after the SELECT clause.
The delta is for the condition. You know if it the condition if it is after the WHERE clause.
The ⋈ is for the table names. You know if it the table names (relations) if it is after the FROM and JOIN clauses.

1)
SELECT b.COLOR
FROM RESERVES r
NATURAL JOIN BUS b
NATURAL JOIN DRIVER d
WHERE d.DRIVER_NAME='Mark';

Relation = ∏COLOR σDRIVER_NAME='Mark' (RESERVES ⋈ BUS ⋈ DRIVER)

2)
SELECT d.DRIVER_ID
FROM DRIVER d
LEFT JOIN RESERVES r
ON r.DRIVER_ID=d.DRIVER_ID
WHERE d.RATING>=7
OR r.BUS_ID=112;

3)
--Long Method
SELECT d.DRIVER_NAME
FROM DRIVER d
WHERE d.DRIVER_ID NOT IN (
    SELECT r.DRIVER_ID
    FROM BUS b
    JOIN RESERVES r
    ON r.BUS_ID=b.BUS_ID
    WHERE b.COLOR='yellow'
);

--Short Method, use this
SELECT d.DRIVER_NAME
FROM DRIVER d
WHERE NOT EXIST (
    SELECT r.DRIVER_ID
    FROM BUS b
    JOIN RESERVES r
    ON r.BUS_ID=b.BUS_ID
    WHERE b.COLOR='yellow'
    AND r.DRIVER_ID=d.DRIVER_ID
);

4)
--Long Method
SELECT d.DRIVER_ID
FROM DRIVER d
WHERE d.AGE>35
AND d.DRIVER_ID NOT IN (
    SELECT r.DRIVER_ID
    FROM BUS b
    JOIN RESERVES r
    ON r.BUS_ID=b.BUS_ID
    WHERE b.COLOR='blue'
);

--Short Method, use this
SELECT d.DRIVER_ID
FROM DRIVER d
WHERE d.AGE>35
AND NOT EXIST (
    SELECT r.DRIVER_ID
    FROM BUS b
    JOIN RESERVES r
    ON r.BUS_ID=b.BUS_ID
    WHERE b.COLOR='blue'
    AND r.DRIVER_ID=d.DRIVER_ID
);

Solution – 2

There are multiple ways to write these queries, but here are some options.

(1) You need to join across the three tables and filter by the name:

select b.color
from bus b
join reserves r
on b.bus_id = r.bus_id join
join driver d
on d.driver_id = r.driver_id
where d.driver_name = 'Mark'

(alternatively could include d.driver_name = ‘Mark’ in the join)

(2) you need to do a left join across two tables and filter by the bus_id and rating:

select d.driver_id
from driver d
left join reserves r
on d.driver_id = r.driver_id
where r.bus_id = 112
or d.rating >= 7

(alternatively could union two queries)

(3) You need to get the drivers that are not in the join:

select d.driver_name
from driver d
where not exists (
    select 1 from reserves r
    join bus b
    on r.bus_id = b.bus_id
    where b.color = 'yellow'
    and r.driver_id = d.driver_id )

(4) Similar to the last one:

select d.driver_id
from driver d
where d.age > 35
and not exists (
    select 1 from reserves r
    join bus b
    on r.bus_id = b.bus_id
    where b.color = 'blue'
    and r.driver_id = d.driver_id )
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.
Accept
Reject