(Base One logo) .NET database and distributed computing tools

Base One Data Dictionary

The BFC Database Library is built on a Data Dictionary, which provides a complete description of record layouts and indexes of the database, for validation and efficient data access. You can use the data dictionary for automated database creation, including building tables, indexes, and referential constraints, and granting access rights to individual users and groups. The database dictionary supports the concept of Attached Objects, which allow database records to include compressed BLOBs (Binary Large Objects) containing images, text, sounds, video, documents, spreadsheets, or programmer-defined data types.

The data dictionary stores useful metadata, such as field descriptions, in a format that is independent of the underlying database system.  Some of the functions served by the Data Dictionary include:

  • Ensuring efficient data access, especially with regard to utilization of indexes
  • Partitioning the database into both logical and physical regions
  • Specifying validation criteria and referential constraints to be automatically enforced
  • Supplying pre-defined record types for Rich Client features, such as security and administration facilities, attached objects, and distributed processing
  • Supporting automatic database creation with dynamic storage allocation parameters

(click for zoom - Sample Data Dictionary Utility Screen)
Sample Data Dictionary Utility Screen

There is a complete facility for managing the data dictionary, so you can view, administer, and report on database metadata. Programmers can use the automated database creation capability during development to build tables and indexes, specify referential constraints, and set storage allocation parameters. When the application goes into production, the data dictionary facility gives the database administrator and end-users useful tools for managing and viewing the database structure.

With Base One's active data dictionary, programmers and administrators can create and maintain database tables, without having to construct tedious, back-end-specific scripts. By localizing structural information in the Data Dictionary, the Database Library reduces maintenance costs and the risk of programming errors. In addition, the Database Library automatically caches data dictionary information locally to improve performance.

The Data Dictionary and Built-In Record Types

The data dictionary is an important part of Rich Client architecture. If you've ever created a database in SQL and then taken a look at what's in it, you may have noticed that the database starts out with quite a few back-end dependent tables. These tables describe the database itself and are "built-in" tables as far as the DBMS is concerned.

Similarly, Rich Client architecture adds its own layer of Built-in Record Types (tables), maintained automatically in the database. The Database Library speeds up programming by using these predefined records, which contain the basic information needed to build most business systems. Built-in record types are automatically added when a Rich Client database is created.

There are built-in record types for the data dictionary, the security system, the distributed processing (batch) facility, the help system, and the screen dictionary. When you run the Build Application Database function, all the built-in record types are created, and in some cases they are populated with the initial data required to run a Rich Client application.

Underneath this architecture are the built-in record types (tables) corresponding to each part of the data dictionary.

BFC Data Dictionary Record Types

Data dictionary records (regions, records, fields, and indexes) are the most basic examples of built-in record types. The System Region consists of all the built-in record types. It is broken down into subregions, such as the Data Dictionary Region, the Security Region and the Batch Region, which group the built-in record types by function.

The data dictionary describes all the other database regions and contains the description of all the other record types, both built-in and application-specific. For example, within the Security Region, there is one User Master Record for each user allowed to access the database. The user master record contains fields that identify the user, such as the user's name, logon ID, security group, and unique number (User Code).

The data dictionary can define every field, record type, region, and index in the database using these built-in records:

  • Database Region Dictionary records (logical hierarchy)
  • Database Record Type Dictionary records (lists relational tables)
  • Database Field Dictionary records (lists columns within tables)
  • Database Index Dictionary records

The Database Field Dictionary describes each field and is used for allocating database buffer memory. The Database Record Type Dictionary provides sufficient information about record types to dynamically create them. The Database Region Dictionary breaks the database down into convenient, logical groupings of record types.

The Database Index Dictionary is used to help perform queries efficiently. Each record contains information about an index in the database, such as the list of key fields, the record type (table) from which index entries are taken, whether or not the index keys are unique, and the storage dictionary record that defines the CREATE parameters used for index creation.

A single set of data dictionaries exists for each Rich Client database. This means that all applications running against the same database share the same set of data dictionaries.

Also contained in the data dictionary are records for the optional parameters that most database systems support in their CREATE TABLE and CREATE INDEX commands. These built-in records are used to specify storage parameters:

  • Database Storage Dictionary records
  • Database Macro Dictionary records

