You are remembered for the rules you break. | ||
| --General Douglas MacArthur | ||
Topics Covered in This Chapter
What Are Business Rules?
Defining and Establishing Business Rules
Validation Tables
Reviewing the Business Rule Specification Sheets
CASE STUDY
Summary
Throughout the database design process, you've performed tasks that helped to establish various levels of data integrity. Thus far, you've established table-level integrity, field-level integrity, and relationship-level integrity. In doing so, you've ensured that the table and field structures are sound, that data entered into the fields will be consistent and basically valid, and that the relationships between the tables are meaningful and properly established. In this chapter you'll learn how to establish the final component of overall data integrity: Business Rules.
A Business Rule is statement that imposes some form of constraint on elements within a field specification for a particular field or on characteristics of a relationship between a specific pair of tables. A Business Rule is based on the way the organization perceives and uses its data; this perception is derived from the manner in which the organization functions or conducts its business.
An important element of any design process is making choices. In database design, one category of choice is which data to record, since obviously we cannot record all data. What data will be recorded clearly will be driven by the way the organization uses its data. A hospital may wish to record times of events to the second; whereas a warehouse only needs to record a date. To guide these and other choices during the database design process and later, during the use of the database, the designer needs a formal statement of the organization's Business Rules. These rules influence the selection of data, the construction of relationships, and the structure of the reports that the database can produce, as well as issues of security and confidentiality. No "one-size-fits-all" set of Business Rules can be drawn up. Instead each organization needs its own specific list of Business Rules.
The following statement is an example of a typical Business Rule:
"A Ship Date cannot be prior to an Order Date for any given order."
This statement, or Business Rule, imposes a constraint on the Range of Values element of the field specifications for a Ship Date field. The organization imposes this constraint in order to ensure that the value of Ship Date is meaningful within the context of a "Sales Order." Without this constraint, any date—including one prior to the Order Date—could be entered into the field, thus rendering the value in the Ship Date field meaningless. The Business Rule ensures the meaningfulness of the Ship Date field.
Because Business Rules depend on the manner in which an organization perceives and uses its data, several organizations might use Business Rules cast in the same words, yet apply them for completely different reasons.
For example, say that the Music Department at "Bel Air High School" is known far and wide for the quality of musicianship it develops in its student musicians. This quality is attained by encouraging the students to focus their musical studies and restrict themselves to learning no more that two instruments. In another part of town, the Music Department at "Lake City High School" (a private school) also imbues its student musicians with a high quality of musicianship by helping the students focus their musical studies. But the students at this school are restricted to learning no more than two instruments due to school policy; the school's inventory of musical instruments is very limited.
Coincidentally, both schools are in the process of designing their own database. In each case, the database will be used to support the daily operations and administrative functions of the school. It so happens that each database contains the tables shown in Figure 11-1. Furthermore, both schools are at the same stage of the database design process and are currently establishing Business Rules. As it turns out, each school happens to be using the following Business Rule in their respective databases:
"A student cannot have more than two instruments checked out at the same time."
This Business Rule applies to the degree of participation between the Students table and Student Instruments table shown in Figure 11-1.
Figure 11-1. Reviewing the degree of participation between the Students and Student Instruments tables.
(Both of these tables appear in each database.) In this instance, a single record in the Students table cannot be associated with more than two records in the Student Instruments table where the value of Check-In Date for each record is Null.
As you should see, this Business Rule applies to both schools. Yet each school requires this constraint for a different reason. "Bel Air High School" requires the constraint because of the manner in which its music program has been established; whereas "Lake City High School" requires the constraint because of the physical limitations of its instrument inventory. This unusual but quite possible example of two identical Business Rules shows that future revisions of the data would result in contrasting changes in the respective Business Rules. The idea, then, is to define and establish the proper set of Business Rules that pertain to the way an organization functions or conducts its business.
This example illustrates another concern. The constraint imposed by certain Business Rules, such as this one, cannot be established within the logical design of the database. For instance, there is no clear way to indicate that the values in Check-In Date must be tested in order to determine whether a student can check out another instrument. Therefore, the constraint must be addressed and established outside of the logical design of the database. How do you determine whether a constraint can be properly represented within this process? You make this determination by identifying the type of Business Rule you're defining.
There are two major types of Business Rules: database-oriented and application-oriented. Both types of Business Rules impose some form of constraint and help enforce and maintain overall data integrity. The difference between them pertains to where and how they are established.
Database-oriented Business Rules are those that impose constraints that can be established within the logical design of the database. In other words, you impose these constraints by modifying elements within a Field Specification or the symbols in a relationship diagram. As long as a constraint can be meaningfully and clearly established by either of these means, the statement from which the constraint is derived is a database-oriented Business Rule. For example, say you have a Vendors table and define the following Business Rule for the VendState field in that table:
"We conduct business exclusively with vendors from the Pacific Northwest."
This Business Rule limits the values that can be entered into the VendState field to "WA," "OR," "ID," and "MT." The constraint imposed by this Business Rule can be meaningfully established by modifying the Range of Values element in the Field Specifications for the VendState field, as shown in Figure 11-2.
Application-oriented Business Rules are statements that impose constraints that cannot be established by modifying a Field Specification or relationship diagram; they must be established within the physical design of the database or within the design of a database application. (The term "database application" is used here to refer to programs -written in some RDBMS software that allow users to use the database easily and perform a number of predefined tasks relative to the function of the organization.) This type of Business Rule is more meaningfully and clearly defined within either of those environments.
Here is an example of a typical application-oriented Business Rule:
"A customer with a `Preferred' status receives a 15 percent discount on all purchases."
This Business Rule determines the amount of discount applied to a customer's purchases, based on a particular status. The constraint cannot be meaningfully established in the logical design for two reasons: there is no field in which to store the discount amount (the amount is a result of a calculation, and calculated fields are not allowed in a table), and there is no way to indicate the criterion used—the customer's status—to determine the discount. Therefore, this -Business Rule must be established within the physical design of the database or the design of the database application.
Note
Although some application-oriented Business Rules may be discussed in this chapter, the manner in which they are defined and established within the physical design of the database or the design of the database application is particular to each software package, and this topic is beyond the scope of this book.
Although both types of Business Rules are important, your main focus during this stage of the database design process will be on database-oriented Business Rules.
Note
Throughout the remainder of the book, database-oriented Business Rules are referred to simply as Business Rules.
Business Rules are divided into two categories that make them easier to understand and define: field-specific and relationship-specific.
Business Rules under this category impose constraints on the elements of a Field Specification for a particular field. Some rules will affect only one element; other rules will affect several elements. For example, this Business Rule only affects one element:
"Order dates are to be displayed in long form, such as `January 10, 1996.'"
This rule affects the Display Format element of the Order Date field in an Orders table. To establish this rule, you modify the Display Format element of the Field Specifications for the Order Date field to indicate the manner in which the date should be displayed.
In contrast, the following rule affects more than one element:
"We must be able to store a zip code for each of our Canadian customers."
The Data Type, Character Support, and Display Format elements of the Field Specifications for the CustZipcode field in a Customers table are affected by this rule. Canadian zip codes include letters, so you must make the following modifications to the Field Specifications in order to impose the constraints defined by this rule:
Change the Data Type setting to "Alphanumeric."
Include "Letters" under the Character Support element.
Modify the Display Format element to ensure that the letters in Canadian zip codes will be capitalized.
Each of these changes is shown in Figure 11-3.
Constraints imposed by relationship-specific business rules affect the characteristics of a relationship between a particular pair of tables.
For instance, assume you're working with the tables and relationships shown in Figure 11-4.
Say you determine that there must be a limit to the number of students for each class. So you define the following Business Rule:
"Each class must have a minimum of five students but cannot have more than twenty."
This Business Rule affects the Degree of Participation between the Classes and Student Classes tables. In order to enforce the constraint defined by this rule, you must modify the relationship diagram to show that a single record in the Classes table must be related to at least five—but no more than twenty—records in the Student Classes table. (Depending on your point of view, you could also infer from this Business Rule that the Type of Participation for the Classes table is now Mandatory; a class will remain listed in the Classes table if and only if there are at least five students registered for the class.) Figure 11-5 shows the modifications that need to be made to the relationship diagram.
During this stage of the design process, you'll define and establish Business Rules for the database. Remember that these rules must be based on the manner in which your organization perceives and uses its data, which (as you well know) will depend on the way the organization functions or conducts its business. The best approach to this task is to define and establish the field-specific Business Rules first, followed by the relationship-specific Business Rules. This approach helps you to remain focused on the type of rule you're defining. It also keeps you from jumping back and forth between different types of Business Rules, which can often lead to confusion and some amount of frustration.
Once again, you'll work with the representative group of users and management. Schedule new meetings with them so that you can work together to define and establish the appropriate Business Rules for the database. Working as a group enables you to make certain that the constraints imposed by the Business Rules you define are meaningful and that there is no confusion or ambiguity as to the necessity of imposing each constraint. If there is some doubt about a constraint, you can discuss the effect it will have on the field or relationship involved, the advantages and disadvantages of imposing the constraint, and then decide whether to keep the rule or disregard it completely based on the results of the discussion.
We begin the process of establishing Business Rules for the database by working with the field-specific Business Rules. The procedure for defining and establishing these rules involves the following steps:
Select a table.
Review each field and determine whether you need to impose any constraints on it.
Define the necessary Business Rules for the field.
Establish the rules by modifying the appropriate Field Specification elements.
Determine what actions test the rule.
Record the rule on the Business Rule Specification sheet.
You'll use this procedure for each table within the database. A detailed breakdown of this process follows.
Select a table.
It doesn't matter which table you start with—you'll eventually take each table through this entire procedure. If you choose a table with an "easy" structure, you'll be able to ease into the process and get accustomed to it before you work on tables that have a more "complex" structure. (Here an "easy" table is one that contains fields that you are relatively familiar with; a "complex" table contains fields that you are not as familiar with and must examine carefully.)
Think about the subject the table represents. Then, pose these questions:
"How is the information regarding this subject used by the organization?"
"What relationships does the table itself have with other tables in the database?"
If necessary, consult the final table list and read the description for this table, and refer to any relationship diagrams that include this table. The answers to these questions will be useful while you're defining Business Rules, and focusing on the table in this manner prepares you for the next step.
Review each field and determine whether there are any constraints that should be imposed on it.
Examine the Field Specifications sheet for each field and determine whether a constraint should be applied to any of its elements. As you review the specification sheet, think about the table in the manner described in the Step 1. Then, pose this question:
"Based on how the table is used within the database, is a constraint necessary for any element within this specification?"
If the answer is no, move on to the next field. However, if the answer is yes, go on to the next step. For example, assume you're working with the CustCounty field in a Customers table, and you have just posed the question about the need for a constraint. Furthermore, assume that the Logical Elements for this field are set as shown in Figure 11-6.
Moving on to the next step is warranted if you receive an answer such as this one:
"Well, the boss wants to begin tracking our customers by county, so we must make certain we record a county for every customer. In fact, we've just added Pierce County and Snohomish County to our sales region, so it'll be imperative that the county names be recorded."
This response clearly is a yes, so you will go on to define the Business Rules in the next step.
Define the necessary Business Rules for the field.
To define the appropriate Business Rules for the CustCounty field, identify the constraints implied by the response in Step 2 and transform each constraint into a Business Rule.
From the responses in Step 2 you can infer that two constraints need to be made to the CustCounty field: a county name is required for each customer, and two more county names need to be added to the current list of county names that can be entered into this field. To transform these constraints into Business Rules, you might start with statements such as
"A county must be associated with each customer."
"The only counties that can be entered into this field are King, Kitsap, Pierce, and Snohomish."
Once you've defined the appropriate Business Rules, you can move on to Step 4.
Establish the rules by modifying the appropriate Field Specification elements.
Take each Business Rule you defined in Step 3 and establish it by modifying the appropriate elements on the Field Specification sheet. (Remember that some Business Rules may affect more than one element.) First, however, you must identify which elements of the Field Specification Worksheet are affected by a particular Business Rule. For example, consider the first Business Rule defined for the custcounty field in Step 3:
"A county must be associated with each customer."
You can deduce that the Required Value, Null Support, and Edit Rule elements will be affected by this rule because the rule explicitly states that a county "must be associated" with a customer. Therefore Required Value will be set to "Yes," Null Support will be set to "No Nulls," and Edit Rule will be set to "Enter Now, Edits Allowed."
As you can see, you must examine each Business Rule very carefully in order to determine which elements of the Field Specifications will be affected by the constraint it imposes. When you first begin to define Business Rules, it's best to have a Field Specification sheet handy to remind you of the elements that can be affected by a Business Rule. As you become more experienced at establishing Business Rules, the elements will come to mind easily.
Now consider the remaining Business Rule in the example:
"The only counties that can be entered into this field are King, Kitsap, Pierce, and Snohomish."
This Business Rule affects the Range of Values element. The new entry for Range of Values will be "King, Kitsap, Pierce, and Snohomish." Figure 11-7 shows the modifications made to the Field Specification sheet for the CustCounty field.
Determine what actions test the rule.
The constraint imposed by a Business Rule is tested when a user tries to perform one of three actions: inserting a record into the table or an entry into a field, deleting a record from the table or a value within a field, or updating a field's value. Now that you've established a Business Rule and understand the constraint it will impose, determine what actions test the rule by identifying when a violation of the rule is most likely to occur. Asking yourself the following questions will make it easier to make a decision:
"Will this rule be violated if I enter a new record into this table?"
"Will this rule be violated if I do not enter a new record into this table?"
"Will this rule be violated if I delete a record from this table?"
"Will this rule be violated if I enter a value into this field?"
"Will this rule be violated if I do not enter a value into this field?"
"Will this rule be violated if I update the value of this field?"
"Will this rule be violated if I delete the value of this field?"
Once you've determined the actions that will trigger a violation of the rule, make note of them; you'll use them in the next step. This information will help an individual or team to establish this rule in the most effective manner possible when implementing the database in an RDBMS program.
The business rule for the CustCounty field will be tested if a user tries to insert a value into the field, because the value must be within a specific range of values. The rule will also be tested if a user tries to delete a value in the CustCounty field, because that value cannot be Null.
Record the rule on the Business Rule Specification sheet.
In order to document your Business Rules for future reference, create a Business Rule Specification sheet for each rule—regardless of its type or category. The Business Rule specification sheet provides three advantages:
It allows you to document every database-oriented Business Rule. . You can use this specification sheet to make certain that you have appropriately defined and properly established each rule.
It allows you to document every application-oriented Business Rule. . Although you cannot establish this type of rule within the logical design of the database, you can at least indicate its basic elements. The information you document for this type of business rule will prove invaluable to the individual or team of individuals who will implement the database within a particular RDBMS or who will create the application program that is used to work with the database.
It provides a standard method for recording all Business Rules. . Business Rules are easier to track and maintain if they are recorded in a consistent manner. Using a uniform format also makes it easier to troubleshoot Business Rules because each aspect of the rule is recorded.
The Business Rule Specification sheet contains the following items:
Statement. . This is the text of the Business Rule itself. It should be clear yet succinct and should convey the required constraints without any confusion or ambiguity. Here's an example of a well-framed statement:
"A booking agent cannot be assigned to more than twenty-five entertainers."
Constraint. . This is a brief explanation of how the constraint applies to the tables and fields. For instance, you can use the following explanation for the constraint imposed by the Business Rule in the above example:
"This Business Rule limits the number of records in the Entertainers table that can be associated with a single record in the Agents table to twenty-five records."
Type. . The type of rule you are defining is indicated here. As you know, the options are database-oriented and -application-oriented.
Category. . Here is where you indicate the category of the rule you are defining. The two options here are field--specific and relationship-specific.
Tested On. . The constraint imposed by a Business Rule is tested when a user tries to perform one of three actions: inserting a record into the table or entry into a field, deleting a record from the table or a value within a field, or updating a field's value. The action that is tested by this business rule is indicated here.
Structures Affected. . Depending on the type of Business Rule, the constraint will affect either a field or a relationship. Here is where you indicate the name of the field affected by the Business Rule or the names of the tables involved in the relationship affected by the rule.
Field Elements Affected. . If the Business Rule pertains to a field, it can affect one or more elements of that field's specifications. This is where you can indicate the elements affected by the constraint.
Relationship Characteristics Affected. . A Business Rule that pertains to a relationship will affect one of the relationship's characteristics. You indicate the characteristic that is affected here.
Action Taken. . Here you indicate the modifications you've made to the elements of a Field Specification or to a relationship diagram. It is very important that the statement you enter here be as clear and unambiguous as possible. Should a problem occur as a result of enforcing this Business Rule, this statement serves as accurate documentation of the steps you have taken to establish the rule. You can use this statement to make certain that these steps were actually carried out and that the rule has been properly established.
Now fill out a Business Rule Specification sheet for the rule you established in Step 4. Figure 11-8 shows a completed Business Rule Specification sheet documenting the Business Rules established for the CustCounty field.
After defining and establishing field-specific Business Rules, the next order of business is to tackle the Business Rules for relationships. The procedure for performing this task involves the following steps:
Select a pair of tables that share a relationship.
Review each relationship characteristic and determine whether a constraint is warranted by the way the organization functions or conducts its business.
Define the necessary Business Rule.
Establish the rule by modifying the relationship characteristic.
Determine what actions will test the rule.
Record the rule on the Business Rule Specification sheet.
As you can see, this procedure is similar to the one used for field--specific Business Rules. Here is a detailed breakdown of each step:
Select a pair of tables that share a relationship
Because you'll take each relationship through this procedure, the pair of tables you start with is unimportant. Once you select a pair of tables, review the relationship diagram used to illustrate their relationship. Then think about what the tables represent and why they are related, and pose the following questions:
"What kind of information do these tables provide?"
"Why is the relationship between these two tables important?"
The answer to these questions will be helpful in defining any necessary Business Rules for the relationship, and keeping them in mind will prepare you for the next step.
Review each relationship characteristic and determine whether a constraint is warranted by the way the organization functions or conducts its business.
Examine each relationship characteristic and determine whether some form of constraint is necessary. As you review the characteristic, remember the answers to the questions posed in Step 1. Now ask the following question:
"Is there a limitation that should be imposed on this characteristic based on the way the organization functions or conducts its business?"
If the answer is yes, then go to the next step. If the answer is no, then review the next table relationship characteristic and perform this step once again. For example, assume you're defining a database for a small dance studio, and you're reviewing the relationship diagram shown in Figure 11-9. The Degree of Participation between the Instructors and Instructor Classes tables is currently under consideration.
Now you pose a question regarding degree of participation:
"Is there some limitation that should be imposed on the degree of participation between these tables based on the way the school functions or conducts its business?"
You can move to the next step if you receive an answer such as this one:
"Yes, there is. We require all instructors to teach at least one class. However, we limit them to teaching no more than eight classes."
This response will be used as that basis of defining a Business Rule in the next step.
Define the necessary Business Rule.
Next define an appropriate Business Rule based on the response you received in Step 2. Identify the constraint implied by the response, and transform it into a Business Rule. For example, consider the response from Step 2 regarding the degree of participation of the tables in the dance studio database:
"Yes, there is. We require all instructors to teach at least one class. However, we limit them to teaching no more than eight classes."
As you can see, you can infer two constraints from this response: the minimum number of classes an instructor can teach is one, and the maximum number is eight. Now you can transform these constraints into a Business Rule. You might use a statement such as this one:
"An instructor must teach one class but no more than eight classes."
After you've defined the rule, you can continue with the next step.
Establish the rule by modifying the relationship characteristic.
Establish the Business Rule you just defined by modifying the appropriate symbols in the relationship diagram. Before you make the modifications, you must determine which table is affected by the constraint. First consider the Business Rule statement:
"An instructor must teach one class, but no more than eight classes."
Next identify which table receives the limitation. In this instance, the limit is on the number of classes an instructor can teach. Therefore the modification is made to the Degree of Participation characteristic of the Instructor Classes table. This rule also affects the Type of Participation characteristic of the Instructor Classes table. The Type of Participation is now Mandatory because a single record in the Instructors table must be associated with at least one record in the Instructor Classes table. Figure 11-10 shows the necessary modifications to the relationship diagram based on this Business Rule.
Determine what actions will test the rule.
As you know, a Business Rule can be tested if a user tries to insert, delete, or update a record in a table or a value in a field. Now that you've established the Business Rule and understand how it affects the relationship, determine the actions by which the rule will be tested. You can determine this by identifying when a violation of the rule is most likely to occur. Use the following questions to help you make your decision:
"Are there circumstances under which this rule will be violated if I enter a new record into this table?"
"Will this rule be violated if I do not enter a new record into this table?"
"Will this rule be violated if I delete a record from this table?"
Once you have determined the actions that will trigger the test, make note of them; you'll use them in the next step. This information will help the individual or team of individuals who is implementing the database to establish this rule in the most effective manner possible.
Here's an important point to note: If you determine that a rule will be violated when you delete a record, then you must alter the current deletion rule for the relationship accordingly or add a new deletion rule to the relationship.
In Chapter 10 you learned that there is no need to worry about deleting records in the subordinate or "many" side of the relationship because there can be no adverse effects from doing so. We must now amend this assertion by stating that an exception to this assertion occurs if deleting a record on the subordinate or "many" side of the relationship would violate a required Business Rule. In this instance, the only option available is a restrict deletion rule. Make certain you keep this in mind when you're determining when a rule will be tested.
The constraint imposed by the Business Rule in the dance studio database will be tested if a user tries to insert a record into the Instructor Classes table because the maximum number of records that can be associated with a particular instructor is eight. The rule will also be tested if a user tries to delete a record from the Instructor Classes table because the minimum number of records that must be associated with a particular instructor is one. Because the rule will be violated if the user tries to delete a record from the Instructor Classes table, you need to add a restrict deletion rule for this table to the relationship. Figure 11-11 shows the modification made to the relationship diagram for the dance -studio database.
Record the rule on the Business Rule Specification sheet.
Finally, fill out a Business Rule Specification sheet for the Business Rule you established in Step 4. Figure 11-12 shows a completed Business Rule Specification sheet for the rule established for the dance studio tables.
As you define field-specific Business Rules, there will be instances in which a rule affects the range of values element of the Field Specifications for a particular field; it commonly limits the range of values to a specific set of valid entries. In many cases, the set of values is made up of a relatively fixed number of entries with values that will rarely change. You could attempt to enumerate each item in the Range of -Values element in the Field Specifications sheet, but you would probably run out of room very quickly. Also this set of entries may be difficult to implement within the RDBMS or hard for a user to commit to memory. You can avoid these problems by storing the set of entries in a validation table.
As you learned in Chapter 3, a validation table is a table that holds data specifically used to implement data integrity. Data in a validation table is rarely modified, and it is not very often that records are added or deleted once the table is populated with the required data. Validation tables usually (but not always) comprise two fields: one is assigned the role of the Primary key and is used to help enforce data integrity; the other field is simply a Non-key field used to store a set of values required by some other field in the database. Figure 11-13 shows two examples of validation tables.
In this chapter, you'll learn how the first field is used in support of enforcing a Business Rule. You'll learn how the Non-key field is used later in Chapter 12.
When a Business Rule limits the range of values of a particular field, you can enforce the constraint by using a validation table; the value for the field will then be drawn from an appropriate field in the validation table. Establishing the Business Rule involves two steps: defining a relationship between the parent table of the field affected by the rule and the validation table, and making a modification to the Range of Values element of the Field Specifications for the affected field in the parent table.
For example, assume you're working with the SuppState field of a Suppliers table, and you've defined the following Business Rule:
"All suppliers must be from the eleven contiguous western United States, Alaska, or Hawaii."
This rule imposes a constraint on the Range of Values element of the SuppState field specifications. It limits the values that can be inserted into the field to "AK, AZ, CA, CO, HI, ID, MT, NM, NV, OR, UT, WA, and WY." It should be evident that an easy and efficient way to establish this rule is by using a States validation table.
Consider the tables shown in Figure 11-14. (Note the new symbol that is used to represent a validation table.) The Suppliers table stores all the requisite data on the suppliers engaged by the organization. The States table is a new validation table that will store the names and abbreviations of the specified states.
In order to establish the Business Rule, the first step is to establish a relationship between these tables. As you can see, there is a one-to-many relationship between the States and Suppliers tables—a single record in States can be associated with one or more Suppliers, but a single record in Suppliers will be associated with only one record in States. You already know that a one-to-many relationship is established by taking a copy of the Primary key on the "one" side and inserting it into the "many" side, where it becomes a Foreign key. Although the Suppliers table already has a field named SuppState, it will be replaced by the State field from the States validation table. (This modification is justified because it is in accordance with the Elements of the Ideal Field and is consistent with the manner in which one-to-many relationships are established.) Figure 11-15 shows the relationship established between these two tables.
Now that the State field is a Foreign key in the Suppliers table, make certain that it conforms with the Elements of a Foreign key, as outlined in Chapter 10. After you're finished, set the characteristics for the relationship in the following manner:
Deletion Rule. . Set a restrict deletion rule for this relationship. You do not want to delete a state in the States table that is referenced by records in the Suppliers table.
Type of Participation. . This is set to "Optional" for the Suppliers table and "Mandatory" for the States table. Although it's unnecessary for there to be any records in the Suppliers table in order to enter a new state in the States table, there must be records in the States table before you can enter records into the Suppliers table. In this way, data integrity is enforced.
Degree of Participation. . This is set to "(0,N)" for the States table and "(1,1)" for the Suppliers table. It's not necessary for a single record in the States table to be associated with any records in the Suppliers table; you may be waiting to engage your first supplier in Alaska. On the other hand, a single record in the States table can be associated with one or more records in the Suppliers table. In the case of the Suppliers table, a single record in that table must be related to only one record in the States table.
Next modify the Range of Values element of the Field Specifications for the State field in the Suppliers table. The entry for this element should now be "Any value within the State field of the States table." Figure 11-16 shows the Logical Elements settings of the State field in the Suppliers table.
Now that you've established the Business Rule, you must decide when it should be tested. Whenever you use a validation table to enforce a Business Rule, you typically want to test the rule if a user attempts to insert a new value into the field or update an existing value within the field. In either case, the rule will be violated if the user attempts to use a value that does not exist in the validation table.
Finally, complete a Business Rule Specification sheet for the Business Rule you've just established with the validation table. Be sure to indicate the modifications you've made to both the field and the new relationship. Figure 11-17 shows the completed Business Rule Specification sheet for the example Business Rule.
Once you've established the Business Rules you believe to be appropriate, review their specification sheets. Carefully examine the specification sheet and make certain that the rule has been properly established and that all the appropriate areas on the sheet are clearly marked. If you find an error, make the necessary modifications and review it once more. Repeat this process until you've reviewed every Business Rule.
Business Rules are an important component of the database. Along with contributing to overall data integrity, Business Rules impose integrity constraints that are specific to the organization. As you've seen, these rules help to ensure the validity and consistency of the data within the context of the manner in which the organization functions or conducts its business. Furthermore, these rules will affect the manner in which the database is implemented in an RDBMS and how it works with the application program used to work with the database.
It's important to understand that you will revisit these rules quite often. As you review the final structure, for example, you may determine that additional Business Rules are necessary. You may discover that several of the rules will not provide the results you had initially envisioned, and thus they need to be modified. It's also possible to determine that some of the rules aren't necessary after all. (In this instance, be absolutely sure to examine the rules carefully before you remove them.)
Keep in mind that the Business Rules you define now are bound to require modifications in the future; you will most likely need to add Business Rules in due course because of changes in the way the -organization functions or conducts its business. The need to modify existing Business Rules or develop new ones comes up naturally—the organization inevitably grows and matures, and so does the manner in which it acts upon or reacts to external forces. These forces affect the manner in which the organization perceives and uses its data, which, in turn, changes the nature of the organization's requirements in terms of Business Rules.
The task of defining and establishing Business Rules is—as are so many other tasks within the database design process—ongoing. Don't be discouraged if you have to perform this task several times. Your efforts will pay great dividends in the long run.
Now it's time to establish Business Rules for Mike's database. You schedule a meeting with Mike and his staff to review the tables and relationships in their database. The first order of business is to define and establish field-specific Business Rules.
You begin the process by reviewing the Products table. As you begin to examine each field, you try to determine whether any constraints are required. Upon reviewing the Category field, you remember that there was some question as to the values that could be entered into the field. (Refer to the Case Study in Chapter 9.) After some discussion with Mike and his staff, you compile a list of categories. Then Mike decides that the values for the Category field should be limited to those on this list to make certain that the staff does not arbitrarily invent new categories. Because Mike wants to limit the values that can be entered in the Category field, you define an appropriate Business Rule to properly establish the constraint. The new rule is stated as follows:
"Invalid product categories are not allowed."
Because there are a number of items in the list of possible categories, you decide that the best way to establish this rule is to create and use a Categories validation table. So you create the table and establish a relationship between it and the Products table. After you establish the relationship, you make the appropriate settings to its characteristics. The new validation table, relationship, and its table relationship characteristics are shown in the diagram depicted in Figure 11-18.
These are the settings you've made for the table relationship characteristics:
There is a restrict deletion rule for the relationship.
The type of participation for the Categories table is set to "Mandatory."
The type of participation for the Products table is set to "Optional."
The degree of participation for Categories is set to "(1,1)."
The degree of participation for Products is set to "(O,N)."
Remember that by establishing this relationship, you've replaced the existing Category field in the Products table with a copy of the Category ID field from the new Categories table. Because the Category ID field in the Products table is a Foreign key, you now make certain that it conforms to the Elements of a Foreign key and make the appropriate modifications to its Field Specifications. Then you modify the Range of Values element to read "Any value within the Category ID field in the Categories table." Figure 11-19 shows the Logical Elements settings of the Field Specifications for the Category ID field in the Products table.
Now you must decide when the rule should be tested. As you already know, you typically want to test a rule established with a validation table if the user attempts to insert a value into the field or update an existing value within the field.
Finally, you complete a Business Rule Specification sheet for this new Business Rule. This specification sheet will reflect the modifications you've made to the Field Specifications for the Category ID field as well as the characteristics of the relationship between the Categories and Products tables. Figure 11-20 shows the completed Business Rule Specification sheet.
You repeat this process for the remaining fields in this table, as well as the fields in the remaining tables. After you're finished, you move on to the next task.
Figure 11-20. The completed Business Rule Specification sheet for the Categories and Products tables.
The next order of business is to establish relationship-specific Business Rules. You begin by reviewing the relationship between the Employees and INVOICES tables, and you review the relationship diagram to determine whether any constraints are necessary. Everything seems to be in order, so you move to the next relationship, that between the VENDORS and PRODUCTS tables, as shown in Figure 11-21.
As you and Mike discuss whether there are any constraints that should be imposed on this relationship, Mike determines that there should be a constraint on the Products table. He wants to make sure that every vendor in the Vendors table is associated with at least one product; he figures that it's unnecessary to keep data on a vendor who's not supplying him with any products. So you define the following Business Rule for this constraint:
"Every vendor must supply at least one product."
Now you establish the Business Rule by modifying the appropriate relationship characteristics. In this instance, you modify the type of participation for the Products table so that it is set to "Mandatory," and you modify the degree of participation for Products table to "(1,N)." Then you add a new restrict deletion rule to the relationship for the Products table. These changes are shown in Figure 11-22.
Because of the nature of the Business Rule, you already know that it will be tested if a user attempts to insert a record into the PRODUCTS table or delete a record from the PRODUCTS table. So you fill out a Business Rule Specification sheet for this rule, which is shown in Figure 11-23.
Now you repeat this process for the remaining table relationships. When you're finished, the process is complete. You're now ready for the next stage of the database design process.
This chapter opens with a definition of Business Rules. You learned that a Business Rule is a constraint imposed on a field or a table relationship that it is based on the way the organization perceives and uses its data, and it is derived from the manner in which the organization functions or conducts its business. You now know that there are two major types of Business Rules: database-oriented and application-oriented. Although our focus here is on database-oriented Business Rules, you know that you can at least record the basic elements of application-oriented Business Rules for use later in the implementation process.
You then learned that database-oriented Business Rules are divided into two categories: field-specific Business Rules, which affect the elements of a Field Specification for a particular field; and relationship-specific Business Rules, which affect the characteristics of a relationship between a pair of tables.
The chapter continued with a discussion of defining and establishing Business Rules. Here you learned that you'll work with users and management to define the Business Rules required by the organization. Furthermore, you learned that it is best to establish the field-specific Business Rules first, followed by the relationship-specific Business Rules.
Next you learned the steps necessary to define and establish each type of business rule. You now know that, in general, you work with a field or relationship, review the field or relationship in light of the rule to determine whether any constraints are necessary, define the appropriate Business Rule, establish the rule by modifying the appropriate Field Specification elements or table relationship characteristics, decide when the rule should be tested, and complete a Business Rule Specification sheet for each Business Rule.
The chapter continues with a discussion on the elements of the Business Rule Specification sheet, and how each element on the sheet is defined. As you now know, using Business Rule Specification sheets allows you to document all of your rules and provides a standard method for recording and reviewing them.
We close the chapter by discussing validation tables. You learned that a validation table can be created and used in support of a Business Rule that limits the Range of Values for a particular field. In this manner, the validation table helps to enforce data integrity. You also learned that you need to establish new table relationships when you use validation tables, and that these relationships have the same types of characteristics as do any other types of table relationships in the database.