Section 2

Problem Domain

The problems in the rest of the handout are based on exercises 4.3 and 5.2 in Ramakrishnan and Gehrke. The problem domain is airline data, including information about flights, aircraft, and employees.

This airline domain is a simplified version of the one from section 1. Here are some other facts about this domain:

• The Employees entity set includes both pilots and other kinds of employees.
• Every pilot is certified for some aircraft, and only pilots are certified to fly.
• The range attribute of the Aircraft entity set specifies the number of miles that an airplane can fly before refueling.
• The name attribute of Aircraft specifies the plane manufacturer and model (e.g., “Boeing 727”).
• The dist attribute of the Flights entity set specifies the distance in miles between the departure city (origin) and the arrival city (dest).
• The flno attribute of Flights specifies the flight number.

From ER Diagram to Relational Model

Consider the following schemas, which map the entity sets and relationship sets from the above ER diagram to tables in the relational model:

• Employees(id, name, salary)
• Aircraft(id, name, range)
• Flights(flno, origin, dest, dist, departs, arrives)
• IsCertified(employee_id, aircraft_id)

For each schema, specify the primary keys and foreign keys that are used.

Relational Algebra Queries

Given the schema that we developed above, write relational-algebra queries to answer the following problems. For these problems, you may assume that the name of an aircraft specifies only the name of its manufacturer.

1. Find the IDs of pilots who are certified for some Boeing aircraft. Make sure that a pilot’s ID does not appear more than once.

   

2. Find the IDs and names of all pilots who are not certified for any Boeing aircraft. For this question, you might find relational algebra’s difference operator (written “-“) useful.

   

   First, we assign the relation produced by the query from question 1 to the relation variable R1. Thus, R1 is a relation containing the IDs of all pilots who can fly Boeing aircraft.

   Then, we assign to R2 the IDs of all certified pilots.

   Next, we use set difference to remove the IDs of Boeing-certified pilots from the set of all pilot IDs, and join this relation with Employees to obtain the pilot names.

SQL Queries

In the relational model/SQL lecture notes, there are rules of thumb given for writing SQL commands. Let’s review them and then complete the exercise below.

Given the schema that we developed above, write SQL queries to answer the following problems:

1. Find the IDs of pilots who are certified for some Boeing aircraft. Make sure that a pilot’s ID does not appear more than once.

   SELECT DISTINCT C.eid
   FROM Certified as C, Aircraft as A
   WHERE C.aid = A.id AND
         A.name LIKE '%Boeing%';
   

2. Find the IDs and names of all pilots who are not certified for any Boeing aircraft.

   SELECT DISTINCT E.id, E.name
   FROM Employees as E, Certified as C
   WHERE E.id = C.eid AND
         E.id NOT IN (SELECT C2.eid
                      FROM Certified as C2, Aircraft as A
                      WHERE C2.aid = A.id AND A.name LIKE '%Boeing%');
   

   An alternate solution:

   SELECT id, name
   FROM Employees
   WHERE id IN (SELECT eid FROM Certified) 
         AND id NOT IN (SELECT C2.eid
                        FROM Certified as C2, Aircraft as A
                        WHERE C2.aid = A.id AND A.name LIKE '%Boeing%');
   

   Note that DISTINCT is not needed in this case, because the outer query only has a single table.

3. Find the names of all employees who make the highest salary.

   SELECT name
   FROM Employees
   WHERE salary = (SELECT MAX(salary)
                   FROM Employees);
   

4. For each pilot who is certified for more than three aircraft, find the pilot’s ID and the maximum range of the aircraft for which he or she is certified.

   SELECT C.eid, MAX(A.range)
   FROM Certified as C, Aircraft as A
   WHERE C.aid = A.id
   GROUP BY C.eid
   HAVING COUNT(*) > 3;
   

5. A customer wants to travel from Madison to New York with no more than one change of flight. List the choice of departure times from Madison if the customer wants to arrive in New York by 6 PM.

   We need to form the union of the departure times of the direct flights from Madison to New York and the departure times of the flights that allow us to get from Madison to New York with one change of flight. For this problem, assume that departs and arrives are of type TIME. We’ll achieve this by using the OR operator.

    SELECT DISTINCT F1.departs
    FROM Flights F1, Flights F2
    WHERE ((F1.origin = 'Madison' AND
          F1.dest = 'New York' AND
          F1.arrives < '18:00:00')
    OR
          (F1.origin = 'Madison' AND
          F2.dest = 'New York' AND
          F1.dest = F2.origin AND
          F2.departs > F1.arrives AND
          F2.arrives < '18:00:00'));
   

Last updated on September 26, 2024.