1. The information rule:
All information in a relational database is represented at a logical level by values in a tabular form.
This means: Form a table.
In SQL: We form a table.

2. Guaranteed access rule:
Every field value in a relational database is logically accessible by table name, primary key name, and column name.
This means: A unique primary index.
In SQL: This is not always done because the index column may be NULL which means that there is no primary key value.

3. Systematic treatment of missing information:
Indicators representing the fact that unknown information is supported. The indicators are independent of the data type.
This means: You can have fields of unknown value.
In SQL: Fields with unknown values are called NULL fields.

4. Online catalog is based on relational model:
Data description and control information is represented at the logical level, just like data in a table format.
This means: System information is in a table format.
In SQL: The system catalogs can be accessed as tables with SQL.

5. Comprehensive data sublanguage:
A language which supports data definition, data manipulation, view manipulation, integrity constraints, authorization, and transaction boundaries must be supported by the DBMS.
This means: One sublanguage to do all of these things.
In SQL: This is done using only SQL.

6. View updating rule:
The DBMS system should be able to determine if a view can update base tables.
This means: Views should be able to update data.
In SQL: If the view is of one table, then it can update the data. If the view is of multiple tables, then the view can not update the data.

7. Set level insert, update, delete:
The ability to operate on multiple rows with one command.
This means: You can do multiple row retrievals and updates.
In SQL: You can do multiple row retrievals and updates.

8. Physical data independence:
Physical changes to the database should not affect existing applications. This includes index modification and moving data sets to a different volume.
This means: A physical change to the structure of data storage does not affect the end user.
In SQL: The SQL system remains online while these things are being changed.

9. Logical data independence:
Existing applications are unaffected when the database is changed logically (i.e., adding new tables or adding new columns).
This means: If a table is expanded or an index is changed, the application program is unaffected.
In SQL: The addition of new data or indices does not affect old programs.

10. Integrity independence:
The DBMS must support entity and referential integrity. The constraints must be defined by the sublanguage and stored in the online system catalog.
This means: Only certain ranges of values are allowed in the data.
In SQL: This feature, called field domain, is generally not supported.

11. Distribution independence:
Applications and terminal activities remain logically unimpaired when data distribution is intruduced.
This means: Only certain ranges of values are allowed in the data.
In SQL: Distributed databases were implemented with the release of DB2 release 2.2 in 1989.

12. Nonsubversion rule:
Integrity rules and constraints can not be bypassed by any tool or non-relational language.
This means: Only one language, SQL, can update the data.
In SQL: You can update only with SQL. You can’t update any other way.

We have seen in non-RDBMS databases that you must know the relationship of the data in order to retrieve the data properly. These relationships are maintained by hidden pointers and references in the data. These pointers are created when the database is defined and loaded. If a field is added or a relationship changes, then the database must be reloaded and all programs using the records must be changed, even though the program does not use the new fields.RDBMS has none of these problems. You can add columns or use a new relationship at any time. Operators, such as JOIN, are used to compare actual values in the tables to establish relationships. You can tell your RDBMS what you want and the system will determine how to retrieve the data. This is called “data independence.”

Data integrity is the consistency of data. The row(s) that are used for the primary key must have a value. This is called “Entity Integrity.” The row(s) that maintain relationship consistency between tables (foreign keys) are for “Referential Integrity.” Referential integrity requires that every foreign key value in one table must have a matching primary key value in the corresponding table or have a null (not used) value.For example, you may not allow a person to be added to the payroll table until that person has been entered into the employee table first.

A table “union” means that the rows of one table are combined under the rows of another table. Unions are rarely used.

Table A:

Table B:

Result table of a union:

The definition (data type) and number of columns in the first table must exactly match the definition and number of columns in the second table. Since the source tables may have different column names, the result table has no column names.

A table “join” means that data from two or more tables can be used to create a single result table. The join makes an RDBMS a very powerful tool.

EMPLOYEE_TBL:

 

PAYROLL_TBL:

Joined Result Table:

Table “selection” means that all or some of the table rows can be retrieved into the result table.

Selected Result Table:

Table “projection” means that all or just some of the table columns can be retrieved into the result table.

Projected Result Table: