Information System Projects
Summer 2003
MIS 359
Solution
SQL Homework Assignment A
The Database For Problems
Table name: CHARTER
The destinations are indicated by standard three-letter airport codes. For example,
STL = St. Louis, MO, ATL = Atlanta, GA, BNA = Nashville, TN, and so on.
Table name: CHARTER(part 2, next four attributes, primary key values repeated to see the
connection to the first part more easily.)
CHAR_HOURS = number of flight hours
CHAR_FUEL_GALLONS = the number of gallons of fuel used during the trip
Table name: CHARTER(Part 3, remaining attributes, primary key values repeated to see the connection to the first and second parts more easily.)
Table name: AIRCRAFT
AC_TTAF = Aircraft total time, airframe (hours)
AC_TTEL = Aircraft total engine time, left engine (hours)
AC_TTER = Aircraft total engine time, right engine (hours)
In a fully developed database system, such attribute values would be updated by application software when the CHARTER entries are posted.
Table name: MODEL
Customers are charged per round-trip mile, using the MOD_CHG_MILE rate. The MOD_SEAT gives the total number of seats in the airplane, including the pilot/copilot seats. Therefore, a PA31-350 trip that is flown by a pilot and a copilot has eight passenger seats available.
Table name: PILOT
The pilot licenses shown here include the ATP = Airline Transport Pilot and COM = Commercial Pilot. Businesses that operate “on demand” air services are governed by Part 135 of the Federal Aviation Administration (FAA). Such businesses are also known as “Part 135 Operators.” Part 135 operations require that pilots successfully complete a flight proficiency check each six months. The date of the “Part 135” proficiency check is recorded under PIL_PT135_DATE. To fly commercially, pilots must have at least a 2nd class medical certificate (PIL_MED_TYPE = 2).
The PIL_RATINGS include
SEL = Single Engine, LandMEL = Multi-engine, Land
SES = Single Engine, SeaInstr = Instrument
CFI = Certified Flight InstructorCFII = Certified Flight Instructor, Instrument
Table name: EMPLOYEE
Table name: CUSTOMER
The E-R diagram reflects this list of relationships:
The Chen ERD
The Crow’s Foot ERD
PROBLEMS
AVIA database contain the tables AIRCRAFT, CHARTER, CUSTOMER, EMPLOYEE, MODEL, and PILOT.
- 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 2778V.
FIGURE P5.2 Selected Attributes from the CHARTER Table For Aircraft 2778V
SELECT CHAR_DATE, AC_NUMBER, CHAR_DESTINATION, CHAR_DISTANCE,
CHAR_HOURS_FLOWN
FROM CHARTER
WHERE AC_NUMBER = '2778V';
- Create a virtual table (named AC2778V) containing the output presented in problem 2.
CREATE VIEW AC2778V AS
(SELECT CHAR_DATE, AC_NUMBER, CHAR_DESTINATION, CHAR_DISTANCE,
CHAR_HOURS_FLOWN
FROM CHARTER
WHERE AC_NUMBER = '2778V');
- 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.)
FIGURE P5.4 Selected Output from the CHARTER and CUSTOMER Tables
SELECT CHAR_DATE,AC_NUMBER,CHAR_DESTINATION,CUS_LNAME,
CUS_AREACODE,CUS_PHONE
FROM CHARTER,CUSTOMER
WHERE CHARTER.AC_NUMBER = '2778V' AND
CHARTER.CUS_CODE = CUSTOMER.CUS_CODE;
- Produce the output shown in Figure P5.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 P5.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_NUMBERAND
AIRCRAFT.MOD_CODE = MODEL.MOD_CODEAND
PILOT.EMP_NUM = EMPLOYEE.EMP_NUMAND
CHARTER.CHAR_PILOT = PILOT.EMP_NUMAND
CHARTER.AC_NUMBER = '2778V';
- Modify the query in problem 5 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 >= ‘15MAY2002'; to define the query’s date restriction.
FIGURE P5.6 Selected Output from the CHARTER, EMPLOYEE, MODEL,
and CUSTOMER Tables
SELECT CHAR_DATE, CHARTER.AC_NUMBER, MOD_NAME, EMP_LNAME, CUS_LNAME
FROM CHARTER, AIRCRAFT, MODEL, PILOT, EMPLOYEE, CUSTOMER
WHERE CHARTER.AC_NUMBER = AIRCRAFT.AC_NUMBERAND
AIRCRAFT.MOD_CODE = MODEL.MOD_CODEAND
PILOT.EMP_NUM = EMPLOYEE.EMP_NUMAND
CHARTER.CHAR_PILOT = PILOT.EMP_NUMAND
CHARTER.CUS_CODE = CUSTOMER.CUS_CODEAND
CHAR_DATE>= '15MAY2002';
- 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 order.
FIGURE P5.11 A List of Customers with Unpaid Balances
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_BALANCE
FROM CUSTOMER
WHERE CUS_BALANCE > 0
- 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.
FIGURE P5.12 The Average Unpaid Customer Balances
SELECT AVG(CUS_BALANCE), MIN(CUS_BALANCE), MAX(CUS_BALANCE),
SUM(CUS_BALANCE)
FROM CUSTOMER;
- 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.
FIGURE P5.13 The Aircraft Data Summary Statement
SELECT CHARTER.AC_NUMBER,
COUNT(CHARTER.AC_NUMBER) AS “Number of Trips”,
SUM(CHARTER.CHAR_DISTANCE) AS “Total Distance”,
AVG(CHARTER.CHAR_DISTANCE) AS “Average Distance”,
SUM(CHARTER.CHAR_HOURS_FLOWN) AS “Total Hours”,
AVG(CHARTER.CHAR_HOURS_FLOWN) AS “Average Hours”
FROM CHARTER
GROUP BY CHARTER.AC_NUMBER;