The Database Storage Dictionary defines the storage parameters that will be used when each record type table and index is created. There is a complete set of database creation parameters available for all of the supported back-end databases. (This includes, for example, entries for the Sybase SQL Anywhere DBSPACE parameter and for the Oracle TABLESPACE and PCTFREE parameters.)

Every record type (table) and index can be associated with a Storage Dictionary record. If no Storage Dictionary record is provided for a given record type or index, then the CREATE SQL commands use the back-end's defaults. Record types and indexes can use the same Storage Dictionary record, which lets them share the same storage parameters, and groups them logically for the DBA. Fields in the Storage Dictionary records contain macros rather than actual values.

Each Storage record is associated with a set of macros, and each macro equates to a parameter. This allows enormous flexibility and control in setting creation parameters.

The Database Macro Dictionary specifies the value to be substituted for each storage parameter. Using macros in the storage dictionary (rather than actual values) affords the greatest flexibility at database creation time. An application is free to use the Macro Dictionary to store any of its own macros as well as those required for database creation.

Data Dictionary Tab View

The Data Dictionary Tab View displays the full contents of the Database Library's built-in data dictionary, and it provides a front-end to the active data dictionary facility. This allows programmers and database administrators to define database tables and then create and maintain them, without having to construct scripts or command files that are specific to a particular back-end DBMS.

Starting from a back-end independent data dictionary layout, the back-end dependent SQL needed to build a new database (CREATE TABLE, CREATE INDEX, ALTER TABLE, GRANT, etc.) is generated. Application programmers and DBAs can define each database record type, associated index, and referential integrity constraint with the Data Dictionary Tab View, or by directly editing Load Dictionary command files.

The Data Dictionary Tab View is a tabbed form for entering and maintaining all table details including:

  • Region IDs and region names
  • Record types (tables) and remarks
  • Fields, including field numbers, names, types, lengths, scales, masks, primary keys, foreign key sequence numbers, deletion rules
  • Indexes, with field key lists
  • Storage records (for specifying database creation parameters)
  • Macros (used with storage records to specify database creation parameters)

Data Dictionary Reports

The Database Field Dictionary Report describes the record layouts for all built-in record types, and the Database Record Type Dictionary Report provides an overview on each record type (table).

If you create the record types without using the active data dictionary, you can run the Check Dictionary Report to verify that the database catalog's view is the same as the data dictionary's. Any discrepancies can then be resolved by comparing the data dictionary field (column) definitions with those in the database catalog, and making the necessary adjustments. The Database Library depends on the accuracy of the dictionary information for essential activities, such as automatically creating record layouts in memory, doing data validation, and constructing efficient queries.

Data Dictionary Reports Dialog

Data Dictionary Reports Dialog

  • Lists the various Dictionary Reports that can be generated.
  • Reports can be generated for the entire database or for particular Database Regions and their sub-regions.

Benefits of the Data Dictionary

The rewards for having a data dictionary are substantial. It enables the database classes to automatically handle multi-user coordination, buffer layouts, data validation, and performance optimizations. Once the data dictionary information has been entered, it can be used to automatically regenerate the full database creation instructions (SQL), including CREATE TABLE, CREATE INDEX, ALTER TABLE, GRANT, etc.) Data dictionary information created under one database system can easily be used to generate the same database layout on any of the other database systems BFC supports (Oracle, MS SQL Server, Access, DB2, Sybase, SQL Anywhere, MySQL, etc.)

If you are creating a new record type, you could use any tool that comes with your DBMS or use a third party tool, but it's easier to do the whole job with the Base One's active Data Dictionary and Command Processor

In any case, your new or existing record type should not only exist in the database but also be defined in the data dictionary, making the dictionary information accessible to your application.

The data dictionary itself consists of record types (tables) created in the database by system-generated command files, tailored for each supported back-end DBMS. Command files contain SQL statements for CREATE TABLE, CREATE UNIQUE INDEX, ALTER TABLE (for referential integrity), etc., using the specific SQL dialect required by that type of database. These command files illustrate the kind of work that is done for you by the active data dictionary, which also applies to other record types, and can be used as a model for writing your own SQL scripts.

BFC | Visual Studio | Database Technology | Distributed Computing

Home Products Consulting Case Studies Order Contents Contact About Us

Copyright 2012, Base One International Corporation