INFO20003 S2 2023 – ASSIGNMENT 1 v1.0 1
INFO20003 Semester 2, 2023
Assignment 1: ER Modelling
Due: Week 5 - Friday 25th August 2023, 5:59pm
Submission: Via LMS https://canvas.lms.unimelb.edu.au/
Ticketing System
Given the recent issues surrounding ticket sales for live concerts, such as crashing ticketing websites, tickets not
allocated fairly, and (more recently) the poor queuing systems affecting ticket purchases for sold-out events, your
team is tasked by a startup company, TicketSwift, to create the database infrastructure for a new ticketing system.
As part of this, you will be creating a MySQL database to store this information. The following specifications have
been provided to you to assist in your design.
A concert is an event in which an artist performs to their fans in a live setting. For most concerts, fans buy tickets
to seats at the concert, given a specific event (e.g., Elvis can have 5 shows in one day in Melbourne). However, to
cope with the high demand for tickets for some events (e.g., Taylor Swift's recent sold-out Eras Tour), fans are
sometimes required to create 'waitlist entries' for tickets, in which organisers will select winning entries to be
converted to tickets (e.g., at random, or by selection to weed out ticket bots or scalpers). Your team is going to be
helping to create the technical infrastructure for the event, tickets, and fans.
Concerts
For each concert series, the system records its details, that are: name (e.g., Map of the Soul Tour), a single artist
(e.g., BTS), and promoter (e.g., Apple Inc). Then, for each concert series, we have different events: an event
consists of a number (e.g., 6, which means it’s the 6th show in this concert series), location (e.g., Melbourne,
Australia), venue (e.g., Marvel Stadium), the event date and time, and the URL to the event website. The location,
date/time, and the venue can obviously differ between events. An event also has a special flag indicating if tickets
are sold regularly or are sold using a waitlist.
INFO20003 S2 2023 – ASSIGNMENT 1 v1.0 2
Each event has at least one ‘zone’, where each zone has different requirements on the seats/spots fans can
purchase tickets to. Each zone is associated with the following information: a zone type (e.g., VIP, general
admission, standing, early-bird, etc.), zone description (as a text description of maximum 500 characters), and
safety limit (e.g., 100 people) of the zone, to comply with existing fire and occupational health and safety codes.
Each zone is associated with exactly one event (as, say, venues such as Melbourne Town Hall can reconfigure its
general admission section to fit any seating configuration). The information of a zone, therefore, can be different
for different occurrences of the event - i.e., even different events in the same concert tour at the same venue have
different zone availabilities (due to, say, last-minute restricted seat releases).
Each zone has at least one 'seat' (even for standing room locations, a 'seat' is allocated purely to keep track of
attendances for safety/headcount reasons). Each seat has a pair of coordinates (e.g., row 3 column 2; or row A
column AB, or for standing room tickets, row STANDING column 234), a flag indicating if the seat is accessible for
wheelchair users, and any special comments of the seat (e.g., some venues mark certain seats as "limited viewing"
or "obstructed view"). Each seat has one or zero fans who have purchased a ticket to that seat.
‍ Fans
For each fan, the system records their details such as first name, last name, a unique email address, a unique
phone number, the names of any affiliations (e.g., Taylor Swift Fan Club, Live Gig VIP List, Simon Music Inc), and
warnings. A fan can be affiliated with any number of affiliations. As for warnings, this is defined as the number of
times a fan has been warned for activities such as misbehaving during concerts; for most fans, this will be zero; but
this is up to an event staff member to enforce.
A unique feature of the system is that it keeps track of the social connections between fans. This allows Fan A (say,
Aoife) to be notified when their friend, Fan B (say, Barbara), purchases a ticket (like a 'TicketSwift recommender
system'). Each fan can have up to 200 connections with other fans. For each fan connection, the system stores the
start date of the connection (e.g., “Aoife and Barbara have been connected fans since 19/12/2012”). If a
connection is ended by one of the fans, it should no longer be recorded in the system.
Now, there are two circumstances for ticket purchases…
Tickets and Waitlisting
For most events, fans can simply buy tickets as per other ticketing systems they are familiar with. The system
maintains the information of the tickets that are purchased. Each ticket is associated with the following
information: seat, fan, date and time of purchase, payment processor (we only accept PayPal, Google Pay, WeChat
Pay, and Apple Pay), and transaction ID (e.g., 00F3123FC34 – transaction IDs are unique across all payment
processors). A fan can purchase up to 6 tickets for the purpose of deterring scalping. There can only be at most one
fan per ticket, obviously. If a fan buys tickets for other people, the tickets will be under the fan’s name instead of
the intended holders of the tickets.
INFO20003 S2 2023 – ASSIGNMENT 1 v1.0 3
Now, for highly popular shows, there is another option available [see also under Concerts above] to configure
ticket sales to be waitlist applications instead. In this case, an event can have any amount of waitlist entries; each
waitlist entry contains a free-form text field (e.g., if promoters want fans to write in with the best slogan to be
shortlisted). A fan can have at most one waitlist entry to a specific event (in the case of Taylor Swift's Eras Tour in
Melbourne which has three events, a fan can apply once per event, so they can theoretically have up to three
waitlist applications).
Each waitlist entry might be processed by an event staff member (if done manually) or determined purely by
randomness (in which case no staff member was involved). If an entry is manually approved, we need to record
which staff member approved it. Once a waitlist entry is accepted, the fan is invited to complete the usual ticket
purchase process. If the fan chooses to finalise their purchase, the waitlist entry will be assigned to the purchase.
Obviously, not all waitlist entries are accepted or have their outcome determined yet; and not all fans might follow
through with their waitlist purchase.
‍ Event staff
For each event, there is at least one staff member involved. Each staff has a staff ID (numeric), first name, and last
name. To prevent staff members from processing their own waitlist entries, all staff members are required to
declare their fan account (if any).
Employers hire staff members and assign them to events. We record the start and end date of employment (might
be different to the event start and end date if the staff member needs to work before or after the event as well),
and a flag indicating if the staff member has done all the training required for their assigned event. We record
employers' name (e.g., "Tim and Colton Touring Worldwide"), email, and a phone number. A staff member can
work for multiple employers throughout their career.
Business Requirements
Your database design needs to be able to meet the business's needs to answer the following questions. You do
NOT need to write SQL queries in your assignment to answer these questions.
1. How many staff members have worked at 2 or more different concerts (e.g., The Weeknd's Melbourne
concert and the "Sounds of Databases in Concert" in NYC)?
2. How many times has the fan named Joe Bloggs been cautioned for violating concert policies?
3. Out of all the events that use a waitlist, which one has the highest rate of ticket purchases compared to
waitlist entries?
4. Which fans with connections to Marc Cheong did not buy any tickets in 2023?
INFO20003 S2 2023 – ASSIGNMENT 1 v1.0 4
5. (To prevent gaming the system) Which staff members processed their own waitlist entries to unfairly
purchase tickets to a show?
6. How many fans who are also staff members are associated with the “Official BTS A.R.M.Y. Fan Club,
Japan”?
7. Which concert(s) did the fans Marc Cheong and Egemen Tanin both attend the year they connected on
the ticket platform?
8. For events with a ‘VIP’ zone, which ones did not successfully sell all the ‘VIP’ tickets?
9. Who was at seat (row A col 2134) during the Rod Laver Arena show (22 May 2023, 10 am) for Rod
Stewart's Anniversary Tour?
10. Which staff members are working at Pseudo Echo's "170 Russell" event in Melbourne (7 August 2023,
10pm) but have not completed all the required training yet?
Instructions
Assignment 1 is worth 10% of your final mark. The assignment will be graded out of 100 marks as described in the
table below:
Component Grading
ER Physical Model with assumptions 80% (8.0 raw final marks)
Conceptual Model in Chen’s Notation 20% (2.0 raw final marks)
.mwb Physical Model File submitted ASSIGNMENT HURDLE
You are to analyse this business case and design a Conceptual ER Model in Chen’s notation (can be hand drawn;
or digital using PowerPoint or Visio1 only) as taught in class and a Physical ER Model for a MySQL Relational
Database in Crow’s foot notation (modelled with MySQL Workbench).
You may list any assumptions you have made about the model. There is a 200-word limit for assumptions.
Assumptions must not be used to simplify the assignment, but only to justify your decision about any ambiguity in
the study.
1 These are the only two supported tools as they are included in your UniMelb Office 365 subscription.
INFO20003 S2 2023 – ASSIGNMENT 1 v1.0 5
Assignment Submission
Please pay special attention to the penalties listed [ ].
You are to submit the assignment under the Assignments tab on Canvas LMS. The submission will require you to
submit two (2) files:
1) A SINGLE PDF document containing:
a) a legible (hand drawn, or otherwise digital) picture of a conceptual model in Chen’s notation.
b) a screenshot/export of your Physical ER Model done in MySQL Workbench. Ensure that tables are fully
expanded so all attributes are readable. Please ensure the image is not blurry.
c) any assumptions you made (limit of 200 words).
Note: All these components must be within a SINGLE PDF document. You can use an online tool like
https://smallpdf.com/merge-pdf to merge multiple PDFs together into a single PDF.
2) A copy of your final .mwb MySQL Workbench file of your Physical ER model.
Note: This model will only be used by staff in circumstances where the screenshot/export of your model in the
PDF is unreadable (i.e., if we have to open this file, a penalty of 10% will occur ), so remember to include
a clear screenshot/export of your physical model in the PDF! If the screenshot/export of your physical model is
unreadable and the .mwb file was not submitted, you will receive zero marks for your physical model .
Remember, if you fail to submit clear and legible models your assignment will be penalised .
Please make sure that you actually submit your files on Canvas . After uploading the files, you need to press
‘Submit Assignment’ to actually submit the files. If you submit late because you failed to press the submit button
and only noticed this after the deadline, your submission will be considered late just like any other late submission
to maintain fairness for all students.
Unless you have an approved extension (see below), you will be penalised -10% of the maximum number of
marks in the assignment per calendar day that your submission is late . For instance, if you received a 78%
raw score, but submitted 2 days late, you'd receive a 58% for the assignment.
Requesting a Submission Deadline Extension
If you need an extension due to a valid reason, you will need to provide evidence to support your request by
5:00pm, 24 August 2023. Medical certificates need to be at least two days in length.
To request an extension:
• Email Timothy Hermanto (timothy.hermanto@unimelb.edu.au) from your university email address,
supplying your student ID, how many days you’d like to extend, and evidence that can support the
number of days you are requesting. Please include in the subject [INFO20003 Assignment 1 Extension].
• If your submission deadline extension is granted you will receive an email reply granting the new
submission date. Do not lose this email!
INFO20003 S2 2023 – ASSIGNMENT 1 v1.0 6
Reminder: INFO20003 Hurdle Requirements
To pass INFO20003, you must pass two hurdles:
• Hurdle 1: Obtain at least 50% (15/30) or higher for the three assignments (each worth 10%)
• Hurdle 2: Obtain at least 50% (35/70) or higher for the combination of quizzes and end of semester exam
It is our recommendation to students that you attempt every assignment and every question in the exam.
GOOD LUCK!