2025¶
Faculty of Science, Technology, Engineering and Mathematics
School of Computer Science & Statistics
| Programmes | Integrated Computer Science · Joint Honors · Management Science and Information Systems Studies |
| Semester | 2 |
| Date | 24th April 2025 |
| Time | 09:30 – 11:30 |
| Examiner | Prof Yvette Graham |
| Materials | None permitted |
Instructions to Candidates¶
- Attempt three questions in total. Question 1 is mandatory. Answer any two from Questions 2, 3, and 4.
- All questions carry equal marks — each is scored out of 25 marks.
- Do not begin the exam until instructed to do so by an invigilator.
- Materials permitted: None
Question 1 [25 marks]¶
A relational database is required to keep track of transport ships and their locations for maritime authorities, recording the movement of ships between ports and the current location of a given ship. The database should keep track of the types of ships stored in the database by recording the tonnage and hull type of each ship, which can be any one of the following: flat-bottom, round-bottom, v-shaped, or multi-hulled. The database also needs to record information about the ports that each ship visits, including the country the port is located in, and the continent of that country, in addition to the sea, ocean or lake it is located on. Each ship will have a home port, the port it is ordinarily docked in. Each visit to a port by a ship is recorded as well as the port at which a given ship is docked at any given time (it can alternatively be in transit). The current location of a ship is recorded at 5 minute intervals, and at any point in time, the database is able to retrieve a complete ship movement history for any ship in the database.
(i) ER Model — [10 marks]¶
Provide a suitable Entity Relationship schema for the above SHIP_TRACKING database. Make sure to state any assumptions you make.
(ii) Relational Mapping — [15 marks]¶
Map the ER schema into a relational database schema and specify all primary and foreign keys. Again, state any assumptions you make.
Question 2 [25 marks]¶
A relational database is required to record airline flight information. Each flight should be identified by a flight number, and consists of one or more flight legs with leg numbers 1, 2, 3, and so on. Each flight leg has scheduled arrival and departure times, airports, and one or more leg instances, one for each date on which the flight travels. Fares are also recorded for each flight. For each flight leg instance, seat reservations are kept, as are the airplane used on the leg and the actual arrival and departure times and airports. An airplane is identified by an airplane id and is of a particular airplane type. A record needs to be kept of which airplane types are allowed to land at which airports, with distinct airports identified by a unique airport code. The relational schema is shown below.
AIRLINE FLIGHT Relational Database Schema¶
┌─────────────────────────────────────────────────────────────────┐
│ FLIGHT │
│ ────── │
│ FLIGHT_NO VARCHAR ★ PK │
│ AIRLINE VARCHAR │
└─────────────────────────────────────────────────────────────────┘
│
1 ────── many
│
┌─────────────────────────────────────────────────────────────────┐
│ FLIGHT_LEG │
│ ──────────── │
│ FLIGHT_NO VARCHAR ★ PK, FK → FLIGHT(FLIGHT_NO) │
│ LEG_NO INT ★ PK │
│ DEP_TIME TIME (scheduled) │
│ ARR_TIME TIME (scheduled) │
│ DEP_AIRPORT VARCHAR ★ PK, FK → AIRPORT(AIRPORT_CODE) │
│ ARR_AIRPORT VARCHAR ★ PK, FK → AIRPORT(AIRPORT_CODE) │
└─────────────────────────────────────────────────────────────────┘
│
1 ────── many
│
┌─────────────────────────────────────────────────────────────────┐
│ FLIGHT_LEG_INSTANCE │
│ ─────────────────── │
│ FLIGHT_NO VARCHAR ★ PK, FK → FLIGHT_LEG(FLIGHT_NO) │
│ LEG_NO INT ★ PK, FK → FLIGHT_LEG(LEG_NO) │
│ DATE DATE ★ PK │
│ DEP_TIME TIME (actual) │
│ ARR_TIME TIME (actual) │
│ DEP_AIRPORT VARCHAR ★ PK, FK → AIRPORT(AIRPORT_CODE) │
│ ARR_AIRPORT VARCHAR ★ PK, FK → AIRPORT(AIRPORT_CODE) │
│ AIRPLANE_ID VARCHAR FK → AIRPLANE(AIRPLANE_ID) │
└─────────────────────────────────────────────────────────────────┘
│
many ───┼─── 1
│
┌─────────────────────────────────────────────────────────────────┐
│ AIRPLANE │
│ ────────── │
│ AIRPLANE_ID VARCHAR ★ PK │
│ TYPE VARCHAR FK → AIRPLANE_TYPE(TYPE) │
└─────────────────────────────────────────────────────────────────┘
│
many ────┼── 1
│
┌─────────────────────────────────────────────────────────────────┐
│ AIRPLANE_TYPE │
│ ─────────────── │
│ TYPE VARCHAR ★ PK │
│ COMPANY VARCHAR │
│ MAX_SEATS INT │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ AIRPORT │
│ ──────── │
│ AIRPORT_CODE VARCHAR ★ PK │
│ NAME VARCHAR │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ ALLOWED_LANDING │
│ ───────────────── │
│ AIRPLANE_TYPE VARCHAR ★ PK, FK → AIRPLANE_TYPE(TYPE) │
│ AIRPORT_CODE VARCHAR ★ PK, FK → AIRPORT(AIRPORT_CODE) │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ FARE │
│ ────── │
│ FLIGHT_NO VARCHAR ★ PK, FK → FLIGHT(FLIGHT_NO) │
│ CLASS VARCHAR ★ PK │
│ AMOUNT DECIMAL │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ CUSTOMER │
│ ───────── │
│ CUSTOMER_ID VARCHAR ★ PK │
│ NAME VARCHAR │
│ PHONE VARCHAR │
└─────────────────────────────────────────────────────────────────┘
│
many ───┼─── 1
│
┌─────────────────────────────────────────────────────────────────┐
│ RESERVATION │
│ ─────────── │
│ FLIGHT_NO VARCHAR ★ PK, FK → FLIGHT_LEG_INSTANCE(...) │
│ LEG_NO INT ★ PK, FK → FLIGHT_LEG_INSTANCE(...) │
│ DATE DATE ★ PK, FK → FLIGHT_LEG_INSTANCE(...) │
│ DEP_AIRPORT VARCHAR ★ PK, FK → FLIGHT_LEG_INSTANCE(...) │
│ ARR_AIRPORT VARCHAR ★ PK, FK → FLIGHT_LEG_INSTANCE(...) │
│ SEAT_NO VARCHAR ★ PK │
│ CUSTOMER_ID VARCHAR FK → CUSTOMER(CUSTOMER_ID) │
└─────────────────────────────────────────────────────────────────┘
Legend: ★ PK = Primary Key · FK = Foreign Key
Specify the following queries in relational algebra for the above database.¶
(a) [5 marks]¶
For each flight, list the flight number, airline, date, departure airport and arrival airport along with the number of available seats, for each leg.
(b) [5 marks]¶
For all the airplane types available in the database, list the airplane type, name of the company, maximum seats available and the airports (or airport codes) where these planes can land.
© [5 marks]¶
List the name, seat number and phone numbers of all the customers of all flights or flight legs that departed from Houston Intercontinental Airport (airport code 'iah') and arrived in Los Angeles International Airport (airport code 'lax') on '2016-03-16'.
(d) [5 marks]¶
List fare information for all the flights run by the airline 'Delta Airlines'.
(e) [5 marks]¶
Retrieve the number of available seats on all flights run by Delta Airline, on '2016-04-09'.
Question 3 [25 marks]¶
The above relational database stores student and course information for a university where information about primary and foreign keys is not explicitly stated. Specify the following queries in SQL on the database schema.
UNIVERSITY Relational Database Schema¶
┌─────────────────────────────────────────────────────────────────┐
│ COURSE │
│ ──────── │
│ COURSE_ID VARCHAR ★ PK │
│ TITLE VARCHAR │
│ CREDITS INT │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ INSTRUCTOR │
│ ──────────── │
│ INST_ID VARCHAR ★ PK │
│ NAME VARCHAR │
│ DEPT VARCHAR │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ SECTION │
│ ──────── │
│ SECTION_ID VARCHAR ★ PK │
│ COURSE_ID VARCHAR FK → COURSE(COURSE_ID) │
│ INST_ID VARCHAR FK → INSTRUCTOR(INST_ID) │
│ SEMESTER VARCHAR │
│ YEAR INT │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ STUDENT │
│ ──────── │
│ STU_ID VARCHAR ★ PK │
│ NAME VARCHAR │
│ DOB DATE │
│ MAJOR VARCHAR │
│ CLASS INT │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ TAKES │
│ ────── │
│ STU_ID VARCHAR ★ PK, FK → STUDENT(STU_ID) │
│ SECTION_ID VARCHAR ★ PK, FK → SECTION(SECTION_ID) │
│ GRADE VARCHAR │
└─────────────────────────────────────────────────────────────────┘
Legend: ★ PK = Primary Key · FK = Foreign Key
(i) [4 marks]¶
Create each of the tables in the above schema. Making sure to appropriately apply constraints, include primary and foreign keys.
(ii) [4 marks]¶
Retrieve the names of all courses along with the name of the instructor taught during the fall of 2008.
(iii) [4 marks]¶
For each section taught by Professor Anderson, retrieve the course number, semester, year, and number of students who took the section.
(iv) [4 marks]¶
Retrieve the name and transcript of each junior student (Class = 1) majoring in mathematics (MATH). A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.
(v) [4 marks]¶
Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses).
(vi) [5 marks]¶
Retrieve the names and major departments of all students who do not have any grade of A in any of their courses.
Question 4 [25 marks]¶
(i) [5 marks]¶
Consider the following two transactions, T1 and T2, and their operations. Analyze the following operations and explain whether T2 operations are updated successfully when T1 is rollback.
(ii) [12 marks]¶
Does strict two-phase locking always guarantee strict schedules? Explain your answer in detail.
(iii) [3 marks]¶
Provide a definition for deadlock.
(iv) [5 marks]¶
Does cautious waiting avoid deadlock?
© Trinity College Dublin, The University of Dublin 2025