comp 231 database management systems
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
Department of Computer Science and Engineering, HKUST Slide 1
Comp 231 Database Management Systems
Comp 231 Database Management Systems
6. Integrity Constraints
Department of Computer Science and Engineering, HKUST Slide 2
Integrity ConstraintsIntegrity Constraints
• Domain Constraints• Referential Integrity• Assertions• Triggers• Functional Dependencies
Integrity constraints guard against accidental damage tothe database, by ensuring that authorized changesto the database do not result in a loss of data consistency.
Department of Computer Science and Engineering, HKUST Slide 3
Domain ConstraintsDomain 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 4
Domain ConstraintsDomain Constraints
condition must be TRUEname of constraint
new domain name
• 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 5
an SQL condition
Specifying ConstraintsSpecifying 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 6
Referential IntegrityReferential 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.
Primary keys ofrespective relations
Foreign key
branch (branch-name, branch-city, asset ) Perryridge Brooklyn 500,000
account ( account-no, branch-name, balance ) A-123 Perryridge 5000
A set of attributes X in R is a foreign key if it is not a primary key of R butit is a primary key of some relation S.
Department of Computer Science and Engineering, HKUST Slide 7
Referential IntegrityReferential 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)
R2 ( K2, …, , … ) t2R1 ( K1, …, … )
t1x
x
Department of Computer Science and Engineering, HKUST Slide 8
Primary keyof Works-for
Foreign keys
Primary key of Dependent
Foreign key of Dependent sinceit is a primary key of Employee
Referential Integrity in the E-R ModelReferential 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)works-forEmp Dep
Department of Computer Science and Engineering, HKUST Slide 9
Referential Integrity for Insertion and Deletion
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 comput
e 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 10
Referential Integrity for UpdateReferential 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 that
t2’[] 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 11
Referential Integrity in SQLReferential 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 12
Referential Integrity in SQL -exampleReferential Integrity in SQL -example
create table customer (customer-name char(20) not null, customer-street char(30), customer-city char(30), primary key (customer-name))
create table 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 13
Referential integrity in SQL- exampleReferential integrity in SQL- example
create table account (branch-name char(15), account-number char(10) not null, balance integer, primary key(account-number), foreign key (branch-name) references branch)
create table 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 14
Cascading Actions in SQLCascading 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 branch
on delete cascade on update cascade,
…)
Department of Computer Science and Engineering, HKUST Slide 15
Cascading Actions in SQLCascading 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 16
AssertionsAssertions
• 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 17
• 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 loanwhere loan.branch-name=branch.branch-
name) >= (select sum(amount) from account
where loan.number-name=branch.branch-name) ))
Assertion Example 1Assertion Example 1
Department of Computer Science and Engineering, HKUST Slide 18
Assertion Example 2Assertion Example 2
loans without such an account
• Every loan has at least one borrower who maintains an account with a minimum balance of $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) ))
must return T or F
Department of Computer Science and Engineering, HKUST Slide 19
TriggersTriggers
• 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 20
Trigger ExampleTrigger 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 21
Trigger ExampleTrigger 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 22
Leaving SQLGoing into Relation Database Theory
Leaving SQLGoing into Relation Database Theory
Department of Computer Science and Engineering, HKUST Slide 23
Functional DependenceFunctional 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 determin
es 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 24
Functional DependenciesFunctional 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
PersonHKID DoBK222222 31/4/1948P111111 29/2/1979R333333 31/2/1961K222222 31/4/1948
Example: HKID DoB
t1
t2
t1
t2
D123456 31/2/1961 ?
P111111 31/2/1971 ?
Department of Computer Science and Engineering, HKUST Slide 25
Alternative Definitions of KeysAlternative 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 26
Functional DependenciesFunctional 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 amountloan-number branch-name
but would not expect the following to hold:loan-number customer-name
Department of Computer Science and Engineering, HKUST Slide 27
ExamplesExamples
loan-number amountloan-number branch-name
loan-number customer-name
loan-infobranch-nm loan-no cust-nm amountPerryridge L-001 Peter Yeung 100000Perryridge L-001 Peter Yeung 100000Central L-001 Peter Yeung 250000Wanchai L-002 Leon Lai 100000
Another example:reverse of the fd’s above
loan-infobranch-nm loan-no cust-nm amountPerryridge L-001 Peter Yeung 100000Perryridge L-001 David Chan 100000Perryridge L-001 May Chan 100000Wanchai L-002 Leon Lai 100000
Department of Computer Science and Engineering, HKUST Slide 28
Use of Functional DependenciesUse 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 29
Closure of a Set of Functional Dependencies
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 30
Examples of Armstrong’s AxiomsExamples of Armstrong’s Axioms
• We can find all of F+ by applying Armstrong’s Axioms:– if , then (reflexivity)
loan-no loan-noloan-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 31
ClosureClosure
• 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 (pseudotransitivit
y)
• 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 32
ExerciseExercise
Given loan-no amountDoes loan-no, branch-name amountWhy???
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 33
Example
A C; AG CG; CG I
A B; B H
• R = (A, B, C, G, H, I)
• F = {A B A C CG H
CG I B H}• some members of F+
A HAG I
CG HI
Department of Computer Science and Engineering, HKUST Slide 34
Closure of Attribute SetsClosure 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 35
Algorithm to Compute ClosureAlgorithm to Compute Closure
result is a (growing) set of attributes
result
result a subset of itself
• Algorithm to compute +, the closure of under F
result:= ;while (changes to result) do
for each in F dobegin if result then result :=result
;end
Department of Computer Science and Engineering, HKUST Slide 36
• R = (A, B, C, G, H, I)F = ( A B
A CCG HCG 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?
Example
Question: What is A+ and G+ ?
result contains all of the attributes of R, so stop
Department of Computer Science and Engineering, HKUST Slide 37
• 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.
Canonical CoverCanonical Cover
From A CI get AB C
Department of Computer Science and Engineering, HKUST Slide 38
Canonical CoverCanonical 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 12 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 39
• 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 BB C
Example of Computing a Canonical Cover
A BCB CAB C
A BCB CB C
A BCB C
A BB C
Department of Computer Science and Engineering, HKUST Slide 40
ExampleExample
• R = (A, B, C, G, H, I)F = { A B
A CCG HCG I B 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 of
CG H to infer CGI HI, and then transitivity