Entity and Referential Integrity

The primary key uniquely identifies a specific tuple from a relation instance. It also links relations together. The relational model incorporates two application-independent integrity rules called entity integrity and referential integrity to ensure these purposes are properly served.

Entity integrity simply requires that no tuple in a relation instance can have NULL (i.e., undefined) values for any of the primary key attributes. This property guarantees that the value of the primary key can uniquely identify each tuple.

Referential integrity involves references from one relation to another. This property can be understood in context of the EMPLOYEE relation by assuming that there is a second relation with the scheme:

          DEPARTMENT (DEPT, LOCATION, PHONE NUMBER)

DEPT is the primary key of DEPARTMENT. The DEPT attribute of the EMPLOYEE relation is said to be a foreign key from the EMPLOYEE relation to the DEPARTMENT relation. In general, a foreign key is an attribute, or set of attributes, in one relation R₁, whose values must match those of the primary key of a tuple in some other relation R₂. R₁ and R₂ need not be distinct. In fact, because supervisors are employees, the SUPERVISOR attribute in EMPLOYEE is a foreign key with R₁ = R₂ = EMPLOYEE.

Referential integrity stipulates that if a foreign key FK of relation R₁ is the primary key PK of R₂, then for every tuple in R₁ the value of FK must either be NULL or equal to the value of PK of a tuple in R₂. Referential integrity requires the following in the EMPLOYEE example:

•  Because of the DEPT foreign key, there should be tuples for the Electrical Engineering, Computer Science and Administration departments in the DEPARTMENT relation.

•  Because of the SUPERVISOR foreign key, there should be tuples for Jones, Brown and Black in the EMPLOYEE relation.

The purpose of referential integrity is to prevent employees from being assigned to departments or supervisors who do not exist in the data base, though it is all right for employee Black to have a NULL supervisor or for an employee to have a NULL department.

SQL

Every data base management system (DBMS) needs a language for defining, storing, retrieving, and manipulating data. SQL is the de facto standard in relational DBMSs. SQL emerged from several projects at the IBM San Jose (now called Almaden) Research Center in the mid-1970s. Its official name now is Data Base Language SQL.

An official standard for SQL has been approved by the American National Standards Institute (ANSI) and accepted by the International Standards Organization (ISO) and the National Institute of Standards and Technology as a Federal Information Processing Standard. The standard has evolved and continues to do so. The base standard is generally known as SQL’89 and refers to the 1989 ANSI standard. SQL’92 is an enhancement of SQL’89 and refers to the 1992 ANSI standard. A third version SQL, commonly known as SQL3, is being developed under the ANSI and ISO aegis.

Although most relational DBMSs support some dialect of SQL, SQL compliance does not guarantee portability of a data base from one DBMS to another. This is true because DBMS vendors typically include enhancements not required by the SQL standard but not prohibited by it either. Most products are also not completely compliant with the standard.

The following sections provide a brief explanation of SQL. Unless otherwise noted, the version discussed is SQL’89.

The CREATE Statement

The relation scheme for the EMPLOYEE example, is defined in SQL by the following command:

    CREATE     TABLE          EMPLOYEE
               (NAME          CHARACTER      NOT NULL,
               DEPT           CHARACTER,
               RANK           CHARACTER,
               OFFICE         CHARACTER,
               SALARY         INTEGER,
               SUPERVISOR     CHARACTER,
               PRIMARY KEY    (NAME),
               FOREIGN KEY    (DEPT)         REFERENCES DEPARTMENT,
               FOREIGN KEY    (SUPERVISOR)   REFERENCES EMPLOYEE)

This statement creates a table called EMPLOYEE with six columns. The NAME, DEPT, RANK, OFFICE, and SUPERVISOR columns have character strings (of unspecified length) as values, whereas the SALARY column has integer values. NAME is the primary key. DEPT is a foreign key that references the primary key of table DEPARTMENT. SUPERVISOR is a foreign key that references the primary key (i.e., NAME) of the EMPLOYEE table itself.

INSERT and DELETE Statements

The EMPLOYEE table is initially empty. Tuples are inserted into it by means of the SQL INSERT statement. For example, the last tuple of the relation instance previously discussed is inserted by the following statement:

   INSERT
   INTO     EMPLOYEE(NAME, DEPT, RANK, OFFICE, SALARY, SUPERVISOR)
   VALUES   VALUES(‘Black’, ‘Administration’, ‘Dean’, ‘ST101’, 60000, NULL)

The remaining tuples can be similarly inserted. Insertion of the tuples for Brown and Jones must respectively precede insertion of the tuples for Kaplan and Rao, so as to maintain referential integrity. Alternatively, these tuples can be inserted in any order with NULL managers that are later updated to their actual values. There is a DELETE statement to delete tuples from a relation.