Question

Create SQL statements based on the given tables:

Code to create the tables:

_{CREATE DATABASE LAB4DB;
USE LAB4DB;}

_{CREATE TABLE CUSTOMER (
CUS_CODE int primary key,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0');}

_{/* -- */}

_{CREATE TABLE EMP (
EMP_NUM int primary key,
EMP_TITLE varchar(4),
EMP_LNAME varchar(15),
EMP_FNAME varchar(15),
EMP_INITIAL varchar(1),
EMP_DOB datetime,
EMP_HIRE_DATE datetime,
EMP_AREACODE varchar(3),
EMP_PHONE varchar(8),
EMP_MGR int
);
INSERT INTO EMP VALUES('100','Mr.','Kolmycz','George','D','1942-06-15','1985-03-15','615','324-5456',null);
INSERT INTO EMP VALUES('101','Ms.','Lewis','Rhonda','G','1965-03-19','1986-04-25','615','324-4472','101');
INSERT INTO EMP VALUES('102','Mr.','VanDam','Rhett','','1958-11-14','1990-12-20','901','675-8993','101');
INSERT INTO EMP VALUES('103','Ms.','Jones','Anne','M','1974-10-16','1994-08-28','615','898-3456','101');
INSERT INTO EMP VALUES('104','Mr.','Lange','John','P','1971-11-08','1994-10-20','901','504-4430','105');
INSERT INTO EMP VALUES('105','Mr.','Williams','Robert','D','1975-03-14','1998-11-08','615','890-3220',null);
INSERT INTO EMP VALUES('106','Mrs.','Smith','Jeanine','K','1968-02-12','1989-01-05','615','324-7883','105');
INSERT INTO EMP VALUES('107','Mr.','Diante','Jorge','D','1974-08-21','1994-07-02','615','890-4567','105');
INSERT INTO EMP VALUES('108','Mr.','Wiesenbach','Paul','R','1966-02-14','1992-11-18','615','897-4358',null);
INSERT INTO EMP VALUES('109','Mr.','Smith','George','K','1961-06-18','1989-04-14','901','504-3339','108');
INSERT INTO EMP VALUES('110','Mrs.','Genkazi','Leighla','W','1970-05-19','1990-12-01','901','569-0093','108');
INSERT INTO EMP VALUES('111','Mr.','Washington','Rupert','E','1966-01-03','1993-06-21','615','890-4925','105');
INSERT INTO EMP VALUES('112','Mr.','Johnson','Edward','E','1961-05-14','1983-12-01','615','898-4387','110');
INSERT INTO EMP VALUES('113','Ms.','Smythe','Melanie','P','1970-09-15','1999-05-11','615','324-9006','105');
INSERT INTO EMP VALUES('114','Ms.','Brandon','Marie','G','1956-11-02','1979-11-15','901','882-0845','110');
INSERT INTO EMP VALUES('115','Mrs.','Saranda','Hermine','R','1972-07-25','1993-04-23','615','324-5505','110');
INSERT INTO EMP VALUES('116','Mr.','Smith','George','A','1965-11-08','1988-12-10','615','890-2984','110');}

_{/* -- */}

_{CREATE TABLE INVOICE (
INV_NUMBER int primary key,
CUS_CODE int,
INV_DATE datetime,
INV_AMOUNT float(8) default 0.0
);
INSERT INTO INVOICE VALUES('1001','10014','2016-01-16',24.94);
INSERT INTO INVOICE VALUES('1002','10011','2016-01-16',9.98);
INSERT INTO INVOICE VALUES('1003','10012','2016-01-16',153.85);
INSERT INTO INVOICE VALUES('1004','10011','2016-01-17',34.87);
INSERT INTO INVOICE VALUES('1005','10018','2016-01-17',70.44);
INSERT INTO INVOICE VALUES('1006','10014','2016-01-17',397.83);
INSERT INTO INVOICE VALUES('1007','10015','2016-01-17',34.97);
INSERT INTO INVOICE VALUES('1008','10011','2016-01-17',369.8);
INSERT INTO INVOICE VALUES('1009','10010','2016-01-17',0.0);}

_{/* -- */}

_{CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
primary key (INV_NUMBER, LINE_NUMBER)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95');}

_{/* -- */}

_{CREATE TABLE PRODUCT (
P_CODE varchar(10) primary key,
P_DESCRIPT varchar(35),
P_INDATE datetime,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int,
P_REORDER int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','2015-11-03','8','5','109.99','0','25595', 0);
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade', '2015-12-13','32','40', '14.99','0.05','21344',1);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade', '2015-11-13','18','12','17.49','0','21344', 0);
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50', '2016-01-15','15','18','39.95','0','23119', 1);
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50', '2016-01-15','23','5','43.99','0','23119', 0);
INSERT INTO PRODUCT VALUES('2232/QTY','B\&D jigsaw, 12-in. blade', '2015-12-30','8','5','109.92','0.05','24288', 0);
INSERT INTO PRODUCT VALUES('2232/QWE','B\&D jigsaw, 8-in. blade', '2015-12-24','6','5','99.87','0.05','24288', 0);
INSERT INTO PRODUCT VALUES('2238/QPD','B\&D cordless drill, 1/2-in.', '2016-01-20','12','5','38.95','0.05','25595', 0);
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer', '2016-01-20','23','10','9.95','0.1','21225', 0);
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.', '2016-01-02','8','5','14.40','0.05',null, 0);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine', '2015-12-15','43','20','4.99','0','21344', 0);
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.', '2016-02-07','11','5','256.99','0.05','24288', 0);
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft', '2016-02-20','188','75','5.87','0',null, 0);
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25', '2016-03-01','172','75','6.99','0','21225', 0);
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50', '2016-02-24','237','100','8.45','0','21231', 0);
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','2016-01-17','18','5','119.95','0.1','25595', 0);}

_{/* -- */}

_{CREATE TABLE VENDOR (
V_CODE int primary key,
V_NAME varchar(15),
V_CONTACT varchar(50),
V_AREACODE varchar(3),
V_PHONE varchar(8),
V_STATE varchar(2),
V_ORDER varchar(1)
);
INSERT INTO VENDOR VALUES('21225','Bryson, Inc.','Smithson','615','223-3234','TN','Y');
INSERT INTO VENDOR VALUES('21226','SuperLoo, Inc.','Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES('21231','D&E Supply','Singh','615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES('21344','Gomez Bros.','Ortega','615','889-2546','KY','N');
INSERT INTO VENDOR VALUES('22567','Dome Supply','Smith','901','678-1419','GA','N');
INSERT INTO VENDOR VALUES('23119','Randsets Ltd.','Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES('24004','Brackman Bros.','Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES('24288','ORDVA, Inc.','Hakford','615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES('25443','B&K, Inc.','Smith','904','227-0093','FL','N');
INSERT INTO VENDOR VALUES('25501','Damal Supplies','Smythe','615','890-3529','TN','N');
INSERT INTO VENDOR VALUES('25595','Rubicon Systems','Orton','904','456-0092','FL','Y');}

Questions:

Table: customer CUS CODE CUS_LNAME CUS FNAME CUS INITIAL CUS_AREACODE CUS PHONE CUS BALANCE 844-2573 894-1238 0 894-2285 894-2180 222-1672 442-3381 297-1228 290-2556 768.93 382-7185 297-3809 0 Columns: CUS CODa5 10012 CUS LNAME 345.86 536.75 CUS INITIALchar CUS AREACODE varchar CUS PHONE O'Brian 10016 221.19 216.55 Table: emp EMP NUM EMP TITLE EMP LNAME EMP FNAME EMP _INITIAL EMP DOB EMP HIRE DATE EMP AREACODE EMP PHONE EMP George 1942-06-15 00:00:00 1985-03-15 00:00:00 615 1965-03-19 00:00:00 1986-04-25 00:00:00 615 1958-11-1400:00:00 1990-12-20 00:00:00 901 1974-10-16 00:00:00 1994-08-28 00:00:00 615 1971-11-08 00:00:00 1994-10-20 00:00:00 901 1975-03-14 00:00:00 1998-11-08 00:00:00 615 1968-02-12 00:00:00 1989-01-05 00:00:00 615 1974-08-21 00:00:00 1994-07-02 00:00:00 615 1966-02-14 00:00:00 1992-11-18 00:00:00 615 1961-06-18 00:00:00 1989-04-14 00:00:00 901 1970-05-19 00:00:00 1990-12-0100:00:00 901 1966-01-03 00:00:00 1993-06-2100:00:00 615 1961-05-1400:00:00 1983-12-01 00:00:00 615 1970-09-15 00:00:00 1999-05-11 00:00:00 615 1956-11-02 00:00:00 1979-11-15 00:00:00 901 1972-07-25 00:00:00 1993-04-23 00:00:00 615 1965-11-08 00:00:00 1988-12-10 00:00:00 615 324-5456 324-4472 EMP TITLE VanDam 898-3456 504-443 890-3220 ULL 324-7883 datetime EMP HIRE _DATE datetime EMP AREACODE varchar(3) Jeanine EMP MGR Diante 897-4358 UL 504-3339 George 890-4925 898-4387 Brandon Saranda 324-5505 890-2984 George HULL Table: invoice INV AMOUNT CONTACT V AREACODE V PHONE STATE Y ORDER Table: vendor 2016-01-16 00:00:00 2494 2016-01-16 C0:C0:00 9.99 Columns: V CODE nt(11) PK datetme NN AMOUNT float _CONTACT var V AREACODE varchar 2016-01-17 00:00:00 ,44 2016-01-17 00:00:00 397.83 2016-01-17 00:C0:CO 3.97 2016-01-17 00:00:00 369.8 STATE .ORDER N NUMBER LINE NUMBER P CODE LINE UNITS LINE_PRICE Table: line P QCH P MIN P PRICE P DISCOUNT V CODE P REOR Table: product INV NUMRFR 11QER/JI Power panter. 15psı., 3-node 2015-11-03 ㆀ:00:00 B varchar(10)PK varchar(35) datetime archar(10) 1003 14Q1A3 9.00 LINE UNITS LINE PRIE P-DESCRIPT P INDATE 1558-QW1 Hd. doth, 1/2-in., 3x50 P DISCOUNT float 256.99 SW-23115 2.5-n. wd. screw, 50 R3TT3 Ste matting, 4x8'x1/6, 5"m.. 2016-01-1700:00:00 18 1. Create a MySQL stored procedure, called updateProdcut, that has two parameters, one being a product number and the other being a quantity by which the P_QOH attribute should be updated. The procedure should update, in the tuple identified by the first parameter, the P QOH by the amount of the second parameter, which could be positive or negative. Furthermore, the procedure should set the P_REORDER attribute to 1 if P_QOH is less than P_MIN and to 0 otherwise Show, using screenshots: (i) The CREATE statement, thenii) a PRODUCT TUPLE before its update, and (iii) invocation/calling of the procedure and the content of the tuple (which should show that it has been updated by the procedure. Calling the procedure is done manually by you by executing the CALL statement. 2. Create a MySQL stored procedure, called updatelnvoice, that has two parameters, one is the invoice number and the other is the amount by which the invoice amount should be updated. Show a screenshot of your procedure and screenshots of testing showing that the procedure works (similar to the above question) 3. Create a MySQL trigger, called afterInsertLine, that invokes the two procedures to appropriately update the invoice and the product to which it pertains. Show screenshots showing the trigger content and testing to demonstrate that the trigger and stored procedures work. 4. Similarly, create triggers, called afterDeleteLine and afterUpdateLine, that update the product and invoice tables appropriately after deleting and updating a line, respectively. Of course, include screenshots showing your triggers and results of testing

Answer 1

create procedure updateProduct
(IN ProductNumber varchar(10),
IN P_QOH_Supplied int)
as
begin
   declare P_MIN_Current int
   declare P_QOH_Current int
   select P_MIN_Current = P_MIN,P_QOH_Current = P_QOH from product where P_Code = ProductNumber

set P_QOH_Current = P_QOH_Current + P_QOH_Supplied
   if (P_QOH_Current < P_MIN_Current ) then
       update product set P_QOH = P_QOH_Current,P_REORDER = 1 where P_CODE = ProductNumber
   else
       update product set P_QOH = P_QOH_Current,P_REORDER = 0 where P_CODE = ProductNumber
end

Before Stored procedure:After executing stored procedure with P_QOH = 10:

4 1546-QQ2 Hrd. cloth, 1/4in., 2x50 2016-01-15 00:00:00.000 15 18 39.950 23119

4 1546-QQ2 Hrd. cloth, 1/4in., 2x50 2016-01-15 00:00:00.000 25 1839.950 231190

create procedure updateInvoice
(IN InvoiceNumber int,
IN Amount FLOAT(8))
as
begin
   declare Amount_Current float(8)
   select Amount_Current = INV_AMOUNT from invoice where INV_NUMBER = InvoiceNumber
   set Amount_Current = Amount_Current + Amount
       update invoice set INV_AMOUNT = Amount_Current where INV_NUMBER = InvoiceNumber
end

Before Execution:

3 1003 10012 2016-01-16 00:00:00.000 153.85

After execution with Amount 100:

3 1003 10012 2016-01-16 00:00:00.000 253.85

3.

create trigger afterInsertLine After insert on Line FOR EACH ROW
begin

   CALL updateProduct (NEW.P_CODE,NEW.LINE_UNITS)
   CALL updateInvoice (NEW.INV_NUMBER,NEW.LINE_PRICE)
end

Before Execution:

2015-12-15 00:00:00.000 43 204.990 21344 0

2 1002 10011 2016-01-16 00:00:00.000 9.98

After Insert command :

INSERT INTO LINE VALUES('1002','2','54778-2T','2','4.99');

11 54778-2T Rattail file, 1/8in. fine 2015-12-15 00:00:00.000 45 20 4.990 21344 0

2 1002 10011 2016-01-16 00:00:00.000 14.97

4.

create trigger afterUpdateLine After insert on Line FOR EACH ROW
begin

declare INVPRICE float(8)

declare PQOH int

set INVPRICE = -OLD.LINE_PRICE

set PQOH = - OLD.LINE_UNITS

   CALL updateProduct (NEW.P_CODE,PQOH)
   CALL updateInvoice (NEW.INV_NUMBER,INVPRICE)

   CALL updateProduct (NEW.P_CODE,NEW.LINE_UNITS)
   CALL updateInvoice (NEW.INV_NUMBER,NEW.LINE_PRICE)
end

create trigger afterDeleteLine After insert on Line FOR EACH ROW
begin

declare INVPRICE float(8)

declare PQOH int

set INVPRICE = -OLD.LINE_PRICE

set PQOH = - OLD.LINE_UNITS

   CALL updateProduct (OLD.P_CODE,PQOH)
   CALL updateInvoice (OLD.INV_NUMBER,INVPRICE)
end