- 162 Views
- Uploaded on
- Presentation posted in: General

Integrity Constraints

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Integrity Constraints

Three things managed by a DBMS

- Data organization
- E/R Model
- Relational Model

- Data Retrieval
- Relational Algebra
- Relational Calculus
- SQL

- Data Integrity and Database Design
- Integrity Constraints
- Functional Dependencies
- Normalization

Purpose: prevent semantic inconsistencies in data

e.g.:

e.g.:

4 kinds of IC’s:

1. Key Constraints

2. Attribute Constraints

3. Referential Integrity Constraints

4. Global Constraints

No entry for Kenmore...

???

What are they?

- predicates on the database
- must always be true (:, checked whenever db gets updated)
There are the following 4 types of IC’s:

Key constraints (1 table)

e.g., 2 accts can’t share the same acct_no

Attribute constraints (1 table)

e.g., 2 accts must have nonnegative balance

Referential Integrity constraints ( 2 tables)

E.g. bnames associated w/ loans must be names of real branches

SQL examples:

1. Primary Key:

CREATE TABLE branch(

bname CHAR(15) PRIMARY KEY,

bcity CHAR(20),

assets INT);

or

CREATE TABLE depositor(

cname CHAR(15),

acct_no CHAR(5),

PRIMARY KEY(cname, acct_no));

2. Candidate Keys:

CREATE TABLE customer (

ssn CHAR(9) PRIMARY KEY,

cname CHAR(15),

address CHAR(30),

city CHAR(10),

UNIQUE (cname, address, city);

Idea: specifies that a relation is a set, not a bag

Effect of SQL Key declarations

PRIMARY (A1, A2, .., An) or

UNIQUE (A1, A2, ..., An)

Insertions: check if any tuple has same values for A1, A2, .., An as any

inserted tuple. If found, reject insertion

Updates to any of A1, A2, ..., An: treat as insertion of entire tuple

- Primary vs Unique (candidate)
- 1 primary key per table, several unique keys allowed.
- Only primary key can be referenced by “foreign key” (ref integrity)
- DBMS may treat primary key differently
- (e.g.: implicitly create an index on PK)
- 4. NULL values permitted in UNIQUE keys but not in PRIMARY KEY

Idea:

- Attach constraints to values of attributes
- Enhances types system (e.g.: >= 0 rather than integer)
In SQL:

1. NOT NULL

e.g.: CREATE TABLE branch(

bname CHAR(15) NOT NULL,

....

)

Note: declaring bname as primary key also prevents null values

2. CHECK

e.g.: CREATE TABLE depositor(

....

balance int NOT NULL,

CHECK( balance >= 0),

....

)

affect insertions, update in affected columns

CHECK cond where cond is:

- Boolean expression evaluated using the values in the row being inserted or updated, and
- Does not contain subqueries; sequences; the SQL functions SYSDATE, UID, USER, or USERENV; or the pseudocolumns LEVEL or ROWNUM
Multiple CHECK constraints

- No limit on the number of CHECK constraints you can define on a column
CREATE TABLE credit_card(

....

balance int NOT NULL,

CHECK( balance >= 0),

CHECK (balance < limit),

....

)

Idea: prevent “dangling tuples” (e.g.: a loan with a bname of ‘Kenmore’ when no Kenmore tuple is not in branch table)

Referencing

Relation

(e.g. loan)

Referenced

Relation

(e.g. branch)

“foreign key”

bname

primary key

bname

Ref Integrity:

ensure that:

foreign key value primary key value

(note: need not to ensure , i.e., not all branches have to have loans)

bname

bname

x

Referencing

Relation

(e.g. loan)

parent

Referenced

Relation

(e.g. branch)

x

x

child

In SQL:

CREATE TABLE branch(

bname CHAR(15) PRIMARY KEY

....)

CREATE TABLE loan (

.........

FOREIGN KEY bname REFERENCES branch);

Affects:

1) Insertions, updates of referencing relation

2) Deletions, updates of referenced relation

c

c

ti

x

parent

x

tj

x

child

what happens when

we try to delete

this tuple?

A

B

- Ans: Oracle allows the following possibilities
- No action
- RESTRICT: reject deletion/ update
- SET TO NULL: set ti [c], tj[c] = NULL
- SET TO DEFAULT: set ti [c], tj[c] = default_val
- CASCADE: propagate deletion/update

c

c

ti

x

x

tj

x

what happens when

we try to delete

this tuple?

Emp

Dept

ALTER TABLE Dept ADD Primary Key (deptno);

ALTER TABLE Emp ADD FOREIGN KEY (Deptno)

REFERENCES Dept(Deptno) [ACTION];

Action: 1) ON DELETE NO ACTION left blank (deletion/update rejected)

2) ON DELETE SET NULL/ ON UPDATE SET NULL

sets ti[c] = NULL, tj[c] = NULL

3) ON DELETE CASCADE

deletes ti, tj

ON UPDATE CASCADE

sets ti[c], tj[c] to new key values

Idea: two kinds

1) single relation (constraints spans multiple columns)

E.g.: CHECK (total = svngs + check) declared in the CREATE TABLE

Example: All Bkln branches must have assets > 5M

CREATE TABLE branch (

..........

bcity CHAR(15),

assets INT,

CHECK (NOT(bcity = ‘Bkln’) OR assets > 5M))

Affects:

insertions into branch

updates of bcity or assets in branch

