Business Rules: Theory

1
Business RulesTheory Implementation

• (What and How)
• Toon Koppelaars
• Centraal Boekhuis, Netherlands

2
YAPPABRA

• Yet
• Another
• Person
• Presenting
• A
• Business
• Rules
• Approach

3
The Relational Model
Timeline
4
Overview

• What are Business Rules?
• A formal theory for this
• Including a classification
• Different implementation strategies
• Guidelines for a strategy (by class)
• Issues to watch out for
• Conclusion

5
What Are Business Rules?

• Some quotes
• A precisely articulated requirement that is both
  machine and user readable
• BRs include Presentation, Database and
  Application rules
• A constraint / test exercised for the purpose of
  maintaining the correctness of persistent data
• Set of conditions that govern a business event
  so that it occurs in a way that is acceptable

6
What Are Business Rules?

• Some quotes
• A precisely articulated requirement that is both
  machine and user readable
• BRs include Presentation, Database and
  Application rules
• A constraint / test exercised for the purpose of
  maintaining the correctness of persistent data
• Set of conditions that govern a business event
  so that it occurs in a way that is acceptable

7
BRs In This Presentation
A model of TRW
The Real World
Queries Transactions
Map
Persistent Data Store
Application Logic
The craft of constructing Application Systems
8
BRs In This Presentation

• Assertions that constrain possible values in
  Database State
• Assertion ? States WHAT, not HOW
• Database State ? Set of current values of our
  tables
• Fully expressable in terms of DB-skeleton
• DB-skeleton ? Table/column structure
• If not fully yet
• Not considered Business Rule
• Either,
• Re-assert (narrow down rambling), or
• Change (extend) table structure

9
BRs In This Presentation

• This is not to say that other ramblings arent
  relevant
• My point isthey must be clearly separated from
  Rules that constrain values in a database state

10
How Versus What

• If the supplier (S) status is updated to value
  less than twenty then all corresponding shipments
  (SP) must have quantity less than 500
• If shipments quantity is updated to value
  greater than 499 then corresponding suppliers
  status must be more or equal than 20

11
Not Fully Constraining Database State

• If customer is high-risk, then services mgr must
  have been notified
• Rewrite to fully assert
• Null
• Extend DB-skeleton to fully assert
• Add services-mgr-notifications (smn) table
• If customer is high-risk, then an entry in
  SMN-table must be present

Cust. table
Cust. table
Notif. table
Risk level
12
A Formal Theory For This

• Constraining
• Possible Values
• In a
• Database State

13
A Formal Theory For This

• What is a Database State?
• Database State is made out of tables
• Table is made out of tuples
• Tuple is made out of attributes
• Attribute takes value out of some value set
• The set of allowed Database States is called
  Database Universe
• Database fairly complex variable
• Database universe its data type

14
Defining This Data Type
15
A Formal Theory For This

• Bottom-up construction of a DB-Universe
• Database Skeleton
• Per table, what are the attributes
• Per Table, per attribute, what is its value set

S
Database Skeleton
16
Attribute Constraints

• Definition of Attribute Value Sets
• Assertions that values of attributes should
  comply with
• Scope of assertion the attribute

Admitted values for Job are MANAGER, CLERK
and SALESMAN
t(JOB) ? 'MANAGER','CLERK','SALESMAN'
17
A Formal Theory For This

• Possible tuples (Cartesian product of value
  sets)
• tuple constraints ? Tuple Universes

S
Database Skeleton
18
Tuple Constraints

• Assertions between different attributes in same
  tuple
• Scope of assertion the tuple

Managers should have more than 15 vacation days
t(JOB)'MANAGER' ? t(VACATION_DAYS)gt15
19
A Formal Theory For This

• Possible tables (Powerset of tuple universe)
• table constraints ? Table Universes

S
Database Skeleton
20
Table Constraints

• Assertions between different tuples in same table
• Scope of assertion the table

All Clerks should have less vacation days than
Salesmen
(?c?T c(JOB) CLERK ? (?s?T s(JOB)
SALESMAN ? c(VACATION_DAYS) lt
s(VACATION_DAYS) ))
21
A Formal Theory For This

