Question

From this logical model the following relational model has been created: CUSTOMER P customer_id NUMBER (5) customer_gname VARCHAR2 (50) customer_fname VARCHAR2 (50) customer busnama VARCHAR2 (100) customer_street VARCHAR2 (50) * customer town VARCHAR2 (50) • customer_state CHAR (3) * customer phone CHAR (10) CUSTOMER PK (customer id) SALE sale_id NUMBER (5) sale_date DATE sale price NUMBER (92) customer_id NUMBER (5) aw_display_id NUMBER (7) SALE PK (sale id) CUSTOMER SALE (customer_id) SALE EXHIBIT (aw display id) SALE_IDX (aw_display_jd) ARTIST * artist code NUMBER (4) artist gname VARCHAR2 (50) artist_frame VARCHAR2 (50) * artist street VARCHAR2 (50) "artist_town VARCHAR2 (50) "artist_state CHAR (3) artist phone CHAR (10) "artist_noworks NUMBER (3) ARTIST PK (artist_code) DATE GALLERY pgallery.id NUMBER (3) gallery_name VARCHAR2 (100) "gallery_manager VARCHAR2 (60) * gallery street VARCHAR2 (50) "gallery_town VARCHAR2 (50) "gallery_state CHAR (3) U gallery.phone CHAR (10) * gallery_salo percent NUMBER (4.1) "gallery_open DATE gallery close DATE GALLERY PK (gallery_jd) gallery phone uk (gallery phone) AW_DISPLAY aw_display_id NUMBER (7) UF' artistcode NUMBER (4) UF' artwork_no NUMBER aw_display start date aw_display_end_date DATE gallery.id NUMBER (3) AW DISPLAY PK (aw_display_id) aw_display_uq (aw display start date, artist code, artwork no) ARTWORK GALLERY (artwork.no, artist.code) GALLERY EXHIBIT (gallery id) ARTWORK FF artistcode NUMBER (4) Partwork.no NUMBER U artwork_title VARCHAR2(100) • artwork minpayment NUMBER (9.2) artwork_submitdate DATE ARTWORK PK (artwork.no, artist_code) artwork_title uk (artist code, artwork_title) ARTIST ARTWORK (wist_code) AW_STATUS Paws id NUMBER (7) UF artist.code NUMBER (4) UF artwork_no NUMBER Uaws date time DATE aws action CHAR (1) gallery.id NUMBER (3) AW STATUS PK (ws id) aw statusu (aws date time, artist code, artwork no) ARTWORK AW STATUS (artwork no, artist.code) GALLERY AW STATUS (gallery id)

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)

(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.

Please help me with this tasks.

Thank you in advance:)

Answer 1

Solution:

(i) CREATE SEQUENCE SQ1 START WITH 300 INCREMENT BY 1;

INSERT INTO AW-STATUS(aws_id) VALUES(SQ1.nextval);

INSERT INTO AW-DISPLAY(aw_display_id) VALUES(SQ1.nextval);

INSERT INTO SALE(sale_id) VALUES(SQ1.nextval);

(ii) CREATE SEQUENCE SQ2 START WITH 500000;

ALTER TABLE AW_STATUS ADD PRIMARY KEY(artist_code);

INSERT INTO ARTWORK VALUES(17,001,“Saint Catherine of Siena'',SQ2.nextval,"2020-10-22 10:00:00");

INSERT INTO AW_STATUS VALUES(300,17,001,"2020-10-22 10:00:00","Y",111);

(iii) (a) INSERT INTO AW_STATUS VALUES(SQ1.nextval,17,001,"2020-10-22 11:00:00","N",111);

(b) UPDATE AW_STATUS SET set aws_date_time="2020-10-22 14:15:00",aws_action="Y" where gallery_id=111;

(c) INSERT INTO AW_DISPLAY VALUES(300,17,001,"2020-10-23", "2020-11-01",111);

(iv) INSERT INTO SALE VALUES(SQ1.nextval,"2020-10-27",850000 ,1,300);