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