Page 145 - Informatics_Practices_Fliipbook_Class12
P. 145
So, Dept_No being the primary key of the table DEPARTMENT, attribute Dept_Num of relation PROJECT becomes
a foreign key of the table PROJECT.
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?
4.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 E0002, E0005, E0007, and E0004. 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
Database Query using SQL 131

