Online Bus Dispatch and Scheduling System Database Design

Online Bus Dispatch and Scheduling System Database Design

Introduction

The project entitled Bus Dispatch and Scheduling System is an online platform design and develop in PHP, MySQL and Bootstrap.

Displays information for dispatching list of buses, time, information about tickets and other information about buses. Provides easy access to makes schedule for buses. Provides security and user privacy access. Displays the bus information (time arrival, bus number, time schedule, etc.).

Electronic reports on system, next bus, and list of buses will arrive that time, by bus number and bus arrival basis for decision-making.

Significance of the Study

The results of the study were deemed beneficial to the following:

1. Terminal In charge. They were able to monitor the buses’ record in an organized way as well as came up with decisions based on facts gathered by the system.
2. They were able to inquire and update obligations easily using one of the features of the developed system.
3. They were able to take their schedules for the arrival of their bus in the terminal. And also making their way to arrive on the exact time in the terminal.
4. They were able to check the information about the tickets if were given exactly to the passengers.
5. The passengers will now be able to view the schedule of trips online and using their smartphone.

Features:

Admin

• Manage Dispatcher (search, create, update, delete record)
• Manage Schedule (search, create, update, delete record)
• Manage Bus Info (search, create, update, delete record)
• Manage Driver (search, create, update, delete record)

Dispatcher

• Update the terminal Location of the bus

Viewer-Passengers

• View the schedule information in the bulletin board and in mobile app

The Online Bus Dispatch and Scheduling System will now produce an output which is the buses schedules (with time arrival, bus number, terminal Location of the bus).

Database Design (Database Schema)

tblbranch (branchid, branchlocation, dateadded)

Description: branch table will store information of the different bus stops or branch location.

Create Table SQL Statement:

CREATE TABLE `tblbranch` (
  `branchid` int(11) NOT NULL,
  `branchlocation` varchar(100) NOT NULL,
  `dateadded` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblbus (busid, busnumber, bustype, dateadded, istravelling)

Description: tblbus will store information of buses such as the bus number/plate number, bus type (aircon, non-aircon), and if the bus is on travel or no.

Create Table SQL Statement:

CREATE TABLE `tblbus` (
  `busid` int(11) NOT NULL,
  `busnumber` varchar(15) NOT NULL,
  `bustype` varchar(15) NOT NULL,
  `dateadded` date NOT NULL,
  `istravelling` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbldriver (driverid, firstname, lastname, contactnumber, profilepicture, dateadded, istravelling)

Description: information of drivers suchas the firstname, lastname, contact number, profile picture will be stored in the tbldriver table.

Create Table SQL Statement:

CREATE TABLE `tbldriver` (
  `driverid` int(11) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `contactnumber` varchar(100) NOT NULL,
  `profile` varchar(100) NOT NULL,
  `dateadded` varchar(100) NOT NULL,
  `istravelling` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblschedule (scheduleid, busid, driverid, originlocation, destinationlocation, departuretime, arrivaltime, terminallocation, status, statusoperation, arrivedatdestination, dateadded)

Description: schedule of trips will be stored in the tblschedule table, it includes the bus info, driver info, origin of location, the destination of the bus, departure time, estimated arrival time, the location of the bus, and the actual time of arrival in the destination.

Create Table SQL Statement:

CREATE TABLE `tblschedule` (
  `scheduleid` int(11) NOT NULL,
  `busid` varchar(100) NOT NULL,
  `driverid` varchar(100) NOT NULL,
  `originlocation` varchar(100) NOT NULL,
  `destinationlocation` varchar(100) NOT NULL,
  `departuretime` datetime NOT NULL,
  `arrivaltime` datetime NOT NULL,
  `terminallocation` varchar(100) NOT NULL,
  `status` varchar(100) NOT NULL,
  `statusoperation` varchar(100) NOT NULL,
  `arrivedatdestination` varchar(100) NOT NULL,
  `dateadded` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbluser (userid, branchid, firstname, lastname, username, password, usertype, dateadded)

Description: users of the system will be the dispatchers and the administrators that has a full control on all of the features of the system.

Create Table SQL Statement:

CREATE TABLE `tbluser` (
  `userid` int(11) NOT NULL,
  `branchid` int(11) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `usertype` varchar(100) NOT NULL,
  `dateadded` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Flow of the System

1. Admin will encode first the following details
   1. Drivers
   2. Bus Info
   3. Dispatcher
2. Then Admin will encode the schedule of trips
3. The dispatcher will then update the trip if that bus reaches the bus stop or terminal. Ex. if the bus stops at terminal 1 then the terminal Location will then update, and the viewers from other terminal will be able to see that the certain bus is at terminal 1.
4. Passengers
   1. View the schedule information in the bulletin board

Development Tools:

PHP7, MariaDB and Bootstrap