Question & Answer: Consider a DB schema consisting of the following relation schemes:…..

Consider a DB schema consisting of the following relation schemes:

Regions (Region_ID, Region_Name)

Countries (Country_id, Country_Name, Region_Id)

Locations (Location_Id, Street_address, Postal_code, City, State_Province, Country_Id)

Jobs (Job_Id, Job_title, Min_Salary, Max_salary)

Departments (Dep_Id, Dep_Name, Manager_Id, Location_Id)

Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id)

Employee_History (Emp_ID, Joining_date, last_date, Job_ID, Dep_ID)

6. Create a trigger to ensure that a salary of an employee cannot exceed the salary of his/her manager. If the employee does not have a manager, then his/her salary cannot be more than 10% of the highest salary in the database.

7. For changes in the job of an employee, updated details provided below must be written to Employee History: hire date of the employee for start date, old job ID, old department ID, Employee ID, todays’ system date for end date. In case a row is already present for employee job history then the start date must be the end date of that (row +1).

8. Make a Trigger to ensure that the salary of the employee is never decreased while working in an organization.

9. Create a trigger to ensure that an increase of salary for an employee is conform with the following rules: If experience is more than 8 years, increase salary by max 20%; If experience is greater than 3 years, increase salary by max of 10%; Otherwise a max increase of 5%.
10. Create a trigger to ensure that Min_salary cannot exceed Max_salary for any job.

Expert Answer

 

6.Create a trigger to ensure that a salary of an employee cannot exceed the salary of

his/her manager. If the employee does not have a manager, then his/her salary cannot

be more than 10% of the highest salary in the database.

// Here you did not mention about the database which you are using.

//In SqlServer

CREATE TRIGGER tri_inssal1 ON [dbo].[Employees]

INSTEAD OF INSERT

AS

declare @emp_id varchar(50);

declare @f_name varchar(100);

declare @l_name varchar(100);

declare @email varchar(100);

declare @phone varchar(15);

declare @hdate datetime;

declare @jobid varchar(50)

declare @emp_sal int;

declare @Comsn_pct varchar(50);

declare @Manager_Id varchar(50);

declare @Dep_Id int;

declare @mgrsal int;

declare @hsal int;

select @emp_id=d.Emp_ID from inserted d;

select @f_name=d.FirstName from inserted d;

select @l_name=d.Last_Name from inserted d;

select @email =d.E-mail from inserted d;

select @phone = d.Phone_number from inserted d;

select @hdate = d.Hire_date from inserted d;

select @jobid = d.Job_Id from inserted d;

select @emp_sal = d.Salary from inserted d;

select @Comsn_pct = d.Comsn_pct from inserted d;

select @Manager_Id = d.Manager_Id from inserted d;

select @Dep_Id = d.Dep_Id from inserted d;

BEGIN

set @mgrsal = (select isnull(salary,0) from Employees where Emp_ID=@Manager_Id)

if(@emp_sal>@mgrsal)

begin

RAISERROR(‘Cannot inset record where employee’s salary more than manager’s salary. ‘,16,1);

ROLLBACK;

end

else if (@mgrsal==0)

begin

set @hsal = (select max(salary) from Employees)

if (@emp_sal>@hsal*10/100)

begin

RAISERROR(‘Cannot inset record where employee’s salary more than 10 perc of the highest salary salary. ‘,16,1);

ROLLBACK;

end

else

begin

insert into Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id)

values(@emp_id,@f_name,@l_name,@email,@phone,@hdate,@jobid,@emp_sal,@Comsn_pct,@Comsn_pct,@Manager_Id,@Dep_Id);

COMMIT;

end

end

else

begin

insert into Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id)

values(@emp_id,@f_name,@l_name,@email,@phone,@hdate,@jobid,@emp_sal,@Comsn_pct,@Comsn_pct,@Manager_Id,@Dep_Id);

COMMIT;

end

END

GO

//You can use before trigger if you are using oracle/Mysql to throw the error message.

Eg:-

