INFO90002 Practice Exam 1
© The University of Melbourne 2021-2022 1


School of Computing and Information Systems
INFO90002
Database Systems and Information Modelling
Practice Exam 1
Semester 1 2022
Reading Time: 15 minutes
Writing Time: 120 minutes (2 Hours)
This exam has 10 pages including this page
ATTEMPT ALL QUESTIONS IN ANY ORDER

Authorised Materials:
While you are undertaking this assessment, you are permitted to
• make use of any textbook, lecture slides (including soft copies)
• MySQL Workbench is supported for E.R. modelling questions
• Draw IO (diagrams.net) for Chen conceptual models or any suitable modelling equivalent
• Any lecture notes, books, laptop, PC
• You are free to use the course materials and your laptop/PC in this exam.
While you are undertaking this assessment, you MUST NOT
• make use of any messaging or communication technology
• record, screenshot, stream, upload or in any known format duplicate this document
• record, screenshot, stream, upload or in any known format duplicate your solutions
• make use of any world wide web or internet based resources such as wikipedia, github,
stackoverflow, google, Weichat or any known search engine / messaging services
• act in a manner that could be regarded as providing assistance to a student who is
undertaking this assessment or in the future will be undertaking this assessment
• seek assistance from any other student who is undertaking this assessment or in the future
will be undertaking this assessment

INFO90002 Practice Exam 1
© The University of Melbourne 2021-2022 2
Instructions to Students
• The total for this exam is 100 marks
• Attempt all 9 questions which are of unequal marks value
• Be sure to number your answers to ensure your questions is marked
• Questions can be answered in any order
• PLEASE DO NOT USE RED font colour or RED pens.
• You may revisit/edit your exam answers throughout
• You must not communicate with other students whilst taking this exam, e.g. using
messaging, chat rooms or email
IMPORTANT
• Your file upload must be a single Word document before the elapsed time.
• No other document format will be assessed (e.g. Pages, txt, .SQL, etc).
• Email submissions will not be assessed.
• Every question attempt must be numbered (e.g. Q2C, Q1, Q6) to ensure it is assessed.
• The official exam language is English. Sections of the submission in languages other than
English will NOT be assessed and will be marked as 0.
• Before submitting your solution document, check that the diagram(s) is/are readable. It is
your responsibility to ensure your answers are readable and make sense to the marker.

The work you submit must be based on your own knowledge and skills and without the assistance
of any other person. You must not copy directly the text from any material without attribution.

INFO90002 Practice Exam 1
© The University of Melbourne 2021-2022 3
Q1. ER Modelling (20 marks)
Q1. Criterion Classics is an on-demand streaming service that streams old television shows (e.g.
‘Skippy’) and movies (‘Lawrence of Arabia’), including black-and-white movies (‘Casablanca’) and
television shows ("I dream of Lucy"). Each Criterion Classics account can have multiple profiles
associated with it – for example, a family account could have one profile for the parents and one
profile for each child. Profiles of type ‘Child’ can only view titles classified as ‘U’ (Universal) and ‘G’
(General). Each profile is stored with an avatar or digital image. About each account we store a name
('Mary Lawson') the monthly subscription price, the number of concurrent screens allowed and the
day of the month (18th) the account is charged and the name of the subscription plan (Basic, Standard,
Premium).
Each movie is an individual title. TV shows can have one or more seasons and up to 24 episodes in a
season. Each title of a TV show (e.g. “Breaking Bad”) will also have episodes title (e.g. “Ozymandias”)
as well as season number (5) and episode number (14) as well as its running time 47:35 (minutes and
seconds) and classification ('MA').
Criterion Classics stores the length of each title/episode in minutes and seconds, as well as the elapsed
time a particular profile stopped watching the title/episode. This is so users can 'continue watching'
where they left off if they have to stop watching for any reason. If a user watches the title to the end,
the abandon time is equal to the title/episode length time.
By collecting and storing this information, Criterion Classics can make recommendations to other users
based on profile data analysis. Criterion Classics makes recommendations about rating appropriate
titles to all profiles. Criterion needs to store the name of the title, the date it was suggested to a profile
and the number of times (total count) a particular title has been recommended to a user. Using this
information and the viewing information of a profile it can determine if its suggested viewing
algorithm is working.

