Dr. Codd was a mathematician who proposed a new model for a database , which is known to us as Relational Database Management System. He came up with twelve rules which every true relational database should follow . Let’s go through these and if you have a hands-on-experience with databases. See how much have you followed Dr. Codd.
Zero Rule :
A system must be able to manage database entirely through the relational capabilities.
The data stored in a database, may it be user data or metadata, must be a value of some table cell. Everything in a database must be stored in a table format in rows and columns
Guaranteed Access Rule
Every single data value is guaranteed to be accessible logically with a combination of table-name, primary-key (row value), and attribute-name (column value). No other means, such as pointers, can be used to access data.
Systematic Treatment of NULL Values
The NULL values in a database must be given a systematic and uniform treatment. This is a very important rule because a NULL can be interpreted as one the following − data is missing, data is not known, or data is not applicable.
Active Online Catalogue
The structure description of the entire database must be stored in an online catalog, known as data dictionary, which can be accessed by authorized users. Users can use the same query language to access the catalog which they use to access the database itself.
Comprehensive Data Sub-Language Rule
A database can only be accessed using a language having linear syntax that supports data definition, data manipulation, and transaction management operations. This language can be used directly or by means of some application. If the database allows access to data without any help of this language, then it is considered as a violation.
View Updating Rule
All the views of a database, which can theoretically be updated, must also be updatable by the system.
High-Level Insert, Update, and Delete Rule
A database must support high-level insertion, updating, and deletion. This must not be limited to a single row, that is, it must also support union, intersection and minus operations to yield sets of data records.
Physical Data Independence
The data stored in a database must be independent of the applications that access the database. Any change in the physical structure of a database must not have any impact on how the data is being accessed by external applications.
Logical Data Independence
The logical data in a database must be independent of its user’s view (application). Any change in logical data must not affect the applications using it. For example, if two tables are merged or one is split into two different tables, there should be no impact or change on the user application. This is one of the most difficult rule to apply.
A database must be independent of the application that uses it. All its integrity constraints can be independently modified without the need of any change in the application. This rule makes a database independent of the front-end application and its interface.
The end-user must not be able to see that the data is distributed over various locations. Users should always get the impression that the data is located at one site only. This rule has been regarded as the foundation of distributed database systems.
If a system has an interface that provides access to low-level records, then the interface must not be able to subvert the system and bypass security and integrity constraints.
When I was in university taking up DBMS1 , these 12 rules were such a pain and they still are , but with time I realised just how important these are for designing a database. So , if you’re a student and you can’t memorise them like me? It’s not end of the world.Practice and study they will become part of your database memory!
Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows !
Happy Reading and Learning!