Relation Schema: Defining Structure

Every database table has two aspects: its structure (what columns exist, what types they have, what constraints apply) and its content (the actual rows of data). The structure is defined by the relation schema, while the content at any moment constitutes the relation instance.

Understanding this separation is fundamental. The schema is like an architectural blueprint—it defines what's possible. The instance is the actual building—what exists right now. Architects can discuss blueprints without ever constructing a building; similarly, we can reason about schemas without any data.

This page explores relation schemas in depth: their formal definition, their components, how they constrain instances, and how they evolve over the lifetime of a database.

A relation schema provides the complete structural specification for a relation. It defines what the relation can contain without specifying what it currently does contain.

Formal Definition:

A relation schema R is a tuple:

R = (R_name, {A₁, A₂, ..., Aₙ}, dom, C)

Where:

• R_name is the unique name of the relation
• {A₁, A₂, ..., Aₙ} is a set of n attribute names
• dom: Aᵢ → Dᵢ is a function mapping each attribute to its domain
• C is a set of constraints that any valid instance must satisfy

The degree of the schema is n, the number of attributes.

Common Notation Styles:

Different textbooks and systems use different notations for schemas:

1. Simple Notation (Most Common):

Employee(emp_id, emp_name, department, salary, hire_date)

2. With Domain Annotations:

Employee(emp_id: INTEGER, emp_name: VARCHAR(100), 
         department: VARCHAR(50), salary: DECIMAL(12,2), 
         hire_date: DATE)

3. With Key Underlining (Textbook Convention):

Employee(emp_id, emp_name, department, salary, hire_date)
         ^^^^^^
         Primary Key (underlined)

4. With Constraint Annotations:

Employee(
    emp_id: INTEGER [PK, NOT NULL],
    emp_name: VARCHAR(100) [NOT NULL],
    department: VARCHAR(50) [NOT NULL, FK→Departments.dept_name],
    salary: DECIMAL(12,2) [NOT NULL, CHECK > 0],
    hire_date: DATE [NOT NULL, CHECK ≤ CURRENT_DATE]
)

Let's examine each component of a relation schema comprehensively.

Attribute Characteristics:

Each attribute in a schema has several properties:

The distinction between intension and extension is fundamental in database theory, borrowed from logic and philosophy.

Intension (Schema): The intension of a relation is its schema—the definition of its structure. It answers: 'What can this relation contain?' The intension is relatively permanent; it changes only through explicit schema modifications (ALTER TABLE).

Extension (Instance): The extension of a relation is its current instance—the set of tuples it contains at a given moment. It answers: 'What does this relation contain now?' The extension changes frequently through data operations (INSERT, UPDATE, DELETE).

The Relationship:

The intension defines a space of possible extensions. At any moment, the actual extension must be one of these valid possibilities. Think of it as:

• Intension defines the rules of the game
• Extension is the current state of play

Analogy: Form vs. Content

Consider a paper form for recording employee information:

• The form design (what fields exist, what's required) is the intension
• Each filled-out form is an extension

You can design a form without filling it out (schema without data). You cannot fill out a form that doesn't exist (data without schema).

Practical Implications:

1. Schema design happens early: You must define the intension before inserting data. Planning the schema is a design activity.

2. Schema changes are expensive: Because the intension defines legal extensions, changing the schema may require transforming all existing data.

3. Instance changes are routine: The extension changes constantly—that's normal database operation.

4. Constraints are intensional: They're part of the schema, defining which extensions are valid.

Constraints are rules embedded in the schema that restrict valid extensions. They are the schema's mechanism for encoding business rules, ensuring data quality, and maintaining consistency.

Types of Schema Constraints:

Constraint Categories:

1. Domain Constraints: Restrict individual attribute values to valid domain members.

salary DECIMAL(12,2) CHECK (salary >= 0 AND salary < 10000000),
email VARCHAR(255) CHECK (email LIKE '%@%.%'),
status CHAR(1) CHECK (status IN ('A', 'I', 'P', 'T'))

2. Key Constraints: Ensure tuple uniqueness and identifiability.

PRIMARY KEY (order_id),
UNIQUE (customer_id, order_date),  -- Composite uniqueness
UNIQUE (social_security_number)     -- Candidate key

3. Referential Integrity Constraints: Ensure that references between relations are valid.

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

4. Semantic Constraints: Capture business rules that span multiple attributes.

CHECK (end_date > start_date),
CHECK (quantity > 0 OR status = 'cancelled'),
CHECK (discount_percent BETWEEN 0 AND 100)

While we've focused on individual relation schemas, a database typically contains multiple relations. The database schema is the collection of all relation schemas plus inter-relation constraints.

Definition (Database Schema):

A database schema S consists of:

• A set of relation schemas: {R₁, R₂, ..., Rₘ}
• A set of inter-relation constraints (primarily foreign keys)
• Additional global constraints and triggers

Diagrammatic Representation:

Database schemas are often visualized using Entity-Relationship Diagrams (ERDs) or relational schema diagrams:

┌────────────────┐       ┌────────────────┐
│  Departments   │       │   Employees    │
├────────────────┤       ├────────────────┤
│ *dept_id  [PK] │──┐    │ *emp_id   [PK] │
│  dept_name     │  │    │  emp_name      │
│  manager_id    │──┼──┐ │  salary        │
│  budget        │  │  │ │  dept_id  [FK] │──┐
└────────────────┘  │  │ │  hire_date     │  │
                    │  │ └────────────────┘  │
                    │  │                     │
                    │  └─────────────────────┘
                    │         
                    │     ┌────────────────┐
                    │     │   Projects     │
                    │     ├────────────────┤
                    │     │ *project_id [PK]│
                    └────▶│  dept_id   [FK]│
                          │  project_name  │
                          │  budget        │
                          └────────────────┘

Components of a Complete Database Schema:

Real databases aren't static—they evolve as requirements change. Schema evolution refers to modifications to the database structure over time. Managing schema evolution is one of the most challenging aspects of database maintenance.

Types of Schema Changes:

Schema Evolution Challenges:

1. Backward Compatibility: Applications written against the old schema may break. Solutions include:

• Maintaining views with old column names
• Gradual deprecation with warnings
• Versioned APIs

2. Data Migration: Existing data may not conform to new constraints. Strategies:

• Clean data before adding constraints
• Add column with default, then populate, then add NOT NULL
• Use triggers to transform data on access

3. Downtime: Some schema changes lock tables. Approaches:

• Blue-green deployments with schema migration
• Online DDL operations (if DBMS supports)
• Scheduled maintenance windows

4. Rollback: What if the schema change causes problems?

• Keep previous migration scripts
• Use transactional DDL (PostgreSQL)
• Maintain schema version history

Well-designed schemas are easier to query, maintain, and evolve. Here are principles distilled from decades of database practice:

Common Schema Anti-Patterns: