Transcript Chapter 6 (Read Chapter 6.1

Chapter 6: Integrity
•
•
•
•
•
•
•
Objective
Key Constraints (Chapter 2)
Cardinality Constraints (Chapter 2)
Domain Constraints
Referential Integrity
Assertions
Triggers
Integrity Constraints
• Objective
– Maintain DB consistency
– Ensure that authorized changes to the database do
not result in a loss of data consistency
Domain Constraints
• Domain constraints
– The most elementary form of integrity
constraint
– Test values & queries Domain Constraints
– New domains can be created from existing data
types
– E.g. create domain Dollars numeric(12, 2)
create domain Pounds numeric(12,2)
• Type conversion
Domain Constraints (Cont.)
• The check clause in SQL-92
– create domain hourly-wage numeric(5,2)
constraint value-test check(value > = 4.00)
– constraint: the hourly-wage >= 4.00
– constraint value-test is optional
– A domain check can have complex conditions
• create domain AccountType char(10)
constraint account-type-test
check (value in (‘Checking’, ‘Saving’))
• check (branch-name in (select branch-name from
branch))
Referential Integrity
• Foreign key constraint
– Assume a branch name “Perryridge” appears in
the account relation
– “Perryridge” should also appear in branch relation
• Formal definition
– Foreign key
• The subset of R2’s attributes
• The primary key of R1
Referential Integrity and the E-R
Model
E1
R
E2
• Consider relationship set R between entity
sets E1 and E2
– The schema of R should include the primary
keys K1 of E1 and K2 of E2
– K1 and K2 form foreign keys on the relational
schemas for E1 and E2 respectively
Referential Integrity and the E-R
Model (Cont.)
• Weak entity sets
– The schema of a weak entity set must include
the primary key of the entity set on which it
depends
– Descriminator
Checking Referential Integrity on DB
Modification
• Rule of Thumb
– Maintain the subset dependency
 (r2)  K (r1)
• Insert
– When inserting a tuple t2 into r2, ensure that
there is a tuple t1 in r1 such that t1[K] = t2[]:
t2 []  K (r1)
DB Modification (Cont.)
• 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
– reject the delete command as an error, or
– delete the tuples that reference t1 (cascading
deletions)
Database Modification (Cont.)
• Update
– Case 1: If the value of the foreign key  is
modified, ensure that
t2’[]  K(r1)
Database Modification (Cont.)
• Update
– Case 2: If the update modifies the value of a
primary key, compute
•  = t1[K] (r2) using the old value of t1[K]
• If this set is not empty
– the update may be rejected as an error,
– the update may be cascaded to the tuples in the
set, or
– the tuples in the set may be deleted.
Referential Integrity in SQL
• Specify keys in the create table
statement:
– primary key clause
– unique key clause
– foreign key clause
• foreign key (account-number)
references account(account-number)
• foreign key (account-number)
references account
Referential Integrity in SQL
create table customer
(customer-name
char(20),
customer-street
char(30),
customer-city char(30),
primary key (customer-name))
create table branch
(branch-name char(15),
branch-city char(30),
assets
integer,
primary key (branch-name))
Referential Integrity in SQL
create table account
(account-number char(10),
branch-name char(15),
balance
integer,
primary key (account-number),
foreign key (branch-name) references branch)
create table depositor
(customer-name char(20),
account-number char(10),
primary key (customer-name, account-number),
foreign key (account-number) references
account,
foreign key (customer-name) references
customer)
Cascading Actions in SQL
create table account
...
foreign key(branch-name) references branch
on delete cascade
on update cascade
...)
• If a delete of a tuple in branch results in
referential-integrity constraint violation, the delete
“cascades” to the account relation
• A chain of foreign dependency
Cascading Actions in SQL (Cont.)
• Referential integrity
– Check at the end of a transaction
– Intermediate steps may violate referential
integrity if later steps remove the violation
– E.g. insert two tuples whose foreign keys point to
each other
• spouse attribute of relation
marriedperson(name, address, spouse)
• Any other example? Is this a good idea?
Alternatives to Cascading
• on delete set null, e.g., null branch-name
• on delete set default
• Null values in foreign key attributes
complicate SQL referential integrity
semantics
– Use not null to prevent
Assertions
• A predicate expressing a condition that DB
always need to satisfy
• create assertion <assertion-name> check
<predicate>
• System tests the validity
– May introduce a significant overhead
Assertion Example
• Sum(all loan amounts for each branch) < Sum(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)))
Triggers
• An automatically executed statementment as a
side effect of a modification to the database
– Specify the conditions under which the trigger is to be
executed
– Specify the actions
• Example
– Conidtion: an overdraft
– Possible actions:
• Set the account balance to zero
• Create a loan in the amount of the overdraft
End of Chapter