Jump to content
sherlockxhouse

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.