Page 253 - Information_Practice_Fliipbook_Class11
P. 253

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}
            2. Primary Key



                  Primary Key: One of the candidate keys is designated as 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.


                     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?



            3. 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.



            4. 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.
            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.



                                                                Database Concepts and the Structured Query Language  239
   248   249   250   251   252   253   254   255   256   257   258