Create table Employee: empID (PK), empFname, empLname, deptID(FK) Insert least s

Question

Create table Employee: empID (PK), empFname, empLname, deptID(FK)

Insert least six rows in the Employee table.

Assumption:

Employee ID once in the EMPLOYEE table can't be changed.

Modify tables to enforce these rules.

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.

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

Step 3:

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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