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:
Consider the following schemas, which map the entity sets and relationship sets from the above ER diagram to tables in the relational model:
For each schema, specify the primary keys and foreign keys that are used.
Employees(id, name, salary)
Foreign keys: none
Aircraft(id, name, range)
Foreign keys: none
Flights(flno, origin, dest, dist, departs, arrives)
Foreign keys: none
IsCertified(employee_id, aircraft_id)
Foreign keys: employee_id from Employees(id),
aircraft_id from Aircraft(id)
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.
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.
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.
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:
The following are one set of solutions. There are other possible solutions as well.
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%';
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.
Find the names of all employees who make the highest salary.
SELECT name FROM Employees WHERE salary = (SELECT MAX(salary) FROM Employees);
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;
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.