Download Centralized semantic control and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
Centralized Semantic Integrity Control
A semantic integrity manager has two main components:
♦ The language for expression and manipulating integrity assertions.
♦ Enforcement mechanism that performs specific actions to enforce DB integrity and updates.
INTEGRITY ENFORCEMENT
Integrity constraints should be manipulated by the database administrator using a high-level language. These constraints can be defined either at relation creation time, or at any time, even if the relation already contains tuples. The effect of integrity constraint violation is to abort the violating transactions. The SQL standard provides means to express the propagation of update actions to correct inconsistencies, with the CASCADING clause within the constraint declaration. More generally, triggers (event-condition-action rules) can be used to automatically propagate updates, and thus to maintain semantic integrity. However, triggers are quite powerful and thus more difficult to support efficiently than specific integrity constraints.
In relational database systems, integrity constraints are defined as assertions. An assertion is a particular expression of tuple relational calculus, in which each variable is either universally ( ∀ ) or existentially ( ∃ ) quantified. Thus an assertion can be seen as a query qualification that is either true or false for each tuple in the Cartesian product of the relations determined by the tuple variables. We can distinguish between three types of integrity constraints:
♦ Predefined
♦ Precondition
♦ general constraints
PREDEFINED CONSTRAINTS
The more common constraints of the relational model:
♦ Not-null attribute
ENO NOT NULL IN EMP ♦ Unique key
(ENO, PNO) UNIQUE IN ASG
♦ Foreign key
A key in a relation R is a foreign key if it is a primary key of another relation S and the existence of any of its values in R is dependent upon the existence of the same value in S
PNO IN ASG REFERENCES PNO IN PROJ ♦ Functional dependency
ENO IN EMP DETERMINES ENAME
PRECOMPILED CONSTRAINTS
Express preconditions that must be satisfied by all tuples in a relation for a given update type
♦ General form: CHECK ON [ WHEN ] ♦ (^) Domain constraint : e.g., constrain the budget: CHECK ON PROJ (BUDGET>500000 AND BUDGET≤1000000) ♦ Domain constraint : On deletion, e.g., only tuples with budget 0 can be deleted: CHECK ON PROJ WHEN DELETE (BUDGET = 0) ♦ Transition constraint : e.g., a budget can only increase: CHECK ON PROJ (NEW.BUDGET > OLD.BUDGET AND NEW.PNO = OLD.PNO)
- OLD and NEW are implicitly defined variables to identify the tuples that are subject to update
GENERAL CONSTRAINTS
May involve more than one relation
♦ General form: CHECK ON : () ♦ Functional dependency: CHECK ON e1:EMP, e2:EMP (e1.ENAME = e2.ENAME IF e1.ENO = e2.ENO) ♦ Constraint with aggregate function: e.g., The total duration for all employees in the CAD project is less than 100 CHECK ON g:ASG, j:PROJ ( SUM(g.DUR WHERE g.PNO=j.PNO) < 100 IF j.PNAME=’CAD/CAM’ )
ENFORCING SEMANTIC INTEGRITY CONSTRAINTS
It consists of rejecting update programs that violate some integrity constraints. A constraint is violated when it becomes false in the new database state produced by the update transaction. A major difficulty in designing an integrity manager is finding efficient enforcement algorithms.
Pretests can be generated at constraint definition time, and enforced at run time when updates occur.
The method described by Nicolas [1982] is restricted to updates that insert or delete a single tuple of a single relation. The algorithm proposed by Bernstein et al. [1980a] and Blaustein [1981] is an improvement, although updates are single single tuple. The algorithm builds a pretest at constraint definition time for each constraint and each update type (insert, delete). These pretests are enforced at run time. This method accepts multi-relation, mono-variable assertions, possibly with aggregates. The principle is the substitution of the tuple variables in the assertion by constants from an updated tuple. Despite its important contribution to research, the method is hardly usable in a real environment because of the restriction on updates.
In the rest of this section, we present the method proposed by Simon and Valduriez [1986, 1987], which combines the generality of updates supported by Stonebraker [1975] with at least the generality of assertions for which pretests can be produced by Blaustein [1981]. This method is based on the production, at assertion definition time, of pretests that are used subsequently to prevent the introduction of inconsistencies in the database. This is a general preventive method that handles the entire set of constraints introduced in the preceding section. It significantly reduces the proportion of the database that must be checked when enforcing assertions in the presence of updates. This is a major advantage when applied to a distributed environment.
COMPILED ASSERTIONS
Triple ( R, U, C ) where
R relation
U update type (insert, delete, modify)
C assertion on differential relations
Example : Foreign key assertion
"g Î ASG, $j Î PROJ : g.PNO = j.PNO
Compiled assertions:
(ASG, INSERT , C1), (PROJ, DELETE , C2), (PROJ, MODIFY , C3)
where
C1:" NEW Î ASG+, $j Î PROJ: NEW.PNO = j.PNO C2:"g Î ASG, " OLD Î PROJ- : g.PNO ≠ OLD .PNO
C3:"g Î ASG, " OLD Î PROJ-, $ NEW Î PROJ+:g.PNO ≠ OLD .PNO OR OLD .PNO = NEW .PNO
The enforcement algorithm [Simon and Valduriez, 1984] makes use of pretests and is specialized according to the class of the assertions. Three classes of constraints are distinguished: single- relation constraints, multi-relation constrains, and constraints involving aggregate functions.
Let us now summarize the enforcement algorithm. Recall that an update transaction updates all tuples of relation R that satisfy some qualification. The algorithm acts in two steps. The first step generates the differential relations R + and R− from R. The second step simply consists of retrieving the tuples of R + and R− , which do not satisfy the pretests. If no tuples are retrieved, the constraint is valid. Otherwise, it is violated.
Example : Suppose there is a deletion on PROJ. Enforcing (PROJ, DELETE , C 2) consists in generating the following statement:
result ← retrieve all tuples of PROJ − where ¬ ( C 2)
Then, if the result is empty, the assertion is verified by the update and consistency is preserved.
