Explain Assertions in DBMS

Explain Assertions in DBMS

Answer:-

Assertions are used to specify additional types of constraints beyond the built-in relational model constraints. In SQL, users can declare these constraints using the CREATE ASSERTION statement in the Data Definition Language (DDL). Each assertion is given a name and is defined by a condition similar to the WHERE clause of an SQL query.

General form:

CREATE ASSERTION <Name_of_assertion> CHECK (<condition>)

For the assertion to be satisfied, the condition specified after the CHECK clause must return true. For example, to enforce the constraint that an employee’s salary must not be greater than the salary of the manager of the department they work for, you can use the following assertion:

CREATE ASSERTION SALARY_CONSTRAINT
CHECK (
    NOT EXISTS (
        SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
        WHERE E.Salary > M.Salary
        AND E.Dno = D.Dnumber
        AND D.Mgr_ssn = M.Ssn
    )
);

In this example, SALARY_CONSTRAINT is the name of the constraint, followed by the CHECK keyword and a condition in parentheses. The condition must hold true in every database state for the assertion to be satisfied. The NOT EXISTS clause ensures that the result of the query is empty, indicating that the condition is always true. If the result is not empty, the assertion is violated.