Right now, you have an empty database. The database exists, and is ready to use, but there is no place to store your data. You can think of this as an empty filing cabinet. Without file folders, you have no place to put your information. In the database, you need to create tables before you can put your information in the database.

However, before you create your tables, there are some things you need to think about first: where to locate your tables, and what type of data will be in the tables.

5.1 Tablespaces

A DBMaker database can be partitioned into several logical areas of storage known as tablespaces. This allows the database to be divided into manageable areas either for logical reasons (tables contain related data) or physical reasons (data must be placed on different disks). This allows you to group your data, or split your data between different physical disks to speed up access time.

Tablespaces can be either fixed in size or automatically extensible. Tablespaces that are fixed in size are called regular tablespaces, and tablespaces that can have their size automatically extended are called autoextend tablespaces. DBMaker also has a special tablespace called the system tablespace.

Regular Tablespaces

A regular tablespace is a tablespace that has a fixed size and contains one or more data files. If a file in a regular tablespace is too small to hold all the data you wish to store in it, you can enlarge it manually or add another file. A regular tablespace can have up to 32767 data files, provided the total number of data pages in all files is 2 GB or less.

Autoextend Tablespaces

As you add data to a file in an autoextend tablespace, the tablespace will grow as needed to contain the data. Each autoextend tablespace can contain one data file and one blob file, and can grow up to 2 GB. You can change any autoextend tablespace to a regular tablespace if you don't wish the tablespace to expand, but a regular tablespace cannot be changed to an autoextend tablespace. If you create a new tablespace in your database and don't specify the type, it is created as an autoextend database by default. The number of pages in a data file, specified in dmconfig.ini, is the initial size if that file belongs to an autoextend tablespace.

The System Tablespace

All DBMaker databases contain an autoextend tablespace known as the system tablespace. Whenever you create a database, DBMaker generates a system tablespace to record the system catalog tables. The system catalog tables store are managed by DBMaker and contain detailed information and statistics about everything stored in the database. You cannot store your own tables in the system tablespace.

The Default User Tablespace

All DBMaker databases also contain an autoextend tablespace known as the default user tablespace. Whenever you create a database, DBMaker generates an empty tablespace to store user tables. Any tables you create are stored there by default. To store a tables in another tablespace, you must first create a tablespace and specify it when creating the table.

5.2 Data Types

When you define a field in a table, you must choose a data type for the field. You first need to understand how to use each field so you can make the right decision about its data type. If you choose the wrong data type, it can waste space in the database, or make the application program take extra steps to convert the data to a usable form. DBMaker supports 16 different data types: CHAR, INTEGER, SMALLINT, FLOAT, DOUBLE, DECIMAL, DATE, TIME, TIMESTAMP, VARCHAR, BINARY, LONG VARCHAR, LONG BINARY, SERIAL, OID, and FILE.

In the paragraphs below the characteristics of each data type will be listed to allow you to see how to use each data type.

CHAR(size)

The CHAR data type is a fixed length data type that can contain any character you can enter from the keyboard. The minimum length of CHAR columns is 1 character, and the maximum length is 3992 characters. You must enter a value for the size parameter when creating a CHAR column.

