Using SQL Developer ONLY Lab 5 1 Create a lab report file MS

Using SQL Developer ONLY!

Lab 5 1.

Create a lab report file (MS Word compatible) and name it as “IT4153_Lab 5_ Your D2L ID”.

a. Create two tables: Employee: empID (PK), empFname, empLname, deptID(FK) and Department: deptID(PK), deptName, chairID chairID is empID from Employee table

b. Insert at least 3 rows in the Department table and at least 6 rows in the Employee table.

c. Create trigger on update of chairID that enforces the following business rules • One employee can chair no more than one department. • Each department has exactly one chair.

Note: Check what happens with department when you delete it\'s chair from the table

Solution

create table Employee(empID NUMBER,
empFname VARCHAR2(20),
empLname VARCHAR2(20),
deptID NUMBER,
PRIMARY KEY (empID)
);
  
ALTER TABLE Employee
ADD FOREIGN KEY (deptID) REFERENCES Department(deptID);
  
  
create table Department(deptID NUMBER,
deptName VARCHAR2(20),
chairID NUMBER,
PRIMARY KEY (deptID));
  

ALTER TABLE Department
ADD FOREIGN KEY (chairID) REFERENCES Employee(empID);   
  
  
  
--b part
insert into Employee values (101,\'John\',\'Kennedy\',10);
insert into Employee values (102,\'Ram\',\'Kumar\',10);
insert into Employee values (103,\'Shayam\',\'Lal\',12);
insert into Employee values (104,\'Donald\',\'Trump\',12);
insert into Employee values (105,\'NArendra\',\'Modi\',12);
insert into Employee values (106,\'Jackie\',\'Obama\',10);

insert into Department values (10,\'Economics\',101);
insert into Department values (12,\'Economics\',102);
insert into Department values (15,\'Economics\',103);

--c.

CREATE OR REPLACE TRIGGER chairID_after_update
BEFORE UPDATE
ON Department
FOR EACH ROW

DECLARE
v_username varchar2(10);
v_count number;
v_sql varchar2(50);
BEGIN

select count(*) into v_count from Department where chairID = :new.chairID;
IF v_count = 0 then
v_sql := \'alter table set chairID =\'+ :new.chairID;
execute immediate v_sql;
end if;

END;
/