Introduction

Thoroughbred Query-IV is a major component of Thoroughbred Dictionary-IV and OPENworkshop.

Thoroughbred Dictionary-IV is a primary component of the Thoroughbred a fourth generation language (4GL) and OPENworkshop. It is a dictionary-driven system that provides a database manager, menu manager, screen developer, message dictionary, and help manager.

The other major components are Thoroughbred Script-IV and Thoroughbred Report-IV. These
components require separate manuals to describe their functions.

Overview of Thoroughbred Query-IV

Thoroughbred Query-IV makes use of a query language to help you get the information you need quickly. A query language contains words and symbols that you can use to request information from the database.

With Query-IV you do not have to use a query language, but instead let Query-IV create your query quickly and easily through selection windows.

If you prefer to use a query language, Query-IV uses a query language similar to SQL (Structured Query Language).

You can easily query a database and save and recall the query. This is a capability normally provided with embedded query languages, which allow you to embed queries in programs. Once you save a Thoroughbred Query, you can execute it from a Menu, Script (4GL program), or from Query-IV.

Database A database is an organized collection of information or data. For example, a telephone directory database would include Data Names like name, address, and telephone number.

SQL The American National Standards Institute (ANSI) has defined a standard version of SQL based on the IBM version, and the International Standards Organization (ISO) has in turn defined an international standard based on the ANSI definition.

If you know SQL, Thoroughbred Query-IV will be familiar to you. If not, you will find Thoroughbred Query-IV is easy to learn.

Tables and the Relational Database

A table is the result of a query. It contains information in vertical columns and horizontal rows, displayed in a two-dimensional, rectangular shape. A table can contain any number of columns and rows.

Each item of information in a column or row is called a field. A field is the intersection of a row and column.

The database that handles all information in terms of tables is known as a relational database. A relational database allows you to use tables without regard to how the information is physically stored in the computer. This makes it easier to get the information you need.

Query languages use a relational database. Tables, columns, and rows are relational database terms. If you are familiar with the terms describing a physical database, such as files, records, and Data Names, it may help to associate these with the terms describing a relational database.

Tables are files, rows are records, and columns are Data Names.

Although these terms are similar, they are not synonymous. One reason is that a relational database is a logical or conceptual structure that can be independent of the underlying physical database.

A table can represent information from part of a file or from more than one file.

A row can represent information from part of a record or from more than one record.

A column can represent information from part of a Data Name or from more than one Data Name.

Database

A database is an organized collection of information.

The telephone directory is a database: the names, addresses, and telephone numbers are the data, and the names are organized alphabetically.

An ordinary dictionary is also a database. The words and definitions are the data, and the words are organized alphabetically.

A checkbook register, in which you record check payments, is another database: the date, check number, payee, payment amount, and account balance are the data, and the payments are organized by date and check number.

The database does not have to be stored in a computer; it can be stored in an ordinary filing cabinet filled with file folders.

Thoroughbred Dictionary-IV stores the information in computer data files. A database consists of one or more related files.

In the preceding examples, each database contained a single file. But a database can be as small or as large as you need it. The telephone database could contain additional files, depending on how much information you needed to control. This could be directories for all the areas in the state, all the states in the country, or all the countries in the world.

Files, Records, Fields
Dictionary-IV references files, records, and fields. In SQL terms they are equal to the following:

Note

Dictionary-IV	SQL
Files	Tables
Records	Rows
Fields	Columns
:::

One thing that makes a database so useful is that the information is organized. If the telephone directory were not organized alphabetically, it would be a useless collection of information.

Organizing information requires that you use a structure, so the information can be stored away and located later as is needed.

The database is the largest structure and can contain a number of related data files. We will discuss a small database with only one file.

The information in a database is organized into data files. A data file in the database would be the telephone file.

The information in data files is organized into records. A record in the telephone file example would be the information for one person: last name, first name, street, city, and telephone number. Records are shown as horizontal rows across the page.

The information in records is organized into Data Names (fields). The Data Names in the telephone file are the last name, first name, street, city, and telephone number. The Data Names are shown as columns.

Data Names are usually, but not always, the smallest pieces of data in the organization.

Below is a listing of	some common synonyms.
Data File	Table, Relation
Record	Row, Tuple
Data Name	Field, Column, Attribute

The information contained in each Data Name is referred to as a value. In the above example under the Data Name LAST-NAME, we have the following values: Agassiz, Donato, Franklin, and Sandlar.

A record has a standard format so every record in the file has the same Data Names. When you define a record, you define a Format, or the sequence of Data Names in the record.

Data Names have certain characteristics, including a name and attributes such as size, the type of data it can hold, and whether it is used as a key to organize the file. The following topics provide a basic understanding of Data Names.

Data Name and Size When you create a Data Name, you give it a name and define the attributes. In the telephone file example, you might name one Data Name LAST-NAME and give it a size of 20. Values entered in this field cannot exceed 20 characters.

The following example is a 20-character field.

Robinson

R	o	b	i	n	s	o	n

Rosencrantz-Guildenstern

R	o	s	e	n	c	r	a	n	t	z	-	G	u	i	l	d	e	n	s

One reason for having a fixed size for Data Names is so your system can find information very quickly. Since Data Names have a fixed length, every record has a fixed length. No matter which record you access, the Data Names can be found in the same position.

Types of Data
Information comes in many forms. A name is information and so is a word, a date, a number, or an
amount of money. Each type of information is handled differently. A number can be added, subtracted,
and multiplied with another number, but not so with a person’s name.

Key Fields Keys are Data Names that help you to organize and locate information in a file. In the telephone file, the LAST-NAME is a key field, because the file is in order by last name. If you want to locate Sam Jones in the telephone file, you look up Jones.

Keys help to identify the information for you. But, for most files, the system needs a special key so that it can identify information: the primary key.

Primary Keys The primary key is a unique identifier for the record. If everyone in the telephone directory had a different last name, then you could use LAST-NAME as the primary key. But since this is not likely to be the case we must use some other method to uniquely identify each person.

If the people with the same last name all had different first names, then you could use the LAST-NAME plus the FIRST-NAME. In this case, the primary key would consist of two key fields: LAST-NAME and FIRST-NAME.

The primary key must contain a unique value that is the name of one and only one record. A social security number is a good example of a unique identifier.

Sometimes it is easier to invent a primary key. For example, you might create a Data Named code that contains values from 0001 to 9999.

The primary key is often informally called the key of the file; however, the key may contain one or more primary key fields. In this example the company name is the primary key.

The primary key is used to organize the file and to locate information. You may also use secondary keys to help you locate information that is not in the primary key.

Secondary Keys Secondary keys allow you to access a file in an order other than primary key order. Unlike primary keys, secondary keys in Dictionary-IV do not have to be a unique identifier.

Secondary keys are also known as cross-indexes, or sorts.

In the following example the BALANCE is used as the secondary key field. The same data is listed in ascending order by balance:

For More Information

… about creating applications, refer to the Thoroughbred Dictionary-IV Developer Guide

… about software conventions and databases, refer to the Dictionary-IV User Guide.

… about using each Dictionary-IV product, refer to the individual reference manual (Query-IV, Report-IV, Script-IV, and Source-IV Reference manuals).

… about Thoroughbred Basic Utilities refer to the Thoroughbred Basic Utilities Reference Manual.

… about Thoroughbred Basic, refer to the Thoroughbred Basic Reference Manual.

From the Query-IV Reference, version 8.9.1. Open this page in the PDF