Page 266 - Trackpad 402_Class-X_Final
P. 266
Different Types of Keys in a Relation
When the data is stored in the form of records then there are chances where the data values are repeated. For
example, in a class of forty students if a table is created with - Name, Address, City, Date of Birth, Phone number then
there may be two or more students with the same name. There are also chances when twins staying in the same house
will have the same address, Date of Birth and phone numbers. Two or more classmates may have different addresses
or phone numbers but share the same birth date. So with so many similarities and so many repetition of values in
different columns we may face the following issues:
• If a specific information is needed in a database then searching will become difficult. For example if we see the
above table where there are two students with the same name- SMITH. If a teacher wants to call parents of Smith
who lives in Defence Colony because he is absent from a long time without any information then it becomes difficult
to search unless both records are uniquely identified.
• Searching might not give you the correct information.
• There are chances of storing duplicate and unwanted/ erroneous values in the table.
• If data is needed from two or more tables then linking of multiple tables cannot be done unless there is a common
and unique column.
A key or an attribute solves all the above problems. It stores data values of the similar type. There are different kinds
of keys that can be created in a table for the efficient retrieval of the data. These keys are as follows:
• Candidate Key: An attribute or a set of attributes that can uniquely identify a record and is capable of being a
primary key is called a candidate key. For example, if we consider a student table then Admno, Rollno, DateofBirth
are the candidate keys as they are chances of unique values.
• Primary Key: A candidate key that uniquely identifies a row in a tuple by storing Not Null and unique values is known
as a Primary key. For example in a STUDENT table - Admission number is a primary key because it is unique for each
student. Even if you pass out from the school and you come after a few years to get some information then using
your admission number you can easily get your details. In an EMPLOYEE table- Employee code is a key with a unique
value and is never repeated for the other employees. In a CUSTOMER table - Customer_Id is the primary key.
In the given table DEPT, the primary key is the DCODE column as it uniquely identifies each record.
Table: DEPT
DCODE DEPARTMENT CITY
D01 MEDIA DELHI
D02 MARKETING DELHI
D03 INFRASTRUCTURE MUMBAI
D05 FINANCE KOLKATA
Candidate Key
Primary Key Alternate Key
Data is arranged on the basis of Primary Keys in the database so that searching becomes accurate, easier and faster.
• Alternate Key: A remaining candidate key which is not selected as the primary key is called an alternate key. In the
table DEPT, the primary key is DCODE, and the alternate key is DEPARTMENT.
• Composite Primary Key: Sometimes a single attribute cannot be used as a primary key then in that case two or
more attributes in combination will form a unique set of values which can be used as a primary key. Such a set
of combination of attributes is known as a Composite Primary key. For example, if admno is not taken then a
combination of Rollno + Phoneno will give a unique combination and can be used as a composite Primary key.
• Foreign Key: It is an attribute or a set of attributes whose values match the primary key of another table. A relationship
between two tables matches the primary key of one table with the foreign key of another table.
266 Trackpad Information Technology-X

