Chapter E 1 : Advanced Revelation Files and Records
Whether you are new to database management systems or just new to Advanced Revelation, there are terms and concepts in this chapter that you will want to be familiar with. Because many of the basics of Advanced Revelation database management are summarized here, you will probably find yourself returning to this chapter time and again to solidify your understanding of these concepts.
About Files & Records ………..-..—200– E1.1
Terminology: Files, Records,and Fields …… a. El.i
Characteristics of Advanced Revelation Files …….. E1.2
Characteristics of Advanced Revelation Records ……. E1.6
Characteristics of Advanced Revelation Fields …….. E1.7
About Files & Records E11.
About Files & Records Advanced Revelation and other programs of its type were designed to help users give order to collections of data, or databases. As a group, these programs are known as database management systems (DBMS) or database managers. The database that you are organizing can be as simple as the contents of your address book or as complex as the parts list for an airplane.
This chapter is a general overview of the basic concepts of database management, Advanced Revelation style. You will find information here about how Advanced Revelation stores information, and about how an Advanced Revelation system is organized.
If you are new to database management, you will want to learn some of the basic principles and terminology used in Advanced Revelation. Even if you have experience with another database manager, you will find it useful to review the Advanced Revelation approach to data management.
The best way to use this chapter is to read through it casually the first time through. Familiarize yourself with some of the basic concepts, and get an idea of what capabilities are available to you in Advanced Revelation.
Later, when you need information about a specific topic such as files or data typing, return to this chapter and read about that topic in detail. You will find that other sections of the documentation will refer you back to this chapter whenever you need background information about a topic.
t= Ifyou are using an Environmental Bond, some features of Advanced Revelation files may work slightly differently than described here. For information on Environmental Bonding in general and on the characteristics of specific bonds, see the Environmental Bonding section.
Terminology: Files, Records, and Fields The information that you store and manage for your Advanced Revelation applications is stored in files. For example, your application might contain files of your customers, of parts used in assembling components that you manufacture, or of books available in a library. Your application can contain any number of files. A complex application such as a financial, personnel, or manufacturing system might contain hundreds of files.
Individual entries in a file are called records. In a file containing customer information, an individual customer will be a single record. In a file containing information on the books in a library, each book will have its own record in the file.
Filing Systems
E1.2 Advanced Revelation Files and Records
Records are in turn divided into fields. A field contains a single component of the record. For example, a customer record might be divided into fields for the customer’s name, address, phone number, and so forth. A conceptual picture of how a file is arranged looks like this:
Phone Date added Dues paid Record 1 (206) 643-9898 05/25/86 $1,000 Record 2 (206) 443-4747 01/08/88 $2,500 Record 3 (206) 628-0888 09/17/87 $1,750
If you have worked with other programs, you may know some of Advanced Revelation’s ter- minology by other names:
Advanced Revelation Name Synonyms Files Tables Records Rows Fields Columns, attributes
Characteristics of Advanced Revelation Files An Advanced Revelation application can contain any number of files. Files that store data are generally referred to as data files. However, the distinction between data files and other types of files is one of convenience. There is no fundamental difference between a file that stores data, a file that contains programs, and a file that contains the characteristics of a data entry window. Each record in a CUSTOMER data file contains information on a single customer, each record in a programs file contains a single program, and each record in the TEMPLATES file defines a single window. In Advanced Revelation, the purpose of a file is defined by how it is used, not by the file type itself.
File Types Advanced Revelation lets you use different file types. Two file types are available in Advanced Revelation: Linear Hash files (default) and ROS files. Linear Hash is the standard filing system used by Advanced Revelation and is recommended for almost all applications. If your Advanced Revelation applications are tied to another environment (for example, dBASE or ASCII) through Environmental Bonding, you can also create and use files in the file type for that environment. For information, see the section Environmental Bonding. In addition, you can get direct access to operating system (DOS or OS/2) files.
Advanced Revelation 2.1
Characteristics of Advanced Revelation Files E1.3
Linear Hash Files Linear Hash files are self-sizing, meaning they expand and contract automatically as data is added and deleted. In addition, they are self-optimizing, so access to data is always as efficient as possible. Linear Hash files represent the best balance between speedy access to data and minimal cost in disk space.
Linear Hash files were specifically designed to be used on networks. If you are using Advanced Revelation, you must use Linear Hash files to share data on a network. The additional advantages mentioned above — self-sizing and self-optimizing structure — are particularly beneficial on a network, because disk access can be a major source of slowdown. Linear Hash files also offer an option to enable you to match certain characteristics (packet or cluster size) of the network you use.
When used in a single-user environment Linear Hash files have an added benefit: the Linear Hash system stores some informa- tion in memory to speed up access to records. This helps reduce the number of disk reads that might otherwise be required to access data.
ts For more information on Linear Hash files, see the chapter “The Linear Hash Filing System” later in this section.
ROS Files The second filing system offered by Advanced Revelation is the ROS system. ROS is a file type used primarily for small files (less than 100K bytes in size) that are not shared in a network.
ROS files can be very fast because the data stays resident in memory (is “buffered” or “cached”) rather than requiring disk access for data retrieval. Advanced Revelation will store as much of the ROS file in memory as it can. As a result, small ROS files are especially efficient, because the entire contents of the file will fit into memory.
ROS files can be used on a network, but they have special requirements not applicable to Linear Hash files. First, ROS files do not support record or file locking, which means that they cannot be shared without risking data corruption. They can still be useful, however, for small files that are only used for lookup or for program files from which data is read but to which it is never written.
Filing Systems
E1.4 Advanced Revelation Files and Records
Second, ROS files will read up to 64K bytes at a time, but networks typically only transfer somewhere between 1K and 4K at a time, so ROS files might not be advantageous on a network.
Files Outside of In addition to these file types, Advanced Revelation allows you to Advanced Revelation access data in the formats of other products, such as dBASE III or
a database server. This capability — Environmental Bonding — means that there is really no constriction on how you choose to store data. For more information on Environmental Bonding, see the Environmental Bonding section.
The Data Dictionary Each Advanced Revelation file has associated with it a data dictionary, or dictionary. Each field that you define has an entry in the dictionary. The dictionary for a file is itself a file and is the central source of information about the file’s field attributes, indexing, and other information.
Volumes You can create and maintain Advanced Revelation files on virtually any storage device: your local hard drive, remote drives, floppy disks — almost anywhere. If you can assign a drive name to a device, you can store and access data there. To create and access files on your system, you associate them with a “volume”, The volume specification consists of two pieces of information about a file: the file’s location and its file type.
For more information on volumes, see the chapter “Advanced Revelation Volumes” later in this section.
Accounts An account is a collection of files that contain related information, such as payroll or inventory records. Accounts are used to group information together so it can be easily located and protected. You can use accounts to protect sensitive information and to segregate different applications, which is particularly important in a networking environment. Each account has its own set of security restrictions, which protects access to the account. For more information on accounts, see “Users and Accounts: Planning Access and Security” in Developing an Application.
Advanced Revelation 2.1
Characteristics of Advanced Revelation Files E1.5
File Synonyms (Qfiles) In Advanced Revelation, you can establish a synonym (Qfile) for an existing file and use the synonym file as you would the original. Generally, when you are working in one account, files in other accounts are not available to you. Qfiles enable you gain access to files in those other accounts.
For information on creating file synonyms, see “Managing Files and Records” in this section.
Editing Operating System Files In addition to the other file types accessible through Advanced Revelation, you can use Advanced Revelation to access data in operating system files. You can, for example, read and edit your DOS AUTOEXEC.BAT file. When you do so, DOS is treated as a single file, and DOS files are treated as records. Operating system files are limited to 64K except when using R/BASIC.
For more information on editing operating system files, see “Using the Full-Screen Editor” in Getting Started and “Programming with R/BASIC” in R/ BASIC.
File and Field Names The conventions for naming Advanced Revelation files and fields are few:
e Names may be any length up to 50 characters.
e Names may contain upper-case alphabetic and numeric characters but must begin with an alphabetic character.
* Names may contain “.” (period) and “_” (underscore) as delimiters (for example, CUSTOMER.HISTORY or CUSTOMER_HISTORY).
If you want your file and field names to be SQL compatible, it is a good idea to limit field length to 18 characters and to use only “_” (underscore) as a delimiter.
Advanced Revelation When you create a new file, that file has two names: the name vs. Operating System you give it in Advanced Revelation and its operating system
Names name. The rules for naming files in your operating system are considerably more restrictive than the rules for naming Advanced Revelation files. DOS will not let you name a file CUSTOMER.HISTORY, for example.
Filing Systems
E1.6 Advanced Revelation Files and Records
To find the operating system file name of an Advanced Revelation file, you can check the volume directory. This is itself a file containing information on the relationship between Advanced Revelation file names (for example, CUSTOMER.HISTORY) and operating system file names (for example, REV10000.LK). For information on listing volume directories, see “Advanced Revelation Volumes” later in this section.
Characteristics of Advanced Revelation Records Records are the building blocks of files. There is no limit on the number of records that you can keep in a file, assuming your computer has sufficient storage space. You can store up to 64K bytes (65,535 characters) in a record, which is about 25 pages of typed, single-spaced text.
Record Keys Each record in a file has a field that is used to uniquely identify that record within the file. The record key, also known as the primary key, is the means by which any record can be distinguished from any other record. Every record must have a key, and no two records in the file can have the same record key. For example, you might designate the CUSTOMER.NUMBER field to be the key field for your customer records. Each customer will have a unique entry in the CUSTOMER.NUMBER field, so that you can tell one customer from another. When assigning record keys, keep the following in mind:
e Record keys may be any length up to 50 characters.
e Record keys may contain any character between ASCII 33 and ASCII 249, inclusive. However, we recommend that you limit record keys to the char- acters A-Z and 0-9.
¢ Record keys cannot contain spaces.
Indexing Unless you have a tiny file, you will need a means of retrieving records other than simply knowing every key for every record in the file. The alternative to memoriz- ing record keys is to index fields whose values you do know. In the CUSTOMER file, the record keys might be six-digit numbers that you have not committed to memory. However, you do know the names of your customers, so you index the name field. You can then retrieve records for those customers by entering their names instead of their record keys.
Advanced Revelation 2.1
Characteristics of Advanced Revelation Fields E1.7
There are three benefits to indexing. One is the ability to retrieve data even if you do not know the record key. The other two are speedier processing time when sorting and when selecting records for reports. Be wary of over-indexing, though, because indexes take up storage space and processing time.
For more information on indexing, see “Creating and Maintaining Indexes” in Developing an Application and “Using Indexes” in Accessing Data.
Characteristics of Advanced Revelation Fields If records are the building blocks of files, then fields are the building blocks of records. This topic discusses the essential attributes of fields, including some that are unique to Advanced Revelation.
Variable-length Fields: The Size and Number of Fields in a Record In Advanced Revelation, there is but one restriction that controls both the maximum number of fields in a record and the maximum size of an individual field: a record can be no longer than 64K bytes (65,535 characters). Each empty field in a record takes up one byte, so you can have one field containing 65,535 characters, or 65,535 fields containing no data, or anything in between.
Each field in a record is variable in length — there is no fixed length established for a field. Although you will specify a display length and justification when defining the field, there is no limit on how many characters you can enter into a field. The only limit on field length is that the entire record cannot exceed 64K.
Advanced Revelation’s variable-length fields have two major advantages over the fixed-length fields of most database managers. First, you need not specify a fixed number of characters. In many systems, you must guess in advance how long the longest piece of data will be for each field.
Second, you can change the structure of a variable-length record very easily. Unlike most database managers, Advanced Revelation allows you to add or delete fields or change their characteristics at any time, even after data has been entered in the file. For example, you might decide to add a “comments” field to a customer record. You can do this even if you have thousands of records in the customer file already.
Moreover, such alterations do not require any special processing before the file can be used again. Many database management programs require that you restructure a file after adding, deleting, or modifying fields and will not let you use the file until you have done so.
Filing Systerns
E1.8 Advanced Revelation Files and Records
Multivalued Fields You can store multiple values or repeating data in a single field. For instance, if there is more than one phone number for a customer or if a part consists ofm ultiple subassemblies, you can store all entries in a single field. Again, the only limit is that the entire record cannot be longer than 64K.
Conceptually, a multivalued field looks like this:
Name Address City State
12 Roy St St. Oberlin NJ
4
Multi-valued field Two or more multivalued fields with related data can be tied together, or “associated”. Imagine a file that contains data on client organizations. In each record of that file, there is one multivalued field containing the names of contacts and another containing their direct-line phone numbers. In Advanced Revelation, you can make these two multivalued fields associated, so that the first contact is tied to her phone number, the second to his phone number, and so on.
Group F is
For the sake of convenience, you can define a field as a group field and specify two or more other fields as elements in the group. This group field can then be used when you run a report, saving you the trouble of naming, one by one, each field contained in the group field.
For example, in your STAFF.PAYROLL file you can define a single field, PAYROLL.SUMMARY, that includes every field that you need for a payroll report (EMPLOYEE.NAME, SALARY, FEDERAL.TAX, STATE.TAX, FICA, etc.). When you run the report, you can simply specify the group field PAYROLL.SUMMARY and produce a complete report without naming the fields individually.
Advanced Revelation 2.1
Characteristics of Advanced Revelation Fields E1.9
Synonym Fields You can create a field that is a synonym of another field. There are then two defini- tions for the same data. Synonym fields have two advantages: you can substitute a short field name for a long one, and you can report data in different formats (trun- cated from 40 to 20 characters, for example) without altering the stored data. A field can have any number of synonyms.
Symbolic Fields Symbolic fields, also called calculated fields, are not associated directly with data in ce the record. Instead, they calculate a value based on a “formula”. This formula can © be used to calculate totals (an INVOICE.TOTAL field, for example), look up values (to extract data from a related file), or perform other processes. Using the payroll example again, you could define the fields for payroll taxes as symbolic fields that calculate taxes based on salary. These calculations could include comparing salary in the STAFF.PAYROLL file with tax tables in other files.
The formula for a symbolic field can be up to 34K, and a file can contain any number of symbolic fields.
t= Ifyou are using Advanced Revelation for OS/2, the formula for a symbolic field can be up to 64K.
The definition of each field consists of up to fifty attributes. These include display length (as distinct from actual length, which is unlimited), justification, single or multivalued, and validation patterns.
Fieldname Each field that you define in a file has a unique name (see “Field Names” above). This name need only be unique within the file — you can use the same name in other files.
Display length Display length refers only to the number of characters that are output when running reports. The only limit on the length of data in a field is the maximum record size, 64K.
Justification Justification, indicating whether data is to be left or right justified or centered, refers to the orientation of data within the display field and imposes no limits on the size of the field.
Filing Systems
E1.10 Advanced Revelation Files and Records
Output format You may choose to store certain types of data in a compact format. This is most common with dates and times, money, phone numbers, and the like. The output format is used to specify how this data will look when it is displayed. For example, you can enter a date as numbers (100968) and convert it to any of a variety of date formats for output: October 9, 1968, 9 Oct 1968, 10/09/68, 10-09-68, 09-10-68, etc.
Validation patterns To ensure that data is entered properly, you can define validation patterns. As data is entered, it can be checked against a valida- tion pattern and rejected if it does not match. For example, you could define a validation pattern that checks data entered into a phone number field. If you do not enter ten numeric characters, you will see an error message and will have to re-enter the phone number.
Data Types When you define a field, you will indicate the field’s “data type”. The data type — such as DATE, TIME, or DOLLARS — tells Advanced Revelation about how you want to store and display the information in a field. Advanced Revelation has predefined display length, justification, output format, and validation patterns (described above) for twelve commonly-used data types. The default values are advisory rather than mandatory, so you can change them as desired.
Table E1-1 Advanced Revelation Data Types
Data type Usage Examples
BOOLEAN Boolean values 1/0, Y/N CHAR(n) Fixed-length
to n characters Helprin, 17 DATE Date 1 April 1991 DATETIME Date and time 4/1/91 08:00:00 DECIMAL(n,m) Fixed-point of
n digits with m decimal places 0, 14.55, .25
(Continued)
Advanced Revelation 2.1
Characteristics of Advanced Revelation Fields
Table E1-1 (Continued) Advanced Revelation Data Types
Data type Usage Examples
DOLLARS (n,m) Monetary; fixed-point of n digits with m decimal places 22.95
FLOAT Floating-point 0, 2.18693 INTEGER Integers 0, -4, 6424 TEXT Variable-length
(default: 4096 characters) Four score and …
TIME Time 11:30:00 VARBINARY character strings of
any value, including hex strings (default: 65531 characters) (no example)
VARCHAR Variable-length (default: 65531 characters) Advanced Revelation, 1991
In addition to these basic data types, an Environmental Bond may offer you others that are used in foreign environments, For more information, see the section En- vironmental Bonding.
Extra Information for Ifyou specify some of these data types, you are required to Data Type provide additional information. For example, if you choose the
data type CHAR, you will be asked for the length. On the other hand, a data type such as DATE does not require any additional information, although you can alter the output format if you choose to.
Filing Systems