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

Question

I am a fresher I want to design a basic bus booking system in which if user has selected a seat it will not be available to another user until the status of the seat booked or unbooked is clear by the first user. Basic bus booking system Maximum 5 seat user can book Seats available or unavailable User can search for bus timings and number of seats available with prices

Madan Shekar · 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.