Data Modelling Assignment Part II COMP1711
Flinders University Page 1 of 6 2021

COMP1711
Data Modelling Assignment Part II
Happy Holidays
DEADLINE: 11.55pm, Thursday of Week 13 (04th of November 2021)
This is not an assignment that can be done at the last minute!
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:










Client is a strong entity. The composite attribute name has only its constituent
simple attributes fName and lName listed. prefType has been merged into the Client
entity from the Preference entity from a 1:1 States relationship with mandatory
participation on both sides. staffNo has been posted into the Client relation from a
1:* Registers relationship where Client was the child and Staff was the parent.
staffNo is NOT NULL as Client has mandatory participation in the Registers
relationship. UPDATE CASCASE was chosen to update staffNo whenever the
attribute changes in the Staff table. DELETE NO ACTION has been selected to stop
Staff being deleted without first updating Client.
Data Modelling Assignment Part II COMP1711
Flinders University Page 2 of 6 2021


(b) Assume you have gotten only the following entities in your ER model(only for attempting this
part):
Depot, Vehicle, HiredVehicle, Client, Company Client, Personal Client and Booking
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

depotID address phone regNum typeID fleetNum colour make model doors body trim
20 IST
Flinders
5000
1400111111
1300111111
ABC001 AQ51 100 Blue Audi Q5 4 SUV Standard
30 Tonsley
Flinders
5500
555111444
555111444
3100200505
DEF003 AQ51 500 Red Audi Q3 4 Small
SUV
Sports
20 IST
Flinders
5000
1400111111
1300111111
XYZ200 MCX2 200 Gold Mazda CX30 4 SUV Luxury
30 Tonsley
Flinders
5500
555111444
555111444
3100200505
WXY350 HAC3 300 Silver Honda Accord 4 Sedan Standard


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.
BookingID HiredDate InsuranceID InsuranceCost PolicyType startDate hireDays ClientID ClientPostcode
Data Modelling Assignment Part II COMP1711
Flinders University Page 3 of 6 2021

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
Data Modelling Assignment Part II COMP1711
Flinders University Page 4 of 6 2021

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?
Data Modelling Assignment Part II COMP1711
Flinders University Page 5 of 6 2021

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

THE FOLLOWING PAGES CONTAIN A SOLUTION TO MODELLING THE ABOVE SCENARIO.
YOU ARE TO USE THIS SOLUTION TO DERIVE YOUR RELATIONS AND DATABASE TABLES



Data Modelling Assignment Part II COMP1711
Flinders University Page 6 of 6 2021































































































































































































































学霸联盟