CS204 Files and Database Systems &
CS207 & Computer Data Management
Due : May 2004
Quiz1 Details can be referred to Text (p.204, 15-17)
1. Given this structure, draw its dependency diagram. Label all transitive and/or partial
dependencies. (Hint: This structure uses a composite primary key.)
2. Break up the dependency diagram you drew above to produce dependency diagrams that are
in 3NF. (Hint: You might have to create a few new attributes. Also, make sure that the new
dependency diagrams contain attributes that meet proper design criteria; that is, make sure
that there are no multi-valued attributes, that the naming conventions are met, and so on.)
3. Using the results of problem 2, draw the E-R diagram.
Quiz 2. Details can be refered to Text (p 302, 2-15, but not 3).
Using the CH2-AVIA database The database must contain the tables AIRCRAFT,
CHARTER, CUSTOMER, EMPLOYEE, MODEL, and PILOT.) Rename this database
CH5_AVIA. Using your DBMS’s import function, import the data from the CH2_AVIA
1. Using the CHARTER table’s contents, write the SQL query that will produce the output
shown in Figure P5.2. Note that the output is limited to selected attributes for aircraft number
2. Produce the output shown in Figure P5.4 for aircraft 2778V. Note that this output includes
data from the CHARTER and CUSTOMER tables. (Hint: Use a JOIN in this query.)
3. Modify the query in problem 2 to include data from the CUSTOMER table. This time, the
output is limited by the date selection, as shown in Figure P5.6. Use the clause WHERE
CHAR_DATE >= ‘5/15/2002’; to define the query’s date restriction.
4. Modify the query in problem 3 to produce the output shown in Figure P5.7. Problem 6’s date
limitation applies to this problem, too.
5. Modify the query in problem 4 to produce the computed (derived) attributes “fuel per hour”
and “total mileage charge.” (Hint: It is possible to use SQL to produce computed “attributes”
that are not stored in any table. For example, the following SQL query is perfectly
SELECT CHAR_DISTANCE, CHAR_GALLONS,
WHERE CHAR_DATE >= ‘1/20/02’;
Use a similar technique on joined tables to produce the output shown in Figure P5.8.
6. Modify the query you produced in problem 5 to create the output shown in Figure P5.9.
Note that, in this case, the computed attributes are derived from two different tables. (Hint:
The MODEL table contains the charge per mile and the CHARTER table contains the total
miles flown. Note also that the output is ordered by date and, within the date, by the
customer’s last name.)
7. Use the techniques that produced the output in problem 6 to produce the charges shown in
Figure P5.10. The total charge to the customer is computed by:
Miles flown * charge per mile
Hours waited * $50 per hour
The miles flown (CHAR_DISTANCE) is found in the CHARTER table, the charge per mile
(MOD_MILECHARGE) is found in the MODEL table, and the hours waited
(CHAR_WAIT) are found in the CHARTER table.
10. Create the SQL query that will produce a list of customers who have an unpaid balance. The
required output is shown in Figure P5.11. Note that the balances are listed in descending
11. Find the average unpaid customer balance, the minimum balance, the maximum balance, and
the total of the unpaid balances. The resulting values are shown in Figure P5.12.
12. Using the CHARTER table as the source, group the aircraft data, then use the SQL functions
to produce the output shown in Figure 5.13. (We used utility software to modify the headers,
so your headers may have a different “look.”)
13. Write the SQL code to generate the output shown in Figure P5.14. Note that the listing
includes selected CHARTER attributes for all flights that did not include a co-pilot. (Hint:
Try using the IS NULL function! And remember that the pilot’s last name requires access to
the EMPLOYEE table, while the aircraft model requires access to the MODEL table.)
14. Write the SQL code that will update the AIRCRAFT table’s airframe and engine hours by
adding to them the total hours flown by each aircraft in the CHARTER table. (Hint: In order
to preserve the original AIRCRAFT table’s values, use ROLLBACK after you have finished