• About This Document
  • Organization of This Guide
    • Database Access Methods
    • Transactional Programming with the MicroKernel Engine
    • Relational Programming
    • Appendixes
• Overview of Zen Access Methods
• Developer Quick Start
  • Choosing An Access Method
  • Database Connection Quick Reference
    • ADO.NET Connections
      • Sample ADO.NET DB Connection Code
    • JDBC Connections
      • Sample JDBC Connection Code
    • Java Class Library
      • Sample JCL Connection String
    • DSN-Less Connections
      • Example
  • ODBC Information
    • Other SQL Access Methods
      • JDBC
      • PDAC
  • Additional Resources for Application Developers
    • Conceptual Information
    • Reference Information
    • Online Developer Resources
    • Sample Code
• Developing Applications for the MicroKernel Engine
  • MicroKernel Engine Environment
    • Documentation
  • Configuration Issues for MicroKernel Engine
• MicroKernel Engine Fundamentals
  • Overview of the MicroKernel Engine
    • MicroKernel Engine Environment
      • Configuration Notes
  • Pages
    • Page Types
    • Page Size
      • Page Size Criteria
      • Large vs. Small Page Size
        • Factors To Consider When Determining Page Size
  • File Types
    • Standard Data Files
    • Data-Only Files
    • Key-Only Files
    • Large Files
    • Long File Names
  • Data Types
  • Key Attributes
    • Key Attributes Description
      • Segmentation
      • Duplicatability
      • Modifiability
      • Sort Order
      • Case Sensitivity
      • Null Value
        • Legacy Null
        • True Null Indexes
        • Rules for True Null Keys
        • NIS Values
        • Sorting of True Null Values
        • Linked Duplicate Keys and True Nulls
        • Repeating Duplicate Keys and True Nulls
        • Unique Keys and True Nulls
        • Nonmodifiable Keys and True Nulls
        • Get Operations and True Nulls
        • Distinct True Nulls
        • Multi-Segmented True Null Keys
        • Excluding Records from an Index
        • Use of Null Indicator Segment in Extended Operations.
        • True Nulls and the SQL Engine
        • True Nulls and Extended Operations
      • Alternate Collating Sequences
      • User-Defined ACS
      • International Sort Rules
    • Key Specification
      • Key Specification Block
      • Key Flag Values
      • Limitations and Side Effects
  • Database URIs
    • Syntax
    • Parameter Precedence
      • Precedence Examples
    • Special Characters
      • Examples
    • Remarks
    • Examples
    • IPv6
  • Double-Byte Character Support
  • Record Length
  • Data Integrity
    • Record Locks
    • Transactions
      • Exclusive Transactions
      • Concurrent Transactions
      • Exclusive vs. Concurrent
    • Transaction Durability
    • System Data
    • Shadow Paging
    • Backing Up Your Files
  • Event Logging
  • Performance Enhancement
    • System Transactions
      • Preparation Phase
      • Writing Phase
      • Frequency of System Transactions
        • Less Frequent
        • More Frequent
    • Memory Management
    • Page Preallocation
    • Extended Operations
  • Disk Usage
    • Free Space List
    • Index Balancing
    • Data Compression
    • Blank Truncation
• Designing a Database
  • Understanding Data Files
  • Creating a Data File
    • Data Layout
      • Key Attributes
      • File Attributes
    • Creating File and Key Specification Structures
      • Sample Data Buffer for File and Key Specifications Using BTRV Entry Point
    • Creating a File with Page Level Compression
    • Calling the Create Operation
      • Create Operation
    • Create Index Operation
  • Calculating the Logical Record Length
  • Choosing a Page Size
    • Optimum Page Size For Minimizing Disk Space
      • Record Overhead in Bytes Without Record Compression
      • Record Overhead in Bytes With Record Compression
      • Page Overhead in Bytes
      • Physical Record Length Worksheet
      • Optimum Page Size Example
    • Minimum Page Size
  • Estimating File Size
    • Formula and Derivative Steps
      • Page Sizes of Special Pages by File Format
  • Optimizing Your Database
    • Duplicatable Keys
      • Linked-Duplicatable Keys
      • Repeating-Duplicatable Keys
      • Linked vs. Repeating
    • Page Preallocation
    • Blank Truncation
    • Record Compression
    • Index Balancing
    • Variable-tail Allocation Tables
    • Key-Only Files
  • Setting Up Security
    • Owner Names
    • Exclusive Mode
    • SQL Security
• Language Interfaces Modules
  • Interface Modules Overview
    • Programming Notes
  • C/C++
    • Interface Modules
      • BTRAPI.C
      • BTRAPI.H
      • BTRVEXID.H
      • BTRCONST.H
      • BTITYPES.H
      • BTRSAMP.C
    • Programming Requirements
  • Delphi
  • DOS (Btrieve)
    • Interface Modules
      • BTRAPI.C
      • BTRAPI.H
      • BTRCONST.H
      • BTITYPES.H
  • Pascal
    • Source Modules
      • BBTRAPID.PAS
      • BTRCONST.PAS
      • BTRSAMPD.PAS
  • Visual Basic
