ch6完整性和安全性.pptVIP

Chapter 6: Integrity and Security Domain Constraints Referential Integrity Assertions断言 Triggers Security Authorization Authorization in SQL Domain Constraints Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. Domain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense. New domains can be created from existing data types E.g. create domain Dollars numeric12, 2create domain Pounds numeric12,2 We cannot assign or compare a value of type Dollars to a value of type Pounds.把一个域中的值转换到另一个域中。 However, we can convert type as belowcast r.A as Pounds  Should also multiply by the dollar-to-pound conversion-rate Domain Constraints Cont. The check clause in SQL-92 permits domains to be restricted: Use check clause to ensure that an hourly-wage domain allows only values greater than a specified value. create domain hourly-wage numeric5,2 constraint value-test约束名checkvalue 4.00 The domain has a constraint that ensures that the hourly-wage is greater than 4.00 The clause constraint value-test is optional; useful to indicate which constraint an update violated. Can have complex conditions in domain check create domain AccountType char10constraint account-type-testcheck value in ‘Checking’, ‘Saving’ 在deposit关系中指定约束：check branch-name in select branch-name from branch这个条件必须不仅仅是在deposit中插入或修改一个元组的时候检测，而且在关系branch中的元组被删除或修改时也要检测。 drop domain hourly-wage alter domain hourly-wage drop constraint value-test alter domain hourly-wage add constraint value-test checkvalue 4.00 Referential Integrity Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. Example: If “Perryridge” is a branch name appearing in one of the tuples in the account relation, then ther