Relational Model Fundamentals & Keys

This chapter covers the foundational concepts of the relational database model, focusing heavily on database keys—how we uniquely identify rows, relate tables together, and design robust database schemas. These concepts form the absolute bedrock for relational database management systems (RDBMS) and are among the most frequently tested topics in technical interviews.

1. Why Do We Need Database Keys?

In a relational database, data is stored in tables (relations) consisting of rows (tuples) and columns (attributes). Without unique identifiers, managing data becomes chaotic:

• Redundancy and Duplicates: If two employees have the exact same first and last name, how do we distinguish them?
• Broken Relationships: When linking related data (like assigning an employee to a department), we need absolute certainty about which record we are referencing.
• Data Integrity: Keys enforce rules that prevent invalid, ambiguous, or duplicate entries from corrupting the database.

2. Core Mental Model: The Unique Badge Analogy

Real-Life Analogy: Think of an Employee ID badge in a company. A company might have dozens of employees named "Rahul", so people's names alone cannot uniquely identify anyone. However, an Employee ID card is strictly unique, never blank (non-null), and sufficient by itself to look up an employee's complete file, salary, and department.

Database keys serve this exact purpose: they are the unique identifiers and linking mechanisms that keep the database organized and searchable.

3. Super Key vs. Candidate Key: Refining the Attributes

Before arriving at a clean primary key, database designers analyze attributes using the concepts of Super Keys and Candidate Keys.

Super Key

A Super Key is any combination of attributes (columns) that can uniquely identify a row (tuple) in a table.

• Example: In an Employee table, employee_id is unique. But (employee_id, email) or (employee_id, mobile_number, name) are also super keys because they collectively guarantee uniqueness.
• Problem with Super Keys: They often contain unnecessary, redundant attributes.

Candidate Key

A Candidate Key is a minimal super key. It is a super key with no redundant attributes; if you remove any attribute from a candidate key, it loses its uniqueness property.

• Example: employee_id by itself is a minimal super key. Similarly, email (if unique) or mobile_number (if unique) can be candidate keys.
• Rule: A table can have multiple candidate keys, but one of them will be chosen as the primary key.

4. Primary Key: The Chosen Identifier

The Primary Key is a specific candidate key chosen by the database designer to uniquely define the tuples of a relation.

Core Properties of a Primary Key:

1. Uniqueness: Every value in the primary key column(s) must be unique. No two rows can share the same primary key value.
2. Non-Null (NOT NULL): A primary key cannot contain NULL values. If a value were null, it could not uniquely identify a row.
3. Immutability & Stability: It must reliably identify the record throughout its lifecycle.

-- Example table creation with a Primary Key
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

5. Alternate Keys: The Runners-Up

An Alternate Key (or secondary key) is any candidate key that was not chosen to be the primary key.

• If a table has 4 candidate keys and 1 is selected as the primary key, the remaining 3 candidate keys automatically become alternate keys. They can still be used to search or uniquely query the table, but they are not the main table identifier.

6. Foreign Key: Linking Tables Together

A Foreign Key is an attribute (or set of attributes) in one table that references the primary key of another table. This establishes a relationship or "link" between the two tables.

How Foreign Keys Work

Imagine two tables: Users and Courses.

• Users has a primary key user_id.
• Courses (or enrollments) stores a user_id column to track which user enrolled in which course.

SQL Implementation Example

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

7. Composite Key and Surrogate Key

Composite Key

A Composite Key is a primary key that consists of two or more attributes working together to uniquely identify a record when a single column is insufficient.

• Example: In an enrollment table where a student can take multiple courses, neither student_id nor course_id alone is unique. But the combination (student_id, course_id) is unique and forms a composite primary key.

Surrogate Key

A Surrogate Key is an artificially generated key created purely for database management purposes, typically implemented as an auto-incrementing integer (e.g., 1, 2, 3...).

• It has no business meaning (unlike an email address or Social Security Number).
• It is commonly used as a primary key to keep joins simple and efficient.

8. Summary of Database Keys

Summary and Key Takeaways

• Database Keys are essential structural rules ensuring data uniqueness, integrity, and relational mapping across tables.
• Super Keys identify rows uniquely, while Candidate Keys are the minimal, stripped-down versions of super keys.
• Primary Keys must be unique and non-null, serving as the definitive anchor for every table.
• Alternate Keys are the leftover candidate keys that were not selected as the primary key.
• Foreign Keys connect child tables to parent tables, enforcing referential integrity across the database schema.
• Composite Keys use multiple columns together to form a unique primary key.
• Surrogate Keys provide artificial, auto-incrementing integer identifiers to decouple primary keys from business logic.
• Understanding these 7 key types is vital for database design, normalization, and successfully clearing technical interview questions.