Assertions An assertion is a predicate expressing a condition that we wish the database always to satisfy. An assertion in SQL takes the form create assertion <assertion-name>check <predicate> When an assertion is made,the system tests it for validity,and tests it again on every update that may violate the assertion This testing may introduce a significant amount of overhead; hence assertions should be used with great care. Asserting for all X,P(X) is achieved in a round-about fashion using not exists X such that not P(X) Database System Concepts,5th Edition,Oct 5.2006 4.17 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.17 ©Silberschatz, Korth and Sudarshan Assertions An assertion is a predicate expressing a condition that we wish the database always to satisfy. An assertion in SQL takes the form create assertion <assertion-name> check <predicate> When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion This testing may introduce a significant amount of overhead; hence assertions should be used with great care. Asserting for all X, P(X) is achieved in a round-about fashion using not exists X such that not P(X)
Assertion Example Every loan has at least one borrower who maintains an account with a minimum balance or $1000.00 create assertion balance_constraint check (not exists select from loan where not exists select from borrower,depositor,account where loan.loan_number borrower.loan_number and borrower.customer name depositor.customer name and depositor.account number account.account number and account.balance >1000))) Database System Concepts,5th Edition,Oct 5.2006 4.18 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.18 ©Silberschatz, Korth and Sudarshan Assertion Example Every loan has at least one borrower who maintains an account with a minimum balance or $1000.00 create assertion balance_constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_name and depositor.account_number = account.account_number and account.balance >= 1000)))
Assertion Example The sum of all loan amounts for each branch must be 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.branch name branch.branch name >=(select sum (amount from account where loan.branch_name branch.branch_name )) Database System Concepts,5th Edition,Oct 5.2006 4.19 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.19 ©Silberschatz, Korth and Sudarshan Assertion Example The sum of all loan amounts for each branch must be 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.branch_name = branch.branch_name ) >= (select sum (amount ) from account where loan.branch_name = branch.branch_name )))
Authorization Forms of authorization on parts of the database: Read-allows reading,but not modification of data. Insert-allows insertion of new data,but not modification of existing data. Update-allows modification,but not deletion of data. Delete -allows deletion of data. Forms of authorization to modify the database schema(covered in Chapter 8): Index-allows creation and deletion of indices. Resources-allows creation of new relations. Alteration-allows addition or deletion of attributes in a relation. Drop-allows deletion of relations. Database System Concepts,5th Edition,Oct 5.2006 4.20 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.20 ©Silberschatz, Korth and Sudarshan Authorization Forms of authorization on parts of the database: Read - allows reading, but not modification of data. Insert - allows insertion of new data, but not modification of existing data. Update - allows modification, but not deletion of data. Delete - allows deletion of data. Forms of authorization to modify the database schema (covered in Chapter 8): Index - allows creation and deletion of indices. Resources - allows creation of new relations. Alteration - allows addition or deletion of attributes in a relation. Drop - allows deletion of relations