Page 254 - Information_Practice_Fliipbook_Class11
P. 254
Foreign Key: An attribute of table R1 that is part of primary key of table R2 is a foreign key of table R1.
1. Dept_No of EMPLOYEE table is a foreign key referencing to Dept_No of DEPARTMENT table.
How?
2. Mgr_Id of DEPARTMENT table is a foreign key referencing to ID of EMPLOYEE table. How?
9.2.3 Relational Model Constraints
The relational model allows us to define several constraints to ensure correct and consistent data in the database.
These constraints are called integrity constraints. We describe below integrity constraints for the relational model:
1. Entity Integrity Constraint: The DBMS ensures the integrity of data in the database. A tuple stored in the database
is uniquely identified by its primary key. So, no primary key attribute can have a NULL value. This is called entity
integrity constraint. Further, note that the database cannot have two tuples with an identical primary key value
because the DBMS distinguishes between the two tuples based on primary key only. For example, since the
Dept_No is the primary key of the DEPARTMENT table, therefore the following tuple cannot be added in
DEPARTMENT.
<NULL, "Sales", "Delhi", E0008>
Entity integrity constraint: No primary key attribute can have a NULL value.
2. Referential Integrity Constraint: If a table has a foreign key that refers to a UNIQUE attribute (typically primary key)
of another table, then the foreign key value should refer to an existing primary key value or should have a NULL
value. For example, attribute Mgr_Id of relation DEPARTMENT refers to the attribute ID of relation EMPLOYEE.
As ID is a primary key of relation EMPLOYEE, Mgr_Id becomes a foreign key for relation DEPARTMENT. For
example, in the DEPARTMENT table, departments having Dept_No 1, 2, 4, and 5 have Mgr_Id values E0002,
E0005, E0007, and E0004, respectively. So, the referential integrity constraint requires that the EMPLOYEE
table must have employees having Id values E0005, E0001, E0004, and E0007. Indeed, the table EMPLOYEE
does have employees having the aforementioned IDs, So, the foreign key constraint is satisfied. For Home Goods
department (Dept_No = 3), Mgr_Id value is NULL. This is in conformity with the referential integrity constraints.
However, as of now, Mgr_Id value cannot be E0014 because there is no employee in the EMPLOYEE table having
this value of attribute ID. We will discuss more about referential integrity in the next chapter.
3. NOT NULL Constraint: NOT NULL constraint can be applied to any attribute of a relation. According to this
constraint, attribute on which it is applied should not have NULL value. For example, we may define this constraint
on the Salary attribute of EMPLOYEE relation indicating that salary of all employees must be specified.
NOT NULL Constraint: Applies to an attribute. No row can have a NULL value.
4. UNIQUE Constraint: UNIQUE constraint when applied on any attribute, will ensure that no two tuples in the
relation should have the same attribute value. For example, you may enforce each department to have a unique
manager by applying UNIQUE constraint on attribute Mgr_Id of relation DEPARTMENT. This would imply that
two departments cannot have the same manager.
Unique Constraint: Applies to an attribute. No two rows can have same value of the specified attribute.
240 Touchpad Informatics Practices-XI

