Bus Tracking System

1. Steps for Admin Management - ST Bus Tracking System

Created By: Lovely Sharma

---

1. Go to bus-tracking-trbx.onrender.com

2. Click on Login

3. Click on Admin

4. Enter admin credentials

5. Click on Login

6. To view list of admins click on 'Manage Other Admins'

7. In order to add a new admin Click on 'Add Admin'

8. Fill the details for the registering new admin and click 'Register'

9. To view list of all users Click on 'Users'

10. You can search among users

11. To view list of drivers Click on 'Drivers'

12. List of drivers will be displayed over here

13. To view list of routes where bus services are available click on 'Routes'

14. To add a new stop click on 'Add Route' Button

15. Enter a new route for ex: "Gandhi Chowk Chandrapur" make it descriptive so that it's easily understandable and click 'Save'

16. To View list of buses Click on 'Bus'

17. If you want to add a new bus click on 'Add Bus'

18. Enter Bus details

19. Click on 'Add' and the bus will be added.

20. Now it's time to assign routes to newly added bus, click on pen icon to edit routes or say bus stops

21. Select all the intermediate stops where the bus will take a halt and enter it's arrival time

22. Once you're done Click on 'Save'

23. Now click on the bus name to view it's route map

24. Click on Live Status to view live status for all the active buses

25. Now if you wish to assign a driver to the newly added bus, Click on 'Drivers' and here you can either add a new driver for the newly added bus or assign a driver from the list of existing drivers by clicking the pen icon.

26. To add a new driver

27. Finally you can Click on 'Live Status' to view the live status of your newly added bus

---

Created with Tango.us

Database

1. Admin Info

CREATE TABLE AdminInfo(
	admin_id SERIAL PRIMARY KEY,
	admin_user_name varchar(20) not null,
	admin_password varchar(100) not null,
	admin_full_name varchar(50) not null,
	admin_contact_number varchar(10) not null,
	admin_dob DATE not null
)

2. Bus Info

CREATE TABLE BusInfo(
	bus_id SERIAL PRIMARY KEY,
	bus_name varchar(50) not null,
	bus_number varchar(20) not null,
	bus_model varchar(40) not null,
	bus_origin varchar(20) not null,
	bus_destination varchar(20) not null	
)

3. Route Info

CREATE TABLE RouteInfo(
	route_id SERIAL PRIMARY KEY,
	route_name varchar(40) not null,
)

6. RouteMapping

create table RouteMapping(
	route_id int,
	bus_id int,
	primary key (route_id, bus_id),
	FOREIGN KEY (route_id) REFERENCES RouteInfo(route_id) ON DELETE CASCADE,
	FOREIGN KEY (bus_id) REFERENCES BusInfo(bus_id) ON DELETE CASCADE
) 

4. Driver Info

CREATE TABLE DriverInfo(
	driver_id SERIAL PRIMARY KEY,
	driver_user_name varchar(20) not null,
	driver_password varchar(100) not null,
	driver_full_name varchar(50) not null,
	driver_gender VARCHAR(6) not null,
    driver_contact_number VARCHAR(10) not null,
	bus_id INT REFERENCES BusInfo(bus_id)
)

5. User Info

CREATE TABLE UserInfo(
	user_id SERIAL PRIMARY KEY,
	user_user_name varchar(20) not null,
	user_password varchar(100) not null,
	user_full_name varchar(50) not null,
    user_contact_number varchar(10) not null,
 	user_gender VARCHAR(6) not null,
    user_dob DATE not null,
    user_profile_pic varchar(50) 
)

6. BusLocation

CREATE TABLE BusLocation (
    bus_id INT primary key REFERENCES BusInfo(bus_id) ,
    latitude FLOAT NOT NULL,
    longitude FLOAT NOT NULL,
    timestamp TIMESTAMP DEFAULT NOW(),
	isActive boolean default false
);

Static Data

INSERT INTO BusInfo(bus_name, bus_number, bus_model, bus_origin, bus_destination)
VALUES ('Mumbai Express', 'MH-01-1234', 'Volvo XYZ', 'Mumbai', 'Pune');
INSERT INTO BusInfo(bus_name, bus_number, bus_model, bus_origin, bus_destination)
VALUES ('Shirdi Special', 'MH-02-5678', 'Mercedes ABC', 'Nashik', 'Shirdi');
INSERT INTO BusInfo(bus_name, bus_number, bus_model, bus_origin, bus_destination)
VALUES ('Ellora Explorer', 'MH-03-9012', 'Tata UVW', 'Aurangabad', 'Ellora');
INSERT INTO BusInfo(bus_name, bus_number, bus_model, bus_origin, bus_destination)
VALUES ('Mahabaleshwar Magic', 'MH-04-3456', 'Ashok Leyland PQR', 'Kolhapur', 'Mahabaleshwar');
INSERT INTO BusInfo(bus_name, bus_number, bus_model, bus_origin, bus_destination)
VALUES ('Pandharpur Express', 'MH-05-7890', 'Eicher UVW', 'Solapur', 'Pandharpur');
INSERT INTO BusInfo(bus_name, bus_number, bus_model, bus_origin, bus_destination)
VALUES ('Tadoba Safari', 'MH-06-2345', 'Scania XYZ', 'Nagpur', 'Tadoba');


