Ships from: Ossining, NY
Usually ships in 1-2 business days
Ships from: Las Vegas, NV
Usually ships in 1-2 business days
Anonymous
Posted Mon Jan 29 00:00:00 EST 2001
This book provides a clear platform independant approach to Database Design. I wasn't going to buy this book because of the 1996 publishing date, but decided to because of a review I read about it in a database tutorial I found on the web. I'm very glad I did. The design approach is as applicable now as it was in 1996. This book is very readable and easy to understand compared to other database design books I have read. While the other books are good, the technical language is hard to understand. 'Database Design for Mere Mortals' is written in plain english and has helped me to both understand database design and appreciate the technical books. Thank you Mr. Hernandez
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.Anonymous
Posted Wed Dec 13 00:00:00 EST 2000
I have a very basic background on DB but I work in a place where I'm constantly exposed to them. Wanting to learn more I asked around and many people suggested this book. I purchased and at first I thought it was very 'philosophical', but soon into Chapter 7 I realized that the preceeding pages were necessary to a full understanding of the 'real' stuff DB are made of. This book talks to you in plain English with examples that are really easy to follow.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.Anonymous
Posted Mon Jul 24 00:00:00 EDT 2000
You don't know anything about database design? No problem! I didn't and I picked this book up and suddendly I was a master of average level database desing. Now the fact that I'm a student studying computer science might give me an edge in this area, however if you don't know anything about computer programming this book gives you a good start too. Check it out if you get the chance. I'm sure there are better books out there but nothing as comprehensive to the wee lil' people like you and me.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.Anonymous
Posted Sat Feb 12 00:00:00 EST 2000
NEED SOMETHING? come here if you need to find something? Come here!
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
More About This Textbook
Overview
Leverage the common-sense methodology of a database expert for developing databases that really work with this hands-on guide to relational database design and mangement. After discussing design objectives, and explaining the importance of terminology, the author walks you through the entire design process, starting with determinng and defining mission objectives, and ending with reviewing data integrity. Whole chapters, complete with case studies, are devoted to analyzing your current database, establishing table structures, keys, field specifications, table relationships, business rules, and views. Concludes with a good exposition of "Bad Design - What Not to Do", obviously based on years of experience. te>
Editorial Reviews
From Barnes & Noble
Fatbrain Review
Leverage the common-sense methodology of a database expert for developing databases that really work with this hands-on guide to relational database design and mangement. After discussing design objectives, and explaining the importance of terminology, the author walks you through the entire design process, starting with determinng and defining mission objectives, and ending with reviewing data integrity. Whole chapters, complete with case studies, are devoted to analyzing your current database, establishing table structures, keys, field specifications, table relationships, business rules, and views. Concludes with a good exposition of "Bad Design - What Not to Do", obviously based on years of experience.Rick Miller
Database Design for Mere Mortals
The year: 1987. Having recently purchased a copy of Oracle for the PC, I upgraded my AT's memory to accommodate the behemoth and then set out to design a relational database that would satisfy all my information needs. I was doomed to failure from the start. I had no practical experience with relational database design and no formal education on the subject. So I dived right in -- and failed miserably. Not for lack of trying but for lack of a good reference book. One that explained logical database design in layman's terms, free of mathematical notation, written by someone with practical experience.
The year: 1997. The book I searched for in vain a decade ago is available now from Addison-Wesley Longman and is titled Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez. It meets my criteria for a good relational database design reference. The book explains the logical design of relational databases in layman's terms, free of mathematical notation. It's written by an analyst who has made mistakes, learned his lessons, and now has something to contribute to the volumes already written about relational database design.
Database Design for Mere Mortals focuses on the logical design of relational databases and in doing so spares you any unnecessary platform- and implementation-specific details. In retrospect, this is where I made my biggest mistake. By starting right away to build the database, creating tables and fields, trying to establish relations, etc., in Oracle, I made it difficult to incorporate changes to the logical design as the project progressed. I needed to split the project into discrete phases, beginning with the logical design phase, then, once all the details were worked out on paper, moving on to the implementation phase. Hernandez suggests not even touching a computer until the logical design phase is complete.
The material in the book is presented in three parts: Part I-Relational Database Design, Part II-The Design Process, and Part III-Other Database Design Issues. Hernandez recommends reading the book in chapter sequence. It's good advice because, otherwise, you may lose perspective and start wondering why he's doing things the way he does.
Part I sets the tone for the book, and I recommend starting there. But Part I gives you more than just a framework from which to move on to Part II; Part I is where he wins your confidence by demonstrating that his knowledge of the material is grounded in experience. For instance, although he mentions it in the introduction, he reiterates the distinction between logical design and implementation. He continues by pointing out how naive database designers will jump right in and begin the design process at the implementation phase rather than at the logical design phase. This, he warns, is an approach that once started, makes it extremely difficult to make changes to the database structures as the design progresses. He gives three reasons for adopting an effective logical database design methodology:
After reading this passage you begin to develop a feeling of camaraderie with the author. He understands the issues that confront you, and he knows you're going to make mistakes. However, you can minimize the impact these mistakes have on your project by following an organized database design methodology.
Hernandez concludes Part I by introducing you to the design methodology and terminology used in Part II. In Chapter 3-Terminology, he makes an excellent distinction between data and information:
He could have driven the point home by quoting Stephen Covey, "Begin with the end in mind."
Hernandez does an effective job of relating commonly used terms to formal terminology; for example, "a field (known as an attribute in relational database theory)." This aspect of the book alone makes it user friendly for the novice. However, in the section on Keys, he fails to mention candidate key. Still, I think this is a minor omission since he goes on to give in-depth coverage of candidate keys in Chapter 8.
The meat of the material appears in Part II-The Design Process. Comprised of Chapters 4 through 13, it's here that Hernandez presents, step by step, his seven phase design methodology. Here's a brief sketch of his approach: Draft mission statement and mission objectives, analyze the current database, create data structures (fields and tables), determine and establish table relationships, determine and define business rules, determine and establish views, then review the entire project to assure data integrity. It's clean and systematic. He even walks you step-by-step through the interview process and arms you with effective questioning techniques.
Part II is also littered with gems of wisdom and great ideas. I particularly like how he begins the design phase by writing, with help from management and users in the organization, a mission statement and a set of mission objectives. He then uses these documents to guide him, keeping him focused on the core purpose of the project throughout the design phase.
His various lists are arguably the best part of the book. Beginning in Chapter 7-Establishing Table Structures, he presents the Elements of the Ideal Field list. Then, when necessary, he presents additional lists to help summarize ideal characteristics of various aspects of relational database design. He ultimately provides you with Elements of the Ideal Table, Elements of a Candidate Key and Elements of a Primary Key. He also takes the time and effort to reprint these lists throughout the book, where appropriate, so you don't have to keep thumbing back to refresh your memory.
Although you will find his lists convenient, if you're an experienced analyst you'll find one aspect of the book somewhat amazing. Hernandez clearly demonstrates how to construct a set of relation schemas that effectively enforce the basic precepts of referential integrity without once mentioning functional dependencies and how they're used to reduce tables to the different levels of normal form. Then again, that's what makes his book so easy to read.
I did find one aspect of his methodology strange at first. During management and user interviews he suggests you keep a list of subjects and characteristics. Each list will later be used to design tables and fields. However, instead of starting with the subject list to create the tables, he starts by using the characteristics list. This, he says, helps eliminate any prejudice you may have about what tables should exist. Like I said, I found it strange at first but this technique appears extremely effective in determining required table structures.
You can tell the author did time in the design trenches because he does an excellent job of removing any romantic notion you may have about being a database analyst. His message is clear: It's grunge work -- and taking shortcuts in the design phase will generate more work than it saves.
Part III brings the book to a close with treatments on how not to design a database and when to break the rules. (You'd better have a good reason and make sure you document any rule you break.)
When I read a book I like to give it a thorough going over in search of typos. Alas, I found four. However, they're clearly accidental and should cause you little distraction. One particular typo gave me a chuckle. On page 369, the label for Figure 12-8 reads "Tales from a Contractors database." In this case "Tales..." should read "Tables...". Four typos in a book this size is an accomplishment. I've seen worse...much, much worse.
How can the book be improved? Although an outstanding text in its present form, I think the book's overall effectiveness could be improved by making the various lists available as tear-out cards. Additionally, a comprehensive checklist of his design methodology, also in the form of a tear-out card, would be nice to have.
Who should read this book? For starters, if you're new to relational database design and haven't taken a formal course on the subject, you'll find Database Design for Mere Mortals to be exactly the kind of reference you're looking for: It's clearly written and is packed full of great information. If you've taken a formal course on relational database analysis and design, whether at the undergraduate or graduate level, you will find this book to be a handy reference for the practical application of all that formal theory you learned in class. After all, what good is it to calculate the cover of a set of functional dependencies if you don't know what to do with them when you're done? Experienced database analysts would do well to review the text before the start of each new project. Either way, it will make an excellent addition to your professional library.
Overall, Database Design for Mere Mortals is an outstanding book. Michael Hernandez did an excellent job of making a difficult topic easy to understand.--Dr. Dobb's Electronic Review of Computer Books
Product Details
Related Subjects
Meet the Author
Michael J. Hernandez is an independent consultant and trainer specializing in relational databases. He has been a featured speaker at a variety of database conferences and is a top-rated Access instructor for Application Developer's Training Company.
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:
These are the settings you've made for the table relationship characteristics:
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...
Table of Contents
First Chapter
Chapter 10:Table Relationships
There is no substitute for the comfort supplied by the utterly taken-for-granted relationship.
-Iris Murdoch
As you learned in Chapter 3, a relationship is a connection between a pair of tables. A relationship exists when the tables are connected by a Primary key and a Foreign key, or are linked together by a linking table; the manner in which the tables are connected depends on the type of relationship that exists between them.
A relationship is a crucial part of the database because
In order to take advantage of the many benefits provided by a relational database, you must make certain that you establish each relationship carefully and properly. Failure to do so can make working with multiple tables at the same time difficult, and it can also make inserting, updating, and deleting records in related tables difficult. These types of problems are discussed later as the design process unfolds.
Types of Relationships
Before you begin to establish relationships between tables in the database, you must learn what types of relationships can exist between a pair of tables. Two tables that are related to each other have a specific type of relationship. Knowing how to properly identify each type of relationship is invaluable for designing a successful database.
As you learned in Chapter 3, there are three possible types of relationships that can exist between a pair of tables: one-to-one, one-to-many, and many-to-many.
One-to-One Relationships
A pair of tables are defined as bearing a one-to-one relationship if a single record in the first table is related to one and only one record in the second table, and a single record in the second table is related to one and only one record in the first table. Figure 10-2 shows a generic example of a one-to-one relationship.
In Figure 10-2, a single record in TABLE A is related to only one record in TABLE B, and a single record in TABLE B is related to only one record in TABLE A. A one-to-one relationship usually (but not always) involves a subset table. For the sake of example, assume that TABLE B is the subset table. Figure 10-3 shows how to diagram a one-to-one relationship. In a relationship diagram, the type of relationship is indicated by the type of line drawn between the tables. Later in this chapter, you'll learn how to indicate the characteristics of the relationship on the diagram as well.
One-to-Many Relationships
A one-to-many relationship is defined as one in which a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table. Two diagrams are needed to illustrate this type of relationship.
Say you're working with two tables, TABLE A and TABLE B. A one-to-many relationship exists between these tables because a single record in TABLE A can be related to one or more (but not necessarily all) records in TABLE B, and a single record in the TABLE B can be related to only one recordin TABLE A.
This is the most common relationship found between a pair of tables, and it is the easiest to identify. Figure 10-6 shows how to diagram a one-to-many relationship.
Many-to-Many Relationships
A many-to-many relationship exists between a pair of tables if a single record in the first table can be related to one or more records in the second table, and a single record in the second table can be related to one or more records in the first table.
Assume once again that you're working with TABLE A and TABLE B. A many-to-many relationship exists between these tables because a single record in TABLE A can be related to one or more records (but not necessarily all) in TABLE B, and a single record in TABLE B can be related to one or more (but not necessarily all) records in TABLE A.
Figure 10-9 shows how to diagram a many-to-many relationship.
Problems with Many-to-Many Relationships
Before you can use the data from the tables involved in a many-to-many relationship you must resolve a few problems. If the relationship is not properly established,
In order to illustrate these problems, consider the table structures in Figure 10-10. There is a many-to-many relationship between the STUDENTS table and the CLASSES table-one student can attend many classes, and one class can be associated with many students.
Currently there is no actual connection between the two tables. Unless you know how to properly establish a many-to-many relationship, you would probably be inclined to add the CLASS ID, CLASS NAME, and INSTRUCTOR ID fields from the CLASSES table to the STUDENTS table in order to identify the classes in which a student is currently enrolled. But you will see the problems that arise from such modifications if you "load" the revised STUDENTS table with sample data. Figure 10-11 shows the "amalgamated" version of this table loaded with sample data.
The problems with an improperly established many-to-many relationship are well-illustrated in Figure 10-11:
Fortunately, you will not have to worry about these problems: you're going to learn the proper way to establish a many-to-many relationship.
Establishing relationships always involves a three-step procedure: identifying the relationships that currently exist between the tables in the database, establishing each relationship in the appropriate manner, and then setting the proper characteristics for each relationship. When the relationships are in place, the tables are ready to be incorporated into Views. You'll learn how to define and use a View later, in Chapter 12.
Identifying Existing Relationships
When you were composing the table descriptions earlier in the database design process (back in Chapter 7, to be exact), you assembled a representative group of users and management to help you with that task. These people were also designated to represent the organization to help with decision making for the remainder of the database design process. At this stage you'll arrange for further meetings with this group to help you identify table relationships.
These people can provide valuable input because they can be expected to have a good perspective of how various subjects (or tables) are related. Although their perceptions of the manner in which these subjects are related may not always be complete or accurate, their contributions will still be useful in identifying most of the relationships. Relationships that are harder to identify can usually be clarified by "loading" given tables with some sample data; the sample data commonly reveals the type of relationship two tables should have.
In the first step of the procedure, you'll identify the relationships that currently exist between the tables. Begin this step by taking a particular table-you can choose a table at random because you'll repeat this process for every table anyway-and determine whether it has a relationship with any of the remaining tables. For example, assume you're working with these tables:
Say you've decided to start with the CLASSES table. Make up a two-column list, writing "Classes" in the first column and writing the names of the remaining tables in the second column, as shown in Figure 10-1.
Now determine whether the CLASSES table bears a relationship with any of the other tables in the list by using the process of elimination. You're only looking for direct relationships; There must be a specific connection between tables participating in a direct relationship. Tables that are indirectly related will be implicitly connected through a third table. (You don't have to worry about indirect relationships yet.)
Eliminate a pair of tables on the list by asking the participants questions about the records in each table. You want to determine the relationship between a single record in one table to one or more records in the other table, and vice versa. (Remember that each record represents a single instance of the subject represented by the table.) There are two types of questions you can ask:
Associative. General in nature, this type of question can be generically stated as follows: "Can a single record in (name of first table) be associated with one or more records in (name
of second table)?" For the example in Figure 10-12, you might use an associative question such as
"Can a single record in CLASSES be associated with one or more records in BUILDINGS?"
Contextual. This type of question contrasts a single instance of the subject represented by the first table against multiple instances of the subject represented by the second table. There are two categories within this type of question: ownership oriented and action oriented.
Ownership-oriented questions are characterized by the use of words or phrases such as "owns," "has," "is part of," and "contains." (An example of this type of question is "Can a single order contain one or more products?") Action-oriented questions use action verbs such as "make," "visit," "place," "teach," and "attend." (An example of this type of question is "Does a single flight instructor teach one or more types of classes?")
Use the type of question you believe to be the most appropriate for the pair of tables you're working with. After you've chosen the type of question you want to use, pose the question twice-once from the perspective of the first table, and then again from the perspective of the second table. The answers to both of these questions will identify the type of relationship that exists between the tables.
Continuing with the example, assume that this is your first question:
"Is a single class held in one or more buildings?"
The answer to this question will reveal the type of relationship that exists between these tables from the perspective of the CLASSES table. If the answer is
"A single class is held in only one building."
then a one-to-one relationship exists between these tables. If the answer is
"A single class may be held in more than one building."
then there exists a one-to-many relationship between the two tables.
Assume you are told that a single class is held in only one building. You now follow up with this question:
"Is a single building used to conduct one or more classes?"
The answer to this question reveals the type of relationship from the perspective of the BUILDINGS table. If the answer is yes, then a one-to-many relationship exists between these tables; if no, it's a one-to-one relationship.
Often relationships will differ from one perspective to the other. When both a one-to-one and a one-to-many relationship are identified for a particular pair of tables, the one-to-many becomes the "official" relationship for these tables, because it accurately represents the relationship from both perspectives.
Now diagram the relationship as shown in Figure 10-13 and cross out this pair of tables on the list.
Although you began asking about relationships from the perspective of the CLASSES table, the relationship you discovered is diagrammed from the perspective of the BUILDINGS table. Always diagram one-to-many relationships from left to right-your diagrams will be consistent, and they'll be easier to read in the long run. Also note that each table's Primary key has been added to the diagram. From now on, make certain that each time you diagram a table you include its Primary key. (As you'll see in the following step, the Primary key is useful when you are establishing relationships.)
Continue this procedure of identifying relationships for each pair of tables on the list. Keep in mind that some pairs of tables will have a direct relationship; others will not. You want to identify only direct relationships. After you've crossed out each pair of tables on the list, select another table, create a new list, and repeat this process. When you've finished identifying all the appropriate relationships, move on to the next step.
Establishing Each Relationship
This step involves defining a connection between the tables for each relationship. The manner in which you define the connection is determined by the type of relationship that exists between the tables.
One-to-One and One-to-Many Relationships
One-to-one and one-to-many relationships are established by using a Primary key and a Foreign key to connect the tables within the relationship. You'll learn the definition of a Foreign key in just a moment.
The One-to-One Relationship
In this type of relationship, one of the tables is referred to as the "main" table and assumes a dominant role in the relationship; the other table is referred to as the "subordinate" table and assumes a subordinate role in the relationship. In some instances, you can arbitrarily decide what role each table is to play within the relationship. In the case where one of the tables is a subset table, the subset table is usually assigned the subordinate role. But there are instances in which a subset table can assume the dominant role within the relationship. As you can see, this type of one-to-one relationship exists between the STAFF and
FACULTY tables shown in Figure 10-14.
You establish a one-to-one relationship by taking a copy of the Primary key from the main table and inserting it into the subordinate table, where it becomes a Foreign key. The name "Foreign key" comes from the fact that the subordinate table has its own Primary key, and since the field being added is the Primary key of a different table, it is "foreign" to the subordinate table. As long as you can visualize this process generically, you'll be able to establish a one-to-one relationship properly for any pair of tables involved in this type of relationship.
As you can see in Figure 10-14, the Primary key of the main table (STAFF) already exists in the subordinate table (FACULTY). That is because FACULTY is already a properly defined subset table. (You learned how to define a subset table properly in Chapter 7, and you established its Primary key in Chapter 8.)
Next consider the one-to-one relationship between the two tables shown in Figure 10-15.
In this example, MANAGERS is a subset table of EMPLOYEES, but
it has a direct relationship to DEPARTMENTS. A single manager is associated with only one department, and a single department is associated with only one manager; MANAGERS is the main table, and DEPARTMENTS is the subordinate table. (This is a good example of an instance in which a subset table is in the dominant position within a relationship.) To establish the relationship between these tables, take a copy of the Primary key from the main table (MANGERS) and insert it into the subordinate table (DEPARTMENTS). Then identify the new Foreign key (Employee ID) by placing the letters "FK" next to its name. The result of these steps is shown in Figure 10-16.
Note that the relationship diagram now shows both the Primary key and the Foreign key, and that each end of the connecting line points specifically to them, making it easier to identify the relationship and the fields used to establish the connection.
The One-to-Many Relationship
The technique used to establish a one-to-many relationship is similar to the one used to establish a one-to-one relationship. In this case, you take a copy of the Primary key from the table on the "one" side of the relationship and insert it into the table on the "many" side, where that field becomes a Foreign key. For example, consider the one-to-many relationship between the BUILDINGS table and the ROOMS table shown in Figure 10-17.
In this example, a single building can contain one or more rooms, but a single room is contained within only one building. You establish this relationship by taking a copy of the Primary key from the table on the "One" side (BUILDINGS) and inserting it into the table on the "Many" side (ROOMS), where it becomes a Foreign key. Now diagram the relationship, making the proper adjustments as you did with the diagram for the one-to-one relationship. Your diagram should look like the one shown in Figure 10-18.
When you diagram a one-to-many relationship, make certain that the connection point on the "One" side points to the Primary key, and that the connection point on the "Many" side points to the Foreign key. (Note that the middle line of the "crow's-foot" is the significant connection point-it should point directly to the Foreign key.) Setting up your diagram this way makes it easier to identify the relationship and the fields used to establish the connection.
The Many-to-Many Relationship
A many-to-many relationship is established using a linking table. You create the linking table by taking a copy of the Primary key from each table involved in the relationship and using those Primary keys to create the new linking table. Next you give the linking table a meaningful name, one that represents the nature of the relationship between the two tables. Then add the linking table to the final table list and make the proper entries for "Table Type" and "Table Description." Figure 10-19 shows a diagram of a many-to-many relationship that has been established for the STUDENTS and CLASSES tables. (Note the new diagram symbol used to represent a linking table.)
There are several points to note about the results of creating this linking table:
The many-to-many relationship has been dissolved. There is no longer a direct relationship between the STUDENTS and CLASSES tables. It has been replaced by two direct one-to-many relationships: one between STUDENTS and STUDENT CLASSES and another between CLASSES and STUDENT CLASSES. In the first relationship, a single record in STUDENTS can be associated with one or more records in STUDENT CLASSES, but a single record in STUDENT CLASSES table can be associated with only one record in STUDENTS. In the second relationship, a single record in the CLASSES table can be associated with one or more records in STUDENT CLASSES table, but a single record in STUDENT CLASSES can be associated with only one record in CLASSES.
The STUDENT CLASSES linking table is assigned a Composite Primary key, composed of two fields: STUDENT ID and CLASS ID. Except in rare instances, a linking table always contains a Composite Primary key.
The STUDENT CLASSES linking table contains two Foreign keys. Each of the two fields in the linking table is a copy of a Primary key from another table. Therefore, each is a Foreign key by definition and is treated as such individually. It is only when the fields are treated as a unit that they are referred to as the Composite Primary key of the table.
The linking table helps to keep redundant data to an absolute minimum. There is no superfluous data in this table at all.
In fact, the main advantage of this table structure is that it allows you to enter as few or as many classes for a single student as is necessary. Later in the database design process, you'll learn how to create Views to draw the data from these tables together in order to present it as meaningful information.
The name of the linking table reflects the purpose of the relationship it helps establish. The data stored in the STUDENT CLASSES table represents a student and the classes in which he or she is enrolled.
As you work with many-to-many relationships, there will be instances in which you will need to add fields to the linking table in order to reduce data redundancy and further refine the table structures. For example, consider the many-to-many relationship between the ORDERS and PRODUCTS tables shown in Figure 10-20.
As you can see, this relationship is improperly established-the fields PRODUCT NUMBER, QUOTED PRICE, and QUANTITY ORDERED were inserted into the ORDERS table in order to associate various products with a particular order. But the presence of these fields in the ORDERS table produces a large amount of redundant data, as shown in Figure 10-21.
There is so much redundant data largely because only one PRODUCT NUMBER can be entered into each record. Therefore, if a customer orders eight items, eight records will have to be entered in this table for this one order-one record for every item the customer orders. You can completely avoid this problem by properly establishing the relationship between the ORDERS and PRODUCTS tables with a linking table. Figure 10-22 shows a diagram of this relationship as it should be established.
Although this arrangement eliminates the redundant data, there is still one minor problem: QUOTED PRICE and QUANTITY ORDERED are no longer appropriate in the ORDERS table. First and foremost, their values are not directly determined by the Primary key of the ORDERS table. Second, they bear no relationship to any of the remaining fields in the table. They do, however, relate to a particular PRODUCT NUMBER that appears within a given order. Therefore, the QUOTED PRICE and QUANTITY ORDERED fields should be removed from the ORDERS table and placed in the ORDER DETAILS linking table. Figure 10-23 shows the revised diagram of this relationship.
Whenever you establish many-to-many relationships between tables, check each table within the relationship to determine whether there are any fields that should be transferred to the linking table. When in doubt, load all the tables with sample data; you should be able to see immediately which fields should be transferred to the linking table.
With all these techniques in mind, establish each of the relationships you identified in the previous step. Make certain you create a diagram for each relationship-you'll add new information to each diagram as the design process further unfolds.
Reviewing the Structure of Each Table
Once you've established the relationships between tables, review all of the table structures. As you established each relationship, you made modifications to the existing table structures and created several new table structures. (Remember the linking tables?) Therefore, you want to make certain that each table conforms to the Elements of the Ideal Table:
Elements of the Ideal Table
It represents a single subject, which can be an object or event.
It has a Primary key.
It does not contain multipart fields.
It does not contain multivalued fields.
It does not contain calculated fields.
It does not contain unnecessary duplicate fields.
It contains only an absolute minimum amount of redundant data.
If you determine that a table is not in conformance with the Elements of the Ideal Table, identify the problem and make the necessary modifications. Then take the table through the appropriate stages of the database design process until you reach the stage we're at now. As long as you've been following proper procedures thus far, you shouldn't encounter any problems with the tables.
Refining All Foreign keys
As you've seen, a Primary key from one particular table becomes a Foreign key in another table when that field is used to establish a relationship between those two tables. A Foreign key must conform to a set of elements, just as all the other keys you've worked with so far.
Elements of a Foreign Key
It has the same name as the Primary key from which it was copied. This rule should be adhered to unless there is an absolutely compelling reason not to do so. (Such a reason is shown in the example under "Alias" in Chapter 9.)
It uses a replica of the Field Specifications for the Primary key from which it was copied. This is in accordance with the sixth element of the Elements of the Ideal Field, as you learned in Chapter 7. There are, however, a few settings in the Logical Elements category of the Field Specifications that will be slightly different for the Foreign key than for its parent Primary key. Figure 10-24 shows the Logical Elements for an EMPLOYEE ID field used as a Primary key in the EMPLOYEES table, and Figure 10-25 shows the Logical Elements for the same EMPLOYEE ID field when it is used as a Foreign key in the ORDERS table.
As you can see in Figure 10-25, three minor changes have been made to these elements. The Type of Key setting is indicated as "Foreign" because the EMPLOYEE ID field is being used as a Foreign key in this instance. The Uniqueness setting is designated as "Non-Unique" because you want to be able to associate a single employee with many orders; if you marked this as "Unique," you'd only be able to enter an employee into the ORDERS table one time, which would greatly limit his or her sales potential! The Edit Rule setting is designated as "Enter Now, Edits Allowed" because there will be times when the wrong employee is credited for an order and you'll need to be able to change the value to reflect the correct employee for the order.
The setting in the Specification Information category of the field specification is also different for a Foreign key as compared with that of the Primary key from which it was created. Figure 10-26 shows the Specification Information for the EMPLOYEE ID field used as a Foreign key in the ORDERS table.
A Foreign key uses a replica field specification because its specification is based on an existing Primary key's unique specification. Make certain you include the name of the Primary key's parent table in the Source Specification item, as shown in Figure 10-26. This makes it easy to compare the Primary key's specifications and the Foreign key's specifications as the need arises.
It draws its values from the Primary key to which it refers. A Foreign key's range of values is limited to existing values of the Primary key to which it refers. For example, you cannot enter an invalid Employee ID into the ORDERS table. Any Employee ID you enter into the ORDERS table must first exist as an Employee ID in the EMPLOYEES table. This ensures consistency in the values of both fields in both tables.
Review the Foreign keys in each table to make certain that they conform to the Elements of a Foreign key. If they are not in accordance with these elements, make the appropriate modifications. But if you've followed proper procedure up to this point, it should be a rare instance when you would have to make any changes.
Establishing Relationship Characteristics
The final step in this procedure is to establish the characteristics of each relationship. These characteristics indicate what will occur when a record is deleted, the type of participation each table bears within the relationship, and to what degree each table participates in the relationship.
Establishing a Deletion Rule for Each Relationship
Now you must establish a deletion rule for the relationship. This rule defines what will happen if a user places a request to delete a record in the main table of a one-to-one relationship or in the "one" side of a one-to-many relationship. Defining a deletion rule for a relationship helps to guard against "orphaned" records, which are records that exist in a subordinate table of a one-to-one relationship but have no related records in a main table, or records that exist in the "many" side of a one-to-many relationship that have no related records in the "one" side.
Two options are available for the deletion rule:
Restrict. The requested record cannot be deleted if there are related records in the subordinate table of a one-to-one relationship or the "many" side of a one-to-many relationship. Related records must be deleted before the requested record can be deleted.
Cascade. The requested record will be deleted as well as all related records in the subordinate table of a one-to-one relationship or the "many" side of a one-to-many relationship.
Use a restrict deletion rule as a matter of course; use a cascade deletion rule very judiciously. The best way to determine whether a cascade deletion rule is appropriate for a set of related tables is to study the relationship diagram for those tables. Consider the diagram shown in Figure 10-27.
Select a pair of related tables and pose the following question:
"If a record in [name of main or 'one' table] is deleted, should related records in [name of subordinate or 'many' table] be deleted as well?"
Here the question is framed so that you can understand the premise behind it. When you pose this question against a particular pair of tables, such as the CUSTOMERS and ORDERS tables, just "fill in the blanks." The question now becomes:
"If a record in the CUSTOMERS table is deleted, should related records in the ORDERS table be deleted as well?"
If the answer to this question is no, you must use a restrict deletion rule for this relationship; otherwise, the cascade deletion rule is appropriate. The answer to this question greatly depends on how the data is being used within the database. If you cannot easily provide an answer, make note of the relationship and continue with the next table relationship. You'll revisit these relationships when you establish Business Rules for the database later in Chapter 11.
After you've identified the type of deletion rule you want to use for a relationship, add it to the relationship diagram. Restrict deletion rules are indicated by an "(R)" and cascade deletion rules by a "(C)." Place the designation under the connection line of the main table or the table on the "one" side of the relationship as shown in Figure 10-28.
You probably noticed that the deletion rule only applies to a record in the main table or the "one" side of the relationship. There is no need to worry about deleting records in the subordinate table or the "many" side of the relationship because there can be no adverse affects as a result of doing so.
Identifying the Type of Participation
for Each Table
Each table participates within a relationship in a particular manner. A table's type of participation determines whether a record must exist in that table before a record can be entered into the other table. There are two types of participation:
Mandatory. There must be at least one record in this table before you can enter any records into the other table.
Optional. There is no requirement for any records to exist in this table before you can enter any records into the other table.
The type of participation for most tables is usually determined later when you're defining Business Rules. However, it's common to establish the type of participation for tables in relationships where the type of participation for each table is obvious, is a result of common sense, or is in accordance with some particular set of standards. Consider the one-to-many relationship between the EMPLOYEES and CUSTOMERS tables shown in Figure 10-29.
Say that in this instance, a particular employee must be assigned to each customer. This employee acts as the customer's "account representative" and takes care of all transactions and communications between the organization and that customer. Although each customer must be associated with a particular employee, not every employee is required to be associated with even a single customer. Remember that many of the people represented in the EMPLOYEES table perform some other function within the organization and may never come into contact with a customer at all.
This scenario does not describe or define any special circumstances; it is the way the organization conducts its business. Therefore, you can infer that
the type of participation for the EMPLOYEES table should be designated as "Mandatory." The EMPLOYEES table must have at least one record because each customer is required to be associated with a particular employee.
the type of participation for the CUSTOMERS table should be designated as "Optional." It is unnecessary for any records to exist in the CUSTOMERS table before you can enter an employee into the EMPLOYEES table.
Once you've determined the type of participation for each table within the relationship, modify the relationship diagram to reflect this new information. A mandatory type of participation is symbolized by a vertical line; an optional type of relationship is symbolized by a circle. Figure 10-30 shows the relationship diagram for the EMPLOYEES and CUSTOMERS tables and illustrates the use of these two symbols.
Identifying the Degree of Participation
for Each Table
Now you must determine the degree to which each table will participate within the relationship. This is a simple matter of identifying the total number of records in one table that can be related to a single record in the other table. The factors used in determining the degree of participation-obvious circumstances, common sense, or conformance to some set of standards-are the same as those used to determine the type of participation. Therefore you will identify the degree of participation for some tables now and leave the remaining tables to be revisited when you define Business Rules for the database.
The degree of participation is symbolized by two numbers, separated by a comma, and enclosed in parentheses, such as "(1,8)." The numbers in this symbol represent the lower and upper boundaries, respectively, of the total number of records in one table that can be related to a single record in the other table. In this instance, a single record in one table can be related to a minimum of one record and a maximum of eight records in the other table.
When you add this symbol to a relationship diagram, place it over the connection line of the appropriate table. Figure 10-31 illustrates the proper use of this symbol within a relationship diagram. This particular diagram indicates the degree of participation for the relationship between the EMPLOYEES and CUSTOMERS tables.
In this example, the diagram indicates that a customer must be related to at least one employee, but no more than one. It also indicates that an employee is not required to be associated with a customer. On the other hand, an employee cannot be associated with more than ten customers.
Figure 10-32 shows an example of an unlimited degree of participation, which is represented by the letter N inside the parenthesis. In this instance, a customer can exist within the CUSTOMERS table without being associated with an order in the ORDERS table. On the other hand, a particular customer can be associated with an unlimited number of records in the ORDERS table. You certainly wouldn't want to limit the amount of purchases a customer can place.
Now that you know how to define the characteristics of a relationship, execute this step for every relationship you've established thus far. As you identify these characteristics, revise the diagram for each relationship to reflect the new information.
Verifying Table Relationships with Users and Management
The very last order of business is to verify the relationships. This is a relatively easy task that can be performed using the following checklist:
1. Make sure that each relationship between a pair of tables has been properly identified.
2. Make certain that each relationship