程序代写代做代考 case study database ER SQL Page 1 of 4

Page 1 of 4

INFO20003 Semester 2, 2018

Assignment 1 – ER Modelling

Due: 5.59pm Friday 24th of August 2018

Submission: Via LMS https://lms.unimelb.edu.au

Case Study: Louis Vuitton Moet Hennessy (LVMH)

LVMH (Louis Vuitton Moet Hennessy) is a French multinational luxury goods conglomerate

based in Paris. LVMH sells a wide range of products, including clothing, cosmetics, jewellery,

wine and even luxury yachts, under a range of different brands (for example, Givenchy, Dior,

Bulgari and Dom Pérignon). For each brand there is a logo, website, and a brief slogan or

description of the brand (for example, “ethically-minded haute couture for men and women”).

Within each brand there are potentially thousands of different products available for retailers

to sell to consumers.

The LVMH conglomerate is interested in keeping track of everything it sells, specifically, each

individual product item’s whereabouts, pricing, sale, and other details. You have been asked

to design a global shipping and inventory database that tracks every LVMH product item from

its place of manufacture (e.g. a Tag Heuer watch manufactured in Switzerland) to its place of

retail sale (e.g. The Hour Glass, Collins Street, Melbourne, Australia or Daimaru Department

Store, downtown Tokyo, Japan).

Transit

The final destination for every product item is a retail store. As they make their way there,

product items are transported from place to place as part of consignments. The origin city and

destination city of each consignment are recorded, as well as when the consignment leaves

its origin and when it arrives at its destination, and the mode of transport that was used (ship,

plane, road). Each consignment has a unique tracking code assigned by LVMH.

A product item may be part of multiple consignments over time as it makes its way to the retail

store. For example, a pair of Dior ballet pumps might be packed into a container with many

other Dior goods and shipped from Dunkirk, France to Dover, UK (the ballet pumps’ first

consignment). From there it might be placed on a pallet and trucked to a warehouse in

Manchester (its second consignment), and then be taken to a retail store in a van (its third

consignment). The name and address of each retail store is recorded.

At any time, an employee at LVMH should be able to query the database to identify where

every unsold product item is. They also need to be able to contact the person in charge of the

consignment (for example, a representative of the shipping company). This person may have

several phone numbers. The name of the country the person is based in also needs to be

recorded.

Page 2 of 4

Pricing

For each product LVMH needs to store its wholesale price in Euros (€). The company also

needs to be able to calculate a product item’s local recommended retail price in the local

currency of the retail store it is sold from (e.g. Japanese Yen ¥, Australian Dollar A$ or British

Pound £). The wholesale Euro € price of a product is fixed and does not change. To calculate

the local retail price of a product, LVMH keeps track of the exchange rates of all international

currencies to the Euro four times a year (on 1 January for the first quarter, 1 April for the

second quarter, 1 July for the third quarter and 1 October for the last quarter). Once the

exchange rate is set, that exchange rate stays fixed until the next exchange rate setting date.

If the exchange rate changes at the beginning of a quarter, the local retail price of a product

will change as well to follow the change in currency.

Tracking

LVMH needs to track whether product items were damaged prior to sale.

Every product item that is above €10,000 wholesale price is tracked with an individual RFID

tag that stays with the item until it is sold. RFID tags are then re-used for other product items

until the tag ceases to work or is lost. Throughout time, an RFID tag can be in three states:

working, broken or lost. LVMH records the RFID number using eight hexadecimal digits (0 to

F), as well as the dates the RFID tag was attached to a product item, and removed from it. If

an RFID tag is broken or lost, the date of removal might not be captured. LVMH wishes to

know which product items exceeding €10,000 in wholesale value a particular RFID tag was

attached to at any point in the last two years.

Your database design needs to be able to solve the following business requirements:

• What was the exchange rate with the Chinese Yuan on 1 January 2016?

• What is the total retail price of damaged product items shipped to a particular retail

store?

• How many product items flew by plane as part of their journey to their retail store?

• How many RFID-tagged product items of a particular brand were damaged?

• Which product item (if any) was RFID tag 58FE7A73 attached to on 28 March 2018?

• What was the local retail price in Chinese Yuan for 100 mL Miss Dior (a perfume) on

15 February 2016?

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.

Page 3 of 4

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

Assignment Submission:

You are to submit the assignment under the Assessment tab on LMS. Each section of the

LMS Submission is clearly labelled and colour coded in LMS for your convenience:

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 BLACK submission link.

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

assignment.

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

under the BLUE submission link.

Page 4 of 4

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 Thursday 23rd of August 5pm. Medical certificates need to be at

least 2 days in length.

To request an extension:

1. Email the Subject Coordinator (deccles@unimelb.edu.au) with your student id, your

name and your university email with the extension request and supporting evidence.

2. 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 individual assignments

(each worth 10%)

• Hurdle 2: Obtain a grade of 50% (35/70) or higher for the MST Mid Semester Test (10)

and the End of Semester Exam (60)

Therefore, it is our recommendation to students that you attempt every assignment and

every question in the test and exam.

GOOD LUCK!

mailto:deccles@unimelb.edu.au