INSERT INTO RouteInfo(route_name)
VALUES
    ('Lonavala'),
    ('Khandala'),
    ('Kamshet'),
    ('Talegaon'),
    ('Chinchwad'),
    ('Sinnar'),
    ('Vani'),
    ('Igatpuri'),
    ('Ghoti'),
    ('Kopargaon'),
    ('Khuldabad'),
    ('Daulatabad'),
    ('Shendra'),
    ('Shivajinagar'),
    ('Garkheda'),
    ('Karad'),
    ('Satara'),
    ('Wai'),
    ('Panchgani'),
    ('Bhose'),
    ('Akkalkot'),
    ('Barshi'),
    ('Indi'),
    ('Mohol'),
    ('Mangalwedha'),
    ('Umred'),
    ('Bhisi'),
    ('Chandrapur'),
    ('Warora'),
    ('Chimur');


INSERT INTO AdminInfo(admin_user_name, admin_password, admin_full_name, admin_contact_number, admin_dob)
VALUES
    ('admin1', 'password1', 'John Doe', '1234567890', '1990-01-01'),
    ('admin2', 'password2', 'Jane Smith', '2345678901', '1992-05-10'),
    ('admin3', 'password3', 'Michael Johnson', '3456789012', '1985-09-21'),
    ('admin4', 'password4', 'Emily Davis', '4567890123', '1998-12-15'),
    ('admin5', 'password5', 'David Wilson', '5678901234', '1993-07-03');


INSERT INTO DriverInfo(driver_user_name, driver_password, driver_full_name, driver_gender, driver_contact_number, bus_id)
VALUES
    ('driver1', 'password1', 'John Smith', 'Male', '1234567890', 1),
    ('driver2', 'password2', 'Emily Johnson', 'Female', '2345678901', 2),
    ('driver3', 'password3', 'Michael Brown', 'Male', '3456789012', 3),
    ('driver4', 'password4', 'Jessica Davis', 'Female', '4567890123', 4),
    ('driver5', 'password5', 'David Wilson', 'Male', '5678901234', 5),
    ('driver6', 'password6', 'Sophia Miller', 'Female', '6789012345', 6);


INSERT INTO UserInfo(user_full_name, user_gender, user_dob, user_contact_number, user_email, user_password)
VALUES
    ('Alex Johnson', 'Male', '1991-02-15', '1234567890', '[email protected]', 'password1'),
    ('Sophie Davis', 'Female', '1993-06-20', '2345678901', '[email protected]', 'password2'),
    ('Benjamin Wilson', 'Male', '1986-09-25', '3456789012', '[email protected]', 'password3'),
    ('Natalie Anderson', 'Female', '1989-04-10', '4567890123', '[email protected]', 'password4'),
    ('Ethan Martinez', 'Male', '1995-07-05', '5678901234', '[email protected]', 'password5'),
    ('Lily Thompson', 'Female', '1997-11-30', '6789012345', '[email protected]', 'password6'),
    ('William Harris', 'Male', '1988-12-12', '7890123456', '[email protected]', 'password7'),
    ('Grace Miller', 'Female', '1992-05-25', '8901234567', '[email protected]', 'password8'),
    ('Henry Jones', 'Male', '1994-09-08', '9012345678', '[email protected]', 'password9'),
    ('Chloe Robinson', 'Female', '1998-03-18', '0123456789', '[email protected]', 'password10');


-- Insert data for BusLocation table (intermediate stops)
INSERT INTO BusLocation (longitude, latitude, bus_id, isActive, current_stop)
VALUES
    (72.648101, 18.752287, 1, true, 'Lonavala'),
    (73.754463, 19.750900, 2, true, 'Nashik'),
    (75.888451, 20.067514, 3, true, 'Aurangabad'),
    (75.898413, 19.883699, 4, true, 'Karad'),
    (76.135082, 17.498504, 5, true, 'Akkalkot'),
    (79.301346, 20.842790, 6, true, 'Umred')