• Possible DB-states (Cartesian product of table
  universes)
• database-constraints ? DB-Universe

S
Database Skeleton
22
Database Constraints

• Assertions between different tables in same
  database state
• Scope of assertion the database state

The vacation taken may not exceed the maximum
allowed
(?t?v(EMP) (?s s?v(VAC) ? s(EMPNO)t(EMPNO)
? d?WORKDATE s(FIRST_DAY)?d?s(LAST_DAY)?
) ? t(VACATION_DAYS) )
23
A Formal Theory For This

• Possible DB-state changes (Directed graph on top
  of DB states)
• transition-constraints ? Transaction Universe

S
Database Skeleton
24
Transition Constraints

• Assertions that constrain database state
  transitions
• Scope of assertion Begin Result DB states

• Allowed vacation days can only increase
• Updates of unique identifiers not allowed
• Newly inserted VAC records must be most recent

25
Important Notice

• A Business Rule cannot be classified without
  context of DB-skeleton
• Mandatory Child Rule
• Normally Multi-Table (Database) constraint,
  however
• Extend parent with one instance of child fields,
  and suddenly this becomes set of Attribute
  constraint(s)

26
Benefits Of This Approach

• Scope-of-data-constrained drives classification ?
  closely related to implementation issues
• Obviously more issues with more scope
• Formal specification ? never cause for ambiguity
• Managers always manage themselves
• What are managers?
• What does manage themselves mean?

27
Benefits Of This Approach

• Warning! (and benefit)
• Do NOT confront users with this formalism
• IT-professional deals with users in informal way
• Continually tries to map ramblings to model
• By doing so the right counter-questions will
  arise

The Real World
Map
Counter
Informal
The Formal Model
28
Moving On To The HOW

• What are Business Rules?
• A formal theory for this
• Including a classification
• Different implementation strategies

Valid
Check
DML1 DML2
S1
S2
Transaction
Begin State
Result State
29
Implementation Strategies
Other Tiers
Database Tier
Pre-DML vs. Post-DML
30
Post-DML Implementation

• Declarative
• Create table syntax
• Easy, efficient, maintainable implementation
• What ? How!
• Procedural
• Triggers associated with table
• Insert, Update, Delete
• Before DML-statement, after statement
• Before each affected row, after each row
• What ? How! ? potentially complex

/
31
Attribute Constraints

• Use declarative (in-line) check-clause
• Can be given a constraint name
• Unknown due to NULLs evaluate to TRUE

Create Table Emp (empno number(4,0) not null
check(empnogt0), ename varchar2(20) not null,
job varchar2(10) not null check(job in
('MANAGER','CLERK','SALESMAN')), vacation_days
number(3,0) not null check(vacation_daysgt10) )
32
Tuple Constraints

• Use declarative (out-of-line) check clause

Create Table Emp (empno number(4,0) not null
ltempno attribute constraintsgt,
ename varchar2(20) not null ltename attribute
constraintsgt, job varchar2(10) not null ltjob
attribute constraintsgt, vacation_days
number(3,0) not null ltvacation_days attribute
constraintsgt, check(jobltgt'MANAGER' or
vacation_daysgt15) )
JOB'MANAGER' ? VACATION_DAYSgt15 A ? B, is
logically equivalent to ?A ? B
33
Table Constraints (1)

• Declarative ? Only UK, PK, self-ref. FK
• All others require procedural implementation

Create Table Emp (empno number(4,0) not null
ltempno attribute constraintsgt,
ename varchar2(20) not null ltename attribute
constraintsgt, job varchar2(10) not null ltjob
attribute constraintsgt, vacation_days
number(3,0) not null ltvacation_days attribute
constraintsgt, ltEmp tuple constraintsgt, primary
key (empno))
34
Table Constraints (2)

• Implementing procedural checks
• Identify violating DML statements
• Derive minimal check for each case
• Build triggers
• Investigate concurrency

35
Table Constraints (3)

• Possibly violating DML-statements
• Scan Predicate (What) for involved columns
• Build cases table
• 1 Insert
• 1 Delete
• 7 Updates
• 23-1

