Question

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

Answer 1

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;
/