Designing a Local Database Model for Commonwealth Transport Services

This assignment presents a comprehensive database design project for Commonwealth Transport Services, encompassing the creation of an Entity Relationship Diagram (ERD), normalization to the third normal form (3NF), and the development of a relational database schema. The project begins with an ERD illustrating the relationships between entities such as Vehicles, Drivers, GameOfficials, Maintenance, BookingCompletion, Locations, and Bookings. Assumptions are clearly stated to clarify design choices, such as handling vehicle maintenance and booking details. The core of the assignment involves normalizing the relations from the ERD, with dependency diagrams provided for each entity, demonstrating the step-by-step conversion to 3NF. The normalization process addresses multivalued attributes and transitive dependencies. Finally, the project concludes with a relational database schema, specifying attributes, data types, field lengths, and primary and foreign keys for each relation. This includes tables for Vehicles, Drivers, GameOfficials, Maintenance, BookingCompletion, Locations, and Bookings. The assignment showcases a detailed understanding of database design principles and practical application of normalization techniques.

2814ICT – DATA MANAGEMENT
7003ICT – DATABASE DESIGN
School of Information & Communication
Technology
Trimester ___, 20___
Assignment Part 1:
Designing a Database for
_________
ASSIGNMENT TITLE: Local Database Model for Commonwealth Transport Services_____
Student 1 s-number: Full name:
Student 2 s-number: Full name:
Student 3 s-number: Full name:
Course Code: Workshop/Lab day & time:
Tutor’s name: Date submitted:
Marks obtained: ________. [For marker to fill up.]
PLAGIARISM
Plagiarism: occurs when the work of another is represented, intentionally or unintentionally, as
one's own original work, without appropriate acknowledgement of the author or the source. See
more at https://www.griffith.edu.au/academic-integrity/information-for-students/what-is-plagiarism.
Plagiarism is a serious offence. Refer to the following document on Student Academic Misconduct:
http://policies.griffith.edu.au/pdf/Student%20Academic%20Misconduct%20Policy.pdf.
Declaration
Except where appropriately acknowledged, this assignment is our own work, has been
expressed in our own words and has not previously been submitted for assessment. We have
also retained a copy of this assessment piece for our own records.
Student 1: Student 2: Student 3:
Name: _______________
Signature: _______________
Date: _______________
Name: _______________
Signature: _______________
Date: _______________
Name: _______________
Signature: _______________
Date: _______________
2814ICT & 7003ICT - Assignment Part 1 Page 1

Note: All students in the group must sign this first page, scan the signed page, and then
place at the beginning of the assignment.
Table of Contents
[Generate a table of content with page numbers.]
Entity Relationship Diagram..............................................................................................3
Assumptions..........................................................................................................................4
Normalisation.......................................................................................................................5
Appendices............................................................................................................................9
Bibliography.......................................................................................................................10
List of Illustrations
[Generate a list of figures and tables with page numbers.]
Figure 1: Entity Relationship Diagram..................................................................................4
Figure 2: Dependency Diagram of Booking Relation in UNF..............................................6
Figure 3: Dependency Diagram of Booking Relation 2nd Normal Form...............................6
Figure 4: Dependency Diagram of Booking Relation is partially in 3rd Normal Form..........6
Figure 5: Dependency Diagram of Vehicles Relation in 3rd Normal Form...........................7
Figure 6: Dependency Diagram of GameOfficials Relation in 3rd Normal Form..................7
Acknowledgements:
[List names of staff and students you have discussed with about this assessment.]
2814ICT & 7003ICT - Assignment Part 1 Page 2 of 11

1)
2)
3)
2814ICT & 7003ICT - Assignment Part 1 Page 3 of 11

Entity Relationship Diagram
[Place your ERD below.]
Figure 1: Entity Relationship Diagram
2814ICT & 7003ICT - Assignment Part 1 Page 4 of 11

Assumptions
[Write any assumptions you may have considered.]
 Vehicle has one to many relation with the maintenance relation because one vehicle can be sent for
