Relational algebra expression for given queries in simple database

[sherlockxhouse]

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. 

[paddy_fields]

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

[sherlockxhouse]

ok, im sorry.

[paddy_fields]

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

[sherlockxhouse]

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

[Psycho]

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.

[mikosiko]

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.

[Barand]

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.

[sherlockxhouse]

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)] ) 

[Barand]

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 |
+-----------+

[mikosiko]

 

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

[sherlockxhouse]

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