Creating a Database
A Zen database consists of two basic parts:
• Data files to store the data physically
• A data dictionary to describe the data
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:
Named Databases
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 database integrity constraints
You use Zen Control Center to name existing, unbound databases and to create new, bound databases. See Zen User’s Guide for 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.
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.
For more information, see
Identifier Restrictions in
Advanced Operations Guide.
Unique Names
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").
Maximum Name Lengths
Zen restricts the maximum length of database component names in a dictionary. See
Identifier Restrictions in
Advanced Operations Guide and
Relational Engine Limits in
SQL Engine Reference.
Case Sensitivity
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.
SELECT *
FROM Course#
Zen returns the column names as follows:
"Name", "Description", "Credit_Hours", "Dept_Name"
Creating a Data Dictionary
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. The following table describes the system tables Zen creates. See also
System Tables in
SQL Engine Reference.
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.
To create a dictionary for an unnamed database
1. Run DDF Builder.
Creating Tables
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 create a table using ZenCC. See
To start Table Editor for a new table in
Zen User’s Guide.
Aliases
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.
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:
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.