The problems refer to an AIRLINE database similar to the one defined in problem 7.21 of the text. The changes in the database schema were done to make a better database design. To make yourself a copy of this database in Sybase, use isql and the stored procedure OSUsp_CopyAirline. If you want to restore your copy to its original data, first delete it by using OSUsp_ClearAirline and then recreate it using OSUsp_CopyAirline.

For these problems you will need to submit your answers, i.e. SQL queries/commands, electronically. This submission will be done using the submit command. You should submit two files: hw1.txt (answers to problem 1) and hw1.sql (answers to problems 2-4). See the next page for details on how to submit files.

1. Foreign Keys and Relational Integrity
   1. Identify the foreign keys for the following relations: AIRPLANE, AIRPLANE_TYPE, AIRPORT, CAN_LAND, FLIGHT, FLIGHT_LEG and LEG_INSTANCE.
   2. For each foreign key identified above, specify whether to use reject, cascade or nullify on deletion and update.
2. Select:

   For the more complex queries, you may want to create intermediate views to break the problem into simpler parts.

   All processing for these queries should be done in your select statements. That is you should not look up some data in the database and then use that data to simplify your query.

   1. List the flight number, leg number and plane type for all leg instances departing on 10/9/89 from ATL.

      Give four different answers for this problem:

      1. Big Cartesian product.
      2. Nested select. (Note that "select * from A where (a,b) in select (a, b) from B where condition" is not allowed. Only a single field is allowed.)
      3. Exists
      4. Join
   2. Determine the number of seats available on non-stop flights from ATL to CMH during the period 10/9/89 through 10/10/89. List the date and number of seats for that date.
   3. List the flight_no, leg_no, airline name, scheduled departure and arrival times, and departure airport name for all LEG_INSTANCES of a flight departing on 10/10/89.
   4. List the flight_no, and intermediate city for all two leg flights from Washington to Miami. Note: a two leg flight would have two FLIGHT_LEG instances with the same flight number and successive leg numbers.
   5. Insert the tuple ('M11', 'ATL') into the table CAN_LAND(airplane_type_name, airport_code).
   6. List the airport_code for the airports that CAN_LAND all plane_types.
3. Delete and update:
   1. Delete all leg instances with the oldest date. Note; You should use select to determine the oldest date. Print out the resulting table.
   2. Change the departure time for the 10/11/89 flight number 787, leg2, to 15:30. Print out the resulting table.
4. DDL - create table; Insert: Assume we want to add the airline names of the various airlines to the database. That requires a new table with two attributes.

   AIRLINES(AIRLINE, AIRLINE_NAME)

   Specify the create table statement needed to create this table. Assume AIRLINE_NAME is a character string of up to 30 characters. Insert the following data into the table.

   AIRLINE AIRLINE_NAME
   US      US Airways
   TWA     Trans World Airline

   Print out the resulting table.

How to Run and Submit Your Solution to Homework 1

``isql'' can be used in batch mode. That is create a file called hw1.sql and use that file as input to isql as follows:

isql -P yourpassword < hw1.sql

OSUsp_ClearAirline
go
OSUsp_CopyAirline
go
/* 2 */
/* a */
select ...
from   ...
       ...
go
/* e */
select ...
from   ...
       ...
go

/* 3 */
/*  a */
delete from   ...
where  ...
       ...
go
...

drop table ...

You are to submit the files hw1.txt and hw1.sql. The class name is c671aa and the subdirectory is called hw1. The submit command should be used as follows:

submit c671aa hw1 hw1.txt hw1.sql