• Interface Libraries
  • Overview of Interface Libraries
    • Linux and macOS
  • Distributing Zen Applications
    • Distribution Rules for Zen
    • Installing Zen with your Application
• Working with Records
  • Sequence of Operations
  • Accessing Records
    • Accessing Records by Physical Location
      • Physical Currency
      • Step Operations
    • Accessing Records by Key Value
      • Logical Currency
        • NCC Insert Operation
      • Get Operations
    • Reading Variable-Length Records
      • Get Equal Operation in C
      • Get Equal Operation in Visual BASIC
    • Accessing Records by Chunks
      • Intrarecord Currency
      • Chunk Operations
  • Inserting and Updating Records
    • Ensuring Reliability in Mission-Critical Inserts and Updates
    • Inserting Nonduplicatable Keys
    • Inserting and Updating Variable-Length Records
      • Insert Operation
    • Reading and Updating Fixed-length Portions
    • Updating Nonmodifiable Keys
    • No-Currency-Change (NCC) Operations
  • Multirecord Operations
    • Terminology
    • Background
    • Validation
    • Optimization
      • Examples
    • Performance Tips
      • Connectors
      • Reject Count
  • Adding and Dropping Keys
• Supporting Multiple Clients
  • Btrieve Clients
    • Assumptions
    • Action Codes
    • Conflict Codes
  • Passive Concurrency
  • Record Locking
  • User Transactions
    • Locks
      • Explicit Record Locks in a Nontransactional Environment
      • Single-Record Locks
      • Multiple-Record Locks
      • When a Record Has Already Been Locked
    • Record Locks in Concurrent Transactions
      • Implicit Record Locks
        • Example Without Implicit Locks
    • Implicit Locks
    • File Locks
  • Examples of Multiple Concurrency Control
    • Example 1
    • Example 2
  • Concurrency Control for Multiple Position Blocks
  • Multiple Position Blocks
  • ClientID Parameter
• Debugging Your Btrieve Application
  • Trace Files
    • MicroKernel Trace File of a BUTIL STAT Call
  • Indirect Chunk Operations in Client-Server Environments
  • Engine Shutdowns and Connection Resets
  • Reducing Wasted Space in Files
• Btrieve API Programming
  • Fundamentals of Btrieve API Programming
    • Btrieve API Flow Chart
      • Inserting Records
      • Updating Records
      • Deleting Records
  • Visual Basic Notes
  • Delphi Notes
  • Starting a Zen Application
    • Adding Zen Source Modules
  • Btrieve API Code Samples
    • Creating a File
      • Sample Code
        • Visual Basic (Creating a File)
        • Delphi (Creating a File)
        • C/C++ (Creating a File)
      • Sample Structures (Creating a File)
        • Visual Basic (Creating a File) – Sample Structure
        • Delphi (Creating a File) – Sample Structure
        • C/C++ (Creating a File)– Sample Structure
    • Inserting Records
      • Sample Code
        • Visual Basic (Inserting Records)
        • Delphi (Inserting Records)
        • C/C++ (Inserting Records)
      • Sample Structures (Inserting Records)
        • Visual Basic (Inserting Records) – Sample Structure
        • Delphi (Inserting Records) – Sample Structure
        • C/C++(Inserting Records) – Sample Structure
    • Updating Records
      • Sample Code
        • Visual Basic (Updating Records)
        • Delphi (Updating Records)
        • C/C++ (Updating Records)
      • Sample Structures (Updating Records)
        • Visual Basic (Updating Records) – Sample Structure
        • Delphi (Updating Records) – Sample Structure
        • C/C++ (Updating Records) – Sample Structure
    • Performing Step Operations
      • Sample Code
        • Delphi (Step Operations)
        • C/C++ (Step Operations)
      • Sample Structures
        • Delphi (Step Operations) – Sample Structure
        • C/C++ (Step Operations) – Sample Structure
    • Performing Get Operations
      • Sample Code
        • Visual Basic (Get Operations)
        • Delphi (Get Operations)
        • C/C++ (Get Operations)
      • Sample Structures (Get Operations)
        • Visual Basic (Get Operations) – Sample Structure
        • Delphi (Get Operations) – Sample Structure
        • C/C++ (Get Operations) – Sample Structure
    • Chunking, BLOBs, and Variable-Length Records
      • Sample Code
        • Visual Basic (Chunking/BLOBs/Variable-Length Records)
      • Sample Structures (Chunking/BLOBs/Variable-Length Records)
        • Visual Basic (Chunking/BLOBs/Variable-Length Records) – Sample Structure
    • Working with Segmented Indexes
      • Sample Code
        • Visual Basic (Segmented Indexes)
        • Delphi (Segmented Indexes)
        • C/C++ (Segmented Indexes)
  • Declarations of Btrieve API Functions for Visual Basic
• Creating a Database
  • Named Databases
  • Bound Databases
  • Creating Database Components
  • Naming Conventions
    • Unique Names
    • Valid Characters
    • Maximum Name Lengths
    • Case Sensitivity
  • Creating a Data Dictionary
  • Creating Tables
    • Aliases
  • Creating Columns
  • Creating Indexes
    • Index Segments
    • Index Attributes
