USA: +1 725-224-8795 
Sign in
Consider a DB schema consisting of the following relation schemes:
| Regions (Region_ID, Region_Name)
Don't use plagiarized sources. Get Your Custom Essay on
Question & Answer: Consider a DB schema consisting of the following relation schemes:…..
GET AN ESSAY WRITTEN FOR YOU FROM AS LOW AS $13/PAGE
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
