本次数据库代写是完成oracle数据库的数据填充，数据查重等操作

FIT2094 Databases
2020 Semester 2
Assignment 2 – SQL – Monash Art Union (MAU)

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 (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.
[2 marks]
TASK 2: SQL Queries (60 marks):
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.
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).