Skip to content
Zeni Study
  • Pages
    • Learn Python In 7 Days
      • Day 1 : Variable + if/else
      • Day 2: Loops
      • Day 3 : Array + String
      • Day 4 — Functions
      • Day 5 — Dictionaries - Hashmap
      • Day 6 — Files & Errors
      • Day 7 — Mini Project & Programmer Thinking
    • Machine Coding:
      • Day 1 — Bank account + Wallet :
      • Day 2 : RateLimiter + seat booking
      • Day 3 : Library Management + Classroom Attendance System
      • Day 4 : Bank account and notification system.
      • Day 5 : Key-value and payment processor.
      • Day 6 : Discount engine + Seat booking( in depth)
      • Day 7: Parking lot System :
    • Machine coding extended:
      • Day 7.5 — Parking Lot (Constraint-Heavy Variant)
      • Day 8 : Order + Payment System
      • Day 8 — Extension Round (Mid-Interview Change)
      • Day 9 — Ticket Booking with Expiry (Simulated Time)
      • Day 9 — Extension Round (Seat Lock Ownership + Forced Unlock)
    • SQL + DB Thinking course
      • Day 1 — Tables, Rows & Thinking in Data
      • Day 2 — Relationships & Foreign Keys (Thinking in Connections)
      • Day 3 — Filtering, Aggregation & Answering Data Questions
      • Day 4 — JOINs (Combining Tables the Right Way)
      • Day 5 — Subqueries, EXISTS & NOT EXISTS (Thinking in Layers)
      • Day 6 — Indexes, Constraints & How Databases Think
      • Day 7 — Full SQL Interview Simulation (SDE-1 Ready)
      • Extended Day 1 — Core Business Systems (Deep Practice)
      • icon picker
        Extended Day 2 — Booking & Platform Systems (High-Depth Practice)
      • Extended Day 3 — Content & Learning Platforms (Capstone Practice)
    • LLD: Java
      • DAY 1 — Core Foundations (SDE-2 Level)
      • DAY 2 — State, Time & Domain Correctness
      • DAY 3 — Extensibility & Failure Handling (SDE-2 Core Signal)
      • DAY 4 — Concurrency, Scheduling & Consistency (SDE-2 Critical)
      • DAY 5 — Workflow Design & Config-Driven Systems (SDE-2+)
      • DAY 6 — Eventing & Usage-Based Systems (Advanced SDE-2)
      • DAY 7 — Interview Simulation (Hard, Production-Grade)
      • Hidden Edge Cases Interviewers Expect (Day-wise)
      • Detailed Testing file:
    • Assignment:
      • If else Practice Set
Zeni Study
/
/
Extended Day 2 — Booking & Platform Systems (High-Depth Practice)
Share
Explore

Extended Day 2 — Booking & Platform Systems (High-Depth Practice)

Below is Extended Day 2 — Booking & Platform Systems, written so the student can independently practice real interview-level DB design, struggle a bit, and then self-correct using detailed solutions.
This day focuses on:
Deep relationships
State + lifecycle
Edge cases interviewers love
⁠

🟢 Extended Day 2 — Booking & Platform Systems (High-Depth Practice)

⏱ Time: 3–4 hours 🎯 Focus: Complex relationships + constraints + edge cases
⁠

🧠 Learning Objectives (Read First)

By the end of today, the student should be able to:
Design schemas for multi-layer platforms
Handle time, availability, and lifecycle
Model many-to-many relationships correctly
Avoid classic interview traps
Write SQL for availability, analytics, and edge cases
⚠️ Today’s problems look scary at first. That’s intentional. Interviews feel like this.
⁠

🧩 Problem 1 — Movie Ticket Booking System (BookMyShow Style)

⁠

📘 Problem Statement

Design a database for a movie ticket booking platform where:
Movies are shown in theatres
Each theatre has multiple screens
Each screen runs multiple shows
Each show has seats
Users book seats for a show
Key constraints:
Same seat can be booked in different shows
Seat availability is per show, not global
A booking can contain multiple seats
⁠

🧠 Step 1 — Think Before Writing SQL

Ask yourself:
Is a seat global or per show? → Global
Is availability global? → ❌ No, per show
Can a booking have multiple seats? → Yes
Can the same seat be booked twice for same show? → ❌ No
⁠

✍️ Step 2 — Identify Entities (Must Do)

Expected entities:
Movie
Theatre
Screen
Show
Seat
Booking
BookingSeat ← critical
❌ If BookingSeat is missing → schema is wrong.
⁠

🧱 Step 3 — Schema Design (Attempt First)

👉 Student Task Design tables with:
Primary keys
Foreign keys
Minimal but sufficient fields
Stop here and attempt.
⁠

📝 Step 4 — Interview Queries (Attempt)

Write SQL for:
Available seats for a given show
All theatres where a movie is playing today
Shows running for a movie
Bookings with no seats (edge case)
Number of seats booked per show
⁠

🔍 HINTS (Read Only If Stuck)

Seat availability = seat − booked seats
BookingSeat maps booking ↔ seat ↔ show
Movie → Show is indirect
“No seats” = LEFT JOIN + NULL
Don’t store seat availability as boolean
⁠

