Learn online DBMS “Relational Model Constraints” Complete Lesson 6

Relational Model Constraints

Constraints are restrictions on the values stored in a database based on the requirements. For example, in the relation EMPLOYEE, the Employee_ID must be a 4-digit number, the Date_of_Birth must be such that the age of employee should be >= 18 and <=65.

Following are the various types of constraints in Relational Model:

1. Domain Constraint–It specifies that the value of every attribute in each tuple must be from the domain of that attribute. For example, the Employee_ID must be a 4-digit number. Hence a value such as “12321” or “A234” violates the domain constraint as the former is not 4-digit long and the latter contains an alphabet.

2. Key Constraint – This constraint specifies that no two tuples in any relation should be identical. In context of this constraint, superkeys, keys, candidate keys and primary keys are used. Let us discuss them in detail:

1. Superkey is a set of attributes in a relation for which no two tuples in a relation state have the same combination of values. Every relation must have at least one superkey which is the combination of all attributes in a relation. Thus for the EMPLOYEE relation, following are some of the superkeys:
2. {Name, Employee_ID, Gender, Salary, Date_of_birth} – defaultsuperkey consisting of all attributes.
3. {Name, Employee_ID, Date_of_Birth}
4. {Employee_ID, Gender, Salary}
5. {Name, Employee_ID, Gender}
6. {Employee_ID} etc.

However {Gender, Salary} is not a superkey because both these attributes have the same values for Employees Neha and Himani.

1. Key is the minimal superkey which means that it is that superkey of a relation from which if more attribute(s) are removed then it no longer remains a superkey. For example the superkey {Name, Employee_ID, Gender} is not a key as we can remove Name and Gender from this combination and then what is left {Employee_ID} is still a Superkey. Now {Employee_ID} is a key as it is a superkey as well as no more removals are possible. A relation may have more than one key. Consider the relation PERSON with the following schema: PERSON (Aadhar_number, PAN, Voter_ID_cardno, Name, Date_of_birth, Address). This relation has three keys namely : { Aadhar_number}, { PAN }, { Voter_ID_no } as every individual in India has a unique  Aadhar card number, PAN as well as Voter ID card number.

• Candiate key is the name given to all the keys of the relation. For example, the PERSON relation has three candidate keys as discussed above.

1. Primary Key is the name given to one of the candidate keys of a relation. Primary key is used to identify tuples in a relation. If a relation has many candidate keys it is preferable to choose that one as primary key which has least number of attributes. Primary key are usually underlined in the schema of the relation. For example in the relation schema:PERSON (Aadhar_number, PAN, Voter_ID_cardno, Name, Date_of_birth, Address), Aadhar_number is the primary key.

The relation between Superkey, Key, Candidate key and Primary key can be explained with the help of following Figure.

1. Null value Constraint–Sometimes it is required that certain attributes cannot have null values. For example, if every EMPLOYEE must have a valid name then the “Name” attribute is constrained to be NOT NULL. 

2. Entity Integrity constraint–This constraint specifies that primary key of a relation cannot have null value. The reason behind this constraint is that we know primary key contains no duplicates. However if we allow null values for a primary key then there can be multiple tuples for which primary key is having null values. Now that implies we are allowing duplicate values (NULL) for a primary key which itself violates the definition of primary key.

3. Referential Integrity constraint – This constraint is specified between two relations. Before defining this constraint let us study the concept of foreign keys

Foreign key in a relation R1 is the set of attributes in R1 that refers to Primary key in relation R2 if the domain of foreign key attributes is same as that of Primary Key attributes and the value of foreign key either occurs as a value of primary key in some tuple of R2 or is NULL.

Thus R1 is called as the referencing relation and R2 as referenced relation and a referential integrity constraint holds from R1 to R2.

The main purpose of this constraint is to check that data entered in some relation is matching with the data entered in some other relation. For example consider two relation schemas:

Department (Dept_Name, Dept_ID, No_of_Teachers)

Teacher (Teacher_Name, Teacher_ID, Dept_ID, Subject)

Following are the primary keys (Underlined above):

• Dept_ID is the primary key of Department relation.
• Teacher_ID is the primary key of Teacher relation.

Now you may notice thatDept_ID is present in both the relations. The reason is that every teacher belongs to a particular department. Instead of storing the complete department details we have only taken Dept_ID which is the primary key of Department relation. Now that means Dept_ID of Teacher relation must have a value that exists in Dept_ID attribute of Department relation or it can be NULL in case a teacher belongs to a department that has not yet been created.

This means Dept_ID of Teacher relation is a foreign key that references to primary key of Department relation (Dept_ID).

Also note it is not a compulsion to have same name for foreign key as of the corresponding referenced primary key. The above two schemas can also be written as follows:

Department (Dept_Name, Dept_ID, No_of_Teachers)

Teacher (Teacher_Name, Teacher_ID, Dept_No, Subject)

Where Dept_No is the foreign key that references Dept_ID of Department relation.

A foreign key can also refer to its own relation. For example suppose we have to create a database of all residents in a colony along with any one of their neighbors. Consider the following relation:

Residents(Name, RID, Block_no, House_no, Floor, Neighbor_RID)

The Primary key of this relation is RID. Now in order to store information about neighbor we have created a foreign key Neighbor_RID that references RID of Residents. Note that the referencing and referenced relation both are same in this case.