Mini project: cinema seat booking

60 min · Advanced

A seat booking is safe when the seats are locked before they are checked, and the check, the booking row and the seat update are committed together. That one transaction is the heart of every ticketing app, from cinemas to trains.

What you'll build

You write one SQL script, cinema-booking.sql, for a small cinema. It creates tables for shows, the seats of each show, bookings and refunds, and loads two evening shows of the same movie with six seats each.

The script then plays out a busy evening: Asha books C5 and C6, Ravi tries C6 and C7 and is refused because C6 is taken, Ravi books C7 and C8 instead, and Asha cancels and gets a 90% refund. A second click on cancel changes nothing. Finally you prove the booking is safe with two sessions racing for the same seat.

Sample run

+---------+--------+
| seat_no | status |
+---------+--------+
| C5      | FREE   |
| C6      | FREE   |
+---------+--------+
+------------+
| asha_seats |
+------------+
|          2 |
+------------+
+---------+--------+
| seat_no | status |
+---------+--------+
| C6      | BOOKED |
| C7      | FREE   |
+---------+--------+
+---------+--------+
| seat_no | status |
+---------+--------+
| C7      | FREE   |
| C8      | FREE   |
+---------+--------+
+------------+
| ravi_seats |
+------------+
|          2 |
+------------+
+-----------+
| cancelled |
+-----------+
|         1 |
+-----------+
+-----------------+
| cancelled_again |
+-----------------+
|               0 |
+-----------------+
+---------+--------+------------+
| seat_no | status | customer   |
+---------+--------+------------+
| C5      | FREE   | NULL       |
| C6      | FREE   | NULL       |
| C7      | BOOKED | Ravi Kumar |
| C8      | BOOKED | Ravi Kumar |
| D5      | FREE   | NULL       |
| D6      | FREE   | NULL       |
+---------+--------+------------+
+------------+------------+-------+--------+-----------+
| booking_id | customer   | seats | amount | status    |
+------------+------------+-------+--------+-----------+
|          1 | Asha Rao   |     2 | 500.00 | CANCELLED |
|          2 | Ravi Kumar |     2 | 500.00 | CONFIRMED |
+------------+------------+-------+--------+-----------+
+-----------+------------+--------+
| refund_id | booking_id | amount |
+-----------+------------+--------+
|         1 |          1 | 450.00 |
+-----------+------------+--------+

Requirements

Your program should do all of these.

The script runs from start to end on any database with mysql -u root -p practice < cinema-booking.sql, and can be run again: it drops its own tables first. Every seat of every show is one row in show_seats, with the primary key (show_id, seat_no), a status FREE or BOOKED and the booking that holds it.
A booking is one transaction: lock the wanted seats in seat order with SELECT ... FOR UPDATE, check they are all FREE, insert the booking and mark the seats BOOKED, then commit.
The seat UPDATE keeps the guard AND status = 'FREE', and its row count must equal the number of seats asked for; anything else means ROLLBACK. The booking amount comes from the show's price inside the database (2 * price), never from a number typed by hand.
A cancellation is one transaction: mark the booking CANCELLED only if it is CONFIRMED, free its seats and insert a refund of 90%. A repeated cancel changes nothing. Two sessions that race for the same seat end with exactly one booking; the other session waits and then sees the seat BOOKED.
The four tables and their columns, in this order: cinema_shows (show_id, movie, screen, starts_at, price), cinema_bookings (booking_id, show_id, customer, seats, amount, status), show_seats (show_id, seat_no, status, booking_id) and cinema_refunds (refund_id, booking_id, amount). Booking status is CONFIRMED or CANCELLED; seat status is FREE or BOOKED.
Load two shows of 'Kalki 2898 AD' on screen 'Audi 1': show 1 at '2025-06-14 18:30:00' for 250.00 and show 2 at '2025-06-14 22:00:00' for 200.00. Each show has the six seats C5, C6, C7, C8, D5 and D6, all FREE at the start.
The script prints these results in this order: the locking read (seat_no, status) for each booking attempt, asha_seats, ravi_seats, cancelled, cancelled_again, then the seat map of show 1 (seat_no, status, customer), the bookings (booking_id, customer, seats, amount, status) and the refunds (refund_id, booking_id, amount), each sorted by its first column.
The constraints stay in place as a safety net: a CHECK on price and on seats, a UNIQUE key on cinema_refunds.booking_id, and foreign keys from bookings, seats and refunds to their parents. A second row for the same seat, or a second refund for one booking, is refused.

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Transactions & ACID

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Sample run

