Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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,

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.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Databases questions