Chapter 5, Problem 6PE

Explanation of Solution

Triggers:

• For inserting into the materialized view branch_cust the user must set a database trigger on an insert into depositor and account.
• Assume that the database system uses immediate binding for rule execution.
• Further, assume that the current version of a relation is denoted by the relation name itself, while the set of newly inserted tuples is denoted by qualifying the relation name with prefix “inserted”.
• The active rules for this insertion are given below –

define trigger insert into_branch_cust_via_depositor

after insert on depositor

referencing new table as inserted for each statement

insert into branch_cust

select branch_name, customer_name

from inserted, account

where inserted.account_number = account.account_number

define trigger insert into branch cust via account

after insert on account

referencing new table as inserted for each statement

insert into branch cust

select branch name, customer name

from depositor, inserted

where depositor.account number = inserted.account number

• In the above statements, if the execution binding was deferred (instead of immediate), then the result of the join of the set of new tuples of account with the set of new tuples of depositor would have been inserted by both active rules, leading to duplication of the corresponding tuples in branch_cust...