Assignment 1: ER Modelling Solution

Case Study: The Louvre Museum

The Louvre Museum is one of the biggest tourist attractions in the world. It attracts approximately 10 million visitors a year. The museum is a former Royal Palace and home to nearly one million art objects including works by DaVinci, David, Rubens, Bernini, Claude and Caravaggio. It is home to the most famous painting in the world ‘Mona Lisa’ (although it is known as La Giaconda).

Your team has been asked to design a MySQL transactional database to collect information about how visitors buy their tickets, and how they enter and leave the museum and the museum’s gallery wings. The following specifications have been provided to your team to assist you in your design. The Louvre is open every day of the year.

Buying Tickets


Visitors to the Louvre can buy tickets online in advance or on arrival at the Louvre museum. To purchase tickets online, visitors must pay €15 per person. The price applies to all visitors regardless of age. Online payment can be made by credit card, debit card or digital wallet (Apple Pay, Google Pay). Visitors can buy as many tickets as they need. For purchases made using cards or digital wallets, the Louvre museum wants to record the name of the financial institution (‘The Australian and New Zealand Banking Corporation’), its abbreviated name (‘ANZ Bank’), the country the card was issued in (‘Australia’), and the bank branch that issued the card (‘Woolloomooloo’). We need to store the account name (‘David Eccles’) the card number (‘0198...8822’), the expiry month and year (01, 2024). As per international financial regulations we must not store the CCV code nor store the full card number.

Visitors who wish to buy their tickets on arrival at the Louvre museum must queue and pay €17 at one of the five entrances to the Louvre museum. Visitors can pay using a credit card, debit card, digital wallet, or in cash. Cash payments must be made in euros. If a visitor pays by card, or digital wallet we store the same information as an online purchase. If they pay by

1
cash, we store no information except their first name, city and country they came from. Please note that a given payment can only be made using one of the payment methods. The business case is not concerned with bulk discounts for purchasing large ticket quantities, nor with ticket refunds.

Entering the Louvre Museum

Visitors can enter the museum by one of five entrances. The five entrances are: Pyramid, Carrousel de Louvre, 99 rue de Rivoli, Passage Richelieu and Portes de Lion. Visitors who have pre-purchased tickets can skip the ticket queue and go straight to one of these entrances. On presentation of a valid ticket, the ticket is scanned, and the date and time of scanning is recorded. To facilitate scanning, each ticket is associated with a unique Barcode. We also ask how the visitor came to the Louvre today, the valid options are ‘Walk’, ’Metro’, ’Train’, ’Bus’, ’Taxi’, ‘Private Vehicle’, ’Other’. All visitors must answer this question before they can be admitted to the Museum. The information about mode of transportation helps the museum to improve various facilities outside each entrance such as parking space, taxi stands and pedestrian access. The mode of transportation is asked for only at first entry, i.e. if a visitor is using the valid ticket to re-enter the museum the same day, the mode of transportation is not recorded again. The Louvre museum must be able to determine how many visitors have entered the museum and how many visitors have entered the museum through each of the five entrances.

To see the art objects on display, visitors must present their ticket a second time at the entrance of the 3 main wings of the museum. The wings are known as Richelieu, Denon and Sully. We record the time the ticket was scanned to enter each of the wings, a valid ticket can enter the wing as many times as they like for the day of admission. Each ticket is valid for the whole day i.e. starting from the time the ticket was first scanned until the museum closes the same day.

Audio Guides

Once they pass through the wing entrance, each visitor has a chance to hire an audio guide. The audio guide is available in 13 languages (French, English, Italian, Russian, Chinese Mandarin, Spanish, Portuguese, Korean, Dutch, Polish, Swedish, Norwegian and Finnish). The cost of the audio guide is €8. A visitor can pay by cash, credit card, debit card or digital wallet. If they pay by credit card, debit card or digital wallet we store the same information as described in “Buying Tickets”. The visitor needs to present their ticket for scanning to hire the audio guide. For each audio guide hired, we need to know the language chosen and which wing issued the audio guide and which wing received the audio guide at the end of the visit.


