代做INFO90002程序设计、代写SQL程序、SQL编程语言代写

日期：2021-07-09 09:22

School of Computing and Information Systems </br>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.