The following topics explain named databases, bound databases, and how to create a database and use a data dictionary to manage data files as tables, columns, and indexes:
A named database has a logical name that allows users to identify it without knowing its actual location. When you name a database, you associate that name with a particular dictionary directory path and one or more data file paths. When you log in to Zen using a database name, Zen uses the name to find the database dictionary and data files. Your database must be named before you can do the following:
•Define triggers
•Define primary and foreign keys
•Bind a database
•Suspend a database’s integrity constraints
You use Zen Control Center to name existing, unbound databases and to create new, bound databases. See Zen User’s Guidefor more information.
Bound Databases
Binding a database ensures that the MicroKernel enforces database security, referential integrity (RI), and triggers, regardless of the method used to access the data. The MicroKernel enforces these integrity controls as follows:
•When you define security on a bound database, Btrieve users cannot access it.
•When you define security on an unbound database, Btrieve users can access it.
•When no security is defined on a bound database, Btrieve users can access the data files as shown in the following table.
Bound File’s Constraint
Level of Access Using Btrieve
RI constraints defined
Users can access and do anything within RI constraints
INSERT triggers defined
Read-only, update, and delete access
UPDATE triggers defined
Read-only, insert, and delete access
DELETE triggers defined
Read-only, insert, and update access
If more than one constraint exists on the bound file, the access level follows the most restrictive constraint. For example, if a file has both INSERT and UPDATE triggers defined, then you have read-only and delete access.
Note Even if you do not bind your database, Zen automatically stamps a data file as bound if it has a trigger, has a foreign key, or has a primary key that is referenced by a foreign key. Thus, a data file may be part of an unbound database, but be bound. In such cases, the MicroKernel enforces integrity constraints on the file as if it were part of a bound database.
The dictionary and data files in a bound database cannot be referenced by other named databases. Also, bound data files cannot be referenced by other tables in the database.
When you create a bound database or bind an existing database, Zen stamps every dictionary and data file with the name of the bound database. Also, Zen stamps every data file with the name of the table associated with that data file. In addition, when you add new tables or dictionary files to the database, Zen automatically binds them.
Creating Database Components
Use Zen Control Center to create databases. See Zen User’s Guide.
To create tables in the database, use Zen Control Center or the CREATE TABLE syntax defined in SQL Engine Reference. When you issue a CREATE TABLE statement, you must include commands that define columns. In addition, you can include commands that define referential integrity (RI) constraints.
Naming Conventions
When you create a database, Zen allows you to assign a descriptive name to each database component. Users and applications refer to the components of the database using these names. This topic outlines the conventions to which you must adhere when naming database components.
The following database components must have unique names within a dictionary:
•Tables
•Views
•Indexes
•Keys
•User names
•Group names
•Stored procedures
•Triggers
•Column names within a single table
Names for parameters and variables must be unique within a SQL statement. Because Zen keywords are reserved words, you cannot use them for naming database components or in parameter names and variables. See SQL Reserved Words in SQL Engine Reference for a list of reserved keywords.
When a column name is repeated in different tables, you can qualify it in each table by preceding it with the relevant table name or alias name. For example, you can refer to the ID column in the Student table as Student.ID. This is a fully qualified column name, and the table name (Student) is the column qualifier.
Valid Characters
Following are the valid characters for the names of database components at the SQL level, and for variables and parameter names:
•a through z
•A through Z
•0 through 9
•_ (underscore)
•^ (caret)
•~ (tilde)
•$ (dollar sign)
Note The name of a database component must begin with a letter. If you specify the name of a database component or a parameter name that does not follow these conventions, specify the name in double quotes (such as “name”).
Zen is case-sensitive when you are defining database component names. If you create a table named TaBLe1, Zen stores the table name in the dictionary as TaBLe1. With the exception of user names, user group names, and passwords, Zen is case-insensitive after you define the component name. After defining the table TaBLe1, you can refer to it as table1.
User names, user group names, and passwords are case-sensitive in Zen. For example, when you log in as the master user, you must specify the user name as Master.
When retrieving data, Zen displays names for tables, views, aliases, and columns based on the case in which they were created.
Zen uses the dictionary to store information about the database. The dictionary consists of several system tables that describe the tables and views of your database.
The system tables contain several types of database information, including table and index definitions, column characteristics, and integrity and security information. Table 37 describes the system tables Zen creates. See also System Tables in SQL Engine Reference.
Table 37 Zen System Tables
Operation
Resulting Table
Create a data dictionary
X$File, X$Field, X$Index
Specify column attributes
X$Attrib
Create stored SQL procedures
X$Proc
Define database security
X$User, X$Rights
Define referential constraints
X$Relate
Define views
X$View
Define triggers
X$Trigger, X$Depend
Because the system tables are part of the database, you can query them to determine their contents. If you have the appropriate rights, you can also create system tables or change their contents.
Note Zen does not display all of the data in the system tables. For example, information about stored views and procedures, other than their names, is available only to Zen. In addition, some data (such as user passwords) is displayed as encrypted characters.
For a complete reference to the contents of each system table, see SQL Engine Reference.
Once you have created a dictionary, you can add tables, columns, and indexes to your database.
►To create a named database
Note You must have named databases in order to use some features, such as referential integrity and triggers.
1 Create a directory in which to store the new dictionary tables.
2 Use Zen Control Center to add a Named Database. See To create a new database in Zen User’s Guide.
When you create a table, you must name it. Each table name must be unique within a database. For more information about rules for naming tables, see Naming Conventions.
When you are deciding which tables to create in your database, consider that different users can look at data in different combinations using views. A view looks like a table and can be treated as a table for most purposes (such as retrieving, updating, and deleting data). However, a view is not necessarily associated with a single table; it can combine information from multiple tables. For more information, see Retrieving Data.
You can assign aliases (also called alias names) to table names in the following elements of statements:
•FROM clause of a SELECT or DELETE statement.
•INTO clause of an INSERT statement.
•List of tables in an UPDATE statement.
Note Aliases apply only to the statement in which you use them. Zen does not store them in the data dictionary.
An alias can be any combination of up to 20 characters. Always separate the table name from the alias with a blank. Separate the alias and the column name with a period (.). Once you specify an alias for a particular table, you can use it elsewhere in the statement to qualify column names from that table.
The following example specifies the alias name s for the table Student and e for the table Enrolls.
SELECT s.ID, e.Grade FROM Student s, Enrolls e WHERE s.ID = e.Student_ID#
You can use an alias to do the following:
•Replace long table names.
When you are working interactively, using aliases can save typing time, especially when you need to qualify column names. For example, the following statement assigns s as the alias for the Student table, e for the Enrolls table, and c1 for the Class table. This example uses aliases to distinguish the source of each column in the selection list and in the WHERE conditions.
SELECT s.ID, e.Grade, c1.ID FROM Student s, Enrolls e, Class c1 WHERE (s.ID = e.Student_ID) AND e.Class_ID = c1.ID)#
•Make a statement more readable. Even in statements with only a single table name, an alias can make the statement easier to read.
•Use the table in the outer query in a correlated subquery:
SELECT s.ID, e.Grade, c1.ID FROM Student s, Enrolls e, Class c1 WHERE (s.ID = e.Student_ID) AND (e.Class_ID = c1.ID) AND e.Grade >= (SELECT MAX (e2.Grade) FROM Enrolls e2 WHERE e2.Class_ID = e.Class_ID)#
Creating Columns
You create columns when you create a table using a CREATE TABLE statement, or you can add columns to an existing table using an ALTER TABLE statement. In either case, you must specify the following characteristics:
•Column name – identifies the column. Each column name must be unique within a table and the column name cannot exceed 20 characters. Zen is case-sensitive when defining database column names, but case-insensitive after you define the column name. For example, if you create a column named ColuMN1, the name is stored in the dictionary as ColuMN1; subsequently, you can refer to it as column1. For more information about rules for naming columns, see Naming Conventions.
•Data type – identifies the kind of data to expect, such as a string of characters or a number, and how much disk storage space to allocate.
For more information about data types, see the Btrieve API Guide.
Creating Indexes
Indexes optimize operations that either search for or order by specific values. Define indexes for any columns on which you frequently perform either of these operations. Indexes provide a fast retrieval method for a specific row or group of rows in query optimization. Zen also uses indexes with referential integrity (RI). Indexes improve performance on joins and help to optimize queries. For more information about RI, see Zen User’s Guide.
In Zen databases, the MicroKernel Engine creates and maintains indexes as part of the physical file for which they are defined. The MicroKernel Engine performs all maintenance for insert, update, or delete operations. These activities are transparent to any Zen application.
To create an index, use a CREATE INDEX statement. This method creates a named index. You can delete named indexes after you create them. For more information about dropping indexes, see Inserting and Deleting Data.
While indexes allow you to sort rows and retrieve individual rows quickly, they increase the disk storage requirements for a database and decrease performance somewhat on insert, update, and delete operations. You should consider these trade-offs when defining indexes.
The next example uses a CREATE INDEX statement to add an index to a table that already exists:
CREATE INDEX DeptHours ON Course (Dept_Name, Credit_Hours)#
Note Be aware that if you use the CREATE INDEX statement on files that contain a lot of data, execution could take some time to complete, and other users may not be able to access data in that file in the meantime.
For detailed information about the CREATE TABLE and CREATE INDEX statements, see SQL Engine Reference.
Index Segments
You can create an index on any single column or group of columns in the same table. An index that includes more than one column is called a segmented index, in which each column is called an index segment.
For example, the Person table in the Demodata sample database has the following three indexes:
•A segmented index consisting of the Last Name column and the First Name column
•The Perm_State + Perm_City column
•The ID column
The number of index segments is affected by the page size of the data file. See Btrieve API Guide for more information on how to use the PAGESIZE keyword. The maximum number of indexes you can create for a table depends on the page size of its data file and the number of segments in each index. As the following table shows, data files with page sizes smaller than 4096 bytes cannot contain as many index segments as a data file with a page size of 4096. The number of index segments that you may use depends on the file’s page size.
Page Size (bytes)
Maximum Key Segments by File Version
8.x and earlier
9.0
9.5
13.0
512
8
8
Rounded up2
Rounded up2
1024
23
23
97
Rounded up2
1536
24
24
Rounded up2
Rounded up2
2048
54
54
97
Rounded up2
2560
54
54
Rounded up2
Rounded up2
3072
54
54
Rounded up2
Rounded up2
3584
54
54
Rounded up2
Rounded up2
4096
119
119
2043
1833
8192
n/a1
119
4203
3783
16384
n/a1
n/a1
4203
3783
1”n/a” stands for “not applicable”
2”Rounded up” means that the page size is rounded up to the next size supported by the file version. For example, 512 is rounded up to 1024, 2560 is rounded up to 4096, and so forth.
3While a 9.5 format or later file can have more than 119 segments, the number of indexes is limited to 119.
Using the page size and fixed record length, you can calculate the efficiency with which data is being stored (such as the number of wasted bytes per page). By having fewer records per page, you can improve concurrency where page-level locking is concerned.
By default, Zen creates all tables with a page size of 4096 bytes. However, you can specify a different page size using the PAGESIZE keyword in a CREATE TABLE statement, or you can create a table using the MicroKernel Database Engine and specify a different page size for that table.
When calculating the total number of index segments defined for a table, a nonsegmented index counts as one index segment. For example, if your table has three indexes defined, one of which has two segments, the total number of index segments is four.
You can use Zen Control Center to display the number of defined index segments and the page size of a data file. For information about this utility, see Zen User’s Guide.
Index Attributes
When you create an index, you can assign to it a set of qualities, or attributes. Index attributes determine the modifiability of the index and how Zen sorts the indexes you define for a table. You can include parameters specifying index attributes anytime you create or alter an index definition.
Indexes can have the following attributes:
Case-sensitivity
Determines how Zen evaluates uppercase and lowercase letters during sorting. By default, Zen creates case-sensitive indexes. To create a case-insensitive index, specify the CASE keyword when you create the index.
Sort order
Determines how Zen sorts index column values. By default, Zen sorts index column values in ascending order (from smallest to largest). To create an index that sorts in descending order, specify the DESC keyword when you create the index.
Uniqueness
Determines whether Zen allows multiple rows to have the same index column value. By default, Zen creates non-unique indexes. To create an index that requires unique values, specify the UNIQUE keyword when you create the index.
Modifiability
Determines whether you can modify index column values after Zen stores the corresponding row. By default, Zen does not allow changes to index column values once Zen stores the row. To create a modifiable index, specify the MOD keyword when you create the index.
Segmentation
Indicates whether the index is segmented (whether it consists of a group of columns combined into a single index). By default, Zen creates indexes that are not segmented. To create a segmented index using the CREATE TABLE statement, specify the SEG keyword for each index segment you create, except the last segment in the index. (The SEG keyword indicates that the next column specified is a segment of the index you are creating.)
Because you can create only one index at a time with the CREATE INDEX command, you do not need to use the SEG keyword to specify a segmented index. If you specify more than one column, Zen creates a segmented index using the columns in the order in which you specify them.
Partial
Indicates whether Zen uses a portion of a CHAR or VARCHAR column, designated as t he only or last index column, when the total size of the column(s) plus overhead is equal to or greater than 255 bytes.
By default, Zen does not create partial indexes. To create a partial index using the CREATE INDEX statement, specify the PARTIAL keyword.
Uniqueness and modifiability apply only to entire indexes. You cannot apply uniqueness or modifiability to a single index segment without applying it to the entire index. For example, if you create a segmented index and specify the MOD keyword for one of the index segments, you must specify the MOD keyword for every segment.
In contrast, you can apply case-sensitivity, sort order, and segmentation to individual index segments without affecting the entire index. For example, you can create a case-insensitive index segment in an otherwise case-sensitive index.
Partial Indexes apply only to the last column defined in the index, as long as that column meets the following criteria:
•The column is the only column defined in the index, or it is the last column defined in the index.
•The last index column is of data type CHAR or VARCHAR.
•The total size of the index, including column overhead, is equal to or greater than 255 bytes.
For more information on indexes and their attributes, see CREATE INDEX in SQL Engine Reference.