Explain the primary key, super key, foreign key and candidate key with example. OR Define key. Explain various types of keys.

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 or SSN 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 and SSN 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, then SSN could be the alternate key.

Foreign Key:

• Definition: Represents a relationship between tables, ensuring referential integrity.
• Example (Employee and Project Tables):
  • Employee ID in the Project table points to the Employee ID in Employee table.
  • Employee ID in Project is a foreign key.
  • Employee ID in Employee is the primary key.