CHAPTER 8
DATABASE DESIGN
Table : tbl_country
tbl_country
CREATE TABLE
`airlinereservationsystem`.`tbl_country` (
`id` int(7) unsigned
NOT NULL auto_increment PRIMARY
KEY,
`country_name`
varchar(50) default NULL,)
Table : tbl_domestic_airlines
Table
tbl_domestic_airlines
CREATE TABLE
`airlinereservationsystem`.`tbl_domestic_airlines` (
`ID` int(10)
unsigned NOT NULL auto_increment PRIMARY KEY,
`AIRLINES_NAME`
varchar(45) NOT NULL,
`SEAT_ECONOMY`
varchar(45) NOT NULL,
`SEAT_BUSINESS`
varchar(45) NOT NULL,)
Table : tbl_flight_info
tbl_flight_info
CREATE TABLE
`airlinereservationsystem`.`tbl_flight_info` (
`ID` int(10)
unsigned NOT NULL auto_increment,
`FLIGHT_NUMBER`
varchar(45) NOT NULL,
`ORIGIN_CITY` int(3)
NOT NULL,
`DESTINATION_CITY`
int(3) NOT NULL,
`DEPARTURE_TIME`
int(10) unsigned NOT NULL,
`ARRIVAL_TIME`
int(10) unsigned NOT NULL,
`DEPARTURE_DAYS`
varchar(45) NOT NULL,
`AIRLINES_ID`
int(10) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `AIRLINES_ID`
(`AIRLINES_ID`),
CONSTRAINT
`tbl_flight_info_ibfk_1` FOREIGN KEY (`AIRLINES_ID`) REFERENCES
`tbl_domestic_airlines` (`ID`)
)
Table : tbl_geographical_cities
tbl_geographical_cities
CREATE TABLE
`airlinereservationsystem`.`tbl_geographical_cities` (
`ID` int(10)
unsigned NOT NULL auto_increment PRIMARY KEY,
`STATION_1` int(3)
NOT NULL, `STATION_2` int(3) NOT NULL,
`DISTANCE` int(10)
unsigned NOT NULL,)
Table : tbl_mastercity
tbl_mastercity
CREATE TABLE
`airlinereservationsystem`.`tbl_mastercity` (
`ID` int(10)
unsigned NOT NULL auto_increment PRIMARY KEY,
`CITY_NAME`
varchar(45) NOT NULL,
)
Table : tbl_registration
tbl_registration
Table
CREATE TABLE
`airlinereservationsystem`.`tbl_registration` (
`id` int(7) unsigned
NOT NULL auto_increment,
`salutation`
varchar(4) NOT NULL,
`first_name`
varchar(50) default NULL,
`last_name`
varchar(50) default NULL,
`email_id`
varchar(50) default NULL,
`password`
varchar(20) default NULL,
`city_name`
varchar(50) default NULL,
`country_id` int(7)
unsigned default NULL,
`address_1`
varchar(500) default NULL,
`address_2`
varchar(500) default NULL,
`mobile_number`
varchar(10) default NULL,
`alternate_number`
varchar(12) default NULL,
`pin_code`
varchar(6) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY
`email_id` (`email_id`),
KEY
`FK_tbl_registration` (`country_id`),
CONSTRAINT
`FK_tbl_registration` FOREIGN KEY (`country_id`) REFERENCES `tbl_country`
(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)
Table : tbl_reservation
tbl_reservation
CREATE TABLE
`airlinereservationsystem`.`tbl_reservation` (
`id` int(7) unsigned
NOT NULL auto_increment,
`user_id` int(7)
unsigned NOT NULL,
`flight_id` int(7)
unsigned default NULL,
`class` char(1)
default NULL,
`no_of_passengers`
int(3) unsigned default NULL,
`booking_date`
timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`travel_date`
timestamp NOT NULL default '0000-00-00 00:00:00',
`airlines_id` int(7)
unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY
`indx_user_id` (`user_id`),
UNIQUE KEY
`indx_flight_id` (`flight_id`),
KEY
`indx_airlines_id` (`airlines_id`),
CONSTRAINT
`FK_tbl_reservation_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_registration`
(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT
`FK_tbl_reservation_2` FOREIGN KEY (`id`) REFERENCES `tbl_flight_info` (`ID`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT
`FK_tbl_reservation_3` FOREIGN KEY (`airlines_id`) REFERENCES
`tbl_domestic_airlines` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
)
Table : tbl_state
tbl_state
CREATE TABLE
`airlinereservationsystem`.`tbl_state` (
`id` int(7) unsigned
NOT NULL auto_increment,
`state_name`
varchar(50) default NULL,
`country_id` int(7)
unsigned default NULL,
PRIMARY KEY (`id`),
KEY `FK_tbl_state`
(`country_id`),
CONSTRAINT
`FK_tbl_state` FOREIGN KEY (`country_id`) REFERENCES `tbl_country` (`id`) ON
DELETE CASCADE ON UPDATE CASCADE
)
Table : tbl_week_days
tbl_week_days
CREATE TABLE
`airlinereservationsystem`.`tbl_week_days` (
`ID` int(10) unsigned
NOT NULL auto_increment PRIMARY
KEY,
`DAYS` varchar(45)
NOT NULL,
)
tbl_passenger_details
CREATE
TABLE `airlinereservationsystem`.`tbl_passenger_details`
(
`id` int(10) unsigned NOT NULL
auto_increment,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`age` int(10) unsigned NOT NULL,
`gender` char(1) NOT NULL,
`registered_user_id` int(7) unsigned NOT
NULL,
PRIMARY KEY
(`id`),
KEY `indx_registered_user_details`
(`registered_user_id`),
CONSTRAINT `FK1` FOREIGN KEY
(`registered_user_id`) REFERENCES `tbl_reservation` (`user_id`) ON DELETE
CASCADE ON UPDATE CASCADE
)
Relationship between tables
No comments:
Post a Comment
We’re eager to see your comment. However, Please Keep in mind that all comments are moderated manually by our human reviewers according to our comment policy. Let’s enjoy a personal and evocative conversation.