maintenance several time. However, not every vehicle must receive maintenance. A newly bought
vehicle will not receive maintenance at the time it is registered into database
 One vehicle can be booked several times but a vehicle may not have received any booking.
 Booking completion must have a bookings relation. This means each booking must have
 actualStartDateTime and other attribute of BookingCompletion relation
 For each booking location must be registered into locations relation. Database will not store any
location to where booking service has not provided. This means location will be registered based on
the officials’ request
 Pickup and Drop off location types is placed in Booking relation not in location to reduce data
redundancy
 The database will store only those languages that are spoken in the countries stored. As languages
and countries has many to many relation it is converted into many to one using a bridge table
 Drivers can speak many language and one language can be spoken by many drivers. This imposes
that drivers and languages also has many to many relation between those relations. That is why
DriverSpokenLanguage bridge table is introduced to decompose the many to many relationship.
 One driver may be associated with many service. But it is also possible that a driver has never
served an official. This proposition of optionality is also possible from officials end. This is because
each booking must be done by an official but an official may not have booked any service
2814ICT & 7003ICT - Assignment Part 1 Page 5 of 11

Normalisation
[Convert each entity from your ERD into a relation schema, draw dependency diagram and
step-by-step convert it to 3NF. Mention the reason why a relation schema cannot be
converted to the 3NF.]
Bookings relation: As per the information available regarding Bookings relation following dependency
diagram has been created.
Figure 2: Dependency Diagram of Booking Relation in UNF
(Source: Created by Author)
As per the diagram, LID is referring to pickup and dropoff locations that is creating multivalued attributes. In
order to move the relation into first normal form these multivalued attributes are converted into single valued
attributes. The location related attributes are also dependent on the LID which imposes partial dependency in
the relation. In order to move the relation into second normal form, this partial dependency is removed and
following relation is generated.
Figure 3: Dependency Diagram of Booking Relation 2nd Normal Form
(Source: Created by Author)
Now LID is acting as the reference for both pickUpLocation and dropOffLocation foreign keys. There is still
transitive dependency in the relation. The actualEndDateTime, startOdometerRating and endOdometerRating
are transitively dependent on the actualStartDateTime. If the transitive dependency is removed then
following becomes the final relation for bookings.
Figure 4: Dependency Diagram of Booking Relation is partially in 3rd Normal Form
(Source: Created by Author)
2814ICT & 7003ICT - Assignment Part 1 Page 6 of 11

The bookings relation has pickupLocationType and dropoffLocationType that are theoretically dependent in
the pickupLocation and dropoffLocationType respectively. This imposes another transitive dependency in the
relation. However, it has been kept intentionally because moving those types into location table would create
a lot of data redundancy.
Vehicles relation: It is in third normal form. The dependency diagram is as following
Figure 5: Dependency Diagram of Vehicles Relation in 3rd Normal Form
(Source: Created by Author)
GameOfficials relation: It is in third normal form. The dependency diagram is as following
Figure 6: Dependency Diagram of GameOfficials Relation in 3rd Normal Form
(Source: Created by Author)
2814ICT & 7003ICT - Assignment Part 1 Page 7 of 11

