RuleGen by Example (2)

[back]

Here's another example (given the well known EMP - DEPT database design:

"Departments that employ a manager, should also employ an administrator."

So when would this constraint need to be checked? Again we need to think about inserts, deletes or updates that could possibly violate this constraint:

1. Whenever a MANAGER is inserted: we need to check if the department for this manager has an ADMNISTRATOR.
2. Whenever an ADMINISTRATOR is deleted: we need to check if the department still complies with this business rule.
3. Whenever we a) update job to MANAGER, or b) update job away from ADMINISTRATOR, or c) transfer a MANAGER or ADMINISTRATOR to another department.

In RuleGen we specify the above when for this rule in following three queries:

 select deptno
from inserted_rows
where job = 'MANAGER'

select deptno
from deleted_rows
where job = 'ADMIN'

select new_deptno as deptno
from updated_rows
where (old_job <> 'MANAGER' or updated_DEPTNO = 'TRUE' ) and new_job = 'MANAGER'
union
select old_deptno
from updated_rows
where old_job = 'ADMIN' and (new_job <> 'ADMIN' or updated_deptno = 'TRUE') 

And finally we need to specify the how for this rule.