Create table Department Department: deptID(PK), deptName, chairID Insert 4 value

Question

Create table Department Department: deptID(PK), deptName, chairID

Insert 4 values into Department table

Each department has exactly one chair

Check what happens with department chair when you delete employee from the Employee table. Do you see a problem?

Write a trigger to fix this problem. You have to raise application error when someone tries to delete employee who is listed as a department chair in the department table. It can be achieved several different ways, but for this exercise, you will use a trigger.

Create at least four test cases to test the solution.

Steps:

1. Write SQL code to create table

2. Write SQL code to insert values in table.

3. Write Trigger code

4. Create Test cases and check trigger execution results.

Create at least four test cases to test the solution.

Please use Adventure works database or create a new database

Explanation / Answer

Step1. Create table department(deptID number(3) ,deptname varchar2(20));

Create table Employee( empID number(3), empFname varchar2(20), empLname varchar2(20), deptID(FK) number(3)) PRIMARY KEY (empID),FOREIGN KEY (deptID) REFERENCES department(deptID);

Step 2:insert into Employee values(100,'John','Worn',1);
insert into Employee values(101,'Narwin','Herd',2);
insert into Employee values(102,'Shailly','Bush',1);
insert into Employee values(103,'Cyrus','Jane',2);
insert into Employee values(104,'James','Stewart',3);
insert into Employee values(105,'John','Snow',4);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.