COMP1350 Practice Exam
Introduction to Database Design and Development
Exam Instructions:
1. You should be able to complete the exam within 3 hours. However, for the exam window, you
will have up to 6 hours to submit the PDF file of your exam answers to Turnitin. The exam
window will automatically close after the 6 hours.
2. This exam is graded out of 100 marks and is worth 50%. This is not a hurdle exam.
3. It is an individual take-home exam which means you will have access to resources of your choice
to answer the questions. However, you would not need access to any resources other than what
has been provided to you on iLearn.
4. There are three sections. Attempt ALL questions in the Three (3) sections. The first section
(Section-A) is about database modelling, the second section (Section-B) is about database
development, and the third section has some questions about database concepts (Section-C).
5. Please use the answer template file provided for you in iLearn to write your answers for all three
sections. DO NOT change the order of the questions in the template provided.
6. Make sure your answers are in the same order as the questions listed in this question paper.
7. Convert your answer file to a PDF document before submitting it to the exam Turnitin link on
iLearn.
Section-A: Question 1 in Section A will require you to use lucid charts/draw.io to draw diagrams to
present your answer. Please ensure you sign in using your student email account to login and work on
these diagrams. To add these images in your document, you will have to export your diagrams as Images
and add them to your answer document. Illegible diagrams will not be considered and no second
attempts will be provided. It is your responsibility to ensure that the diagrams are legible when zoomed
in. You may choose to hand draw and upload the image in the word document. Question 2,3 can be
typed-up.
Section-B: An sql script for Section B is provided on iLearn; You should be able to run the script on
MySQL Workbench to double-check your queries. You may choose to use the tables listed under Section
B to write your queries. Code execution is not compulsory. But highly recommended.
Section-C: Based on your understanding of the concepts, you will have to answer the questions. You do
not have to cite/use any external sources. Please refrain from defining terms as you would be tested
based on your answer.
Case Study:
Dracarys Airlines have been in airline business for the last 80 years. They started storing data in a filebased system and then transitioned into using spreadsheets. As years progressed, their business has
grown exponentially leading to an increase in number of customers and volume of data. You are
employed to develop a database model to replace the current spreadsheets.
You have been provided with the following business rules about Dracarys Airlines.
Aircraft & Aircraft Maintenance: Every aircraft is provided with a unique identifier. Other details of
an aircraft include the date the aircraft was purchased and its seat configuration description. There are
two types of aircraft- Short-haul and Long-haul. Each aircraft is a particular type of aircraft model. Every
aircraft model is identified by a unique identifier. Other details of an aircraft model include a name and
a seating capacity. An aircraft gets serviced in an available hangar. Each hangar is identified by a unique
identifier. Other details of a hangar include an address and a phone number. Aircrafts are serviced by
servicing teams. Every servicing team is identified by a unique identifier. Other details of a servicing
team include the name of the team lead and their contact number. An aircraft gets serviced at a hangar
location by a service team. The date of service and any comments are recorded for every service.
Flights: Every flight has a unique identifier. Other details of a flight include attributes like the departure
time of the flight. Flights can be either Short-haul or Long-haul. Short-haul aircrafts are rostered for
Short-haul flights only, and Long-haul aircrafts are rostered for Long-haul flights only. In both scenarios
mentioned above, an aircraft can be used for multiple flights and a flight uses only one aircraft at a time.
However, when that aircraft is to be serviced, then another aircraft will need to be allocated to that
flight. So, to keep a track of aircraft and flight allocation, the date range (start date and end date) of the
allocation needs to be stored as well.
Routing: A flight is assigned to fly on a specific route. A route could have multiple flights assigned to it.
Every route is identified by a unique identifier. Other details of a route include a route description and
a priority number. A route will have one starting point, one destination, and may contain multiple
stopover points. The starting point, destination and stopover points are all locations. Every location
needs to have details like country and is identified by an airport code. A scheduled flight is identified by
a unique identifier and gets created every time the flight makes a route. For this assigned schedule for
a flight, there are details needed such as flight identifier, date of flight, average speed, average height,
duration and fuel required.
Staffing: Every staff member is provided with a unique staff number for Dracarys Airlines. The
company also needs to keep track of other details about their staff members like their name and
address. There are two types of staff that need to be tracked in the system: Pilots and Flight Attendants.
For pilots, their pilot rank and total flight hours need to be recorded. For flight attendants, their
certificate number and certification level need to be recorded. Every scheduled flight has one captain
(who is a pilot), one second pilot (who is a pilot), additional pilots (this is optional and can be multiple).
The scheduled flight will also have at least one flight attendant as a matter of policy.
Ticketing & Customer details: Every ticket has a unique code. A ticket also has other information to
be captured such as: the date the ticket was generated, customer it is generated for, seat number, class
and its corresponding cost are stored. A ticket is generated for a scheduled flight and for only 1
customer. Every customer needs to be uniquely identified in the system. Other details which the
system needs to sore about a customer include: their name, address and phone number. A customer
could be part of the Airline’s loyalty program. These loyalty program customers would also have their
frequent flyer number and their join date recorded in the system.
Some further clarification of concepts used by Dracarys Airlines
In many different industries, there are terms which are used to represent certain concepts. As part of
this assignment, there are a few terms which Dracarys Airlines use to represent certain concepts. Below
are some clarifications which they have provided for new people who are joining the company. The
clarifications are being provided here to help you in your task.
What do Dracarys Airlines consider as the difference between a Flight, an Aircraft, and a model?
Aircraft refers to the plane / vehicle itself whereas a flight is the number that is allocated by the airlines.
Example of aircraft is ‘GH6579’ whereas flight is ‘QF400’ and it can be of model ‘Boeing 737’
What do Dracarys Airlines consider as the difference between a Route and a Flight?
Route 1 could be (Sydney-Melbourne) and Route 2 could be (Sydney-Brisbane-Melbourne) whereas
Flight ‘QF400’ will always fly Route 1.
What do Dracarys Airlines consider as the difference between a Flight and a Scheduled Flight?
Flight ‘QF400’ could have 3 scheduled flights (SF1, SF2, SF3) rostered on 3 different dates & other
attributes that are dependent on the day of scheduled flight
Section-A (40 marks)
1. Based on the business rules and data needs outlined in the case study, construct an EER model.
This model should include entities, attributes, primary identifiers, and the relationships among
entities with cardinalities and constraints. If any inheritance relationships are used, they must
show total/partial and overlap/disjoint constraints. You must explain your choice of
total/partial and overlap/disjoint constraints for each of the relationship. You may need to
introduce additional entities also to ensure every data requirement is captured. Data types for
attributes are not required. State any assumptions if you think something is not clear or has not
been addressed.
(Please remember that you may be tested on some of the concepts such as using an example to
explain concepts such as an identifier attribute/overlap & disjoint constraints)
(25 marks)
2. Transform your diagram (only the entities, attributes and relationships relating to the case study
section Aircraft & Aircraft Maintenance) into logical tables using the steps outlined in lectures.
For step 8, use the 8a approach. Show what is done in each of the steps with inclusion of primary
and foreign keys and a final list of tables.
(9 marks)
3. Normalise the table below into 2NF relations. How would an insertion anomaly work in this table
(6 marks)

