Question

Define Super Key, Candidate Key, Primary Key and Foreign Key

Answer 1

In relational database design, there are several types of keys that are used to define relationships between tables. Here are the definitions of four common types of keys:

1. Super Key: A super key is a set of one or more attributes that, taken collectively, can uniquely identify a record in a table. A super key may contain more attributes than necessary to uniquely identify a record. For example, a super key for a table of employees might include attributes such as name, employee ID, and email address.

2. Candidate Key: A candidate key is a super key for which no subset of its attributes can uniquely identify a record. In other words, a candidate key is a minimal set of attributes that can uniquely identify a record in a table. A table may have more than one candidate key. For example, in a table of employees, the candidate keys might be employee ID and email address.

3. Primary Key: A primary key is a candidate key that is chosen to be the main key for a table. It is used to enforce data integrity and to ensure that each record in the table is unique. A primary key must be unique, non-null, and immutable (i.e., it cannot be changed once it has been assigned). For example, in a table of employees, the primary key might be employee ID.

4. Foreign Key: A foreign key is a column or a set of columns in one table that refers to the primary key of another table. It is used to establish a relationship between two tables. The foreign key column(s) in one table must have the same data type and constraints as the primary key column(s) in the other table. For example, in a table of employee reviews, there might be a foreign key column that refers to the employee ID column in the employees table. This establishes a relationship between the two tables such that each review is associated with a specific employee.