School of Computing and Information Systems
INFO90002
Database Systems and Information Modelling
Practice Exam 2 SOLUTIONS
Semester 1 2021
Reading Time: 30 minutes
Writing Time: 120 minutes (2 Hours)
This exam has 8 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 S2 2020 Practice Exam No 2
2021 University 0f Melbourne
Instructions to Students:
This exam is in 8 sections. Attempt all questions in all sections.
We recommend using pencil and paper for modelling questions to save time
The total for this exam is 100 marks representing 50% of your final assessment
Attempt all questions which are of unequal marks value
This exam is a timed assessment which must be completed within 150 minutes of
official commencement time
Questions can be answered in any order (please number your attempts)
Start a new question on a new page
PLEASE DO NOT USE RED font colour
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 PDF document before the elapsed time.
No other document format will be assessed (e.g. Pages, doc, 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.
If you choose to upload photos, ensure that they are clear, compressed to < 1mb, and
importantly, check them once you have uploaded. It is your responsibility to ensure your
file and any images has been submitted successfully.
The work you submit must be based on your own knowledge and skills and without the assistance of any other
person. You MUST NOT directly copy work that you have not authored (e.g. slide notes, websites, other student's
study notes).
INFO90002 S2 2020 Practice Exam No 2
2021 University 0f Melbourne
3
Q1. ER Modelling (30 Marks)
TeaTime
TeaTime is a platform for tea lovers. To use the system, users must install the TeaTime app on their
phone and set up a user profile that contains: a login name (their email address), password (encrypted
and stored as a string of 64 characters), short screen name e.g. "DonkeyKong64" (which other users
will see), profile photo, and a “My Cuppa” entry - their favourite tea type, chosen from a list of
standard drink names such as “Peppermint Tea” and “Black Tea with Honey”. We plan to have up to
2 million customers (end users) accessing the system.
While a user’s phone is switched on, the TeaTime app sends the phone’s current location to the server
once per minute. We store these, so that the system knows where a given user is “now”, as well as
the history of where the user has been.
All locations in this system 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 in Greenwich). We will use a precision of 4
decimal places, which is about 11 metres at the equator. For example, the Doug McDonell building at
UniMelb is at latitude -37.7989, longitude 144.9627.
The names, locations and opening hours of about 600 tea stores are available via the app. Users can
browse stores either in an alphabetical list or via a map. When viewing a store's profile, users can see
reviews and photos uploaded by other users, and the store's average rating. The store's opening hours
are recorded as an opening and closing time for each day of the week (for example, a café might open
Mondays 8am to 6pm, Tuesdays 9:30am to 7pm, and so on). Each café stores a menu of the teas it
sells: these must be drawn from our standard list mentioned above.
Users can rate stores. A rating consists of a whole number between 0 and 10, along with an optional
piece of text (up to about 30 words). A given user can only rate a given tea store once. Users can mark
particular stores as favourites. These can be viewed in a list. Users can later “unfavourite” the tea
store if they wish, and yet later “favourite” it again. We keep a history of these favourites and
unfavourites. Customers can use the app to order drinks from a tea store. To do this a user first selects
a tea store, then chooses how many of each tea type(s) they want from the store's menu. We keep
track of when orders are placed and when customers later pick up the order.
Q.1. Draw a physical model in Crow’s Foot notation for this case study. Be sure to write down any
assumptions you make.