E
NFORCING
B
USINESS
R
ULES BY THE
A
PPLICATION OF
T
RIGGER
C
ONCEPTS
G. KNOLMAYER* H. HERBST* M. SCHLESINGER*
*
Institute of Information Systems, University of Berne, Hallerstr. 6, CH-3012 Berne, {knolmayer¦herbst¦schlesi}@iwi.unibe.ch
Abstract. Business Rules are important organizational elements which can be trea-ted diffently in information systems. One possible implementation alternative are trigger mechanisms provided by active database systems. The paper summarizes our research on conceptual and imple-mentational aspects of business rules.
1 Introduction
The concept of event-driven information systems (IS) has gained much attention in recent years as well in computer science as in business administration. Central notions in this concept are „business rules“ and „triggers“. However, there are remarkable diffe-rences e.g. between the vision of process orientation in business process reengineering, the discussion and development of active database mechanisms in the DBMS community and the present state of DBMS implementations.
The term „business rule“ has been defined insuffi-ciently and used heterogenously ([Appl88]; [SaVo91]). We define business rules with reference to [BBGS90] as statements about how the business is done, i.e., about guidelines and restrictions with res-pect to states and processes in an organization [HeKn94].
In the management literature ([Sche94]; [MeHo86]) rather broad concepts have been presented on the basis of trigger mechanisms. This is in a certain contrast to the functionalities provided by trigger implementations in commercially available database systems.
Goals of the research project are to join these dif-ferent points of view and to provide empirical evidence about the state of the art of applying these concepts in Swiss companies.
2 Subtasks of the Research Project
To achieve the objectives of the project, several subtasks have been defined:
• Establishing a system for classifying business rules on a conceptual level.
• Comparison of selected methods for decribing business rules.
• Comparison of commercially available relational database systems with respect to trigger mecha-nisms.
• Conducting a survey on practical experiences with applying database triggers.
• Case studies to extract and analyze business rules implemented in practically applied information sy-stems.
• Evaluation of the applicability of available data-base trigger mechanisms to implement the seman-tics of the business rules found in the case studies.
3 A Classification System for Business
Rules
3.1 Goals
Main objectives for classifying business rules are to
• get a systematic basis for the case studies
• build a basis for structuring business rules on a conceptual level.
Additional goals and criteria of the classification are discussed in [HeKn94].
3.2 Classification by Rule Components
Business rules can be represented according to the structure of rules in active databases ([DaBM88]; [GaDi93]) using the three components event, condition and action (ECA). These components can be used for a classification of business rules.
Events are either elementary or complex.
Elemen-tary events are primitives which cannot be further
de-composed; following subtypes can be distinguished:
• Data related events are triggered by an action
rela-ted to a data object; this object may be stored in a database but can also be an object of the real world.
• Time events are specified time-points triggering
• User defined events cover situations which are not
due to data related or time events and are explicitly raised by applications or users.
Complex events are a combination of elementary or
other complex events with additional properties. We distinguish the following types of complex events:
“Boolean“ events:
• Disjunction or conjunction of two other events
(e.g. ‘[product X ordered] and [customer order for product X received]’).
• Selection events (e.g. ‘any 3 of [event1; event2; ...;
eventn]’) could be also expressed by a combination
of boolean conditions but allow an easier descrip-tion of the semantics.
• Sequence events ([event1; event2; ...; eventn]) are a
conjunction of events which must occur in a specified temporal order.
“Temporal“ events:
• Interval events are events which occur between
two specified events (e.g. ‘[client calls] between [offer sent] and [30 days after offer sent]’).
• Periodical events are events which occur
periodically (e.g. ‘every 10th of a month’ or ‘every 5th order of a customer’).
• Delay events occur a specified time delay after the
occurrence of another event (e.g. ‘10 days after [acceptance of customer order]’).
Conditions may also be categorized as elementary or complex. An elementary condition is either a predicate or a query over the data in a database [HaWi92] as e.g.
• a comparison of an attribute value of an object and a constant (e.g. ‘order state = 2’)
• a comparison of attribute values of two objects (e.g. ‘order total ≤ credit limit’) or
• a set-operation on data (e.g. ‘∃ customer related to order’).
Complex conditions consist of several (elementary or
complex) conditions which are combined by Boolean operators.
Actions resulting from the application of a certain business rule may appear as events in logically de-pendent rules; therefore it is proposed to classify the action component similar as the elementary events:
• Data related actions perform an insert, a retrieval,
a derivation, an update or a delete of a data object; as mentioned above, the referenced object may exist only in the real world or may also be modelled in a database system.
• Message actions contain a message to a user of the
IS; the message can result from an application or another user.
• User actions are performed by users of the system
and go beyond message actions.
3.3 Classification by Origins of Business
Rules
Business rules may originate outside or inside of an organization. Externally originating rules can be further divided into natural facts which are eternally fixed and (e.g. legal) norms which are specified by the society and may change. Internal origins can be either primary or secondary; an origin is primary if its content is originally described in a source document, whereas an origin is secondary if the business rule has been previously derived from ano-ther source.
3.4 Classification by the Involvement of
Organizational Units
Business rules can involve one or several organi-zational units; this distinction leads to intra and inter unit rules (cf. Figure 1). For the classification of a specific business rule, the ECA components are assigned to the business unit which is responsible for processing the component. This classification may help to support the administration of business rules in an organization.
! "# $
!% & &
Figure 1: Involvement of organizational units
3.5 Classification by the Assignment of the
ECA Components to a Computer
System and/or its Environment
The ECA components of the business rules can be either assigned to the computer system and/or to its environment (Figure 2), resulting in eight types of rules. This assignment can be determined from different viewpoints:
• Description of the current system (systems survey)
• Description of possible assignments (alternatives analysis)
• Description of the planned assignments (analysis of target systems).
Figure 2: Processes without (a) and with (b) assign-ment of the ECA components to a computer system and/or its environment.
4 A Comparison of Selected Methods for
Describing Business Rules
Several methods of systems analysis have been pro-posed. The commonly used methodologies make no special reference to business rules. In [HKMS94] we compare the capabilities of selected function-oriented methods (Data Flow Diagram, Conceptual Processing Model of Merise, State Transition Dia-gram, Petri Net), data-oriented methods (Entity Re-lationship model, NIAM, ER-Rules model, Beha-viour Integrated ER model, Entity Life History mo-del of SSADM) and object-oriented methods (Event Schema of OOA&D) to specify business rules.
It is shown that the common analysis methods are insufficient or at least inconvenient for a complete and systematic treatment of business rules. Some en-hancements of the common methods are more powerful but still emphasize only certain aspects and types of business rules.
5 State of the Art of Trigger
Implemen-tations and their Applications
5.1 Comparison of Database Trigger
Mechanisms
The three relational database management systems Ingres, Oracle and Sybase have been compared with respect to the functionalities of their trigger imple-mentations. The first vendor introducing a trigger mechanism was Sybase in 1987; Ingres followed in 1989 and Oracle in 1993. Because of the lack of trig-ger standards (which will probably defined in SQL3), the functionalities of the database triggers differ; furthermore, one can presume that the trigger imp-lementations do not match all requirements of the customers. In Table 1 some features of the trigger implementations of the three database systems are compared [ScAc94].
Some differences between the trigger implemen-tation in Ingres, Oracle and Sybase are:
• The possibility to specify user defined events exists only in Ingres
• In Oracle a trigger can not manipulate data in the table related to the triggering event because the table is locked.
Criteria INGRES V6.4 ORACLE V7.0 SYBASE V10
Event
Data related event I / U / D (no retrieval) I / U / D (no retrieval) I / U / D (no retrieval)
Time events No No No
User defined event Yes No No
Complex event No Only disjunction of data
related events
No Condition
Descriptive Yes Yes Yes
Procedural Yes Yes Yes
Action
Data related action I / U / D / R I / U / D / R (exception: table related
to event is locked)
I / U / D / R
Message action Yes Yes Yes
Other criteria
Max. trigger per table ∞ 12 3
Processing for each Tuple Statement/Tuple Statement
Trigger recursion Yes (20) Yes (32) Yes (16)
Call of stored procedures Yes Yes Yes
• Triggers are processed for each statement (in Oracle and Sybase) or for each referenced tuple (in Ingres and Oracle)
• The number of specifyable triggers per table differs.
All systems regarded are not able to handle retrie-val events and time events. Furthermore, powerful tools for trigger administration and consistency checking are missing.
5.2 Survey on Practical Experiences with
Applying Database Triggers
A survey of Ingres, Oracle and Sybase users was conducted with respect to the application of database triggers [Rytz94]. The following statements were obtainted from this research (Table 2):
• The availability of database triggers is known to all respondents. The knowledge about the functiona-lities differs; the users of systems in which trigger mechanisms have a longer tradition are more familiar with this concept than e.g. users of the Oracle database.
• Database triggers are used by 47 % of the users; additional 33 % of the responding users intend to apply trigger mechanisms in the future.
• Main application purposes of database triggers are the implementation of event-action constructs, integrity constraints and security concepts.
• Main problems with database triggers are insuffi-cient performance, missing standardization, no or insufficient methodological support in analysis and design, no support by development tools and insuf-ficient tools for trigger administration.
5.3 Case Studies
Three information systems have been analyzed within the research project (Table 3). The case stu-dies focused on the extraction of the business rules implemented in these systems and on the classifica-tion of the extracted rules according to our classifi-cation system. Another purpose is to get information about how business rules are actually implemented and which types can be implemented by using data-base triggers of Ingres, Oracle or Sydata-base.
A comparison of the empirical data with the func-tionalities of database triggers available in the database systems mentioned above leads among others to the following results:
Users of SYBASE (n = 8) INGRES (n = 12) ORACLE (n = 16) Knowledge about the trigger functionalities
Very well known 37 % 18 % 13 %
Well known 50 % 64 % 37 %
Modestly known 13 % 18 % 37 %
Application of database triggers
At present 74 % 50 % 31 %
Additionally in future 13 % 33 % 44 %
Not intended 13 % 17 % 25 %
Purpose of the (actual or future) trigger application
EA-constructs 71 % 90 % 75 % Static integrity 57 % 40 % 83 % Referential integrity 71 % 70 % 83 % Dynamic integrity 57 % 40 % 67 % Security concepts 57 % 40 % 33 % Others 14 % 20 % 25 %
Implementation of integrity constraints in
Database triggers 63 % 25 % 44 %
Stored procedures 50 % 25 % 38 %
Centralized modules 25 % 17 % 31 %
Decentral in applications 25 % 25 % 56 %
Table 2: Selected results of the survey
• Most events are data related, joined by a disjunc-tion operator; thus they are complex events. Though the underlying elementary events can be directly implemented by trigger mechanisms, only Oracle V7.0 allows the implementation of a dis-junction; in Ingres and Sybase one has to specify a trigger for each elementary event containing e.g. a call of a stored procedure. Time and delay events can not be implemented by the available trigger mechanisms.
• All conditions found in the case studies can be implemented by the database triggers regarded.
• Most actions found in the analyzed IS are message actions which can be implemented by the trigger mechanisms. Data related actions can also be implemented by database triggers with the exception mentioned in the column ORACLE V7 of Table 1. The large amount of user actions in the case study DOM-2 results from the underlying
business processes. These user actions can not and most of them should not be implemented in an IS. The largest number of business rules triggered by a single event was found in the case study DOM-2 in which the modification of a single field leads to the execution of approximately 230 business rules! An implemention of these rules under the restriction (im-posed by Oracle and Sybase) of using only one trig-ger per event would lead to a very complex trigtrig-ger.
A comparison of the business rules found in the BALICO-system with the functionality of Oracle V7.0 triggers showed that 342 rules (65%) can be directly implemented. In the other cases “trick-programming“ may allow an “indirect“ implemen-tation. At present, a partial re-engineering of the BALICO-system is carried out in which the business rules implemented originally in Oracle*FORMS 3.0 are transformed to database triggers of Oracle V7.0 by applying Oracle*CASE and Oracle*FORMS 4.0.
CASE STUDY DAZ
[Eise94a]
DOM-2 BALICO
[KnSc94b] Enterprise Swiss Bank Corporation Swiss insurance company Swiss PTT Purpose of the system Decentral entry of
payments
Entry and administration of damage claims
Administration of owned and rented buildings Programming environment COBOL IMS database COBOL IMS database Oracle*Forms 3.0 Oracle V6 database Procedure Code analysis, interviews Code analysis, interviews Code analysis
LOC (approximately) 1’500 12’000 55’000
Business rules
Number of business rules 76 758 523
- Computer System (CS) 64 (84 %) 627 (83 %) 523 (100 %)
- CS Environment 12 (16 %) 131 (17 %) 0 (0 %)
Types of business rule components Elementary event types
Data related events 10 (13 %) 0 (0 %) 176 (34 %)
Time events 2 (3 %) 0 (0 %) 0 (0 %)
User events 5 (7 %) 78 (10 %) 181 (26 %)
Complex event types Disjunction of data related events 59 (77 %) 673 (89 %) 213 (41 %) Delay events 0 (0 %) 7 (1 %) 0 (0 %) Condition types Elementary conditions 66 (87 %) 102 (13 %) 167 (32 %) Complex conditions 10 (13 %) 598 (79 %) 58 (11 %) Unconditioned 0 (0 %) 58 (8 %) 298 (57 %) Action types
Data related actions 28 (18 %) 32 (4 %) 60 (12 %)
Message actions 115 (76 %) 611 (77 %) 96 (18 %)
User actions 9 (6 %) 151 (19 %) 0 (0 %)
Complex actions 0 (0 %) 0 (0 %) 367 (70 %)
5.4 Summary on Problems with the
Application of Trigger Mechanisms
Though the database triggers of Ingres, Oracle and Sybase allow the specification of a large amount of business rules, their possibilities are not entirely satisfying. We summarize the problems resulting from the discussion above as follows:
− Insufficient methodological support for a rule oriented software development
− No tools for administration and for consistency checks
− Missing standardization (which will presumably be a part of SQL3)
− No user events in Oracle and Sybase
− Insufficient possibilities to specify complex events
− Restricted number of triggers per event in Oracle and Sybase
− Restricted possibilities of data manipulation in the action components of Oracle triggers
− Insufficient performance due to trigger usage.
6 Research Opportunities
In this project some opportunities for follow-up research have been identified:
• Insufficient methodological support of rules on a conceptual level demands for a meta-model sup-porting a business rule oriented analysis.
• This meta-model may serve as a basis for the development of a rule repository.
• Specification of algorithms for administration and checking consistency of business rules resp. database triggers.
• Development of graphical user interfaces for the management of business rules.
• Specification of an active layer for arbitrary database management systems.
Acknowledgment
The research project “Enforcing Business Rules by the Application of Trigger Concepts“ (Project No. 5003-034330) is part of the “Priority Programme Informatics“, funded by the Swiss National Science Foundation.
References
Papers Resulting from the Project
[Eise94a] Eisenegger, P., Fallstudie zur Analyse von Geschäftsregeln in der Anwendung ‘Dezen-trale Auftragserfassung Zahlungsverkehr’, Master Thesis, Institute of Information Systems, University of Berne 1994.
[Eise94b] Eisenegger, P., Anwendung von SSADM-Techniken am Beispiel einer Fallstudie, Internal Paper, Institute of Information Systems, University of Berne 1994.
[HeKn94] Herbst, H., Knolmayer, G., Ansätze zur Klassifikation von Geschäftsregeln, Working Paper 46, Institute of Information Systems, University of Berne 1994 (submitted for publication).
[HKMS94] Herbst, H., Knolmayer, G., Myrach, T., Schlesinger, M., The Specification of Business Rules: A Comparision of Selected Methodolo-gies, in: A.A. Verrijn-Stuart, T. W. Olle (Eds.), Methods and Associated Tools for the Informa-tion System Life Cycle, Amsterdam et al.: Elsevier 1994, pp. 29 - 46.
[Heut94] Heutschi, R., Gegenüberstellung von Trigger-Mechanismen in den Datenbanksystemen Ingres, Oracle und Sybase, Master Thesis, Institute of Information Systems, University of Berne 1994.
[KnHe93] Knolmayer, G., Herbst, H., Business Rules, in: Wirtschaftsinformatik 35 (1993) 4, pp. 386 - 390.
[KnSc94b] Knolmayer, G., Schlesinger, M., Die Transformierbarkeit von Applikationstriggern in Datenbanktrigger am Beispiel der Oracle-Ent-wicklungsumgebung und des BALICO-Systems, to appear as Working Paper, Institute of Infor-mation Systems, University of Berne 1994. [KnSc94a] Knolmayer, G., Schlesinger, M.,
Ge-schäftsregeln in einem System der Liegenschafts-verwaltung und ihre Abbildbarkeit in einem kom-merziell verfügbaren Datenbanksystem, to appear in: GI-Datenbank-Rundbrief (1994) 14.
[Rytz94] Rytz, M., Stand der Nutzung von DatenbankTriggern in der Anwendungsentwicklung -Eine empirische Untersuchung zum Einsatz von Triggerkonzepten in Oracle, Sybase und Ingres, Master Thesis, Institute of Information Systems, University of Berne 1994.
[ScAc94] Schlesinger, M., Achermann, F., Ver-gleichende Gegenüberstellung der Triggermecha-nismen von Ingres, Oracle und Sybase, to appear as Working Paper, Institute of Information Systems, University of Berne 1994.
Further References
[Appl88] Appleton, D.S., Second Generation Applications, in: Database Programming & Design 1 (1988) 2, pp. 48 - 54.
[BBGS90] Bell, J., Brooks, D., Goldbloom, E., Sarro, R., Wood, J.: Re-Engineering Case Study Analysis of Business Rules and Recommenda-tions for Treatment of Rules in a Relational Database Environment, Bellevue Golden: US West Information Technologies Group 1990. [DaBM88] Dayal, U., Buchmann, A.P., McCarthy,
D.R., Rules Are Objects Too: A Knowledge Mo-del for an Active, Object-Oriented Database Management System, in: K.R. Dittrich (Ed.), Advances in Object-Oriented Database Systems, Berlin et al.: Springer 1988, pp. 129 - 143. [GaDi93] Gatziu, S., Dittrich, K.R., Events in an
Active Object-Oriented Database System, in: N.W. Paton, M.H. Williams (Eds.), Rules in Database Systems, Berlin et al.: Springer 1993, pp. 23 - 39.
[HaWi92] Hanson, E.N., Widom, J., An Overview of Production Rules in Database Systems, Working Paper CIS-TR-92-031, University of Florida, 1992.
[MeHo86] Mertens, P., Hofmann, J., Aktionsorien-tierte Datenverarbeitung, in: Informatik-Spektrum 9 (1986) 9, S. 323 - 333.
[SaVo91] Sandifer, A., Von Halle, B., Linking Rules to Models, in: Database Programming & Design 4 (1991) 7, pp. 13 - 16.
[Sche94] Scheer, A.W., Wirtschaftsinformatik -Referenzmodelle für industrielle Geschäfts-prozesse, 5th ed., Berlin et al.: Springer 1994.
