1. Chapter 1: The Relational Database
2. Chapter 2: Design Objectives
3. Chapter 3: Terminology
4. Chapter 4: Conceptual Overview
5. Chapter 5: Starting the Process
5.1. Conducting Interviews
5.1.1. Role
5.1.2. Who
5.1.3. Purpose
5.1.4. Participant Guidelines
5.1.5. Interviewer Guidelines
5.2. Defining the Mission Statement
5.2.1. The Well-Written Mission Statement
5.2.2. Composing a Mission Statement
5.3. Defining the Mission Objectives
5.3.1. Well-Written Mission Objectives
5.3.2. Composing Mission Objectives
6. Chapter 6: Analyzing the Current Database
6.1. Getting to Know the Current Database
6.1.1. Purpose
6.1.2. Key questions
6.1.3. Type of existing databases
6.1.3.1. Paper-based databases
6.1.3.1.1. Definition
6.1.3.1.2. Challenges
6.1.3.1.3. Purpose
6.1.3.1.4. Example
6.1.3.2. Legacy databses
6.1.3.2.1. Definition
6.1.3.2.2. Common issues
6.1.3.2.3. Advantages over paper-based databases
6.1.3.3. Human knowledge bases
6.1.4. Analysis process
6.1.5. Key considerations
6.1.6. Rule
6.2. Conducting the Analysis
6.2.1. Steps
6.2.2. Example
6.3. Looking at How Data Is Collected
6.3.1. Paper-based data
6.3.2. Computer programs
6.3.3. Web-based data
6.3.4. Organizing samples
6.4. Looking at How Information Is Presented
6.4.1. Reports
6.4.2. Screen presentations
6.4.3. Web pages
6.5. Conducting Interviews
6.5.1. Clarifying data samples
6.5.2. Understading daily data usage
6.5.3. Defining field and table structures
6.5.4. Identifying future information needs
6.6. Basic interview techniques
6.6.1. The Importance of Questions
6.6.2. The Interview Process
6.6.2.1. Open-ended questions
6.6.2.2. Closed questions
6.6.3. Identifying Subjects
6.6.3.1. How to identify subjects
6.6.3.2. Example
6.6.4. Identifying Characteristics
6.6.4.1. How to identify characteristics
6.6.4.2. Exapmle
6.6.5. Notes
6.6.5.1. User interviews
6.6.5.2. Manager interviews
6.7. Interviewing Users
6.7.1. Reviewing Data Type and Usage
6.7.1.1. Types of data
6.7.1.2. Usage of data
6.7.1.3. Process
6.7.1.4. Example
6.7.2. Reviewing the Samples
6.7.2.1. The goal
6.7.2.2. Process
6.7.2.3. Example
6.7.3. Reviewing Information Requirements
6.7.3.1. Current Information Requirements
6.7.3.1.1. The goal
6.7.3.1.2. Process
6.7.3.1.3. Example
6.7.3.2. Additional Information Requirements
6.7.3.2.1. The goal
6.7.3.2.2. Process
6.7.3.2.3. Example
6.7.3.3. Future Information Requirements
6.7.3.3.1. The goal
6.7.3.3.2. Process
6.7.3.3.3. Example
6.8. Interviewing Management
6.8.1. Reviewing Current Information Requirements
6.8.1.1. The goal
6.8.1.2. Process
6.8.2. Reviewing Additional Information Requirements
6.8.2.1. The goal
6.8.2.2. Process
6.8.3. Reviewing Future Information Requirements
6.8.3.1. The goal
6.8.3.2. Process
6.8.4. Reviewing Overall Information Requirements
6.8.4.1. The goal
6.8.4.2. Process
6.9. Compiling a Complete List of Fields
6.9.1. The Preliminary Field List
6.9.1.1. Step 1: Review and Refine the List of Characteristics
6.9.1.1.1. The goal
6.9.1.1.2. Process
6.9.1.2. Step 2: Determine Whether There Are New Characteristics in Any of Your Samples
6.9.2. The Calculated Field List
6.9.3. Reviewing Both Lists with Users and Management
6.10. Example: Analyzing the Current Database
6.10.1. Introduction
6.10.2. Collecting samples
6.10.3. Interviewing staff
6.10.4. Handling new field request
6.10.5. Interviewing Mike
6.10.6. Creating the preliminary field list
6.10.7. Separating calculated fields
7. Chapter 7: Establishing Table Structures
7.1. Defining the Preliminary Table List
7.1.1. Identifying Implied Subjects
7.1.1.1. Review prelimitnary field list
7.1.1.2. Importance
7.1.1.3. Process
7.1.1.4. Cross-checking with subjects list
7.1.2. Using the List of Subjects
7.1.2.1. Resolve Duplicate Items
7.1.2.2. Resolve Items That Represent the Same Subject
7.1.2.2.1. Compare, both lists
7.1.2.2.2. Resolve duplicates
7.1.2.2.3. Repeat until names are standardized
7.1.2.3. Combine lists
7.1.3. Using the Mission Objectives
7.1.3.1. Identify subjects from mission objectives
7.1.3.2. Cross-check with preliminary table list
7.1.3.3. Repeat untill all objectives are reviewed
7.2. Defining the Final Table List
7.2.1. Refining table names
7.2.1.1. Unique
7.2.1.2. Concise
7.2.1.3. Clear
7.2.1.4. Consistent
7.2.2. Table types
7.2.2.1. Data
7.2.2.2. Linking
7.2.2.3. Subset
7.2.2.4. Validation
7.2.3. Table Descriptions
7.2.3.1. Definition
7.2.3.2. Importance
7.2.3.3. Clearity
7.2.3.4. Avoid
7.2.3.5. Example
7.2.3.6. Interviewing user & management
7.2.3.6.1. Conduct interviews
7.2.3.6.2. Gather information
7.2.3.6.3. Finalize descriptions
7.3. Associating Fields with Each Table
7.3.1. Identify fields
7.3.2. Organize on Paper
7.3.3. Ensure completeness
7.4. Refining the Fields
7.4.1. Improving the Field Names
7.4.1.1. Unique & descriptive
7.4.1.2. Clarity & specificity
7.4.1.3. Table name prefixes
7.4.1.4. Concise names
7.4.1.5. Avoid acronyms
7.4.1.6. No redundant words
7.4.1.7. No multi-concepts
7.4.1.8. Singular form
7.4.1.9. Naming conventions
7.4.2. Using an Ideal Field to Resolve Anomalies
7.4.2.1. Ideal field
7.4.2.1.1. Definition
7.4.2.1.2. Purpose
7.4.2.2. Characteristics
7.4.2.2.1. Distinct feature
7.4.2.2.2. Single value
7.4.3. Resolving Multipart Fields
7.4.3.1. Problems
7.4.3.2. Resolution
7.4.3.2.1. Identify distince items
7.4.3.2.2. Create separate fields
7.4.4. Resolving Multivalued Fields
7.4.4.1. Problems
7.4.4.2. Solution (New Table)
7.4.4.2.1. Remove field from original table
7.4.4.2.2. Create a new table
7.4.4.2.3. Use a connecting field
7.4.4.2.4. Assign proper naming
7.4.4.2.5. Ensure minimal redundancy
7.5. Refining the Table Structures
7.5.1. Redundant data & deplicate fields
7.5.1.1. Definition
7.5.1.2. Issues
7.5.1.3. Acceptable cases
7.5.1.4. Unacceptable cases
7.5.1.5. Duplicate fields
7.5.1.6. Solutions
7.5.2. Using an Ideal Table to Refine Table Structures
7.5.2.1. Elements of the ideal table
7.5.2.1.1. Guidelines
7.5.2.1.2. Purpose
7.5.2.1.3. Characteristics of an ieadl table
7.5.2.2. Resolving unnecessary duplicate fields
7.5.2.2.1. Importance
7.5.2.2.2. Problems
7.5.2.2.3. Type of unnecessary duplicated fields
7.5.2.3. Establishing subset table
7.5.2.3.1. Identifying issues
7.5.2.3.2. Creating subset table
7.5.2.3.3. Linking subset table
7.5.2.3.4. Refining previously undidentified subset table
7.6. Example: Establishing Table Structures
7.6.1. Review preliminary field list
7.6.2. Refine preliminary table list
7.6.3. Cross-check with mission objectives
7.6.4. Convert to final table list
7.6.5. Assign & refine fields
8. Chapter 8: Keys
8.1. Why Keys Are Important
8.1.1. Ensure unique identificaiton
8.1.2. Enforce integrity
8.1.3. Establish relationships
8.2. Establishing Keys for Each Table
8.2.1. Candidate Keys
8.2.1.1. Elements of a Candidate Key
8.2.1.1.1. No
8.2.1.1.2. Should be
8.2.1.2. Artificial Candidate Keys
8.2.1.2.1. No
8.2.1.2.2. Should be
8.2.2. Primary Keys
8.2.2.1. Elements of a Primary Key
8.2.2.1.1. Must be
8.2.2.1.2. Validation process
8.2.2.1.3. Example
8.2.2.2. Rules for Establishing a Primary Key
8.2.2.2.1. Each table has one primary key
8.2.2.2.2. Premary key must be unique
8.2.2.2.3. Purpose
8.2.3. Alternate Keys
8.2.3.1. Definition
8.2.3.1.1. Candidate keys not chosen as primary key
8.2.3.1.2. Provide alternative unique identification
8.2.3.2. Marking in table structure
8.2.3.2.1. "AK" (Alternate key) for single-field keys
8.2.3.2.2. "CAK" (Composite alternate key) fro multi-field keys
8.2.3.3. Handling in database design
8.2.3.3.1. If not used, treat as a normal field
8.2.3.3.2. Not a focus in logical design
8.2.3.3.3. Used in RDBMS implementation
8.2.4. Non-keys
8.2.4.1. Definition
8.2.4.1.1. Not a candidate, primary, alternate, or foreign key
8.2.4.1.2. Represents a characteristic of the table’s subject
8.2.4.2. Purpose
8.2.4.2.1. Holds descriptive data
8.2.4.2.2. Value depends on the primary key
8.2.4.3. Marking in table structure
8.2.4.3.1. No special designation
8.2.4.3.2. No need to explicitly mark in table structure
8.3. Table-Level Integrity
8.3.1. Definition
8.3.1.1. Ensures data consistency in a table
8.3.1.2. Part of overall data integrity
8.3.2. Key rules
8.3.2.1. No duplicate records
8.3.2.2. Primarykey uniquely identifies each record
8.3.2.3. Preimary key values are unique
8.3.2.4. Primary key valies connot be null
8.3.3. Establishment
8.3.3.1. Define a valid primary key
8.3.3.2. Ensure compliance with primary key rules
8.3.3.3. Enhance integrity with field specifications
8.4. Reviewing the Initial Table Structures
8.4.1. Purpose
8.4.1.1. Validate table structures with user & management
8.4.1.2. Ensure database meets business requirements
8.4.2. Key tasks
8.4.2.1. Check for missing subjects -> add as tables
8.4.2.2. Validate table name & description -> Clarify if ambigous
8.4.2.3. Validate field names -> Ensurre consistency & explain changes
8.4.2.4. Verify fields in table -> Add any missing attributes
8.5. Example: Establishing Keys
8.5.1. Step 1: Candiate keys
8.5.1.1. Meet all requirements
8.5.1.2. Example
8.5.2. Step 2: Primary keys
8.5.2.1. Choose best candidate key
8.5.2.2. Selection criteria
8.5.2.2.1. Single-field -> Composite
8.5.2.2.2. Meaningful name
8.5.2.2.3. Uniquely identifies table
8.5.2.3. Example
8.5.3. Step 3: Subset tables
8.5.3.1. Must share primary key with parent table
8.5.3.2. Example
8.5.4. Step 4: Final review
8.5.4.1. Verify: Subjects, table names, field names, fields
8.5.4.2. Adjustment: Add CALL PRIORITY to VENDORS table
9. Chapter 9: Field Specifications
9.1. Why Field Specifications Are Important
9.1.1. Field-level integrity
9.1.2. Data integrity
9.1.3. Understading data
9.1.4. Data dictionary
9.2. Field-Level Integrity
9.2.1. Clear identity & purpose
9.2.2. Consistency
9.2.3. Valid modifications
9.2.4. Sound field structure
9.3. Anatomy of a Field Specification
9.3.1. General elements
9.3.1.1. Field name
9.3.1.2. Parent table
9.3.1.3. Specification type
9.3.1.3.1. Unique
9.3.1.3.2. Generic
9.3.1.3.3. Replica
9.3.1.4. Additional elements
9.3.1.4.1. Source specification
9.3.1.4.2. Shared by
9.3.1.4.3. Alias(es)
9.3.1.5. Field Description
9.3.1.5.1. Guidelines for Writing a Field Description
9.3.2. Physical elements
9.3.2.1. Date type
9.3.2.2. Length
9.3.2.3. Decimal places
9.3.2.4. Character support
9.3.2.4.1. Letters
9.3.2.4.2. Numbers
9.3.2.4.3. Keyboard characters
9.3.2.4.4. Special characters
9.3.3. Logical elements
9.3.3.1. Key type
9.3.3.2. Key structure
9.3.3.3. Uniqueness
9.3.3.4. Null support
9.3.3.5. Values entered by
9.3.3.6. Required value
9.3.3.7. Range of values
9.3.3.8. Edit rule
9.4. Using Unique, Generic, and Replica Field Specifications
9.4.1. Unique specification
9.4.2. Generic specification
9.4.3. Replica specification
9.4.4. Key considerations
9.5. Defining Field Specifications for Each Field in the Database
9.5.1. Process of defining specifications
9.5.2. Collaboration with Users & Management
9.5.3. Defining & reviewing specifications
9.5.4. Handling missing or incomplete specifications
9.6. Example: Defining Field Specifications
9.6.1. Initial field specification definition
9.6.1.1. Defined specifications before meeting
9.6.1.2. Fields were straightforward
9.6.2. Meeting with Mike & staff
9.6.2.1. Reviewed predefined specifications
9.6.2.2. No objections from staff
9.6.2.3. Identified issue with CATEGORY field
9.6.3. Resolving CATEGORY field issue
9.6.3.1. Unclear range of values
9.6.3.2. Set a general range for now
9.6.3.3. Planned revisit during business rules phase
9.6.4. Completion of field specifications process
9.6.4.1. Addressed all concerns
10. Chapter 10: Table Relationships
10.1. Why Relationships Are Important
10.1.1. Definition
10.1.2. Benefits
10.1.3. Relationship-level integrity
10.1.4. Consequences of poor relationships
10.1.5. Example
10.2. Types of Relationships
10.2.1. One to one relationships
10.2.1.1. Definition
10.2.1.2. Characteristics
10.2.1.3. Example
10.2.2. One to many relationships
10.2.2.1. Definition
10.2.2.2. Characteristics
10.2.2.3. Example
10.2.3. Many to many relationships
10.2.3.1. Definition
10.2.3.2. Challenges
10.2.3.3. Incorrect method
10.2.3.4. Correct approach
10.2.3.5. Example
10.3. Identifying Existing Relationships
10.3.1. Process steps
10.3.1.1. Create table matrix
10.3.1.2. Analyze each table pair
10.3.1.3. Determine relationship types
10.3.1.4. Apply relationship formulas
10.3.1.5. Diagram relationships
10.3.2. Question types
10.3.3. Relationship types
10.3.3.1. One-to-one (1:1)
10.3.3.2. One-to-many (1:N)
10.3.3.3. Many-to-many (M:N)
10.3.3.4. Self-referencing relationships
10.3.4. Relationship formulas
10.3.4.1. 1:1 + 1:1 = 1:1
10.3.4.2. 1:N + 1:1 = 1:N
10.3.4.3. 1:N + 1:N =M:N
10.4. Establishing Each Relationship
10.4.1. One-to-one relationships
10.4.1.1. table roles
10.4.1.1.1. Parent table
10.4.1.1.2. Child table
10.4.1.2. Role assignment rules
10.4.1.3. Implementation mechanism
10.4.1.4. Relationship diagramming
10.4.1.5. Examples
10.4.2. One-to-many relationships
10.4.2.1. Establishment technique
10.4.2.2. Diagramming
10.4.2.3. Example
10.4.2.4. Resolving multivalue fields
10.4.2.4.1. Process
10.4.2.4.2. Inherent relationship
10.4.2.4.3. Example
10.4.3. Many-to-many relationships
10.4.3.1. Establishment method: Linking table
10.4.3.1.1. Definition
10.4.3.1.2. Creation procedure
10.4.3.1.3. Diagram symbol
10.4.3.2. Results of creating linking table
10.4.3.2.1. Dissolves direct M:N relationship
10.4.3.2.2. Creates two 1:N relationship
10.4.3.2.3. Contains two foreign keys
10.4.3.2.4. Use composite primary key
10.4.3.2.5. Minimizes redundant data
10.4.3.3. Field management in linking table
10.4.3.3.1. When to add fields to linking table
10.4.3.3.2. Moving fields from original table
10.4.3.3.3. Eliminating duplicate data across tables
10.4.3.4. Example
10.4.4. Self-Referencing Relationships
10.4.4.1. One-to-One and One-to-Many
10.4.4.1.1. Definition
10.4.4.1.2. Establishment methods
10.4.4.1.3. Diagramming guidelines
10.4.4.1.4. Considerations and challenges
10.4.4.1.5. Alternative approaches
10.4.4.2. Many-to-Many
10.4.4.2.1. Definition
10.4.4.2.2. Establishment method
10.4.4.2.3. Example
10.4.4.2.4. Implementation benefits
10.4.5. Reviewing the Structure of Each Table
10.4.5.1. When to Review
10.4.5.2. Ideal table criteria
10.4.5.3. Review actions
10.4.5.4. Common issues
10.5. Refining All Foreign Keys
10.5.1. Naming
10.5.2. Specification type
10.5.3. General elements modificaitons
10.5.4. Logical elements modifications
10.5.5. Value constraints
10.6. Establishing Relationship Characteristics
10.6.1. Deletion rules
10.6.1.1. Purpose
10.6.1.2. Rule types
10.6.1.2.1. Deny (D)
10.6.1.2.2. Restrict (R)
10.6.1.2.3. Cascade (C)
10.6.1.2.4. Nullify (N)
10.6.1.2.5. Set default (S)
10.6.1.3. Selection process
10.6.1.3.1. Default approach: use Restrict
10.6.1.3.2. For standard relationships
10.6.1.3.3. For self-referencing relationships
10.6.1.4. Implementation
10.6.2. Participation Types
10.6.2.1. Definition
10.6.2.2. Types
10.6.2.2.1. Mandatory
10.6.2.2.2. Optional
10.6.2.3. Determination timing
10.6.2.4. Standard relationships
10.6.2.5. Self-referencing relationships
10.6.3. Degree of participation
10.6.3.1. Definition
10.6.3.2. Notation
10.6.3.2.1. Format (min,max)
10.6.3.2.2. Example
10.6.3.2.3. Unlimited maximum: user "N"
10.6.3.3. Determination factors
10.6.3.3.1. Obvious circumstances
10.6.3.3.2. Common sense
10.6.3.3.3. Organizational standards
10.6.3.3.4. Business policies
10.6.3.4. placement
10.6.3.4.1. Standard relationships
10.6.3.4.2. Self-referencing relationships
10.6.3.5. Example
10.6.3.5.1. Employee-Customer relationship
10.6.3.5.2. Staff-Manager relationship
10.6.3.5.3. Customer-Orders relationship
10.6.4. Relationship verification
10.6.4.1. Purpose
10.6.4.2. Verifycation checklist
10.6.4.2.1. Relationship Identification
10.6.4.2.2. Relationship Establishment
10.6.4.2.3. Foreign Key Compliance
10.6.4.2.4. Deletion Rules
10.6.4.2.5. Participation Types
10.6.4.2.6. Participation Degrees
10.6.4.3. Approval process
10.7. Relationship-Level Integrity
10.7.1. Difinition
10.7.1.1. Third component of overall data integrity
10.7.1.2. Follows table-level and field-level integrity
10.7.1.3. Achieved through verification of relationships
10.7.2. Core components
10.7.2.1. Sound Connections
10.7.2.2. Meaningful Record Insertion
10.7.2.3. Safe Record Deletion
10.7.2.4. Record Quantity Control
10.7.3. Progression
10.7.3.1. Leads to business rules (final integrity component)
10.8. Example: Identifying and Establishing Relationships
10.8.1. Table involved
10.8.1.1. CUSTOMERS
10.8.1.2. EMPLOYEES
10.8.1.3. INVOICES
10.8.1.4. PRODUCTS
10.8.1.5. VENDORS
10.8.2. Relationship identification process
10.8.3. Discovered relationships
10.8.3.1. CUSTOMERS to INVOICES (1:N)
10.8.3.2. EMPLOYEES to INVOICES (1:N)
10.8.3.3. PRODUCTS to INVOICES (M:N)
10.8.3.4. VENDORS to PRODUCTS (1:N)
10.8.4. Relationship implementation
10.8.4.1. One-to-many (1:N)
10.8.4.2. Many-to-many (M:N)
10.8.5. Verification steps
10.8.5.1. Table structure review
10.8.5.2. Foreign key compliance check
10.8.5.3. Field specifications modification
10.8.6. Relationship characteristics
10.8.6.1. Deletion rules defined
10.8.6.2. Participation types identified
10.8.6.3. Participation degrees established
11. Chapter 11: Business Rules
11.1. What Are Business Rules?
11.1.1. Definition
11.1.2. Purpose
11.1.3. Influence Areas
11.1.4. Characteristics
11.1.5. Examples
11.1.6. Implementation
11.1.7. Types of business rules
11.1.7.1. Database oriented
11.1.7.1.1. Defined with in logical database design
11.1.7.1.2. Implementation methods
11.1.7.1.3. Characteristics
11.1.7.1.4. Example
11.1.7.2. Application oriented
11.1.7.2.1. Cannot be established in logical design
11.1.7.2.2. Implementation locations
11.1.7.2.3. Reasion for exclusion from logical design
11.1.7.2.4. Example
11.2. Categories of Business Rules
11.2.1. Field-Specific Business Rules
11.2.1.1. Definition
11.2.1.2. Characteristics
11.2.1.3. Example: single element impact
11.2.1.4. Example: multiple element impact
11.2.2. Relationship-Specific Business Rules
11.2.2.1. Definition
11.2.2.2. Areas of impact
11.2.2.2.1. Degree of Participation
11.2.2.2.2. Type of Participation
11.2.2.3. Implementation
11.2.2.4. Example
11.2.3. Defining and Establishing Business Rules
11.2.3.1. Define field-specific rules first
11.2.3.2. Establish field-specific rules
11.2.3.3. Define relationship-specific rules
11.2.3.4. Establish relationship-specific rules
11.3. Defining and Establishing Business Rules
11.3.1. Defining and Establishing Field-Specific Business Rules
11.3.1.1. Step 1: Select a Table
11.3.1.1.1. Consider tables with familiar structure first
11.3.1.1.2. Key Questions
11.3.1.1.3. Resources to consult
11.3.1.2. Step 2: Review Each Field
11.3.1.2.1. Examine field specifications
11.3.1.2.2. Key Question
11.3.1.2.3. If yes → proceed to Step 3
11.3.1.2.4. If no → move to next field
11.3.1.3. Step 3: Define Business Rules
11.3.1.3.1. Identify constraints implied by responses
11.3.1.3.2. Transform constraints into clear rule statements
11.3.1.3.3. Example
11.3.1.4. Step 4: Establish Rules by Modifying Field Specifications
11.3.1.4.1. Identify elements affected by each rule
11.3.1.4.2. Make appropriate modifications
11.3.1.4.3. Example Modifications
11.3.1.5. Step 5: Determine Testing Actions
11.3.1.5.1. When might rule be violated?
11.3.1.5.2. Key Questions
11.3.1.6. Step 6: Document on Business Rule Specifications Sheet
11.3.1.6.1. Advantages
11.3.1.6.2. Sheet Components
11.3.2. Defining and Establishing Relationship-Specific Business Rules
11.3.2.1. Step1: Select a Relationship
11.3.2.1.1. Choose any relationship to start
11.3.2.1.2. Review relationship diagram
11.3.2.1.3. Key Questions
11.3.2.2. Step 2: Review the Relationship
11.3.2.2.1. Examine relationship characteristics
11.3.2.2.2. Consider current settings
11.3.2.2.3. Key Question
11.3.2.2.4. If yes → proceed to Step 3
11.3.2.2.5. If no → review next relationship
11.3.2.3. Step 3: Define Business Rules
11.3.2.3.1. Identify constraints from responses
11.3.2.3.2. Transform constraints into clear rule statements
11.3.2.3.3. Example
11.3.2.4. Step 4: Establish Rules by Modifying Relationship Characteristics
11.3.2.4.1. Identify affected relationship characteristics
11.3.2.4.2. Make appropriate modifications
11.3.2.4.3. Example
11.3.2.5. Step 5: Determine Testing Actions
11.3.2.5.1. When might rule be violated?
11.3.2.5.2. Key Questions
11.3.2.5.3. Important Notes
11.3.2.6. Step 6: Document on Business Rule Specifications Sheet
11.4. Validation Tables
11.4.1. What Are Validation Tables?
11.4.1.1. Difinition
11.4.1.2. Characteristics
11.4.1.3. Structure
11.4.1.3.1. Primary key field
11.4.1.3.2. Non-key field
11.4.1.4. Purpose
11.4.2. Using Validation Tables to Support Business Rules
11.4.2.1. Implementation Steps
11.4.2.2. Example Case
11.4.2.3. Establishing the Relationship
11.4.2.4. Setting Relationship Characteristics
11.4.2.5. Modifying Field Specifications
11.4.2.6. Testing the Rule
11.5. Reviewing the Business Rule Specifications Sheets
11.6. Example: Defining and Establishing Business Rules
11.6.1. Field-Specific Business Rules
11.6.1.1. PRODUCTS Table Analysis
11.6.1.2. Business Rule Definition
11.6.1.3. Implementation with Validation Table
11.6.1.4. Relationship Characteristics
11.6.1.5. Field Specification Modifications
11.6.1.6. Rule Testing
11.6.1.7. Documentation
11.6.2. Relationship-Specific Business Rules
11.6.2.1. Relationship Review
11.6.2.2. Business Rule Definition
11.6.2.3. Implementation
11.6.2.4. Rule Testing
11.6.2.5. Documentation
12. Chapter 12: Views
12.1. What Are Views?
12.1.1. Definition
12.1.2. Base tables
12.1.3. Characteristics
12.1.4. Benefit & uses
12.2. Anatomy of a View
12.2.1. Data
12.2.1.1. Single-Table Data View
12.2.1.1.1. Definition & Characteristics
12.2.1.1.2. Behavior
12.2.1.1.3. Data Modification
12.2.1.1.4. Purpose
12.2.1.1.5. Example
12.2.1.2. Multitable Data View
12.2.1.2.1. Definition & Requirements
12.2.1.2.2. Behavior
12.2.1.2.3. Data Modification
12.2.1.2.4. Data Presentation
12.2.1.2.5. Primary Keys
12.2.1.2.6. Example
12.2.2. Aggregate
12.2.2.1. Definition & Purpose
12.2.2.2. Aggregate Functions
12.2.2.3. Behavior
12.2.2.4. Key Characteristics
12.2.2.5. Uses & Applications
12.2.2.6. Example: CLASS REGISTRATION View
12.2.3. Validation
12.2.3.1. Definition & Purpose
12.2.3.2. Comparison to Validation Tables
12.2.3.3. Structure & Design
12.2.3.4. Benefits & Functions
12.2.3.5. Example: APPROVED SUBCONTRACTORS
12.3. Determining and Defining Views
12.3.1. Working with Users and Management
12.3.1.1. Process Overview
12.3.1.2. Preparation Before Meetings
12.3.1.3. Meeting Considerations
12.3.1.3.1. Review notes with the group
12.3.1.3.2. Examine data entry, report, and presentation samples
12.3.1.3.3. Review tables and their subjects
12.3.1.3.4. Analyze table relationships
12.3.1.3.5. Study business rules
12.3.1.4. Post-Identification Steps
12.3.2. Defining Views
12.3.2.1. Process Overview
12.3.2.2. Information Sources
12.3.2.3. Example: CUSTOMER CALL LIST
12.3.2.4. Field Selection
12.3.2.5. Documentation
12.3.3. Calculated Fields in Views
12.3.3.1. Definition & Characteristics
12.3.3.2. Example 1: LAST PURCHASE DATE
12.3.3.3. Example 2: CUSTOMER NAME
12.3.3.4. Implementation Process
12.3.3.5. Guidelines for Usage
12.3.4. Filtering View Data
12.3.4.1. Purpose & Function
12.3.4.2. Example: CUSTOMER CALL LIST
12.3.4.3. Implementation Requirements
12.3.4.4. Best Practices
12.3.4.5. Documentation
12.3.5. View Specifications Sheet
12.3.5.1. Purpose & Requirements
12.3.5.2. Component: Name
12.3.5.3. Component: Type
12.3.5.4. Component: Base Tables
12.3.5.5. Component: Calculated Field Expressions
12.3.5.6. Component: Filters
12.3.5.7. Documentation Value
12.4. Example: Determining and Defining Views
12.4.1. Meeting Agenda & Process
12.4.2. Identified Views
12.4.3. PREFERRED CUSTOMERS View
12.4.4. VENDOR PRODUCT COUNT View
13. Chapter 13: Reviewing Data Integrity
13.1. Why You Should Review Data Integrity
13.1.1. Purpose of Final Review
13.1.2. Importance Despite Careful Design
13.1.3. Consequences of Integrity Issues
13.1.4. Benefits of Review
13.2. Reviewing and Refining Data Integrity
13.2.1. Modular Approach
13.2.2. Table-Level Integrity
13.2.3. Field-Level Integrity
13.2.4. Relationship-Level Integrity
13.2.5. Business Rules
13.2.6. Views Review
13.2.7. Outcome of Completed Review
13.3. Assembling the Database Documentation
13.3.1. Final table list
13.3.2. Field Specifications sheets
13.3.3. Calculated field list
13.3.4. Table structure diagrams
13.3.5. Relationship diagrams
13.3.6. Business Rule Specifications sheets
13.3.7. View diagrams
13.3.8. View Specifications sheets
13.4. Done at Last!
13.5. Example—Wrap-Up
14. Chapter 14: Bad Design-What Not to DO
14.1. Flat-File Design
14.1.1. Definition & Overview
14.1.2. Characteristics
14.1.3. Major Problems
14.1.3.1. Redundant data
14.1.3.2. Inconsistent data
14.1.3.3. Lack of data integrity
14.1.3.4. Inefficient storage
14.1.4. Specific Structure Issues
14.1.4.1. Multipart fields
14.1.4.2. Calculated fields
14.1.4.3. Unnecessary duplicate fields
14.1.4.4. No true primary key
14.1.4.5. Multiple subjects in one table
14.1.5. Conclusion
14.2. Spreadsheet Design
14.2.1. Appropriate Uses
14.2.2. Common Misconception
14.2.3. Example: Store Manager Tracking
14.2.4. Specific Problems
14.2.4.1. Duplicate fields
14.2.4.2. Multipart fields
14.2.4.3. Multivalued fields
14.2.4.4. Usability issues
14.2.5. Consequences
14.2.6. Recommendation
14.3. Database Design Based on the Database Software
14.3.1. RDBMS vs Formal Design Methods
14.3.2. The Design Trap
14.3.3. Reasons to Avoid RDBMS-Driven Design
14.3.3.1. Decision limitations
14.3.3.2. Requirements distortion
14.3.3.3. Knowledge constraints
14.3.3.4. Skill level constraints
14.3.3.5. Final RDBMS may be unsuitable
14.3.4. Consequences
14.3.5. Proper Approach
14.4. A Final Thought
15. Chapter 15: Bending or Breaking the Rules
15.1. When May You Bend or Break the Rules?
15.1.1. Analytical Database Design
15.1.1.1. Definition & Purpose
15.1.1.2. Key Characteristics
15.1.1.3. Design Rule Violations
15.1.1.4. Justification for Exceptions
15.1.1.5. Recommended Design Approach
15.1.1.6. Best Practices
15.1.2. Improving Processing Performance
15.1.2.1. Common Misconception
15.1.2.2. The Real Question: Data Integrity vs Performance
15.1.2.3. Consequences of Breaking Rules
15.1.2.3.1. Inconsistent data
15.1.2.3.2. Redundant data
15.1.2.3.3. Impaired data integrity
15.1.2.3.4. Inaccurate information
15.1.2.4. Better Alternatives (Try First)
15.1.2.4.1. Hardware enhancements
15.1.2.4.2. Operating system optimization
15.1.2.4.3. Database structure review
15.1.2.4.4. Implementation review
15.1.2.4.5. Application program review
15.1.2.5. When Rule Breaking Might Be Acceptable
15.2. Documenting Your Actions
15.2.1. When to Document
15.2.2. Importance of Documentation
15.2.3. Essential Documentation Elements
15.2.3.1. Reason for breaking rules
15.2.3.2. Design principle being violated
15.2.3.3. Database aspect being modified
15.2.3.4. Specific modifications made
15.2.3.5. Anticipated effects
15.2.4. Documentation Management