Types of Keys in Relational Model:
In the relational model of database management systems (DBMS), keys play a crucial role in establishing relationships between tables. There are several types of keys, each serving a specific purpose. Let’s discuss the main types of keys and provide examples using a “Students” table and a “Courses” table.
1. Primary Key:
Definition:
A primary key is a column (or a set of columns) in a table that uniquely identifies each record in that table. It must have unique values and cannot contain NULL values.
Example:
Student Table
+-----------+--------------+------+-------------+
| StudentID | Name | Age | Major |
+-----------+--------------+------+-------------+
| 1 | Alice | 21 | Computer Sci|
| 2 | Bob | 22 | Chemistry |
| 3 | Charlie | 20 | Physics |
| 4 | David | 23 | Mathematics |
| 5 | Emily | 22 | Biology |
+-----------+--------------+------+-------------+In this example, the StudentID column is the primary key. It uniquely identifies each student in the “Students” table.
2. Foreign Key:
Definition:
A foreign key is a column (or a set of columns) in a table that refers to the primary key in another table. It establishes a relationship between the two tables.
Example:
Courses Table
+-----------+--------------+
| CourseID | CourseName |
+-----------+--------------+
| 1 | Math 101 |
| 2 | English 201 |
| 3 | History 301 |
+-------------------------+Enrollment Table
+--------------+-----------+-----------+
| EnrollmentID | StudentID | CourseID |
+--------------+-----------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 1 |
+--------------------------------------+
In this example, StudentID and CourseID in the “Enrollments” table are foreign keys. They refer to the primary keys in the “Students” and “Courses” tables, establishing a relationship between them.
3. Candidate Key:
Definition:
A candidate key is a column (or a set of columns) in a table that can uniquely identify each record, similar to a primary key. However, in a given context, only one candidate key is chosen as the primary key.
Example:
Students Table
+--------------+--------------+-----+-------------+
| SSN | Name | Age | Major |
+-------------------------------------------------+
| 123-45-6789 | Alice | 21 | Computer Sci|
| 234-56-7890 | Bob | 22 | Chemistry |
| 345-67-8901 | Charlie | 20 | Physics |
+-------------------------------------------------+
In this example, the SSN (Social Security Number) is a candidate key, but it is chosen as the primary key for uniquely identifying students.
4. Super Key:
Definition:
A super key is a set of one or more columns that can uniquely identify a record in a table. It may contain more columns than necessary to form a minimal unique identifier.
Example:
Students Table
+-----------+-----------------+--------------+-----+-------------+
| StudentID | SSN | Name | Age | Major |
+-----------+-----------------+--------------+-----+-------------+
| 1 | 123-45-6789 | Alice | 21 | Computer Sci|
| 2 | 987-65-4321 | Bob | 22 | Chemistry |
| 3 | 555-12-3456 | Charlie | 20 | Physics |
| 4 | 789-01-2345 | David | 23 | Mathematics |
| 5 | 456-78-9012 | Emily | 22 | Biology |
+-----------+-----------------+--------------+-----+-------------+In this example, the combination of StudentID and SSN forms a super key. While StudentID alone is sufficient as a primary key, the combination still forms a super key.
5. Composite Key:
Definition:
A composite key is a primary key composed of multiple columns, where each column is essential for uniqueness.
Example:
Courses Table
+----------------+-------------+
| DepartmentCode | CourseNumber|
+------------------------------+
| CS | 101 |
| MATH | 201 |
| ENG | 301 |
+------------------------------+In this example, the combination of DepartmentCode and CourseNumber forms a composite key that uniquely identifies each course.