INTEGRITY RULES

Click here for audio-text lecture and feed it to the speech agent
Click here for an audio lecture that can be played using RealPlayer

  • Integrity rules are needed to inform the DBMS about certain constraints in the real world.
  • Specific integrity rules apply to one specific database.
    Example: part weights must be greater than zero.
  • General integrity rules apply to all databases.
    Two general rules will be discussed to deal with: primary keys and foreign keys.

    PRIMARY KEYS


  • Primary key is a unique identifier for a relation.
  • There could be several candidate keys, as long as the they satisfy two properties:
    1. uniqueness
    2. minimality
  • From the set of candidate keys, one is chosen to be the primary key.
  • The others become alternate keys.

    EXAMPLE: The relation R has several candidate keys.
    ID SSN License_Number NAME
    If we select ID to be the primary key, then the other candidate keys become alternate keys.

    THE ENTITY INTEGRITY RULE


  • No component of the primary key of a base relation is allowed to accept nulls.


    WHAT ARE NULLS?
    Null may mean "property does not apply". For example, the supplier may be a country, in which case the attribute CITY has a null value because such property does not apply.
    Null may mean "value is unknown". For example, if the supplier is a person, then a null value for CITY attribute means we do not know the location of this supplier.

  • Nulls cannot be in primary keys, but can be in alternate keys.
    EXAMPLE: SSN may be null for one and only one person (why?)

    FOREIGN KEYS


  • A foreign key is an attribute of one relation R2, whose values are required to match those of the primary key of some other relation R1 (R1 and R2 can be identical)
    EXAMPLE: SP relation has attribute S#, and S relation has primary key S#. Then S# in SP is considered a foreign key.
    SP is called the "referencing relation". S is called the "referenced relation".
    We can draw a "referential diagram"
    SP ---S#---> S
    or simply
    SP --------> S

    WHY ARE FOREIGN KEYS IMPORTANT?


  • Foreign-to-primary-key matching are the "glue" which holds the database together.
    Another way of saying it
  • Foreign keys provide the "links" between two relations.
    A relation's foreign key can refer to the same relation.
    EXAMPLE: EMP ( EMP#, SALARY, MGR_EMP#, ... )
    EMP# is the primary key MGR_EMP# is the foreign key
    EMP is a "self-referencing relation".

    THE REFERENTIAL INTEGRITY RULE
  • The database must not contain any unmatched foreign key values.

    REFERENTIAL INTEGRITY RULE



    EXAMPLE: The three 3NF relations are:
    SP(S#,P#,QTY)
    SC(S#,CITY)
    CS(CITY,STATUS)

    The referential diagrams are:
    SP ---S#---> SC ---CITY---> CS
    DELETE INTEGRITY RULE:
    We should not delete (S5,London) from SC if S5 is present in SP.
    INSERT INTEGRITY RULE:
    We should not insert (S3,P2,200) into SP unless S3 is present in SC.

    (Please study these integrity rules in light of the "update anomalies" for non-3NF relations)