Integrity constraints
This presentation is the property of its rightful owner.
Sponsored Links
1 / 28

Integrity Constraints PowerPoint PPT Presentation


  • 121 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

Integrity Constraints

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


Integrity constraints

Integrity Constraints


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

???


Integrity constraints

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;


    Summary integrity constraints

    Summary: Integrity Constraints


    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


  • Login