C
Design Guidelines
Here, in alphabetical order, are the various sets of design guidelines that appear throughout the book.
Defining and Establishing Field-Specific Business Rules
Select a table.
Review each field and determine whether it requires any constraints.
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 a Business Rule Specifications sheet.
Defining and Establishing Relationship-Specific Business Rules
Select a relationship.
Review the relationship and determine whether it requires any constraints.
Define the necessary business rules for the relationship.
Establish the rule by modifying the appropriate relationship characteristics.
Determine what actions will test the rule.
Record the rule on a Business Rule Specifications sheet.
Elements of a Candidate Key
It cannot be a multipart field.
It must contain unique values.
It cannot contain Null values.
Its value cannot cause a breach of the organization’s security or privacy rules.
Its value is not optional in whole or in part.
It comprises a minimum number of fields necessary to define uniqueness.
Its values must uniquely and exclusively identify each record in the table.
Its value must exclusively identify the value of each field within a given record.
Its value can be modified only in rare or extreme cases.
Elements of a Foreign Key
It has the same name as the primary key from which it was copied.
It uses a replica of the field specifications for the primary key from which it was copied.
It draws its values from the primary key to which it refers.
Elements of a Primary Key
It cannot be a multipart field.
It must contain unique values.
It cannot contain Null values.
Its value cannot cause a breach of the organization’s security or privacy rules.
Its value is not optional in whole or in part.
It comprises a minimum number of fields necessary to define uniqueness.
Its values must uniquely and exclusively identify each record in the table.
Its value must exclusively identify the value of each field within a given record.
Its value can be modified only in rare or extreme cases.
Rules for Establishing a Primary Key
Each table must have one—and only one—primary key.
Each primary key within the database must be unique—no two tables should have the same primary key unless one of them is a subset table.
Elements of the Ideal Field
It represents a distinct characteristic of the subject of the table.
It contains only a single value.
It cannot be deconstructed into smaller components.
It does not contain a calculated or concatenated value.
It is unique within the entire database structure.
It retains the majority of its characteristics when it appears in more than one 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 or 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.
Field-Level Integrity
Field-level integrity ensures the following:
The identity and purpose of a field is clear, and all the tables in which it appears are properly identified.
Field definitions are consistent throughout the database.
The values of a field are consistent and valid.
The types of modifications, comparisons, and operations that can be applied to the values in the field are clearly identified.
Guidelines for Composing a Field Description
Use a statement that accurately identifies the field and clearly states its purpose.
Write a clear and succinct statement.
Refrain from restating or rephrasing the field name.
Avoid using technical jargon, acronyms, or abbreviations.
Do not include implementation-specific information.
Do not make this description dependent upon the description of another field.
Do not use examples.
Guidelines for Composing a Table Description
Include a statement that accurately defines the table.
Include a statement that explains why this table is important to the organization.
Compose a description that is clear and succinct.
Do not include implementation-specific information in your table description, such as how or where the table is used.
Do not make the table description for one table dependent upon the table description for another table.
Do not use examples in a table description.
Guidelines for Creating Field Names
Create a unique, descriptive name that is meaningful to the entire organization.
Create a name that accurately, clearly, and unambiguously identifies the characteristic a field represents.
Use the minimum number of words necessary to convey the meaning of the characteristic the field represents.
Do not use acronyms, and use abbreviations judiciously.
Do not use words that could confuse the meaning of the field name.
Do not use names that implicitly or explicitly identify more than one characteristic.
Use the singular form of the name.
Guidelines for Creating Table Names
Create a unique, descriptive name that is meaningful to the entire organization.
Create a name that accurately, clearly, and unambiguously identifies the subject of the table.
Use the minimum number of words necessary to convey the subject of the table.
Do not use words that convey physical characteristics.
Do not use acronyms and abbreviations.
Do not use proper names or other words that will unduly restrict the data that can be entered into the table.
Do not use a name that implicitly or explicitly identifies more than one subject.
Use the plural form of the name.
Identifying Relationships
Use this procedure to identify the official relationship between a pair of tables within a table matrix:
Select a pair of tables and note the entry at the junction of the first table and the second table.
Locate the second table on the same side of the matrix you’re working on and note the entry and the junction between it and the first table on the opposite side of the matrix.
Apply the appropriate formula (shown next) to the two entries and identify the official relationship between the tables.
1:1 + 1:1 = 1:1
1:N + 1:1 = 1:N
1:N + 1:N = M:N
Diagram the relationship in the appropriate manner.
Cross out both entries on the matrix.
Identifying View Requirements
Use this procedure to identify your organization’s view requirements.
Review your notes with the group of user/management representatives.
Review the data entry, report, and presentation samples you gathered during the early stages of the design process.
Examine the tables and the subjects they represent.
Analyze the table relationships.
Study the business rules.
Interview Guidelines
Participant Guidelines
Make the participants aware of your intentions.
Let the participants know that you appreciate their taking part in the interview and that their responses to the interview questions are valuable to the overall design project.
Make sure everyone understands that you are the official arbitrator if and when a dispute arises.
Interviewer Guidelines
Choose an appropriate meeting platform to conduct your meeting.
Set a reasonable, practical limit for the number of people participating in each interview.
Conduct separate interviews for users and management.
When you have to interview several groups of people, designate a group leader for each group.
Prepare your questions prior to the interview.
If you’re not very good at taking notes, either assign that task to a dependable transcriber for each interview or inform the group ahead of time that you’re going to record the interview for reference purposes.
Give everyone your equal and undivided attention.
Keep the pace of the interview moving.
Always maintain control of the interview.
Mission Statements
A well-written mission statement has the following attributes:
It expresses its point succinctly and immediately.
It avoids unnecessary statements or details and is well defined.
It avoids phrases or sentences that explicitly describe specific tasks.
It makes sense to you (the database developer) and to those for whom you are designing the database.
Mission Objectives
A well-written mission objective has the following attributes.
It comprises a declarative sentence that clearly defines a general task and is free from unnecessary details.
It expresses itself in general terms that are succinct, to the point, and unambiguous.
It makes sense to you and to those for whom you are designing the database.
Relationship-Level Integrity
This type of integrity ensures the following.
The connection between the two tables (or key fields) in a relationship is sound.
You can insert new records into each table in a meaningful manner.
You can delete an existing record without producing any adverse effects.
There is a meaningful limit to the number of records that can be interrelated within the relationship.
Resolving a Multivalued Field
Use this generic procedure to resolve a multivalued field:
Remove the field from the table and use it as the basis for a new table. If necessary, rename the field in accordance with the field name guidelines that you learned earlier.
Take the primary key from the original table and incorporate it into the new table structure. This field will perform two specific functions in the new table: It will serve as part of the table’s composite primary key, and it will serve as a foreign key that helps to establish the relationship between the new table and the original table.
Assign an appropriate name, type, and description to the new table and add it to the Final Table List.
Table-Level Integrity
This type of integrity ensures the following:
There are no duplicate records in a table.
The primary key exclusively identifies each record in a table.
Every primary key value is unique.
Primary key values are not Null.