Codd’s 12 RDBMS Rules and SQL Server

I was going through Codd’s 12 Rules (actually 13) and was pondering upon how SQLServer does against it. Here’s my view on same.

Rule 0: The system must qualify as relational, as a database, and as a management system.
i.e. For a system to qualify as a relational database management system (RDBMS), it system must use its relational facilities (exclusively) to manage the database.
Meets. Seldom something be written about it in SQLServer context.

Rule 1: Information Rule
i.e. All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables.
Data indeed held in tables, there are variables too, but they are session specific. Meets.

Rule 2: Guaranteed Access Rule
i.e. All data must be accessible. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row.
Primary Key is searched (which is unique for a table), and once row is identified, respective column value can be derived. Meets.

Rule 3: Systematic Treatment of NULL Values
i.e. The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.
Ideally, any operation working with NULL should lead to NULL. Though there were some issues in past due to which some SQLServer options are in place which decides how NULL is handled (instead of handling it the "definite" way).
- ANSI_NULLS: If OFF, then NULL = NULL is True for the comparison. If ON (the default), NULL = NULL returns UNKNOWN (ideal situation).
- CONCAT_NULL_YIELDS_NULL: If ON, NULLs are treated ideal way. e.g. NULL + <numValue> = NULL. If OFF, NULLs are treated in a nonstandard way such that NULL + <Value> = <Value>. (done for backward compatibility as per BOL, which says it all)
Somewhat meets if you are working with older SQLServer systems. Meets if you are working with default options.

Rule 4: Dynamic Online Catalog Based on the Relational Model
i.e. The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database’s structure (catalog) using the same query language that they use to access the database’s data.
INFORMATION_SCHEMA in SQLServer is somewhat implementing the same rule. There are other views in SYS which can provide more specific DB information. Meets.

Rule 5: Comprehensive Data Sublanguage Rule
i.e. The system must support at least one relational language that
- Has a linear syntax
- Can be used both interactively and within application programs,
- Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback).
TSQL meets the same requirement. Its important here that TSQL is not procedural like in case of Oracle. It simply provides an interface to dig out anything from SQLServer DBEngine. Meets.

Rule 6: View Updating Rule
i.e. All views that are theoretically updatable must be updatable by the system.
Somewaht meets. SQLServer isnt that great at handling updatable views, they have started to come a bit late recently. You can use INSTEAD OF triggers, but they look patch’y. Specifically handling views when they include aggregates is somewhat tricky.

Rule 7: High-Level Insert, Update, and Delete
i.e. The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
INSERT, DELETE and UPDATE covers this. More recent MERGE also works on same row-set handling principle. Though BCP does get around and works at somewhat at lower level, but thats a defined purpose and not default. Meets.

Rule 8: Physical Data Independence
i.e. Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.
TSQL interface is transparent to end user though system might be making use of Indexes, Partitions, Filegroups etc. Moreover, seperate OLEDB, Database (relational) and Storage Engine handles this rule well. Meets.

Rule 9: Logical Data Independence
i.e. Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.
This is not a very straight forward objective and require some best practice being followed at Development part. If you are calling for column names specifically instead of SELECT *, there isnt much in it. Moreover, views can be created on top of splitted tables which can be completely hidden from end user. Meets.

Rule 10: Integrity Independence
i.e. Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
NULL, PK and FK constraints are managed well inside the DBEngine. Triggers can be also be added on top of these constraints to provide with next steps. Meets.

Rule 11: Distribution Independence
i.e. The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully :
- when a distributed version of the DBMS is first introduced; and
- when existing distributed data are redistributed around the system.
Distributed transactions are just meant for that. Federated Servers is another such application of this rule.

Rule 12: Non-Subversion Rule
i.e. 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.
Though if we are within TSQL context, this rule is fairly preserved. But there are ways to work around with it using BCP and by disabling the constraints / triggers, its important for Dev/DBA to be aware of this shortfall when using these applications. Meets, but can breach as well.

Reference: Codd’s 12 rules

 

Vikas Rajput (http://vikasrajput.wordpress.com)