Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

by Michael J. Hernandez
     
 

View All Available Formats & Editions

&a>Database Design for Mere Mortals™, Second Edition, is a straightforward, platform-independent tutorial on the basic principles of relational database design. It provides a commonsense design methodology for developing databases that work.

Database design expert Michael J. Hernandez has expanded his best-selling first edition,

See more details below

Overview

&a>Database Design for Mere Mortals™, Second Edition, is a straightforward, platform-independent tutorial on the basic principles of relational database design. It provides a commonsense design methodology for developing databases that work.

Database design expert Michael J. Hernandez has expanded his best-selling first edition, maintaining its hands-on approach and accessibility while updating its coverage and including even more examples and illustrations.

This book will give you the knowledge and tools you need to create efficient and effective relational databases.

Product Details

ISBN-13:
9780321605474
Publisher:
Pearson Education
Publication date:
03/17/2003
Series:
For Mere Mortals
Sold by:
Barnes & Noble
Format:
NOOK Book
Pages:
672
Sales rank:
1,136,719
File size:
11 MB
Note:
This product may take a few minutes to download.

Read an Excerpt


Chapter 11: Business Rule

Reviewing the Business Rule Specification Sheets

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.

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 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...

Read More

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >