bdfd1m02 | Database Administration |
Database Administration
Tutorial for Planning and Designing a Database
Organizing a Database
Normalization
Primary Key
Dependency
Business Application
First Normal Form
Second Normal Form
Removing Independent Attributes
Third Normal Form
Resulting Tables
Duplicating Data across Tables
Optimizing the Database Design
Duplicating Data to Improve Performance
Assessing the Normalized Tables
Checking Seat Availability
Booking a Passenger on a Flight
Displaying Passengers Booked on a Flight
Displaying All Flights Booked for a Passenger
Displaying an Aircraft Configuration
Canceling Passenger Bookings
Improving Access to the Data
Displaying Passenger Information by Name or Number
Accessing Flight Information
Accessing Passengers from the Seat Table
Accessing Aircraft Configurations from the Flight Table
Final Database Design Structure
Mapping Tables to TPFDF Files
Before You Begin
Data Requirements
Data Field Lengths
Passenger LRECs
Calculating the Number of Subfiles Needed
Block Size
Chaining
Overflow Blocks
Mapping the Passenger Name File
Distributing the Passenger Name LRECs
File Structure
Mapping the Passenger Number File
Distributing the Passenger Number LRECs
Ensuring a Good Distribution
File Structure
Mapping the Aircraft File
Distributing the Aircraft LRECs
Allowing for Expansion
File Structure
Mapping the Flight File
Distributing the Flight LRECs
Allowing for Expansion
File Structure
Mapping the Seat File
Distributing the Seat File LRECs
Mapping the Passenger File
Spreading Data over Several LRECs
Coding the DSECT and DBDEF Macros
DSECT and DBDEF for the Passenger Name File
DSECT
DBDEF
DSECT and DBDEF for the Passenger Number File
DSECT
DBDEF
DSECT and DBDEF for the Flight File
DSECT
DBDEF
DSECT and DBDEF for the Seat File
DSECT
DBDEF
DSECT and DBDEF for the Passenger File
DSECT
DBDEF
DSECT and DBDEF for the Aircraft File
DSECT
DBDEF
Creating the DSECT and DBDEF Macros
Creating a DSECT Macro Definition
Sample DSECT Macros Supplied with the TPFDF Product
File Names
Modifying the Sample DSECT Macros
Modifying the Beginning DSECT Macro Statements
Assigning Values to Global Set Symbols
File Description
Block Header
Defining the LREC Size and LREC ID Fields
Defining Different LREC IDs in the Same File
DSECT Instructions for Defining User Fields in LRECs
Algorithm DSECT Statements
Ending DSECT Statements
Creating C Structures for Files with Existing DSECT Definitions
Creating a DBDEF Macro Definition
DBDEF Macro Parameter Syntax
Global DSECT Override Parameters
Default Key Parameters
Basic Index Parameters
Data Extraction Parameters
Parameters for TPFDF Recoup and TPFDF CRUISE Processing for Customer-Format Files
TPFDF Recoup User Exits
B+Tree File Parameters
Miscellaneous Parameters
Examples and Concepts
Database Optimization Examples
Reducing I/O Processing
Reducing File Accesses
Combining Files
Using Algorithms instead of Indexing
Indexing
Basic Indexing
Simple Indexing
Multiple Indexing to a Single Detail Subfile
Multiple-Level Indexing
Single Indexing to Multiple Detail Files
Block Indexing
Implementing Block Index Support
Block Index File Characteristics
B+Tree Indexing
B+Tree Index File Node Blocks
B+Tree Data File Data Blocks
B+Tree Data File Characteristics
Additional Considerations When Using B+Tree Indexing
Structure of a Data File That Uses B+Tree Indexing
Defining the DSECT and DBDEF for a Data File That Uses B+Tree Indexing
Defining the DSECT and DBDEF for a B+Tree Index File
Multiple ECB Chain Chasing
Partitioning and Interleaving
Partitions
Advantages of Partitioned Files
Example of Partitioning
Coding the DSECT for Partitioned Files
Adding a New Partition
Interleaves
Advantages of Interleaved Files
Coding the DSECT Macro for Interleaved Files
Adding Blocks to an Interleave
Database Design Hints and Tips
File Integrity
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Selecting an Optimum Block Size
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Reducing the Number of Overflow Blocks
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Setting Different Sizes for Overflow Blocks
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Packing Files Regularly
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Reducing Overflow by Frequent Packing
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Packing Subfiles after Replacing an LREC
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Using New Pool Blocks for Overflow Blocks
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Specifying a Lower Packing Limit
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Logging Data at Optimum Intervals
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Maintaining a Log File
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Balancing Updating Speed against Accessing Speed
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Getting the Right Amount of Working Storage
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Specifying a Display Order for LRECs
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Linking Logically Related Data
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Managing a First-In-First-Out (FIFO) File
Problem
Solution
DSECT Set Symbols
DBDEF Statements
Application Coding
Using Customer-Format Files
NAB-Type Files with Fixed-Length Items
NAB-Type Files with Variable-Length Items
ADD/DEL-Type Files with Fixed-Length Items
ADD/DEL-Type Files with Variable-Length Items
CNT Files Using the CNT Parameter
CNT Files Using the CPT Parameter
Files Containing Fixed-Position References
Index