• Relational Database Design
  • Overview of Database Design
  • Stages of Design
    • Conceptual Design
    • Logical Design
      • Table Relationships
      • Normalization
        • First Normal Form
        • Second Normal Form
        • Third Normal Form
      • Keys
    • Physical Design
• Inserting and Deleting Data
  • Overview of Inserting and Deleting Data
  • Inserting Values
  • Transaction Processing
  • Deleting Data
  • Dropping Indexes
  • Dropping Columns
  • Dropping Tables
  • Dropping an Entire Database
• Modifying Data
  • Overview of Modifying Data
  • Modifying Tables
  • Setting Default Values
  • Using UPDATE
• Retrieving Data
  • Overview of Retrieving Data
  • Views
    • Features of Views
    • Temporary and Stored Views
    • Read-Only Tables in Views
    • Mergeable Views
  • Selection Lists
  • Sorted and Grouped Rows
  • Joins
    • Joining Tables with Other Tables
    • Joining Views with Tables
    • Types of Joins
      • Equal Joins
      • Nonequal Joins
      • Cartesian Product Joins
      • Self Joins
      • Left, Right, Full Outer Joins
  • Subqueries
    • Subquery Limitations
    • Correlated Subqueries
  • Restriction Clauses
    • Restriction Clause Operators
      • Boolean Operators
      • Condition Operators
      • Expression Operators
    • Restriction Clause Examples
      • OR and Equal To (=)
      • IN
      • LIKE
  • Functions
    • Aggregate Functions
    • Arguments to Aggregate Functions
    • Aggregate Function Rules
    • Scalar Functions
• Storing Logic
  • Stored Procedures
    • Stored Procedure and Positioned Update
    • Declaring Stored Procedures
    • Invoking Stored Procedures
    • Deleting Stored Procedures
  • SQL Variable Statements
    • Procedure-Owned Variables
    • Assignment Statements
  • SQL Control Statements
    • Compound Statement
    • IF Statement
    • LEAVE Statement
    • LOOP Statement
    • WHILE Statement
  • SQL Triggers
    • Timing and Ordering of Triggers
      • Specifying the Triggered Action Time
      • Specifying Trigger Order
    • Defining the Trigger Action
• Managing Data
  • Overview of Managing Data
  • Defining Relationships Among Tables
    • Referential Integrity Definitions
  • Keys
    • Primary Keys
      • Creating Primary Keys
      • Dropping Primary Keys
      • Changing Primary Keys
    • Foreign Keys
      • Creating Foreign Keys in Existing Tables
      • Creating Foreign Keys When Creating a Table
      • Dropping Foreign Keys
  • Referential Constraints
    • Referential Integrity Rules
      • Insert Rule
      • Update Rule
      • Delete Rule
        • Anomaly on Delete-Connected Cycles
        • Anomaly on Multiple Paths
  • Referential Integrity in the Sample Database
    • Creating the Course Table
    • Adding a Primary Key to Course
    • Creating the Student Table with Referential Constraints
  • Administering Database Security
    • Understanding Database Rights
    • Establishing Database Security
    • Enabling Security
    • Creating User Groups and Users
      • Creating User Groups
      • Creating Users
    • Granting Rights
      • Granting Rights to the PUBLIC Group
      • Granting Rights to User Groups
      • Granting Rights to Users
    • Dropping Users and User Groups
    • Revoking Rights
    • Disabling Security
    • Retrieving Information about Database Security
  • Concurrency Controls
    • Transaction Processing
    • Starting and Ending Transactions
    • Using Savepoints to Nest Transactions
    • Special Considerations
    • Isolation Levels
      • Exclusive Isolation Level (SQL_TXN_SERIALIZABLE)
      • Cursor Stability Isolation Level (SQL_TXN_READ_COMMITTED)
      • Transactions and Isolation Levels
      • Avoiding Deadlock Conditions
        • Deadlock Conditions under Exclusive Isolation Level
        • Deadlock Conditions under Cursor Stability Isolation Level
    • Passive Control
  • Atomicity in Zen Databases
    • Transaction Control in Procedures
• A. Sample Collations Using International Sorting Rules
  • German Sample Collations
    • Unsorted Data
    • Sorted Data
  • Spanish Sample Collations
    • Unsorted Data
    • Sorted Data
  • French Sample Collations
    • Unsorted Data
    • Sorted Data
• B. Sample Database Tables and Referential Integrity
  • Overview of the Demodata Sample Database
  • Structure of the Demodata Sample Database
    • Assumptions
    • Entity Relationships
  • Example of Referential Integrity in the Demodata Database
  • Table Design of the Demodata Sample Database
    • BILLING Table
    • CLASS Table
    • COURSE Table
    • DEPT Table
    • ENROLLS Table
    • FACULTY Table
    • PERSON Table
    • ROOM Table
    • STUDENT Table
    • TUITION Table

About This Document

B. Sample Database Tables and Referential Integrity