✅ Solution — Problem 1

✅ Schema

CREATE TABLE movies (
movie_id INT PRIMARY KEY,
title VARCHAR(100)
);

CREATE TABLE theatres (
theatre_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE screens (
screen_id INT PRIMARY KEY,
theatre_id INT,
FOREIGN KEY (theatre_id) REFERENCES theatres(theatre_id)
);

CREATE TABLE shows (
show_id INT PRIMARY KEY,
movie_id INT,
screen_id INT,
show_time TIMESTAMP,
FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
FOREIGN KEY (screen_id) REFERENCES screens(screen_id)
);

CREATE TABLE seats (
seat_id INT PRIMARY KEY,
screen_id INT,
seat_number VARCHAR(10),
FOREIGN KEY (screen_id) REFERENCES screens(screen_id)
);

CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
user_id INT,
show_id INT,
booking_time TIMESTAMP,
FOREIGN KEY (show_id) REFERENCES shows(show_id)
);

CREATE TABLE booking_seats (
booking_seat_id INT PRIMARY KEY,
booking_id INT,
seat_id INT,
FOREIGN KEY (booking_id) REFERENCES bookings(booking_id),
FOREIGN KEY (seat_id) REFERENCES seats(seat_id)
);
⁠

✅ Query Solutions

1️⃣ Available Seats for a Show
SELECT s.seat_id, s.seat_number
FROM seats s
JOIN shows sh ON s.screen_id = sh.screen_id
LEFT JOIN booking_seats bs ON s.seat_id = bs.seat_id
LEFT JOIN bookings b ON bs.booking_id = b.booking_id
AND b.show_id = sh.show_id
WHERE sh.show_id = 101
AND b.booking_id IS NULL;
⁠
2️⃣ Theatres Where a Movie Is Playing Today
SELECT DISTINCT t.theatre_id, t.name
FROM theatres t
JOIN screens sc ON t.theatre_id = sc.theatre_id
JOIN shows sh ON sc.screen_id = sh.screen_id
WHERE sh.movie_id = 10
AND DATE(sh.show_time) = CURRENT_DATE;
⁠
3️⃣ Shows Running for a Movie
SELECT sh.show_id, sh.show_time
FROM shows sh
WHERE sh.movie_id = 10;
⁠
4️⃣ Bookings With No Seats
SELECT b.booking_id
FROM bookings b
LEFT JOIN booking_seats bs ON b.booking_id = bs.booking_id
WHERE bs.booking_seat_id IS NULL;
⁠
5️⃣ Seats Booked Per Show
SELECT b.show_id, COUNT(bs.seat_id) AS seats_booked
FROM bookings b
JOIN booking_seats bs ON b.booking_id = bs.booking_id
GROUP BY b.show_id;
⁠

🧠 Reflection (Must Answer)

Why seat availability is per show?
Why not store seat availability flag?
Which table grows fastest?
Which joins were hardest?
⁠

🧩 Problem 2 — Ride Sharing Platform (Uber Lite)

⁠

📘 Problem Statement

Design a database where:
Users can be riders or drivers
Trips are booked by users
Each trip has:
rider
driver
start location
end location
status (REQUESTED, COMPLETED, CANCELLED)
Payments recorded per trip
⁠

🧠 Step 1 — Thinking

Ask:
Is driver different entity or user? → User
Can user be both rider & driver? → Yes
Can trip exist without payment? → Yes
⁠

✍️ Step 2 — Identify Entities

Expected:
User
Driver (role, not table)
Trip
Payment
⁠

🧱 Step 3 — Schema Design (Attempt First)

👉 Student Task Design schema carefully.
⁠

📝 Step 4 — Interview Queries

Write SQL for:
Total trips per driver
Drivers with no trips
Average trip fare
Users with most cancellations
⁠

🔍 HINTS

Driver is user_id in trips
Use status column
“No trips” → LEFT JOIN
“Most” → GROUP BY + ORDER BY
⁠

✅ Solution — Problem 2

✅ Schema

CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE trips (
trip_id INT PRIMARY KEY,
rider_id INT,
driver_id INT,
start_location VARCHAR(100),
end_location VARCHAR(100),
status VARCHAR(20),
fare INT,
created_at TIMESTAMP,
FOREIGN KEY (rider_id) REFERENCES users(user_id),
FOREIGN KEY (driver_id) REFERENCES users(user_id)
);

CREATE TABLE payments (
payment_id INT PRIMARY KEY,
trip_id INT,
amount INT,
payment_time TIMESTAMP,
FOREIGN KEY (trip_id) REFERENCES trips(trip_id)
);
⁠

✅ Query Solutions

1️⃣ Total Trips Per Driver
SELECT driver_id, COUNT(*) AS total_trips
FROM trips
GROUP BY driver_id;
⁠
2️⃣ Drivers With No Trips
SELECT u.user_id, u.name
FROM users u
LEFT JOIN trips t ON u.user_id = t.driver_id
WHERE t.trip_id IS NULL;
⁠
3️⃣ Average Trip Fare
SELECT AVG(fare) FROM trips WHERE status = 'COMPLETED';
⁠
Gallery
 
Want to print your doc?
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.