The local state government has decided to change the manner in which garbage and recycling collections are charged to ho

[answerhappygod]

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 metres, 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). 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.
Prepare a logical level design for the case
study above.
● The logical model must be drawn using the Oracle Data Modeler.
The information engineering or Crow’s foot notation must be used in
drawing the model. Your logical model must not show data types.
● All relations depicted must be in 3NF
● You are required to add at least one surrogate key to your
design (you are free to select the most appropriate relation to
make this change in)
● All attributes must be commented in the database (ie. the
comments must be part of the table structure, not simply comments
in the schema file).
● Check clauses/lookup tables must be applied to attributes
where appropriate.
● You MUST include the legend as part of your model. Please edit
the legend panel to show your name
Generate the schema for the database in Oracle
Data Modeler and use the schema to create the database in your
Oracle account.
The only edit you are permitted to carry out to the generated
schema file is to add header comment/s containing your details and
the commands to spool/echo your run of the script. In generating
your schema file ensure you:
● Capture the output of the run of your schema statements using
the spool command.
● Ensure your script includes drop table statements at the start
of the script.
● Name the schema file as bin_schema.sql.
Thankyou! Please answer correctly with
explanation otherwise it's a downvote.