|
A key is the data item that
exclusively identifies a record. For example, account number, Product code,
employee number, and customer number are used as key fields because they
identify a record stored in the database |
|
A super key for an entity is a
set of one or more attributes whose combined value uniquely identifies the
entities in the entity set. For example, for an entity set Employees, the set
of attributes (emp_name, address) can be considered to be a super key, if we
assume that there are no two employees with the same name emp_name and same
address |
|
The primary key of a relation
can be said to be a minimal super key. The field or group of fields which
form the unique identifier for a table is called the table’s primary
key. The primary key should be chosen such that its attributes are never or very rarely changed. For instance, the address field of a person should not be part of the primary key, since it is likely to change. Emp_code, on the other hand, is guaranteed to never change, till he is in the organization. |
|
There is only one primary key in a table. But there can be multiple candidate keys. A candidate key is an attribute or set of attributes that uniquely identifies a record. These attributes or combination of attributes are called candidate keys. In such a case, one of the candidate key is chosen as a primary key. The remaining candidate keys are called Alternate keys. |
|
In many cases, as we design
the database, we will have tables that will use more than one column as part
of the primary key. These are called composite keys (or concatenated keys).
In other words, when a record cannot be uniquely identified by a single
field, in such cases a composite key is used. A composite key is a group of
fields that uniquely identify a record. |
|
In a relation, column whose
data values correspond to the values of a key column in another relation is
called a Foreign key. In a relational database the
foreign key of a relation would be the primary key of another relation |
|
A secondary key is an
attribute or combination of attributes that may not be a candidate key but
classifies the entity set on a particular characteristic. For example, the
entity set EMPLOYEE having the attribute ‘Department’ which identifies by its
values which means all instances of EMPLOYEE who belong to a given
department. More than one employee may
belong to a department, so the Department attribute is not a candidate key
for the entity set EMPLOYEE since it cannot uniquely identify an individual
employee. However, the ‘Department’ attribute does identify all employees
belonging to a given department. |