Relational Data Model, Schemas, Keys & Constraints – Core Concepts

1. The Relational Data Model – Overview

The relational data model, proposed by E. F. Codd in 1970, represents data as a collection of relations (tables). Each relation is a set of tuples (rows) that share the same attributes (columns). Formally, a relation $R$ over a set of attributes $\{A_1,A_2,\dots ,A_n\}$ is a subset of the Cartesian product

$$R\subseteq dom(A_1)\times dom(A_2)\times \cdots \times dom(A_n)$$

where $dom(A_i)$ is the domain (allowed values) of attribute $A_i$.

Key properties:

These properties make relational tables easy to manipulate with set‑theoretic operations, which underlie relational algebra and SQL.

2. Schemas, Instances, and Database State

• Schema: The structural description of a relation, written as $R(A_1,A_2,\dots ,A_n)$. It defines attribute names, domains, and constraints but contains no data.
• Instance: A concrete set of tuples that conforms to a schema at a particular point in time.
• Database State: The collection of all relation instances in the database at a given moment.

Example:

Customer(CustID, CustName, Address, Phone, Email)

The schema above tells us the table has five attributes. An instance might be:

The database state consists of this Customer instance together with instances of any other relations.

3. Keys – The Backbone of Integrity

3.1 Primary Key (PK)

A primary key is a minimal set of attributes that uniquely identifies each tuple in a relation. It must be unique (no two rows share the same PK value) and not null.

• Implementation: In SQL, PRIMARY KEY constraint enforces both uniqueness and NOT NULL.

3.2 Candidate Keys

A candidate key is any attribute set that could serve as a primary key. The primary key is chosen from the candidate keys.

Example: In a Student(SID, Email, SSN, Name) relation, both SID and SSN could be candidate keys; one is selected as PK.

3.3 Superkey

A superkey is any superset of a candidate key. It also uniquely identifies tuples but may contain unnecessary attributes.

3.4 Foreign Key (FK)

A foreign key is an attribute (or set) in one relation that references the primary key of another relation, establishing a referential link.

• Referential Integrity: The DBMS must ensure that a foreign‑key value either matches an existing primary‑key value in the referenced table or is NULL (if allowed).

Worked Example – Banking Schema

Customer(CustomerID PK, CustomerName, Address, Phone, Email)
Loan(LoanNumber PK, LoanType, Amount)
Borrows(CustomerID FK → Customer.CustomerID,
        LoanNumber FK → Loan.LoanNumber,
        BorrowDate)

• CustomerID in Borrows must exist in Customer.
• LoanNumber in Borrows must exist in Loan.

If a user tries to insert (CustomerID='C999', LoanNumber='L123') into Borrows and C999 does not exist in Customer, the DBMS rejects the operation, preserving referential integrity.

4. Integrity Constraints

4.1 Entity Integrity

Ensures that primary key attributes are never NULL. This guarantees each tuple is identifiable.

4.2 Referential Integrity

Ensures foreign key values correspond to existing primary key values. It prevents orphan records.

4.3 Domain Constraints

Restrict the set of permissible values for an attribute (e.g., Age must be an integer between 0 and 120). Implemented via data types, CHECK clauses, or enumerated types.

4.4 User‑Defined (Business) Constraints

Application‑specific rules that cannot be expressed by the three basic constraints. Examples:

• “A loan amount cannot exceed the customer's credit limit.”
• “An order must contain at least one book.”

These are usually enforced with CHECK constraints, triggers, or application logic.

5. Mapping ER Concepts to Relational Model

Example – Online Bookstore

ER: Customer (1) — places — (M) Order; Order (M) — contains — (M) Book.

Relational mapping:

Customer(CustID PK, Name, …)
Order(OrderID PK, CustID FK → Customer.CustID, OrderDate)
OrderItem(OrderID FK → Order.OrderID,
          BookID FK → Book.BookID,
          Quantity, Price)
Book(BookID PK, Title, Author, Price)

The OrderItem table resolves the M:N relationship between Order and Book.

6. SQL DDL for Defining Constraints

6.1 Creating Tables with Primary & Foreign Keys

CREATE TABLE Customer (
    CustomerID   CHAR(5)      PRIMARY KEY,
    CustomerName VARCHAR(50)  NOT NULL,
    Address      VARCHAR(100),
    Phone        VARCHAR(15),
    Email        VARCHAR(50)  UNIQUE
);

