 DROP TABLE IF EXISTS Airline CASCADE;
DROP TABLE IF EXISTS User_Category CASCADE;
DROP TABLE IF EXISTS Customer CASCADE;
DROP TABLE IF EXISTS Registered_Customer_Account CASCADE;
DROP TABLE IF EXISTS Traveller_Class CASCADE;
DROP TABLE IF EXISTS Airport CASCADE;
DROP TABLE IF EXISTS Plane_Type CASCADE;
DROP TABLE IF EXISTS Aircraft_Seat CASCADE;
DROP TABLE IF EXISTS Aircraft_Instance CASCADE;
DROP TABLE IF EXISTS Route CASCADE;
DROP TABLE IF EXISTS Flight_Schedule CASCADE;
DROP TABLE IF EXISTS Seat_Price CASCADE;
DROP TABLE IF EXISTS Booking CASCADE;
DROP TABLE IF EXISTS Pilot CASCADE;
DROP TABLE IF EXISTS session CASCADE;
DROP TABLE IF EXISTS Guest_Customer CASCADE;

DROP TYPE IF EXISTS  Booking_Status_Enum;
DROP TYPE IF EXISTS  Flight_Status_Enum;
DROP TYPE IF EXISTS  aircraft_status_Enum;
DROP TYPE IF EXISTS  gender_type_Enum;
DROP TYPE IF EXISTS  customer_category_Enum;
DROP TYPE IF EXISTS  registered_customer_category;

/*
---------------------------------------------------------------
*/
CREATE TYPE Flight_Status_Enum AS ENUM(
'Scheduled',
'Departed-On-Time',
'Delayed-Departure',
'Landed',
'Cancelled'
);

 CREATE TYPE aircraft_status_Enum AS ENUM( 
 'On-Ground',
 'In-Air');  

CREATE TYPE Booking_Status AS ENUM(
'Not paid',
'Paid'); 

CREATE TYPE gender_type_Enum AS ENUM(
'Male',
'Female',
'Other'); 

CREATE TYPE customer_category_Enum AS ENUM(
'guest',
'registered'
);

CREATE TYPE registered_customer_category AS ENUM(
'General',
'Frequent',
'Gold'
);

CREATE TYPE class_type_Enum AS ENUM(
'Economy',
'Business',
'Platinum'); 

/*
---------------------------------------------------------------
*/




CREATE TABLE Airport (
  Code varchar(10),
  Country varchar(20),
  City varchar(20),
  State varchar(20),
  PRIMARY KEY (Code)
);



CREATE TABLE Airline (
  Airline_Name varchar(30),
  Airline_Hotline varchar(20),
  Airline_Email varchar(50),
  Address_1 varchar(100),
  Address_2 varchar(100),
  PRIMARY KEY (Airline_Name)
);

CREATE TABLE Aircraft_Instance (
  Aircraft_ID SERIAL,
  Model_ID int,
  Airline_Name varchar(15),
  Aircraft_Status aircraft_status_enum,
  Maintenance_Date Date,
  Purchase_Date Date,
  PRIMARY KEY (Aircraft_ID),
  FOREIGN KEY (Airline_Name) REFERENCES Airline(Airline_Name)
);



CREATE TABLE User_Category (
  User_Type registered_customer_category,
  Minimum_Bookings varchar(10),
  Discount numeric(4,2),
  PRIMARY KEY (User_Type)
);

CREATE TABLE Customer (
  Customer_ID SERIAL,
  Customer_Type customer_category_Enum,
  PRIMARY KEY (Customer_ID)
);

CREATE TABLE Registered_Customer_Account (
  Customer_ID SERIAL,
  Password varchar(50),
  First_Name varchar(25),
  Last_Name varchar(25),
  Gender gender_type_Enum,
  DOB DATE,
  Email varchar(50),
  Mobile varchar(15),
  User_Type registered_customer_category,
  Address varchar(80),
  Country varchar(50),
  Passport_no varchar(20),
  No_of_Bookings int,
  Joined TIMESTAMP,
  display_photo bytea,
  PRIMARY KEY (Customer_ID)
);

