This assignment involves two electronic submissions (PDF and .sql via FLO).

You are encouraged to work in pairs for this assignment, however it is not mandatory that you do so. You do not need to work with the same partner as Assignment Part I.  A pair is two people.

WORTH: The assignment is worth 30% of the assessment.

1 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) For simplicity lets assume that we have the following entities in our conceptual model( only for attempting this part) : Vehicle and Booking

We now want to validate our conceptual model through normalisation.  List the functional dependencies and translate the conceptual model to 3NF. Show each step, using guidance from chapter 14 of the book and the lecture.

1). Vehicle: Convert this table to 3NF

2). Booking: Fill some dummy data (about 4 rows) and convert this table to the 3NF

(c) 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 you    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.

2 Submissions

The submission of any work will be taken as your claim that it is your own work (or that of you and your partner if working in pairs), i.e., that you cognitively and physically created it. It also signals    your agreement to re-do it or similar work under supervision if any doubts are raised about your    authorship of the work.

The required submission for Part II of the assignment is:

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

Submit part (c) 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.

3 Assessment

If you a working as a pair each member will receive the same overall mark.

The assignment is worth 30% of the total assessment. It will be marked out of 60. A guide to the breakdown of the marks over the parts is

(a)  30 marks

(b)  20 marks

(c)   10 marks

The assumption and clarification document will not formally assessed.

This is only a guide since the submissions are inter-dependent and will not be marked independently. Inconsistencies between them will lose marks.

4 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.

4.2 Happy-Holidays

The car hire firm Happy-Holidays, 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). All cars are the current model. If a vehicle is  fitted with a mobile phone, its phone number (S 12) is recorded. Cars can either be traditional            vehicle or electric vehicle. Traditional cars are either diesel or petrol operated (N 2). The electric vehicle have four types (S 5): Battery electric vehicles (BEVs), Plug-in hybrid electric vehicles (PHEVs), Fuel cell electric vehicles (FCEVs), Non-plug-in hybrid EVs (HEVs).

Each Happy-Holidays 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.

Happy-Holidays has several different types of daily hire tariffs. 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 of car, the daily rental tariff ($ 3) is recorded for each of the tariff types. Also recorded for each make of car is the daily insurance tariff ($ 3).

When a vehicle is hired, the depot from which it is hired, the hire time and date (D) are 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. The company only accepts Master card or Visa card. (For security     reasons neither cash nor cheques are accepted by Happy-Holidays.) There will be one or up to four persons recorded as being nominated drivers of the hired vehicle (None of these have to be the hirer). A vehicle's kilometreage (N 5) (distance travelled) is recorded when it is hired, and the fuel   level for traditional cars only (N 2). At any one time only one vehicle is on hire to a customer. Also   recorded is the applicable tariff type, the number of hire days (N 2.

A make 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.

Happy-Holidays 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. Any minor damages to the car such as scratches or minor dents  are recorded as comments (S 200), along with the date they were entered into the database (D).

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.

4.3 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 past one year

•     List all the electric vehicles

•     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 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.

These changes have been included in the documentation below.