Q1. You are asked to model a conceptual Model of the Criterion Classics case study in Chen
notation. State any assumptions you have made.


INFO90002 Practice Exam 1
© The University of Melbourne 2021-2022 4
Q2. SQL DDL (10 marks)
The British Broadcasting Corporation (BBC) has a number of commissioning editors (e.g. Martin
Davidson) who have cast a number of landmark shows ("Life on Earth", "A History of Britain") from
independent production companies such as names like Hartswood films (Sherlock, Coupling) with
producer Sue Vertue. Sometimes more than one production company is involved in the production of
a series. Every Show is given a name (e.g. "Sherlock"), a number of episodes (between 6 and 24), a
budget (up to several hundred thousand pounds) and an estimated air date for the first episode. The
Producers must also nominate who is the target audience (Audiences are graded by age and income
demographic) and classify the show (e.g. Documentary, Drama, Comedy, Light Entertainment, Game
Show, Reality, News and Current Affairs, Special Event).

Q2. Write the SQL DDL for the following Chen conceptual model. There is no need to specify not null.
You can use whichever data types you think are appropriate for a MySQL relational database




INFO90002 Practice Exam 1
© The University of Melbourne 2021-2022 5
Q3. SQL (20 marks)
This case study is for the SQL model to help contextualise the Physical ER model
Lunch Rider
LunchRider is a new startup in the food delivery business. LunchRider allows people to order lunches
from local food vendors and have them delivered by a delivery rider on a bike. When a customer opens
the LunchRider app, it first presents a list of local vendors such as cafes, restaurants and snack bars.
The customer clicks on a vendor. Then the app shows the meals available from that vendor.

The customer chooses which meals they want from that vendor: for example “2 chili burgers meals"
and "1 mango smoothie”. The phone sends this order to the LunchRider server, along with the
customer's phone GPS coordinates - the meals will be delivered to this location. Customers can click
to “like” a meal and the total number of likes (across all customers) is displayed beside each meal.
When a customer’s order is received, LunchRider broadcasts a work offer to riders who are near the
customer. Riders see the offer pop up on their app and can press “accept” or “no thanks”.

The chosen rider goes to the vendor, picks up the meals, and delivers them to the customer’s location.
We record at what time the rider delivers the order. Payment is automatically deducted from the
customer’s credit card and bank transactions are handled by the bank. LunchRiders provide their
name, mobile phone number, and date of birth. Whenever a rider is on duty, the rider’s app sends the
GPS coordinates about once per minute, allowing us to keep track of each rider’s location.

If someone wants to order a meal, they need to first register as a customer, giving their name, email
address, mobile phone number and current credit card.Over time a customer may register more than
one credit card.Vendors must register the name and address of their business, including GPS
coordinates and a contact email address, and provide the name, price, description (max 1,000
characters) and photo of each meal that they want to sell.

After delivery, the app allows the customer to rate the rider’s service, choosing from 1 star (worst), 2,
3, 4 or 5 stars (best). The app also allows the customer to add the rider to their “favourite” list.
LunchRider uses this information to help choose riders for future work offers.

All locations are recorded as a pair of numbers representing latitude and longitude. Latitudes are
between -90 and 90 degrees (south pole to north pole) while longitudes are between -180 and 180
degrees (west or east of the prime meridian). LunchRider uses a precision of 4 decimal places, which
is about 11 metres at the equator (smaller in Melbourne). For example, the Doug McDonell building
at The University of Melbourne is at latitude -37.7989, longitude 144.9627.

INFO90002 Practice Exam 1
© The University of Melbourne 2021-2022 6

Figure 2: The Lunch Rider Entity Relationship Model

INFO90002 Practice Exam 1
© The University of Melbourne 2021-2022 7
Questions 4A-4E require you to write one single SQL statement per question. Do not use views,
temporary tables or inline views. Format code for ease of reading. Ensure user-friendly output by
renaming columns where appropriate. Values of column fields are in italics e.g. Tom Hardy
For Example:
Q. List the name and salary of Jane Grey
SELECT firstname, lastname, salary
FROM employee
WHERE firstname = 'Jane' and lastname = 'Grey';

Q3A. Write the SQL for the following query:
List all Vendor names who offer a meal where the price is greater than $60.00 and less than $80.00
( 1 marks)

Q3B. List the names of riders born in 1997 have accepted an offer in August 2020?
( 3 marks)

