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.
