bms-sql-query.md

BookMyShow-style Movie Show Listing – Database Design Task

submission by : Aryan Kathawale.

It's like the BookMyShow screen: pick a theatre and you see the next 7 days.
Choose a date → see all movies playing that day in that theatre, with showtimes, language, 2D/3D, certification, etc.

P1 – List entities, attributes + create normalized tables

Main entities + their key attributes:

1. Theatre

   • theatre_id (PK)
   • name
   • city
   • address
   • total_screens

2. Movie

   • movie_id (PK)
   • title
   • duration_minutes
   • release_date
   • language
   • certification (UA / A / U / etc.)

3. Screen (screens inside a theatre)

   • screen_id (PK)
   • theatre_id (FK)
   • screen_number
   • seating_capacity

4. Show (one specific screening – movie + time + screen)

   • show_id (PK)
   • screen_id (FK)
   • movie_id (FK)
   • show_date (DATE)
   • start_time (TIME)
   • format (2D / 3D / IMAX / etc.)
   • is_cancelled (bool)

-- 1. Theatre
CREATE TABLE Theatre (
    theatre_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    address VARCHAR(250),
    total_screens TINYINT UNSIGNED DEFAULT 1
);

-- 2. Movie
CREATE TABLE Movie (
    movie_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(150) NOT NULL,
    duration_minutes SMALLINT UNSIGNED NOT NULL,
    release_date DATE,
    language VARCHAR(40) NOT NULL,
    certification VARCHAR(10) DEFAULT 'UA'
);

-- 3. Screen
CREATE TABLE Screen (
    screen_id INT PRIMARY KEY AUTO_INCREMENT,
    theatre_id INT NOT NULL,
    screen_number TINYINT UNSIGNED NOT NULL,
    seating_capacity SMALLINT UNSIGNED NOT NULL,
    FOREIGN KEY (theatre_id) REFERENCES Theatre(theatre_id),
    UNIQUE KEY uk_theatre_screen (theatre_id, screen_number)
);

-- 4. Show
CREATE TABLE `Show` (
    show_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    screen_id INT NOT NULL,
    movie_id INT NOT NULL,
    show_date DATE NOT NULL,
    start_time TIME NOT NULL,
    format ENUM('2D','3D','IMAX','4DX','Dolby Cinema') DEFAULT '2D',
    is_cancelled TINYINT(1) DEFAULT 0,
    FOREIGN KEY (screen_id) REFERENCES Screen(screen_id),
    FOREIGN KEY (movie_id) REFERENCES Movie(movie_id)
);

Some sample data to test with:

-- One theatre
INSERT INTO Theatre (name, city, address) 
VALUES ('PVR Nexus Forum', 'Mumbai', 'Forum Mall, Andheri');

-- Few movies
INSERT INTO Movie (title, duration_minutes, language, certification) VALUES
('Dasara', 155, 'Telugu', 'UA'),
('Kisi Ka Bhai Kisi Ki Jaan', 145, 'Hindi', 'UA'),
('Tu Jhoothi Main Makkaar', 150, 'Hindi', 'UA'),
('Avatar: The Way of Water', 192, 'English', 'UA');

-- Screens in theatre 1
INSERT INTO Screen (theatre_id, screen_number, seating_capacity) 
VALUES (1, 1, 350), (1, 2, 180), (1, 3, 420);

-- Some shows for 25 Feb 2025
INSERT INTO `Show` (screen_id, movie_id, show_date, start_time, format) VALUES
(1, 1, '2025-02-25', '12:15:00', '3D'),      -- Dasara
(2, 2, '2025-02-25', '09:00:00', '2D'),       -- KKBKKJ
(2, 2, '2025-02-25', '14:10:00', '2D'),
(2, 2, '2025-02-25', '18:25:00', '2D'),
(3, 3, '2025-02-25', '09:15:00', '2D'),       -- Tu Jhoothi
(1, 4, '2025-02-25', '10:30:00', '3D'),       -- Avatar
(3, 4, '2025-02-25', '16:00:00', '3D');

P2 – Query to get all shows on a specific date for a specific theatre

SELECT 
    t.name                  AS theatre_name,
    sh.show_date,
    m.title                 AS movie_title,
    m.language,
    m.certification,
    sh.format,
    TIME_FORMAT(sh.start_time, '%l:%i %p') AS show_time,
    sc.screen_number
FROM 
    `Show` sh
    INNER JOIN Screen sc ON sh.screen_id = sc.screen_id
    INNER JOIN Theatre t  ON sc.theatre_id = t.theatre_id
    INNER JOIN Movie  m   ON sh.movie_id = m.movie_id
WHERE 
    t.theatre_id = 1
    AND sh.show_date = '2025-02-25'
    AND sh.is_cancelled = 0
ORDER BY 
    sh.start_time ASC;

This should give you the clean list of movies and their timings just like the BookMyShow page.
Tables are in 3NF / close to BCNF.

result :

  theatre_name   | show_date  |        movie_title        | language | certification | format | show_time | screen_number 
-----------------+------------+---------------------------+----------+---------------+--------+-----------+---------------
 PVR Nexus Forum | 2025-02-25 | Kisi Ka Bhai Kisi Ki Jaan | Hindi    | UA            | 2D     | 09:00 AM  |             2
 PVR Nexus Forum | 2025-02-25 | Tu Jhoothi Main Makkaar   | Hindi    | UA            | 2D     | 09:15 AM  |             3
 PVR Nexus Forum | 2025-02-25 | Avatar: The Way of Water  | English  | UA            | 3D     | 10:30 AM  |             1
 PVR Nexus Forum | 2025-02-25 | Dasara                    | Telugu   | UA            | 3D     | 12:15 PM  |             1
 PVR Nexus Forum | 2025-02-25 | Kisi Ka Bhai Kisi Ki Jaan | Hindi    | UA            | 2D     | 02:10 PM  |             2
 PVR Nexus Forum | 2025-02-25 | Avatar: The Way of Water  | English  | UA            | 3D     | 04:00 PM  |             3
 PVR Nexus Forum | 2025-02-25 | Kisi Ka Bhai Kisi Ki Jaan | Hindi    | UA            | 2D     | 06:25 PM  |             2
(7 rows)