CREATE TABLE Guest_Customer_Account (
  Customer_ID SERIAL,
  First_Name varchar(25),
  Last_Name varchar(25),
  Gender gender_type_Enum,
  DOB DATE,
  Passport_no varchar(20),
  Email varchar(50),
  Mobile varchar(15),
  PRIMARY KEY (Customer_ID)
);

CREATE TABLE Traveller_Class (
  class_id SERIAL,
  Class _name class_type_Enum,
  PRIMARY KEY (class_id),
);

CREATE TABLE Aircraft_Seat (
  Model_ID INT,
  Seat_ID SERIAL,
  Traveler_Class_ID int,
  PRIMARY KEY (Model_ID, Seat_ID),
  FOREIGN KEY (class_id) REFERENCES Traveller_Class(Traveler_Class_ID)
);

CREATE TABLE `Plane_Type` (
  `Model_ID` SERIAL,
  `Model_Name` varchar(30),
  `variant` varchar(15),
  `Manufacturer` varchar(20),
  `Economy_Seat_Capacity` int,
  `Business_Seat_Capacity` int,
  `Platinum_Seat_Capacity` int,
  `E_seats_per_row` int,
  `B_seats_per_row` int,
  `P_seats_per_row` int,
  `max_load` numeric(12,2),
  `fuel_capacity` numeric(12,2),
  PRIMARY KEY (`Model_ID`),
  FOREIGN KEY (`Model_ID`) REFERENCES `Aircraft_Seat`(`Model_ID`),
  FOREIGN KEY (`Model_ID`) REFERENCES `Aircraft_Instance`(`Model_ID`)
);

CREATE TABLE `Route` (
  `Route_ID` SERIAL,
  `Origin` varchar(10),
  `Destination` varchar(10),
  `Duration` interval,
  PRIMARY KEY (`Route_ID`),
  FOREIGN KEY (`Destination`) REFERENCES `Airport`(`Code`),
  FOREIGN KEY (`Origin`) REFERENCES `Airport`(`Code`)
);

CREATE TABLE `Flight_Schedule` (
  `Flight_ID` SERIAL,
  `Route_ID` INT,
  `Aircraft_ID` varchar(5),
  `Arrival_Date` Date,
  `Arrival_Time` DATETIME,
  `Departure_Date` Date,
  `Departure_Time` DATETIME,
  `Flight_Status` Flight_Status_Enum,
  `Aircraft_ID` varchar(5),
  `Pilot_ID` varchar(5),
  PRIMARY KEY (`Flight_ID`),
  FOREIGN KEY (`Route_ID`) REFERENCES `Route`(`Route_ID`)
);

CREATE TABLE `Seat_Price` (
  `Route_ID` INT,
  `class_ID` class_type,
  `price` numeric(10,2),
  PRIMARY KEY (`Route_ID`, `class_ID`),
  FOREIGN KEY (`Route_ID`) REFERENCES `Route`(`Route_ID`)
);

CREATE TABLE `Booking ` (
  `Booking_ID` SERIAL,
  `Customer_ID` INT,
  `Flight_ID` INT,
  `Seat_Price` numeric(10,2),
  `Discount` numeric(4,2),
  `Final_Price` numeric(10,2) - AUTO CALCULATED,
  `Booking_Status` Booking_Status_Enum,
  `Booking_Date` Date,
  PRIMARY KEY (`Booking_ID`),
  FOREIGN KEY (`Customer_ID`) REFERENCES `Customer`(`Customer_ID`),
  FOREIGN KEY (`Flight_ID`) REFERENCES `Flight_Schedule`(`Flight_ID`)
);

CREATE TABLE `Pilot` (
  `Pilot_ID` SERIAL,
  `First_Name` varchar(30),
  `Last_Name` varchar(30),
  `Contact` varchar(15),
  `Email` varchar(30),
  `DOB` DATE,
  `Gender` gender_type,
  `Assigned_Airport` varchar(10),
  `Country` varchar((20),
  PRIMARY KEY (`Pilot_ID`),
  FOREIGN KEY (`Assigned_Airport`) REFERENCES `Airport`(`Code`)
);

created 2 years ago by saadha salim

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

Postgres is not only free and open-source but also it is highly extensible.
Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
ACID(Atomicity, Consistency, Isolation, Durability) compliant.
First DBMS which implemented Multi-version concurrency control (MVCC) feature.
It's the default database server for MacOS.
It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;