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