03 - The Relational Model

🎯 Learning Objectives

• Define relation, tuple, attribute, and domain
• Differentiate between superkey, candidate key, primary key, and foreign key
• Explain the concept of referential integrity
• Distinguish between natural keys and surrogate keys
• Describe the difference between procedural and declarative query languages

📋 Prerequisites

• 01 - DBMS Introduction — Basic understanding of what a database is
• Basic set theory from BSMA1001 (Maths 1) — Sets, subsets, Cartesian product

📖 Core Content

3.1 Intuition: What is a "Relational" Database?

• Each row (tuple) represents one entity or relationship
• Each column (attribute) represents one property of that entity
• Each cell contains a single value from a specific domain (set of allowed values) Think of it like a spreadsheet, but with strict rules:

1. Every column has a fixed data type (domain)
2. No two rows are identical
3. Rows have no inherent order
4. Tables can be linked through keys

Why This Matters: The relational model was a revolutionary breakthrough in the 1970s. Before it, databases forced you to think about how data was physically stored (pointers, hierarchies). The relational model lets you think purely about data and relationships — the system handles the storage details.

3.2 Formal Definitions

Domain

• $D_1 = \{\text{'A'}, \text{'B'}, \text{'C'}, \dots, \text{'F'}\}$ (grades)
• $D_2 = \text{INTEGER}$ (all integers)
• $D_3 = \{\text{'CS'}, \text{'Math'}, \text{'Physics'}\}$ (departments) Domains must be atomic (indivisible). You can't have a domain of "lists of phone numbers" in a pure relational model.

Relation Schema

Relation Instance

• A relation is a set of tuples — no duplicates allowed
• Tuples are unordered — you can't rely on row position
• Each tuple assigns a value (or NULL) to each attribute

Degree and Cardinality

• Degree = Number of attributes (columns)
• Cardinality = Number of tuples (rows)

3.3 Keys

Superkey

Candidate Key

Primary Key

• Unique — no two tuples have the same primary key value
• Not NULL — every tuple must have a primary key value
• Stable — should rarely, if ever, change Declared in SQL as:

sql
CREATE TABLE instructor (
    ID        VARCHAR(5),
    name      VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary    NUMERIC(8,2),
    PRIMARY KEY (ID)
);

Foreign Key

sql
CREATE TABLE teaches (
    instructor_id VARCHAR(5),
    course_id     VARCHAR(8),
    semester      VARCHAR(6),
    year          NUMERIC(4,0),
    PRIMARY KEY (instructor_id, course_id, semester, year),
    FOREIGN KEY (instructor_id) REFERENCES instructor(ID)
);

• Match a primary key value in the referenced table, or
• Be NULL (if allowed) This ensures we never have an orphaned record (e.g., a course taught by a non-existent instructor).

Surrogate Key

sql
CREATE TABLE student (
    student_id INTEGER GENERATED ALWAYS AS IDENTITY, -- surrogate
    roll_number VARCHAR(10),                          -- natural key
    name       VARCHAR(50),
    PRIMARY KEY (student_id)
);

3.4 Worked Examples

Example 1: Finding Candidate Keys

• $A$ is not on any RHS? Actually $A$ appears on LHS only.
• $D$ appears only on RHS of $C \rightarrow D$.
• So $A$ must be in every candidate key. Step 2: Compute $A^+ = \{A, B, C, D\}$ (using $A \rightarrow B, B \rightarrow C, C \rightarrow D$)
• Since $A^+$ includes all attributes, $A$ alone is a candidate key. Step 3: Check if any subset of $A$ is a key... $A$ has only one attribute, so $A$ is the sole candidate key.

Example 2: Superkey Count

• Number of superkeys = $2^{(5-1)} = 2^4 = 16$ If there are two candidate keys of size 1 each, with an overlap of 0:
• Number of superkeys = $2^{4} + 2^{4} - 2^{3} = 16 + 16 - 8 = 24$

3.5 Referential Integrity in Practice

