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 alive 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.

For each concert series, the system records its details, that are: name (e.g., Map of the Soul Tour), asingle 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 awaitlist.

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: azone 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.

For each fan, the system records their details such as first name, lastname, 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 …

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 beat 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.

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 awaitlist 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.

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).

Employershire staff members and assign them to events. We record the start and enddate of employment (might be different to the event start and enddate 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.

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 awaitlist, 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?

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?

Assignment 1 is worth 10% of your final mark. The assignment will be graded out of 100 marks as described in the table below:

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’sfoot 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.

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-pdfto 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    aclear 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.

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 beat least two days in length.

To request an extension:

•      Email Timothy Hermanto ([email protected]) 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!