Cinema Chain Database
A relational database for a cinema chain across Madrid, Barcelona, and Sevilla. I designed the full schema, populated it, and answered real business questions with SQL.
17 tables, third normal form.
The database models a cinema chain with three locations in Spain: Gran Via in Madrid, Diagonal in Barcelona, and Centro in Sevilla. Movie tickets are sold only through the website, so every ticket carries a registered customer, a card payment and a specific seat; concessions accept any payment method and anonymous walk-ins, so they needed their own transaction structure rather than a variant of the first. Below, the seventeen tables in the five functional groups they were designed in, with the seed rows each group carries, and the entity relationship diagram color-coded by the same groups.
| Group | Tables | What it holds | Seed rows |
|---|---|---|---|
| Location & facilities | location auditorium auditorium_type |
The physical chain: three sites, eight auditoriums, five auditorium types. | 163 tables |
| Films & scheduling | film film_genre genre showtime showtime_group |
The catalog and the schedule. film_genre is a junction table, so a film can carry several genres. |
625 tables |
| Movie ticket sales | movie_ticket seat ticket_status |
Online ticket transactions, one row per seat sold, with 160 seats to sell them against. | 4183 tables |
| Concessions | shop_ticket shop_item product product_category |
In-store food and drink: 104 orders across 184 line items. | 3034 tables |
| Shared entities | customer payment_method |
Bridge entities both sales systems read, required for tickets and optional for concessions. | 182 tables |
| 17 tables · 3NF | 817 rows | ||
The choices the diagram does not show.
Most of the work in a relational schema is in decisions a diagram cannot express: where a price lives, which relationship earns its own table, and what is worth storing twice. Six of them below, each with the table it lives in.
Auditorium-based pricingauditorium_type.Price_Multiplier
Prices are set by auditorium type, not by seat, which is how cinemas actually invest. Price_Multiplier gives the final price as base times multiplier, so pricing logic lives in one place.
Film to genre, many to manyfilm_genre
Films routinely span several genres. A junction table handles that without the redundancy and integrity problems of a comma-separated column.
Two separate ticketing systemsmovie_ticket · shop_ticket
movie_ticket requires a customer, a seat and a showtime. shop_ticket allows an anonymous purchase with none of them, because in a real foyer people buy snacks without watching a film.
Location stored on the ticketmovie_ticket.Location_ID
Location is derivable through showtime and auditorium, and stored on the ticket anyway. It keeps revenue-per-location reporting to a single join.
Showtime groups as time rangesStart_Range · End_Range
Each daily period carries a start and end time, not just a name. Tickets link to a group by foreign key; concession orders match on purchase time falling inside the range, which is what lets one query count both streams by time of day.
Shared customer and paymentcustomer · payment_method
A customer record is required for a ticket and optional for a shop order. The rule that tickets are card-only is a business rule, enforced in the application rather than the schema.
Everything you need.
Built with a team for the SQL II course at IE. Clone the repo, or download the schema script, the seed data and the Workbench model and run the whole database locally. The full eleven-page report is below: the business context, the design decisions, the normalization check, and all five business questions with their queries and output.