Database

1. Database Chapter 2 – The Rule of Rules - Integrity Fabian Pascal, Practical Issues In Database Management.

2. The issues • Understanding business rules is critical in the implementation of integrity in databases • Integrity constraints and business rules -the distinctions • Classification scheme for constraints • DBMS integrity enforcement • Declarative vs procedural constraints • Integrity support in SQL

3. Fundamentals • Consider a hotel reservations DB • Reservation identified by reservation number (res#), made for room with room number (room), has a scheduled arrival date (arr_date) and a scheduled departure date (dpt_date)

4. Keys • Note, the table has two composite natural candidate keys • What are they? • {room, arr_date} and {room, dpt_date}

5. Keys • We could have used either of these key values however as is usual practice we define a simple surrogate key (res#). • Why? • Simplicity and Efficiency

6. Business Rules • Imagine hotel opened on 01/08/1995 has 350 rooms. The business rules are; • R1. Reservation numbers are between 1 and 9999999 • R2. room numbers are between 1 and 350 • R3. Arrivals are later than 01/08/1995 • R4. For all reservations, scheduled departure date is later than the arrival date • R.5 Reservations have distinct reservation numbers • R6. For every reservation, the combination of room number and arrival date is unique • R7. For every reservation, the combination of room number and departure date is unique • R8. Reservations for the same room do not overlap

7. How? • How do you propose we implement these business rules? • Any ideas?

8. Integrity constraints • Business rules are represented in the database by implementing integrity constraints • An integral part of the logical model • Their role – to constrain database values to only those that yield rows representing true facts.

9. Domain constraints • Domains are just data types. Or named pools of values. Remembering from last week, they consist of at least • one possible value representation, • a permissible value set and • an applicable operator set. • The constraints representing business rules R1, R2 and R3 are domain constraints

10. Domain constraints • D1. DOMAIN res# INTEGER(7) > 0 and <= 9999999 • D2. DOMAIN room INTEGER (3) >= 1 and <= 350 • D3. DOMAIN res-date DATE >= 01/08/1995

11. Column Constraints • A column constraint specifies the domain by which the column is defined so restricting the values to just those within the domain • So, arrival date and departure date are defined by the res-date domain. • Room and Res# columns are defined by the room and res# domains

12. Column constraints • C1: reserve.res# DOMAIN res# • C2: reserve.room DOMAIN room • C3: reserve.arr_date DOMAIN res_date • C4: reserve.dpt_date DOMAIN res_date

13. Table constraints • Table constraints apply to a single table • They can be single row or multi-row table constraints • The table constraint representing R4 is a single row constraint (check constraint) • T1: reserve.dpt_date >= reserve.arr_date

14. Multirow constraints • Multirow table constraints representing R5, R6 and R7. • These are uniqueness constraints (key constraints) • R5 is represented simply by a primary key • T2: reserve.res# PRIMARY KEY

15. Unique constraints • R6 and R7 are represented by uniqueness constraints • T3: reserve.(room, arr_date) UNIQUE • T4: reserve.(room, dpt_date) UNIQUE

16. R8 – the tricky one • The multirow constraint representing R8 is more complex • The constraint has to prevent an overlap of reservations • T5: r2.res# <> r1.res# AND r2.room = r1.roomAND r2.arr_date < r1.dpt_dateAND r2.dpt_date > r1.arr_date

17. What’s that in plain english? • There should not be any two rows r1 and r2 with the same room values such that the arr_date value in r2 is less than or equal to the dpt_date value in r1 or the dpt_date value in r2 is greater than or equal to the arr_date value in r1.

18. Database constraints • These constraints span two or more tables • Implemented referential integrity constraints or foreign key values that match primary key values

19. Database constraints • If we had a customer table{cust#, name, res#, ... } • res# would be a foreign key referencing the primary key in the reserve table • DB1: customers.res# FOREIGN KEY REFERENCES reserve.res#

20. Correctness • Of course implementing these constraints only partially ensures values are correct (represent true propositions or facts). • What the database can know, if correctly implemented, is that any row with any values violates any of the integrity constraints declared for that table and therefore violates the business rule

21. Database predicate • Pascal argues the “meaning” of the database can be represented by the sum total of all the integrity constraints (see page 53). • So all the table predicates ORed and ANDed together. • ... Hmm, academically interesting I suppose.

22. Base v Derived Constraints • Constraints on base tables are declared by the database designer • Derived tables (views) inherit their constraints from base tables • Oracle has some interesting conditions regarding updatable views • For example views with pseudocolumns or expressions are not updatable • Restrictions on updatable join tables views

23. Integrity enforcement • During every update operation • DBMS checks table’s after-update state against the constraints • If a violation is discovered DBMS responds to prevent. • No overlap rule – DBMS compares every row in the after-update state of RESERVE to all its other rows – effectively a self-join, and rejects any offending rows • So, no overlap rule involves checking multiple rows in a single table – hence a multirow table constraint.

24. Integrity Rules • Integrity rules are the DBMS constraint enforcement mechanism – they consist of • A name, for eg. R8 • An integrity constraint for eg. T5 • A checking time (immediate or deferred) (note these are configurable in SQL) • A violation response (rejection or some other compensating action)

25. DBMS support • Data language facility to declare constraints of all types • Inference of constraints on derived tables (views) • Recording of integrity rules in data dictionary • Support of all table operations for rule checking • Deferrable checks • User definable violation responses (where appropriate)

26. Declarative v Procedural • Pascal advocates for declarative • But, the non-overlap rule is not possible in Oracle without triggers. • Triggers can interact unpredictably with nonprocedural constraints (remember the mutating table problem from last year).

27. SQL and Integrity • PK and FK declarations • CHECK Constraints • CREATE DOMAIN is CREATE TYPE in Oracle • Table and database constraints are called assertions and are implemented by triggers usually. • Table rules can also be embedded into CREATE TABLE • Constraints can be named, or the DBMS creates an arbitrary name • Checking is usually supported IMMEDIATE or DEFERABLE • Violation response is usually just rejection

28. The OVERLAPS operator • An undocumented function in Oracle. • So should we use it? (should we used undocumented functions?) • See Kevin Meade’s very good article • http://www.orafaq.com/node/2067 • Nothing similar in SQLServer • .Net - One of the entity SQL set operators (but this is at the application level)

29. Overlaps trigger Create trigger nooverlap on reserve for insert, update as Begin if exists (select * from inserted y, reserve x where y.room#= x.room# and y.res# <> x.res# and y.dpt_date > x.arr_date and y.arr_date < x.dpt_date) Rollback End

30. Overlap as a check constraint • Pascal argues it should be able to be implemented as a declarative constraint. • But, not possible in most RDBMS • We can tell trying to formulate this logic directly in SQL is too much for most of us (even me)

31. Conclusions • Data language ought to support where possible integrity constraints of arbitrary complexity declaratively rather than procedurally. • Stored procedures and triggers are preferable to enforcing integrity in the application (why?)

32. Users are advised to • Design normalised databases, to minimise complex constraints. • Familiarise themselves with logic and teh four constraint categories (domain, column, table and database) • Choose a DBMS with better declarative integrity support • Document all procedural and any application level constraints • Be alert to undetected integrity violations