INFO20003 Database Systems · University of Melbourne · 2020 Semester 1
The entity-relationship design I submitted in April 2020 for the Louvre's ticketing database, rebuilt from the original MySQL Workbench file. Switch between the Crow's-foot model, the Chen diagram and the refined 2026 schema, select an entity for its keys and SQL, or put the 2020 export side by side.
As designed (2020) against refined (2026)
The 2020 model is shown exactly as submitted; the refined schema that runs the demo database sits beside it. Every change below links to the design-review finding behind it.
3 kept6 restructured4 split2 merged1 replaced by a view
TicketticketrestructuredThe barcode keeps its own unique column beside a numeric key, the ticket points at a whole order, and the transport question moves to the first entry scan.
Why: Foreign keys point at part of a composite key; Re-entry through the same gate cannot be recorded; Where a sale happened is only recorded for card payments
Invoicespurchase_orderorder_linesplitOne invoice row per product became an order with order lines, so a sale can hold several products, records when and where it happened, and every foreign key points at a whole key.
Why: Foreign keys point at part of a composite key; No purchase records when it happened; A foreign key joins text to a number
ProductproductkeptSame role. Prices are integer euro cents instead of FLOAT, and each product has a fixed code.
Payment MethodspaymentrestructuredOne row per payment with a single method column, instead of a primary key built from two optional columns.
Why: Payment Methods has optional columns in its primary key; A foreign key joins text to a number
EFTPOScard_paymentfinancial_institutionsplitCard details become a subtype of payment sharing its key; banks move to their own table; card digits are stored as text so leading zeros survive.
Why: The masked card number is stored as an integer; Where a sale happened is only recorded for card payments; No purchase records when it happened
Cashcash_paymentkeptStill the first name, city and country of a cash payer, now a subtype of payment sharing its key.
Why: Payment Methods has optional columns in its primary key
Entranceentry_scanentrancesplitEvery entrance scan gets its own row and key, so re-entry through the same gate fits; the five gate names become a lookup table.
Wingswing_scanwingsplitKeyed by the wing's name, the 2020 table could hold three rows in total. Each wing scan now has its own row, ticket and time; the wings become a lookup table.
Hired Audio Guideaudio_guide_hirelanguagerestructuredEach hire has its own key and records the order that paid for it; the 13 languages become a lookup table instead of an ENUM.
Why: Audio guides still in service cannot be registered; Spaces and typos in names
Audio Guide Deviceaudio_guide_devicerestructuredThe retirement date is optional, so a device still in service can be registered (and its hires stored).
Special ExhibitionexhibitionkeptSame role, with its run dates and the places per slot.
Time Slotexhibition_slotrestructuredA slot is now a dated 15-minute slot of one exhibition, with its capacity, instead of a time of day shared by every exhibition.
Exhibition Bookingexhibition_bookingmergedMerged with Booking Ticket: one row per ticket per slot, with its own key, and a trigger that stops overbooking.
Why: Foreign keys point at part of a composite key; Three overlapping ways to link tickets and bookings, and a stored count; Time slots have no date and no exhibition
Booking Ticketexhibition_bookingmergedIts only job, linking tickets to bookings, is now a column of exhibition_booking.
Why: Foreign keys point at part of a composite key; Three overlapping ways to link tickets and bookings, and a stored count
Hall Napoleonhall_napoleon_scanrestructuredAn admission belongs to one booking (at most one per booking), so attendance can be traced to an exhibition.
Why: Three overlapping ways to link tickets and bookings, and a stored count; Time slots have no date and no exhibition
Actual Number of Visitorslot_availability (view)replaced by a viewA stored count that could drift from the rows it counts becomes a view computed from bookings and admissions.
Why: Three overlapping ways to link tickets and bookings, and a stored count
museum_visit (view)One row per visit (a ticket's first entry), for counting visitors.
The brief
The case study cast students as the team designing a transactional MySQL database for the Louvre: how visitors buy tickets, how they move through the entrances and wings, and how audio guides and timed exhibition bookings work. The deliverables were a conceptual model in Chen's notation, a physical model in Crow's-foot notation built in MySQL Workbench with up to 400 words of assumptions, and the .mwb file. Worth 10%, due 3 April 2020. No SQL had to be written; the brief listed ten business questions the design should be able to answer.
Visitors buy tickets online in advance or at an entrance on the day, by card, digital wallet or (on site) cash. For cards the museum keeps the bank, branch and country, the account name, a masked card number and the expiry; for cash, only a first name, city and country.
Tickets are scanned at one of five entrances, and again at each of the three wings. The first scan of the day records how the visitor travelled; a ticket is valid until closing that day and can re-enter.
Guides are hired at a wing and returned at any wing. The museum wants the language, both wings and how long each guide was out, and keeps a register of devices by serial number. The official app is sold through the app stores.
Exhibitions in the Hall Napoleon are included in the ticket but must be booked in timed slots with limited places. The museum must always know how many places are booked, admitted and still free in every slot.
April 2020
A Chen diagram of 11 entities and 11 relationships, drawn in Axure; a physical model of 16 tables and 18 foreign keys in MySQL Workbench; and 13 numbered assumptions explaining the cardinalities. These are the original exports, unchanged.
2026 revival
I forward-engineered the model to MySQL, replayed five years of synthetic activity through it, and checked it against every question in the brief. The diagrams look reasonable; running them is less kind.
13 / 16
tables created on MySQL
Run on MySQL 26.7.0, 3 CREATE TABLE statements fail: a primary key with optional columns and foreign keys that point at half a key. MySQL 8.0, current in 2020, would have created 15.
3 / 19,545
wing visits the Wings table can keep
Wings is keyed by the wing's name, so after one visit to Denon no other can be stored. The order of wings cannot be answered.
6 · 2 · 2
questions answerable · partly · not
Most of the brief's questions can be answered from the 2020 design; the wing questions cannot, and purchases carry no date.
Explore
The refined schema holds 66,219 synthetic rows across 19 tables, covering 1 March 2015 to 29 February 2020. Nothing in it is real visitor data.
The diagrams on their own page, with the 13 assumptions and how the model was built, file by file.
OpenThe brief's ten questions, each answered in SQL against five years of synthetic activity, with a verdict on the 2020 design.
OpenRun your own SQL in the browser against the refined database, or against the 2020 design rebuilt with the same data.
OpenEvery table and row of the demo database, searchable, with a CSV download for each table.
OpenWhat the refined schema's indexes buy, measured: repeated timings with and without them, medians with intervals.
OpenOptional, with your own key: a model writes the SQL, a validator checks it and it runs read-only in your browser.
OpenHow often a model's SQL matches the hand-written answers, with Wilson intervals and paired comparisons.
OpenProvenance, evaluation design, limitations, the AI use statement, model and data cards, decision records.
OpenAbout this project
coursework/)