Your task for this assignment is to design a database which can be used to support tracking of the building damage caused by bushfires - a National Fire System (NFS).

Any assumptions you wish to make please state it in your answer.

Your task for this assignment is to design a database which can be used to support tracking of the building damage caused by bushfires - a National Fire System (NFS). When a bushfire starts it is noted as a fire event. Each fire event is assigned a unique identifier such as F20200135 and given a name such as West Forest Fire" (fire event names are not unique, the same name may be reused many times). For a given fire, the date on which the fire started, the coordinates of the estimated start location (latitude and longitude), and the number of hectares which were burnt, are recorded. Sometimes one fire event may spark further fire events, for example when embers travel many kilometres in the wind and start a new fire. The NFS needs to record if a particular fire event was started by sparks from another fire event. Fire events occur within Local Government Areas (LGAS) - a given fire event may involve many LGAS. A bushfire may impact a property and damage buildings located on that property. The assessment of such damage is the major task of the NFS. Properties are identified by a unique national property id and have their street address/location and property size in hectares recorded as part of the system. Such properties may be a farm property, a residential property (in a city or town) or a business property. Each property has a single owner who is identified by an owner id. The owner's name and contact phone number are also recorded. A particular owner may own several properties. For properties such as groups of apartments/villas or units owned by individuals which share lawns, roads etc (i.e. a strata title), the owner of the property, for this scenario, will be taken as the body corporate. In this way, all properties will be regarded as having only a single owner. Properties are located in a local government area or LGA. Each LGA is assigned a unique LGA code, for example, 45. The LGA's name, for example, "Wardinia", size (in hectares), state the LGA is located in, Chief Executive Officer's name, and bushfire contact phone number are recorded. A property may have one or more buildings located on the property. Each building on the property is assigned a building number for that property. These building numbers are reused for each property - for example, property 4231212 may have a building number 1, but also property 2133251 may also have a building number 1. Some properties may have no buildings located on them. For a building, the NFS must record the date the building was approved for construction and the size of the building in square metres. If a building is replaced due to fire damage the new building will be assigned a new building number ie. building numbers are not reused within a property. For this system, we will assume that insurance takes place at a building level only. Each building may be insured by an insurance company, although not all buildings are covered in this manner. For a given building, there will only be a single insurance cover at a particular point in time. However, a given building may be insured by different insurance companies over time. Each insurer is identified by an insurance code, the insurer's name and contact phone number are also recorded. An LGA employs several fire damage assessors who carry out initial assessment for all damaged buildings, regardless of insurance cover. These assessors assign a building damage assessed cost. Following this visit by the LGA assessor, the damage is added to the NFS system. Once added, if the building is insured, an insurance assessor will visit and determine the amount to be paid under the building's current insurance coverage (this may not be the same as the damage cost assessed by the LGA assessor). Insurance companies employ insurance assessors to visit each building which has been damaged by a fire event, evaluate the building damage assessed cost, and determine the amount to be paid to the building owner. This determination may require multiple visits. Although several visits may be required, only one assessor is used to assess the damage of a given building due to a particular fire event All assessors are identified by a unique assessor id. The system needs to record the assessor's name, their contact number, the type of assessor (LGA or Insurer), and the insurer they currently work for or the LGA they are employed by