Relational Model & Keys

The Relational Model, proposed by Edgar F. Codd in 1970, represents data as a collection of relations (tables). Each table consists of tuples (rows) and attributes (columns). Relationships between tables are enforced through keys.

---

Learning Objectives

After completing this chapter, you will be able to:

• Define the key terms of the relational model.
• Differentiate between all six types of keys.
• Understand integrity constraints (entity, referential, domain).
• Identify suitable primary keys from requirements.
• Design tables with proper key relationships.
• Answer interview questions on keys.

---

Core Terminology

Term	Meaning	Example
Relation	A table	Students table
Tuple	A row (record)	One student’s data
Attribute	A column (field)	Name, Age, Email
Domain	Set of allowed values for an attribute	Age must be integer 0-150
Degree	Number of attributes (columns)	Students has degree 4
Cardinality	Number of tuples (rows)	Students has cardinality 100
Schema	Structure definition	Column names and types
Instance	Actual data at a moment	The current 100 rows

---

Types of Keys

1. Super Key

A Super Key is any set of attributes that can uniquely identify a row. It may contain extra (redundant) attributes.

For the table Students(Student_ID, Name, Email, Phone):

Super Key	Unique?	Minimal?
{Student_ID}	Yes	Yes
{Email}	Yes	Yes
{Phone}	Yes (if unique)	Yes
{Student_ID, Name}	Yes	No (Name is redundant)
{Name, Email}	Yes	Yes (if Name alone isn’t unique)

2. Candidate Key

A Candidate Key is a minimal Super Key — no proper subset of it can uniquely identify a row.

Attribute Set	Candidate Key?	Reason
{Student_ID}	Yes	Minimal, unique
{Email}	Yes	Minimal, unique
{Student_ID, Name}	No	Not minimal (Student_ID alone works)

3. Primary Key

The Primary Key is the Candidate Key chosen by the database designer as the main identifier.

Rules:

• Uniquely identifies every row
• Cannot be NULL (entity integrity)
• Only one per table
• Should be stable (never changes)

4. Alternate Key

Alternate Keys are Candidate Keys that were NOT chosen as the Primary Key.

Example: If Student_ID is the PK, then Email is an Alternate Key.

You can enforce Alternate Key uniqueness with a UNIQUE constraint.

5. Foreign Key

A Foreign Key is an attribute in one table that references the Primary Key of another table. It creates a relationship between tables.

Customers                         Orders
┌─────────────┐                  ┌──────────────────┐
│ Customer_ID │←── FK ────────── │ Order_ID         │
│ Name        │                  │ Customer_ID (FK) │
│ Email       │                  │ Order_Date       │
└─────────────┘                  │ Total            │
                                 └──────────────────┘

Rules:

• FK value must either match a PK in the parent table, or be NULL
• This is called Referential Integrity

6. Composite Key

A Composite Key consists of two or more attributes — used when no single attribute can uniquely identify a row.

Enrollments
┌──────────────────┐
│ Student_ID (PK)  │ ← composite
│ Course_ID  (PK)  │ ← composite
│ Enrollment_Date  │
└──────────────────┘

Neither Student_ID alone nor Course_ID alone is unique. Together they are.

---

Key Hierarchy

Super Key (any unique set of attributes)
    │
    └── Candidate Key (minimal Super Key)
            │
            ├── Primary Key (chosen one)
            └── Alternate Key (not chosen)

Foreign Key — references PK of another table
Composite Key — uses multiple columns for uniqueness

---

Integrity Constraints

1. Entity Integrity

Primary Key cannot be NULL (or partially NULL for composite keys).

INSERT INTO Students (Name) VALUES ('Rahul');
-- ERROR: Student_ID (PK) cannot be NULL

2. Referential Integrity

Foreign Key value must either match an existing Primary Key in the parent table, or be NULL.

INSERT INTO Orders (Customer_ID) VALUES (999);
-- ERROR: Customer_ID 999 does not exist in Customers table

3. Domain Integrity

Values must belong to a valid set (data type, CHECK constraints, ENUM).

Age INT CHECK (Age > 0 AND Age < 150)

---

Choosing a Primary Key

Factor	Why It Matters
Stable	Should never change (avoid SSN — people change them)
Unique	Must always be unique
Simple	Prefer a single column over composite
Non-NULL	Every row must have a value

Best practice: Use surrogate keys (auto-increment integers or UUIDs) instead of natural keys. Surrogate keys are system-generated, never change, and have no business meaning.

---

Real-World Example

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(255) UNIQUE  -- Alternate Key
);

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,  -- Foreign Key
    Order_Date DATE,
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);

---

Interview Deep Dive

---

Key Takeaways

• Super Key: Any set of attributes that uniquely identifies a row.
• Candidate Key: Minimal Super Key (no redundant attributes).
• Primary Key: The chosen Candidate Key (non-NULL, unique, one per table).
• Alternate Key: Candidate keys not chosen as primary.
• Foreign Key: Links tables by referencing another table’s primary key.
• Composite Key: Uses multiple attributes for uniqueness.
• Entity Integrity: Primary key cannot be NULL.
• Referential Integrity: Foreign key must match existing primary key or be NULL.
• Domain Integrity: Column values must satisfy data type and constraints.