Showing posts with label Codd's 12 Rules. Show all posts
Showing posts with label Codd's 12 Rules. Show all posts

Codd's Twelve Rules - Rule 12 - Non-Subversion Rule

Codd's Twelve Rules - Rule 12 - Non-Subversion Rule

Rule 12
Non-Subversion Rule
Rule
If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.
Description
“This rule states that if a Relational Database Management System has an interface that provides access to low level records, this interface then must not be able to subvert the system and bypass security and integrity constraints.”
“The system must not have features that allow you to subvert database structure integrity. Basically, the system must not include back doors that let you cheat the system for features such as administrative privileges or data constraints.”
“There should be no way to modify the database structure other than through the multiple row database language (like SQL).”
Overall, there should not be a way in any form to violate the integrity constraints defined on a database. Only the language/sublanguage which was used to define those constraints can be able to redefine them. If we would say that a low level language is supported by a RDBMS, then it should not be possible for that low level language to bypass any integrity constraints defined in a high level language.
Example violations of this rule
SQL Server 2008 violates this through Bulk copy and Disabling constraints and triggers options.

Some DBMS that fulfills this property
SQL Server, Oracle


Go to Home - Codd's Twelve Rules


Codd's Twelve Rules - Rule 11 - Distribution Independence

Codd's Twelve Rules - Rule 11 - Distribution Independence Rule



Rule 11
Distribution Independence
Rule
The data manipulation sub-language of a relational DBMS must enable application programs and terminal activities to remain logically unimpaired whether and whenever data are physically centralized or distributed.
Description
The distribution of the parts of any database to different sites/locations should be invisible to the end users. That is, like in distributed database it should give a centralized effect to the end users who access it.
Also, the existing applications are able to continue their operations when the database is distributed or redistributed.
This way of execution provides parallelism in handling transactions.
Example
Oracle
CREATE TABLE sales_hash(salesman_id  NUMBER(5), salesman_name VARCHAR2(30), sales_amount  NUMBER(10), week_no       NUMBER(2)) PARTITION BY HASH(salesman_id) PARTITIONS 4 STORE IN (ts1, ts2, ts3, ts4);
The table sales_hash will be created with 4 partitions on salesman_id values, and partitions will be stored in tablespaces ts1, ts2, ts3, and ts4.
MySQL
CREATE TABLE Emp(Eid INT, EName VARCHAR(30), Salary INT, Dno INT) ENGINE = INNODB PARTITION BY HASH(Dno) PARTITIONS 6;
The above statement creates a table Emp with 6 partitions on Dno attribute values.
Some DBMS that fulfills this property
SQL Server, Oracle, MySQL, IBM DB2


Go to Home - Codd's Twelve Rules



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


Codd's Twelve Rules - Rule 9 - Logical Data Independence


Codd's Twelve Rules - Rule 9 - Logical Data Independence



Rule 9
Logical Data Independence
Rule
“The ability to change the conceptual (logic) schema without having to change the next higher level external schema or application programs”.
“Insulation of application programs and terminal users from the negative effects of information-preserving changes of the logical database schema”.
Description
1. The addition or removal of new entities, attributes, or relationships to the conceptual schema should be possible without having to change existing external schemas or having to rewrite existing application programs.
2. Logical Data independence means if we add some new columns or remove some columns from table then the user view and programs should not change.
3. The term "Logical Data Independence" refer to the ability to present the stored information in different ways to different users. The way you store the data and the way you present it to a particular user is independent. Different users perceive the same data differently.
Example
If user B add a new column salary in his view/table then it will not effect the external view user; user A, but internal view of database has been changed for both users A & B. Now user A can also print the salary. It means if we change in view then program which use this view need not to be changed.
For a table with the schema Employee(Eno, Ename, Street, City, Salary), a view with the attributes (Eno, Ename) will not be affected if any other attributes of Employee is altered.
More examples/illustrations can be tried in the following links;

Achievement
Logical Data Independence is more difficult to achieve when compared to Physical Data Independence since application programs are heavily dependent on the logical structure of the data that they access.
Some DBMS that fulfills this property
Almost all Relational DBMSs support this property with certain limits (say for example if columns or tables removed).
Together, Rule 8 Physical Data Independence and Rule 9 Logical Data Independence, specify that specific access or storage techniques used by the RDBMS—and even changes to the structure of the tables in the database—shouldn’t affect the user’s ability to work with the data.


Wikipedia

Search results