✈️ 1️⃣ Flights Table

✅ Purpose

Stores flight details.

✅ Table: Flights

 CREATE TABLE Flights (

    Flight_ID VARCHAR(10) PRIMARY KEY,

    Flight_Name VARCHAR(50),

    Source VARCHAR(50),

    Destination VARCHAR(50)

);

INSERT INTO Flights VALUES

('F101', 'Air India 101', 'Mumbai', 'Delhi'),

('F102', 'Indigo 202', 'Pune', 'Bangalore'),

('F103', 'SpiceJet 303', 'Delhi', 'Chennai');

✔ Primary Key (PK)

👉 Flight_ID

• Unique

• Cannot be NULL

• Identifies each flight
  
  

👤 2️⃣ Passengers Table

✅ Purpose

Stores passenger details.

✅ Table: Passengers

CREATE TABLE Passengers (

    Passenger_ID VARCHAR(10) PRIMARY KEY,

    Name VARCHAR(50),

    Age INT

);

✔ Primary Key

👉 Passenger_ID

INSERT INTO Passengers VALUES

('P1', 'Rahul', 32),

('P2', 'Sneha', 28),

('P3', 'Amit', 45);

🎟️ 3️⃣ Bookings Table

✅ Purpose

Connects passengers and flights.

✅ Table: Bookings

CREATE TABLE Bookings (

    Booking_ID VARCHAR(10) PRIMARY KEY,

    Passenger_ID VARCHAR(10),

    Flight_ID VARCHAR(10),

    Seat_No VARCHAR(5),

    FOREIGN KEY (Passenger_ID) REFERENCES Passengers(Passenger_ID),

    FOREIGN KEY (Flight_ID) REFERENCES Flights(Flight_ID)

);

INSERT INTO Bookings VALUES

('B1', 'P1', 'F101', '12A'),

('B2', 'P2', 'F102', '15C'),

('B3', 'P3', 'F101', '10B');

Test Queries:

SELECT p.Name, f.Flight_Name, b.Seat_No

FROM Bookings b

JOIN Passengers p ON b.Passenger_ID = p.Passenger_ID

JOIN Flights f ON b.Flight_ID = f.Flight_ID;

2. 🔹 Passengers booked on flight F101

SELECT Name

FROM Passengers

WHERE Passenger_ID IN

(

    SELECT Passenger_ID

    FROM Bookings

    WHERE Flight_ID = 'F101'

);

🔹 Flights with bookings

SELECT *

FROM Flights

WHERE Flight_ID IN

(

    SELECT Flight_ID

    FROM Bookings

);

🔑 Keys Explanation

✅ Primary Key (PK)

A column that:

✔ Uniquely identifies each record
✔ No duplicates
✔ No NULL values

Examples:

• Flight_ID
  
  

• Passenger_ID
  
  

• Booking_ID
  
  

✅ Foreign Key (FK)

A column that:

✔ Links to another table’s Primary Key
✔ Creates relationship between tables

Examples in Bookings table:

• Passenger_ID → refers to Passengers.Passenger_ID
  
  

• Flight_ID → refers to Flights.Flight_ID
  
  

🔗 Relationship Diagram (Simple)

Passengers ----< Bookings >---- Flights

Meaning:

• One passenger can have many bookings
  
  

• One flight can have many bookings
  
  

🎯 Simple Real-World Meaning

Primary Key

👉 Like Aadhaar number (unique ID)

Foreign Key

👉 Like referencing someone’s Aadhaar in another document

💡 Why this is important

✔ Prevents duplicate data
✔ Maintains data integrity
✔ Helps join tables easily
✔ Core concept in DBMS & SQL interviews