Jump to content

Relational algebra expression for given queries in simple database


sherlockxhouse
Go to solution Solved by mikosiko,

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

Link to comment
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.

Link to comment
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)] ) 

Link to comment
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 |
+-----------+
Link to comment
Share on other sites

  • Solution

 

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.