/* Database Exam, Artist database, Dr. N. Drawel */

CREATE TABLE GALLERY ( GAL_NUM char(4), GAL_OWNER varchar(35), GAL_AREACODE char(3), GAL_PHONE char(8), GAL_RATE number(8) ); INSERT INTO GALLERY VALUES('5','L. R. Gilliam','901','123-4456',0.37); INSERT INTO GALLERY VALUES('6','G. G. Waters','405','353-2243',0.45 ); INSERT INTO GALLERY VALUES('1','N. D. Cosner','203','123-9956',0.67); INSERT INTO GALLERY VALUES('2','S. H. Artwork','415','154-3243',0.30);

/* -- */

CREATE TABLE PAINTER ( PTR_NUM char(4), PTR_LASTNAME varchar(15) NOT NULL, PTR_FIRSTNAME varchar(15) NOT NULL, PTR_AREACODE char(3), PTR_PHONE char(8) ); INSERT INTO PAINTER VALUES('123','Ross','Georgette','901','885-4567'); INSERT INTO PAINTER VALUES('126','Itero','Julio','901','346-1112'); INSERT INTO PAINTER VALUES('127','Geoff','George','615','221-4456');

/* -- */

CREATE TABLE PAINTING ( PTNG_NUM char(4), PTNG_TITLE varchar(35), PTNG_PRICE number, PTR_NUM char(4), GAL_NUM char(4) ); INSERT INTO PAINTING VALUES('1338','Dawn Thunder',245.5,'123','5'); INSERT INTO PAINTING VALUES('1339','A Faded Rose',6723,'123','1'); INSERT INTO PAINTING VALUES('1340','The Founders',567.99,'126','6'); INSERT INTO PAINTING VALUES('1341','Hasty Pudding Exit',145.5,'123',''); INSERT INTO PAINTING VALUES('1342','Plastic Paradise',8328.99,'126','6'); INSERT INTO PAINTING VALUES('1343','Roamin''',785,'127','6'); INSERT INTO PAINTING VALUES('1344','Wild Waters',999,'127','5'); INSERT INTO PAINTING VALUES('1345','Stuff ''n Such ''n Some',9800,'123','5');

The Artist database (Artist.sql script) is loaded with this exam and you are ready to start. Use this script to create the tables with some sample data. > Given the Artist database table structures, there are 3 tables: GALLERY, PAINTER, and PAINTING. Each table has the following schema: GALLERY (GAL NUM, GAL_OWNER, GAL_AREACODE, GAL_PHONE, GAL_RATE) PAINTER (PTR_NUM, PTR_LASTNAME, PTR_FIRSTNAME, PTR_AREACODE, PTR_PHONE) PAINTING (PTNG_NUM, PTNG_TITLE, PTNG_PRICE, PTR NUM, GAL NUM) Note that the underlined attributes constitute each table's primary key. You will also do well to note that the PAINTING table has foreign keys relationships into the GALLERY table via its GAL_NUM attribute and PAINTER table via PTR_NUM attribute. Please explore the database and then write the appropriate SQL command to answer the following questions. A) Using the integrity enhancement features of SQL, please add the following constraints: 1. Create the foreign keys for this database and any missing primary keys. 2. PING_PRICE values in PAINTING table must be greater than 100$ and less than a 10000$ 3. No two titles with the same names in PAINTING table B) Give appropriate SQL statements for each of the following: 1. The manager wants to add a new painter as an artist in the database. What would the insert statement look like? 2. For a painter to be considered in the Artist database, the painter must have painted at least one painting, however, a painting might not be exhibited in a gallery. Based on these business rules, please insert one painting for the painter that you inserted in Q1. 3. List all painting titles with price greater than 5000$. 4. How many paintings does 'Ross Georgette' have? 5. Find the title of paintings that displayed on 'L. R. Gilliam' gallery. 6. Make a discount of 10% for all paintings. 7. Write the SQL command to save all the changes made to the table. 8. Remove a gallery number '5' from the GALLERY table. Is deleting a GALLERY row creates problems in the database, why/why not? If yes, how to solve this problem? 9. Select one of the following: (only one please) a. Display painters' details who exhibit their paintings at more than one gallery at a time b. List the paintings that are displayed on a gallery that has the maximum rate