Relational Model Defined By Codds Twelve Rules Computer Science Essay

This report tries to explain what Codds Twelve Rules means. And by comparing MySQL with relational model as defined by Codd's Twelve Rules, this report also gives an abstract view how MySQL comply with Codd's Twelve Rules. This report is based on MySQL 5 InnoDB engine.

Edgar F. Codd is well-known for his contribution to relational model of database in 1970s. However, in 1980s the term "relational" was used by many database vendors to spell it out their database products which might not adhere to the model that Edgar F. Codd has proposed. In order to clarify his style of relational database, and provide people a straightforward standard that can indicate to what extent a database software conforms to his model, the Codd's Twelve Rules were propose.

There are 13 rules in Codd's Twelve Rules. Our textbook omits the first one, 'rule 0', which means this report begins from the second one in Codd's rules, 'rule 1'.

Rule 1: THE INFO Rule

This rule requires all data in relational database management system(RDBMS) should be stored as values in tables at logical level. Some DBMS use Key-Value to store data, 'Redis' for example, which contradict the info Rule, so these DBMS will never be regarded as relational DBMS.

MySQL dose store all data by means of tables with values in columns of rows. Users can only usage of values that are stored in tables. Even the info descript the database itself is store in tables, i. e. table 'tables' in 'Information schema' stores the description of all the tables which have been created. So, MySQL meets the necessity of rule 1.

Rule 2: The Guaranteed Access Rule

Users must be able to usage of values by providing table name, the value of primary key and the name of the columns. In another word, the DBMS should support primary key in tables and enforce each tables contains primary key in order to avoid data duplication.

MySQL does support to define primary type in tables. Yet, users can also create tables that don't possess it. For example, create one table has columns 'a' and 'b' without primary key. In that circumstance, there may be several rows that gets the same value in column 'a', preventing users to access to the worthiness of column 'b' in the row he want. So, MySQL will not fulfill the requirement of Rule2 and it offers user more flexibility by accepting tables without primary key.

Rule 3: Systematic Treatment of NULL Values:

The database must support 'NULL' as a value other than '0' or 'empty string', as a representation of "data missing or inapplicable". And the database provides systematic way to manipulate NULL value.

MySQL fulfill this requirement by supporting NULL value and treat it in a systematic way. In MySQL, 'NULL' is supported and is undoubtedly missing data following ANSI/ODBC SQL standard. MySQL implements ternary logic. Users can not compare values with NULL, even NULL with NULL by using '=', because NULL is missing data. The results of these compares are 'unknown'. MySQL provides 'IS NULL' and 'IS NOT NULL' statement in order to take care of the compares with value 'NULL'.

Rule 4: Active online catalog predicated on the relational model

Data dictionary of one DBMS should be stored as ordinary data by means of tables. Authorized users must be able to using the query language (SQL for example) that they used to query ordinary data to access to database catalog or structure.

MySQL stores database catalog data using tables --- the same way it store ordinary data. These tables are in system database such as 'Information_schema'. For instance, table 'tables' in 'Information_schema' contains information about all tables in MySQL, like 'TABLE_NAME', 'TABLE_TYPE'. Authorized users may use SQL to query this table in order to gain access to to data catalog of current tables. So, MySQL well implements this Rule.

Rule 5: Comprehensive data language

The DBMS must support at least one language you can use directly by users or within application queries. This language must also supports all areas of database use including data (view) definition, data manipulations, integrity constraints, securities and transaction managements. SQL is a language that is comprehensive enough to support each one of these requirements. So, any DBMS that implements ANSI/ODBC SQL will comply with this rule.

MySQL follows the ANSI/ODBC SQL standard, yet there are several dissimilarities between them in several cases. The difference is seen in documents of MySQL. All these differences are just about statement syntax, i. e MySQL doesn't support 'select into table', users should using 'Insert into select' to do the same works. But after all, all database use within MySQL can be implemented by using SQL regardless of whether the syntax differs from standard SQL. So, MySQL fulfills Rule 5.