Q3C. How many customers have ordered and liked the 'Betty Deluxe Burger Meal' meal ordered from
the Melbourne store with coordinates latitude -37.8156 and longitude 144.9636 ?
(4 marks)
Q3D. List the names of riders who are also customers of the Lunch Rider app (assume the rider uses
the same phone number as they would as a customer)
(4 marks)
Q3E. List the vendor names who have never had any ordered meal liked by any customer
(3 marks)
Q3F.Name the riders who have received a rating of 4 or above from more than 500 different
customers
(5 marks)

PRACTICE EXAM Part 1
© The University of Melbourne 2021-2022 8
Q4. Normalisation (20 marks)
The table below is part of the medical records for a Veterinarian Clinic
PRACTICE (Animal_ID, Animal, Animal type, OWNER_ID, Owner, Phone, Consult, PROC_ID, PROC_DESC)

Animal
ID
Animal Animal Type Owner_ID Owner Phone Consult ProcID Description
317 Ralph Dog 10 Julie Sumner 0409 673-888 13-Oct-13 101 Annual Checkup
317 Ralph Dog 10 Julie Sumner 0409 673-888 27-Apr-13 115 Teeth Clean
317 Ralph Dog 10 Julie Sumner 0409 673-888 14-Oct-14 119 3 month Checkup
398 Zeno Canary 23 Tony Rijks 0408 322-444 21-Jul-14 105 Parasite treatment
398 Zeno Canary 23 Tony Rijks 0408 322-444 14-Oct-13 119 3 month Checkup
441 Panda Short haired cat 47 Helene Hanff 0419 121-212 24-Apr-14 715 Initial Consultation
441 Panda Short haired cat 47 Helene Hanff 0419 121-212 27-Apr-13 115 Teeth Clean
518 Zeno Canary 23 Tony Rijks 0408 322-444 1-Mar-15 001 6 month Checkup

The combination of ANIMAL_ID and PROC_ID is the candidate key for the relation. The following functional dependencies hold:
• Animal_ID → ANIMAL, ANIMAL Type, OWNER_ID
• OWNER_ID → OWNER, PHONE
• PROCID → DESCRIPTION
• ANIMAL_ID, PROC_ID → Consult

Q4. Please normalise the data to third normal form (3NF). At each stage give an example of an anomaly. Show each stage of normalisation (i.e. 1NF, 2NF,
3NF).
Key: BOLD primary key ITALIC foreign key BOLD + ITALIC primary foreign key


PRACTICE EXAM Part 1
© The University of Melbourne 2021-2022 9

Q5. NoSQL (4 marks)
Q5. There are trade-offs between the principles of ACID and BASE. Discuss the trade-off between
availability and consistency for relational and NoSQL databases. Illustrate your answer using either
the example of Facebook or the example of Twitter.
(4 marks)

Q6. Applications (4 marks)
Q6. There are problems with giving end users an SQL interface to access a database. Describe two (2)
distinct problems, and for each, how providing application software to users solves the problem.
(2+2=4 marks)

Q7. Distributed Databases (4 marks)
Q7. Describe how synchronous updates work and the advantages and disadvantages of this approach.
(4 marks)

Q8. Data Warehouses (14 Marks)
Sennheiser Australia wants to track information about the selling of audio equipment (microphones,
headphones, turntables) via physical retail stores located throughout Australia. You need to design a
data warehouse to report information about sales of audio equipment over time. You need to store
the retail store (store ID, Manager’s first name, Manager’s last name, phone number, address, state),
and product (product-id, name, description, category, retail price, wholesale price). The sales
managers want to find the number of items sold (e.g. turntables), the revenue and profit. The
information needs to be accessible by the retail store, its state, product name (e.g. “Sen-300-II”) and
for different times (week, month, quarter, half year and year).

Q9A. Draw a star schema to support the design of this data warehouse, showing the attributes in each
table. Be sure to denote PK, FK and PFK.
(8 marks)

Q9B. Briefly explain what OLTP and OLAP databases are and demonstrate the difference between
these two types of databases.
(6 marks)


PRACTICE EXAM Part 1
© The University of Melbourne 2021-2022 10
Q9. Security and Backups (4 marks)
Q9. What are the advantages and disadvantages of a logical backup when compared with a physical
backup of a database?
(4 marks)

END OF EXAM