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

