1 / 19

INTEGRITY

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.

tao
Download Presentation

INTEGRITY

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INTEGRITY

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

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

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

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

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

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

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

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

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

  11. Example GStudent (ID,NAME) SQL> create table GStudent (id varchar2(10), name varchar2(30) not null, constraint gStuPk primary key (id));

  12. SQL> connect system/manager@tori Connected. SQL> desc dba_constraints Name Null? Type ----------------------------------------- -------- ---------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE SQL> select constraint_name, constraint_type from dba_constraints where owner = 'A' CONSTRAINT_NAME C -------------------------------------------- GSTUPK P SYS_C001127 C Oracle example

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

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

  15. WHY INTEGRITY is Importance?

  16. IN DATABSE Design should consider • Foreign key can be NULL? • Answer depend on the Business Rule

  17. Insert • DEPENDENT • AUTOMATIC • NULLIFY • DEFAULT

  18. Delete (Refer to others) • Restrict • Cascade • Nullified • Default

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

More Related