Key Concepts

Show this topic in Library frames

This section explains some basic concepts of databases and some of the key concepts that distinguish Pervasive.SQL from other database products.

Basic Database Structures and Terms

Most database management systems in use today share a common set of basic structures. This section briefly explains those structures. The descriptions that follow refer to the diagram below:

Value

The most basic element of a database is a value. A value is one piece of data, one characteristic, for a specific entity. For example, in the diagram, the name "John Doe" or the phone number "555-1212" is a value.

Column or Field

Another element is a column, or a field. A column represents a characteristic with no specific value. Columns generally have names that describe the given characteristic. For example, in the telephone book, Name and Phone are columns. They do not have specific values unless you look up a particular person. Field is sometimes used to refer to the generic characteristic of a specific row. For example, someone might point to a specific box in the table above and ask, "What is the value of that field?"

Row or Record

Another element is called a row, or a record. A row is a collection of all the values for one particular instance. For example, one entry in the phone book, complete with name, address, and phone number, is one record or row.

Cell

A cell is a column within a specific record. You can think of it as the intersection of a row and a column. Each cell has a specific value. For example, you might tell a co-worker, "The value of the cell located at row 2, column 3 is `12345'."

Table

A collection of rows and columns makes up a table. A table is a set of data that shares exactly the same structure. Tables generally have names that describe the contents of the table. For example, the table above is called "Phone Book." With Pervasive.SQL, each table is stored as a separate data file on the hard disk.

Index

An index is an ordered list of all the values in a particular column. A table can have zero or more indexes on it. The database engine uses indexes to find specific records in the database without having to step through every record one at a time. Creating indexes on columns which will frequently be used in database searches is likely to improve the performance of your database.

Database

A database is a collection of one or more tables. The data in the tables does not need to be related among the various tables, but usually there are many relations. For example, a database might consist of the "Food Preferences" table below, and the "Phone Book" table above. With Pervasive.SQL, a database consists of one or more data files and Data Dictionary Files (DDFs) on your hard disk. The DDFs are special data files that contain all the definitions for tables, columns, and other attributes that define the structure of your database.

Schema

The term schema refers to the complete set of definitions that describe the entire structure of a database. A typical schema includes definitions for tables, columns, indexes, and many other attributes. The DDFs for a database contain the database's schema.

Remote

The term remote refers to an object, such as a file server or a database, that is not located in the computer you are using now. When you connect to a database over the network, you are connecting to a remote database. Remote is the opposite of local. Remote can refer to either the client or the server, depending on whether you are currently seated at the server computer or a client computer. Remote always refers to an object that is not located on the system you are using.

Local

The term local refers to the computer you are using right now, or something stored on this computer. A local database is a database in which the data files are stored on the hard disk of the computer you are currently using. Local is the opposite of remote. Local can refer to either the client or the server, depending on whether you are currently seated at the server computer or a client computer.

Relational

The term relational refers to the storage of data in the form of related tables. The related tables allow relationships to be created between sub-sets of data.

For example, you can see that both our example tables contain the Name column, and some of the names are the same. Because we can cross-reference the names in the Phone table with the names in the Food table, we have the power to ask and answer such questions as, "What is the phone number of someone who likes steak?" We may also answer such questions as, "Which consumer profile purchased the most product B after buying product A?"

You can see how powerful relational data access is. The SRDE component of Pervasive.SQL provides full relational access to your data.

Join

A join refers to an association between columns of related tables. Typically, a join operation is part of a SELECT query, which is used to obtain information from related tables.

Unique Benefits of Pervasive.SQL

One unique feature of Pervasive.SQL is that it allows applications to access data through either the industry-standard relational method outlined above, or through an ultra-high-speed transactional or hierarchical method known as the Btrieve interface. In fact, Pervasive.SQL allows applications to use both access methods at the same time to access the same data.

Transactional Interface

The transactional interface is a high-performance, low-overhead access method, capable of handling updates, inserts, and deletes much faster than other database products.

Applications that use the transactional interface bypass the relational interface and communicate directly with the MKDE to maximize performance.

In the interest of performance, the transactional interface offers only basic security, including file passwords and encryption. It does not allow SRDE data access to bypass transactional security.

Relational Interface

The relational interface uses industry-standard ODBC to provide a rich environment for data definition, security, reporting, stored procedures, and universal application access without requiring any application programming. Databases that are ODBC-enabled can be accessed by any ODBC-standard software program.

As an end user of an application based on Pervasive.SQL, you may not be able to choose which access method your application uses, but your application vendor has most certainly taken this into account. No other DBMS available today offers this combination of flexible relational access and high-speed transaction throughput.

Additional Access Methods

In addition to the Transactional and Relational interfaces, Pervasive.SQL provides methods to access data through OLE DB, Java (JDBC), ActiveX, ADO, and Pervasive Direct Access Components (PDAC) for Delphi and C++ Builder.

Terminology Revisited

When using the Btrieve interface, the terms table and database are generally not used, and data files are referred to directly as such. In addition, Btrieve users normally use the terms records and fields rather than rows and columns.