本次澳洲作业主要为数据库相关的sqlassignment
Task 1 Modelling
The local state government has decided to change the manner in which 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 bin which is collected and clients will then be charged for the weight and type of collection made.
The following describes the data which is 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). 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 record
ed 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).
This 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.
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 which were collected for that property in this collection.
Using LUCIDCHART (DO NOT USE SQL DEVELOPER) – Create a logical level diagram using Crow’s foot notations to represent the “Collections” data requirements described above. Clearly state any assumptions you make when creating the model. Be sure to include all attributes,including Primary Keys and Foreign Keys, as part of your design. You do not need to include indicators (*) to show if an attribute is required or not nor datatypes/sizes.
In LucidChart use the same diagram style as we have used for conceptual modelling, simply amend the approach to show a logical model.
In building your model you must conform to FIT9132 modelling requirements