Integrity / Semantic Integrity / “Consistency”

1. Integrity / Semantic Integrity / “Consistency” The concern with the correctness or accuracy of data in DBMS. One aims to ensure that data in database is reasonable. Integrity is compromised by system failures, and by invalid updates. Violations are due mainly to program errors. Integrity subsystem:- • monitors transactions (detection) • takes action if violation occurs http://csiweb.ucd.ie/staff/acater/comp30150.html

2. Integrity system needs set of rules to define • what errors to check for • when to check • what to do if error detected e.g. Rule22: After updating S.Status: [trigger condition] S.Status > 0 [constraint] else { set error code [violation response] reject} http://csiweb.ucd.ie/staff/acater/comp30150.html

3. Rules can be coded in high level language and incorporated into System Data Dictionary, so that validation is handled by the system and not just left to application programs. If checking is left to application programs, it can give rise to problems: • each application program must “trust” other ones • many people will be coding the same checks - duplicating effort • checks are “buried in code”, not explicit Semantics of database is a matter of shared understanding between users, may be implied by integrity constraints expressed by DBA e.g. • hours worked >= 0 and <= 120 • grades are A,B,C,D,E • times: 0900---1730 Such constraints do not guarantee correctness, only reasonableness. e.g. one could still enter 60 hours instead of 6 hours for hours worked. http://csiweb.ucd.ie/staff/acater/comp30150.html

4. Types of integrity constraint 6 Different ways of classifying realistic constraints • limit or range • Values of an attribute must fall within certain bounds (limit), • or must be one of a certain range of values, e.g. A, B, C, D, E. • unconditional or conditional • Constraints may apply (need to be enforced) universally, • or only when some condition is true. • e.g. salary < 40000 if grade = “clerk” • record or set • Does it apply to each individual record • e.g. salary <= 460000 • or to a set of records • set of managers must be subset of set of employees • average salary of hospital consultants > 215000 http://csiweb.ucd.ie/staff/acater/comp30150.html

5. static or transitional • Does constraint specify a property of a correct state of the db, • or does it specify a property of a correct transition. • e.g. a new salary must exceed an old salary • selective or general • Constraints may be enforced selectively, after certain maintenance operations (update, delete, insert...); or may be enforced at any time. • immediate or deferred • Constraints that must hold after performing any maintenance operation are immediate; • those that must hold only after completion of a transaction are deferred. • eg transaction transferring funds from one account to another - database is consistent only after both maintenance operations are complete. http://csiweb.ucd.ie/staff/acater/comp30150.html

6. Integrity Model Similar to security model using access matrix, but integrity rules are independent of which subject is making the request. Rules are tuples: (O, t, c, p, ap) • O: data object • t: access type • c: condition governing applicability of p • p: assertion (semantic constraint) that must be true for O • ap: auxiliary procedure, optionally specifying what system should do if c is true but p is not Auxiliary procedure allows DBA to specify a triggered procedure (daemon) to be invoked whenever a maintenance operation is executed, or a certain state of the database is reached. http://csiweb.ucd.ie/staff/acater/comp30150.html

7. Triggers, where available, can be used for various purposes: • performing the actual intended checking for overcomplex integrity constraints • logging selectively; alerting management • allowing the database to maintain itself automatically, eg to keep counts of records - useful information for query optimisation There are potential problems with such triggers: • infinite loops: if trigger altering db causes another trigger to alter db ... • access rights of trigger - user? or DBA? • potential for confusion over responsibilities http://csiweb.ucd.ie/staff/acater/comp30150.html

8. Implicit integrity constraints Some constraints are directly built into data structure • systems supporting keys will check, as a record is inserted into DBMS, to prevent duplicates • RDMBS will check entity integrity constraint and referential integrity contraints The aim of some developments in data models is to allow database designers to capture (as implicit integrity constraints) more of the real-world semantics of the situations being modelled. Always, an issue with integrity constraints is the need for efficient methods to validate data when checking for violation of the constraints. http://csiweb.ucd.ie/staff/acater/comp30150.html

9. SQL: Checks, Domains, Assertions, Triggers • Can create new named domains, as alias for existing domain, or with a restriction on acceptable values drawn from it, optionally with a default value • Can add elementary checks to be performed only upon insert and/or update operations affecting an attribute of a relation • Can define assertions which are stated as SQL queries, and so can use the full power of SQL retrieval operations. (Simple checks should be associated with individual attributes where possible, for efficiency of DBMS.) • (And can invoke triggers, in active databases) • CREATE DOMAIN CLUBNUM AS INTEGER • CHECK (CLUBNUM > 0 AND CLUBNUM < 2001) CREATE TABLE CLUB ( ………, NMEMS INTEGER CHECK NMEMS > 3, ………) http://csiweb.ucd.ie/staff/acater/comp30150.html

10. SQL: Complex checks, Assertions • Checks may be required that involve more than one attribute of a relation • (say, ncommittee < nmems+3) • These can be defined as part of the table, rather than part of one attribute • SQL supports even more complex integrity checks through assertions. The technique involves writing a query that selects tuples that violate a condition, and checking that no such tuples exist. CREATE ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS (SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPT D WHERE E.DEPTNUM = D.NUM AND E.SALARY > M.SALARY AND M.ID = D.MANAGERID)) http://csiweb.ucd.ie/staff/acater/comp30150.html

11. SQL: Triggers (not necessarily implemented) Triggers may involve performing SQL statements or running external programs. CREATE TRIGGER TOTALSAL AFTER UPDATE OF SALARY ON EMPLOYEE REFERENCING OLD ROW AS O, NEW ROW AS N, FOR EACH ROW WHEN (N.DNO IS NOT NULL) UPDATE DEPT SET TOTAL_SAL = TOTAL_SAL + N.SALARY - O.SALARY WHERE N.DNO = DEPTNUMBER); “Statement-level triggers” (saying FOR EACH STATEMENT) take effect after a transaction has performed its update/insert/delete operations. “Row-level triggers” take effect after an insert/update/delete operation on a tuple in a relation. http://csiweb.ucd.ie/staff/acater/comp30150.html