INTEGRITY

Document Sample
INTEGRITY Powered By Docstoc
					INTEGRITY
Integrity constraint
   Integrity constraints are specified on a
    database schema and are expected to
    hold on every valid database state of
    the schema.
   Integrity type for relational model
       Entity integrity
       Referential integrity
       Domain integrity
Domain Constraint
   Specify that within each tuple, the value
    of each attribute A must be an atomic
    value from DOM(A)
   Data Type
   FORMATE
   RANGE
   NULL or NOT NULL
   UNIQUE OR NOT UNIQUE
Entity integrity, Referential
Integrity and foreign key
   Entity integrity constraint states that no
    primary key value can be null.
   Why?
       Because the primary key is used to identified
        individual tuples in a relation
       If having null values implies that we can not
        identify some tuples.
       Ex. 2 or more tuples have null values, we might
        not able to distinguish them if we tried to
        reference them from other relations.
     Referential integrity constraint
   It is specified between 2 relations
   It is used to maintain the consistency
    among tuples in the two relations
   Informally, the referential integrity
    constraint states that a tuple in one relation
    that refer to another relation must refer to
    existing tuple in that relation.
Key Property
   The key satisfied 2 conditions
       Two distinct tuples in any state of the
        relation cannot have identical values for
        (all) the attributes in the key
        (Uniqueness)
       It is minimum set – that is can not remove
        any attributes and still have the
        uniqueness constraint in condition 1 hold
        (Minimality)
Candidate and Primary key
   In a relation may have more than one
    key.
   Each of Key is called a Candidate Key
   Example
       Relation Student       (ID,FNAME,LNAME, TCODE,
        FACTCODE, DEPTCODE)
       Has 2 candidate keys: ID and FNAME+LNAME
   One candidate key is selected to be Primary Key of
    the relation
     Foreign key
   A set of attributes FK in relation R1 is a foreign key of R1
    that references relation R2 if it satisfied the following 2
    rules
       The attributes in FK have the same domain(s) as the
        primary key attributes PK of R2;
         the attributes FK are said to reference or refer to the relation R2
       A value of FK in tuple t1 of the current state r1(R) either
        occurs as
          a value of PK for some tuple t2 in the current state r2(R)
          or is null.

          In the former case, we have t1[FK] = t2[PK],

         and we say that the tuple t1 references or refers to the tuple t2.
Specified constraints
   Must clear meaning and role of that
    each set of attributes plays in the
    various relation schemas of the
    database.
   Referential integrity constraint the from
    relationship among entities.
Specifying Basic Constraints in
SQL
   Create table Table_name
    (Col1   data_type constraint ,
    col2,…,
    Coln    Data_type constraint,
    Constraint Cont_name Primary Key
      (key atts)
  Example
GStudent (ID,NAME)

SQL> create table GStudent
  (id varchar2(10),
  name varchar2(30) not null,
  constraint gStuPk primary key (id));
           Oracle example
SQL> connect system/manager@tori
Connected.
SQL> desc dba_constraints
Name                             Null? Type
                                                                      SQL> select
----------------------------------------- -------- ----------------
OWNER                             NOT NULL VARCHAR2(30)
                                                                        constraint_name,
CONSTRAINT_NAME                   NOT NULL VARCHAR2(30)                 constraint_type from
CONSTRAINT_TYPE                              VARCHAR2(1)
TABLE_NAME                       NOT NULL VARCHAR2(30)                  dba_constraints
SEARCH_CONDITION
R_OWNER
                                 LONG
                                 VARCHAR2(30)                           where owner = 'A'
R_CONSTRAINT_NAME                VARCHAR2(30)
DELETE_RULE                      VARCHAR2(9)
STATUS                           VARCHAR2(8)                          CONSTRAINT_NAME                           C
DEFERRABLE                 VARCHAR2(14)                               --------------------------------------------
DEFERRED                   VARCHAR2(9)
                                                                      GSTUPK                                    P
VALIDATED                  VARCHAR2(13)
                                                                      SYS_C001127                               C
GENERATED                  VARCHAR2(14)
BAD                        VARCHAR2(3)
RELY                       VARCHAR2(4)
LAST_CHANGE                DATE
Specifying key and referential
integrity constraint
   Primary
       CONSTRINT Constraint_Name Primary
        Key (Key Attribute)
   Referential Integrity
       CONSTRAINT Constraint_Name Foreign
        Key (FK attri) references Table_name
        (Key_attri) On BusinessRule
    Example
create table Teacher
 (tcode varchar2(2),
  tname varchar2(30) not null,
  constraint TeacherPK Primary key (tcode))

alter table gstudent add (tcode varchar2(2));

alter table gStudent add (constraint GSTUFK
   foreign key (tcode) references
   teacher(tcode))
WHY INTEGRITY
is Importance?
IN DATABSE Design should
consider
   Foreign key can be NULL?
       Answer depend on the Business Rule
Insert
   DEPENDENT
   AUTOMATIC
   NULLIFY
   DEFAULT
Delete (Refer to others)
   Restrict
   Cascade
   Nullified
   Default
UPDATE OPERATIONS and DEALING
with Constraint Violation
   INSERT OPEARTION
       Provide of a list of a new Tuple t that insert in Relation R
       Domain Constraint can be violated if an attribute value is
        given that does not appear in the corresponding domain.
       Key constraint can be violated if a key value in the new tuple
        t already exist in another tuple in the relation r(R)
       Entity integrity can be violated if the primary key of the new
        tuple t is null.
       Referential Integrity can be violated if the value of any
        foreign key in t refer to a tuple that does not exist in the
        reference relation.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:9/6/2012
language:English
pages:19