create trigger tri_inssal1

before insert or update

on Employees

for each row

begin

IF (mgrsal >empsal) THEN

raise_application_error( -20001, ‘No insertion with higher salary.’);

END IF;

end;

8. Make a Trigger to ensure that the salary of the employee is never decreased while working in an organization.

CREATE TRIGGER tri_upsal1 ON [dbo].[Employees]

INSTEAD OF UPDATE

AS

declare @emp_id varchar(50);

declare @f_name varchar(100);

declare @l_name varchar(100);

declare @email varchar(100);

declare @phone varchar(15);

declare @hdate datetime;

declare @jobid varchar(50)

declare @emp_sal int;

declare @Comsn_pct varchar(50);

declare @Manager_Id varchar(50);

declare @Dep_Id int;

declare @sal1 int;

select @emp_id=d.Emp_ID from inserted d;

select @f_name=d.FirstName from inserted d;

select @l_name=d.Last_Name from inserted d;

select @email =d.E-mail from inserted d;

select @phone = d.Phone_number from inserted d;

select @hdate = d.Hire_date from inserted d;

select @jobid = d.Job_Id from inserted d;

select @emp_sal = d.Salary from inserted d;

select @Comsn_pct = d.Comsn_pct from inserted d;

select @Manager_Id = d.Manager_Id from inserted d;

select @Dep_Id = d.Dep_Id from inserted d;

BEGIN

set @sal1 = (select isnull(salary,0) from Employees where Emp_ID=@emp_id)

if(@sal1>@emp_sal)

begin

RAISERROR(‘Cannot insert less salary for the employee ‘,16,1);

ROLLBACK;

end

else

begin

update Employees set Emp_ID=@emp_id, FirstName=@f_name, Last_Name=@l_name, E-mail=@email, Phone_number=@phone, Hire_date=@hdate, Job_Id=@jobid, Salary=@emp_sal, Comsn_pct=@Comsn_pct, Manager_Id=@Manager_Id, Dep_Id=@Dep_Id where Emp_ID=@emp_id;

COMMIT;

end

END

GO

7. For changes in the job of an employee, updated details provided below must be written to Employee History

Note:- Due to lack of time and multiple questions I couldn’t answer for the all questions. Similar to these examples you can complete it easily

CREATE TRIGGER trginsteadupd ON [dbo].[Employees]

INSTEAD OF UPDATE

AS

declare @emp_id varchar(50);

declare @f_name varchar(100);

declare @l_name varchar(100);

declare @email varchar(100);

declare @phone varchar(15);

declare @hdate datetime;

declare @jobid varchar(50)

declare @emp_sal int;

declare @Comsn_pct varchar(50);

declare @Manager_Id varchar(50);

declare @Dep_Id int;

select @emp_id=d.Emp_ID from inserted d;

select @f_name=d.FirstName from inserted d;

select @l_name=d.Last_Name from inserted d;

select @email =d.E-mail from inserted d;

select @phone = d.Phone_number from inserted d;

select @hdate = d.Hire_date from inserted d;

select @jobid = d.Job_Id from inserted d;

select @emp_sal = d.Salary from inserted d;

select @Comsn_pct = d.Comsn_pct from inserted d;

select @Manager_Id = d.Manager_Id from inserted d;

select @Dep_Id = d.Dep_Id from inserted d;

BEGIN

// Query to select last jodID and other details here

//Insert query to Employee_History table here

update Employees set Emp_ID=@emp_id, FirstName=@f_name, Last_Name=@l_name, E-mail=@email, Phone_number=@phone, Hire_date=@hdate, Job_Id=@jobid, Salary=@emp_sal, Comsn_pct=@Comsn_pct, Manager_Id=@Manager_Id, Dep_Id=@Dep_Id where Emp_ID=@emp_id;

COMMIT;

END

GO

Note:- Due to lack of time and multiple questions I couldn’t answer for the all questions. Similar to these examples you can do it easily

Still stressed from student homework?
Get quality assistance from academic writers!