2814ICT - Database Design Assignment: Commonwealth Transport Services

This assignment presents a comprehensive database design for Commonwealth Transport Services, fulfilling the requirements of the 2814ICT Database Management course. The project begins with an introduction to the importance of well-structured data for business enterprises and outlines the core components of the database design. It includes an Entity Relationship Diagram (ERD) detailing entities such as Vehicle, Maintenance, Country, Language, Game Official, Driver, Address, Location, Booking, and Completed Trip, along with their respective attributes. The design process incorporates assumptions to establish cardinalities between entities. The assignment then delves into database normalization, providing dependency diagrams for each relation to ensure the database is in third normal form (3NF). Finally, the relational database schema is presented, including table names, keys, attributes, data types, and field lengths, all designed in Microsoft Visio. This structured approach facilitates efficient data access and management for Commonwealth Transport Services. The document concludes with references to supporting materials used in the design process.

COMMONWEALTH TRANSPORT SERVICES DATABASE DESIGN
GROUP DETAILS:
1

Table of Contents
INTRODUCTION................................................................................................................................1
ENTITY RELATIONSHIP DIAGRAM....................................................................................................2
Entities and Attributes.................................................................................................................2
ENTITY RELATIONSHIP DIAGRAM.................................................................................................3
Assumptions.................................................................................................................................3
Database Normalization..................................................................................................................4
Dependency Diagram for each relation.......................................................................................4
Relational Database Schema...........................................................................................................7
Conclusion........................................................................................................................................9
References.....................................................................................................................................10
2

INTRODUCTION
Data being a very essential asset to any business enterprise, needs to well structured in order to
enable quick access. In this task, we shall be implementing a database design for
Commonwealth Transport Services. The entity relationship diagram (ERD), dependency
diagrams for relations and the relational database schema will be designed to illustrate the
database design.
3

ENTITY RELATIONSHIP DIAGRAM
Entities and Attributes
- The following are the database entities with their respective attribute:
No. Entity Attributes
1. Vehicle Vehicle Identification Number (VIN), Registration No, Make,
Model, Color, Current Odometer, Passenger Capacity, Available
2. Maintenance Maintenance ID, VIN, Odometer reading, Maintenance date, Final
Cost, Description, Action Nature
3. Country Country Code, Country Name
4. Language Language Code, Language Name
5. County Language ID, Country Code, Language Code
6. Game Official Official ID, Name, Role, Country Code, Language Code
7. Company Driver Driver ID, Name, License Number, Clearance Level, Language
Code
8. Address Address ID, Street No, Street Name, suburb, state, post code
9. Location Location id, Address ID, type
10. Booking Booking reference number, Driver ID, Official ID, Intended Start
date, intended start time, projected end date, projected end
time, pick up Location ID, Drop off location id
11. Completed Trip Completion ID, booking reference number, actual start date,
actual start time, actual end date, actual end time
4

ENTITY RELATIONSHIP DIAGRAM
(Chen & S, 2018)
Assumptions
The following are the assumptions made to arrive at the cardinalities:
5

i. A vehicle can be maintained or repaired several times and thus a cardinality of one
or many.
ii. A language may not be spoken in all the countries; however, it might be spoken in
many languages and thus a cardinality of one or many.
iii. A country may produce one or many game officials and therefore a cardinality of
one or many.
iv. Each game official may make one or many bookings and thus a cardinality of one or
many.
v. For every booking, a driver is involved. Therefore, a driver may be booked many
times and thus a cardinality of one or many.
vi. An address may be used in one or may locations and thus a cardinality of one or
many.
vii. A location may be referenced more than once in the bookings table and thus a
cardinality of one or many.
viii. For every completed trip, there is only one booking associated with it. This
transforms to one and only one cardinality.
Database Normalization
Dependency Diagram for each relation
- The following dependency diagrams illustrate relation normalization. All the tables are
in the third normal form (3NF).
6

Vehicle
VIN Reg_No Make Model Color Current_Odometer Pass_Capacity Available
Vehicle Maintenance Table
ID VIN Odometer_reading Date Final_Cost Description Action_Nature
Countries
Country Code Country Name
Languages
Language Code Language Name
Country_Languages
ID Country_Code Language_Code
7

Game Officials
Official_ID Name Role Country_Code Language_Code
Game Officials
Official_ID Name Role Country_Code Language_Code
Company Driver
Driver_ID Name License_Number Clearance_Level Language_Code
Addresses
Address_I
D
Street_No Street
Name
Suburb State Post_code
Locations
Location_ID Address Type
8

Bookings
Ref_No DriverID OfficialID Intended
_Start_
date
Intended
_start_
time
Projected
_end_date
projected
_end_time
Pickup
_Locatio
_ID
Dropoff
_location
_id
Completed Trips
CompletionI
D
Ref_N
o
ActualStartDat
e
ActualStartTim
e
ActualEndDat
e
ActualEndTim
e
Relational Database Schema
The diagram below illustrates the relational database schema. The table names, keys,
attributes, data types and field length have been included.
9

(Lioy & Kevin.s., 2019)
10

Conclusion
In conclusion, we have been able to design the database structure for Commonwealth
Transport services. Both the entity relationship diagram and the relational database schema
have been designed in Microsoft Visio.
11

References
Chen & S, P. P., 2018. The Entity-Relationship Model: Toward a Unified View of Data. 4th ed.
Sacramento: Creative Media Partners.
Lioy & Kevin.s., 2019. MySQL: SQL Database Programming for Beginner. 2nd ed.
s.l.:Independently Published.
12