sqlrelational-algebra

Relational algebra expression for queries


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

  • 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
    );