Page 144 - Informatics_Practices_Fliipbook_Class12
P. 144

Proj_No, Dept_Num
              Proj_No, Proj_Name, Dept_Num
              Proj_Name, Dept_Num
        Note that in the combination of attributes: {Proj_No, Proj_Name}, the attribute  Proj_Name is superfluous

        as the attribute  Proj_No alone qualifies to be a candidate key. So, the combination of attributes {Proj_No,
        Proj_Name} does not qualify to be a candidate key. Similarly, none of the combinations of attributes qualifies to be
        a candidate key.
              {Proj_No, Dept_Num}, {Proj_No, Proj_Name, Dept_Num}

                1.  Does Proj_Name alone qualify to be a candidate key of the relation PROJECT? Why?
                2.  Does Dept_Num alone qualify to be a candidate key of the relation PROJECT? Why?


        As mentioned above, since two departments may have projects with the same name, Proj_Name alone will not

        form a candidate key. However, project names being unique within a department, Proj_Name, and Dept_Num,
        the combination of attributes {Proj_Name,  Dept_Num} will also form a candidate key. In light of the above
        discussion, the table  PROJECT has the following candidate keys:
              {Proj_No}
              {Proj_Name, Dept_Num}


               Candidate key: Minimal set of attributes that identifies a tuple.



        Primary Key
        One of the candidate keys is chosen as the primary key. Generally, the candidate key with the minimum number of
        attributes is chosen as the primary key. So, we choose Proj_No as the primary key for the relation PROJECT.



               Primary Key: One of the candidate keys is designated as primary key.




                1.  ID is a candidate key for the table EMPLOYEE. Why?
                2.  Is there a candidate key other than ID for the table EMPLOYEE? Why?
                3.  Dept_No is a candidate key for the table DEPARTMENT. Why?
                4.  Is there a candidate key other than  Dept_No for the table DEPARTMENT? Why?


        Alternate Keys

        A candidate key that is not chosen as the primary key is called an alternate key. For example, {Proj_Name, Dept_
        Num} will be an alternate key for the relation PROJECT.


               Alternate Key: A candidate key(s) other than primary key



        Foreign Key

        An attribute or a set of attributes in one table that refer to the primary key of another table is known as a foreign key. For
        example, in the table PROJECT, attribute Dept_Num refers to the attribute Dept_No of  the table DEPARTMENT.




          130  Touchpad Informatics Practices-XII
   139   140   141   142   143   144   145   146   147   148   149