Airline Management System

Database Program

A. WITHOUT CONSTRAINTS

(Simple table creation – no validation rules)

1. Airline Table

CREATE TABLE Airline (

    airline_id INT,

    airline_name VARCHAR(50),

    country VARCHAR(40)

);

2. Flight Table

CREATE TABLE Flight (

    flight_id INT,

    airline_id INT,

    source VARCHAR(40),

    destination VARCHAR(40),

    departure_time TIME,

    arrival_time TIME,

    flight_type VARCHAR(15),

    total_seats INT,

    status VARCHAR(15)

);

3. Passenger Table

CREATE TABLE Passenger (

    passenger_id INT,

    passenger_name VARCHAR(50),

    gender CHAR(1),

    age INT,

    passport_no VARCHAR(20),

    nationality VARCHAR(40)

);

4. Booking Table

CREATE TABLE Booking (

    booking_id INT,

    flight_id INT,

    passenger_id INT,

    booking_date DATE,

    seat_no VARCHAR(5),

    fare DECIMAL(10,2),

    travel_class VARCHAR(15)

);

Insert Sample Data (Without Constraints)

INSERT INTO Airline VALUES

(1, 'Air India', 'India');

INSERT INTO Flight VALUES

(101, 1, 'Mumbai', 'Delhi', '10:00', '12:00', 'Domestic', 180, 'Scheduled');

INSERT INTO Passenger VALUES

(501, 'Rahul Mehta', 'M', 35, 'P1234567', 'Indian');

INSERT INTO Booking VALUES

(1001, 101, 501, '2024-02-01', '12A', 5500, 'Economy');

B. WITH CONSTRAINTS

(Complete validation & integrity rules)

1. Airline Table

(PRIMARY, UNIQUE, NOT NULL, DEFAULT)

CREATE TABLE Airline (

    airline_id INT PRIMARY KEY,

    airline_name VARCHAR(50) NOT NULL UNIQUE,

    country VARCHAR(40) DEFAULT 'India'

);

2. Flight Table

(PRIMARY, FOREIGN, CHECK, BETWEEN, DEFAULT, IN)

CREATE TABLE Flight (

    flight_id INT PRIMARY KEY,

    airline_id INT NOT NULL,

    source VARCHAR(40) NOT NULL,

    destination VARCHAR(40) NOT NULL,

    departure_time TIME NOT NULL,

    arrival_time TIME NOT NULL,

    flight_type VARCHAR(15)

        CHECK (flight_type IN ('Domestic','International')),

    total_seats INT

        CHECK (total_seats BETWEEN 50 AND 400),

    status VARCHAR(15) DEFAULT 'Scheduled',

    FOREIGN KEY (airline_id) REFERENCES Airline(airline_id)

);

3. Passenger Table

(PRIMARY, NOT NULL, UNIQUE, CHECK, DEFAULT)

CREATE TABLE Passenger (

    passenger_id INT PRIMARY KEY,

    passenger_name VARCHAR(50) NOT NULL,

    gender CHAR(1)

        CHECK (gender IN ('M','F')),

    age INT

        CHECK (age BETWEEN 1 AND 100),

    passport_no VARCHAR(20) UNIQUE,

    nationality VARCHAR(40) DEFAULT 'Indian'

);

4. Booking Table

(PRIMARY, FOREIGN, CHECK, DEFAULT, IN)

CREATE TABLE Booking (

    booking_id INT PRIMARY KEY,

    flight_id INT NOT NULL,

    passenger_id INT NOT NULL,

    booking_date DATE DEFAULT CURRENT_DATE,

    seat_no VARCHAR(5) NOT NULL,

    fare DECIMAL(10,2)

        CHECK (fare > 0),

    travel_class VARCHAR(15)

        CHECK (travel_class IN ('Economy','Business','First')),

    FOREIGN KEY (flight_id) REFERENCES Flight(flight_id),

    FOREIGN KEY (passenger_id) REFERENCES Passenger(passenger_id)

);

Insert Sample Data (With Constraints)

INSERT INTO Airline (airline_id, airline_name)

VALUES (1, 'Air India');

INSERT INTO Flight

(flight_id, airline_id, source, destination, departure_time, arrival_time, flight_type, total_seats)

VALUES

(101, 1, 'Mumbai', 'Delhi', '10:00', '12:00', 'Domestic', 180);

INSERT INTO Passenger

(passenger_id, passenger_name, gender, age, passport_no)

VALUES

(501, 'Rahul Mehta', 'M', 35, 'P1234567');

INSERT INTO Booking

(booking_id, flight_id, passenger_id, seat_no, fare, travel_class)

VALUES

(1001, 101, 501, '12A', 5500, 'Economy');