Question

draw the entity relationship model for the following data

he system needs to record details of all registered vehicles driven in the local government area. A vehicle is identified by a Vehicle Identification Number (VIN). Background reading on a VIN is available from several sites such as ​AutoCheck​. The government wishes to record the type of vehicle, such as motorbike, car, truck etc, the year the vehicle was manufactured and the model such as CX3, as vehicle attributes. Vehicles are produced by manufacturers such as Ford, Mazda etc. Each manufacturer is to be assigned a unique manufacture code, in addition the system must record the manufacturers name and the country in which the manufacturer is based (for example Ford is based in America, Mazda is based in Japan).

Drivers are identified by their licence number. The attributes which are to be recorded for a driver are the driver's first name, last name, residential address and date of birth. The government also wish to have recorded the expiry date for a driver's licence and the total number of demerit points the driver has accumulated.

Each demerit (potential driving offence) is identified by a demerit code. A description of the demerit and the number of points incurred for that demerit are to be stored. For example the demerit with code 05, has a description of "Failing to give way" and results in 3 demerit points being incurred by the driver.

Each traffic offence by a driver is identified by an offence number.The location of the offence and the date and time at which the offence occurred are to be stored. In some circumstances when a driver is stopped by a police officer for say, a speeding offence, the police officer may also identify other offences such as "Driving contrary to a major defect notice". Each offence recorded has a unique offence number and is for a particular (ie one) demerit. The police officer who issued the offence must also be recorded.

Page 1 of 5

Once a driver has accumulated 12 demerit points over a period of three years the driver’s licence is suspended for a period of 6 months. When this occurs the driver’s licence is suspended from the date recorded for the offence which caused the points to equal or exceed the permitted maximum. The local state government informs the driver of their suspension via a letter sent through the post to the drivers registered address. The date at which the suspension began and the date at which it ends are to be stored in the system.

Police officers are identified by an officer id. The system should record the officer's first name, last name and rank e.g. constable, sergeant etc. Police officers are based in Police Stations, each station has one officer who is in charge of the station (a station is required to always have an officer in charge). Officers are assigned, from a particular date, to a station for a minimum of two years, after this time they may apply to move to a different station. Within these allocations some officers may return to a station they were previously allocated to. Police stations are identified by a station number. In addition the government wishes to record the stations' address, direct contact phone number and if the station is open 24 hours a day or not.

Answer 1

Here squares represent ENTITY

Diamonds represent RELATIONSHIP

Eclipses represent ATTRIBUTES

Eclipses with underline represents ATTRIBUTE WITH PRIMARY KEY