Commonwealth Transport Services Database Design Assignment 2022

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