Report: Designing a Database for Commonwealth Transport Services

This report details the database design for Commonwealth Transport Services, encompassing the initial ER diagram, key assumptions, and the process of normalizing tables to the Third Normal Form (3NF). It includes tables for Country, LocationType, Location, Driver, Vehicle, VehicleMaintenance, DriverLanguage, GameOfficials, and Booking, each normalized to 3NF. The report also provides a conversion of the ER diagram into a relational schema, specifying field types and descriptions for each table. References are provided for further reading on database design principles and ER diagram representation. This report is a comprehensive overview of database design principles applied to a practical scenario, suitable for database management students.

Student ID –
Date -
Designing a Database for Commonwealth Transport Services
Assignment Part 1
Module Tutor –
1 | P a g e

Report
Contents
ER Diagram................................................................................................................................3
Assumptions...............................................................................................................................3
Normalization of Tables.............................................................................................................4
Conversion of E-R diagram to relational schema......................................................................7
References..................................................................................................................................9
2 | P a g e

ER Diagram
(Tutorialspoint.com 2015)
Assumptions
1. The driver’s country and language will be accumulated into the database.
2. The cost of vehicle will be accumulated into the database along with availability,
passenger capacity.
3 | P a g e

Normalization of Tables
Table: Country
Country (ATRCountryCode, ATRLanguage)
Normalization: 3NF
Table: LocationType
LocationType (ATRTypeID, ATRType)
Normalization: 3NF
Table: Location
Location (ATRLocationID, ATRAddress, ATRTypeID)
Normalization: 3NF
4 | P a g e
ATRCountryCode ATRLanguage
ATRLocationID ATRAddress ATRTypeID
ATRTypeID ATRType

Table: Driver
Driver (ATRLicenceNumber, ATRName, ATRClearanceLevel, ATRFirstAidLevel,
ATRFirstAidQualDate, ATRSecurityLevel, ATRSecurityTrainingDate,
ATRCertifyingAuthority)
Normalization: 3NF
Table: Vehicle
Vehicle (ATRVIN, ATRRegistrationNumber, ATRMake, ATRModel, ATRColour,
ATRCost, ATRPessengerCapacity, ATRAvailability)
Normalization: 3NF
Table: VehicleMaintenance
5 | P a g e
ATRLicenceNu
mber
ATRName ATRClearanceLevel ATRFirstAidLevel
ATRFirstAidQualDate ATRSecurityLeve ATRSecurityTrainingDate
ATRCertifyingAuthority
ATRVIN ATRRegistrationNu ATRMake ATRMode ATRColour
ATRCost ATRPessengerCapacity ATRAvailability
ATRMaintenanceID ATRVIN ATRCurrentOdometer ATRStartDat ATRCost
ATRDescription ATRMode

VehicleMaintenance (ATRMaintenanceID, ATRVIN, ATRCurrentOdometer, ATRStartDate,
ATRCost, ATRDescription, ATRMode)
Normalization: 3NF
Table: DriverLanguage
DriverLanguage (ATRLicenceNumber, ATRCuntryCode, ATRLanguage)
Normalization: 3NF
Table: GameOfficials
GameOfficials (ATRCommonwealthID, ATRCountryCode, ATRLanguage, ATRName,
ATRRole)
Normalization: 3NF
Table: Booking
Booking (ATRBookingID, ATRLicenceNumber, ATRVIN, ATRCommonwealthID,
ATRStartDateTime, ATRProjectedDateTime, ATRPickupLocationID,
ATRDropOffLocationID, ATROdometerStartReading, ATROdometerEndReading)
6 | P a g e
ATRBookingID ATRLicenceNumbe ATRVIN ATRCommonwealthID
ATROdometerEndReadin
ATRStartDateTim
ATRProjectedDateTim ATRPickupLocationI ATRDropOffLocationID
ATROdometerStartReading
ATRLicenceNumber ATRCountryCode ATRLanguage
ATRCommonwealthI
D
ATRCountryCod ATRLanguage ATRNam ATRRole

Normalization: 3NF
Conversion of E-R diagram to relational schema
Table Field Type Description
COUNTRY ATRCountryCode VARCHAR(10) Primary Key
ATRLanguage VARCHAR(20)
LOCATIONTYPE ATRTypeID INTEGER Primary Key
ATRType VARCHAR(20)
LOCATION ATRLocationID INTEGER Primary Key
ATRAddress VARCHAR(100)
ATRTypeID INTEGER
DRIVER ATRLicenceNumber VARCHAR(20) Primary Key
ATRName VARCHAR(50)
ATRClearanceLevel VARCHAR(20)
ATRFirstAidLevel VARCHAR(20)
ATRFirstAidQualDate DATETIME
ATRSecurityLevel VARCHAR(20)
ATRSecurityTrainingDate DATETIME
ATRCertifyingAuthority VARCHAR(20)
VEHICLE ATRVIN VARCHAR(20) Primary Key
ATRRegistrationNumber VARCHAR(20)
ATRMake VARCHAR(20)
ATRModel VARCHAR(20)
ATRColour VARCHAR(20)
ATRCost DECIMAL(5,2)
ATRPessengerCapacity INTEGER
ATRAvailability BIT
VEHICLEMAINTENAN
CE
ATRMaintenanceID INTEGER Primary Key
ATRVIN VARCHAR(20) Foreign Key
References to
VEHICLE (ATRVIN)
ATRCurrentOdometer DECIMAL(5,2)
ATRStartDate DATETIME
ATRCost DECIMAL(5,2)
ATRDescription VARCHAR(100)
ATRMode VARCHAR(20)
DRIVERLANGUAGE ATRLicenceNumber VARCHAR(20) Primary Key
Foreign Key
References to Driver
(ATRLicenceNumber)
ATRCuntryCode VARCHAR(10) Primary Key
Foreign Key
References to Country
(ATRCuntryCode)
ATRLanguage VARCHAR(20) Primary Key
Foreign Key
References to Country
7 | P a g e

(ATRLanguage)
GAMEOFFICIALS ATRCommonwealthID VARCHAR(10) Primary Key
ATRCuntryCode VARCHAR(10) Foreign Key
References to Country
(ATRCuntryCode)
ATRLanguage VARCHAR(20) Foreign Key
References to Country
(ATRLanguage)
ATRName VARCHAR(50)
ATRRole VARCHAR(20)
BOOKING ATRBookingID INTEGER Primary Key
ATRLicenceNumber VARCHAR(20) Foreign Key
References to Driver
(ATRLicenceNumber)
ATRVIN VARCHAR(20) Foreign Key
References to
VEHICLE (ATRVIN)
ATRCommonwealthID VARCHAR(10) Foreign Key
References to
GameOfficials
(ATRCommonwealthI
D)
ATRStartDateTime DATETIME
ATRProjectedDateTime DATETIME
ATRPickupLocationID INTEGER Foreign Key
References to Location
(LocationID)
ATRDropOffLocationID INTEGER Foreign Key
References to Location
(LocationID)
ATROdometerStartReading DECIMAL(5,2)
ATROdometerEndReading DECIMAL(5,2)
(Carlo Zaniolo, 1982)
References
Carlo Zaniolo, A New Normal Form for the Design of Relational Database Schemata,
September 1982
8 | P a g e

Tutorialspoint.com 2015, ER Diagram Representation, Viewed on 22nd August 2017
<http://www.tutorialspoint.com/dbms/er_diagram_representation.htm>
9 | P a g e