•  About This Document
  • Where to Get Information
  • Who Should Read This Manual
  •  Manual Organization
    • Database Access Methods
    • Transactional Programming with the MicroKernel Engine
    • Relational Programming
    • Appendixes
  • Typographical Conventions
•  Overview of Zen Access Methods
  • Overview of Zen Access Methods
  • SQL Access in Zen
•  Developer Quick Start
  • Choosing An Access Method
  •  Database Connection Quick Reference
    •  ADO.NET Connections
      • Sample ADO.NET DB Connection Code
    •  ADO/OLE DB Connections
      • Sample ADO/OLE 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
      • ADO/OLEDB
      • 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
        • Non-Modifiable 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
    • 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
      • Optimum Page Size Example
    • Minimum Page Size
  •  Estimating File Size
    • Formula and Derivative Steps
  •  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
    • Registering Zen ActiveX Files
    • 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 Non-Duplicatable Keys
    •  Inserting and Updating Variable-Length Records
      • Insert Operation
    • Reading and Updating Fixed-length Portions
    • Updating Non-Modifiable Keys
    • No-Currency-Change (NCC) Operations
  •  Multi-Record 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 Non-Transactional Environment
      • Single-Record Locks
      • Multiple-Record Locks
      • When a Record Has Already Been Locked
    •  Record Locks in Concurrent Transactions
      • Implicit Record 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
•  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
•  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

Sample Database Tables and Referential Integrity

proggde_debug_chap

proggde_btrapiprog_chap

proggde_crtdatabe_chap

NAMING_CONVENTIONS

CREATING_INDEXES

proggde_reldbdesign_chap

proggde_insdeldata_chap

proggde_modddata_chap

proggde_rtrivedata_chap

proggde_sortlogic_chap

proggde_mangdata_chap

proggde_sampisr_chap

proggde_samptbls_chap