Skip to content
Zeni Study
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
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.