Integrity Constraints, Triggers & Indexing

Integrity Constraints, Triggers & Indexing

To maintain the accuracy, consistency, and reliability of data in a database, Database Management Systems (DBMS) use various rules and mechanisms. The most fundamental among these are Integrity Constraints and Triggers.

1. Integrity Constraints

1.1 Types of Integrity Constraints

A. Domain Integrity Constraint

• Ensures that every value in a column falls within a predefined domain (data type, length, or format).
• Example: Age must be an integer, or Gender must be 'M' or 'F'.
• Implemented using data types, CHECK constraints, or DEFAULT constraints.

B. Entity Integrity Constraint

• Ensures that each row in a table is uniquely identifiable.
• Rule: The Primary Key of a table cannot contain a NULL value.
• Reason: If the primary key is NULL, we cannot uniquely identify that row, defeating the purpose of a primary key.

C. Referential Integrity Constraint

• Maintains consistency between two related tables. It is enforced using Foreign Keys.
• Rule: A Foreign Key value in a referencing table must either match a Primary Key value in the referenced table, or it must be NULL (if permitted).
• You cannot add a record to a child table with a foreign key that doesn't exist in the parent table. Similarly, you cannot delete a record from a parent table if related records exist in the child table (unless using CASCADE actions).

D. Key Constraint

• Specifies that an attribute or set of attributes must uniquely identify a tuple.
• Enforced using UNIQUE constraints. Unlike a Primary Key, a UNIQUE constraint allows NULL values (usually only one).

2. Triggers and Assertions

2.1 Triggers

• Events: INSERT, UPDATE, or DELETE.
• Timing: Triggers can be executed BEFORE the event (e.g., to validate or modify data before insertion) or AFTER the event (e.g., to log an audit trail or update a related summary table).
• Level:
  • Row-Level Trigger: Fires once for every row affected by the triggering statement (using the FOR EACH ROW clause). Uses OLD and NEW qualifiers to access values.
  • Statement-Level Trigger: Fires exactly once per statement, regardless of how many rows are affected.
• Use Cases: Enforcing complex business rules, maintaining audit logs, auto-generating derived values, and synchronizing replicated tables.

2.2 Assertions

• An assertion is a predicate expressing a condition that the database must always satisfy.
• Unlike triggers which are tied to specific tables and events, assertions are stand-alone schema objects that apply to the entire database.
• Example: CREATE ASSERTION Check_Salary CHECK (NOT EXISTS (SELECT * FROM Employee WHERE Salary < 0));
• If an operation violates an assertion, the operation is aborted.

3. Database Indexing

3.1 Types of Indexing

1. Primary Index: Defined on an ordered data file. The index is based on the primary key of the table.
2. Clustering Index: Defined on an ordered data file where the index is based on a non-key field (e.g., Department ID). There is only one clustering index per table.
3. Secondary Index: Defined on an unordered data file. It provides an alternate way to access data. A table can have multiple secondary indexes.

3.2 Indexing Structures

A. B-Tree (Balanced Tree)

• A self-balancing search tree where each node can contain multiple keys and pointers.
• Characteristics: Data records can be stored in both internal nodes and leaf nodes. This means searching can stop early if the key is found in an internal node.
• Drawback: Range queries are slow because traversing between leaf nodes requires moving up and down the tree structure.

B. B+ Tree

• An advanced version of the B-Tree and the most widely used indexing structure in modern relational databases.
• Characteristics: All data pointers (actual database records) are stored only at the leaf nodes. Internal nodes store only keys to guide the search.
• Advantage: Leaf nodes are linked together sequentially like a linked list. This makes range queries (e.g., WHERE Salary BETWEEN 40000 AND 60000) extremely fast, as the database simply finds the starting point and traverses the linked list.

C. Hashing

• Uses a hash function to map a search key directly to a specific block address (bucket) on the disk.
• Advantage: Extremely fast for exact-match queries (WHERE ID = 101). Expected time complexity is O(1).
• Drawback: Inefficient for range queries, as consecutive keys may be hashed to completely different disk locations. Prone to hash collisions.