Relational Model Constraints and Relational Database Schema

In the relational model, a database state represents the data stored in all relations at a particular point in time. To ensure data correctness, consistency, and validity, various types of constraints are imposed on the data.

Categories of Constraints

Constraints in relational databases are classified into four main types:

1. Inherent (Model-Based) Constraints

   • Implicit in the relational model.

   • Example: All tuples in a relation must be unique (no duplicates).

2. Schema-Based (Explicit) Constraints

   • Defined as part of the database schema using DDL.

   • Examples: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

3. Application-Based (Semantic) Constraints

   • Business rules that cannot be enforced through schema alone. Must be enforced through application logic.

   • Example: A manager’s salary must be higher than their employees’.

4. Data Dependencies

   • Constraints like functional dependencies and multivalued dependencies, primarily used during normalization.

Schema-Based (Explicit) Constraints

Explicit constraints are rules written into the schema to maintain data integrity.

a. Domain Constraints

• Each attribute has an associated domain (set of valid atomic values).

• Values in tuples must come from the domain of the attribute.

• Example: Age must be a positive integer.

b. Entity Integrity

• Every relation must have a Primary Key.

• Primary key values cannot be NULL, must be unique.

• Ensures that each tuple is uniquely identifiable.

c. Referential Integrity

Ensures consistency across related relations using foreign keys.

A foreign key (FK) in relation R1 references the primary key (PK) in relation R2 if:

• The attributes in FK have the same domains as the PK in R2.

• Each FK value in R1 must either:

  • Match a PK value in R2, or

  • Be NULL (if optional).

• R1 = referencing relation

• R2 = referenced relation

• FK can also refer to the same relation (self-referencing).

d. Key Constraints

Key constraints ensure uniqueness and identify records:

• Superkey: Any set of attributes that uniquely identifies tuples.

• Candidate Key: A minimal superkey (no redundant attributes). meaning no attribute can be removed without losing uniqueness.

• Primary Key: One selected candidate key; must be unique and not null.

• Alternate Key: Candidate keys not chosen as the primary key.

Other schema-level constraints include:

• NOT NULL: Disallows missing values.

• UNIQUE: Ensures distinct values across rows.

• DEFAULT: Assigns default value if none provided.

• CHECK: Validates values using a condition.

  • Example: CHECK (age > 18).

Relational Database Schema and State

• A Relational Database Schema (S) is a set of relation schemas:
  S = {R1, R2, ..., Rm}

• A Relational Database State (DB) is a set of relation states at a point in time:
  DB = {r1, r2, ..., rm}

• Integrity Constraints (IC) are associated with S and must hold in every valid state of the database.

Valid vs. Invalid State

• A valid state satisfies all defined constraints.

• An invalid state violates one or more constraints.

Other Types of Constraints

These are application-specific rules or behavioral constraints not directly enforced by schema definitions:

a. Semantic Integrity Constraints

• Example: An employee's salary must be less than their supervisor’s.

• Enforced using application code, triggers, or assertions.

b. State Constraints

• Must hold true in any state of the database.

• Example: Stock quantity must always be ≥ 0.

c. Transition Constraints

• Govern how values may change from one state to another.

• Example: Employee salary must never decrease.

Operations of the Relational Model and Constraint Violations

In the relational model, operations are broadly divided into two categories:

1. Retrieval Operations

• Involve queries that return new relations based on relational algebra or SQL SELECT.

• Do not change the database state.

2. Update Operations

Update operations modify the database state. They include:

• Insert – Adds new tuples.

• Delete – Removes existing tuples.

• Update (Modify) – Changes values of existing tuples.

All update operations must maintain the integrity constraints defined on the database.

The Insert Operation

• Inserts a new tuple into a relation.

• The DBMS checks for violations of the following constraints:

Constraint Type	Description
Domain Constraint	Attribute value does not match its data type or domain.
Key Constraint	A duplicate primary or candidate key already exists.
Entity Integrity	A NULL value is provided for a primary key.
Referential Integrity	A foreign key refers to a non-existent tuple in another table.

The Delete Operation

• Removes one or more tuples from a relation.

• May violate referential integrity if other tuples (in child tables) reference the tuple being deleted.

Handling Referential Integrity Violations on Deletion

When a parent tuple is referenced by foreign keys in child relations, the DBMS can enforce referential integrity using one of three actions:

1. RESTRICT

• Prevents deletion if the tuple is referenced elsewhere.

• Ensures data consistency and avoids orphaned records.

• Example: You cannot delete an employee if their SSN is referenced in the WORKS_ON table.

2. CASCADE

• Automatically deletes all referencing tuples in child tables.

• Used when deleting a parent should also delete related child records.

• Example: Deleting an employee deletes all WORKS_ON records linked to them.

3. SET NULL / SET DEFAULT

• Sets the foreign key in referencing tuples to NULL or a default value.

• Useful if the relationship becomes optional.

• Limitation: If the foreign key is part of the primary key, SET NULL violates entity integrity.

Example DDL Implementation:

CREATE TABLE EMPLOYEE (
  SSN CHAR(9) PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE WORKS_ON (
  Pno INT,
  Essn CHAR(9),
  FOREIGN KEY (Essn)
    REFERENCES EMPLOYEE(SSN)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

The Update Operation

• Modifies attribute values in existing tuples.

• Requires a condition to identify which tuples to modify.

• Must preserve all relevant constraints.

Common Constraint Violations in Update

• Domain constraint: New value does not match expected domain.

• Key constraint: Update results in duplicate key.

• Entity integrity: Update sets a primary key to NULL.

• Referential integrity: Update breaks the link with referenced tuple.