INFO20003 Semester 1, 2019
Assignment 1 – ER Modelling
Due: 6:00pm Friday 5 : Via LMS https://lms.unimelb.edu.au
Case Study: Bridgman Art Library
Copyright By PowCoder代写 加微信 powcoder
Bridgman Art Library manages copyright of artworks on behalf of individuals, art galleries, museums, libraries and universities (“owners”). Customer organisations that wish to use a copyrighted image created by an artist (“maker”) can pay a fee to Bridgman Art Library, who will then provide a high- quality image for reproduction.
Uses of Artworks
Once a customer is registered in the Bridgman database, they can browse through information about the available artworks. When the customer finds an artwork they wish to use, they must register a proposal for the use of the artwork. The proposal contains information about the intended use (e.g. for a TV documentary, book cover, calendar, T-shirt, tote bag, theatre set dressing, etc.), intended market (Commercial, Research, or Non-Profit) and intended duration of use. The duration of use could range from a week to five years. The customer also nominates the price (in US dollars) they are willing to pay for their proposed use. If a customer wants exclusive use of an artwork, this must be recorded in the database. The date the proposal was made is also stored.
Each intended category of use is recorded once in the system and designated with a unique five-digit numeric code (e.g. 10001 “Life-size Reproduction”; 10010 “TV Documentary”; 10037 “T-shirt”; 12831 “Tea Towel”; 60001 “Book Cover”). A customer can propose multiple intended categories of use for the same artwork (e.g. 12831 “Tea Towel” and 19833 “Ceramic Mug”) as part of a single proposal. If the intended category of use has not been requested previously, it must be added to the database. This ensures that the categories are common for all art copyright managed by Bridgman.
From Proposal to Contract
Once the customer’s proposal has been registered in the system, a Bridgman Art Library account manager prepares a contract with a negotiated “contract price”, and the status of the contract being “offered”. For each contract, the date the account manager created it is captured in the database. The contract price may be the same as, or higher than, the price that the customer offered in their proposal.
If the contract is agreed upon by both the customer and the artwork’s owner, this is recorded in the system with the contract status being “approved”. The date of approval is captured, and the customer receives the invoice, where each invoice is uniquely identified by the proposal number and the contract iteration number.
When an agreement has not been reached, the contract’s status becomes “cancelled”, and a brand- new contract is prepared with a different contract price. For cancelled contracts, the date of approval will not be captured.
INFO20003 A1 S1 2019 1
The process of issuing new contracts for the same proposal is repeated as many times as it takes to reach an agreement – sometimes it continues for so long that different contracts for the same proposal may be prepared by different account managers. For legal and accounting purposes, it is critical that Bridgman keeps track of all contracts, including those that were never approved.
Account managers are paid a base salary, and they may also be paid bonuses when they negotiate an approved contract which significantly exceeds the proposal price. When an account manager negotiates an approved contract price that is more than US$2,000 above the proposal price, they receive a bonus of 2% of their base salary for each such contract. Each account manager’s first and last name, city and country must also be recorded.
Owners and their Representatives
Bridgman Art Library needs to be able to identify who has owned the artwork at any point in time since the artwork’s creation. The owner may be the artist, an art dealer, auction house (e.g. Sotheby or Christies), art gallery, museum, etc. Each artwork may have been owned by many owners over time but can only have one owner at any particular moment.
About each owner we need to know the organisation name (if known), address, and country, and the details of their representative who deals with the owner’s negotiations. For each owner representative we need to store their first and last name, email address, and phone number including international dialling code (e.g. +44 7388 2120) of the representative. Even if the owner has had many representatives in the past, we need to capture the details of only the most recent such representative. To avoid conflicts of interest, Bridgman allows each representative to deal on behalf of a single owner only. Even after a representative ceases to work for an owner, their details remain stored in the database.
Bridgman Art Library refers to the creator of the artwork as a maker. A maker can be an individual, a company (e.g. Tiffany), or an atelier (e.g. Wedgwood). Each maker can produce many artworks throughout their lifetime. The database needs to be able to store the maker’s first and last name (e.g. Paolo Veronese), nationality (e.g. Italian), birth year and death year, and the atelier or company name (if known). However, not all makers will have all this information – some of this information might be simply unknown. Bridgman records a single maker for each artwork; however, there might be artworks without a known maker.
For each artwork, we store a unique object ID, the type of artwork, the title (e.g. “The Wedding Feast at Cana”), the artwork’s maker, the artwork’s dimensions in centimetres – height, width, and, if appropriate, depth (e.g. H 677 × W 994 cm), a description of the artistic medium (e.g. “oil on canvas”), origin (e.g. “Venice, Italy”), the year the artwork was finished (e.g. 1563), and importantly whether it is available for copyright reproduction (“Yes”, “No”, or “By Negotiation”).
Customers can be individuals or companies. We need to know contact information for each customer – first and last name, company name (if relevant), postal address, city, country, email address, and telephone numbers. At any given time, multiple customers may have copyright permission for a particular artwork.
INFO20003 A1 S1 2019 2
Your database design needs to be able to solve the following business requirements:
What is the approved contract with the highest price?
How many proposals for sculptures were made in 2018 by customers from Ireland?
Who owned ’s painting Mount Hermannsburg on 15 February 2016?
How many contracts created in 2017 were cancelled?
Who is the account manager with the highest number of approved contracts?
How much bonus has been earned by account managers for a particular artwork?
What is the most common intended use of artworks?
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.
Examples of artworks managed by Bridgman Art Library
Title: Coming South (1886)
Type: Painting
Maker: (1856–1931, English/Australian) Dimensions: H 63.5 × W 52.2 cm
Medium: Oil on canvas
Origin: Australia
Title: Napoleonic Egg (1912)
Type: Jewellery
Maker: House of Fabergé (Russian company) Dimensions: H 11.7 × W 8.9 × D 9.1 cm
Medium: Gold, ruby, diamond, and other materials Origin: Russia
INFO20003 A1 S1 2019 3
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 Conceptual Model in Chen’s Notation .mwb Physical Model File
20% 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 (e.g. 987654.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 the 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.
INFO20003 A1 S1 2019 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 5pm, Thursday 4 April. Medical certificates need to be at least two days in length.
To request an extension:
1. Email the head tutor, from your university email
address, supplying your student ID, 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
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!
INFO20003 A1 S1 2019 5
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com