+---------+--------+
| seat_no | status |
+---------+--------+
| C5      | FREE   |
| C6      | FREE   |
+---------+--------+
+------------+
| asha_seats |
+------------+
|          2 |
+------------+
+---------+--------+
| seat_no | status |
+---------+--------+
| C6      | BOOKED |
| C7      | FREE   |
+---------+--------+
+---------+--------+
| seat_no | status |
+---------+--------+
| C7      | FREE   |
| C8      | FREE   |
+---------+--------+
+------------+
| ravi_seats |
+------------+
|          2 |
+------------+
+-----------+
| cancelled |
+-----------+
|         1 |
+-----------+
+-----------------+
| cancelled_again |
+-----------------+
|               0 |
+-----------------+
+---------+--------+------------+
| seat_no | status | customer   |
+---------+--------+------------+
| C5      | FREE   | NULL       |
| C6      | FREE   | NULL       |
| C7      | BOOKED | Ravi Kumar |
| C8      | BOOKED | Ravi Kumar |
| D5      | FREE   | NULL       |
| D6      | FREE   | NULL       |
+---------+--------+------------+
+------------+------------+-------+--------+-----------+
| booking_id | customer   | seats | amount | status    |
+------------+------------+-------+--------+-----------+
|          1 | Asha Rao   |     2 | 500.00 | CANCELLED |
|          2 | Ravi Kumar |     2 | 500.00 | CONFIRMED |
+------------+------------+-------+--------+-----------+
+-----------+------------+--------+
| refund_id | booking_id | amount |
+-----------+------------+--------+
|         1 |          1 | 450.00 |
+-----------+------------+--------+

Requirements

Your program should do all of these.

The script runs from start to end on any database with mysql -u root -p practice < cinema-booking.sql, and can be run again: it drops its own tables first. Every seat of every show is one row in show_seats, with the primary key (show_id, seat_no), a status FREE or BOOKED and the booking that holds it.

A booking is one transaction: lock the wanted seats in seat order with SELECT ... FOR UPDATE, check they are all FREE, insert the booking and mark the seats BOOKED, then commit.

The seat UPDATE keeps the guard AND status = 'FREE', and its row count must equal the number of seats asked for; anything else means ROLLBACK. The booking amount comes from the show's price inside the database (2 * price), never from a number typed by hand.

A cancellation is one transaction: mark the booking CANCELLED only if it is CONFIRMED, free its seats and insert a refund of 90%. A repeated cancel changes nothing. Two sessions that race for the same seat end with exactly one booking; the other session waits and then sees the seat BOOKED.

The four tables and their columns, in this order: cinema_shows (show_id, movie, screen, starts_at, price), cinema_bookings (booking_id, show_id, customer, seats, amount, status), show_seats (show_id, seat_no, status, booking_id) and cinema_refunds (refund_id, booking_id, amount). Booking status is CONFIRMED or CANCELLED; seat status is FREE or BOOKED.

Load two shows of 'Kalki 2898 AD' on screen 'Audi 1': show 1 at '2025-06-14 18:30:00' for 250.00 and show 2 at '2025-06-14 22:00:00' for 200.00. Each show has the six seats C5, C6, C7, C8, D5 and D6, all FREE at the start.

The script prints these results in this order: the locking read (seat_no, status) for each booking attempt, asha_seats, ravi_seats, cancelled, cancelled_again, then the seat map of show 1 (seat_no, status, customer), the bookings (booking_id, customer, seats, amount, status) and the refunds (refund_id, booking_id, amount), each sorted by its first column.

The constraints stay in place as a safety net: a CHECK on price and on seats, a UNIQUE key on cinema_refunds.booking_id, and foreign keys from bookings, seats and refunds to their parents. A second row for the same seat, or a second refund for one booking, is refused.