Data Structure | Algorithm
INSTRUCTIONS
Please note that your group must not start the modelling task until each member individually has completed the week 6 Applied logical model for the property rental case study, pushed it to their private repo, and compared your answer with that provided in the sample solution to check their understanding.
This task continues the work you have started in assignment 1A by refining/extending the model you developed and implementing it as a set of tables under your Monash Oracle database account.
Since this is an ongoing development process based on your assignment 1A submission and marker feedback, you must ensure that your assignment 1A submission and the marker feedback remains confidential and is only seen by the members of your group and the unit teaching staff.
Assignment 1B’s brief must be read in conjunction with the assignment 1A brief – i.e. your final model must encompass both sets of requirements. You may modify your assignment 1A conceptual model in any manner you wish as you work through assignment 1B, provided your final model meets both sets of requirements. Your assignment 1A conceptual model will not be submitted or assessed again, updates are only part of the group working towards your logical model.
In developing your final logical data model, composite attributes present on your conceptual model must be expanded into their component simple attributes, unless otherwise directed.
If the supplementary material presented in this document does not guide you in deciding the components, you may make any reasonable decision on their simple component attributes.
Further discussions with Clean Up Inc (CUI) have revealed the points listed below:
i.Local Authorities are classified into the following fixed types:
○ Borough
○ City
○ District Council
○ Shire
○ Town
ii.Road surfaces are classified into the following fixed types:
○ Asphalt
○ Concrete
○ Unsealed
iii.Bins supplied to properties will be replaced if needed, the fixed reasons noted are:
○ Damaged by owner
○ Bin Failure (fair use eg. old age)
○ Damaged during pickup of waste
○ Stolen
iv.The types of waste collected by CUI currently include green waste, landfill, standard
recycle and glass. The company would like to be able to easily add new types of
waste for collection which suit particular local authorities needs.
v.Waste collections are made at one of the following fixed intervals:
○ Weekly
○ Fortnightly
○ Monthly
vi.The RFID codes used to identify bins are recorded as a 16 character hexadecimal number. For recording purposes it is important to be able identify what the actual charge was made for the supply of a bin to a property. This bin charge is determined by what contract the bin was supplied under. Note that this is not necessarily the bin charge under the current contract, it may be that the bin located at the property was supplied under a previous contract.
vii.Each truck driver employed by CUI is assigned a unique driver number. Drivers are approved by the company to drive individual trucks in the fleet. A given truck may be driven by many approved drivers. Each truck is identified by its Vehicle Identification Number (VIN). When a driver is approved to drive a particular truck the date of that approval is recorded. Note that there is no dependency between truck make and model (such details are not necessarily unique to a particular make/model).
viii.When a truck is making a pickup of a bin from a property on a particular date, if the bin is detected by the truck’s on board scales as being too heavy, the waste is not collected, and this bin collection is flagged as being overweight (Y). If the bin is not overweight, it is flagged as not overweight (N) and collected.
A particular type of waste can only be collected once on any given date from a particular property. However a property may have different types of waste collected on the same date.
ix.CUI have indicated that they wish all phone numbers in the system to be recorded as simple attributes.
x.Clean Up Inc have supplied the following two forms as samples of those which are used within their business. You should note that the data shown is incomplete and only representative of the type of data for each item.