2
We need to know how long each audio guide was used by each visitor. This gives the Louvre Museum an idea of how long visitors spend in the Museum. Each audio guide is identified by a unique 16-digit alphanumeric serial number (e.g. D117E351C9900T47) and we record when it was activated for the first time to be brought into service and when was it retired from use. Visitors have the option of going to the Android Play Store, or Apple App store, and downloading the Official Louvre Museum app which costs €4. As app store purchases are associated with a debit card or credit card, the same information is stored as described in “Buying Tickets”.

Specialist Exhibitions

As part of the admission price covered by the ticket, visitors can also visit specialist exhibitions in the Hall Napoleon. For each specialist exhibition we record the name of the exhibition, the exhibition opening and closing dates. Currently there is an exhibition to commemorate the 500th anniversary of the death of Leonardo Da Vinci. However, because of the popularity of specialist exhibitions visitors have to book online a timed entrance to the exhibit. Timed entrances are in 15-minute slots and allow no more than 45 people per fifteen-minute slot. Please note the number of visitors admitted per 15-minute slot will vary depending on the type of exhibition and its popularity. The tickets are scanned at the entrance of Hall Napoleon. At any time, we must know how many people have been admitted to the specialist exhibition, how many spaces have been booked, and how many spaces remain for each fifteen-minute slot.

Your database design needs to be able to meet the business' need to answer questions as:

    • How many tickets were purchased using each payment method in the current financial year?

    • In last six months, which main entrance has recorded the highest number of pedestrian visitors?

    • What percentage of the daily visitors to the museum hire an audio guide?

    • How long was each audio guide in use by visitors for a given day?

    • What Is the most adopted common order by in which patrons to visit the wings? (e.g. Denon à Sully, DenonàRichelieuàSully etc.)

    • How many visitors managed to visit all three wings?

    • What are the 10 most attended specialist exhibitions in last five years?

    • What are the most popular 15-minute slots (with respect to attendance) for specialist exhibitions?

    • For large group bookings (i.e. where 30+ tickets are bought under a single online order) museum management wants to know how many tickets remain unused.
    • During the winter months, which weekday morning (i.e. excluding Sat and Sun) is busiest?




3
NOTE: These requirements are listed for your guidance only. You do not need to submit any SQL queries or explanations of how these requirements are met.

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:

ER Physical Model with assumptions
80%
Conceptual Model in Chen’s Notation
20%
.mwb Physical Model File
Assignment Hurdle




    • You are to analyse this Business Case and design a Conceptual ER Model in Chen’s notation (can be hand drawn) 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 on a separate page. There is a 400-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.

    • As part of your submission you must submit your final MySQL Workbench

.mwb file of your Physical ER Model.

NOTE: Items 1 (Conceptual Model & Physical ER model) and 2 (Assumptions) must be submitted as a single PDF. The title of the PDF document must be your student id (e.g. 987654.pdf). Similarly, the title of your .mwb file should be your studentid.mwb (e.g. 987654.mwb).

Assignment Submission:

You are to submit the assignment under the Assignments tab on Canvas LMS.

Assignment 1 has two submission links for the following files:

    1. ONE PDF document named as your student id (e.g. 987654.pdf) containing a legible (hand drawn) conceptual model in Chen’s notation, a legible picture of your Physical ER Model and your assumptions (if you stated any). Submit this single PDF document under the “Assignment 1 PDF file” link.

4
NOTE: If you fail to submit legible models you will be penalised 10% of the mark for this assignment.

    2. Your final.mwb MySQL Workbench file of the Physical ER model. Submit your

.mwb file under the “Assignment 1 mwb file” link.

Requesting a Submission Deadline Extension

If you need an extension due to a valid (medical) reason, you will need to provide evidence to support your request by 5pm, 2nd of April. Medical certificates need to be at least two days in length.

To request an extension:

    • Email Oscar Correa (oscar.correa@unimelb.edu.au) from your university email address, supplying your student ID, the extension request and supporting evidence.

    • If your submission deadline extension is granted you will receive an email reply granting the new submission date. Do not lose this email!

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 a grade of 50% (35/70) or higher for the End of Semester Exam

Therefore, it is our recommendation to students that you attempt every assignment and every question in the exam.

GOOD LUCK!



















5