Summary

   

---

Reviewing the Business Rule Specifications Sheets

After you've established the business rules you believe to be appropriate, review their specifications sheets. Carefully examine each specification sheet and make certain that you've properly established the rule and that you've clearly marked all of the appropriate areas on the sheet. 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. They contribute to overall data integrity and 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 according to the manner in which the organization functions or conducts its business. Additionally, these rules will eventually influence the manner in which you implement the database within your RDBMS and how you design and develop end- user application programs for 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 some of the rules will not provide the results you had initially envisioned , so you'll need to modify them. It's also possible for you 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 is quite normalthe 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 business-rule requirements.

The task of defining and establishing business rules isas are so many other tasks within the database-design processongoing. Don't be discouraged if you have to perform this task several times. Your efforts will pay great dividends in the long run.

CASE STUDY

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 start the process by reviewing the PRODUCTS table. As you examine each field, you determine whether it requires any constraints. When you come upon the C ATEGORY field, you remember that there was some question regarding its range of values. (Refer to the Case Study in Chapter 9.) You discuss this issue once again with Mike and his staff, and you finally come to a consensus on a distinct list of categories. Mike then decides that the values for the C ATEGORY field should be limited to those on this list to make certain that the staff does not arbitrarily invent new categories. Based on Mike's decision, you define an appropriate business rule to establish the constraint.

Invalid product categories are not allowed.

There are a number of items in the list of possible categories, so you decide that the best way to establish this rule is to use a validation table. You create a new table called CATEGORIES and then establish a relationship between it and the PRODUCTS table. Next , you diagram the relationship and set the relationship's characteristics in the appropriate manner. Figure 11.18 shows the results of your work.

Figure 11.18. The relationship diagram for the PRODUCTS and CATEGORIES tables.

Here are the settings you used for the relationship's characteristics:

• There is a Restrict deletion rule for the relationship.

• The CATEGORIES table has a mandatory type of participation.

• The PRODUCTS table has an optional type of participation.

• The CATEGORIES table has a (1,1) degree of participation.

• The PRODUCTS table has a (0,N) degree of participation.

Remember that by establishing this relationship, you've replaced the existing C ATEGORY field in the PRODUCTS table with a copy of the C ATEGORY ID field from the new CATEGORIES table. You must now make certain that the C ATEGORY ID field in the PRODUCTS table conforms to the Elements of a Foreign Key and then make the appropriate modifications to its field specification. Finally, set the field's Range of Values element to something such as this:

Any value within the C ATEGORY ID field in the CATEGORIES table

Figure 11.19 shows the settings you've made to the Logical Elements category of the field specifications for the C ATEGORY ID field in the PRODUCTS table.

Figure 11.19. Logical Elements settings for the C ATEGORY ID foreign key 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 Specifications sheet for this new business rule. This specification sheet will reflect the modifications you've made to the field specifications for the C ATEGORY ID field, as well as the characteristics of the relationship between the CATEGORIES and PRODUCTS tables. Figure 11.20 shows the completed Business Rule Specifications sheet.

Figure 11.20. The completed Business Rule Specifications sheet for the new business rule.

You repeat this process for the remaining fields in this table and for the fields in the remaining tables. After you're finished, you move on to the next task.

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 the relationship requires any constraints. Everything seems to be in order, so you move to the relationship between the VENDORS and PRODUCTS tables. Figure 11.21 shows the relationship diagram for these tables.

Figure 11.21. The relationship diagram for the VENDORS and PRODUCTS tables.

As you and Mike discuss whether you should impose any constraints 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 rule by modifying the appropriate relationship characteristics. You begin by designating a Mandatory type of participation and assigning a (1,N) degree of participation to the PRODUCTS table. You then define a Restrict deletion rule for the relationship based on the PRODUCTS table; this will keep you from accidentally deleting the only product associated with a given vendor. Figure 11.22 shows the results of your modifications.

Figure 11.22. The revised relationship diagram for the VENDORS and PRODUCTS tables.

You already know that this type of business rule will be tested when a user attempts to insert a record into or delete a record from the PRODUCTS table, so you complete this process by filling out a Business Rule Specifications sheet for this rule. Figure 11.23 shows the completed specification sheet.

Figure 11.23. A completed Business Rule Specifications sheet.

Now you repeat this process for the remaining relationships. When you're finished, the process is complete and you're ready for the next stage of the database-design process.

---

Top