Transcript IMS 4212: Data Modeling—Business Rules

IMS 4212: Data Modeling—Business Rules
Business Rules—Topics
•
•
•
•
•
•
•
•
Intro to Business Rules
Business Policies
Legal Restrictions
Allowable Attribute Values
Domain Rules
Existential Integrity
Referential Integrity
Complex Policies and Triggers
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
1
IMS 4212: Data Modeling—Business Rules
Intro to Business Rules
• Business Rules are a broad collection of statements that
constrain what the organization can do or how it acts
• Because many organizational activities are reflected in
data stored in the database the rules must also be
reflected in the database and/or its use
• There are many categories of business rules
– Different expressions in the data modeling process
– Different implementation techniques
• Dependent on DBMS capabilities
• Developer and organizational preferences
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
2
IMS 4212: Data Modeling—Business Rules
Business Policies
• Examples
– Only managers can authorize a price discount
– If customers return a product without a receipt they can
only receive the lowest price charged for the product in
the last year
– All hours worked beyond 40 in the same calendar week
will be paid at time-and-a-half
– The manager’s key must be inserted in the register to
cancel a sale
– Credit purchases may not be made by customers with a
positive credit balance over 60 days old who have not
made a payment in 60 days
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
3
IMS 4212: Data Modeling—Business Rules
Business Policies (cont.)
• Documenting Business Policies
– If policy pertains to only one table list policy in simple
English with table design
– If policy involves multiple tables list policies in their
own documentation section
– May consolidate all policies (single- or multi-table in
the same area)
– This list of policies can be quite large
• Document where in the system the rule is enforced
– Maintaining the rules when they change (frequently)
can be troublesome without documentation
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
4
IMS 4212: Data Modeling—Business Rules
Ensure that Data Makes Sense
• Examples
– A student’s graduation date cannot be before their birth
date or enrollment date
– A zip code must match the specified city/state
combination
– A student’s degree objective cannot be “MD” if they are
an undergraduate student
• Document with attribute (or table) definition with
English statements
• Enforce with rules (single table checks) or triggers
(multi table checks)
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
5
IMS 4212: Data Modeling—Business Rules
Legal Restrictions
• Examples
– Employees under 18 must finish work by 10:00 PM
Sunday through Thursday and may not work more than
20 hours per week or six hours per day Sunday through
Thursday except in the summer
– Alcohol may not be sold to anyone under 21 and
tobacco may not be sold to anyone under 18
– Sales tax must be collected unless the purchaser has a
tax exempt ID number or the products are being
shipped out of state
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
6
IMS 4212: Data Modeling—Business Rules
Legal Restrictions (cont.)
• Documenting Legal Restrictions
– Same as business policies
• Enforcing Legal Restrictions
– Same as business policies
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
7
IMS 4212: Data Modeling—Business Rules
Enforcing Business Rules
• Business rules that pertain to data values can be
enforced when attempts are made to change data
– Insert, Update, or Delete data
• If the data change violates a rule the database will
automatically reject the change
• Changes rejected by the database raise errors in the
using application
• Design philosophy is to protect the data at the DB level
• Identify the correct technique for enforcing each rule
• Can you think of business rules that are not reflected in
data?
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
8
IMS 4212: Data Modeling—Business Rules
Enforcing Business Rules (Other Options)
• Enforce in application logic
– Logic must be replicated in all using applications
– Makes maintenance more difficult
• Enforce in employee training, policies, and procedures
• Layered approach
– Protect in the DB
– Use application and training protection to minimize
database rejections
• Protect applications—must use error handling in
applications to trap database rejections
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
9
IMS 4212: Data Modeling—Business Rules
Hierarchy of Business Rules
• Attribute Data Types: Incompatible data is rejected
• Domain rules for individual attributes
– Ranges, Null or not Null, allowable values
– Uniqueness of a non-PK column’s values
• Exestential Integrity (Uniqueness and existence of PK)
• Referential integrity (PK-FK requirements)
• Complex business rules:
– May require calculations to enforce
– Often require checks of multiple tables (other than PKFK referential integrity checks) or multiple records
– Enforced with separate objects called “Triggers”
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
10
IMS 4212: Data Modeling—Business Rules
Attribute Data Types
• Attempting to insert certain data types in a field will
lead to rejection
– $1,456.25 cannot go into a smallmoney field because of
the non-numeric characters (dollar sign and comma)
– Dates must be in an acceptable format to go into a
datetime field
• Excessively long strings will be truncated and entered
• Compatible values can be accepted
– SmallMoney value will go into a Money column
– Varchar(10) value will go into Char(15) column
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
11
IMS 4212: Data Modeling—Business Rules
Allowable Attribute Values
• These are the domain rules for attributes
– Price must be greater than zero
– Social security numbers must be specified as exactly
nine numeric digits with no other characters allowed
– Employee first and last names may not be left blank
• Document these as statements with the table design
• Enforcing Attribute Values
– Most enforceable by attribute type specifications
– Constraints can be declared for domain values
(Will cover for SQL Server in the lab later)
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
12
IMS 4212: Data Modeling—Business Rules
Existential Integrity
•
•
•
•
•
Duplicate records are not allowed
PK must be unique
Empty PKs are not allowed
Documentation is inherent in declaration of PK fields
Enforcement
– DBMS will automatically enforce uniqueness and
existence of field(s) declared to be PKs
– May enforce uniqueness on alternate keys by creating
unique index on the key attribute(s)
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
13
IMS 4212: Data Modeling—Business Rules
Referential Integrity
• Referential integrity refers to the possibility of having
“orphaned” child records
• Is it possible to have a product record without a
matching category record or supplier record?
• Many (but not all) business situations require that
there be a matching parent record for every child
record in a Parent-Child relationship
– Occasionally an orphaned child can be allowed
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
14
IMS 4212: Data Modeling—Business Rules
Referential Integrity (cont.)
• Documenting Referential Integrity
– Cardinality to the parent entity documents referential
integrity
Parent
indicates that related parent is required
Parent
indicates that related parent is not required
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
15
IMS 4212: Data Modeling—Business Rules
Referential Integrity (cont.)
• Enforcing Referential Integrity
– Enforced in DBMS through properties of the
relationship, depending on DBMS capabilities
– Cascade delete deletes all related child records when
the parent record is deleted
– Cascade update updates FK in related child records
when parent PK is changed
– Nullify changes FK to Null when parent is deleted
– No effect (do not enforce) leaves original FK value in
place when parent is deleted or changed OR allows
insertion of unrelated FK in child
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
16
IMS 4212: Data Modeling—Business Rules
Referential Integrity (cont.)
• Enforcing Referential Integrity (cont.)
– Take care when selecting cascade delete or nullify rules
– Deleting an obsolete product will delete all records of
receipts or sales of the product
• Destroys all accounting information pertaining to
these transactions
• Destroys customer purchase information
– Consider archiving obsolete data along with all related
child data
– Can also mark records as being inactive and write
queries to ignore them
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
17
IMS 4212: Data Modeling—Business Rules
Complex Policies and Triggers
• An important class of business rules requires checks
outside the value of interest, record of interest, or even
the current table for detection of a policy violation
– Students may only enroll in ISM 4212 if
• They have completed ISM 3005; or
• They have completed ISM 3253; or
• They have an approved petition to do so
– Employees may not work more than 40 hours in one
calendar week without approval of their division
manager
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
18
IMS 4212: Data Modeling—Business Rules
Complex Policies and Triggers (cont.)
• Enforcing Business Policies
– Policies are enforced with “Triggers”
• Procedural code that is run automatically when
certain events take place against the DB
• Trigger returns a success or failure code or may be
written to prohibit the attempted action
– Inserting an EmployeeShift record may cause the
violation of different underage employee work rules
• Check rules (we’ll see how for SQL Server later)
whenever an Insert is attempted against the table
• Same for Update of an EmployeeShift record
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
19
IMS 4212: Data Modeling—Business Rules
Complex Policies and Triggers (cont.)
• Triggers contain procedural code similar to that in
stored procedures
• Can perform whatever queries are needed to gather the
information needed to evaluate the policy criteria
– Query previous work shifts completed in the week
– Query scheduler’s managerial status
• Can make a determination as to whether the policy has
been violated
• Reject the action if the policy is violated
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
20
IMS 4212: Data Modeling—Business Rules
Complex Policies and Triggers (cont.)
• Rules can only be broken when records are inserted,
updated, or deleted
• The same insert, update, or delete action can affect
many rules
• All potentially violated rules must be checked
– Checks can take place in the Database
• Enforcement applies to all use
• Faster execution
– Checks can also take place in the procedural code that
uses the DB
• Enforcement restricted to just that application
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
21