Beginner

Campus event tickets: ERD case study

The student union sells tickets to its events and wants to replace its spreadsheet.

The case study

  1. The union runs events. Each event has an event ID, a title, an event date and a ticket price, and is held at exactly one venue.
  2. A venue has a venue ID, a name and a capacity. A venue can host many events, and a newly added venue may not have hosted any yet.
  3. Students buy tickets. We record each student's student number, name and email.
  4. A ticket has a ticket number and a purchase date. Each ticket is for exactly one event and is bought by exactly one student. A student can buy many tickets, and an event may not have sold any yet.

Your task

Draw an Entity Relationship Diagram in Crow's Foot notation. Show every entity with its attributes, mark each primary key (PK) and foreign key (FK), and set the minimum and maximum cardinality at both ends of every relationship.

Watch out: not every noun in the case study is an entity. Some only describe something else.

Draw it in the ERD Lab and get feedback on each entity, relationship and key.

Open the ERD Lab

Model answer

Show the model answer
Crow's Foot ERD for Campus event ticketshostssellsbuysVenuePK Venue IDNameCapacityEventPK Event IDTitleEvent dateTicket priceFK Venue IDTicketPK Ticket numberPurchase dateFK Event IDFK Student numberStudentPK Student numberNameEmail

Entities and keys

  • Venue: Venue ID (PK), Name, Capacity
  • Event: Event ID (PK), Title, Event date, Ticket price, Venue ID (FK)
  • Ticket: Ticket number (PK), Purchase date, Event ID (FK), Student number (FK)
  • Student: Student number (PK), Name, Email

Relationships, read both ways

  • Each venue hosts zero or more events. Each event is held at exactly one venue.
  • Each event sells zero or more tickets. Each ticket is for exactly one event.
  • Each student buys zero or more tickets. Each ticket is bought by exactly one student.

Nouns that are not entities

  • Capacity: Capacity only describes a venue. It's an attribute of Venue.
  • Ticket price: Ticket price describes an event. It's an attribute of Event.
  • Union: The union is the organisation that owns the system. There's only one, and we store nothing about it.
  • Purchase date: Purchase date describes a ticket. It's an attribute of Ticket.