Consider a DB schema consisting of the following relation schemes: Regions (_Reg

Question

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)

Keys are _underlined_

Now, express the following queries in SQL:

[Part 2] Use triggers for the queries below:

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.

Explanation / Answer

CREATE ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS (SELECT * FROM Employees E, Employees M, Departments D WHERE E.Salary > M.Salary AND E.Dep_ID = D.Dep_ID AND D.Manager_ID = M.Emp_ID) OR (SELECT * FROM Employees E WHERE E.Salary < ((SELECT Max(Salary) FROM Employees)*10 + SELECT Max(Salary) FROM Employees) AND Manager_ID=NIL)

This is for the 6th part. I am doing it in MySQL. It is very similar to SQL. The first part of assertion is salary less than manager and the other part is where you manager is not present.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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