1 / 40

Comp 231 Database Management Systems

Comp 231 Database Management Systems. 6. Integrity Constraints. Integrity Constraints. Domain Constraints Referential Integrity Assertions Triggers Functional Dependencies. Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes

etana
Télécharger la présentation

Comp 231 Database Management Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Comp 231 Database Management Systems 6. Integrity Constraints Department of Computer Science and Engineering, HKUST Slide 1

  2. Integrity Constraints • Domain Constraints • Referential Integrity • Assertions • Triggers • Functional Dependencies 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. Department of Computer Science and Engineering, HKUST Slide 2

  3. Domain Constraints • They define valid values for attributes • They 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. Department of Computer Science and Engineering, HKUST Slide 3

  4. new domain name name of constraint condition must be TRUE Domain Constraints • 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 numeric(5,2)constraint value-test check (value>=4.00) • The domain hourly-wage is declared to be a decimal number with 5 digits, 2 of which are after the decimal point • The domain has a constraint that ensures that the hourly-wage is greater than 4.00. • constraint value-test is optional; useful to indicate which constraint an update violated. Department of Computer Science and Engineering, HKUST Slide 4

  5. an SQL condition Specifying Constraints • Can have complex conditions in domain check • create domain AccountType char(10)constraint account-type-test check (value in (‘Checking’, ‘Saving’)) • check can be associated with a table definition:create table account … …check (branch-name in (select branch-name from branch)) Department of Computer Science and Engineering, HKUST Slide 5

  6. Foreign key account ( account-no, branch-name, balance ) A-123 Perryridge 5000 branch (branch-name, branch-city, asset ) Perryridge Brooklyn 500,000 Primary keys of respective relations Referential Integrity • Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attribute in another relation. • If an account exists in the database with branch name “Perryridge”, then the branch “Perryridge” must actually exist in the database. A set of attributes X in R is a foreign key if it is not a primary key of R but it is a primary key of some relation S. Department of Computer Science and Engineering, HKUST Slide 6

  7. R2 ( K2, …, , … ) R1 ( K1, …, … ) t2 t1 Referential Integrity • Formal Definition • Let r1(R1) and r2(R2) be relations with primary keys K1 and K2 respectively. • The subset  of R2 is a foreign key referencing K1 in relation r1, if for every t2 in r2 there must be a tuple t1 in r1 such that t1[K1]=t2[]. • Referential integrity constraint: (r2)  K1 (r1) x x Department of Computer Science and Engineering, HKUST Slide 7

  8. works- for Primary key of Works-for Emp Dep Foreign keys Primary key of Dependent Foreign key of Dependent since it is a primary key of Employee Referential Integrity in the E-R Model • Consider relationship R between entity E1 and E2. R is represented as a relation including primary keys K1 of E1 and K2 of E2. Then K1 and K2 form foreign keys on the relational schemas for E1 and E2 respectively. • Weak entity sets are also a source of referential integrity constraints. For, the relation schema for a weak entity set must include the primary key of the entity set which it depends.Dependent ( employee-no, dependent-name, age, sex ) Works-for ( employee-no, dept-no) Department of Computer Science and Engineering, HKUST Slide 8

  9. Referential Integrity for Insertion and Deletion • The following tests must be made in order to preserve the following referential integrity constraint:(r2)  K(r1) • Insert. If a tuple t2 is inserted into r2. The system must ensure that there is a tuple t1 in r1 such that t1[K] = t2[]. That is t2[] K(r1) • Delete. If a tuple t1 is deleted from r1, the system must compute the set of tuples in r2 that reference t1: =t1[K](r2)if this set is not empty, either the delete command is rejected as an error, or the tuples that reference t1 must themselves be deleted (cascading deletions are possible) Department of Computer Science and Engineering, HKUST Slide 9

  10. Referential Integrity for Update • if a tuple t2 is updated in relation r2 and the update modifies values for the foreign key , then a test similar to the insert case is made. Let t2’ denote the new value of tuple t2. The system must ensure thatt2’[] K(r1) • if a tuple t1 is updated in r1, and the update modifies values for primary key(K), then a test similar to the delete case is made. The system must compute=t1[K](r2)using the old value of t1 (the value before the update is applied). If this set is not empty, the update may be rejected as an error, or the update may be applied to the tuples in the set (cascade update), or the tuples in the set may be deleted. new foreign key value must exist no foreign keys contain the old primary key Department of Computer Science and Engineering, HKUST Slide 10

  11. Referential Integrity in SQL • Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement: • The primary key clause of the create table statement includes a list of the attributes that comprise the primary key. • The unique key clause of the create table statement includes a list of the attributes that comprise a candidate key. • The foreign key clause of the create table statement includes both a list of the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. Department of Computer Science and Engineering, HKUST Slide 11

  12. Referential Integrity in SQL -example createtable customer (customer-name char(20) not null, customer-street char(30), customer-city char(30),primary key (customer-name)) createtable branch (branch-name char(15) not null, branch-city char(30), assets integer, primary key (branch-name)) Department of Computer Science and Engineering, HKUST Slide 12

  13. Referential integrity in SQL- example createtable account (branch-name char(15), account-number char(10) not null, balance integer, primary key(account-number), foreign key (branch-name) references branch) createtable depositor (customer-name char(20) not null, account-number char(10) not null, primary key (customer-name, account-number), foreign key (account-number) references account, foreign key (customer-name) references customer) Department of Computer Science and Engineering, HKUST Slide 13

  14. Cascading Actions in SQL • Due to the on delete cascade clauses, if a delete of a tuple in branch results in referential-integrity constraint violation, the delete “cascades” to the account relation, deleting the tuple that refers to the branch that was deleted. • Cascading updates are similar. create table account ….. foreign key (branch-name) references branchon delete cascade on update cascade, …) Department of Computer Science and Engineering, HKUST Slide 14

  15. Cascading Actions in SQL • If there is a chain of foreign-key dependencies across multiple relations, with on delete cascade specified for each dependency, a deletion or update at one end of the chain can propagate across the entire chain. • If a cascading update or delete causes a constraint violation that cannot be handled by further cascading operation, the system aborts the transaction. As a result, all the changes caused by the transaction and its cascading actions are undone. Department of Computer Science and Engineering, HKUST Slide 15

  16. Assertions • An assertion is predicate expressing a condition that we wish the database always to satisfy. • An assertion in SQL-92 takes the formcreate assertion <assertion-name> check <predicate> • When an assertion is made, the system tests it for validity. This testing may introduce a significant amount of overhead; hence assertions should be used with great care. • Any predicate allowed in SQL can be used. Department of Computer Science and Engineering, HKUST Slide 16

  17. Assertion Example 1 • The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch. createassertion sum-constraint check(notexists (select * from branchwhere (selectsum(amount) from loanwhere loan.branch-name=branch.branch-name) >= (selectsum(amount) from accountwhere loan.number-name=branch.branch-name) )) Department of Computer Science and Engineering, HKUST Slide 17

  18. must return T or F Assertion Example 2 • Every loan has at least one borrower who maintains an account with a minimum balance of $1000.00. createassertion balance-constraint check (notexists (select * from loanwherenotexists (select * from borrower, depositor, account where loan.loan-number=borrower.loan-numberand borrower.customer-name=depositor.customer-nameand depositor.account-number=account.account-numberand account.balance >=1000) )) loans without such an account Department of Computer Science and Engineering, HKUST Slide 18

  19. Triggers • A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. • To design a trigger mechanism, we must: • Specify the conditions under which the trigger is to be executed. • Specify the actions to be taken when the trigger executes. • The SQL-92 standard does not include triggers, but many implementations support triggers. Department of Computer Science and Engineering, HKUST Slide 19

  20. Trigger Example • Suppose that instead of allowing negative account balances, the bank deals with overdrafts by • setting the account balance to zero • creating a loan in the amount of the overdraft • giving this loan a loan number which is identical to the account number of the overdrawn account. • The condition for executing the trigger is an update to the account relation that results in a negative balance value. Department of Computer Science and Engineering, HKUST Slide 20

  21. Trigger Example define trigger overdraft on update of account T (if new T.balance < 0then (insert into loan values (T.branch-name, T.account-number, - new T.balance)insert into borrower (select customer-name, account-numberfrom depositorwhere T.account-number = depositor.account-number)update account Sset S.balance =0where S.account-number =T.account-number)) The keyword new used before T.balance indicates that the value of T.balance after the update should be used; if it is omitted, the value before the update is used. PL/SQL Trigger Example Department of Computer Science and Engineering, HKUST Slide 21

  22. Leaving SQLGoing into Relation Database Theory Department of Computer Science and Engineering, HKUST Slide 22

  23. Functional Dependence • Existence dependence: The existence of B depends on A • Functional dependence: B’s value depends on A’s value • EmpName is functionally dependent on EmpNo • Given the EmpNo, I can one and only one value of EmpName • Constraints on the set of legal relation instances • Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. • Functional dependence is a generalization of the notion of a key. Department of Computer Science and Engineering, HKUST Slide 23

  24. Example: HKID  DoB t1 t1 t2 t2 ? ? D123456 P111111 31/2/1961 31/2/1971 Functional Dependencies • Let R be a relation schema  R,   R • The functional dependency   holds on R if and only if for any legal relation r(R), whenever any two tuples t1 and t2 of r agree on the attributes , they also agree on the attributes . That is, t1[] = t2[]  t1[] = t2[] • True for all tuple pairs • True for all instances R = ( A, B, C, D, E )  = A, B, C  = C, D Department of Computer Science and Engineering, HKUST Slide 24

  25. Alternative Definitions of Keys • K is a superkey for relation schema R if and only if K  R • This is the uniqueness property of “key” • K is a candidate key for R if and only if • K  R, and • there is no   K,   R make sure key is shortest possible (minimality) Department of Computer Science and Engineering, HKUST Slide 25

  26. Functional Dependencies • Functional dependencies allow us to express constraints that cannot be expressed using superkeys. Consider the schema:Loan-info = (branch-name, loan-number, customer-name, amount)We expect the following set of functional dependencies to hold: loan-number  amount loan-number  branch-namebut would not expect the following to hold: loan-number  customer-name Department of Computer Science and Engineering, HKUST Slide 26

  27. Examples loan-number  amountloan-number  branch-nameloan-number  customer-name  Another example: reverse of the fd’s above Department of Computer Science and Engineering, HKUST Slide 27

  28. Use of Functional Dependencies • We use functional dependencies to: • test relations to see if they are legal under a given set of functional dependencies. If a relation r is legal under a set F of functional dependencies, we say that r satisfies F. • Specify constraints on the set of legal relations; we say that F holds on R if all legal relations on R satisfy the set of functional dependencies F. A specific instance of a relation schema may satisfy a functional dependency even if the functional dependency does not hold on all legal instances. For example, a specific instance of Loan-schema may, by chance, satisfy loan-number  customer-name. Department of Computer Science and Engineering, HKUST Slide 28

  29. Closure of a Set of Functional Dependencies • Given a set of functional dependencies F, there are certain other functional dependencies that are logically implied by F. • The set of all functional dependencies logically implied by F is the closure of F. • We denote the closure of F by F+. • We can find all of F+ by applying Armstrong’s Axioms: • if   , then    (reflexivity) • if   , then    (augmentation) • if    and  , then    (transitivity)these rules are sound and complete. Department of Computer Science and Engineering, HKUST Slide 29

  30. Examples of Armstrong’s Axioms • We can find all of F+ by applying Armstrong’s Axioms: • if   , then    (reflexivity)loan-no  loan-no loan-no, amount  loan-noloan-no, amount  amount • if   , then    (augmentation)loan-no  amount (given)loan-no, branch-name amount, branch-name • if    and  , then    (transitivity)loan-no  branch-name (given) branch-name  branch-city (given)loan-no  branch-city Department of Computer Science and Engineering, HKUST Slide 30

  31. Closure • We can further simplify computation of F+ by using the following additional rules. • If    holds and    holds, then    holds (union) • If    holds, then    holds and    holds (decomposition) • If    holds and    holds, then    holds (pseudotransitivity) • The above rules can be inferred from Armstrong’s axioms. E.g.,  ,    (given)    (by augmentation)    (by transitivity) Department of Computer Science and Engineering, HKUST Slide 31

  32. Exercise Given loan-no amount Does loan-no, branch-name  amount Why??? It is not covered by any of the above axioms, so we must derive it: loan-no, branch-name  loan-no (reflexivity) loan-no amount (given) loan-no, branch-name  amount (transitivity) Department of Computer Science and Engineering, HKUST Slide 32

  33. Example • R = (A, B, C, G, H, I) • F = {A  B A  C CG  H • CG  I • B  H} • some members of F+ • A  H • AG  I • CG  HI A  B; B  H A  C; AG  CG; CG  I Department of Computer Science and Engineering, HKUST Slide 33

  34. Closure of Attribute Sets • Define the closure of  under F (denoted by +) as the set of attributes that are functionally determined by  under F:    is in F+    +Given loan-noIf loan-no  amountthen amount is part of loan-no+ I.e., loan-no+= loan-no,amount, …If loan-no  branch-namethen branch-name is part of loan-no+ I.e., loan-no+= loan-no,amount, branch-name …If loan-no  customer-name then continue ….Else stop  is a set of attributes Department of Computer Science and Engineering, HKUST Slide 34

  35. result  a subset of itself result   Algorithm to Compute Closure • Algorithm to compute +, the closure of  under F result:= ; while (changes to result) do for each    in F do begin if   result then result :=result  ; end result is a (growing) set of attributes Department of Computer Science and Engineering, HKUST Slide 35

  36. result contains all of the attributes of R, so stop Example • R = (A, B, C, G, H, I)F = ( A  B A  C CG  H CG  I B  H} • (AG+)1. Result= AG2. Result= ABCG (A  C; A  B and A  AG)3. Result= ABCGH (CG  H and CG  AGBC)4. Result=ABCGHI (CG  I and CG  AGBCH) • Is AG a candidate key?1. AG  R2. Does A+  R? 3. Does G+  R? Question: What isA+and G+ ? Department of Computer Science and Engineering, HKUST Slide 36

  37. Canonical Cover • Consider a set F of functional dependencies and the functional dependency    in F. • Attribute A is extraneous in  if A  and if A is removed from , the set of functional dependencies implied by F doesn’t change. Given AB  C and A  C then B is extraneous in AB • Attribute A is extraneous in  if A   and if A is removed from , the set of functional dependencies implied by F doesn’t change. Given A  BC and A  B then B is extraneous in BC • A canonical cover Fc for F is a set of dependencies such that F logically implies all dependencies in Fc and Fc logically implies all dependencies in F, and further • No functional dependency in Fc contains an extraneous attribute. • Each left side of a functional dependency in Fc is unique. From A  C I get AB  C   Department of Computer Science and Engineering, HKUST Slide 37

  38. Canonical Cover • Compute a canonical over for F: repeat use the union rule to replace any dependencies in F1  1 and 1  2 replaced with 1  12 Find a functional dependency    with an extraneous attribute either in  or in  If an extraneous attribute is found, delete it from   until F does not change Department of Computer Science and Engineering, HKUST Slide 38

  39. A  BCB  C AB  C A  BCB  CB  C A  BCB  C A  BB  C Example of Computing a Canonical Cover • R = (A, B, C)F = { A  BC B  C A  B AB  C} • Combine A  BC and A  B into A  BC • A is extraneous in AB  C because B  C logically implies AB  C. • C is extraneous in A  BC since A  BC is logically implied by A  B and B  C. • The canonical cover is:A  B B  C Department of Computer Science and Engineering, HKUST Slide 39

  40. Example • R = (A, B, C, G, H, I)F = { A BA CCG HCG IB H} • some members of F + • A H • by transitivity from A B and B H • AG I • by augmenting A C with G, to get AG CG and then transitivity with CG I • CG HI • from CG H and CG I : “union rule” can be inferred from • definition of functional dependencies, or • Augmentation of CG I to infer CG  CGI, augmentation ofCG H to inferCGI HI, and then transitivity Department of Computer Science and Engineering, HKUST Slide 40

More Related