/ No directly adjacent vacation records allowed
/(?s,t?T (s(EMPNO) t(EMPNO) ? s(FIRST_DAY) lt
t(FIRST_DAY)) ? (?d?WORKDATE s(LAST_DAY) lt d lt
t(FIRST_DAY)) )
36
Table Constraints (4)

• Derive minimal check
• Can be done formally using rewrite rules of
  predicate calculus
• Start with Insert, then Delete, then Update(s)
• If delete requires no checkthen all updates will
  require insert-checks
• Challenge Updates ? extra Transition Constraints
• Build PL/SQL function(s) implementing check(s)
• Typically has PK, rowid or other column values of
  affected row(s) as input parameter

37
Table Constraints (5)

• Build table triggers
• Call check functions from (before) row-triggers
• Check-Functions must read other rows in same
  table
• Row triggers not allowed to read mutating table

Before statement resets package variable B/A
row creates entry in package variable After
statement loops over entries, calls check
DML
ORA-04091
38
Table Constraints (6)

• Investigate concurrency
• User A
• insert into vac values (1,sysdate,sysdate5),
  succeeds
• User B
• insert into vac values (1,sysdate6,sysdate8),
  succeeds
• Users A and B commit (in whatever order)
• Violation of no-directly-adjacent-vacaction-record
  s
• In this case triggers should exclusively lock the
  parent employee-record

39
Remark On Concurrency

• General rule
• Lock all data used in check-function
• Select .... for update gives exclusive locks
• For constraint checking read locks will suffice
  in most casesA read lock
• Can be held by many TXs on same row
• Prevents other TXs from obtaining write lock on
  that row

Read locks are (still) not available in Oracle
40
Database Constraints

• Declarative ? Only Foreign Key gt1 table
• All others procedural
• Same process used with table constraints
• Investigate cases per table involved
• Potentially many cases to be investigated( 2n1
  2m1 )

41
Transition Constraints

• Usually simple
• Salary not allowed to decrease
• Use old and new available in row-triggers
• Needs further sub-classification
• And implementation guidelines

42
The Cause For Deferring
43
The Cause For Deferring SQL

• DML-statements operate on a single table
• Some transitions require DML on gt1 table
• Database constraints
• Each product has at least two components and
  Each component belongs to a product
• Must choose one to be implemented deferred

44
The Cause For Deferring SQL

• DML-statements operate in a single way
• Either Insert, or Update, or Delete
• Some transitions require gt1 type of DML on same
  table (different tuples)
• Table constraints
• Number of type A products plus twice the number
  of type B products must equal zero or one
  hundred
• Start with inserting 100 type A products
• Cannot introduce type B products without
  temporarily violating constraint

45
Conclusions

• Separate between ramblings
• Dealing with database states
• Mapping outside database
• Scope-of-data constrained by assertion
• Practical classification, correlates to
  implementation
• Regarding implementation (post-DML)
• Big thing missing read row locks
• Elegant solution for deferred checking

46
Conclusions

• When looking at vendors
• Rule syntax?
• Classification?
• Minimal checks?
• Deferred checks?
• Declarative features?
• Concurrency?
• What versus How?

47
Business Rules Theory Implementation t.koppelaa
rs_at_inter.nl.net
48
Additional Material
49
What Makes a Good Skeleton?

• Given Database Skeletons Sk1 and Sk2
• A guideline could bethe complexity,
  composition, (weighted) number ofBRs involved
  in DBU based on Sk1 versus Sk2
• But consider alsowhat are the query specs
  involved in the application logic?
• Complexity of these varies too with different
  Database Skeletons

50
Comparison With Others

• Comparison with other classifications
• Barbara von Halle
• Chris Date

51
Comparison With Others

• Barbara von Halle
• Terms
• Facts
• Rules
• Mandatory constraints
• Guidelines
• Action-enablers
• Computations
• Inferences

52
Terms (von Halle)

• A noun or noun phrase with an agreed upon
  definition
• Customer
• Customer Credit Rating Code
• Days of the work week
• Female
• Mix of Table names, Attribute names, Attribute
  value sets, and elements thereof

53
Facts (von Halle)

• A statement that connects terms, through
  prepositions and verbs into sensible
  business-relevant observations