sql
CREATE TABLE department (
    dept_id   INTEGER PRIMARY KEY,
    dept_name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE employee (
    emp_id    INTEGER PRIMARY KEY,
    emp_name  VARCHAR(50),
    dept_id   INTEGER,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

1. Insert employee with dept_id=999 when no department 999 exists? → REJECTED
2. Delete department 1 when employees reference it? → Depends on the policy:
   • ON DELETE RESTRICT (default): Rejected
   • ON DELETE CASCADE: All employees in dept 1 are also deleted
   • ON DELETE SET NULL: Employees' dept_id set to NULL

3.6 Why the Relational Model Won

1. Simplicity: Users think in terms of tables, not pointers or hierarchies
2. Data independence: Physical storage is hidden from users
3. Set-oriented operations: Queries operate on sets of tuples, not one-at-a-time
4. Solid theory: Based on set theory and predicate logic — provably correct transformations
5. Declarative queries: SQL specifies what to get, not how to get it

3.7 Key Constraints Reference

⚠️ Common Pitfalls

Pitfall 1: Confusing Primary Key and Unique Constraints

• A table has exactly ONE PRIMARY KEY, but multiple UNIQUE constraints
• PRIMARY KEY automatically implies NOT NULL; UNIQUE allows NULLs
• PRIMARY KEY is used for foreign key references by default
• PRIMARY KEY often creates a clustered index; UNIQUE creates a non-clustered index

Pitfall 2: Thinking Foreign Keys Must Match Column Names

sql
FOREIGN KEY (dept_id) REFERENCES department(dept_id)  -- same name
FOREIGN KEY (d_id) REFERENCES department(dept_id)      -- different name, still valid

Pitfall 3: Using Composite Natural Keys When Surrogate Keys Are Better

• Are long and cumbersome to join
• May change (name changes due to marriage)
• Have no performance advantage
• Make foreign key references large Better: Use a surrogate key (auto-increment ID) and add a UNIQUE constraint on the natural key.

📝 Practice Questions

Q1. Define the following terms: relation, tuple, attribute, domain.

Answer

• Relation: A table consisting of a set of tuples (rows) conforming to a schema
• Tuple: A single row in a relation; an ordered set of attribute values
• Attribute: A named column in a relation representing a property
• Domain: The set of allowed values for an attribute (e.g., INTEGER, VARCHAR(20))

Example: In instructor(ID, name, dept_name, salary), each row is a tuple, each column is an attribute, and VARCHAR(5) is the domain of ID.

Q2. What is a superkey? How does it differ from a candidate key?

Answer

• Superkey: Any set of attributes that uniquely identifies a tuple. Can have redundant attributes.
• Candidate key: A minimal superkey — removing any attribute breaks uniqueness.

Example: In relation employee(emp_id, email, name):

• {emp_id} is a superkey AND a candidate key
• {emp_id, email} is a superkey but NOT a candidate key (not minimal)
• {email} may be a candidate key if emails are unique

Q3. Explain referential integrity with an example.

Answer

Referential integrity ensures that a foreign key value in one table must match a primary key value in the referenced table (or be NULL).

Example:

sql
CREATE TABLE orders (
    order_id  INTEGER PRIMARY KEY,
    cust_id   INTEGER REFERENCES customer(cust_id)
);

If we try to insert an order with cust_id = 999 when no customer has cust_id = 999, the DBMS rejects it. This prevents orphaned orders from non-existent customers.

Q4. What is the difference between a natural key and a surrogate key?

Answer

Q5. Given relation R(A, B, C) and FD set {A → B, B → C}, find the candidate key(s).

Answer

Step 1: Find attributes never on RHS: A is on LHS only, never on RHS. So A is mandatory in any key. Step 2: Compute A⁺ = {A, B, C} (using A→B, then B→C). A⁺ covers all attributes. Step 3: Check if any subset of A works... A is a single attribute, so it's minimal.

Candidate key: {A}

Q6. A relation has 6 attributes. If there are two candidate keys each of size 2, what is the maximum number of superkeys?

Answer

Using the formula: $2^{(N-n1)} + 2^{(N-n2)} - 2^{(N-(n1+n2))}$

Where N=6, n1=n2=2: $= 2^{4} + 2^{4} - 2^{2} = 16 + 16 - 4 = 28$

This assumes no overlap between the two candidate keys. If they share an attribute, the count changes.

Q7. What is the degree and cardinality of a relation?

Answer

• Degree: The number of attributes (columns) in the relation schema
• Cardinality: The number of tuples (rows) in the relation instance

Example: If instructor has columns (ID, name, dept_name, salary) and 12 rows:

• Degree = 4
• Cardinality = 12

Q8. Consider the tables:

pseudo
student(roll_no, name, dept)
enrollment(roll_no, course_code, semester)

Answer

roll_no in enrollment is both:

• It's part of the composite primary key of enrollment (along with course_code and semester)
• It's also a foreign key referencing student(roll_no)

This dual role is common in junction/association tables that model many-to-many relationships.

🔗 Cross-References

• Next Topic: 04 - SQL DDL
• Previous Topic: 02 - DBMS Architecture
• Related: BSMS2001 (BDM) — Relational model for business data
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 2 (Relational Model), Chapter 3 (SQL) Join Discord Previous02 - DBMS ArchitectureNext04 - SQL DDL