can you share Database schema for bus booking system ASP.NET

Create Table Administrator

(

AId int primary key identity(1000,1),

AName varchar(100) not null ,

AEmail varchar(100) not null,

APassword varchar(50) not null

)

Create Table Customer

(

CId int primary key identity(100,1),

CFName varchar(100) not null,

CLFName varchar(100) not null,

CEmail varchar(100) not null,

CPassword varchar(50) not null,

CMobilePhone int not null,

)

Create Table BusCity

(

BCId varchar(10) primary key,

BName varchar(100) not null unique

)

Create Table BusType

(

BTId int identity(10,1) primary key,

BType varchar(100) not null unique

)

Create Table BusInformation

(

BId varchar(10) primary key,

BName varchar(100) not null unique,

BNumOfSeats int not null,

BFromCity varchar(10) not null,

BToCity varchar(10) not null,

BPrice int not null,

BTId int,

constraint Fk_BusInfo_BusType Foreign key(BTId) references BusType(BTId),

constraint Fk_BusInfo_BusFromCity Foreign key(BFromCity) references BusCity(BCId),

constraint Fk_BusInfo_BusToCity Foreign key(BToCity) references BusCity(BCId)

)

Create Table BookingDetails

(

BDId int identity(2000,1) primary key,

CId int,

BId varchar(10),

BDDateofTravel Date,

BDFromCity varchar(10) not null,

BDToCity varchar(10) not null,

BDNumOfSeats int not null,

BDPrice int not null,

constraint Fk_BookingDetails_BusInformation Foreign key(BId)references BusInformation(BId),

constraint Fk_BookingDetails_Customer Foreign key(CId)references Customer(CId),

constraint Fk_BusDetails_BusFromCity Foreign key(BDFromCity) references BusCity(BCId),

constraint Fk_BusDetails_BusToCity Foreign key(BDToCity) references BusCity(BCId)

)

Create Table BookedStatus

(

BSId int identity(3000,1) primary key,

BSAvailableSeats int not null,

BookedDate Date,

IsAvailable bit,

BId varchar(10),

constraint Fk_BookedStatus_BusInfo Foreign key(BId)references BusInformation(BId)

)

DDL Code:

Create Table Administrator

(

AId int primary key identity(1000,1),

AName varchar(100) not null ,

AEmail varchar(100) not null,

APassword varchar(50) not null

)

Create Table Customer

(

CId int primary key identity(100,1),

CFName varchar(100) not null,

CLFName varchar(100) not null,

CEmail varchar(100) not null,

CPassword varchar(50) not null,

CMobilePhone int not null,

)

Create Table BusCity

(

BCId varchar(10) primary key,

BName varchar(100) not null unique

)

Create Table BusType

(

BTId int identity(10,1) primary key,

BType varchar(100) not null unique

)

Create Table BusInformation

(

BId varchar(10) primary key,

BName varchar(100) not null unique,

BNumOfSeats int not null,

BFromCity varchar(10) not null,

BToCity varchar(10) not null,

BPrice int not null,

BTId int,

constraint Fk_BusInfo_BusType Foreign key(BTId) references BusType(BTId),

constraint Fk_BusInfo_BusFromCity Foreign key(BFromCity) references BusCity(BCId),

constraint Fk_BusInfo_BusToCity Foreign key(BToCity) references BusCity(BCId)

)

Create Table BookingDetails

(

BDId int identity(2000,1) primary key,

CId int,

BId varchar(10),

BDDateofTravel Date,

BDFromCity varchar(10) not null,

BDToCity varchar(10) not null,

BDNumOfSeats int not null,

BDPrice int not null,

constraint Fk_BookingDetails_BusInformation Foreign key(BId)references BusInformation(BId),

constraint Fk_BookingDetails_Customer Foreign key(CId)references Customer(CId),

constraint Fk_BusDetails_BusFromCity Foreign key(BDFromCity) references BusCity(BCId),

constraint Fk_BusDetails_BusToCity Foreign key(BDToCity) references BusCity(BCId)

)

Create Table BookedStatus

(

BSId int identity(3000,1) primary key,

BSAvailableSeats int not null,

BookedDate Date,

IsAvailable bit,

BId varchar(10),

constraint Fk_BookedStatus_BusInfo Foreign key(BId)references BusInformation(BId)

)

Online Bus Ticket Booking

This project has two user roles - First is Admin and second are Customers. The project allows customers to book their travel tickets online.

Admin part

Administrator user of the website can add city information, bus information, bus routes (From City to To City), Bus details(bus information/Bus ticket price per city to city details), No of Seats per bus, Available seats per bus on booking date, Bus Available and Booked status.

Customer

Customers can search available buses for booking tickets by Bus Name/ Traveling City From and To / Date of traveling with one way or return and by price.

There needs to be a search feature that will list all available buses and bus details.

For search and details, customers don't need to be logged in. Any user can search and check for available bus and their routes.

But for booking, Customer needs to be logged in, Customer can register to the web application for booking tickets .

After a customer is logged in, he or she can book a Bus Ticket by entering number of seats they need, date of travel, and From City and To City.

No need for payment details for now.