Section-B (35 marks)
A ‘.sql’ file has been provided for you to download so you can write and test your sql queries for SectionB. Using the tables and data descriptions provided below, answer the questions below. Put your final
answers into the answer document you use to answer Section-A and Section-C. You do not have to show
the execution results of your queries. Each question in this section is worth 5 marks.

Please note you are not allowed to use ‘Natural Join’ keyword for any of the questions
1. Write a query to print the details of the aircraft (id and purchase date) if the aircrafts have more
than 150 seats. Only include aircrafts that have been purchased in either October of any year or
in any months in year 2014 or 2016. Sort the results by number of seats in descending order.
2. Write a query to print the id of the aircraft that are of type ‘Airbus’
3. Write a query to print the details of the aircraft (id and name of the aircraft type) if the aircraft
has been serviced at ‘H4’. Sort the results by the name of the aircraft type. Also remove any
duplicate results, if there are any
4. Write a query to print all details of the service if the aircraft has been serviced at any hangar in
NSW in the third quarter of 2019. You have to use subquery to derive at your answer
5. Rewrite Task 4 in join format.
6. Write a query to print the team number, their seniority level and number of services they have
performed. Only include teams that have a seniority level of either 1 or 3 and have less than 4
services performed. Sort the results by the number of services in descending order. Please note
that if a particular team has not serviced at all, you have to include them in the results as well.
7. Write a query to print number of aircrafts that have been serviced, if the aircrafts were
purchased after 2017 or if the seating capacity is not equal to 104.
Section- C (25 marks)
1. Consider any e-commerce platform. Provide an example of a static query and a dynamic query
(5 marks)
2. What are stored procedures and triggers? Provide an example of when each of them would be
implemented? You may use the context of Macquarie University here.
(10 marks)
3. How can Macquarie University use a data warehouse? Provide examples of different data marts
they may have.
(10 marks)
———————-
That’s it! End of the exam questions!
Be sure to double check you have attempted all the questions and included all of you
answers (in question order) for Section-A, Section-B, and Section-C in the same answer
document.
Remember to convert your document to a PDF file before submitting it to the Turnitin
link in the exam section on iLearn.