Reference no: EM132314898

Data Modelling Assignment

General Specification

(a) Derive relations from the supplied Entity-Relationship Diagram given below into a logical model representation in terms of the relational data model. Identify a primary key for each relation and subsequently all foreign keys.

You need to use the database description language (DBDL) as described in Chapter 17 of the textbook, for example

You also need to justify the reasoning behind the derivation (e.g. why does it contain a foreign key, can the foreign key be NULL, why has a particular ON UPDATE been chosen, etc.), for example:

(b) Translate your answer to (a) into SQL (in a file .sql) and build the database using capabilities of SQLite. Include the relevant DROP statements and specify ALL primary and foreign keys. If want to demonstrate ISO SQL that is not available in SQLite than include as a comment, but ensure that you have correct, runnable SQLite database as well. You also need to populate your database (INSERT INTO ...) with some sample data and perform some simple queries to ensure it works correctly.

The required submission for Part II of the assignment is:

Submit part (a) on FLO as a PDF containing the description of the derived relations and your reasoning behind the derivation of the relation.

Submit part (b) on FLO as a SQLite .sql file that can be executed on SQLite ver 3.x. You will be penalised heavily if the file does not execute without errors.

You may also submit an assumption and clarification document to assist in interpreting your derivations and implementation.

Specifications
PLEASE base the design on the description i.e. treat the description like requirements. Failure to adhere to the description is very poor professional practice. (If you really believe the description is invalid, please discuss it with me as soon as possible.) I am quite happy, in consulting, to explain any part of the description that is problematic. Also keep up to date on FLO for any clarifications.

Wheels-4-U
The car hire firm Wheels-4-U, requires a database system to manage their fleet of hire vehicles. Each vehicle in the fleet has a unique fleet membership number (N 3). Also recorded is the vehicle's registration number (S 7), colour (S 20), and make (S 8) and model (S 8). Along with the make and model combination, also recorded is the number of doors (N 1), body style (hatch, sedan, SUV, or coupe), and trim level (standard, sport, luxury).

Each Wheels-4-U depot has an identification code (N 2). Also recorded is the depot's address (A), 1 to 4 phone numbers (S 14), and a fax number (S 14). The current location (depot) of vehicles available for hire is recorded.

Wheels-4-U has several different types of daily hire tariffs, for example, depending on the type of driver (e.g. under 21) or varied driving locations (city or country). For each type, an identification code (S 2) is recorded only with a description of the conditions under which it applies (S 50). For each make/model of car, the daily rental tariff ($ 3) is recorded for each of the tariff types.

When a vehicle is hired, the depot from which it is hired, the hire time and date (D) are recorded. The intended drop off depot also recorded. The hirer (client) involved is recorded along with the type (S 2) and number (S 20) of the credit card to be used to pay for that hire. (For security reasons neither cash nor cheques are accepted by Wheels-4-U.) There will be one person recorded as being the nominated driver of the hired vehicle (this does not have to be the hirer). A vehicle's kilometreage (N 5) (distance travelled) is recorded when it is hired. At any one time only one vehicle is on hire to a customer. Also recorded is the applicable tariff type and the number of hire days (N 2). Optional insurance maybe taken for each hire. An insurance policy number (N 5), policy type (excess reduction, excess removal, or full cover) and cost ($ 3) is recorded.

A make and model of vehicle may also be booked for a future hiring, in which case, the nominated pick-up depot, the starting time and date for the booking (D), and the intended number of hire days (N2) are recorded. An optional preferred colour (S 12) may be recorded.

A client may make any number of bookings.
Wheels-4-U records the individual details of any client the first time that the person or the company has business with the company. A client's name (C), address (A) and one or two contact phone numbers (S 14) are recorded along with a generated unique client identifier. This identifier is used for all subsequent references to that client. If the client is a company, they must have a (single) nominated person also recorded as their representative. The details of such representatives are recorded as though they were person clients. A (person) client's driver's license number (S 12) is recorded the first time that they hire a vehicle or are a nominated driver.

For each vehicle, a service history is kept. It contains for each past service, (scheduled or repair service), the date (D), the cost ($ 4), a service description (S 50) and the identification of the depot where the servicing was done. A vehicle may have had none or several past services. For all vehicles, the next scheduled service has the associated kilometrage (N 5), date-to-be-done-by (D) and the depot to do the servicing recorded.

Only after the vehicle is returned an invoice is generated for the vehicle hired (with a unique invoice ID (N 10) that contains the details or the hirer (and company if applicable), the vehicle hired, the return depot, an indication that the vehicle passed a quality check (a ‘Yes' or ‘No') and the final cost ($ 5) based on the number of days and the daily rental tariff. The date paid (D) is also recorded and if it has not been recorded the invoice is considered unpaid.

Operations and Questions
The "Operations and Questions" are here to help verify/validate you design. You do not need to provide answers to questions for the assignment.
• Add a new hire
• Add a new vehicle to the fleet
• List all hires for the last month
• Which make of vehicle has had the most hires?
• Which make of car is the most/least popular?
• Which depot has the most vehicles available for hire?
• Which depot is the most popular based on hires?
• List all the hires with a the number of hire days more than X.
• List all the vehicles that have a scheduled service soon (in the next month).
• List all vehicles that do not currently have a future hire booked
• List all invoices that have not been currently paid.
• What are the different daily rental tariffs for tariff ID "C1" for each of the make/models?

5. Scope Creep!

The customer loves the design that was create and, as always, the customer now has some more ideas for you to include - otherwise known as "scope creep". We usually try to avoid this but there are a few good ideas that we will include:
• Drivers have the same information stored as if they were clients
• Multiple drivers for a single hire
• The Insurance Policy Types should also have the individual Insurance Policy Number that its generated and given to the Client.

Attachment:- Data Modelling.rar