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
   249   250   251   252   253   254   255   256   257   258   259