FIT2094 Databases
2020 Semester 2
Assignment 2 – SQL – Monash Art Union (MAU) Assignment weighting 15% – Lecturer in Charge: Minh Le
Monash Art Union (MAU) is a company which accepts artworks from artists and offers these artworks to galleries around the country for display and potential sale. A registered MAU customer may, while the artwork is being displayed, decide to purchase the item. MAU charge twenty percent of the price at which the artwork is sold as their commission (note this is not recorded as part of the model). The gallery is paid a standard percentage of the sale price as its commission, this percentage is negotiated per gallery and so may vary from gallery to gallery (this gallery percentage is recorded as part of the model). The remainder of the sale price goes to the artist.
MAU assign an artist code to each artist which the company represents. MAU record the artist’s name, their contact address and telephone number (not all artists supply a telephone number). When an artist has completed an artwork which they wish to sell through MAU, they contact the company and offer the work to be sold by MAU. MAU maintain for each artist the number of works which MAU currently hold in stock (ie. available for display/sale) for that artist.
All artworks accepted by MAU are assigned an artwork number specific to a particular artist. For example artist 1234 will have artworks 1, 2, 3 etc and artist 4567 will also have artworks 1, 2, 3 etc. The title of the artwork, the date the work was accepted into the MAU system and the minimum payment which the artist is prepared to accept for their work is recorded. The work may be sold for any price above this minimum payment such that the artist receives at least this minimum amount and the gallery and MAU commission requirements are satisfied.
A gallery is identified by a gallery id. MAU record the name of the gallery, the gallery manager’s name, the address of the gallery and the contact phone number for the gallery (all galleries are required to provide a unique contact number).
Art collectors who are interested in purchasing an artwork must register with MAU as a customer before they are able to make any purchase. Each customer is identified by their customer id. The customer’s name, address and contact phone number are recorded. If the customer is a business customer their business name is also recorded.
Page 1 of 14
A gallery considers the artworks which MAU has on offer and then requests one or more artworks and displays the artwork(s) in its gallery with the intention of generating a sale. The date at which the display starts is recorded. If the item generates little interest then the gallery will return the artwork to MAU. At a later date, after it is back at MAU, the gallery might request and display the same artwork again.
When an artwork from MAU stock is sold, the sale is assigned a unique sale id. The artwork, date of the sale, sale price and customer who purchased the item is recorded. MAU is interested in identifying which display of the artwork generated the sale.
To allow tracking the status of an artwork, MAU would like to have recorded the status of each artwork at the current point in time and its full status history. MAU would like to record five possible values for this status:
● 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 a gallery, include gallery id
● S – sold, or
● R – returned to the artist
These five possible values are fixed and will not need to be extended. Based on these requirements a logical model has been created for MAU:
Page 2 of 14
From this logical model the following relational model has been created:
For this assignment, you will populate these tables with appropriate test data and write the SQL queries specified below. You must ensure that any activities you need to carry out in the database to complete the assignment conform to the requirements of the provided data model.
The schema/insert file for creating this model is available in the archive ass2-student.zip – this file creates the Monash Art Union tables and populates several of the tables (those shown in purple on the supplied model) – you should read this schema carefully and be sure you understand the various data requirements. You must not alter the schema file in any manner, it must be used as supplied. This schema file contains a single commit after the inserts have completed since this is setting up an initial state of the database for you to work with, you should not use this as your method of approach.
Page 3 of 14
IMPORTANT points for you to observe, when completing this assignment, are:
1. The ass2-student.zip archive also contains four SQL scripts for you to code your answers in, you should ensure these files are regularly pushed to GitLab server so a clear development history is available for the marker to verify (a minimum of four pushes are required). In each file, you must fill in the header details with your name and student ID before beginning any work. Your script files must not include any SPOOL or ECHO commands. Although you might include such commands when testing your work they must be removed before submission (a 5 mark grade penalty will be applied if your documents contain spool or echo commands)
2. You are free to make assumptions if needed. However, your assumptions must align with the details here and in the assignment 2 forum and must be clearly documented (see the required submission files).
REMEMBER you must keep up to date with the Ed assignment 2 forum where further clarifications may be posted (this forum is to be treated as your client). Please be careful to ensure you do not post anything which includes your reasoning, logic or any part of your work to this assignment forum as doing so violates Monash plagiarism/collusion rules.
3. Queries that use subqueries and SQL conditions unnecessarily to get required data will be penalised. Views must not be used in arriving at any solutions for the tasks you are required to complete as part of this assessment.
4. In handling dates, the default date format must not be assumed; you must make use of the TO_DATE and TO_CHAR functions where appropriate. Failure to do so will incur a 50% grade penalty for questions involving dates.
5. PL/SQL (ie, user created functions, procedures, triggers, packages or PL/SQL structures) must not be used for any part of the solutions to these assignment tasks. Any question which makes use of such structures will not be graded.
6. In completing the following tasks, you must design your test data so that you always get output for the SQL scripts/queries specified below – this may require you to add further data as you move through completing the required tasks. Queries that are correct but do not produce any output (“no rows selected” message) using your test data will lose 50% of the marks allocated, so you should carefully check your test data and ensure it thoroughly validates your SQL queries.
You may need to rerun the schema, especially when you have been experimenting with your solutions and may have corrupted the database unintentionally. If you suspect that there might be such problems, simply rerun the schema. The schema includes the appropriate drop commands at the head of the file.
Page 4 of 14
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 pointfor the following tasks.
TASK 1: Data Manipulation (20 marks):
(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.
[10 marks]
Page 5 of 14
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.)
[1 mark]
(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.
[3 marks]
Page 6 of 14
(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.
[4 marks]
(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 (60 marks):
Your answers for these tasks must be placed in the supplied SQL Script
Q2-mau-queries.sql
[2 marks]
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.
Page 7 of 14
[4 marks]
(ii) List the artist code, artist full name, artwork number, artwork title, artist minimum payment 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 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.
[6 marks]
(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.
Page 8 of 14
Your output must have the form shown below (only some rows shown). Your actual data may clearly be different from the data shown below.
[10 marks]
(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.
[10 marks] (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.
Page 9 of 14
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.
[14 marks]
(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, Artwork 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.
Page 10 of 14
TASK 3: Design Modifications (20 marks):
Your answers for these tasks must be placed in the supplied SQL Script
Q3-mau-mods.sql
[16 marks]
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.
[2 marks]
(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.
[6 marks] Page 11 of 14
(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.
[12 marks]
Page 12 of 14
SUBMISSION REQUIREMENTS
Due Date: Friday 6th November 2020 at 5 PM (Week 12)
Please note, if you need to resubmit, you cannot d epend on your tutors’ availability, for this reason, please be VERY CAREFUL with your submission. It is strongly recommended that you submit several hours before this time to avoid such issues.
For this assignment there are four files you are required to submit:
● Q1a-mau-insert.sql ● Q1b-mau-dm.sql
● Q2-mau-queries.sql ● Q3-mau-mods.sql
If you need to make any comments to your marker/tutor please place them at the head of each of your solution scripts in the “Comments for your marker:” section.
Do not zip these files into one zip archive, submit four independent SQL scripts. The individual files must also have been pushed to the FIT GitLab server with an appropriate history as you developed your solutions.
Late submission will incur penalties at the rate of -5 mark for every 12 hours the submission is late.
Please note we cannot mark any work on the GitLab Server, you need to ensure that you submit correctly via Moodle since it is only in this process that you complete the required student declaration without which work cannot be assessed.
It is your responsibility to ENSURE that the files you submit are the correct files – we strongly recommend after uploading a submission, and prior to actually submitting, that you download the submission and double-check its contents.
Your assignment MUST show a status of “Submitted for grading” before it will be marked.
If your submission shows a status of “Draft (not submitted)” it will not be assessed and will incur late penalties after the due date/time.
Please carefully r ead the documentation under the “Assignment Submission” on the Moodle Assessments page which covers things such as extensions and resubmission.
Page 13 of 14
CRITERIA FOR MARKING:
Submissions will be graded on:
● the correct application of relational database principles,
● the correct handling of transactions and the setting of appropriate transaction boundaries
i.e. correct placement of commits, and
● the correct application of SQL statements and constructs to:
○ populate tables,
○ modify existing data in tables,
○ prepare reports by retrieving the required data in the required format, and
○ modify the “live” database structure to meet the expressed requirements (including
appropriate use of constraints). In making these modifications there must be no loss of existing data or data integrity within the database.
Submissions will be grade penalised if they:
● contain SET ECHO … or SPOOL commands
● make use of views
● use subqueries and SQL conditions unnecessarily,
● do not use to_char/to_date where appropriate in handling dates,
● do not have an appropriate development history on the FIT GitLab server for all source files (at
least four pushes required).
Page 14 of 14