CREATE TABLE Loan (
    LoanNumber   CHAR(6)      PRIMARY KEY,
    LoanType     VARCHAR(20)  NOT NULL,
    Amount       DECIMAL(12,2) CHECK (Amount > 0)
);

CREATE TABLE Borrows (
    CustomerID   CHAR(5)      NOT NULL,
    LoanNumber   CHAR(6)      NOT NULL,
    BorrowDate   DATE,
    PRIMARY KEY (CustomerID, LoanNumber),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
        ON DELETE CASCADE,
    FOREIGN KEY (LoanNumber) REFERENCES Loan(LoanNumber)
        ON DELETE RESTRICT
);

• PRIMARY KEY enforces entity integrity.
• UNIQUE on Email is a domain constraint.
• CHECK on Amount is a domain constraint.
• ON DELETE CASCADE propagates deletions, illustrating a referential‑integrity rule.

6.2 Adding Constraints After Table Creation

ALTER TABLE Borrows
ADD CONSTRAINT chk_borrow_date
CHECK (BorrowDate <= CURRENT_DATE);

This user‑defined constraint ensures no future borrow dates.

7. Comparison of Constraint Types

Advantages

• Data Quality: Guarantees that only valid data is stored.
• Self‑Documentation: Constraints describe business rules directly in the schema.
• Performance: Indexes created for PKs and UNIQUE constraints speed up queries.

Disadvantages

• Over‑Constraining: Excessive constraints can hinder legitimate data entry and require frequent schema changes.
• Complexity: Triggers and complex CHECK conditions may be hard to maintain.
• Portability: Some DBMSs have slight syntax differences for constraints, affecting migration.

8. Worked Trace – Inserting a Borrow Record

Assume the following current data:

Customer
+-----------+------------+
| CustomerID| CustomerName|
+-----------+------------+
| C001      | Ram        |
| C002      | Sita       |
+-----------+------------+

Loan
+-----------+----------+
| LoanNumber| Amount   |
+-----------+----------+
| L100      | 5000.00 |
| L101      | 12000.00|
+-----------+----------+

Borrows (empty)

Step 1 – Insert a valid borrow

INSERT INTO Borrows (CustomerID, LoanNumber, BorrowDate)
VALUES ('C001', 'L100', '2024-09-01');

• DBMS checks entity integrity: CustomerID and LoanNumber are NOT NULL → OK.
• Checks referential integrity: C001 exists in Customer, L100 exists in Loan → OK.
• Primary key (CustomerID, LoanNumber) is unique → OK.
• Record is stored.

Step 2 – Attempt an invalid borrow (non‑existent customer)

INSERT INTO Borrows (CustomerID, LoanNumber, BorrowDate)
VALUES ('C999', 'L100', '2024-09-02');

• DBMS finds no CustomerID='C999' in Customer.
• Referential integrity violation → transaction aborted, error returned.

Step 3 – Attempt duplicate primary key

INSERT INTO Borrows (CustomerID, LoanNumber, BorrowDate)
VALUES ('C001', 'L100', '2024-09-03');

• Primary key (C001, L100) already exists.
• Primary key violation → transaction aborted.

This trace illustrates how relational constraints protect data automatically.

9. Common Misconceptions

10. Summary

The relational data model provides a mathematically sound foundation for modern databases. By defining relations, keys, and integrity constraints, it ensures that data remains accurate, consistent, and meaningful. Understanding how primary, candidate, and foreign keys interact, and how to express constraints in SQL DDL, is essential for designing robust schemas and for answering exam questions that test both theory and practical SQL skills.

Exam tip

• Remember the three core integrity rules: entity integrity (PK not null), referential integrity (FK matches PK), and domain integrity (type/check). Many exam items ask you to identify which rule a given constraint enforces.
• When a schema is given, first underline primary keys, then draw foreign‑key arrows, and finally list the implied constraints. This visual step earns marks for completeness.
• SQL DDL questions usually require you to write CREATE TABLE statements with PK, FK, and CHECK clauses. Write the constraints in the order: PRIMARY KEY, then UNIQUE/NOT NULL, then FOREIGN KEY.
• For “classify DBMS by data model”, recall the hierarchy: hierarchical → network → relational → object‑relational → NoSQL (document, key‑value, column‑family, graph). A one‑line table is enough.
• Practice the trace: simulate an INSERT, identify which constraint is checked first, and state the outcome. This shows deep understanding and often appears in short‑answer sections.