|
- Course Work1 = 11% (Individual Work Set 12th Nov, Returned 13th Dec)
- Course Work2a = 11% (Group Work [2/3] Set 7th Jan, Required 14 Feb)
- Course Work2b = 11% (Group Work [2/3] Set 7th Jan, Required 21 Mar)
- Exam = 67%
Please note there is no "right" answer to this, the actual diagram will depend on your interpretation and may not look like this one. For instance the relationship between Airlines and Flights could be either one of these:
This is just my idear of the ER diagram:
You can print these from the screen or right-click on the links and then open the documents in MS-Word.
The Coursework 1 queries against the FIG database are listed here in war text form. Just cut and past to your SQL session and they will work:
-------------------------------------------------------
-- Database Systems - First Cource Work 12th Nov 2001
-- R. H. Reepe 98000746 CSP4 Return by 14th Jan 2002
-------------------------------------------------------
-------------------------------------------------------
-- This part just sets up the column widths and line
-- lengths for display purposes. These are SQL*Plus
-- commands which need no semi-colon to run.
-------------------------------------------------------
SET LINES 100
SET PAGES 65
COLUMN airport_code FORMAT A12
COLUMN airline_code FORMAT A12
COLUMN type_code FORMAT A12
COLUMN saver_name FORMAT A12
COLUMN country_name FORMAT A18
COLUMN visa_type FORMAT A10
COLUMN single FORMAT 9999.99
COLUMN retrn FORMAT 9999.99
-------------------------------------------------------
-- There are the queries required for the coursework
-------------------------------------------------------
PROMPT ===============================================
PROMPT Query 1a
PROMPT ===============================================
SELECT airline_name
FROM airlines
WHERE airline_code IN ('PR','GA','TG','CX','AI','QF')
/
PROMPT ++++++++++++++++++++++++
PROMPT Which is the same as:
PROMPT ++++++++++++++++++++++++
SELECT airline_name
FROM airlines
WHERE airline_code = 'PR'
OR airline_code = 'GA'
OR airline_code = 'TG'
OR airline_code = 'CX'
OR airline_code = 'AI'
OR airline_code = 'QF'
/
PROMPT ===============================================
PROMPT Query 1b
PROMPT ===============================================
SELECT A.airport_name
FROM airports A, countries C
WHERE A.country_code = C.country_code
AND C.country_name = 'Australia'
/
PROMPT ++++++++++++++++++++++++
PROMPT Which is the same as:
PROMPT ++++++++++++++++++++++++
SELECT airport_name
FROM airports
WHERE country_code IN ( SELECT country_code
FROM countries
WHERE country_name = 'Australia' )
/
PROMPT ===============================================
PROMPT Query 1c
PROMPT ===============================================
SELECT COUNT(*)
FROM flights F, times T
WHERE F.flight_number = T.flight_number
AND T.day = 7
AND F.aircraft = '747'
/
PROMPT ===============================================
PROMPT Query 1d
PROMPT ===============================================
SELECT COUNT(*)
FROM flights
WHERE aircraft = '747'
AND flight_number IN ( SELECT flight_number
FROM times
WHERE day = 7 )
/
PROMPT ===============================================
PROMPT Query 1e
PROMPT ===============================================
SELECT flight_number
FROM flights
MINUS
SELECT flight_number
FROM times
/
PROMPT ++++++++++++++++++++++++
PROMPT Which is the same as:
PROMPT ++++++++++++++++++++++++
SELECT flight_number
FROM flights
WHERE flight_number NOT IN ( SELECT flight_number
FROM times )
/
PROMPT ===============================================
PROMPT Query 1f
PROMPT ===============================================
SELECT DISTINCT FL.aircraft
, FL.flight_number
FROM flights FL
, fares FA
, seat_classes S
, types T
WHERE FL.flight_number = FA.flight_number
AND FA.type_code = T.type_code
AND T.seat_class_code = S.seat_class_code
AND S.seat_class_name = 'Business'
/
PROMPT ++++++++++++++++++++++++
PROMPT Which is the same as:
PROMPT ++++++++++++++++++++++++
SELECT DISTINCT aircraft, flight_number
FROM flights
WHERE flight_number
IN ( SELECT flight_number
FROM fares
WHERE type_code
IN ( SELECT type_code
FROM types
WHERE seat_class_code
IN ( SELECT seat_class_code
FROM seat_classes
WHERE seat_class_name = 'Business')))
/
PROMPT ===============================================
PROMPT Query 1g
PROMPT ===============================================
SELECT airport_code
FROM airports
WHERE airport_code IN ( SELECT origin
FROM flights )
UNION
SELECT airport_code
FROM airports
WHERE airport_name LIKE '%Int%'
/
PROMPT ++++++++++++++++++++++++
PROMPT Which is the same as:
PROMPT ++++++++++++++++++++++++
SELECT airport_code
FROM airports
WHERE airport_name LIKE '%Int%'
OR airport_code IN ( SELECT origin
FROM flights )
/
PROMPT ===============================================
PROMPT Query 1h
PROMPT ===============================================
SELECT T.type_code, season_name, end_date - start_date AS "LENGTH"
FROM types T, seasons S
WHERE T.season_code = S.season_code
AND T.saver_code IN ( SELECT saver_code
FROM savers
WHERE saver_name = 'Ipex' )
/
PROMPT ===============================================
PROMPT Query 1i
PROMPT ===============================================
DROP VIEW fare_savers
/
CREATE VIEW fare_savers AS
SELECT S.saver_name
, S.saver_code
, A.airline_name
, FL.origin
, FL.destination
, FA.single
, FA.retrn
, FA.flight_number
FROM savers S
, airlines A
, flights FL
, fares FA
, types T
WHERE FL.flight_number = FA.flight_number
AND FL.airline_code = A.airline_code
AND S.saver_code = T.saver_code
AND T.type_code = FA.type_code
/
SELECT *
FROM fare_savers
WHERE saver_code = 'AP'
/
PROMPT ===============================================
PROMPT Query 1j
PROMPT ===============================================
SELECT C.country_name
, C.country_code
, R.visa_type
, R.conditions
FROM countries C, restrictions R
WHERE C.country_code = R.country_code
ORDER BY C.country_name
/
PROMPT ===============================================
PROMPT Query 1k
PROMPT ===============================================
SELECT A.flight_number, B.flight_number
FROM times A, times B
WHERE A.day = B.day
AND A.dep_time = B.dep_time
AND A.flight_number > B.flight_number
AND A.day = 5
/
PROMPT ===============================================
PROMPT Query 1l
PROMPT ===============================================
SELECT A.airline_name, COUNT(*)
FROM airlines A, flights F, countries C, airports P
WHERE A.airline_code = F.airline_code
AND P.country_code = C.country_code
AND F.origin = P.airport_code
GROUP BY A.airline_name
HAVING COUNT(*) = ( SELECT COUNT(*) FROM countries )
/
PROMPT ===============================================
PROMPT Query 1m
PROMPT ===============================================
DROP VIEW average_distance;
CREATE VIEW average_distance AS
SELECT A.airline_code, AVG(distance) AS "AVG"
FROM airlines A, flights F
WHERE A.airline_code = F.airline_code
GROUP BY A.airline_code
/
SELECT airline_code
FROM average_distance
WHERE avg = ( SELECT MAX(avg)
FROM average_distance )
/
PROMPT ===============================================
PROMPT Query 2
PROMPT ===============================================
UPDATE flights
SET destination = 'HKG'
, distance = distance + 1750
WHERE flight_number = 'TG911'
/
INSERT INTO stops
VALUES ( 'TG911', 'SIN', '1' )
/
COMMIT
/
PROMPT ===============================================
PROMPT Query 3
PROMPT ===============================================
PROMPT ++++++++++++++++++++++++
PROMPT remove flights
PROMPT ++++++++++++++++++++++++
DELETE FROM flights
WHERE flight_number
IN ( SELECT flight_number
FROM fares
WHERE type_code
IN ( SELECT type_code
FROM types
WHERE saver_code = 'PX'))
/
PROMPT ++++++++++++++++++++++++
PROMPT remove fares
PROMPT ++++++++++++++++++++++++
DELETE FROM fares
WHERE type_code
IN ( SELECT type_code
FROM types
WHERE saver_code
IN ( SELECT saver_code
FROM savers
WHERE saver_name = 'Pex'
)
)
/
PROMPT ++++++++++++++++++++++++
PROMPT remove types
PROMPT ++++++++++++++++++++++++
DELETE FROM types
WHERE saver_code
IN ( SELECT saver_code
FROM savers
WHERE saver_name = 'Pex'
)
/
PROMPT ++++++++++++++++++++++++
PROMPT remove savers
PROMPT ++++++++++++++++++++++++
DELETE FROM savers
WHERE saver_name = 'Pex'
/
|
|
|
|
|
|
|
|