Q1) Consider the following database. EMPLOYEE(Name, SSN, Salary, DNO, Supervisor

Question

Q1) Consider the following database.

EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode)

DEPARTMENT(DNO, TotalSalary, ManagerSSN)

STARTING_PAY(JobCode, StartPay)

Note that Dept_No in EMPLOYEE table is foreign key. Based on Active Database Concepts, answer the following questions:

Write the active rules (triggers) for following two events

1. Limit all salary increases to 50%.

2. All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table.

Q3) Explain Hybrid fragmentation

Explanation / Answer

Answer for Question 1:

Enforce policy that salaries may never decrease
before trigger emp_salary_no_decrease

EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode)

create trigger emp_salary_no_decrease
before update of EMPLOYEE
for each row
when (new.Salary < old.Salary)
begin
log the event;
signal error condition;
end

All new hires for a given
job code get the same starting salary, which is
available in the STARTING_PAY table.
before trigger emp_start_pay

EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode)
STARTING_PAY(JobCode, StartPay)

create trigger emp_start_pay
before insert on EMPLOYEE
for each row
set Salary =
(select StartPay
from STARTING_PAY
where JobCode = new.JobCode)

Answer for Question 2:

Hybrid fragmentation is the second fragmentation method that is unique to IDS with AD and XP Options database servers.
Hybrid fragmentation combines the advantages of both hash and expression-based fragmentation to provide fragment elimination for both range and
equality expressions in the WHERE clause and to minimize the effect of data skew that might result from expression-based fragmentation alone.

For hybrid fragmentation you create dbslices that contain several dbspaces, evenly distributed across co-servers.
In the case of a single co-server database server, you create dbslices that contain several dbspaces evenly distributed across disks.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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