… to use a car either to visit friends, move apartments, go on holidays, or do some shopping.
The business owns a number of vehicles of different classes and these include small passenger vehicles such as the Hyundai I30, larger passenger vehicles such as the Toyota Camry, small moving vans such as the Hyundai Iload, and a couple of medium sized four-wheel-drive vehicles. The information the business needs to store about the vehicles is the VIN number, registration number, vehicle class, make, model, engine capacity, colour, body type, odometer reading, year, current location, date acquired, date disposed, staff number.
The business has negotiated with various city councils for the council to provide a designated car parking space for each vehicle. Some of these are provided free by the councils and some councils charge the SmartCarShare business an annual fee for the car parking spaces (referred to as locations). The Information that needs to be held about the locations is location number, Street address, suburb, postcode, local council, local council contact phone, parking levy amount, staff number.
People can register to be a member of SmartCarShare and the business has several membership types. They are Casual, Budget and Regular and the fee structure for the memberships is shown below.
Information about the membership types and the fees charged must also be kept in the database. Fees that currently apply, previous years fees, and fees that will apply in the future all need to be maintained in the database.
When a person signs up to be a member the following details are recorded about them, member number, last name, first name, street address, suburb, postcode, phone, email, driver’s license number, license expiry date, date of birth. Their membership details are also recorded and are membership number, application date, membership type (Casual, Budget, Regular) membership type description, date joined, last renewed, expiry date, status (pending, approved, suspended) accepted membership agreement, location of agreement (a filepath). A member can change membership types at any time. If changing to a higher level membership, the member can get credit for previous payment, while if they change to a lower level membership they do not get a refund.
Once people are accepted as a member they are sent a membership smartcard within a couple of days. Once they have the card they are able to book a vehicle for as little as one hour at a time to a maximum of three days at a time. Fuel and Insurance are included in the rental fees. Part of the member registration process is for members to agree to a set of terms and conditions which lay out the charges for the vehicle rentals and the conditions that apply to booking vehicles, using the vehicles, return of the vehicles, and the reporting any damage that may have occurred while they were in charge is a vehicle. Both the driver who caused the damage and the member no of the associated member must be recorded in the database.
When a booking is made the following information must be entered and recorded in the database – booking number, membership number, date and time of the booking, date and time the car is required, date and time the car is to be returned, date and time the car was actually returned, booking notes, and a staff number if the booking was processed by a member of staff. A booking can be made for any vehicle at any location. Sometimes when a booking is made two or more people are going to drive the car. In that case the information about the additional drivers must be recorded. The details required for each additional driver are their license No, last name, first name, street address, suburb, postcode, phone, email, driver’s license number, license expiry date, member no (this driver is linked to). The maximum number of additional drivers that can be linked to a member is three. A member can hire many cars but not two at the same time.
When a booking is made a payment must also be made for the booking. The payment details are payment number, payment date, membership number, payment type, amount, credit card number, expiry date, CCV, cardholder name, staff number (if the payment was processed by a staff member).
When a member picks up a vehicle to start the rental
- They swipe the membership smartcard on the card reader in the windscreen
- If they have a booking for that day and time and the car door will open and a locked compartment inside the car that contains the key will also Swiping the card causes a program to record the starting date and time of the rental.
| · They check the condition of the car, if it’s dirty, damaged, has rubbish inside, has less than half a tank of petrol or was returned late by the previous member they complete a condition report via their phone app which records the following details – condition report number, member number, rego number, problem type (late return, amount of time late, dirty, insufficient fuel, damaged) conditions notes.
When a member returns the car · The vehicle must be returned by the due date/time. · They must remove any rubbish from the car. · Members are required to fill the car with fuel if the fuel tank is under 50% full. · They put the keys back in the key compartment, close the door and swipe the smartcard on the windscreen again. This locks the car and the key compartment and records the date and time returned. Member suspensions.
Members have a number of rules they need to follow. These include returning the vehicle on time, not leaving any rubbish in the vehicle, reporting any damage that occurs and filling up with fuel if the fuel gauge shows less than ½ a tank. If they break these rules they will receive a suspension. Depending on the problem and the member’s history of breaking the rules they can be suspended for between 1 week and 3 months. If they have more than 5 suspensions and are continually breaking the rules their membership will be cancelled.
A manager must process any suspensions and the details that need to be recorded are suspension number, membership number, date started, suspension duration, reason for the suspension, staff number (of the manager)
If a vehicle is damaged a senior staff member will go to the location where the car is stored and complete a damage report. The details recorded are damage report number, membership number, drivers name, date and time, description of damage, photos of the damage (up to 6 photos). If the car is seriously damaged it will be taken away for repair and a replacement car provided in that location.
If a car was reported as dirty a vehicle wash is requested. Any staff member can complete this request which is stored in the database. The details recorded are registration number, location number, date and time booked, condition report number, staff number, the date and time the wash was requested, the date and time wash will take place and the date and time the wash was completed. This request also sends an email to a car wash company near the location and washes are usually completed within a few hours. The email contains a time that day for which there is no booking. Booking are prevented for a 30 minute timeslot while the wash is taking place.
On a regular basis the vehicles required scheduled, or unscheduled maintenance. These can be scheduled by senior office staff and managers. The details that need to be recorded in relation to maintenance are registration number, date and time booked, staff number, date and time of service, expected time of the service, and completed (Y/N). Booking of the vehicle needs to be prevented during the period of maintenance.
The database also need to keep the following information about the staff. Staff number, last name, first name, street address, suburb, postcode, phone, email, position type, date of birth.
The items required for the Assignment are: |
||
| Item | Description | |
| 1 | Conceptual Entity Relationship Diagram
Initially you are to read the case study and analyse the data requirements of the client. You need to identify the entities, relationships between the entities and determine the attributes of each entity. Having done so you then develop a Conceptual Entity Relationship diagram using the Crows Foot notation (method and symbols). You cannot use a different approach. The diagram must use the Conceptual Design approach to documenting attributes (Composite attributes, Multi-Valued attributes and Derived attributes). You are also required to document Cardinality and Participation for all relationships. On completion of your initial diagram save it as a draft and then meet with the client to validate the design. (see item 7) |
|
| 2 | Logical Database Design
When the client has accepted the Conceptual Design and agrees that it is accurate you will convert the design into a logical Entity Relationship Diagram. You need to document the entities, relationships between the entities and determine the attributes of each entity using the Crows Foot notation. You cannot use a different approach. You must ensure the diagram does not contain any Composite Attributes, Derived Attributes, Multi- valued attributes, or any Many to Many relationships. You are also required to document Cardinality and Participation for all relationships |
| 3 | Physical Design
Once you have completed the Logical Database design you need to choose a Database Management System for you implementation and then document the Physical design based on the chosen DBMS. The design needs to include 1. Table Names, Column Names, Column description, 2. Data Types and the Length of the columns 3. Constraints details including, Primary Keys, Foreign Keys, Not Null constraints & any other Check constraints. |
| 4 | Assumptions and Business Rules
You need to document all business rules that are explicit in the descriptions of the system and assumptions about rules that you will need to make in a number of instances. Your assumptions should be realistic for a system of this type. |
| 5 | Normalisation
Once the Entity relationship diagrams and Business rules are complete you are to undertake the task of Normalisation of the data using the techniques taught to you in class. You will need to Normalise the data in sections (or user views) to make the task manageable. You need to show the data in each view in the following forms (that will demonstrate you fully understand the principle involved). Un-normalised, 1st Normal Form, 2nd Normal Form, 3rd Normal Form. Once you have Normalised the different user view you need to consolidate them into a single set of relations that is in 3rd Normal Form. It is not acceptable to just show the final relations. |
| 6 | Comparing the Models
Once you have completed the Normalisation you need to compare the final design with the Logical Entity relationship Diagram to further Validate the design. If there are differences you need to document what they are and explain why the differences came about. Having identified the differences you reconcile the designs (update the models so they are same) making sure they will achieve what the client requires. |
| 7 | Validation with the client
On completion of developing your draft version of the conceptual design you must meet with the client and ask him/her to review the design and provide feedback. This feedback may identify missing items or clear up some misconceptions of the data requirements. You must document the feedback you were give. If an update of your design is required, you create a second version of the Conceptual Design and once again meet with the clients to verify the design improvements are correct as far as the client understands. When you submit the work for this section of the assignment you must include the initial design, the feedback, the second design and any further feedback and designs that were required. |
| 8 | Signoff
When all design are developed you prepare a document in which you will obtain sign off from the client for receiving the final set of Designs. |
