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. (30 marks)

Q2. SQL                       (20 marks)

Q3.  Normalisation           (16 marks)

Q.3A. The table shown below is part of an office inventory database. Identify the design problems and draw a revised table structure in 3rd Normal Form (3NF) that corrects those problems. For each step explicitly identify and discuss which normal form is violated.

(Key: PK = Bold FK = Italic PFK = Bold + Italic)

Inventory (ItemID, Description, Quan, Cost/Unit, Dept, DeptName, DeptHead)

ItemID is the candidate key for this table.

The following functional dependencies hold:

Dept ⟶ DeptName and DeptHead

Quan, Cost/Unit ⟶ InventoryValue

Table 1. The Inventory table (10 marks)

Q.3B. Given the following relation (Inventory), and its functional dependencies - is it possible to demonstrate Armstrong's Axioms of Reflexivity, Augmentation and Transitivity?

Inventory (ItemID, Description, Quan, Cost/Unit, Dept, DeptName, DeptHead)

ItemID is the candidate key for this table.

The following functional dependencies hold:

Dept ⟶ DeptName and DeptHead

ItemID ⟶ Description, Cost/Unit

Quan, Cost/Unit ⟶ InventoryValue (6 marks)

Q4. Data Warehousing                                       (10 marks)

Q4A. Transurban operates toll roads in Australia and the United States of America. Traffic data analysis helps  determine  road  maintenance,  RFID  reader  maintenance, gantry  maintenance  and  capacity management to reduce inefficiencies in its tollway network.

Each part of the tollway is identified by a section (E3), and multiple sections (E1, E2, E3) make up a chunk, (C3) and multiple Chunks make up an entire roadway ("Eastlink"). Many different vehicles on toll roads every day. Each vehicle falls into one category: motorcycles, passenger vehicles, 4WD's, vans, trucks, prime movers, buses, trailers, recreational and miscellaneous machinery (e.g. tractors, cranes, street sweepers, back hoes).

Transurban's management wants to understand the vehicle trips on its road network. They need to understand the number of trips, trip length, trip duration, at different times of the day, on different days and during different period (e.g. Easter holidays, school holidays).

Draw a star schema to support the design of this data warehouse, showing the attributes in each table. You do not need to select data types. Clearly display the legend for Primary Key, Foreign Key and Primary Foreign Key. (8 marks)

Q.4B. Why are star schemas preferred over relational database designs to support decision making?  (2 marks)

Q5. Security and Backups                                    (10 marks)

Q5A. One of the technical safeguards of database systems is access control. Which type of access is based on the “need to know” principle? Explain how this principle works with an example. (4 marks)

Q5B. Learning Management Systems use Role-based Access Control (RBAC). Explain this statement.   ( 2marks)

Q5C Illustrate the concept of an SQL injection. Describe a scenario and a string a hacker could enter that would jeopardise database security. (4 marks)

Q6. Transactions       (10 marks)