Relational Model Constraints and Relational Database Schema

In a relational database, constraints are the rules or "guardrails" that protect the integrity of data. They ensure that the information stored in the database is always correct, consistent, and valid by preventing invalid data from being entered.

A database state refers to all the data stored in the tables at a specific moment, and constraints ensure every state is a valid one.

Categories of Constraints

Constraints can be grouped into three main categories based on how they are defined and enforced.

1. Inherent (Model-Based) Constraints: These are fundamental rules that are built into the relational model itself. No need to define them explicitly.

   • Example: A relation cannot have duplicate tuples (rows).

2. Schema-Based (Explicit) Constraints: These are the most common type of constraints. They are defined directly in the database schema using Data Definition Language (DDL) when creating or altering tables.

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

3. Application-Based (Semantic) Constraints: These are complex business rules that cannot be expressed by schema constraints alone. They must be programmed and enforced within the application logic that interacts with the database.

   • Example: A business rule stating that a manager's salary must always be greater than the salary of any employee they manage.

4 . Data Dependencies : Constraints like functional dependencies and multivalued dependencies, primarily used during normalization.

Schema-Based Constraints (Explicit)

These explicit rules are the primary tools for maintaining data integrity.

They can be broken down by the scope they apply to: column, table, or across multiple tables.

1. Column-Level Constraints (Rules for Attributes)

These constraints apply to the values within a single column.

• Domain Constraint: Specifies that every value in an attribute must be an atomic value from its predefined domain (i.e., its data type and set of allowed values).

  • Example: An Age attribute must be a positive integer. An attempt to insert "Twenty" or -5 would be rejected.

• NOT NULL: Ensures that a column cannot have a NULL (empty) value. Every row must have an explicit value for this column.

• UNIQUE: Guarantees that all values in a column (or a set of columns) are unique across all rows in the table. NULL values are typically allowed.

• CHECK: A flexible constraint that allows for defining a custom condition that every value in the column must satisfy.

  • Example: In an Employees table, could have CHECK (Salary > 30000).

• DEFAULT: Assigns a default value to a column if no value is provided during an INSERT operation.

2. Table-Level Constraints (Rules for Rows)

These constraints apply to entire rows (tuples) or the relationships between columns within a single table.

Entity Integrity Constraint: Primary key of a table cannot contain NULL values. Since the primary key's purpose is to uniquely identify each row, allowing it to be NULL would make identification impossible.

Key Constraints: Ensure that rows can be uniquely identified.

• Super Key: Any set of attributes that, taken together, uniquely identifies a row.
• Candidate Key: A minimal super key (no attribute can be removed without losing the uniqueness).
• Primary Key: The one candidate key chosen to be the primary identifier for the table.

3. Database-Level Constraints (Rules Across Tables)

This constraint governs the relationships between tables, ensuring they remain consistent.

Referential Integrity Constraint (Foreign Key): A foreign key's value must either, match an existing primary key value in the referenced table, OR be NULL (if the relationship is optional and the column allows NULLs).

Example: In an Orders table, the CustomerID (foreign key) must match a valid CustomerID in the Customers table (referenced table), ensuring that no order can exist for a non-existent customer.

Foreign key, which is a column (or set of columns) in one table that refers to the primary key of another table is used to maintains a valid link between two tables.

• The referencing relation is the table containing the foreign key.
• The referenced relation is the table containing the primary key.

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

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. Does not change the database state.

2. Update Operations modify the database state. They include:

   • Insert – Adds new tuples.
   • Delete – Removes existing tuples.
   • Update (Modify) – Changes values of existing tuples.

Relational database systems employ integrity constraints to ensure the accuracy, consistency, and validity of data. These constraints define rules that the data must adhere to.

When users perform update operations, the Database Management System (DBMS) checks for potential constraint violations. Any operation that violates the rules is either rejected or triggers specific actions to maintain data integrity.

Constraint violations during each type of update operation:

I. Insert Operations

The INSERT operation is used to add new tuples (rows) into a relation (table). An insertion can potentially violate several types of integrity constraints.

Constraint Type	Description
Domain Constraint	Attribute value does not match its data type or domain.
Key Constraint	A duplicate primary or candidate which 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.

• Domain Constraint Violation : An INSERT operation violates a domain constraint if an attribute value provided for the new tuple does not fit the corresponding domain (e.g., wrong data type, value outside an allowed range).

-- Assuming a 'budget' attribute is defined as NUMERIC(12,2)

INSERT INTO department (dept_name, building, budget)
	VALUES ('Biology', 'Watson', 'High'); 
-- Violates domain constraint for budget

• Key Constraint violation: An INSERT operation violates a key constraint if the value provided for a primary key or unique key attribute (or combination of attributes) already exists in another tuple in the relation.

-- Assuming 'dept_name' is the primary key for 'department'

