Implement a conditional CASE using dreamhome database
Upload in pdf form Chapter 8: Practice Procedure, Trigger, and Cursor 1. Procedure (1 pts) Implement a conditional CASE using dreamhome database -- raising salary of Staff (1) Start phpMyAdmin and choose dreamhome database (2) Click on Routines tab and add routine (3) Select procedure and give name raiseSalary (4) Drop input output parameter (no parameter this case) (5) Type code a. UPDATE Staff b. SET salary = CASE i. WHEN oposition = ‘Manager’ ii. THEN salary * 1.05 iii. ELSE iv. salary * 1.02 c. END; (6) Make sure you enter ‘ ‘ correctly (7) Click go (8) Click execute to execute this procedure (9) If no error, check staff table to see the salary values are updated. Your answer here (screen copy of updated Staff table) 2. Trigger (2 pts) Implement a business constraint by using a trigger. Constraint: “It allows only less than or equal to 6 staff in the Staff table at any time.” (1) Click on Triggers tab and click add a trigger Whenever a user tries to insert a new record into Staff table, following trigger will be activated. Before insertion, it will count the current number of staff in the table and it will display error message if count() returns greater than or equal to 6. Otherwise ( < 6),="" the="" new="" record="" will="" be="" inserted="" successfully.="" trigger="" name:="" num_of_staff_const="" table:="" staff="" time:="" before="" event:="" insert="" definition:="" begin="" declare="" number_of_staff="" integer;="" declare="" msg="" varchar(100);="" select="" count(*)="" into="" number_of_staff="" from="" staff;="" if="" number_of_staff="">= 6 THEN set msg ='It allows only less than or equal to 6 staff in the Staff table at any time'; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; END IF; END See Figure 1 for the details of trigger example. (2) After creating a trigger, try to insert a new record into Staff table. You would see an error message as described above. · Your answer here (screen capture of error message) (3) Edit the trigger by changing the criteria staff number from 6 to 7. Try to insert a new record (your name should be there) into Staff. You would see it performed successfully. · Your answer here (screen copy of updated Staff table) Figure 1. Adding a trigger No_of_staff_const 3. Cursor (2 pts) (1) Create a procedure with cursor, “client_cursor” which will display the first names and last names from clients whose preferred type are equal to ‘Flat’ from client table. Definition: Begin declare vfname varchar(30); declare vlnamevarchar(30); declare done integer DEFAULT 0; declare clientCursor cursor for select fname, lname from client where prefType ='Flat'; declare continue handler for not found set done=1; OPEN clientCursor; getClient: LOOP FETCH clientCursor INTO vfname, vlname; If done = 1 then Leave getClient; end if; select vfname, vlname; -- just display fname and lname END LOOP getClient; Close clientCursor; END (2) Click Go, and execute the procedure client_cursor. (3) Show result · Your answer here (screen capture of the execution of client_cursor) Refer to Figure 2 for the details of client_cursor procedure. Figure 2. Adding a new cursor client_cursor 2 CREATE DATABASE DreamHome ; /* DATETIME of SQL Server will be used as DATE data type of the standard SQL */ USE DreamHome ; CREATE TABLE Branch ( branchNo CHAR(4) NOT NULL, street VARCHAR(25) NOT NULL, city VARCHAR(15) NOT NULL, postcode VARCHAR(8) NOT NULL, CONSTRAINT branch_PK PRIMARY KEY (branchNo) ); CREATE TABLE Staff ( staffNo VARCHAR(5) NOT NULL, fName VARCHAR(15) NOT NULL, lName VARCHAR(15) NOT NULL, oPosition VARCHAR(10) NOT NULL, sex CHAR (1), DOB DATE, salary DECIMAL(9,2) NOT NULL, branchNo CHAR(4) NOT NULL, CONSTRAINT staff_PK PRIMARY KEY (staffNo), CONSTRAINT Staff_Branch_FK FOREIGN KEY (branchNo) REFERENCES Branch(branchNo) ); CREATE TABLE PrivateOwner ( ownerNo VARCHAR(7) NOT NULL, fName VARCHAR(15) NOT NULL, lName VARCHAR(15) NOT NULL, address VARCHAR(50) NOT NULL, telNo VARCHAR(13) NOT NULL, CONSTRAINT privateOwner_PK PRIMARY KEY (ownerNo) ); CREATE TABLE PropertyForRent ( propertyNo VARCHAR(8) NOT NULL, street VARCHAR(25) NOT NULL, city VARCHAR(15) NOT NULL, postcode VARCHAR(8) NOT NULL, propertyType VARCHAR(10) NOT NULL, rooms SMALLINT NOT NULL, rent DECIMAL(5, 1) NOT NULL, ownerNo VARCHAR(7) NOT NULL, staffNo VARCHAR(5), branchNo CHAR(4) NOT NULL, CONSTRAINT propertyForRent_PK PRIMARY KEY (propertyNo), CONSTRAINT Property_Owner_FK FOREIGN KEY (ownerNo) REFERENCES PrivateOwner(ownerNo), CONSTRAINT Property_Staff_FK FOREIGN KEY (staffNo) REFERENCES Staff(staffNo), CONSTRAINT Property_Branch_FK FOREIGN KEY (branchNo) REFERENCES Branch(branchNo) ); CREATE TABLE Client ( clientNo VARCHAR(7) NOT NULL, fName VARCHAR(15) NOT NULL, lName VARCHAR(15) NOT NULL, telNo VARCHAR(13) NOT NULL, prefType VARCHAR(10) NOT NULL, maxRent DECIMAL(5, 1) NOT NULL, CONSTRAINT client_PK PRIMARY KEY (clientNo) ); CREATE TABLE Viewing ( clientNo VARCHAR(7) NOT NULL, propertyNo VARCHAR(8) NOT NULL, viewDate DATE NOT NULL, comments VARCHAR(50), CONSTRAINT viewing_PK PRIMARY KEY (propertyNo, clientNo), CONSTRAINT Viewing_Propty_FK FOREIGN KEY (propertyNo) REFERENCES PropertyForRent(propertyNo), CONSTRAINT Viewing_Client_FK FOREIGN KEY (clientNo) REFERENCES Client(clientNo) ); CREATE TABLE Registration ( clientNo VARCHAR(7) NOT NULL, branchNo CHAR(4) NOT NULL, staffNo VARCHAR(5) NOT NULL, dateJoined DATE NOT NULL, CONSTRAINT registration_PK PRIMARY KEY (clientNo, branchNo), CONSTRAINT Regist_Client_FK FOREIGN KEY (clientNo) REFERENCES Client(clientNo), CONSTRAINT Regist_Branch_FK FOREIGN KEY (branchNo) REFERENCES Branch(branchNo), CONSTRAINT Regist_Staff_FK FOREIGN KEY (staffNo) REFERENCES Staff(staffNo) ); /*-- COMMIT -- Inserting the test data -- -- Note: SQL Server and some other DBMS do not accept DATE in front of date literals -- so you may need to remove the DATE words from the INSERT commands below. */ INSERT INTO Branch VALUES ('B005', '22 Deer Rd', 'London', 'SW1 4EH'); INSERT INTO Branch VALUES ('B007', '16 Argyll St', 'Aberdeen', 'AB2 3SU'); INSERT INTO Branch VALUES ('B003', '163 Main St', 'Glasgow', 'G11 9QX'); INSERT INTO Branch VALUES ('B004', '32 Manse Rd', 'Bristol', 'BS99 1NZ'); INSERT INTO Branch VALUES ('B002', '56 Clover Dr', 'London', 'NW10 6EU'); INSERT INTO Staff VALUES ('SL21', 'John', 'White','Manager', 'M', '1945-10-01', 30000, 'B005'); INSERT INTO Staff VALUES ('SG37', 'Ann', 'Beech','Assistant', 'F', '1960-10-11', 12000, 'B003'); INSERT INTO Staff VALUES ('SG14', 'David','Ford', 'Supervisor','M', '1958-11-24', 18000, 'B003'); INSERT INTO Staff VALUES ('SA9', 'Mary', 'Howe', 'Assistant', 'F', '1970-02-19', 9000, 'B007'); INSERT INTO Staff VALUES ('SG5', 'Susan','Brand','Manager', 'F', '1940-06-03', 24000, 'B003'); INSERT INTO Staff VALUES ('SL41', 'Julie','Lee', 'Assistant', 'F', '1965-06-13', 9000, 'B005'); INSERT INTO PrivateOwner VALUES ('CO46', 'Joe', 'Keogh', '2 Fergus Dr, Aberdeen AB2 7SX','01224-861212'); INSERT INTO PrivateOwner VALUES ('CO87', 'Carol','Farrel','6 Achray St, Glasgow G32 9DX', '0141-357-7419'); INSERT INTO PrivateOwner VALUES ('CO40', 'Tina', 'Murphy','63 Well St, Glasgow G42', '0141-943-1728'); INSERT INTO PrivateOwner VALUES ('CO93', 'Tony', 'Shaw', '12 Park Pl, Glasgow G4 0QR', '0141-225-7025'); INSERT INTO PropertyForRent VALUES ('PA14', '16 Holhead', 'Aberdeen', 'AB7 5SU', 'House',6, 650, 'CO46', 'SA9', 'B007'); INSERT INTO PropertyForRent VALUES ('PL94', '6 Argyll St', 'London', 'NW2', 'Flat', 4, 400, 'CO87', 'SL41', 'B005'); INSERT INTO PropertyForRent VALUES ('PG4', '6 Lawrence St','Glasgow', 'G11 9QX', 'Flat', 3, 350, 'CO40', NULL, 'B003'); INSERT INTO PropertyForRent VALUES ('PG36', '2 Manor Rd', 'Glasgow', 'G32 4QX', 'Flat', 3, 375, 'CO93', 'SG37', 'B003'); INSERT INTO PropertyForRent VALUES ('PG21', '18 Dale Rd', 'Glasgow', 'G12', 'House',5, 600, 'CO87', 'SG37', 'B003'); INSERT INTO PropertyForRent VALUES ('PG16', '5 Novar Dr', 'Glasgow', 'G12 9AX', 'Flat', 4, 450, 'CO93', 'SG14', 'B003'); INSERT INTO Client VALUES ('CR76', 'John', 'Kay', '0207-774-5632', 'Flat', 425); INSERT INTO Client VALUES ('CR56', 'Aline','Stewart', '0141-848-1825', 'Flat', 350); INSERT INTO Client VALUES ('CR74', 'Mike', 'Ritchie', '01475-392178', 'House', 750); INSERT INTO Client VALUES ('CR62', 'Mary', 'Tregar', '01224-196720', 'Flat', 600); INSERT INTO Viewing VALUES ('CR56', 'PA14', '2001-05-24',