2) Multiple Relations: NOT supported in Oracle

Need to be implemented as a Trigger

SQL example:

2) Multiple relations: every loan has a borrower with a savings account

CHECK (NOT EXISTS (

SELECT *

FROM loan AS L

WHERE NOT EXISTS(

SELECT *

FROM borrower B, depositor D, account A

WHERE B.cname = D.cname AND

D.acct_no = A.acct_no AND

L.lno = B.lno)))

Problem: Where to put this constraint? At depositor? Loan? ....

Ans: None of the above:

CREATE ASSERTION loan-constraint

CHECK( ..... )

Checked with EVERY DB update!

very expensive.....

Issues:

1) How does one decide what global constraint to impose?

2) How does one minimize the cost of checking the global constraints?

Ans: Functional dependencies.

but before we go there

- SET ALL CONSTRAINTS DEFERRED;
- Defers all constraint checks till the end of the transaction
- Especially useful in enforcing Referential integrity
- Insert new rows into ‘Child’ table but referred key is not yet in Parent
- Insert corresponding row in ‘Parent’ table
- Constraint checking done at the end of the transaction

- Can also defer individual constraint checking by specifying the constraint name
- Finding the constraint information in Oracle
- SELECT * FROM USER_CONSTRAINTS;
- SELECT * FROM USER_CONS_COLS;

Three things managed by a DBMS

- Data organization
- E/R Model
- Relational Model

- Data Retrieval
- Relational Algebra
- Relational Calculus
- SQL

- Data Integrity and Database Design
- Integrity Constraints
- Functional Dependencies
- Constraints that hold for legal instance of the database
- Example: Every customer should have a single credit card

- Normalization

A B C

“ AB determines C”

two tuples with the same values for A and B

will also have the same value for C

Constraints that will hold on all “legal” instances of the database for the

specific business application.

In most cases, specified by a database designer/business architect

Shorthand:

C BD same as C B

C D

Be careful!

AB C not the same as AC

BC

Not true

Example:

suppose R = { A, B, C, D, E, H} and we determine that:

F = { A BC,

B CE,

A E,

AC H,

D B}

Then we determine the ‘canonical cover’ of F:

Fc = { A BH,

B CE,

D B}

ensuring that F and Fc are equivalent

Note: F requires 5 assertions

Fc requires 3 assertions

Canonical cover (or minimal cover) algorithm: In the book

(not covered here).

Equivalence of FD sets:

FD sets F and G are equivalent if the imply the same set of FD’s

e.g. A B and B C : implies A C

equivalence usually expressed in terms of closures

Closures:

For any FD set, F, F+ is the set of all FD’s implied by F.

can calculate in 2 ways:

(1) Attribute Closure

(2) Armstrong’s axioms

Both techniques tedious-- will do only for toy examples

F equivalent to G iff F+ = G+

A. Fundamental Rules (W, X, Y, Z: sets of attributes)

1. Reflexivity

If Y X then X Y

2. Augmentation

If X Y then WX WY

3. Transitivity

If X Y and Y Z then XZ

B. Additional rules (can be proved from A)

4. UNION: If X Y and X Z then X YZ

5. Decomposition: If X YZ then X Y, X Z

6. Pseudotransitivity: If X Y and WY Z then WX Z

2

3

Proving 4.(sketch): X Y => XXXY =>XXY

XYYZ

=> X YZ

For every step we used the rules from A.

Given;

F = { A BC, (1)

B CE, (2)

A E, (3)

AC H, (4)

D B} (5)

Exhaustively apply Armstrong’s axioms to generate F+

F+ = F

1. { A B, A C}: decomposition on (1)

2. { A CE}: transitivity to 1.1 and (2)

3. { B C, B E}: decomp to (2)

4. { A C, A E} decomp to 2

5. { A H} pseudotransitivity to 1.2 and (4)

Given;

R = { A, B, C, D, E, H,I} and:

F = { A BC,

C D,

CE,

AH I}

Attribute closure A

Iteration Result

-----------------------------------

0 A

1 A B C

2 A B C D

3 A B C D E

What is the closure of A (A+) ?

Algorithm att-closure (X: set of Attributes)

Result X

repeat until stable

for each FD in F, Y Z, do

if Y Result then

Result Result Z

Better to determine if a set of attributes is a key

Our goal:

given a set of FD set, F, find an alternative FD set, G that is:

smaller

equivalent

Bad news:

Testing F=G (F+ = G+) is computationally expensive

Good news:

Canonical Cover algorithm:

given a set of FD, F, finds minimal FD set equivalent to F

Minimal: can’t find another equivalent FD set w/ fewer FD’s

- 1. Canonical Cover algorithm
- result (Fc) guaranteed to be the minimal FD set equivalent to F

- 2. Closure Algorithms
- a. Armstrong’s Axioms:
- more common use: test for extraneous attributes
- in C.C. algorithm
- b. Attribute closure:
- more common use: test for superkeys
- 3. Purposes
- a. minimize the cost of global integrity constraints
- so far: min gic’s = |Fc|

In fact.... Min gic’s = 0

(FD’s for “normalization”)

Example:

R =

R: “Universal relation”

tuple meaning: Jones has a loan (L-17) for $1000 taken out at the Downtown

branch in Bkln which has assets of $9M

Design:

+ : fast queries (no need for joins!)

- : redudancy:

update anomalies examples?

deletion anomalies