In the context of a Database Management System (DBMS), a “key” refers to a field or combination of fields that is used to uniquely identify a record in a database table. Keys play a crucial role in organizing and managing data within a database. There are different types of keys in a DBMS, and each serves a specific purpose:
Primary Key:
- Definition: Uniquely identifies each record in a table.
- Example (Employee Table): Choose either
Employee ID
orSSN
as a primary key. - Note: Must be unique and cannot be null.
Super Key:
- Definition: A set of one or more attributes whose combined value uniquely identifies an entity.
- Example (Employee Table):
(Employee ID, Full Name)
or(Employee ID, Full Name, Dept ID)
can be a super key.
Candidate Key:
- Definition: A column or set of columns that can uniquely identify any record in a table.
- Example (Employee Table):
Employee ID
andSSN
are candidate keys. - Note: Minimal super keys are called candidate keys.
Composite Key:
- Definition: A combination of two or more columns used to uniquely identify each row.
- Example (Employee Table): A primary key made by the combination of more than one attribute.
Alternate Key:
- Definition: Candidate keys not chosen as the primary key.
- Example (Employee Table): If
Employee ID
is the primary key, thenSSN
could be the alternate key.
Foreign Key:
- Definition: Represents a relationship between tables, ensuring referential integrity.
- Example (Employee and Project Tables):
Employee ID
in theProject
table points to theEmployee ID
inEmployee
table.Employee ID
inProject
is a foreign key.Employee ID
inEmployee
is the primary key.