Reference no: EM132677086
FZA2094 Databases - Monash University
Assignment - SQL - Monash Art Union
Assignment Tasks
Using the supplied schema file (mau_ass2_schm_insert.sql) create the tables for the Monash Art Union System and insert the supplied values. This provides you with a starting point for the following tasks.
TASK 1: Data Manipulation:
(a) Load selected tables with your own additional test data using the supplied
Q1a-mau-insert.sql script file, and SQL commands which will insert, as a minimum, the following sample data -
• 15 ARTWORKs
• 10 AW_DISPLAYs
• 4 SALEs, and
• the required AW_STATUS entries to support the ARTWORK, AW_DISPLAY and SALE data you add.
Please note, these are the minimum number of entries you must insert; you are encouraged to insert more to provide a richer data set to draw from.
For this task only, data that you add in the database should follow the rules mentioned below:
1. You may treat all of the data that you add as a single transaction since you are setting up the initial test state for the database.
2. The primary key values for this data should be hardcoded values (i.e., NOT
make use of sequences) and must consist of values below 100.
3. Dates used must be chosen between the 1st January 2019 and 31th July 2020.
4. Artworks that you add must be from at least 10 different artists.
5. AW_Displays that you add must have at least 3 different start dates and must involve both the years permitted, i.e., 2019 and 2020.
6. At least 2 different galleries must have AW_Displays displayed in them.
For this task ONLY, you can look up and include values for the loaded tables/data directly where required. However, if you wish, you can still use SQL to get any non-key values.
You are reminded again that in carrying out this task you must not modify any data or add any further data to the tables which were previously populated by the supplied schema file.
For all subsequent questions (Q1b onwards) you are NOT permitted to manually:
• lookup a value in the database, obtain its primary key or the highest/lowest value in a column, or
• calculate values external to the database, e.g., on a calculator and then use such values in your answers. Any necessary calculations must be carried out as part of your SQL code.
You must ONLY use the data as provided in the text of the questions. Where a particular case (upper case, lower case, etc.) for a word is provided you must only use that case. You may divide names such as Zora Mandrey into the first name of Zora and a last name of Mandrey if required. Failure to adhere to this requirement will result in a mark of 0 for the relevant question.
(b) For the following tasks, your SQL must correctly manage transactions and use sequences to generate new primary keys for numeric primary key values
(under no circumstances may a new primary key value be hardcoded as a number or value). Your answers for these tasks must be placed in the supplied SQL Script
Q1b-mau-dm.sql
You may use the following artwork statuses with an artwork as and when required:
• W - in MAU storage at the MAU central warehouse
• T - in transit (being shipped to/from a gallery), include to/from which gallery id
• G - located at the gallery, include gallery id
• S - sold, or
• R - returned to the artist
You are reminded again that queries that use subqueries and SQL conditions
unnecessarily to get required data will be penalised.
(i) Create sequences which will allow entry of data into the AW_STATUS, AW_DISPLAY and SALE tables - the sequences must begin at 300 and go up in steps of 1 (i.e., the first value is 300, the next 301, etc.)
(ii) Suppose it is now 10 AM on 22nd October 2020 and an artwork called "Saint
Catherine of Siena'' has just been received by the MAU central warehouse from the artist with artist code 17. The minimum payment this artist is prepared to accept for this artwork is $500,000. Take the necessary steps in the database to record the
required entries for this new arrival.
(iii) Using the timing as indicated, take the necessary steps in the database to record the following activities. You may assume that no other artworks have been added by this artist (artist code 17).
a) On the same day at 11 AM, this new arrival is sent from the MAU warehouse to Karma Art gallery (Ph:0413432569).
b) It is received by the gallery 3 hours and 15 minutes after leaving the MAU warehouse. MAU is immediately informed of the safe arrival of this extremely precious artwork through a telephone call.
c) On the next day the gallery places the artwork on display for a total of 10 days.
(iv) It is now 2:30 PM on the 5th day since this artwork has been displayed. No other artwork has been put on display after this artwork was displayed in any gallery (ie. it is the most recent display). It is now sold to a customer (customer id 1) for $850,000. Take the necessary steps in the database to record the required activities.
TASK 2: SQL Queries:
Your answers for these tasks must be placed in the supplied SQL Script Q2-mau-queries.sql
ANSI joins must be used where two or more tables are to be joined, under no circumstances can "implicit join notation" be used - see the week 7 workshop slide 22 and tutorial
Where a question indicates "Your output must have the form shown below" - this means the same appearance and alignment of columns/data as the sample output shows. Clearly your actual data may be different.
(i) List the artist code, artist name (as a single attribute) and full name of the state for all the artists who live in either VIC, NSW or WA. You should only show those artists who either do not have a given name, a family name or a phone number.
The output must be displayed in ascending order of Artist Name, where two artists have the same name, order the output by artist code.
The column headings in your output should be renamed as Artist Code, Artist Name and Artist State. Your output must have the form shown below. Your actual data may clearly be different from the data shown below.
(ii) List the artist code, artist full name, artwork number, artwork title, artist minimum payment, date submitted and the number of days the artwork was held by MAU for all artworks that have been returned to the artists within 120 days of the work being submitted and were never sent to any gallery for display. Please note that the date the artwork is returned should not be included in the calculating Number of Days with MAU.
The output must be displayed by artist code in ascending format. For those artists with more than one artwork in the output, the artwork held for the longest period of time by MAU should be displayed first.
The column headings in your output should be renamed as Artist Code, Artist Name, Artwork No., Artwork Title, Artwork Min. Payment and Number of Days the artwork with MAU.
Your output must have the form shown below (only some rows shown). Your actual data may clearly be different from the data shown below.
(iii) List the artist code, artwork no, artwork title, gallery id, gallery name, display start date and number of days the artwork was on display for all MAU artworks that have completed their display and were on display in the gallery for less than 13 days.
The output must be ordered by artwork - for any given artwork the shortest period of display should be shown first. Where an artwork has been displayed in different galleries for the same period this should be ordered by gallery id and display start date.
The column headings in your output should be renamed as Artist Code, Artwork No.,
Artwork Title, Gallery ID, Gallery Name, Display Start Date and Number of Days in Gallery.
Your output must have the form shown below (only some rows shown). Your actual data may clearly be different from the data shown below.
(iv) List the artist code, artwork number, artwork title and number of movements for those artworks which have moved to/from any gallery, i.e., in transit less than the average number of movements to/from any gallery by an artwork.
The output must be displayed in ascending format of number of movements. For those artworks with the same number of movements, display them by the artist code and artwork number in ascending format.
The column headings in your output should be renamed as Artist Code, Artwork No., Artwork Title and Number of Movements.
Your output must have the form shown below (only some rows shown). Your actual data may clearly be different from the data shown below.
(v) MAU would like the ability to quickly find out an estimated minimum selling price for all the unsold artworks for the five galleries (gallery ids 1 to 5) they currently deal with so they can prioritise their return in the case of competing gallery display requests.
For this display, list the artist code, artwork title and for each gallery, the minimum selling price for the artwork title for the artist that MAU should be prepared to sell the artwork for, taking into account, the payment to the gallery as commission, payment to MAU as
commission and the minimum payment to the artist requirements for all the unsold artworks. The estimated minimum selling price should be rounded up to the nearest dollar.
As an example, an artwork has been provided by an artist with an indicated minimum payment to the artist of $1400. This artwork is to be sold by a gallery with a 10% commission. An estimate of the minimum selling price must include the minimum payment to the artist, the gallery commission and the MAU commission (20%). Here since 30% is commissions, the $1400 must represent 70% of the sold price, so the estimated minimum selling price would need to be $2000 (artist payment $1400, gallery commission $200 and MAU commission $400).
The output must be displayed in ascending format by artist code and where there is more than one artwork for an artist, display the result by artwork title in ascending format.
The column headings in your output should be renamed as Artist Code, Artwork Title and Min. Sale Price Est. (Gallery 1), Min. Sale Price Est. (Gallery 2), Min. Sale Price Est.
(Gallery 3), Min. Sale Price Est. (Gallery 4) and Min. Sale Price Est. (Gallery 5).
Your output must have the form shown below (only some rows shown). Your actual data may clearly be different from the data shown below.
(vi) For each artwork sold, list the artist code, artist full name, artwork title, the gallery id for the gallery in which the work was sold, the sale price and the percentage that the artwork sold above the estimated minimum selling price. The estimated minimum selling price
includes the commission payment to the gallery where it was sold, the commission payment to MAU and the minimum payment to the artist - see example part (v).
As an example, assume an artwork which had an estimated minimum selling price of $2000 sold for $2500 - this would represent a sale at 25% above the minimum selling price.
The last row of the output should show the average (in the sixth column) of the percentage sold above the minimum selling price for all the sales.
The column headings in your output should be renamed as Artist Code, Artist Full Name, Artist Title, Gallery ID, Sale Price and % Sold Above Min. Sell Price.
Your output must have the form shown below (some rows have been blurred). Your actual data will clearly be different from the data shown below.
TASK 3: Design Modifications:
Your answers for these tasks must be placed in the supplied SQL Script Q3-mau-mods.sql
These tasks should be attempted only after task 1 and task 2 have been successfully completed. They are to be completed on the "live" database ie. the database with the data loaded from your previous work.
In completing this task, you must:
• if you need to add new columns, tables or related constraints, follow the naming conventions used in the data models and schema file which have been provided,
• provide column comments for any new columns that you add, and
• correctly manage any transactions used as part of your solution
(i) MAU would like to be able to easily determine the total number of times each customer has bought an artwork. Add a new attribute which will record this requirement.
Based on the data which is currently stored in the system, this attribute must be initialised to the correct current number of times each customer has bought an artwork.
(ii) For each artwork that has been sold, MAU would like to record i) the commission in dollars that should be paid to them ii) the commission in dollars that should be paid to the gallery and iii) the actual payment in dollars that should be made to the artist.
Based on the data which is currently stored in the system, change the database to meet this requirement such that these individual amounts are now also readily available in the database for all sales which have taken place. You may assume that the current percentage commissions were the same when the artwork was sold.
(iii) MAU have observed that Galleries often display several items from the MAU stock with the same starting date and for the same period. They in effect create a Gallery Exhibition themed around a particular artist, media or other theme. To better record, and publicise, these exhibitions MAU would like to record a numeric exhibition code as an identifier, the name for the exhibition, the theme of the exhibition ( A: artist, M: media, or O: other), the details of the artworks which are displayed and the total number of artworks which make up the exhibition. An exhibition must contain at least one artwork.
Based on the data that is currently stored in the system, change the database to meet this requirement. Add an exhibition themed around a particular artist for one of your galleries using the artworks you have on display from Q1 (a). You may hardcode the gallery id, artist code and display start and end dates.
Attachment:- Databases.rar