Reference no: EM133107236

CP2404 Database Modelling - James Cook University

Assignment Part  - Conceptual Database Modelling

This assignment has been designed to assess students' ability to model a database, by constructing an entity-relationship diagram for a particular business scenario. This assignment addresses the following learning objectives for this subject:
• Develop a database model using the entity-relationship model
• Apply the techniques of normalisation

Requirements

1) You are to write a brief discussion of your solution, i.e. how you approached the modeling problem and any issues you may have encountered (maximum of ½ page)

2) You are to write all applicable business rules necessary to establish entities, relationships, optionalities, connectivities, cardinalities and constraints. If a many-to-many relationship is involved, state the business rules regarding the bridging entities after breaking down the many-to- many relationship. An example business rules format can be found in Appendix A of this document.

3) Based on these business rules*, draw a fully labeled and implementable Entity-Relationship Diagram (ERD). Include all entities, relationships, optionalities, connectivities, cardinalities and constraints. You must use Crow's foot notation and MS Visio (or other software) to create the ERD. A Hand-drawn ERD will NOT be accepted. A sample ERD can be found in Appendix A of this document. (Note: The ERD created using the drawing tool (e.g. Visio) will need to be saved as an image file and then be included in your document file to be submitted)

4) A summary to describe the major justifications, assumptions and limitations related to your database design. For example:
• Assumption/justifications for optionalitiy, connectivities, constraints data type and data domain; and
• Special cases or data integrity issues that cannot be handled.

Business Description (Scenario)

You are going to create a database to manage rental cars and invoices for the Car Hire Me (CHM) company.

CHM consists of a number of offices around Australia and customers can rent a car from one office (or location) and return it to another office (or another location). For example, a customer can rent a car in Brisbane and return it in Sydney. Each location should keep the details of its address.

There are 6 different types of cars:
1. Economy, for example, Holden Spark, Manual Transmission
2. Compact, for example, Suzuki Swift, Automatic
3. Standard, for example, Toyota Corolla, Automatic
4. Full Size, for example, Toyota Camry, Automatic
5. Full Size Van, for example, Kia Carnival, Automatic
6. Full Size 4wd, for example, Mitsubishi, Automatic

Each car type should contain a number of doors, a number of seats, a number of airbags, daily rental- car cost and daily rental-insurance cost. In addition, each care type can also be classified as a normal or luxury car.

Beside the normal information such as license plate, registration, name, manufacturer, a number of cylinders, color, image and location; each car should also be known whether to have USB ports or Bluetooth or not. In addition, the database should also have a field which user can optionally input some extra information.

CHM quite often launches special discounts or promotions. Each promotion has a valid code within a valid time and its value is formulated based on rental car cost, for example, 5% or 10% of the rental cost.

Customers must sign-up before they can hire a car. The database should record the details of a customer such as name, address, date of birth, email and phone. Email should be unique, and the postcode should have 4 numerical digits from 0200 to 9999.

When a car is hired, the following information should be recorded: the borrower, the rental date (the date it is hired), the expected returned date (the date it is returned), the promotion if applicable and whether the car is insured or not. When the car is returned, the actual returned date is recorded together with the returned location. In addition, the customer can optionally leave a feedback such as rating and comment. The rating should have 5 different values in total.

To improve customer services, the database should also keep information about what cars customers have been searching for.

When a customer hires a car, one payment will be processed based on the rental date and the expected returned date, and an invoice will be issued as well. If the customer is less than 25 years old, a double of the insurance cost will be applied.

When a car is returned, an additional payment may be applied based on the actual returned date. For example, if a car is returned more than one hour late, an additional one-day payment will be applied, and an additional invoice will be issued.

Invoices should have the details of invoice date and total cost. The total cost should be computed based on the daily rental-car cost, the daily rental-insurance cost, the number of days, the promotion, and young drivers (if exists). GST is 10% of the total cost.

Attachment:- Conceptual Database Modelling.rar