Relational algebra expression for given queries in simple database

Recommended Posts

I am just new to relational algebra probably a pre-step before learning SQL queries. Can you help me make the expressions of relational algebra expression for each of the following queries. This is the table 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);
```

a. Find the names of drivers who have reserved at least three busses.
b. Find the names of drivers who have reserved all busses.
c. Find the names of drivers who have reserved all busses called Shuttle.
d. Find the IDs of drivers whose rating is better than some driver called Paul.

I would be grateful if somebody can help me here

Share on other sites

Or you could try and learn, and then do your homework yourself.

ok, im sorry.

Share on other sites

You won't learn unless you try to do it yourself, otherwise there's no point

Share on other sites

Thank you!! in the event, i could try answering some of those questions, will you help me correct my work?

Share on other sites

Thank you!! in the event, i could try answering some of those questions, will you help me correct my work?

If you are at least making an attempt, people will be more willing to help you. I'm a bit confused by your use of the terminology "relational algebra". Seems like you are simply wanting to run a query - don't see what algebra has to do with it.

I would start with the last problem. I think it is the easiest.

Share on other sites

Assuming that the OP is really talking about Relational Algebra, then this worth to be read completely

https://en.m.wikipedia.org/wiki/Relational_algebra

There are some implementations examples that could help.

Edited by mikosiko

Share on other sites

It seems to me that Relational Algebra is much the the same as 4th Normal Form and higher - fine for the classroom but something never used in practice.

Share on other sites
Hello there everyone,

I have finally an answer on letter C and D questions. Letter C was confirmed correct by my instructor.

Seems all of my classmates have same answers on Letter D yet no one can prove where did they get the "D2", they only say that they got the answer from internet

I'm getting curios also where D2 came from. I would be grateful if somebody here does. Please. Thank you and more power.

c. π driver_name ([π driver_id, bus_id (reserves) / π bus_id(σ bus_name=‘Shuttle’ (bus))]  driver )

d. π D2.driver_id (σ D2.rating > driver.rating [ ρ (D2,Driver) × σ driver_name=‘Paul’ (Driver)] )

Share on other sites

If it helps, these are the four queries I would use. I wasn't sure if ( a ) meant 3 different buses or not. If it does, change to COUNT(DISTINCT bus_id)

Data

```mysql> SELECT * FROM driver;
+-----------+-------------+------+--------+
| driver_id | driver_name | age  | rating |
+-----------+-------------+------+--------+
|         1 | Paul        |   35 |      6 |
|         2 | Peter       |   45 |      7 |
|         3 | Mary        |   28 |      8 |
|         4 | Jane        |   35 |      4 |
|         5 | John        |   48 |      3 |
+-----------+-------------+------+--------+

mysql> SELECT * FROM bus;
+--------+---------------+--------+
| bus_id | bus_name      | color  |
+--------+---------------+--------+
|      1 | Shuttle 1     | Red    |
|      2 | Shuttle 2     | Red    |
|      3 | Single-decker | Green  |
|      4 | School Bus    | Yellow |
|      5 | Double-decker | Blue   |
+--------+---------------+--------+

mysql> SELECT * FROM reserves;
+-----------+--------+------------+
| driver_id | bus_id | date       |
+-----------+--------+------------+
|         1 |      1 | 2014-09-01 |
|         1 |      2 | 2014-09-02 |
|         1 |      3 | 2014-09-03 |
|         1 |      4 | 2014-09-04 |
|         1 |      5 | 2014-09-05 |
|         2 |      3 | 2014-09-02 |
|         2 |      4 | 2014-09-03 |
|         2 |      5 | 2014-09-04 |
|         3 |      1 | 2014-09-06 |
|         3 |      2 | 2014-09-07 |
|         3 |      3 | 2014-09-08 |
|         3 |      4 | 2014-09-09 |
|         3 |      5 | 2014-09-10 |
|         4 |      1 | 2014-09-02 |
|         4 |      3 | 2014-09-05 |
|         4 |      5 | 2014-09-01 |
|         5 |      1 | 2014-09-07 |
|         5 |      5 | 2014-09-03 |
|         5 |      5 | 2014-09-07 |
+-----------+--------+------------+
```

Queries

```mysql> SELECT driver_name
-> FROM reserves
-> INNER JOIN driver USING (driver_id)
-> GROUP BY driver_name
-> HAVING COUNT(bus_id) >= 3;
+-------------+
| driver_name |
+-------------+
| Jane        |
| John        |
| Mary        |
| Paul        |
| Peter       |
+-------------+

mysql> SELECT driver_name
-> FROM reserves
-> INNER JOIN driver USING (driver_id)
-> GROUP BY driver_name
-> HAVING COUNT(DISTINCT bus_id) =
->     (SELECT COUNT(*) FROM bus);
+-------------+
| driver_name |
+-------------+
| Mary        |
| Paul        |
+-------------+

mysql> SELECT driver_name
-> FROM reserves
->     INNER JOIN driver USING (driver_id)
->     INNER JOIN bus USING (bus_id)
-> WHERE bus_name LIKE 'Shuttle%'
-> GROUP BY driver_name
-> HAVING COUNT(DISTINCT reserves.bus_id) =
->     (SELECT COUNT(*) FROM bus
->     WHERE bus_name LIKE 'Shuttle%');
+-------------+
| driver_name |
+-------------+
| Mary        |
| Paul        |
+-------------+

mysql> SELECT driver_id
-> FROM driver d
->     INNER JOIN (
->         SELECT rating
->         FROM driver
->         WHERE driver_name='Paul'
->         ) paul
->     ON d.rating > paul.rating;
+-----------+
| driver_id |
+-----------+
|         2 |
|         3 |
+-----------+
```

Share on other sites

Hello there everyone,

I have finally an answer on letter C and D questions. Letter C was confirmed correct by my instructor.
Seems all of my classmates have same answers on Letter D yet no one can prove where did they get the "D2", they only say that they got the answer from internet
I'm getting curios also where D2 came from. I would be grateful if somebody here does. Please. Thank you and more power.

c. π driver_name ([π driver_id, bus_id (reserves) / π bus_id(σ bus_name=‘Shuttle’ (bus))]  driver )

d. π D2.driver_id (σ D2.rating > driver.rating [ ρ (D2,Driver) × σ driver_name=‘Paul’ (Driver)] )

The D2 came from this part  ρ (D2,Driver), here you are using the RENAMING (or RENAME) operator, which is equivalent to the ALIAS(ing) in SQL..

maybe it will help a little more:  http://www.databasteknik.se/webbkursen/relalg-lecture/index.html

Share on other sites

thank you very much mikosiko,, now i understand how they got the answers and i'm ready on my own.

Create an account

Register a new account