Advanced Database Management System - Tutorials and Notes: Codd's Twelve Rules - Rule 10 - Integrity Independence

Saturday, 28 February 2015

Codd's Twelve Rules - Rule 10 - Integrity Independence

Codd's Twelve Rules - Rule 10 - Integrity Independence


Rule 10
Integrity Independence
Rule
Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.
A minimum of the following two integrity constraints must be supported:
1. Entity integrity: No component of a primary key is allowed to have a null value. That is, no records can have NULL values in its Primary Key attribute.
2. Relational integrity: For each distinct non-null foreign key value in a relational database, there must exist a matching primary key value from the same domain. In other words, if a foreign key cannot have null values as its component then it must refer a matching primary key value with the same set of permitted values to accept any new records.
Description
This rule insists that the declaration of integrity constraints must be part of the language that is used to define the database structure. Also, these integrity constraints should be stored as part of Data dictionary.
For example, if you define a new table in Oracle using SQL, then SQL must provide facilities to define the integrity constraints. These integrity constraints are stored as part of SYSTEM tablespace.
Example
(Green color - primary key, red color - foreign key)
CREATE TABLE Emp(Eno CHAR(5) PRIMARY KEY, Ename VARCHAR(25), Phone NUMBER(10), dno NUMBER(3), FOREIGN KEY dno REFERENCES Dept(dno));
In this definition PRIMARY KEY means UNIQUE + NOT NULL, and we have defined PRIMARY KEY as part of table definition itself using SQL.
Secondly, if we have attribute dno as a NON-NULL foreign key, then there should be a table definition like the one follows and dno should refer the PRIMARY KEY;
CREATE TABLE Dept(Dno NUMBER(3) PRIMARY KEY, Dname VARCHAR(35), Dlocation VARCHAR(30), Phone NUMBER(10));
Some DBMS that fulfills this property
SQL Server, Oracle, MySQL, IBM DB2



Go to Home - Codd's Twelve Rules


No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents