integrity constraints
Download
Skip this Video
Download Presentation
Integrity Constraints

Loading in 2 Seconds...

play fullscreen
1 / 28

Integrity Constraints - PowerPoint PPT Presentation


  • 191 Views
  • Uploaded on

Integrity Constraints. Review. 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. Integrity Constraints.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Integrity Constraints' - brooks


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.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 - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
review
Review

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
integrity constraints1
Integrity Constraints

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...

???

slide4
IC’s

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

key constraints
Key Constraints

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

key constraints1
Key Constraints

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
attribute constraints
Attribute Constraints

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 constraint in oracle
CHECK constraint in Oracle

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),

....

)

referential integrity constraints
Referential Integrity Constraints

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)

referential integrity constraints1
Referential Integrity Constraints

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

referential integrity constraints2
Referential Integrity Constraints

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
  • DELETE: delete ti, tj
  • UPDATE: set ti[c], tj[c] to updated values
referential integrity constraints3
Referential Integrity Constraints

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

global constraints
Global Constraints

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

global constraints not in oracle
Global Constraints (NOT in Oracle)

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.....

global constraints1
Global Constraints

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

deferring the constraint checking
Deferring the constraint checking
  • 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;
review1
Review

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
functional dependencies
Functional Dependencies

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

functional dependencies1
Functional Dependencies

Shorthand:

C  BD same as C  B

C  D

Be careful!

AB  C not the same as AC

BC

Not true

functional dependencies2
Functional Dependencies

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).

functional dependencies3
Functional Dependencies

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+

armstrong s axioms
Armstrong’s Axioms

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 XZ

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 => XXXY =>XXY

XYYZ

=> X YZ

For every step we used the rules from A.

fd closures using armstrong s axioms
FD Closures Using Armstrong’s Axioms

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)

attribute closures
Attribute Closures

Given;

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

F = { A  BC,

C D,

CE,

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

functional dependencies4
Functional dependencies

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

fd so far
FD so far...
  • 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”)

another use of fd s schema design
Another use of FD’s: Schema Design

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

ad