Customer can register and book tickets. Admin can view all booked information.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Madan Shekar7y ago
Accepted answer
Create Table Administrator
(
AId int primary key identity(1000,1),
AName varchar(100) not null ,
AEmail varchar(100) not null,
APassword varchar(50) not null
)
Create Table Customer
(
CId int primary key identity(100,1),
CFName varchar(100) not null,
CLFName varchar(100) not null,
CEmail varchar(100) not null,
CPassword varchar(50) not null,
CMobilePhone int not null,
)
Create Table BusCity
(
BCId varchar(10) primary key,
BName varchar(100) not null unique
)
Create Table BusType
(
BTId int identity(10,1) primary key,
BType varchar(100) not null unique
)
Create Table BusInformation
(
BId varchar(10) primary key,
BName varchar(100) not null unique,
BNumOfSeats int not null,
BFromCity varchar(10) not null,
BToCity varchar(10) not null,
BPrice int not null,
BTId int,
constraint Fk_BusInfo_BusType Foreign key(BTId) references BusType(BTId),
constraint Fk_BusInfo_BusFromCity Foreign key(BFromCity) references BusCity(BCId),
constraint Fk_BusInfo_BusToCity Foreign key(BToCity) references BusCity(BCId)
)
Create Table BookingDetails
(
BDId int identity(2000,1) primary key,
CId int,
BId varchar(10),
BDDateofTravel Date,
BDFromCity varchar(10) not null,
BDToCity varchar(10) not null,
BDNumOfSeats int not null,
BDPrice int not null,
constraint Fk_BookingDetails_BusInformation Foreign key(BId)references BusInformation(BId),
constraint Fk_BookingDetails_Customer Foreign key(CId)references Customer(CId),
constraint Fk_BusDetails_BusFromCity Foreign key(BDFromCity) references BusCity(BCId),
constraint Fk_BusDetails_BusToCity Foreign key(BDToCity) references BusCity(BCId)
)
Create Table BookedStatus
(
BSId int identity(3000,1) primary key,
BSAvailableSeats int not null,
BookedDate Date,
IsAvailable bit,
BId varchar(10),
constraint Fk_BookedStatus_BusInfo Foreign key(BId)references BusInformation(BId)
)

DDL Code:
Create Table Administrator
(
AId int primary key identity(1000,1),
AName varchar(100) not null ,
AEmail varchar(100) not null,
APassword varchar(50) not null
)
Create Table Customer
(
CId int primary key identity(100,1),
CFName varchar(100) not null,
CLFName varchar(100) not null,
CEmail varchar(100) not null,
CPassword varchar(50) not null,
CMobilePhone int not null,
)
Create Table BusCity
(
BCId varchar(10) primary key,
BName varchar(100) not null unique
)
Create Table BusType
(
BTId int identity(10,1) primary key,
BType varchar(100) not null unique
)
Create Table BusInformation
(
BId varchar(10) primary key,
BName varchar(100) not null unique,
BNumOfSeats int not null,
BFromCity varchar(10) not null,
BToCity varchar(10) not null,
BPrice int not null,
BTId int,
constraint Fk_BusInfo_BusType Foreign key(BTId) references BusType(BTId),
constraint Fk_BusInfo_BusFromCity Foreign key(BFromCity) references BusCity(BCId),
constraint Fk_BusInfo_BusToCity Foreign key(BToCity) references BusCity(BCId)
)
Create Table BookingDetails
(
BDId int identity(2000,1) primary key,
CId int,
BId varchar(10),
BDDateofTravel Date,
BDFromCity varchar(10) not null,
BDToCity varchar(10) not null,
BDNumOfSeats int not null,
BDPrice int not null,
constraint Fk_BookingDetails_BusInformation Foreign key(BId)references BusInformation(BId),
constraint Fk_BookingDetails_Customer Foreign key(CId)references Customer(CId),
constraint Fk_BusDetails_BusFromCity Foreign key(BDFromCity) references BusCity(BCId),
constraint Fk_BusDetails_BusToCity Foreign key(BDToCity) references BusCity(BCId)
)
Create Table BookedStatus
(
BSId int identity(3000,1) primary key,
BSAvailableSeats int not null,
BookedDate Date,
IsAvailable bit,
BId varchar(10),
constraint Fk_BookedStatus_BusInfo Foreign key(BId)references BusInformation(BId)
)
Online Bus Ticket Booking
This project has two user roles - First is Admin and second are Customers. The project allows customers to book their travel tickets online.
Admin part
Administrator user of the website can add city information, bus information, bus routes (From City to To City), Bus details(bus information/Bus ticket price per city to city details), No of Seats per bus, Available seats per bus on booking date, Bus Available and Booked status.
Customer
Customers can search available buses for booking tickets by Bus Name/ Traveling City From and To / Date of traveling with one way or return and by price.
There needs to be a search feature that will list all available buses and bus details.
For search and details, customers don't need to be logged in. Any user can search and check for available bus and their routes.
But for booking, Customer needs to be logged in, Customer can register to the web application for booking tickets .
After a customer is logged in, he or she can book a Bus Ticket by entering number of seats they need, date of travel, and From City and To City.
No need for payment details for now.
Customer can register and book tickets. Admin can view all booked information.