INSERT INTO department (dept_name, building, budget)
	VALUES ('Physics', 'Watson', 90000); 
-- If 'Physics' already exists as a department name, this violates the primary key constraint

• Entity Integrity Constraints: An INSERT operation violates entity integrity if any part of the primary key of the new tuple is provided as NULL.

-- Assuming 'Ssn' is the primary key for 'EMPLOYEE'

INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
	VALUES ('Cecilia', 'Kolonsky', NULL, 4); 
-- Violates entity integrity (Ssn cannot be NULL)

• Referential Integrity Constraints: An INSERT operation violates referential integrity if the value of any foreign key in the new tuple refers to a tuple that does not exist in the referenced relation.

-- Assuming 'Dno' in 'EMPLOYEE' is a foreign key referencing 'Dnumber' in 'DEPARTMENT'

INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
	VALUES ('Cecilia', 'Kolonsky', '677678989', 7); 
-- Violates referential integrity if no department with Dnumber = 7 exists

II. Delete Operations

The DELETE operation is used to remove existing tuples from a relation. DELETE operations primarily cause violations of referential integrity constraints.

• Referential Integrity Constraints: A DELETE operation violates referential integrity if the tuple being deleted is referenced by foreign keys in other tuples in the database.

• Deleting a department that still has employees assigned to it.

-- Assuming 'Dnumber' is primary key of 'DEPARTMENT'
-- 'Dno' in 'EMPLOYEE' references 'DEPARTMENT'

DELETE FROM DEPARTMENT WHERE Dnumber = 5;
 -- If there are employees with Dno = 5, this violates referential integrity

Handling Referential Integrity Violations on Deletion

Deletion of parent tuple causes a referential integrity violation when a parent tuple is referenced by foreign keys in child relation, the database designer can specify alternative actions in the DDL:

• RESTRICT (Default): The deletion is prevented. This is the most common default behavior. Ensures data consistency and avoids orphaned records.

• CASCADE: The deletion is propagated by automatically deleting all referencing tuples (i.e., tuples in the referencing relation that refer to the deleted tuple).

  • Example: If ON DELETE CASCADE is specified, deleting a department would automatically delete all employees assigned (referring) to that department.

• SET NULL: The referencing foreign key attribute values in the dependent tuples are automatically set to NULL. This option is only valid if the foreign key attribute is not part of the primary key of the referencing relation and is allowed to be NULL.

  • Example: If ON DELETE SET NULL is specified for Dno in EMPLOYEE, deleting a department would set the Dno of its employees to NULL.

• SET DEFAULT: The referencing foreign key attribute values are automatically set to their specified default value.

  • Example: If ON DELETE SET DEFAULT is specified for Dno in EMPLOYEE and a default is defined, deleting a department would assign its employees to the default department.

III. Update Operations

The UPDATE operation is used to modify the values of one or more attributes in existing tuples. Updates can violate domain, key, and referential integrity constraints.

• Domain Constraints: Changing an attribute value to one that does not conform to its defined domain or data type.

-- Attempting to set a numeric salary to a text value
UPDATE employee SET salary = 'fifty thousand' WHERE ssn = '123456789';

• Key Constraints: Modifying the value of a primary key or unique key attribute to a value that already exists for another tuple in the relation, or setting a primary key attribute to NULL. This effectively acts like a DELETE followed by an INSERT for the primary key.

-- Assuming 'Ssn' is the primary key for 'EMPLOYEE'
UPDATE EMPLOYEE SET Ssn = '987654321' WHERE Ssn = '999887777'; -- Violates primary key constraint if '987654321' already exists

• Referential Integrity Constraints: Modifying a foreign key attribute to refer to a non-existent primary key in the referenced relation. Also, modifying a primary key attribute in the referenced relation can violate referential integrity if dependent tuples in the referencing relation are not updated accordingly.

-- Modifying foreign key Dno to a non-existent department
UPDATE EMPLOYEE SET Dno = 7 WHERE Ssn = '999887777'; 
-- Violates referential integrity if department 7 does not exist

-- Modifying a primary key that is referenced by a foreign key
-- If Dnumber=5 is changed to Dnumber=8, without ON UPDATE CASCADE,
-- employees assigned to department 5 would now reference a non-existent department.

DBMS provides options for handling referential integrity violations caused by UPDATE operations:

• RESTRICT (Default): The update is rejected.

• CASCADE: The update of the primary key in the referenced relation is propagated to the corresponding foreign key attributes in the referencing relation.

  • Example: If ON UPDATE CASCADE is specified, changing Dnumber in DEPARTMENT from 5 to 8 would automatically update Dno to 8 for all employees previously in department 5.

• SET NULL: The referencing foreign key attribute values are set to NULL if the updated primary key is referenced.

• SET DEFAULT: The referencing foreign key attribute values are set to their specified default value if the updated primary key is referenced.