Answered Essay: 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, First_Name, 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)

* Keys are in bold

Use triggers for the queries below:

1. 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.

2. 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).

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

4. 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%.

5. Create a trigger to ensure that Min_salary cannot exceed Max_salary for any job.

Expert Answer

 

1. CREATE TRIGGER Employee_Salary_Trigger ON Employee AFTER UPDATE AS

BEGIN
DECLARE @EmployeeSalary MONEY
DECLARE @ManagerSalary MONEY
SELECT @EmployeeSalary = Salary FROM Employees
SELECT @ManagerSalary = Salary FROM Employees
IF @EmployeeSalary > @ManagerSalary
BEGIN
ROLLBACK
PRINT (‘Manager salary cannot be less than the salary of his or her employee’)
END
END

2. CREATE OR REPLACE TRIGGER Job_Change_Trigger

AFTER UPDATE of Job_ID
ON EMPLOYEES
FOR Each Row
DECLARE
EndDate date;
StartDate date;
BEGIN
SELECT MAX(Last_Date) into EndDate
FROM Employee_History
WHERE Emp_ID = :Old.Emp_ID;

IF EndDate is null THEN
StartDate := :old.Hire_Date;
ELSE
StartDate := EndDate + 1;
END IF;

INSERT INTO Employee_History VALUES (:old.Emp_ID, StartDate, sysdate, :old.Job_ID, :old.Dep_ID);
END;

3. CREATE OR REPLACE TRIGGER Check_Salary_Not_Decreased
BEFORE UPDATE
ON Employees
FOR each row
BEGIN
IF :Old.Salary > :New.Salary THEN
RAISE_APPLICATION_ERROR(-20111,’Error – Salary can not be decreased’);
END IF;
END;

4. DECLARE

Experience number(2);
Hike number(5,2);
BEGIN
SELECT FLOOR((SYSDATE-Hire_Date) / 365 ) INTO Experience
FROM Employees
IF Experience > 8 THEN
Hike := 1.20;
ELSIF Experience > 3 THEN
Hike := 1.10;
ELSE
Hike := 1.05;

UPDATE Employees SET Salary = Salary * Hike
END;

5. CREATE TRIGGER Employee_Min_Max_Salary_Trigger ON Jobs AFTER UPDATE AS

BEGIN
DECLARE @Min_Salary MONEY
DECLARE @Max_Salary MONEY
SELECT @Min_Salary = Salary FROM Jobs
SELECT @Max_Salary = Salary FROM Jobs
IF @Min_Salary > @Max_Salary
BEGIN
ROLLBACK
PRINT (‘Min_Salary for an employee cannot exceed his or her Max_Salary.’)
END
END

Buy Essay
Calculate your paper price
Pages (550 words)
Approximate price: -

Help Me Write My Essay - Reasons:

Best Online Essay Writing Service

We strive to give our customers the best online essay writing experience. We Make sure essays are submitted on time and all the instructions are followed.

Our Writers are Experienced and Professional

Our essay writing service is founded on professional writers who are on stand by to help you any time.

Free Revision Fo all Essays

Sometimes you may require our writers to add on a point to make your essay as customised as possible, we will give you unlimited times to do this. And we will do it for free.

Timely Essay(s)

We understand the frustrations that comes with late essays and our writers are extra careful to not violate this term. Our support team is always engauging our writers to help you have your essay ahead of time.

Customised Essays &100% Confidential

Our Online writing Service has zero torelance for plagiarised papers. We have plagiarism checking tool that generate plagiarism reports just to make sure you are satisfied.

24/7 Customer Support

Our agents are ready to help you around the clock. Please feel free to reach out and enquire about anything.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

HOW OUR ONLINE ESSAY WRITING SERVICE WORKS

Let us write that nagging essay.

STEP 1

Submit Your Essay/Homework Instructions

By clicking on the "PLACE ORDER" button, tell us your requires. Be precise for an accurate customised essay. You may also upload any reading materials where applicable.

STEP 2

Pick A & Writer

Our ordering form will provide you with a list of writers and their feedbacks. At step 2, its time select a writer. Our online agents are on stand by to help you just in case.

STEP 3

Editing (OUR PART)

At this stage, our editor will go through your essay and make sure your writer did meet all the instructions.

STEP 4

Receive your Paper

After Editing, your paper will be sent to you via email.

× How can I help you?