Relational Database Schema
[Convert your ERD into a relational database schema.]
Relation: Vehicles
Attribute name Data type Field
length
Description Primary
key
Foreign key
VIN Char 25 Identifies each row uniquely Yes
regNumber Varchar 50 Registration number of
vehicle consist of letters and
numbers
make Varchar 50
colour Varchar 50
currentOdometer Integer 11
passengerCapacit
y
Integer 11
status Boolean 1 If vehicle is currently
available
Relation: Drivers
Attribute name Data type Field
length
Description Primary
key
Foreign key
DID Integer 11 Identifies each row uniquely Yes
fullName Varchar 50 First, middle and last name of
driver
licenceNumber Varchar 50 It is eighteen characters in
length
levelOfClearance Integer 11 1 to 4 representing the
security clearance of the
driver where 4 is the highest
level of clearance
Relation: GameOfficials
Attribute name Data type Field
length
Description Primary
key
Foreign key
coomonwealthID Char 8 Identifies each row uniquely.
It is eight character in length
Yes
country Integer 11 Reference to primary key of
Countries relation
Yes
fullName Varchar 50 First, middle and last name
of game official
role Char 20 Can be head-coach,
physician or may others
prefferedLanguag
e
Integer 11 Reference to primary key of
Languages relation
Yes
Relation: Maintenance
Attribute name Data type Field
length
Description Primary
key
Foreign key
MID Integer 11 Identifies each row uniquely Yes
VID Char 25 Reference to primary key of
Vehicles relation
Yes
mDate Date Date of maintenance
odometerReading Integer 11 Rating after maintenance
finalCost Decimal 10,2
briefDesc Text
type Char 10 Maintenance or Repair
2814ICT & 7003ICT - Assignment Part 1 Page 8 of 11

Relation: BookingCompletion
Attribute name Data type Field
length
Description Primary
key
Foreign key
booking Integer 11 Identifies each row
uniquely. Reference to
primary key of Bookings
relation
Yes Yes
actualStartDateTime Datetime Date and time both
actualEndDateTime Datetime Date and time both
startOdometerRating Integer 11
endOdometerRating Integer 11
Relation: Locations
Attribute name Data type Field
length
Description Primary
key
Foreign key
LID Integer 11 Identifies each row uniquely Yes
streetNumber Integer 11
streetName Varchar 50
suburb Varchar 50
state Varchar 50
postCode Integer 11
Relation: Bookings
Attribute name Data type Field
length
Description Primary
key
Foreign key
BID Char 25 Identifies each row
uniquely
Yes
vehicle Integer 11 Reference to primary key of
Vehicles relation
Yes
official Char 8 Reference to primary key of
GameOfficials relation
Yes
driver Integer 11 Reference to primary key of
Drivers relation
Yes
startDateTime Datetime Preferred not actual
endDateTime Datetime Preferred not actual
pickupLocation Integer 11 Reference to primary key of
Locations relation
Yes
pickupLocationType char 20 Can be hotel, airport,
restaurant
dropoffLocation Integer 11 Reference to primary key of
Locations relation
Yes
dropoffLocationType char 20 Can be hotel, airport,
restaurant
2814ICT & 7003ICT - Assignment Part 1 Page 9 of 11

Appendices
[Add any additional work other than what has been requested. Your marker may not look
at and mark the content in this section.]
2814ICT & 7003ICT - Assignment Part 1 Page 10 of 11

Bibliography
[Add any references (e.g., books, online documents, etc.) that you have used. Your marker
may not look at and mark the content in this section.]
Amin, M., Romney, G.W., Dey, P. and Sinha, B., 2019. Teaching Relational Database Normalization in an
Innovative Way. The Journal of Computing Sciences in Colleges, p.48.
Ľachová, K., Trebuňa, P., Bohács, G., Győrváry, Z., Kluth, A., Małkus, T., Tyrańska, M., Eben-Chaime, M.,
Frischer, R., Pollak, M. and Jančíková, Z.K., 2019. Modelling of electronic Kanban system by using of entity
relationship diagrams.
Noh, H.N., Bahari, M. and Zakaria, N.H., 2018. A Conceptual Model of Database Normalization Courseware
Using Gamification Approach. In PROCEEDINGS OF NEW ACADEMIA LEARNING INNOVATION
(NALI) SYMPOSIUM 2018 (p. 23).
Zhang, L., Shi, L., Zhang, B., Zhao, L., Dong, Y., Liu, J., Lian, Z., Liang, L., Chen, W., Luo, X. and Pei, S.,
2017. Probabilistic Entity-Relationship Diagram: A correlation between functional connectivity and
spontaneous brain activity during resting state in major depressive disorder. PloS one, 12(6).
2814ICT & 7003ICT - Assignment Part 1 Page 11 of 11