Let us understand what are Codd’s 12 rules in DBMS and RDBMS and let us also see the explanation of each and every Codd’s Rules in DBMS with examples.
What is a Database?
A database is essentially a collection of different database elements such as tables, rows, columns which are used to store data in such a manner that it is easy to retrieve and manipulate the data in the database.
A Database Management System is primarily a collection of interrelated data and a set of programs used to access the data.
A Relational Database Management System is similar to a DBMS but it works on a relational model which is way different from traditional DBMS.
Before you go ahead with these 12 Codd’s Rules in DBMS, you must understand the Difference Between DBMS and RDBMS.
How Do Codd’s 12 Rules Help?
These rules have been developed by Edgar Frank Codd, who is an expert in relational model databases, is also popularly known as the father of database management system.
According to his theory, a DBMS can be considered as an RDBMS only if it satisfies the Codd’s 13 rules. A DBMS should be compliant with these ACID properties as well.
However, there is no single database management system that satisfies all the E.F. Codd’s 12 Rules in RDBMS.
There is one DBMS, dataphor, which is a truly relational database management system but there are a lot of controversies around it.
In addition, almost all the DBMS satisfies some or the other Codd’s 12 Rules.
Let’s see how the DBMS systems are segregated based on the number of Codd’s rules they follow:
- If a database follows 5 – 6 rules of Codd in Oracle, then it can be regarded as a DBMS.
- For a database application that obeys 7 – 9 of Codd’s rules in DBMS, then it is considered as Semi – RDBMS.
- If a database system implies 9 – 13 Codd’s rules in RDBMS, then it qualifies to be considered as a truly relational DBMS.
Note: There is a total of 13 Codd Rules in DBMS. Since the rules start from 0, it comes to a total of 12 Codd rules.
E.F. Codd’s 12 Rules
Rule 0: Foundation Rule
The foundation rule states that a relational database management system must be able to use the relational model functionalities to organise, store, retrieve and manipulate the data.
This is the most important rule in all the Codd’s 12 Rules that every DBMS system should follow for it to be considered as a Relational DBMS.
Rule 1: Information Rule
The information rule states that all the information in a database should be stored in a table.
Therefore, any data that needs to be stored in the database, whether it is metadata or user data should be in table cells.
The tabular structure helps in maintaining relations between multiple tables and thereby, keeping all the data in a uniform manner.
The information rule, thereby, helps to retrieve and manipulate the data efficiently using relational database functionalities and query language.
Rule 2: Guaranteed Access Rule
As the name suggests, every single value or data present in the database must be accessible using a logical combination.
The use of pointers is strictly not allowed to refer to any field in a table. Each and every data value must be accessible without any ambiguity.
The atomic data values are, therefore, retrieved using a combination of primary keys, row-level data, tables and column names.
The guaranteed access rule also indicated that there is no redundant data in the database and is one of the most important Codd’s 12 Rules.
Rule 3: Systematic Treatment of Null Values Rule
The 3rd Codd’s rule states that the null values occurring in a database should be treated systematically and represented efficiently in the database.
Every RDBMS must support null values for representing missing data values or information, unknown data and inapplicable data within the database.
These null values are very different and have no connection with zero, an empty string or blank value in the table fields. However, this rule is not applicable to primary keys.
The null values are displayed in <Null> format in some of the RDBMS applications.
Rule 4: Dynamic Online Catalog based on the Relational Model Rule
The online catalog rule states that the database should provide access to its structure which is nothing but data dictionary.
A data dictionary consists of information that describes the format, structure and content of the database. It also includes the relationship between the tables and column values.
The access to the database structure should be provided using the same tools that are used to access the actual database using SQL.
Rule 5: Comprehensive Data Sublanguage Rule
The data sublanguage rule states that there should be at least one query language that enables the user to perform different functionalities within the database.
A relational database management system may be queried using different languages.
However, there must be one query language that can achieve all the functionalities such as following:
- Transaction statements such as commit, rollback
- View definition
- Database integrity
- Data manipulation
- Defining the data
- Retrieval of data
- Access authorization
Almost all the DBMS and RDBMS applications use one major query language which is Standard Query Language (SQL).
Rule 6: View Updating Rule
The view updating rule states that all the views in a database that are updatable in theory must be updated practically in the system as well.
A view is nothing but a virtual table based on the result set of an SQL statement. It does not store any data but contains rows and columns similar to a table.
These views are virtual tables used to provide multiple database users different views of the structure based on their authorization levels. It is, however, one of the most difficult rules to implement in a real-world scenario.
Rule 7: High-level Insert, Update and Delete Rule
The insert, update and delete rule states that every RDBMS system must support the operations for insertion, deletion and modification.
Any SQL query must be able to retrieve multiple rows and columns of data through a single query.
Every SQL query must be able to retrieve, store and update multiple rows and columns of data through a single query.
Therefore, an operation must be able to fetch or update multiple rows at a given point of time using the intersection, union and other functionalities.
Rule 8: Physical Data Independence Rule
It’s a common scenario that a database (which is a back-end system) is being used by a front-end application.
The physical data independence rule states that the data should not be dependent on the external applications that are using the database.
Any physical modifications to the structure of relational database must not interrupt the application that is accessing the database.
In other words, the architecture of the database and the end user application must be independent of each other. This rule focuses on the set-oriented nature of a relational database management system.
Rule 9: Logical Data Independence Rule
The logical data independence rule states that the data should be independent of the external application accessing the data. However, in the real world, this rule is difficult to implement.
Any logical changes that are done to the database should not affect or disrupt the application where the data is being accessed by the end user.
Usually, tables in a database are joined to each other to exhibit logical capabilities. This change should not affect the application.
Rule 10: Integrity Independence Rule
The integrity independence rule states that a relational database must maintain integrity constraints and should not allow any application or end user to violate these integrity constraints.
These integrity constraints must be stored in the online catalog or the data dictionary as a part of metadata.
The data integrity, in a relational database, is managed using the concept of referential integrity and is one of the most important Codd’s 12 Rules.
The integrity constraints ensure the consistency and accuracy in a relational database management system.
Here are some of the integrity constraints:
- Check constraint
- Primary key constraint
- Not null constraint
- Foreign key constraint
- Unique key constraint
The integrity constraints should be dependent on the existing applications that are accessing the database.
Rule 11: Distribution Independence Rule
The distribution independence rule states that the end user accessing the database application must not be able to find or view that the data is located in different locations.
Usually, the data is combined or assembled using different data centres located at multiple locations. All that he/she needs to see is the combined data from all the different locations.
This should not bother the end user and he/she should get an impression that the data is at one particular location.
Rule 12: Non Subversion Rule
Since the structured query language or SQL is a high-level database query language, it does not allow low-level data manipulation.
The non-subversion rule states that if a relational database system allows using a low-level language then there should not be any provision to manipulate the database structure.
This low-level language must not be able to bypass any integrity constraints and must follow the security guidelines.
Let us now see different RDBMS and the number of Codd’s rules followed by different databases.
- FoxPro database system follows a minimum of 7 Codd’s rules.
- Microsoft SQL Server follows around 11 Codd’s rules.
- Oracle database system follows 11 Codd’s rules as well.
Let’s discuss more on the E.F. Codd’s 12 rules in the comment section below. Let us know if you have any doubts or have any information to share.
For more information on E.F. Codd’s 13 rules, please check Wikipedia.
Just to add on the physical storage independence, this rule enables the database administrators to improve the performance of the system by doing some tweaks in the back-end servers without worrying about the change in underlying data in the front-end.
Can you please help me with getting Codd’s 12 rules PowerPoint pressentation?
Rule 10: You have mentioned “The integrity constraints should be dependent on the existing applications that are accessing the database.”
Isn’t the whole point of rule is integrity of RDBMS to be independent of accessing application? can you please clarify the above point.