Appendix A
DATA INTEGRITY RULE EXAMPLES
Appendix A contains examples of data integrity rules to show the types and formats of the various data integrity rules. For a detailed description of data integrity rules, refer to Data Resource Simplexity.
Data Integrity Rule Symbols
Mathematical symbols:
+ addition
- subtraction
* multiplication
/ division
** exponentiation
= equals
Logical symbols:
< less than
> greater than
<= less than or equal to
>= greater than or equal to
= equal to
<> not equal to
>< must be equal to, or must be
^ hierarchy (parent – child)
& logical and
| logical or
|| concatenated with
:: valid for
~ relationship
<< comes from, translates from
>> goes to, translates to
Blank means a blank data value
‘ ‘ means a blank data value
Set symbols:
{ } a set
n{ }m a set with minimum and maximum values
( ) grouping of elements
n m substring from n to m
Data Integrity Rule Common Words
Cardinality! Proactive Update!
Constraint! Rederivation!
Change! Retroactive Update!
Comment: Selection!
Condition! Source!
Constraint! Translation!
Delete! Unique!
Derivation! Violation!
Notification!
Employee
Cardinality!
Change!
Street Segment
Cardinality 1!
Cardinality 2!
Customer
Inactive Delete!
Active Delete!
Product
Product. Market Value
Domain! 1 <= integer <= 1000
Need! Required
Class – Student
Cardinality!
Constraint!
Data Value Rules
20 <= Trailer. Length <= 60
Trailer. Length
Domain! 20 <= Integer <= 60
Company. Name
Domain! 5 characters <= Text <= 30 characters & first character <> Blank
Domain! 5 <= alphanumeric characters <= 128 & right justified
Domain! -180.0 <= real <= +180.0
Driver. Birth Date, Domain! >< Valid Date
Candidate. Birth Date
Domain! January 1, 1996 <= Date <= December 31, 1998
Sample Tree. Height, Domain! 36.75 <= Real <= 72.25
Product. Audit Indicator
Domain! {‘Y’ | ‘N’} Default ‘N’
When default value entered
Product. Audit Indicator Default Value Entry = “Y”
Region. Code
Domain! {‘AK’ | ‘BK’ | ‘CD’ | ‘RQ’ | ‘XT’}
Customer
Region. Code, Domain >< Region. Code
Conditional Data Value Rules
Need! Region. Code is Required
Need! 1{Region. Code}1
Need! Prevented
Need! Region. Code is Optional
Need! 0{Region. Code}1
Need! Optional
Need! Region. Code is Prevented
Need! 0{Region. Code}0
Need! Prevented
Product Order. Value Condition!
When Product Status. Code = ‘Confirmed’
Product Order. Sale Date is Required
Product Order. Ship Date is Prevented
Product Order. Delivery Date is Prevented
When Product Status. Code = ‘Shipped
Product Order. Sale Date is Required
Product Order. Ship Date is Required
Product Order. Delivery Date is Prevented
When Product Status. Code = ‘Delivered
Product Order. Sale Date is Required
Product Order. Ship Date is Required
Product Order. Delivery Date is Required
Product Order. Value Condition!
Product Order. Ship Date >= Product Order. Sale Date
Product Order. Delivery Date >= Product Order. Ship Date
Employee Seniority - Employee Type Integrity!
When Employee Type. Code = ‘1’ Employee Seniority. Code >< ‘B’
When Employee Type. Code = ‘2’ Employee Seniority. Code >< {‘B’ | ‘C’}
And so on…
Domain! Employee Type. Code & Employee Seniority. Code ><
Employee Seniority – Employee Type Integrity.
Employee Seniority – Employee Type Integrity
Employee Seniority – Employee Type Integrity. Begin Date
Need! Required
Employee Seniority – Employee Type Integrity. End Date
When Employee Seniority – Employee Type is valid
Employee Seniority – Employee Type. End Date is Prevented
When Employee Seniority – Employee Type is not valid
Employee Seniority – Employee Type. End Date is Required
Data Structure Rules
Stream - Stream Segment Cardinality!
1{Stream}1 ~ 2{Stream Segment}M
Employee
Employee. Social Security Number
Need! Required
Department. Number
Need! Required
Domain! Department: Department. Number
Ethnicity. Code
Need! Optional
Domain! {Blank | Ethnicity: Ethnicity. Code}
Employee – Ethnicity Cardinality!
1{Employee}1 ~ 0{Ethnicity}1
Class ~ Professor Cardinality!
1{Professor}1 ~ 0{Class}3
Conditional Data Structure Rules
Degree ~ Student Cardinality!
When a Student is an Undergraduate Student a Degree is Prevented.
When a Student is a Graduate Student a Degree is Required.
Student >< {[Undergraduate] Student | [Graduate] Student}
Degree ~ [Undergraduate] Student. Cardinality!
1{[Undergraduate] Student}1 ~ 0{Degree}0
Degree ~ [Graduate] Student. Cardinality!
1{[Graduate] Student}1 ~ 1{Degree}M
Prospective Student ~ Student Constraint!
Prospective Student <> Student
Data Derivation and Data Rederivation Rules
Employee
Employee. Age, Years- Derivation!
Employee. Age, Years = Current Date - Employee. Birth Date
Well Type. Code, Derivation!
Derive: On initial data entry
Rederive: When contributor’s data value changes
When Well Casing Type Code = ‘Black Steel’ & Well. Depth <= 10 Feet
Well Type. Code = ‘1’
When Well Casing Type Code = ‘Black Steel’ & Well. Depth > 11 Feet & <= 100 Feet
Well Type. Code = ‘2’
When Well Casing Type Code = ‘Black Steel’ & Well. Depth > 300 Feet
Well Type. Code = ‘3’
And so on…
Product Shipment
Product Shipment. Product Count, Derivation!
Product Shipment. Product Count = Count of unique Products in Product Shipment
Derive: When Product Shipment Status. Code =’C’
Rederive: None
Product Shipment. Total Weight, Derivation!
Product Shipment. Total Weight = Sum of all Product Load. Weight in Product Shipment
Derive: When Product shipment Status Code = ‘C’
Rederive: None
Product Shipment. Average Product Weight, Derivation!
Produce Shipment. Average Product Weight = Product Shipment. Total Weight / Product Shipment. Product Count
Derive: When contributors have been derived.
Rederive: None
Vehicle
Vehicle. Yearly Miles, Derivation!
January 1 {Sum Vehicle Trip. Miles} December 31
Derive: January 15 of following calendar year
Rederive: None
Data Retention Rules
Employee
Employee. Name, Change!
Move Employee. Name to Employee History. Name
Enter effective date of name change in Employee History. Date
Enter new employee name in Employee. Name
Employee
Employee. Name, Change!
Move Employee to Employee History
Enter effective date of name change in Employee History. Date
Enter new employee name in Employee. Name
Employee
Employee. Birth Date, Change!
Change allowed with no History
Customer. Retention!
Customer. Inactive!
When Customer has not Purchased a Product for 12 months
Delete Customer
Customer. Left Country!!
When Customer has left the country
Delete Customer
Customer. Deceased!!
When Customer has deceased
Delete Customer
Customer. Delete!
Move Customer to Customer History
Enter current data in Customer History. Date
Delete Customer
Customer History
When Current Date >= Customer History. Date + 36 Months
Delete Customer History
Data Selection Rules
[Retirement Eligible] Employee. Selection!
Employee. Age, Years >= ‘50’ & Employee. Service Years >= ‘25’
[Management Level] Employee. Selection!
Management Level. Code = {‘2’ | ‘4’ | ‘5’}
[Preferred] Employee. Selection!
[Retirement Eligible] Employee & [Management Level] Employee
Product. Style, Selection!
3 Product. Comment 8
Data Translation Rules
Pole
Pole. Length, Inches- Translation!
Pole. Length, Meters * 39.39
Product
Product. Price, US Dollars- Translation!
Exchange Rate. Euro = current exchange rate
Exchange Rate. Date = current Date
Product. Price, Euro = Product. Price, US Dollars * Exchange Rate. Euro
Traffic Accident
Traffic Accident. Location, State Plane Coordinate- Translation!
Traffic Accident. Location, Latitude Longitude- = Traffic Accident. Traffic Accident Location, State Plane Coordinates- & Geographic Coordinate. Algorithm Six.
Translation
Translation. Algorithm Twelve
Driver
Driver. Complete Name, Inverted- Translation!
Driver. Complete Name, Normal- & Translation. Algorithm Twelve
Translation
Translation. Miles – Kilometers
Translation. Meters – Feet
And so on.
Road Segment
Road Segment. Length, Kilometers- Translation!
Road Segment. Length, Miles- & Translation. Miles - Kilometers
Translation. Pi, Seven Decimals = 3.14159623
Road Segment
Road Segment. Length, Kilometers
Road Segment. Length Significant Digits
Inheritance
Coordinate
Coordinate. Longitude Degree, Domain!
-180.0 <= value <= 180.0
Well Head
Well Head. Longitude Degrees, Domain! >< Longitude. Degree, Domain!
Coordinate
Coordinate. Longitude Degrees, Washington Federal- Domain!
122.234 <= value <= 123.615
Coordinate. Longitude Degrees, Washington State- Domain!
122.197 <= value <= 123.675
Well Head
Wellhead. Longitude Degrees, Domain! >< Coordinate. Longitude Degrees, Washington Federal- Domain!
Versions
Customer
Customer. Left Country Duration! <Pre-1995>
Customer. Left Country Duration! <January 1995>
Student
Student. Name
Student. Name, Change! < Through 1997>
Student. Name, Change! <1998>
Customer
Customer. Deceased Retention! <Pre-2001>
Customer. Left Country Retention! <Pre-2001>
Customer. Inactive Retention! <2001>
Class ~ Professor. Cardinality! <Pre 2007>
1{Professor}1 ~ 0{Class}3
Class ~ [Full] Professor. Cardinality! <2007>
1{Professor}1 ~ 0{Class}2
Class ~ [Associate] Professor. Cardinality! <2007>
1{Professor}1 ~ 0{Class}4
Failure and Notification
Trailer. Length
Domain! 20 <= Integer <= 60
Violation! Move Trailer to Trailer Suspense
Trailer
Violation! Move Trailer to Trailer Suspense
Trailer. Length
Domain! 20 <= Integer <= 60
And so on.
Trailer
Violation! Move Trailer to Trailer Suspense
Notification! Entry into Vehicle Error Log.
Trailer. Length
Domain! 20 <= Integer <= 60
Data Source Rules
Employee: Race. Code
Source! << PYRL.EMPL_RACE
Employee. Name
Source ! When Employee. Hire Date >= ‘January 1, 1990’
& <= ‘December 31, 1999’ << PYRL.EMPL_NM
When Employee. Hire Date >= January 1, 2000
<< AA.EMP_NAME
Employee. Birth Date
Source! When Department. Identifier ^ Employee = ’62’ | ‘63’ | ‘64’
<< PYRL_EMPL_BD
When Department. Identifier ^ Employee <> ’62’ | ‘63’ | ‘64’
<< TRAIN.EMPLYE_BDATE
Employee. Birth Date
Source! When Department. Identifier ^ Employee = ’62’ | ‘63’ | ‘64’
<< PYRL_EMPL_BD
When Department. Identifier ^ Employee <> ’62’ | ‘63’ | ‘64’
<< TRAIN.EMPLYE_BDATE
When TRAIN.EMPLYE_BDATE = ‘ ‘ << PYRL.EMPL_BD
When PYRL.EMPL_BD = ‘ ‘ << AA.EMP_BIRTH
Hierarchy
The up-caret is used to denote a hierarchy. The example below is read as “Report is parent to Site Class, which is parent to Age Category, which is parent to Species, which is parent to Timber Stand.” The hierarchy could also be read in the other direction as “Timber Stand is subordinate to Species, which is subordinate to Age Category, which is Subordinate to Site Class, which is subordinate to Report.
Report ^ Site Class ^ Age Category ^ Species ^ Timber Stand
University ^ Department ^ Course ^ Class ^ Section
Library ^ Wing ^ Floor ^ Section ^ Row ^ Shelf ^ Book
The hierarchy symbol can also be used for recursive data relations. The example below specifies that a parent organization unit cannot have the same identifier as the subordinate organization unit. In other words, an organization unit cannot report to itself.
Organization Unit. Identifier ^ <> Organization Unit. Identifier
The example below specifies that any organization unit in the hierarchy cannot have the same identifier as the subordinate organization unit. In other words, no parent, grandparent, great grandparent, and so on, can have the same identifier as the subordinate organization unit.
Organization Unit. Identifier ^^ <> Organization Unit. Identifier
Concatenation
Concatenations use the double parallel symbol. The example below shows that a person’s complete name in the normal sequence is obtained from the person’s individual name concatenated with a blank, concatenated with the person’s middle name, concatenated with a blank, concatenated with a person’s family name.
Person. Name Complete, Normal = Person. Individual Name || ‘ ‘
|| Person. Middle Name || ‘ ‘ || Person. Family Name
Person. Name Complete, Inverted = Person. Family Name || ‘, ‘
|| Person. Individual Name || ‘ ‘ || Person. Middle Name
Sub-stringing
Sub-stringing uses the back slashes with the ranges of the sub-stringing outside the back slashes and the data attribute inside the back slashes. In the example below, the vehicle’s manufactured date is obtained by sub-stringing fields 5 through 8 from the vehicle comment data attribute.
Vehicle. Year Manufactured = 5 Vehicle. Comment 8
Valid For
The valid for symbol specifies a constraint for a data value. The example below specifies that student type code contained in the student data entity must equal one of the type codes in the student type data reference set that is valid for the student’s registration date.
Student: Student. Type Code ><
Student Type. Code :: Student. Registration Date
Move To
The move to symbol specifies that data are moved from one location to another location. The example below specifies that the violation action for a data integrity rule failure of trailer data results in the trailer data record begin moved to the trailer suspense data entity.
Violation! Trailer >> Trailer Suspense
Data Reconstruction Rules
Student
Reconstruct! Current back through January 1984
STDNT_NME
STDNT_BRTH_DT
STDNT_WGHT
STDNT_CHNG_DT
And so on.
Data Recast Rules
Recast! 1970 through 1999 to 1970 using Recast Algorithm 6
BDG:OBJ
BDG:SOB
BDG:PRG
BDG:SPRG
And so on.