-
Scheduled Flight (FlightCode, StartingAirport, DestinationAirport, DepartureDate, DepartureTime, FlightDuration, AirportCode*, AirlineNo*)
A Scheduled Flight has its information stored within an entity called “scheduled Flight” this comprises of many attributes such as a FlightCode,startingAirport, DestinationAirport, DepartureDate, DepartureTime, FlightDuration, AirportCode*, AirlineNo*
-
Airline (AirlineNo, AirlineName, TelNo, FaxNo)
Information of the Airline comprises of many attributes such as an unique Airlineno AirlineName, telno and a fax no.
-
Airport (AirportCode, AirportName, Country, TelNo)
Airport comprises of many attributes such an unique Airport code, airportname, country and a telno.
-
Payment ( PaymentNo ,Mode, PaymentDate, BookingId*)
Payment information is also stored into an entity called “Payment” such as an unique payment no, the mode of Payment, Payment Date and BookingId which is a foreign key.
Question 3
Customer
CREATE TABLE GROUPA5Customer(
customerNo CHAR(5) NOT NULL,
address VARCHAR(20) NOTNULL,
telNo CHAR(10) NOT NULL,
title VARCHAR(4)
surname VARCHAR(15) NOTNULL,
forename VARCHAR(15) NOTNULL,
PRIMARY KEY(CustomerNo));
INSERT INTO GROUPA5Customer
VALUES ('CU111','94 St.Rita rd',0114205467,'Mr', 'Steve','Nixon');
INSERT INTO GROUPA5Customer
VALUES ('CU222','86 Paul Rd',0112345796,'Mr','John','Dale');
INSERT INTO GROUPA5Customer
VALUES ('CU333','43 White Rd',0112326745,'Mr','Peter','loose');
INSERT INTO GROUPA5Customer
VALUES ('CU444','90 Gordn Rd',0112786543,'Mr','White','Gordon');
INSERT INTO GROUPA5Customer
VALUES ('CU555','42 Hampden Lane',0112897453,'Mr','Ameen','Tom');
INSERT INTO GROUPA5Customer
VALUES ('CU777','67 Pool Street',0112349603,'Mrs','Jennifer','Tailor');
COMMIT;
AIRPORT
CREATE TABLE GROUPA5Airport(
airportCode CHAR(5) NOT NULL,
airportName VARCHAR(20) NOT NULL,
country VARCHAR(20) NOT NULL,
telNo CHAR(10) NOT NULL,
PRIMARY KEY (airportCode));
INSERT INTO GROUPA5Airport
VALUES ('CDK12','Tuton','London',9589621358);
INSERT INTO GROUPA5Airport
VALUES ('XYZ13','Cape Town','South Africa',455454122);
INSERT INTO GROUPA5Airport
VALUES ('HHK14','Bangalore','India',9974456529);
INSERT INTO GROUPA5Airport
VALUES ('DDL15','Denver','USA',5364721009);
INSERT INTO GROUPA5Airport
VALUES ('HQZ16','Heathrow','London',4484145657);
COMMIT;
AIRLINE
CREATE TABLE GROUPA5Airline(
airlineNo CHAR(5) NOT NULL,
airlineNAme VARCHAR(25) NOT NULL,
telNo CHAR(10) NOT NULL,
faxNo CHAR(10) NOT NULL,
PRIMARY KEY(airline));
INSERT INTO GROUPA5Airline
VALUES ('ULL86','Sri Lankan Air Ways', 0112446688, 0112446689);
INSERT INTO GROUPA5Airline
VALUES ('SQL44','Quatar Airways', 5565896472, 4565896472);
INSERT INTO GROUPA5Airline
VALUES ('NSL86','Ethiad Airways', 5255846923, 1122564236);
INSERT INTO GROUPA5Airline
VALUES ('SQL58','Quatar', 5565896472, 4565896472);
INSERT INTO GROUPA5Airline
VALUES ('ULL54','Sri Lankan Air Ways', 0112446688, 0112446689);
COMMIT;
HOTEL
CREATE TABLE GROUPA5Hotel(
hotelId CHAR(5) NOT NULL,
hotelName VARCHAR(20) NOT NULL,
street VARCHAR(20) NOT NULL,
town VARCHAR(15) NOT NULL,
postCode CHAR(9) NOT NULL,
PRIMARY KEY (hotelId),
INSERT INTO GROUPA5Hotel
VALUES ('HIL01','Hotel Hilton','7 Brverly Street','U.S.A','081263356');
INSERT INTO GROUPA5Hotel
VALUES ('TAJ02','Taj','21 Tower Brigde,' London','546667699');
INSERT INTO GROUPA5Hotel
VALUES ('HOL01','Holiday Inn','04 Ql Rashif Park','Dubia','964466562');
INSERT INTO GROUPA5Hotel
VALUES ('HIL02','Hotel Hilton','22 Lakw House','Sri Lanka','888999898');
INSERT INTO GROUPA5Hotel
VALUES ('TAJ01','Taj','03 Bristol','West Germany',' 595456632');
COMMIT;
HOLIDAY BOOKING
CREATE TABLE GROUPA5HolidayBooking(
bookingId CHAR(5) NOT NULL,
bookingDate DATE NOT NULL
totalCost NUMBER(6,2) NOT NULL,
customerNo CHAR(5) NOT NULL,
PRIMARY KEY(BookingId),
FOREIGN KEY(customerNo) REFERENCES GROUPA5Customer(customerNo));
INSERT INTO GROUPA5HolidayBooking
VALUES ('LK112','07-Jan-2007',32.49 ,'CU111');
INSERT INTO GROUPA5HolidayBooking
VALUES ('LK113','31-Jan-2008',92.10, 'CU555');
INSERT INTO GROUPA5HolidayBooking
VALUES ('LK114','04-Sep-2006',62.35, 'CU222');
INSERT INTO GROUPA5HolidayBooking
VALUES ('LK115','08-Apr-2006',112.50, 'CU777');
INSERT INTO GROUPA5HolidayBooking
VALUES ('LK116','03-Mar-1999',79.50, 'CU111');
COMMIT;
PAYMENT
CREATE TABLE GROUPA5Payment(
paymentId CHAR(5) NOT NULL,
paymentDate DATE NOT NULL,
modeOfPayment VARCHAR(20) NOT NULL,
bookingId CHAR(5) NOT NULL,
PRIMARY KEY(paymentId),
FOREIGN KEY(bookingId) REFERENCES GROUPA5HolidayBooking(bookingId));
INSERT INTO GROUPA5Payment
VALUES ('DP666','30-Jan-07','Cash','LK112');
INSERT INTO GROUPA5Payment
VALUES ('DP667','30-Oct-04','Credit Card','LK114');
INSERT INTO GROUPA5Payment
VALUES ('DP668','27-Feb-01','Cash','LK115');
INSERT INTO GROUPA5Payment
VALUES ('DP669','14-Apr-07','Cheque','LK113');
INSERT INTO GROUPA5Payment
VALUES ('DP670','19-Mar-04','Cash','CU111','LK116');
COMMIT;
SCHEDULED FLIGHT
CREATE TABLE GROUPA5ScheduledFlight(
scheduledFlightCode CHAR(9) NOT NULL,
desAirportCode CHAR(5) NOT NULL,
orgAirportCode CHAR(5) NOT NULL,
depatureDate DATE NOT NULL,
departureTime CHAR(5) NOT NULL,
duration VARCHAR(12) NOT NULL,
airlineNo CHAR(5) NOT NULL,
PRIMARY KEY (scheduledFlightCode),
FOREIGN KEY(airlineNo) REFERENCES GROUPA5Airline(airlineNo),
FOREIGN KEY(desAirportCode) REFERENCES GROUPA5Airport(airportCode),
FOREIGN KEY(orgAirportCode) REFERENCES GROUPA5Airport(airportCode));
INSERT INTO GROUPA5ScheduledFlight
VALUES ('NN001','CDK12','XYZ13','12-Jul-07','11:20','3hrs','ULL86');
INSERT INTO GROUPA5ScheduledFlight
VALUES ('NN002','HHK14','DDL15','08-Mar-06','08:35','4hrs 15mins','SQL44');
INSERT INTO GROUPA5ScheduledFlight
VALUES ('NN003','XYZ13','HQZ16','30-Nov-07','21:30','11hrs 40mins','NSL86');
INSERT INTO GROUPA5ScheduledFlight
VALUES ('NN004','DDL15','CDK12','31-Jan-07','10:40','7hrs 30mins','ULL54');
INSERT INTO GROUPA5ScheduledFlight
VALUES ('NN005','HQZ16','HHK14','20-Apr-99','05:28','9hrs','SQL58');
COMMIT;
HOTELBOOKING
CREATE TABLE GROUPA5HotelBooking(
bookingId CHAR(5) NOT NULL,
hotelId CHAR(5) NOT NULL,
checkInDate DATE NOT NULL,
duration VARCHAR(12) NOT NULL,
roomType VARCHAR(10) NOT NULL,
PRIMARY KEY (bookingId,hotelId),
FOREIGN KEY (bookingId) REFERENCES GROUPA5HolidayBooking(bookingId),
FOREIGN KEY (hotelId) REFERENCES GROUPA5Hotel(hotelId));
INSERT INTO GROUPA5HotelBooking
VALUES ('LK114','HIL01','06-Mar-08','2 Days','Single');
INSERT INTO GROUPA5HotelBooking
VALUES ('LK112','TAJ01','07-Apr-08','6 Days','Double');
INSERT INTO GROUPA5HotelBooking
VALUES ('LK116','HIL02','08-Dec-08','7 Days','Single');
INSERT INTO GROUPA5HotelBooking
VALUES ('LK113','HIL01','09-Feb-08','8 Days','Double');
INSERT INTO GROUPA5HotelBooking
VALUES ('LK115','TAJ02','10-Sep-08','11 DAYS','Single');
COMMIT;
FLIGHT BOOKING
CREATE TABLE GROUPA5FlightBooking(
bookingId CHAR(5) NOT NULL,
scheduledFlightCode CHAR(9) NOT NULL,
flyingClass VARCHAR(25) NOT NULL,
bookingDate DATE NOT NULL,
PRIMARY KEY (bookingId, scheduledFlightCode),
FOREIGN KEY (bookingId) REFERENCES GROUPA5HolidayBooking(bookingId),
FOREIGN KEY (scheduledFlightCode) REFERENCES GROUPA5ScheduledFlight(scheduledFlightCode));
INSERT INTO GROUPA5FlightBooking
VALUES ('LK113','NN003','Business' ,'07-Mar-2008');
INSERT INTO GROUPA5FlightBooking
VALUES ('LK115','NN005','Economic','06-Apr-2008');
INSERT INTO GROUPA5FlightBooking
VALUES ('LK112','NN001','Economic','13-Jun-2004');
INSERT INTO GROUPA5FlightBooking
VALUES ('LK113','NN005','Business','07-APR-08');
INSERT INTO GROUPA5FlightBooking
VALUES ('LK116','NN004','FirstClass','09-Jan-2008');
COMMIT;
HOTEL TELNO
CREATE TABLE GROUPA5HotelTelNo(
hotelId CHAR(5) NOT NULL,
telNo NUMBER(10) NOT NULL,
PRIMARY KEY (hotelId),
FOREIGN KEY (hotelId) REFERENCES GROUPA5Hotel(hotelId));
INSERT INTO GROUPA5HotelTelNo
VALUES ('HIL01',1325647869);
INSERT INTO GROUPA5HotelTelNo
VALUES ('TAJ02',1548904538);
INSERT INTO GROUPA5HotelTelNo
VALUES ('HOL01',2436578956);
INSERT INTO GROUPA5HotelTelNo
VALUES ('HIL02',4563895630);
INSERT INTO GROUPA5HotelTelNo
VALUES ('TAJ01',3425674868);
COMMIT;
Part B
Question 1 (a)- Creating tables
Student
CREATE TABLE GROUPA5Student(
studentNo CHAR(5) NOT NULL,
name VARCHAR(20) NOT NULL,
street VARCHAR(20) NOT NULL,
town VARCHAR(10) NOT NULL,
country VARCHAR(15) NOT NULL,
postcode CHAR(6) NOT NULL,
email VARCHAR(20) NOT NULL,
PRIMARY KEY(studentNo));
Tutor
CREATE TABLE GROUPA5Tutor(
tutorId CHAR(5) NOT NULL,
forename VARCHAR(20) NOT NULL,
surname VARCHAR(20) NOT NULL,
telNo CHAR(12) NOT NULL,
qualific VARCHAR(5) NOT NULL,
employDate DATE NOT NULL,
introducedBy CHAR(5),
PRIMARY KEY(tutorId),
FOREIGN KEY(introducedBy) REFERENCES GROUPA5Tutor(tutorId));
Classroom
CREATE TABLE GROUPA5Classroom(
roomNo CHAR(5) NOT NULL,
location VARCHAR(15) NOT NULL,
seatingCapacity NUMBER(3) NOT NULL,
PRIMARY KEY(roomNo));
Course
CREATE TABLE GROUPA5Course(
courseCode CHAR(7) NOT NULL,
courseName VARCHAR(20) NOT NULL,
lengthHours NUMBER(2) NOT NULL,
tutionFee NUMBER(6,2) NOT NULL,
tutorId CHAR(5) NOT NULL,
roomNo CHAR(5),
PRIMARY KEY(courseCode),
FOREIGN KEY(tutorId) REFERENCES GROUPA5Tutor (tutorId),
FOREIGN KEY(roomNo) REFERENCES GROUPA5Classroom(roomNo));
Enrolment
CREATE TABLE GROUPA5Enrolment(
studentNo CHAR(5) NOT NULL,
courseCode CHAR(7) NOT NULL,
enrolmentDate DATE NOT NULL,
grade CHAR(1),
PRIMARY KEY(studentNo, courseCode),
FOREIGN KEY (studentNo) REFERENCES GROUPA5Student(studentNo),
FOREIGN KEY(courseCode) REFERENCES GROUPA5Course(courseCode));
Question 2 (b) - Inserting data into tables
Question 2 (c) – Insert statements
Insert statements for table GROUPA5Student
INSERT INTO GROUPA5Student
VALUES('C2345','Dan Smith','4 Avery Hill','Hatfield','Herts','HA15RT','[email protected]');
INSERT INTO GROUPA5Student
VALUES('E3456','Glen Boss','29 Catford Street','Bromley','Kent','BR25RB','[email protected]');
INSERT INTO GROUPA5Student
VALUES('G4567','James West','11 Kings Avenue','Talworth','Surrey','SW18PT','[email protected]');
INSERT INTO GROUPA5Student
VALUES('H7654','Joe Lees','96 George Street','Bromley','Kent','BR63EF','[email protected]');
INSERT INTO GROUPA5Student
VALUES('J8764','Tim Jones','111 The Ash','Bromley','Kent','BR48HA','[email protected]');
INSERT INTO GROUPA5Student
VALUES('H9567','Abraham Lingon','98 Queen Street','Mahawatte','Kandy','IJ89HJ','[email protected]');
INSERT INTO GROUPA5Student
VALUES('K2342','Ann Drew','67, Moor Street','Hill','Colombo','UI88UY','[email protected]');
INSERT INTO GROUPA5Student
VALUES('P8790','Lola Morton','45 Sea Avenue','Portsmouth','Moscow','SW87GH','[email protected]');
INSERT INTO GROUPA5Student
VALUES('D7876','Frank Ranger','87 Chapel Street','Ash Town','Kent','NM76JH','[email protected]');
INSERT INTO GROUPA5Student
VALUES('I9845','Morton Miller','111 Level Road','Venice','Italy','TY45KI','[email protected]');
COMMIT;
Insert statements for table GROUPA5Tutor
INSERT INTO GROUPA5Tutor
VALUES('AA123','Andrew','Anders','020-89115001','MSc','25-Apr-1999','');
INSERT INTO GROUPA5Tutor
VALUES('BB456','Belinda','Brown','020-89111234','BA','02-Jun-2002','');
INSERT INTO GROUPA5Tutor
VALUES('CC789','Cathy','Cane','020-89113456','MBA','31-Mar-2004','BB456');
INSERT INTO GROUPA5Tutor
VALUES('JH246','James','Harding','020-79115555','MSc','01-Dec-1996','');
INSERT INTO GROUPA5Tutor
VALUES('MP135','Marie','Powell','020-79115488','PhD','11-Mar-1997','JH246');
INSERT INTO GROUPA5Tutor
VALUES('LK654','Andy','William','020-09785632','MSc','08-Jul-1978','');
INSERT INTO GROUPA5Tutor
VALUES('UL509','Sardor','Mohamed','020-94783629','BA','19-Feb-1986','LK654');
INSERT INTO GROUPA5Tutor
VALUES('QR868','Mujeeb','Refath','020-93028766','MSc','16-Sep-1996','');
INSERT INTO GROUPA5Tutor
VALUES('QR301','Jebez','Solomon','020-98148734','BA','10-Oct-1984','UL509');
INSERT INTO GROUPA5Tutor
VALUES('FY678','Malathy','Muruhaiya','020-87239812','PhD','27-Nov-1987','');
COMMIT;
Insert statements for table GROUPA5Classroom
INSERT INTO GROUPA5Classroom
VALUES('H2.07','Harrow',50);
INSERT INTO GROUPA5Classroom
VALUES('E1.01','Euston',40);
INSERT INTO GROUPA5Classroom
VALUES('E2.09','Euston',20);
INSERT INTO GROUPA5Classroom
VALUES('E3.04','Euston',40);
INSERT INTO GROUPA5Classroom
VALUES('C1.02','Cavendish',40);
INSERT INTO GROUPA5Classroom
VALUES('U9.06','Georgia',20);
INSERT INTO GROUPA5Classroom
VALUES('Y8.04','Portsmouth',50);
INSERT INTO GROUPA5Classroom
VALUES('I6.05','Portsmouth',20);
INSERT INTO GROUPA5Classroom
VALUES('R7.02','Torronto',30);
INSERT INTO GROUPA5Classroom
VALUES('B1.09','Georgia',40);
COMMIT;
Insert statements for table GROUPA5Course
INSERT INTO GROUPA5Course
VALUES ('3SCC444','Systems Analysis',20,500.00,'JH246','E3.04');
INSERT INTO GROUPA5Course
VALUES ('3SCC555','Web Design',10,250.00,'MP135','C1.02');
INSERT INTO GROUPA5COURSE
VALUES ('3SCC666','Database Management',30,750.00,'JH246','H2.07');
INSERT INTO GROUPA5COURSE
VALUES ('3SCC777','Designing OO Systems',25,600.00,'AA123','C1.02');
INSERT INTO GROUPA5COURSE
VALUES ('3SCC888','C++ Programming',20,900.00,'CC789','H2.07');
INSERT INTO GROUPA5Course
VALUES ('3SCC222','Software Engineering',40,800.00,'QR868','U9.06');
INSERT INTO GROUPA5Course
VALUES ('3SCC333','Object Oriented',30,650.00,'MP135','I6.05');
INSERT INTO GROUPA5COURSE
VALUES ('3SCC111','Java',25,600.00,'JH246','H2.07');
INSERT INTO GROUPA5COURSE
VALUES ('3SCC999','Internet Programming',35,550.00,'QR301','C1.02');
INSERT INTO GROUPA5COURSE
VALUES ('3SCC123','Business Practice',40,900.00,'CC789','Y8.04');
COMMIT;
Insert statements for table GROUPA5Enrolment
INSERT INTO GROUPA5Enrolment
VALUES ('C2345','3SCC555','15-Mar-2006','A');
INSERT INTO GROUPA5Enrolment
VALUES ('E3456','3SCC444','05-Feb-2006','C');
INSERT INTO GROUPA5Enrolment
VALUES ('G4567','3SCC444','15-Jul-2006','');
INSERT INTO GROUPA5Enrolment
VALUES ('E3456','3SCC666','11-Feb-2007','B');
INSERT INTO GROUPA5Enrolment
VALUES ('J8764','3SCC666','21-Mar-2007','F');
INSERT INTO GROUPA5Enrolment
VALUES ('E3456','3SCC333','18-Oct-1966','C');
INSERT INTO GROUPA5Enrolment
VALUES ('E3456','3SCC888','05-Feb-2006','C');
INSERT INTO GROUPA5Enrolment
VALUES ('G4567','3SCC999','07-Nov-1983','');
INSERT INTO GROUPA5Enrolment
VALUES ('H7654','3SCC666','21-Jan-2000','F');
INSERT INTO GROUPA5Enrolment
VALUES ('J8764','3SCC111','21-Mar-2007','F');
COMMIT;
Viewing the tables
GROUPA5Student
GROUPA5Tutor
GROUPA5Course
GROUPA5Classroom
GROUPA5Enrolment
QUERIES
Question 3
Find out the number of courses that have been taken by each student. For each student, give their name and number of courses enrolled on.list the students in alphabetical order.
Question 4
Calculate the average tuition fee of all courses held in ‘Cavendish’.
Question 5
List the details of all courses related to Design, which cost less than 600.make sure that the word “Design” is in the course name.
Question 6
Find out how many tutors teach courses that are less than 10 hours long.
Question 7
Give the following details of the “data base management” course :
Course code, tuition fee, room number, room location and tutor name.
Question 8
For each tutor who teaches more than one course,find out the number of courses taught and the total duration/lenghth of the courses. the following columns are required in the output:
Tutor forename and surname,number of courses and total duration hours.
Question 9
List the detail of all tutors who have taught “Glen Boss’, and also show the course name in the output
Question 10
List the details of courses allocated the classroom with the highest seating capacity
Question 11
Creative a view called GoodStudents, providing details of all students who have achieved a grade of either ‘A’or ‘B’ include the following columns: student number,student name,course code,course name and grade
Question 12
Add an attribute called ‘dateOfBirth’ to the student table.
Enter a date of birth for each student
Altering the table GROUPA5Student
Inserting data from date of birth for each student in GROUPA5Student
Table GROUPA5Student
Question 13
Write an update query to increase the tuition fee of all courses taught by ‘Marie Powell’
By 25% Assume that you do not know Marie Powell’s tutor id.
Table GROUPA5Course
Question 14
Write an update query to delete any record in the Enrolment table where a grade has not been entered.
Table GROUPA5Enrolment
Question 15
Draw an ER diagram representing the entities and relationships given the relational schema above.
