Q3. Data Modelling (20 marks)

The local state government has decided to change how garbage and recycling collections are charged to households and businesses. Previously, all clients were charged a flat rate based on their property rate. The new approach will use onboard cart tipper truck scales to weigh each collected bin, and clients will then be charged for the weight and type of collection made.

The following describes the data to be stored in a new "Collections" system to support this user pays-by-weight approach.

Garbage and recycling collections are managed by local authorities such as cities and shires. Each local authority is assigned an id, has a chief executive officer (given name and family name) and a contact telephone number. The type of authority is also recorded (this must be shire, city or borough - this is a fixed set of values). Each authority has responsibilities for the streets in their local authority area. Each street is assigned an id and has a recorded length in meters, particular road surface and number of lanes.

Properties are located on streets. A property has a property id and a street number. Each property has recorded the owners' contact details:  name, email address and phone number. Each owner is assigned an owner id. For the purposes of the "collections" system, a property is only recorded as being owned by one owner. For each property, the system records when wheelie bins, of each type, are supplied/replaced (there are three types of bins supplied: garbage, green and recycle). A TYPE entity is not required, this will be handled by a check constraint in implementation in the DBMS. The bin data will help track misuse/theft of bins.

Collections schedules are created for each street – they are identified by a schedule id and include the frequency and type of the collection (for example recycle collections are made every second Friday). The type of collection will be garbage, green or recycle (again as listed above, a TYPE entity is not required).

When a scheduled collection is made at a property a street collection id is generated. The collection date is recorded, and the truck weighs and records the number of kilograms that were collected for that property in this collection.

(a) Complete the table below listing all entities and keys present in this scenario (note: the number of rows does not necessarily represent the number of required entities)

(b) Select two of your ENTITIES in 3A above involved in a relationship and

1. list the two entity names involved in this relationship

2. describe the type of relationship that exists between them as identifying or non-identifying (provide a reason for your answer), and

3. describe the cardinality of the relationship listing minimum and maximum cardinalities for each end of the relationship. Clearly indicate the relationship end which you are referring to by using the following form. For example. with two entities ENTITYA and ENTITYB:

   ENTITYA - min 0 max 1
   ENTITYB - min 1 max Many

Q2. Normalisation (10 marks)

A little league netball organises a competition for under-16 teams. Each team is graded according to their age bracket. Grade A is for the 15-16 years old and grade B for the 13-14 years old. Matches are organised for both grades and a match can have teams from different grades competing. At the end of the competition, the winner is determined for each grade. The competition runs for seven weeks and consists of eight teams. Each round, there are four matches scheduled on Saturday during the competition weeks. The following schedule shows some samples of the information about the matches.

Represent this form in UNF. In creating your representation, you should note the following:

• The league have indicated that Teams are identified by a team_id and Umpires are identified by an umpire_id, these attributes must be added to your UNF
• Each Team has a Team name and the grade they play in (A or B)  as indicated on this form.
• Round numbers are not reused, i.e. the round number is unique
• A given team can only play once in any round (as illustrated above)

Continue the normalisation to third normal form (3NF).

Clearly write the relations in each step from the unnormalised form (UNF) to the third normal form (3NF).

Clearly indicate primary keys on all relations from 1NF onwards by underlining the primary key attribute/s. Show the dependencies (partial dependency at 1NF, transitive dependency at 2NF, and full dependency at 3NF) via dependency diagrams, e.g., a_id → a_name, a_desc.

Include all candidate keys at the 1NF stage.

Do not add new attributes during the normalisation.

Question 1. Relational Algebra (10 marks)

Given the following set of relations, which represent an online holiday rental system where guests book properties owned by hosts. Note that a particular property can only be rented by a single guest at a particular time:

GUEST (guest_id, guest_name, guest_address, guest_mobile)

BOOKING (guest_id, prop_id, booking_startdate, booking_enddate, booking_totalguest)

PROPERTY (prop_id, prop_streetaddress, prop_city, prop_state, prop_capacity, prop_pricepernight, prop_cleaningfee, host_id)

HOST (host_id, host_name, host_mobile, host_email)

Write the relational algebra for the following queries (your answer must show an understanding of query efficiency).

project: π  select: σ  join: ⨝  intersect ⋂ union ⋃  minus -

 (a) List the street address, city, and state of all properties which were booked by a guest named ‘Jack Kaniva’. You may assume that there is only one guest who has such a name [4 marks]