• Customer can place order
• Line item is for product
• Customer has credit rating code
• Information that aids in designing the DB-skeleton

Terms Facts (Documented) DB-skeleton
Attribute Universes
54
Mandatory Constraints (von Halle)

• A complete statement that expresses a
  unconditional circumstance that must be true (or
  not true) for the business event to complete with
  integrity
• Customer cannot have more than 10 open orders
• Amount of order cannot exceed credit limit
• Tuple, Table, Database constraints
• Maybe even Transaction constraints too
• Depends on the unconditional circumstance

55
Guidelines (von Halle)

• A complete statement that expresses a warning
  about a circumstance that should be true or not
  true
• Customer should not have more than 10 open orders
• Not a BR does not constrain a DB-state
• Something the UI should/might signal
• Then up to the user to allow (commit) or not
  (rollback)
• Could be turned into a Transaction constraint
• By introducing a guidelines_violated table

56
Action Enabler (von Halle)

• A complete statement that tests conditions and
  upon finding them true, initiates a business
  event, message or other activity (outside the
  db)
• If order is valid, then initiate the place order
  process for the order
• If customer is high-risk, then notify services
  mgr
• Not fully constraining DB-state personal
  preference to always force these to be
  Transaction Constraints
• By introducing a actions to be performed table
• Build agent that monitors table and performs the
  actions

57
Computation (von Halle)

• A complete statement that provides an algorithm
  for arriving at the value of a term
• Total-due of order sum of line-item amounts
• If term is stored (ie. part of DB-skeleton)
• Term is redundant and the computation is a static
  constraint (tuple, table or database)
• Else,
• This probably becomes centralised code to be used
  by other APP- or BRC-code

58
Inference (von Halle)

• A complete statement that tests conditions and
  upon finding them true, establishes the truth of
  a new fact
• If customer has no outstanding invoices, then
  customer is of preferred status
• If customer is of preferred status then new
  customer order qualifies for 20 percent discount
• Definition of virtual (derivable) table
• Centralised Views to be used by other code
• Second example is strange though
• Has the ring of a Transaction constraint (depends
  on semantics of qualifies for)

59
Comparison With Others

• Chris Date
• Presentation rules
• Application rules
• Database rules
• Domain (or Type) constraints
• Column (or Attribute) constraints
• Table (or Relvar) constraints
• Database constraints

Transition constraints ignored for simplicity
60
Presentation Rules (Date)

• Rules that apply to the presentation aspects of
  an application
• To do with the end user interface displayed
  forms, printed output, error messages, and so
  forth
• To facilitate uniformity and consistency of these
  across applications
• Many people tend not to include such rules under
  the general heading of BRs
• So do I.
• The IDE should facilitate the uniformity and
  consistency

61
Application Rules (Date)

• Rules that apply to the aspects specific to the
  business function per se
• ltno clear examples givengt
• Frankly, its hard to draw a sharp dividing line
  between database rules and application rules. I
  plan to treat them together
• ?
• My guess to be re-asserted, or in need of
  db-skeleton extension

62
Domain Constraints (Date)

• Specify legal values for a domain (type)
• Can be either system defined types, or user
  defined types (of arbitrary complexity)
• Quantity type legal values must be such that
  they can be represented by, precisely, the
  integers 1, 2, , 5000
• Attribute constraints defining the attribute
  allowed values set

63
Column Constraints (Date)

• Specify legal values for a given column
  (attribute)
• A statement to the effect that column values are
  drawn from some given domain
• Ordered_quantity takes its values from the
  quantity type
• Binding of attributes to their allowed value sets
• Where is the constraint here?

64
Table Constraints (Date)

• Can be as complicated as you like, provided only
  that it refers to one table
• S.statusgt0 and s.statuslt100
• If S.cityLondon then s.status20
• If two supplier rows have equal S values, then
  they must be the dame row
• Tuple and Table constraints
• Weird first example, why not a domain?

65
Database Constraints (Date)

• Can be as complicated as you like, provided only
  that it refers to at least two tables
• Foreign key
• Suppliers with status X must not ship any part in
  quantity greater than Y
• Database Constraints