Assign_std by xiaopangnv


									                         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


                       FROM CHARTER
                            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
    this problem.)


To top