/* Database Systems, Coronel/Morris */
/* Type of SQL : MySQL */


CREATE TABLE GALLERY (
  GAL_NUM      VARCHAR(4),
  GAL_OWNER    VARCHAR(35),
  GAL_AREACODE VARCHAR(3),
  GAL_PHONE    VARCHAR(8),
  GAL_RATE     NUMERIC(4,2),
  PRIMARY KEY(GAL_NUM)
);

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');

/* -- */

CREATE TABLE PAINTER (
  PTR_NUM       VARCHAR(4),
  PTR_LASTNAME  VARCHAR(15),
  PTR_FIRSTNAME VARCHAR(15),
  PTR_AREACODE  VARCHAR(3),
  PTR_PHONE     VARCHAR(8),
  PRIMARY KEY(PTR_NUM)
);

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    VARCHAR(4),
  PTNG_TITLE  VARCHAR(35),
  PTNG_PRICE  NUMERIC(7,2),
  PTR_NUM     VARCHAR(4),
  GAL_NUM     VARCHAR(4),
  PRIMARY KEY(PTNG_NUM),
  FOREIGN KEY(PTR_NUM) REFERENCES PAINTER(PTR_NUM),
  FOREIGN KEY(GAL_NUM) REFERENCES GALLERY(GAL_NUM)
);

INSERT INTO PAINTING VALUES('1338','Dawn Thunder','245.5','123','5');
INSERT INTO PAINTING VALUES('1339','A Faded Rose','6723','123',NULL);
INSERT INTO PAINTING VALUES('1340','The Founders','567.99','126','6');
INSERT INTO PAINTING VALUES('1341','Hasty Pudding Exit','145.5','123',NULL);
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');


