Assertions

1. An assertion is a predicate expressing a condition we wish the database to always satisfy.
2. Domain constraints, functional dependency and referential integrity are special forms of assertion.
3. Where a constraint cannot be expressed in these forms, we use an assertion, e.g.
   • Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch.
   • Ensuring every loan customer keeps a minimum of $1000 in an account.
4. An assertion in DQL-92 takes the form,

    create assertion  assertion-name check predicate
   
   

5. Two assertions mentioned above can be written as follows.

   Ensuring the sum of loan amounts for each branch is less than the sum of all account balances at the branch.

    create assertion  sum-constraint check
   
   		 (not exists (select * from branch
   
   		 		 where (select sum)amount) from loan
   
   		 		 		 where (loan.bname =  branch.bname >=
   
   		 		 		 		 (select sum)amount) from account
   
   		 		 		 		 where (account.bname =  branch.bname)))
   
   

6. Ensuring every loan customer keeps a minimum of $1000 in an account.

    create assertion  balance-constraint check
   
   		 (not exists (select * from loan L
   
   		 		 (where not exists (select *
   
   		 		 from borrower B, depositor D, account A
   
   		 		 where L.loan# =  B.loan# and B.cname = D.cname
   
   		 		 		 		 and D.account# = A.account# 
   	and A.balance >= 1000 )))
   
   

7. When an assertion is created, the system tests it for validity.

   If the assertion is valid, any further modification to the database is allowed only if it does not cause that assertion to be violated.

   This testing may result in significant overhead if the assertions are complex. Because of this, the assert should be used with great care.

8. Some system developer omits support for general assertions or provides specialized form of assertions that are easier to test.