The University of Texas
CS 327e
Fall 2001
Lab 1 Key
1a. For each table, identify, where possible,
a. The primary key
Table CHARTER: CHAR_TRIP
Table AIRCRAFT: AC_NUM
Table MODEL: MOD_CODE
Table PILOT: EMP_NUM
Table EMPLOYEE: EMP_NUM
Table CUSTOMER: CUS_CODE
b. A super key
Table CHARTER: CHAR_TRIP + CHAR_DATE
Table AIRCRAFT: AC_NUM + MOD-CODE
Table MODEL: MOD_CODE + MOD_NAME
Table PILOT: EMP_NUM + PIL_LICENSE
Table EMPLOYEE: EMP_NUM + EMP_DOB
Table CUSTOMER: CUS_CODE + CUS_LNAME
c. A candidate key
Table CHARTER: No practical candidate key is available. For example,
CHAR_DATE + CHAR_DESTINATION + AC_NUMBER + CHAR_PILOT + CHAR_COPILOT
will still not necessarily yield unique matches, because it is possible to fly an aircraft to the same destination twice on one date with the same pilot and copilot. You could, of course, present the argument that the combination of all the attributes would yield a unique outcome.
Table AIRCRAFT: See the previous discussion
Table MODEL: See the previous discussion
Table PILOT: See the previous discussion
Table EMPLOYEE: See the previous discussion. But Perhaps the combination of
EMP_LNAME + EMP_FNAME + EMP_INITIAL + EMP_DOB
will yield an acceptable candidate key.
Table CUSTOMER: See the previous discussion
d. The foreign key(s)
Table CHARTER: CHAR_PILOT (references PILOT)
CHAR_COPILOT (references PILOT)
AC_NUMBER (references AIRCRAFT)
CUS_CODE (references CUSTOMER)
Table AIRCRAFT: MOD_CODE
Table MODEL: NONE
Table PILOT: EMP_NUM (references EMPLOYEE)
Table EMPLOYEE: NONE
Table CUSTOMER: NONE
e. A secondary key.
Table CHARTER: CHAR_DATE + AC_NUMBER + CHAR_DESTINATION
Table AIRCRAFT: MOD_CODE
Table MODEL: MOD_MANUFACTURER + MOD_NAME
Table PILOT: PIL_LICENSE + PIL_MED_DATE
Table EMPLOYEE: EMP_LNAME + EMP_FNAME + EMP_DOB
Table CUSTOMER: CUS_LNAME + CUS_FNAME + CUS_PHONE
1b. Create the Entity Relationship diagram. Hint: Take a look at the table
contents. You will discover that an
AIRCRAFT can be used to fly many CHARTER trips, but that each CHARTER trip is
flown by one AIRCRAFT. Similarly, you will discover that a MODEL references
many AIRCRAFT, but each AIRCRAFT references a single MODEL, and so on.
Teacher's Note: Database design properly
precedes the creation of table structures and contents. However, we have found
that our students generally find it much easier to understand design concepts
and requirements after they have examined some data. By observing actual
attribute values, the abstract notion of relationships becomes "real"
and, therefore, more easily understood. So, throughout this chapter and in the
chapter's question and problem sets, we have shown the table structures and
contents first and then the E-R diagram is constructed to reflect the available
information. Actually, this process is too often typical of real world projects
in which "documentation after the fact" and "reverse
engineering" are an unfortunate reality. Do stress that proper design always begins at the conceptual level and
that table structures and contents are done at the implementation stage...
after the design is judged to be correct. We will follow the appropriate procedures when we examine design issues in
greater detail in Chapter 4.
After carefully exploring the CH2_AVIA database's contents, note the following relationships:
¨ a CUSTOMER can request many CHARTER trips, but each CHARTER trip is requested by one CUSTOMER.
¨ Each CHARTER trip requires one AIRCRAFT, but an AIRCRAFT may be used to fly many different CHARTER trips.
¨ Each AIRCRAFT is of a certain MODEL and, because the charter company may have many aircraft of a given model, each MODEL may reference many AIRCRAFT.
¨ a PILOT may pilot many CHARTER trips, but each CHARTER trip is piloted by one PILOT.
¨ a PILOT may serve as a copilot on a CHARTER trip.
¨ each PILOT is an EMPLOYEE (but not all EMPLOYEEs are PILOTs!).
The E-R diagram reflects this list of relationships:
Teacher's Note: The orientation of the E-R diagram's components is
immaterial. Show the students that the E-R diagram is just as valid if the
entities are re-arranged. The key is the proper identification of all the
relationships. However, it is considered to be good form to avoid crossing
relationship lines.
The above diagram is there for reference only (not required for lab1).
1c. Produce the
output shown in Figure P3.5 for aircraft 2778V. Note that this output includes
data from the CHARTER, EMPLOYEE, and MODEL tables.
Hint: Two of the joins pass through other tables. For
example, the “connection” between CHARTER and MODEL requires the existence of
AIRCRAFT, because the CHARTER table does not contain a foreign key to MODEL.
However, CHARTER does contain a foreign key to AIRCRAFT, which contains a
foreign key to MODEL.
Figure P3.5
Selected Output from the CHARTER, EMPLOYEE,
and MODEL Tables
SELECT CHAR_DATE,CHARTER.AC_NUMBER,MOD_NAME,EMP_LNAME
FROM CHARTER,AIRCRAFT,MODEL,PILOT,EMPLOYEE
WHERE CHARTER.AC_NUMBER = AIRCRAFT.AC_NUMBER AND
AIRCRAFT.MOD_CODE = MODEL.MOD_CODE AND
PILOT.EMP_NUM = EMPLOYEE.EMP_NUM AND
CHARTER.CHAR_PILOT = PILOT.EMP_NUM AND
CHARTER.AC_NUMBER = '2778V';
1d. Create the SQL query that will produce a list of customers who have an unpaid balance. The required output is shown in Figure P3.11. Note that the balances are listed in descending order.
FIGURE P3.11 A List of
Customers with Unpaid Balances
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_BALANCE
FROM CUSTOMER
WHERE CUS_BALANCE <> 0
ORDER BY CUS_BALANCE DESC;
1e. Using the CHARTER table as the source, group the aircraft data, and then use the SQL functions to produce the output shown in Figure 3.13. (We used utility software to modify the headers, so your headers may have a different “look.”)
FIGURE
P3.13 The Aircraft Data Summary Statement
SELECT CHARTER.AC_NUMBER, COUNT(CHARTER.AC_NUMBER),
SUM(CHAR_DISTANCE), AVG(CHAR_DISTANCE),
SUM(CHAR_HOURS_FLOWN), AVG(CHAR_HOURS_FLOWN)
FROM AIRCRAFT, CHARTER
WHERE AIRCRAFT.AC_NUMBER =
CHARTER.AC_NUMBER
GROUP BY
CHARTER.AC_NUMBER;