Codd's rules

Rule 1: The Information Rule

Every piece of data that we permanently store in a database is located in a table. In general, SQL Server fulfills this rule, because we cannot store any information in anything other than a table.

Rule 2: Guaranteed Access Rule

Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name. This rule stresses the importance of primary keys for locating data in the database. The table name locates the correct table, the column name finds the correct column, and the primary key value finds the row containing an individual data item of interest. We can also access data by any of the columns in the table, though we aren’t always guaranteed to receive a single row back.

Rule 3: Systematic Treatment of NULL Values

NULL values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational RDBMS for representing missing information in a systematic way, independent of data type. This rule requires that the RDBMS support a distinct NULL placeholder, regardless of data type.  NULLs are distinct from an empty character string or any other number, and they are always to be considered as unknown values.

Rule 4: Dynamic Online Catalog Based on the Relational Model

This rule requires that a relational database be self-describing. In other words, the database must contain certain system tables whose columns describe the structure of the database itself. This rule is becoming more of a reality in each new version of SQL Server, as with the implementation of the INFORMATION_SCHEMA system views.

Rule 5: Comprehensive Data Sublanguage Rule

This rule mandates the existence of a relational database language, such as SQL, to manipulate data. T-SQL fulfils this function for SQL Server and carries out all the data definition and manipulation tasks required to access data.

Rule 6: View Updating Rule

This rule can be technically fulfilled using INSTEAD OF triggers, but in what can be a less-than-straightforward manner. You need to take care when considering how to apply updates, especially if the view contains a GROUP BY clause and possibly aggregates.

Rule 7: High-Level Insert, Update, and Delete

This rule stresses the set-oriented nature of a relational database. It requires that rows be treated as sets in insert, delete, and update operations. The rule is designed to prohibit implementations that support only row-at-a-time, navigational modification of the database. The SQL language covers this via the INSERT, UPDATE, and DELETE statements.

Rule 8: Physical Data Independence

Applications must still work using the same syntax, even when changes are made to the way in which the database internally implements data storage and access methods. In fact, users of the data need only be able to get the basic definition of the data they need.

Adding indexes, changing the filegroup of an object, using partitioning:

Rule 9: Logical Data Independence

Rule 10: Integrity Independence

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. The database must support a minimum of the following two integrity constraints:

• Entity integrity: No component of a primary key is allowed to have a NULL value.

• Referential 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.

Rule 11: Distribution Independence

This rule says that the database language must be able to manipulate data located on other computer systems.  In essence, we should be able to split the data on the RDBMS out onto multiple physical   systems without the user realizing it.

Rule 12: Non-Subversion Rule

If a relational system has or supports a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules or constraints expressed in the higher-level (multiple-records-at-a-time) relational language. This rule requires that alternate methods of accessing the data are not able to bypass integrity constraints. However, SQL Server 2008 violates this rule in two places:

• Bulk copy: By default, you can use the bulk copy routines to insert data into the table directly and around the database server validations.

• Disabling constraints and triggers: There’s syntax to disable constraints and triggers, thereby subverting this rule.

Disadvantages of traditional file approach

·         Data security

·         Data redundancy

·         Data isolation

·         Program /data dependence

·         Lack of flexibility

·         Concurrent access anomalies