SQL/MP Installation and Management Guide

HP NonStop SQL/MP Installation and Management Guide—523353-004
3-1
3
Understanding and Planning
Database Tables
An understanding of the types of organizations of tables and their corresponding file
structures is essential for effective use and functioning of the database.
This section presents conceptual information about tables and how to plan table
organization. Subsequent sections describe how to create tables and associated
database objects.
Understanding SQL File Structures
A relational database consists of two structural levels, the logical and the physical:
•
The logical level includes the tables and views you access directly through SQL
statements. When you request an operation on the database or a display of its
contents, you work with the database at the logical level.
•
The physical level underlies the logical level and is composed of physical files on
disks.
SQL tables and indexes are implemented on the physical level as Guardian files. If a
table or index is partitioned, it is implemented as multiple separate files. Table and
index files are managed by the SQL file system and are accessed implicitly through the
DP2 disk process.
The data you insert into tables and views is stored in these underlying files. Tables and
indexes are associated with their corresponding physical files through entries in the
data dictionary for your database. Views are associated with physical files through their
underlying tables.
When you create a table, you establish the characteristics of the underlying file by
specifying them as parameters in the CREATE TABLE statement. That is, you create
the file implicitly through this statement rather than directly through an explicit file-
creation statement.
The type of table created determines the type of the corresponding file:
•
A key-sequenced table has a key-sequenced file structure.
•
An entry-sequenced table has an entry-sequenced file structure.
•
A relative file has a relative file structure.
Data-transfers to and from the files are done in terms of logical records and key fields
within those records.
Each file has a unique primary key associated with it, which contains a unique value
used to order and identify records in the file.