Rule 6: View Updating

This rule means that the alteration that user makes in a view will cause the alteration of tables from which the view is created, if this view is theoretically updatable.

In MySQL, many theoretically updatable views can be updated, yet, there a wide range of limits. For instance, due to the documentation of MySQL, 'delete' and 'update' cannot be used to update a view that has several underlying table. So, MySQL does not fulfill this rule.

Rule 7: The RDBMS may handle individual records but it must mainly handle sets of records

This rule means users can use one single command to query, insert, delete and update sets of values in multiple rows or multiple tables.

MySQL are designed for procedure of multiple rows in a single table. Because it uses SQL, that has commands that can handle operation of sets of records, as its data language. For example, MySQL can insert multiple records with this statement, 'INSERT INTO table_name (a, b, c) VALUES (3, 4, 5), (6, 7, 8)'. But MySQL cannot handle procedure of sets of records that are from different tables in one command. But users can also handle this problem by using transaction that containing some SQL commands. So, MySQL implements this rule by allowing user to operate command on multiple rows in a single table, while does not support operation of multiple tables in single command.

Rule 8: Physical Data Independence

This rule means that alterations that have been made to database in physical level, for example, export one database, and open it in another computer won't lead to the changes in logical level. And users can still access to the data without altering their commands.

MySQL can export one database by creating 'back up' file. This file can be restore by MySQL in another computer. The physical underlying of this database has changed while the table structure will never be changed and users can access to this restored one without any adjustment on the queries. So, MySQL does provide some degree of physical data independence in InnoDB engine. However, if users want to change the store engine of any table from transactional someone to non-transactional, the logical level will also change. In sum, MySQL provide physical data independence in InnoDB engine, but changing the store engine may cause change in database logic.

Rule 9: Logical Data Independence

This rule means that the changes of logical level in the database won't lead to changes of queries that predicated on former structure. For example, users can split one table into two, while use the same query as before.

In MySQL, adding columns to a table will not require changes in application or queries that are base on the structure of the table. However, other changes of logical level, such as incorporate two tables into one, may call for a modification of the application form predicated on the structure. So, MySQL does not adhere to this Logical Data Independence rule.

Rule 10: Integrity Independence

This means that integrity definition of data in a single DBMS should be thought to be one part of data dictionary, and be stored in the same form as ordinary data. This also requires that this integrity definition can be access by users using language, SQL for example, to query, define or alter the integrity independence.

MySQL fulfills this rule. It stores data dictionary in tables in 'information schema'. For example, the column 'COLUMN_KEY' in the table 'COLUMNS' defines whether this column is primary key or has other constraints. And 'KEY_COLUMN_USAGE' table defines which key columns have constrains. Users can access to integrity definition data by query these tables using ordinary SQL statement.

Rule 11:Distribution Independence

Today, many DBMS introduce the function to using distribute data in several locations. However, for this reason rule, where this data be distributed and exactly how DBMS manage them should not be obvious to users. Users can use the data just as as they use data that been stored in one place.

The InnoDB engine will not provide the ability to store data in different locations. MySQL has a distributed engine called MySQL Cluster. In InnoDB engine, MySQL introduce XA Transaction which is dependant on X/Open XA specification since 5. 0. 3. This specification provides users the ability to employ multiple resources in one transaction. However, users got to know the underlying works, of course, if the structure of the distributed DBMS changes, the XA Transaction statement could also need to be adjusted. So, MySQL does not comply with the rule 11.

Rule 12: The Nonsubversion Rule

Sometime the DBMS provide API or other low-level interface for users to handle complicated transactions. However, those interfaces must not break all the rule above and bypassing integrity constraints and security.

MySQL provides APIs for different applications or programming languages as low-level interface. You will discover back doors in them, custom command 'SHOW' for example. However, these backdoors are only maintained for the compatibility with the former edition.

Summary

In Sum, due to the comparison between MySQl and Codd's rules, MySQL implement almost all of these rules, though there are still some limitations. It can be seen as a DBMS that is relational.

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.