INFO90002 2019s2 Assignment One #1
INFO90002 - 2019 sem2 - Assignment One
“COFFEE CLUB”
Coffee Club is a social platform for coffee lovers. Users connect
via a smartphone app to the Coffee Club server. Your job is to
design a database that will support these software features.
Cafes
The names, addresses, map locations and opening hours of about
ten thousand cafes are visible in the app. Users can browse cafes
either via an alphabetical list or a map interface. Only cafes
located in Australia are covered in this app.
When viewing a café’s profile, a user can see the café’s average
rating, as well as reviews and photos uploaded by other users.
For each café we record the opening and closing time for each
of the seven days of the week. (For example, a café might open
Sundays 8am to 3pm, Mondays 9am to 7pm, and so on. We can
ignore exceptions like public holidays.)
Each café stores a menu of the drinks it sells and their prices.
Drink names must be drawn from a standard list of about 100
drinks maintained in the system. Some cafés may also sell other
items (such as cakes) but we don’t record these in our system.
Users
The app will have up to 20 million users. This assignment is concerned only with features for
customer users: café staff and system administrators will access the system in other ways.
To use the system, users must first install the Coffee Club app on their phone, then set up a
user profile that contains: a login name (email address), password (encrypted and stored as a
string of 64 characters), short screen name (a single string less than 20 characters long which
other users will see), and optional profile photo.
Each user can display a “My Brew”, which is their favourite coffee drink, chosen from the
list of standard drink names mentioned above. Users tend to change their choice of “My
Brew” over time: we want to keep track of these changes as the data yields valuable
intelligence about coffee preference trends.
Ratings, photos and favourites
Users can rate cafes. A rating consists of a whole number of “stars” between 1 and 5
inclusive, along with an optional piece of text (up to about 20 words). A user can only rate a
given café once. Users can also upload photos they take of a cafe: these can later be browsed
by other users. Users can also mark particular cafes as “favourites”. A user’s app will beep if
they pass within 500m of one of their favourite cafés, or if another user they are following
checks in at a favourite cafe. Users can later “unfavourite” the café if they wish, and still
later, “favourite” it again, and we need to keep a history of these actions.
INFO90002 2019s2 Assignment One #2
Posting to the Timeline
The Coffee Club app has a social media-style timeline, to which
users can post text and photos. A post consists of a short piece of
text (up to 1000 characters) with an optional photo. We expect
users to post once per day each on average.
Other users can “like” a post, and if they wish, later “unlike” it.
Users can also “comment” on posts, adding a short piece of text
(again up to 1000 characters). However users cannot comment on
comments: i.e. commenting is “depth one”.
The app allows users reading their newsfeed to sort the list of
posts, either by the time each post was entered, or by the number
of likes each post has received.
Social Graph
Users can ‘follow’ other users. The social graph works like
Twitter’s: user A can follow user B, without B needing to
approve, nor B automatically becoming a follower of A. Users might for example choose to
follow people they know offline, or celebrities, or expert members of the user community.
If a user decides to follow another, they can later choose to “unfollow” them if they wish: we
need to keep track of these actions for later analysis.
Locations
While a user’s phone is switched on, the Coffee Club app sends
the user’s current location to the server every minute. Locations
are recorded as a pair of numbers representing latitude and
longitude. We use a precision of 4 decimal places: for example,
the Doug McDonell building is at latitude -37.7990, longitude
144.9630.
Check-ins
Users can use their app to “check in” at cafes that they visit.
Optionally, the user can tag one other user in the checkin.
Checkin data is used to enable the user’s followers to see when
the user is present at the café. We mine checkin data periodically
for business intelligence.
News
The app has a ‘news’ page where users can browse system-
generated stories about significant events such as sales or parties.
A story consists of a heading and a short body of text (max 1000
characters) and a photo. Stories can be browsed either in date
order or by café. We allow customers to like news stories, but not
comment on them. (end of case)
INFO90002 2019s2 Assignment One #3
Business Queries that your model should support
Your database design needs to be able to solve the following business requirements.

1. Can we find the cafes that are closest to a particular user’s current location?
2. Can we find the most popular drinks, as indicated by how often a drink has been listed
as someone’s “My Brew”?
3. Does the system prevent a user from rating the same café more than once?
4. Can we calculate a café’s average rating?
5. Can we tell if a user has passed within 500
metres of one of their favourite cafes?
6. Can we find the ten most recently-uploaded
photos for a particular café?
7. Can we sort the social-media timeline by the
time each post was entered?
8. Can we find the ten most-liked social-media
posts?
9. Can we find the set of all users that a particular user is following?
10. Can we search through location records to check whether a particular user has ever
been near the Doug McDonell building?

Note 1: this list is not exhaustive. Read the case to identify all the business requirements.
Note 2: there is no need to submit SQL answers to these queries.