Any data entered in a CHAR column that is shorter than the column length is padded with spaces. When entering CHAR data, you must enclose it in single quotes(` '). Note that double-byte characters occupy two bytes. If you are using double-byte characters you must account for this when you specify the length of the column.

e.g. `This is a CHAR string.'
　　`This is another CHAR string.'

VARCHAR(size)

The VARCHAR data type is a variable length data type that can contain any character you can enter from the keyboard. The minimum length of VARCHAR columns is 1 character, and the maximum length is 3992 characters. You must enter a value for the size parameter when creating a VARCHAR column.

Unlike the CHAR data type which uses spaces for padding, only the characters entered are stored in the database. When entering data in a VARCHAR column, you must enclose it in single quotes(` '). Note that double-byte characters occupy two bytes. If you are using double-byte characters you must account for this when you specify the length of the column.

e.g. `This is a VARCHAR string.'
　　`This is another VARCHAR string.'

BINARY(size)

The BINARY data type is a fixed length data type that can contain any binary value. The minimum length of BINARY columns is 1 byte, and the maximum length is 3992 bytes. You must enter a value for the size parameter when creating a BINARY column. Any data entered in a BINARY column that is shorter than the column length is padded with a zero-value byte.

You can enter character data by enclosing the data in single quotes (` '), the same as when entering CHAR data. However, in BINARY columns the data is stored as hexadecimal values representing the ASCII code of the characters, not as the actual characters you entered.

You can also enter hexadecimal values directly by enclosing the data in single quotes and appending the `x' character (` 'x) to indicate the string contains a hexadecimal value. Since it requires two digits to represent all possible values for each byte in hexadecimal, you must use an even number of digits when entering hexadecimal values.

e.g. `AaBbCcDdEe'
　　`41614262436344644565'x

SMALLINT

The SMALLINT data type is an exact signed numeric data type with a precision of 5 and a scale of 0. The SMALLINT data type uses 2 bytes of storage and has a maximum value of 32,767 and a minimum value of -32,768.
If you attempt to move a value larger than the allowed maximum from a data type such as INTEGER or DOUBLE, DBMaker displays a conversion error and does not move the data.

e. g. 4769
　　 8376

INTEGER

The INTEGER data type is an exact signed numeric data type with a precision of 10 and a scale of 0. The INTEGER data type uses 4 bytes of storage and has a maximum value of 2,147,483,647 and a minimum value of -2,147,483,648.
If you attempt to move a value larger than the allowed maximum from a data type such as DOUBLE, DBMaker displays a conversion error and does not move the data. The INTEGER data type may be abbreviated as INT.

e. g. 393848
　　 -298376

FLOAT

The FLOAT data type is an approximate signed numeric data type with a mantissa of precision seven. Precision refers to the total number of digits in the mantissa, both left and right of the decimal point. The FLOAT data type uses 4 bytes of storage and has a valid input range of 3.402823466E38 to -3.402823466E38. The smallest valid input values are 1.175494351E-38 and -1.175494351E-38. If you attempt to move a value larger than the allowed maximum from a data type such as DOUBLE, DBMaker displays a conversion error and does not move the data.

e. g. 3.583837E34
　　 -1.827362E-27

DOUBLE

The DOUBLE data type is an approximate signed numeric data type with a mantissa of precision fifteen. Precision refers to the total number of digits in the mantissa, both left and right of the decimal point. The DOUBLE data type uses 8 bytes of storage and has a valid input range of 1.0E308 to -1.0E308. . The smallest valid input values are 1.0E-308 and -1.0E-308.

e. g. 2.89837457884451E285
　　 -1.93873634847372E-174

DECIMAL(precision, scale)

The DECIMAL data type is an exact signed numeric value with a variable precision and scale. Precision refers to the total number of digits in the mantissa, both left and right of the decimal point. Scale refers to the number of digits right of the decimal point. The default values are precision = 17 and scale = 6.

The amount of storage used by a decimal column is based on the actual value entered, not on the default precision and scale values or the precision and scale values entered when defining the column. To calculate the amount of storage use the formula:

For example, the number 9283.83 would be stored in 6 bytes. The calculation used to determine this is shown below:

If you attempt to move a value larger than the allowed maximum from a data type such as FLOAT or DOUBLE, DBMaker displays a conversion error and does not move the data. The DECIMAL data type may be abbreviated as DEC.

e.g. 3452.8373645
　　736.383732652

DATE

The date data type is a fixed length data type that contains a calendar date (year, month, and day). The DATE data type uses 6 bytes of storage and has a precision of 11. Valid years are from 0001 to 9999.

The DATE data type has multiple input and output formats. If the values in the database do not appear correctly, or you are not able to enter dates you think are valid, you should check the date input and output formats to make sure they are correct.

e. g. `0001/01/01'
　　 `1999/12/31'

TIME

The TIME data type is a fixed length data type that contains a time-of-day. The TIME data type uses 6 bytes of storage and has a precision of fifteen and a scale of three. All time values entered are assumed to be in twenty-four hour format by default unless the optional values `AM' and `PM' are specified.

The TIME data type has multiple input and output formats. If the values in the database do not appear correctly, or you are not able to enter times you think are valid, you should check the time input and output formats to make sure they are correct.

e. g. `22:04:05.666'
　　 `10:04:05.666 PM'

TIMESTAMP

The TIMESTAMP data type is a fixed length data type that contains a calendar data and a time-of-day. The TIMESTAMP data type uses 12 bytes of storage and has a precision of 27 and a scale of 3. Valid years are from 0001 to 9999. All time values entered are assumed to be in twenty-four hour format by default unless the optional values `AM' and `PM' are specified.

The TIMESTAMP data type uses the input and output formats for the TIME and DATE data types to display values and determine if input values are valid. If the values in the database do not appear correctly, or you are not able to enter times you think are valid, you should check the time input and output formats to make sure they are correct.

e.g. '1997/01/01 10:02:03.444 PM'
　　 '01.01.1997 22:02:03.444'

LONG VARCHAR

The LONG VARCHAR data type is a variable length data type that can contain any character you can enter from the keyboard. The maximum length of LONG VARCHAR columns 2 gigabytes, or more than 2,000,000,000 characters.

Unlike the CHAR data type which uses spaces for padding, only the characters entered are stored in the database. When entering data in a LONG VARCHAR column, you must enclose it in single quotes(` '). Note that double-byte characters occupy two bytes. If you are using double-byte characters you must account for this when you specify the length of the column.

e.g. `This is a VARCHAR string.'
　　`This is another VARCHAR string.'

LONG BINARY

The LONG VARBINARY data type is a variable length data type that can contain any binary value. The maximum length of LONG VARBINARY columns is 2 gigabytes, or about 2,000,000,000 bytes. Unlike BINARY data type which uses zero-value bytes for padding, only the bytes entered are stored in the database.

You can enter character data by enclosing the data in single quotes (` '), the same as when entering CHAR data. However, in LONG VARBINARY columns the data is stored as hexadecimal values representing the ASCII code of the characters, not as the actual characters you entered.

e.g. `AaBbCcDdEe'
　　`41614262436344644565'x

SERIAL(start)

The SERIAL data type is a special data type that provides a sequence of consecutive values. To do this, DBMaker allocates an integer number for each table in a database and uses this number to generate a unique sequence for the corresponding table. DBMaker manages and maintains these integer numbers internally. The value of each integer value is automatically increased by one each time it is used. You can specify the first value in a sequence by providing an integer value for the optional START parameter when defining a SERIAL column, or you can omit the START parameter to use the default value of 1. Each table in a database can have only one column of the SERIAL data type.

Since the internal value used to generate a SERIAL number is actually an integer value, the SERIAL data type shares all of the properties of the integer data type. Like the INTEGER data type, it is an exact signed numeric data type with a precision of 10 and a scale of 0 that occupies 4 bytes of storage. The SERIAL data type also has the same range of values as the INTEGER data type, with a maximum value of 2,147,483,647 and a minimum value of -2,147,483,648.

To insert the next number in a sequence into a SERIAL column, place a NULL or empty value in the SERIAL column when you insert a new row into a table. DBMaker will insert the internal serial number for that table into the SERIAL column of the new record, and automatically increase the internal value by one.

If you insert a new and supply an integer value for the SERIAL column instead of a NULL or empty value, DBMaker will use the supplied integer value instead of the next number in the sequence, and will not increment the internal value. If the supplied integer value is greater than the last serial number generated, DBMaker will reset the sequence of generated serial numbers to start with the supplied integer value.

e.g. 100, 101, 102, 103, 104, 105, 106, 107
　　100, 101, 50,102, 103, 110, 111, 112

FILE

The FILE data type is a special data type that stores the contents of any existing file as an external file that DBMaker can reference like any other data. It is similar to the LONG VARCHAR and LONG VARBINARY data types, except DBMaker stores the data externally as a file instead of internally as an object in the database. This allows third-party tools to access and manipulate the data in its native format, without having to re-import the data to register any changes in the database.

The FILE column stores a reference to a record in the system catalog tables. The system catalog contains information that the databases uses to find the file object. When you display a FILE column, you do not actually see what is stored in the FILE column itself, but one of three views of the information stored in the system catalog or the file itself: the filename, the file contents, or the file size. The FILE data type can store data in two ways, as a system file object or as a user file object.

A system file object copies an existing file to the file object directory of the database and gives it a unique name. The database will manage this file, and it is deleted when there are no references to it in the database.

A user file object creates a link to an existing file, leaving the file in the original location with the original name. Since this file was created by the user, it will not be deleted when there are no references to it in the database. DBMaker must have read permission on a file before you can insert it into the database as a user file object.

The FILE data type is a structured data type that occupies 24 bytes of storage. The maximum length of the path that you can use to enter a file object is 79 characters.

OID

The OID (object identifier) data type is a special data type that provides a unique ID for each object (record, BLOB) stored in a database. It is a structured data type that has a precision of 10 and a scale of 0, and occupies 8 bytes of storage. DBMaker automatically generates and inserts an OID with each record. Since the OID is internally managed and maintained by DBMaker, you cannot use the OID data type directly.

The value generated for an OID is related to the storage location of objects in the database. This means that two OIDs that are generated consecutively may not necessarily be sequential.

The OID values act as a hidden pseudo-column in tables, and will not appear in queries such as select * from customers. However, you can explicitly select the OID column by using `OID' as a column name in a query.
Although it is possible to use an OID in a query to select data from a table and then use the OIDs to update the table data, this is not common practice when using the SQL language. OIDs are usually used in the internal programming interface, and not directly in the interactive dmSQL environments.

5.3 Creating a Table

Every table is defined with a table name and a set of columns. There can be up to 252 columns in each table, and each column contains:

a column name and a data type
a length (the length might be predetermined by the data type, such as INTEGER) or precision and scale (for columns of the DECIMAL data type only) or a starting number (for columns of SERIAL data type only).

To create a table, you must provide the table name, column definitions and the name of the associated tablespace. If a table is not explicitly associated with a tablespace, the table placed in the system tablespace by default.

The first table you will create is the Employees table. To create this table, enter the following command:

dmSQL> create table Employees (Number SERIAL, FirstName CHAR(15), 
                              LastName CHAR(20), Manager INT, 
                                Phone CHAR(15), BirthDate DATE, 
                              HireDate DATE);

This command creates the table Employees , which has seven columns: Number, FirstName, LastName, Manager, Phone, BirthDate, and HireDate. The number column uses a SERIAL number to provide an employee number that will increment automatically every time a new employee is added. FirstName, LastName, and Phone use the CHAR data type. CHAR is used for the Phone column as well as the names, because the phone number may have brackets, dashes or periods in it. The last two columns, BirthDate and HireDate, use the DATE data type.

Now that you have created the Employees table, you should create the other tables in the Tutorial database by entering the commands below:

dmSQL> create table Regions (Number INT NOT NULL, Name CHAR(40));

dmSQL> create table IDTypes (Number INT NOT NULL, Type CHAR(50), 
                             Description CHAR(255));

dmSQL> create table Customers (Number SERIAL, FirstName CHAR(15), 
                               LastName CHAR(20), Phone CHAR(15),    
                               IDType INT, IDRegion INT, 
                               IDNumber CHAR(20), Credit SMALLINT);

dmSQL> create table Suppliers (Number SERIAL, Name CHAR (50), 
                               Phone CHAR(15), Contact CHAR(35));

dmSQL> create table MovieTypes (Number INT NOT NULL, Type CHAR(30), 
                                Description CHAR(255));

dmSQL> create table Movies (Number SERIAL, Name CHAR(75), 
                            Year CHAR(4), Country CHAR(30), 
                            Type1 INT, Type2 INT, 
                            Type3 INT, Type4 INT, 
                            Rating CHAR(10), Length SMALLINT,    
                            Color CHAR(3), BW CHAR(3), 
                            Tape CHAR(3), Disc CHAR(3), 
                            Quantity SMALLINT, Supplier INT,    
                            Date DATE, Price FLOAT, 
                            Rate SMALLINT);

dmSQL> create table Receipts (Number SERIAL, Customer INT, 
                              Employee INT);

dmSQL> create table LineItems (Receipt INT NOT NULL, LineItem INT NOT NULL, 
                               Movie INT, Quantity SMALLINT, 
                               Amount SMALLINT) ;

In some of the tables shown above, the keyword NOT NULL is used. This indicates that the column must contain data when you insert a new record. There are several other keywords that can also be used when creating a table. These keywords are shown in the following syntax diagram.

Details on these additional commands are given below.

Default Values for Columns

A column can be assigned a default value so that when a new row is inserted and no value is provided for a column or the column is omitted, a default value is automatically supplied. You can specify a different default value for each column in a table. If a default value is not explicitly defined for a column, the default value will be implicitly set to NULL. Legal default values can be constants or built-in functions.

For example, you can use the following SQL command to specify the default value of the column Contact in the table Suppliers is a constant-`Unknown'.

dmSQL> create table Suppliers (Number SERIAL, 
                                 Name CHAR (50), 
                                 Phone CHAR(15), 
                               Contact CHAR(35) default `Unknown');

Lock Mode

The lock mode of a table identifies the type of lock that DBMaker automatically places on objects when accessing the database. DBMaker supports three levels of lock mode: table, page, and row. The page lock mode is used by default if the lock mode is not explicitly specified when a table is created.

If the lock mode is set to a higher level (such as table), the level of concurrency on database accesses will be lower, but the required lock resources (shared memory) will also be smaller. If the lock mode is set to a lower level (such as row), the level of concurrency on database accesses will be higher, but the required lock resources (shared memory) will be larger. In other words, if you insert or modify rows in a table with the lock mode set to table, no one else can access this table. The reason for this is that an exclusive lock is taken on the entire table.

The following SQL command shows you how to specify the lock mode on a table.

dmSQL> create table Suppliers (Number SERIAL, 
                               Name CHAR (50), 
                               Phone CHAR(15), 
                               Contact CHAR(35) default `Unknown')    
                               lock mode row;

Fillfactor

The fillfactor feature is used to optimize the utilization of space on data pages by reserving space on a data page to allow for the expansion of individual records that already exist on a page. It does this by specifying the percentage of a page that can be filled before it stops allowing new records to be inserted. By allowing space for a record to grow on the same data page, the records can be accessed more efficiently by avoiding the need to retrieve information for one record from multiple pages. The following SQL example specify the fillfactor of table Suppliers to be 80%.

dmSQL> create table Suppliers (Number SERIAL, 
                               Name CHAR (50), 
                               Phone CHAR(15), 
                               Contact CHAR(35) default `Unknown')    
                               lock mode row fillfactor 80;

In this case, no new row can be inserted into the data page when the used space is larger than 80% of the page space. The value for fillfactor can be from 50 to 100.

Nocache

The nocache feature is useful when accessing large tables by table scan. Although DBMaker uses page buffers in shared memory to cache retrieved data and avoid frequent disk I/O when accessing a database, table scans on large tables can still cause frequent disk I/O activity. This happens during a table scan on a table with a larger number of data pages than the number of page buffers, which causes all page buffers to be exhausted.

Once the nocache option is specified when creating a table, DBMaker only uses one page buffer to cache the data retrieved from a table during a table scan. This prevents the page buffers from being exhausted by only one large table scan.

The following SQL command gives an example of how to specify the nocache option:

dmSQL> create table Suppliers (Number SERIAL, 
                               Name CHAR (50), 
                               Phone CHAR(15), 
                               Contact CHAR(35) default `Unknown')    
                               lock mode row fillfactor 80 nocache;

Temporary Tables

You can create a temporary table for storing data on a temporary basis. Temporary tables only exist during a single session, and DBMaker will automatically drop the temporary table when you disconnect from the database. Temporary tables support fast data operations and can be used only by the creator. If you execute an operation improperly on a temporary table, the data in this table may be incorrect.

The following SQL command gives an example of creating a temporary table named test:

dmSQL> CREATE TEMPORARY TABLE test (Number SERIAL, 
                                    Name CHAR(50), 
                                    Phone DATE);