Category : Databases and related files
Archive   : PCDBMS.ZIP
Filename : MANUAL.2

CHAPTER 5

COMMAND REFERENCE



OVERVIEW

The commands summarized in Table 5-1 are implemented in
PC-DBMS. Subsequent sections document them.

COMMAND FUNCTION

DEFINE invokes define utility to update schema
DEKEY removes an index
DISPLAY invokes display utility to update
a table
EXIT terminates execution of PC-DBMS
KEY builds an index
OPEN makes available an existing or new
data base
OUTPUT redirects output to file or printer
PACK removes deleted records from files
REMOVE removes table from the data base
SELECT retrieves records from one or two tables
SHOW displays portion of schema
TOLERANCE sets tolerance used for numeric
comparisons

Table 5-1. Summary of PC-DBMS commands.



NOTATIONAL CONVENTIONS

Syntax forms for the commands use the following notational
conventions.

- Lower case words indicate that you should substitute a
word or value of your choice.

- Upper case words are keywords and they should be used
where shown. However, keywords can be truncated to three
or more characters and can be entered in upper or lower

5-1


COMMAND REFERENCE
NOTATIONAL CONVENTIONS


case.

- Brackets ([]) indicate optional elements.

- Ellipses (...) indicate the preceding item(s) may be
repeated one or more times.




DEFINE

The define command is issued after the open command is given
for a new or existing data base. It invokes the define
utility to enter definitions of tables and their fields. The
syntax is:

DEFINE

The define utility is conversational. It presents two data
entry screens. The first screen accepts the description of a
table. The second screen accepts descriptions of that
table's fields.

The first or table screen prompts for the table's name, the
specification of the file containing it, the record
termination character and an optional verbal description.

When that screen is completed, the second screen type appears
for entry of field data -- field name, data type, field
width, indexing and an optional verbal description. When
completed, the screen reappears blank for definition of the
next field. If there is no "next" field, press F10 and the
first data entry screen will reappear to define the next
table. Then, if there is no "next" table, press F10 to exit
the define utility.

File specifications may include drive and path names. If a
drive or path name is given, the file must always be on that
drive or in that directory. If neither is given, PC-DBMS
will look for the file on the logged drive in the current
directory.

The table screen prompts for a number to describe the end of
record terminator. Enter 0 for no terminator, 1 for carriage
return and 2 for carriage return-line feed. The choice of 2
facilitates processing the file outside PC-DBMS.

Fields may have one of three data types: text, number or
filler. Filler fields may contain anything -- blanks,
numbers or text. The indexing and description information is
not solicited for filler fields. Filler fields can be

5-2


COMMAND REFERENCE
DEFINE


displayed with the select command but may not be used in
where or sort clauses. The display utility ignores them; on
input, they are blank filled. Filler fields are useful in
masking columns of extraneous material in imported files or
to insert blanks between fields during input.

If a field is more than 141 characters wide, it cannot be
indexed so the index prompt will not appear.

If the data base is to include a table in an imported file,
its definition must exactly describe the file. Filler fields
of any desired width can be used. Any record whose first
character is a backslash (\) will be considered deleted.



DEKEY

The dekey command updates the schema so that it shows the
named field as not keyed. It has no effect on the index file
but the old index will never be usable. The syntax is:

DEKEY qfn

Qfn is a qualified field name. For example, DEKEY OPUS.WORK
changes the schema so that field WORK in table OPUS is
regarded as not keyed. When a field is indexed, it is
necessary to maintain the indices when records are added,
changed or deleted. Dekeying avoids this overhead.



DISPLAY

The display command projects a data entry form on the screen.
The form consists of background text and field blocks
corresponding to non-filler fields in the data table. The
syntax is:

DISPLAY [\INPUT] table_name [WHERE ]
\QUERY
\EDIT

The display form operates on a single table given by
table_name. That table may have up to 60 fields. Some
tables may be rejected because all their non-filler fields
cannot be squeezed onto the screen.

A form will be created dynamically. Display fields
corresponding to data fields appear as rectangular blocks;
they are one or more characters wide and one or more lines
high.

5-3


COMMAND REFERENCE
DISPLAY


The display command has four forms. With the \QUERY
qualifier, a select command is composed for use as the next
command. If \INPUT is specified, new records are entered and
appended to the table. The default \EDIT qualifier permits
retrieval, editing and deletion of existing records. The
where clause form of the command also retrieves records for
editing and deletion, those satisfying the where clause.
Screen layouts are similar for all forms; only the bottom
information lines differ. The various forms are discussed
below.

An example of the \INPUT form is

DISPLAY\INPUT COMP

In this example, a form will be projected with a blank field
for each non-filler data field in the COMP table. Next to
each field, its name will be shown. At the bottom right of
the screen, INPUT will be displayed to identify the function.
The rest of the bottom line gives other useful information.

Fill in all fields (or leave some blank) and press F7 to
store the new record. The form reappears with blank fields
to enter another record. Press F10 to exit the display
utility. However, exiting does not store any record still on
screen.

The \QUERY form of the display command helps compose certain
select commands. The form shows QUERY at the bottom right
and has blank fields. Fill in up to ten of them and press
F7. This exits the display utility and restores the main
screen. A select command resulting from the QUERY form
entries will be in the command window. Edit, erase or
execute it. It will have AND connectives, no parentheses, no
negations, = comparisons and ALL in its display list.

For example, suppose you invoke the display utility with

DISPLAY\QUERY COMP

You enter 1770 in the BORN field and Beethoven, L. in the
NAME field. After pressing F7, the main screen reappears
with the following in the command window:

SELECT COMP.* WHERE NAME="Beethoven, L." AND BORN=1770

The command can be edited if it's unsuitable. For example,
only certain fields might be desired in the output.

The \EDIT form of the display command is used to find and
edit or delete records. Two phases repeat in a cycle.
First, a screen query is executed. Second, qualifying

5-4


COMMAND REFERENCE
DISPLAY


records are displayed one after the other on the form for
editing and deletion. When the editing and deletion are
complete, the blank query screen reappears to repeat the
process.

For example, to edit records in the COMP table, the command
is

DISPLAY\EDIT COMP

The query phase of the cycle is almost identical to the
DISPLAY\QUERY function. A form appears with QUERY at the
bottom right and blank fields. Fill in up to ten fields and
press F7 to execute the query. Alternatively, press F10 to
exit from the display utility.

If F7 was pressed, all the qualifying records are displayed
one at a time on the form. The bottom line will now show
editing prompts and EDIT at the right.

As each qualifying record is displayed, do one of three
things: leave it unchanged, edit it or delete it. To leave
it unchanged, press F7. To edit it, make the desired changes
on screen and press F6. To delete it, press F4. In any
case, the next qualifying record will be displayed. However,
to return to the first (query) phase, press F10. It is not
possible to exit the display utility directly from the edit
screen.

After all qualifying records have been displayed, the screen
will clear and the following message will appear:

NO (MORE) RECORDS QUALIFY

Press any key to continue

After pressing a key, the query screen reappears to begin the
query-edit cycle anew. Alternatively, press F10 to exit the
display utility.

DISPLAY\EDIT is the default. That is, if there is no where
clause and no qualifier (\), then the \EDIT qualifier is
assumed.

The last form of the display command is the only one that
uses the where clause. Any qualifier is ignored. This form
of the command retrieves for editing and deletion records
which satisfy the where clause.

The where clause is documented under the select command. The
default table name at the beginning of the clause is
table_name. A second table can be referenced in the clause.

5-5


COMMAND REFERENCE
DISPLAY


Qualifying records are projected on the same edit screen used
with the \EDIT form of the command. However, after the last
record has been processed, the display utility is exited.

For example, one way to retrieve Beethoven's record for
editing is with this command:

DISPLAY COMP WHERE NAME $ "Beethoven"

When text fields are entered or displayed on screen forms,
quote marks are treated as ordinary characters; they are not
string delimiters. Hence, use one quote for each quote mark
you wish to embed in the record.

Numbers are entered according to the rules for numbers in
commands. See COMMAND FORMATTING AND EXECUTION in chapter 4.

The PC-DBMS editor operates in each field block. However,
only replace mode is available. See THE PC-DBMS EDITOR in
chapter 4.

To move the cursor forwards from one field to the next, press
tab. Press F9 to move it backwards.

A help screen is available by pressing F1.

Whenever records are added, deleted or modified by the
display utility, the corresponding index file is
automatically updated. However, if more than 13 fields are
indexed, only the first 13 will be updated. The indices on
the other fields will be incorrect. They should be dekeyed
and then rebuilt with the key command. In practice, few
tables have more than 13 keys.



EXIT

The exit command terminates execution of PC-DBMS and returns
the user to the operating system. The command's syntax is
EXIT but it can be issued by pressing F10.



KEY

The key command builds an index for the specified field and
updates the schema to reflect that fact. The syntax is:

KEY qfn



5-6


COMMAND REFERENCE
KEY


Qfn is a qualified field name. For example, KEY OPUS.WORK
builds an index for the field WORK in the table OPUS. The
index will be updated when the table is altered by the
display command.

Indexing will be aborted and an error message displayed if an
attempt is made to key a field wider than 141 characters.



OPEN

Open is typically the first command the user gives. It makes
an existing data base available. If the data base does not
exist, its schema file will be created. The command syntax
is:

OPEN dbname

Dbname is the name of the existing or new data base. For
example, OPEN MUSIC. If the schema file is not in the
current directory, precede the data base name with the
appropriate path name (but no spaces).

There is no close command. An open data base will be closed
when the exit or open command is given.



OUTPUT

The output command is used to redirect the output of the show
and select commands to a file or to a device such as a
printer. The syntax is:

OUTPUT [file-spec]

File-spec is the name of the file or device. For example,
OUTPUT FILE.DAT sends output to a disk file and OUTPUT LPT1:
prints the output. If no file specification is given, the
screen default will be restored.

If file-spec is a disk file which already exists, it will be
deleted and a new file of the same name created.

The redirected output will include column headings. A file
created by this command will not become part of the data
base. That function is served by the "into" clause of the
select command.




5-7


COMMAND REFERENCE
PACK COMMAND


PACK COMMAND

The deletion of data records and the removal of tables (with
the remove command) leaves unused space in data and schema
files, respectively. The pack command recovers that space by
pushing undeleted data or schema records passed deleted ones.
It then truncates the file. The syntax is:

PACK [\SCHEMA] [table-name ...]

To pack the schema file, append the qualifier \schema to the
keyword pack. If the qualifier is not specified, at least
one table name must be given.

For example,

PACK\SCH COMP OPUS

packs the schema and two data files.

The pack command will also delete trailing nulls (ascii
zeros) which PC-DBMS uses to pad files to a 128 byte
boundary.

When a data file is packed the schema's record count (which
includes deleted records) is updated. The change is apparent
when the show command is executed.

It is necessary to remove indices on files to be packed
because indices store record numbers and these change during
packing. The schema will be updated to show a previously
keyed field as unkeyed and the associated index file will be
deleted. However, if the packing does not change record
numbers (no deleted records before the last undeleted
record), no change will be made to the index file or the
schema keying information.

CAUTION! The pack command operates on the disk directory and
the file allocation table. It expects to find conventional
disk drives. DON'T USE PACK IF YOUR DRIVE IS UNCONVENTIONAL.
Pack has not been tested on 3 1/2 " drives or on drives
greater than 32 megabytes. If you can't use pack, a text
editor can be used to delete records flagged as deleted.



REMOVE

The remove command marks as deleted the schema definition of
the named table(s). The syntax is:

REMOVE table_name [...]

5-8


COMMAND REFERENCE
REMOVE


For example, REMOVE CUSTOMERS BALANCES removes the two named
tables. The data files are unaffected by this command but
they can no longer be accessed.



SELECT

The select command retrieves data from the tables. It is
capable of gathering data from one or two tables, filtering
and sorting the retrieved records, displaying up to 12 of
their fields in any order, and writing the result to a new
table which becomes a part of the data base. The output
ordinarily is written to the bottom half of the screen or
whatever target is specified by the output command. However,
output will be written to the designated new table if the
into clause is used.

The abbreviated syntax is:

SELECT qfn's [WHERE ] [SORTED BY ]
[INTO new_table_name]

The first clause, qfn's, is the only necessary one. It is a
list of qualified field names to be displayed. The fields
can come from one or two tables. An example of a two table
retrieval is SELECT COMP.NAME COMP.BORN OPUS.WORK. If more
than twelve fields are specified, additional ones will be
ignored and the query will be executed. The display will be
truncated to 78 characters unless output has been redirected
with the output command in which case truncation occurs after
the 130th character.

To display all fields of a table, two special qfn's can be
used. The table name and period are as usual but the field
name is either ALL or ALLF. For example, SELECT COMP.ALL
displays all non-filler fields of the COMP table. ALLF is
used when the filler fields are also desired. A synonym for
ALL is the wildcard *.

Each time a table name is encountered in the select command,
that table becomes the default. Subsequent field names which
omit the table name and period reference the default table.
For example, SELECT COMP.NAME COUNTRY OPUS.* displays two
fields from COMP and all fields from OPUS.

Figure 5-1 is an example of a simple select command.






5-9


COMMAND REFERENCE
SELECT



SELECT COMP.*


NAME BORN DIED COUNTRY

Tchaikovsky, P. 1840 1893 Russia
Brahms, J. 1833 1897 Germany
Verdi, G. 1813 1901 Italy
Handel, G. 1685 1759 England
Mozart, W. 1756 1791 Austria
Bach, J. 1685 1750 Germany
Chopin, F. 1810 1849 France
Beethoven, L. 1770 1827 Germany
Haydn, F. 1732 1809 Austria
Mendelssohn, F. 1809 1847 Germany
Debussy, C. 1862 1918 France

Figure 5-1. A simple select command.

New numeric fields can be calculated from existing ones. The
calculated field must be named and defined. First the name
is specified as 1-16 characters. The name must not be
qualified and it must not be a field in the default table.
The name is followed by an equals sign, =. Last comes the
algebraic definition of the calculated field.

The algebraic definition can contain constants and field
names. All field names must be for numeric fields.

The definition follows the conventions of algebra and uses
the notation of Basic. The following operators are provided:

+ addition - subtraction or negation * multiplication /
division ^ exponentiation

Exponentiation has the highest precedence in the order of
evaluation, multiplication and division are next followed by
addition and subtraction. The order of evaluation can be
altered by parentheses nested to any desired degree.

Nine functions are also available. Each is followed by an
argument in parentheses. The argument can be a constant or
an expression. The functions are:









5-10


COMMAND REFERENCE
SELECT


ABS absolute value
LOG common logarithm
LN natural logarithm
EXP exponential
SIN sine in radians
COS cosine in radians
TAN tangent in radians
FIX integer part of argument
INT largest integer <= argument

For example, SELECT COMP.NAME BORN DIED LIFE = DIED - BORN
COUNTRY produces a display of five fields, the fourth one
being calculated.

The where clause is optional. A where clause contains one to
ten comparisons (conditions) connected by ANDs and ORs. A
record must satisfy the where clause to be displayed. For
example, attaching the where clause WHERE BORN>1799 to the
preceding query would eliminate from the display the five
composers born before 1800.

The eight comparisons are shown in Table 5-2.

COMPARISON REPRESENTATION

less than <
less than or equal to <=
equal to =
greater than or equal to >=
greater than >
unequal to <>
contains $
does not contain %

Table 5-2. The eight comparison operators.

On one side of the comparison there must be a field name,
possibly qualified. On the other side, there may be another
field name, possibly qualified, or a constant, numeric or
text as appropriate. The elements on the two sides of the
comparison must be of the same data type, both numeric or
both text. Filler fields may not be used in comparisons.
All eight operators can be used with text elements. Only the
first six can be used with numeric elements. Spaces before
and after the operator are optional.

For numeric comparisons, the default tolerance is zero. The
tolerance can be altered with the tolerance command.

Text constants used with the first six comparison operators
are right padded with blanks to match the length of the field
element on the other side of the comparison. If a text

5-11


COMMAND REFERENCE
SELECT


constant is longer than the field element, the query will be
rejected. Text comparisons use the ASCII collating sequence.
Hence, WHERE NAME>"B" excludes NAMEs beginning with "A".

The $ and % operators test whether the left hand element
contains the right hand element. For example, NAME $ "Bee"
is true for the Beethoven record but false for the other
composers. The % operator returns the opposite result.

The default table name in the where clause is inherited from
the qfn list but is changed each time a qualified field name
is encountered.

Fields from different tables may appear in the same
comparison. If qfn's from different tables are in the
display list, it is likely there will be at least one
cross-table comparison. For example, Figure 5-2 shows a join
of composers (COMP) with their works (OPUS).

SELECT COMP.* OPUS.WORK WHERE COMPOSER=COMP.NAME


NAME BORN DIED COUNTRY WORK

Brahms, J. 1833 1897 Germany Lullaby
Beethoven, L. 1770 1827 Germany Symphony No. 5

Figure 5-2. Using select for a join.

The 11 records in COMP are paired with the 2 in OPUS for 22
possible combinations. Of those 22, only the two which have
identical values of COMP.NAME and OPUS.COMPOSER are
displayed. (This conceptual execution of a join is not
necessarily how PC-DBMS implements it.)

PC-DBMS permits up to ten comparisons to be used, linked
together by ANDs and ORs. For example, to find composers who
lived entirely in the nineteenth century, use

SELECT COMP.* WHE BORN>1799 AND DIED<1900

The complement to this set of composers is the set of
composers not alive at any time during the nineteenth
century. One way to find them is with the complement
operator, NOT. For example,

SELECT COMP.* WHE NOT ( BORN>1799 AND DIED<1900 )

NOT makes anything that is true false and vice versa. It is
typically used on a combination of comparisons as above
because the individual comparisons have their own
complements. For example, NOT NAME>"Smith" is just NAME<=

5-12


COMMAND REFERENCE
SELECT


"Smith". Space between NOT and ( is optional.

Parentheses, nested to any desired depth, can be used to
alter the order of evaluation of a where clause. In the
preceding comparison, parentheses assure that the two
comparisons are evaluated and combined and then that result
is negated.

Like algebra, there is an order of evaluation in where
clauses. Comparisons are evaluated (to true and false)
first. Then NOTs are applied. AND comes next and OR is
last.

A table may be referenced for the first time in the command
in the where clause.

The sorted by clause is also optional. It contains a list of
one to five field names from tables referenced earlier in the
command. The first field name is the major sort key and the
last is the minor sort key. Filler fields may not be used.

The default sorting order is ascending (aardvarks before
zebras). To get descending order on a key, follow it with
"\d" or "\D". Use "\a" or "\A" to get ascending order, the
default.

For example, Figure 5-3 shows retrieval of composers in
reverse order of birth and breaks ties (Bach and Handel) by
alphabetizing their names.

SEL COMP.* SOR BY BORN\D NAME


NAME BORN DIED COUNTRY

Debussy, C. 1862 1918 France
Tchaikovsky, P. 1840 1893 Russia
Brahms, J. 1833 1897 Germany
Verdi, G. 1813 1901 Italy
Chopin, F. 1810 1849 France
Mendelssohn, F. 1809 1847 Germany
Beethoven, L. 1770 1827 Germany
Mozart, W. 1756 1791 Austria
Haydn, F. 1732 1809 Austria
Bach, J. 1685 1750 Germany
Handel, G. 1685 1759 England

Figure 5-3. A select command with sorting.

The select command will abort with an error message if the
output string is too wide. The output string contains the
fields to be displayed plus two spaces between adjacent

5-13


COMMAND REFERENCE
SELECT


fields. Spaces are suppressed if the into clause is present.
Add to the output string width the width of the widest sort
field. The sum may not exceed 141.

The optional into clause comes last. It makes the retrieved
table part of the data base by writing the output to a file
and making appropriate entries in the schema. Only an
information message will be displayed on screen.

Into is followed by the name for the new table. That name
follows the conventions for naming tables but may not exceed
eight characters. The name may not already be in use as a
table name. The file for the table will have the table's
name as its file name and DAT as its extension. It will be
in the current directory of the logged drive. No drive will
be recorded for it in the schema. The new table will have no
indices.

No more than two tables can be referenced prior to the into
clause.

An example of a complex select command follows. It
illustrates simultaneous use of all the clauses.

SELECT COMP.COUNTRY COMP.BORN OPUS.*
WHERE COMPOSER=COMP.NAME
AND NOT (DIED<1900 AND BORN>1799)
SORT BY NAME BORN\D
INTO NU_TABLE

During output, scrolling can be suspended by pressing the
control-NumLock key and resumed by pressing any other key.
The F10 key aborts output.

Skeletal select commands can be composed easily with the
DISPLAY\QUERY command.



SHOW

The show command displays a portion of the schema. The
syntax is:

SHOW [\FIELDS] [\DESCRIBE] [table_name ...]

The optional qualifiers \FIELDS and \DESCRIBE determine what
kind of information is retrieved from the schema. It is
possible to use zero, one or two qualifiers. Note that
SHOW\FIELDS\DESCRIBE has the same effect as
SHOW\DESCRIBE\FIELDS.


5-14


COMMAND REFERENCE
SHOW


The FIELDS qualifier displays information on fields. Without
this qualifier, information on tables is retrieved. The
DESCRIBE qualifier retrieves verbal descriptions. Without
this qualifier, tabular information is retrieved.

If one or more tables is named, only information on those
tables is retrieved. Otherwise, data on all tables is shown.

Four examples follow. They show all possible combinations of
the qualifiers (one, the other, both, neither). For brevity,
schema information is retrieved for only one table.

The unqualified show command produces tabular information on
one or more tables. See Figure 5-4.

SHOW COMP 'info on table COMP

TABLE NAME FILE RECORDS FIELDS EOR MODIFIED

COMP COMPOSER.DAT 11 6 2 01-11-85

Figure 5-4. The show command without qualifiers.

The output exhibits the number of records and fields. EOR
gives the end of record terminator; 0 means no terminator, 1
means carriage return and 2 means carriage return-line feed.

With just the DESCRIBE qualifier, show displays a verbal
description of one or more tables. See Figure 5-5.

SHO\DESCRIBE COMP 'description of table COMP

TABLE NAME DESCRIPTION

COMP information about some composers

Figure 5-5. The show\describe command.

With just the FIELDS qualifier, show displays tabular
information about the fields of one or more tables. See
Figure 5-6.












5-15


COMMAND REFERENCE
SHOW



SHO\FIELDS COMP 'info on fields in table COMP

FIELD NAME TYPE KEY FROM TO


TABLE COMP

NAME T N 1 19
BORN N N 20 23
filler1 F N 24 24
DIED N N 25 28
filler2 F N 29 29
COUNTRY T N 30 44

Figure 5-6. The show\fields command.

The TYPE column indicates the field's data type: T for text,
N for numeric and F for filler. KEY indicates if the field
is indexed: Y for yes and N for no. The FROM and TO columns
show each field's starting and ending positions in the data
file.

Show with both qualifiers (either order) exhibits verbal
descriptions of the fields of one or more tables. See Figure
5-7.

SHO\FIE\DES COMP 'descriptions of fields in COMP

FIELD NAME DESCRIPTION


TABLE COMP

NAME the composer's last name and first initial
BORN the year the composer was born
filler1
DIED the year of the composer's death
filler2
COUNTRY the composer's country of residence

Figure 5-7. The show\fie\des command.

During output, scrolling can be suspended by pressing the
control-NumLock key and resumed by pressing any other key.
The F10 key aborts output.







5-16


COMMAND REFERENCE
TOLERANCE


TOLERANCE

The tolerance command sets the comparison tolerance used in
processing where clauses. It affects only numerical
comparisons. The syntax is

TOLERANCE number

The tolerance is set to the absolute value of number. The
default value is zero, i.e., the value before this command is
first given.

Slight conversion errors occur in converting between decimal
(used by people and stored in files) and binary (used
internally by PC-DBMS) because the machine does not have
infinite precision. Non-zero tolerance circumvents these
errors.

The range created by tolerance can be exploited to simplify
queries. For example, with zero tolerance, certain records
are qualified by

WHERE YEAR>=1895 AND YEAR<=1905

If tolerance is set to 5, the same records will, ignoring
conversion errors, be qualified by

WHERE YEAR=1900

Here are the formal tolerance rules. If F is a field with
value a and r is another field or a user specified number and
t is the tolerance, then the following are true conditions.

F = r if and only if r-t <= a <= r+t
F <> r if and only if a < r-t or a > r+t
F > r if and only if a > r-t
F >= r if and only if a >= r-t
F < r if and only if a < r+t
F <= r if and only if a <= r+t

If NOT precedes a where clause condition, the operator of the
condition will be complemented and the NOT removed when the
condition is processed. For example, NOT YEAR > 1900 is
processed as YEAR <= 1900. If tolerance is 2, then records
with YEAR greater than 1898 should be excluded. In fact,
records with YEAR less than or equal to 1902 will be
included.






5-17















APPENDIX A

KEYWORDS



PC-DBMS keywords can be truncated to three or more characters
and entered in upper, lower or mixed case. The keywords and
their truncations should not be used for names of data bases,
tables or fields. A list of keywords is in Table A-1.



ALL KEY
ALLF OPEN
AND OR
BY OUTPUT
DEFINE PACK
DEKEY QUERY
DESCRIBE REMOVE
DISPLAY SCHEMA
EDIT SELECT
EXIT SHOW
FIELDS SORTED
INPUT TOLERANCE
INTO WHERE

Table A-1. The keywords of PC-DBMS.

















A-1














APPENDIX B

THE RELATIONAL MODEL



PC-DBMS is a data base management system which implements the
relational model of data management. This appendix describes
the relational model and how to design data bases around it.

Relational data bases contain data tables. These are
familiar objects; they have rows called records and columns
called fields.

Data bases typically have more than one table. For a given
information content, a data base might have one, two, three
or more tables. How many tables should contain that
information? Which fields should be put into which tables?
The purpose of this appendix is to help you answer those
questions, i.e., to help you design your data base. If
you're still looking for more information when you finish,
you might consult C. J. Date, "An Introduction to Database
Systems," (Reading, Massachusetts: Addison-Wesley Publishing
Co., 1976).

We will progress through several data bases, each better
organized than the last.

First, consider the one table data base in Figure B-1.

NAME_1 SSNO_1 NAME_2 SSNO_2

Jack 123-45-6789 Mary 987-65-4321

Figure B-1. A table with repeating fields.

There are four fields and one record. It is obvious that the
fields repeat; the second pair of fields contain the same
sort of information as the first pair. This kind of table is
called unnormalized.

Suppose we have to find Mary's Social Security number. We
would have to search the NAME_1 field and the NAME_2 field
for "Mary". She might be in either one. This is a difficult

B-1


THE RELATIONAL MODEL



query. More difficult still is a join, that is, connecting
the records in this table with the records in another table
on the basis of a common field like Social Security number.
Each record has two such numbers. Which one should be used?

Unnormalized tables can be normalized simply. Just make
additional records of the repeating fields as in Figure B-2.

NAME SSNO

Jack 123-45-6789
Mary 987-65-4321

Figure B-2. A normalized table.

Normalized tables are said to be in first normal form (1NF).

Let's define a concept known as the primary key of a table.
The primary key is a field or combination of fields which,
when their values are specified, uniquely identifies no more
than one record. For example, in a table with the two fields
SSNO and BIRTH_PLACE, SSNO would be the primary key because
for each listed Social Security number there is only one
birth place. BIRTH_PLACE cannot be the primary key because
for a given BIRTH_PLACE there may be many Social Security
numbers (people).

If a table has a primary key, its records are unique.

It may take several fields to comprise a primary key. For
example, in a table which gives different people's incomes
for different years, there are the fields SSNO, YEAR and
INCOME. The first two uniquely identify the record; they are
the primary key.

Let's append a fourth field to this two field primary key
table to study second normal form (2NF). The table is
already in 1NF because its records do not contain lists or
repeating fields. See Figure B-3.

SSNO YEAR INCOME NAME

123-45-6789 1980 15000 Jack
987-65-4321 1980 22000 Mary
123-45-6789 1981 16500 Jack
987-65-4321 1981 24000 Mary

Figure B-3. A table not in 2NF.

Once both SSNO and YEAR are specified, the record can be
identified and the other two fields looked up. Neither field
in the primary key is sufficient to identify INCOME uniquely.

B-2


THE RELATIONAL MODEL



For example, YEAR = 1980 identifies two records. The entire
primary key, both fields, is necessary to identify INCOME.

However, once SSNO is known, NAME can be looked up in Figure
B-3. That is, NAME depends on less than all the fields in
the primary key. In relational jargon, INCOME is fully
functionally dependent on the primary key but NAME is only
functionally dependent.

If all the non-key fields in a 1NF table are fully
functionally dependent on the primary key, the table is in
2NF. Why should we care?

Data base corruption is one reason we should care. For
example, suppose in entering "Mary" twice, it was misspelled
once as "Marie". Then, one Social Security number would
correspond to two NAMEs. Note that full functional
dependency implies only one value per non-key field (INCOME)
for each primary key value. Incorrect replicates (Mary and
Marie) cannot exist because multiple entries per primary key
are precluded. Hence, consistency or integrity is one reason
for 2NF.

There are other reasons for preferring 2NF but let's go on to
correct the problem. Split one table into two. See Figure
B-4.

SSNO YEAR INCOME SSNO NAME

123-45-6789 1980 15000 123-45-6789 Jack
987-65-4321 1980 22000 987-65-4321 Mary
123-45-6789 1981 16500
987-65-4321 1981 24000

Figure B-4. Tables in 2NF.

Both tables are in 2NF. In the left table, SSNO and YEAR are
the primary key and INCOME is fully functionally dependent on
them. In the right table SSNO is the primary key and NAME is
the non-key field (but full functional dependency is
immaterial because of the one field primary key).

Note that in the two table version of the data base, we can
record Fred's Social Security number (right table) although
there isn't enough information to make an entry for him in
the left table. This capability did not exist in the single
table (1NF) version.

The table in Figure B-5 is in 2NF but it still has problems.




B-3


THE RELATIONAL MODEL




SSNO AGE HOME_TOWN AVG_TEMP

123-45-6789 32 Boston 53
987-65-4321 19 Dallas 59
369-47-5814 50 El Paso 59
421-63-9631 41 Boston 47

Figure B-5. A table not in 3NF.

SSNO is the primary key of this table. The non-key fields,
AGE, HOME_TOWN and AVG_TEMP, are functionally dependent on
the primary key. (They are fully functionally dependent,
too, but this is trivial for a one field primary key.)

Observe that Boston has two average temperatures and they are
different. True, this illustrates a data entry error.
However, it is possible to structure a data base to guard
against such errors. That's what third normal form (3NF) is
for.

In Figure B-5, there is a functional dependency between
non-key fields. That is, AVG_TEMP is dependent on HOME_TOWN.
Once HOME_TOWN is known, an uncorrupted data base would tell
us AVG_TEMP. Dependency among non-key fields is permissible
in 2NF but not in 3NF.

The remedy, as before, is to split one table into two. See
Figure B-6.

SSNO AGE HOME_TOWN HOME_TOWN AVG_TEMP

123-45-6789 32 Boston Boston 53
987-65-4321 19 Dallas Dallas 59
369-47-5814 50 El Paso El Paso 59
421-63-9631 41 Boston

Figure B-6. Tables in 3NF.

In each new table of Figure B-6, the left field is the
primary key. In the left table, the two non-key fields are
mutually independent -- knowing either does not identify the
other. Hence, the left table is in 3NF. There is only one
non-key field in the right table so 2NF implies 3NF.

Aside from avoiding the corruption encountered earlier, it is
also possible to record the AVG_TEMP for a HOME_TOWN even if
we don't know the SSNO of anyone who lives there. That is,
we can have a record in the right table without a
correspondent in the left table. Similarly, we can have a
record in the left table even if we don't know the AVG_TEMP
of that person's HOME_TOWN.

B-4


THE RELATIONAL MODEL



To sum up, a table in 1NF has no lists or repeating fields in
its records. A 2NF table is a 1NF table in which each
non-key field is fully functionally dependent on the entire
primary key. Finally, a 3NF table is a 2NF table in which
the non-key fields are mutually independent.

A data base consisting of third normal form tables provides
the most protection against corruption. It also permits
tables to be modified individually, e.g., we don't have to
know the age and Social Security of someone in Detroit to
enter that city's average temperature in Figure B-6.










































B-5















APPENDIX C

USER SUPPORTED SOFTWARE



PC-DBMS is distributed as user supported software. You are
encouraged to make copies of the distribution diskette for
others. There is no license agreement but please pass along
all files without modification. User groups and similar
organizations may impose a nominal charge to cover the cost
of duplication.

The user supported concept of software is a significant
innovation (not mine) which benefits both user and author.
For the user, there is the convenience of leisurely
evaluation of the program on your own system in your own home
or office to see if it's right for your application. The low
cost of user supported software distribution also makes
possible a voluntary payment lower than the price of
commercial software. And, of course, there's no copy
protection to diminish the utility of the program.

For the author, there is the satisfaction of getting a far
larger number of copies into use than is possible with
conventional marketing. Also, the interference of piracy is
turned into a reinforcement in the distribution of the
program. Finally, if you have dealt with a publisher, print
or electronic, you understand the undesirability of that
alternative.

However, it doesn't work unless you want it to. If you find
PC-DBMS useful, I hope you will see fit to make a voluntary
payment of $20, by check or through your bank credit card.
Beyond what you already have, this is what you will receive
from me.

- Support. Comments, suggestions and especially bug
reports are welcome from anyone. However, only users who
have made a voluntary payment can expect replies to their
questions. Support will be provided on a best efforts
basis by mail and through Compuserve. My Compuserve id
is 74216,3033.


C-1


USER SUPPORTED SOFTWARE



- My thanks. PC-DBMS took a year to develop, test and
document. I enjoyed every minute of it.


Now, I ask that you send your voluntary payment with the
registration form. A copy of that form is on the next page
or, if you wish to leave your manual intact, another copy is
in the file REQUEST.DOC. If you need an invoice, you'll find
one following the registration form in both the manual and in
REQUEST.DOC.

For quantity purchases of PC-DBMS, contact Kware.

Thank you.







































C-2


PC-DBMS REGISTRATION FORM


Name _____________________________ Phone ____________

Company _____________________________

Address _____________________________

_____________________________

City _____________________________

State ______________ Zip __________

version 2.0


source of your copy (club or bulletin board),

if known _____________________________________________


To use a credit card, please indicate:

Visa __ or Mastercard __

card number ____ ___ ___ ___

expiration month/year ______/________

signature ______________________________


To pay by check, please make payable to Kware for $20.
Virginia residents add 4% sales tax.


Kware Thank you.
P. O. Box 16206
Arlington, Va. 22215 Paul Kobrin















C-3




INVOICE


Vendor: Kware, Inc. Federal EIN: 54-1304150
P. O. Box 16206
Arlington, Va. 22215 Date: / /


Description Qty Price Extension
----------- --- ----- ---------

PC-DBMS, 1 $20.00 $20.00
registration
& support
---------
Sub-total 20.00

Tax @ 4% for Va. residents ($.80)

---------
Amount due



You may retain this invoice.
Please make check payable to Kware.



Page Index-1


INDEX



All Default table,
keyword, 3-4, 5-4, 5-9 3-5 to 3-6, 5-5, 5-9,
Allf 5-12
keyword, 5-9 Define command, 3-10, 5-2
Alphabetize, 3-4, 5-13 Define utility, 3-10,
And 3-12, 4-2, 5-2
keyword, 3-4, 5-4, Dekey command, 3-12, 5-3
5-11 to 5-13 Delete key, 3-4, 4-3
Apostrophe Directory, 5-14
comment delimiter, 4-3 see also subdirectory
Ascii, 4-1 to 4-2, 5-12 Display command, 3-8,
Backslash 5-3, 5-5, 5-7
in deleted records, Display utility,
4-2, 5-3 3-8 to 3-9, 3-12,
Backspace key, 4-2, 5-3 to 5-6
3-3 to 3-4, 3-10, Distribution diskette,
4-3 2-1
Blank padding, 5-11 Dos, 2-1, 2-3, 3-1, 3-12,
Brackets, 5-2 4-4
Calculated fields, 3-4, Drive names, 5-2
5-10 Editor, 4-2, 5-6
Carriage return, Ellipses, 5-2
3-2 to 3-3, 4-3 End key, 4-3
Case Errors
sensitivity, 4-2, 5-2, see messages
A-1 Exit command, 3-12, 4-4,
Character string, 4-3 5-6
see also text string Export
Close command, 5-7 data, 4-2
Color, 2-1, 2-4 F1 key, 3-2, 3-8, 4-4,
Command window, 3-2, 4-2, 5-6
4-4, 5-4 F10 key, 3-6, 3-9,
Comments 3-11 to 3-12, 4-4,
in commands, 4-3 5-2, 5-4 to 5-5,
Comparison, 5-11 to 5-12, 5-14, 5-16
5-17 F2 key, 4-4
see also condition F3 key, 3-3, 4-4
Compuserve, C-1 F4 key, 3-9, 5-5
Condition, 3-4, 5-11, F6 key, 3-9, 5-5
5-17 F7 key, 3-3, 3-8 to 3-9,
see also comparison 4-4, 5-4 to 5-5
Continuation character, F8 key, 4-4
4-3 F9 key, 3-8, 4-4, 5-6
Control-numlock, Field block, 3-8, 4-2,
3-6 to 3-7, 5-14, 5-3, 5-6
5-16 Fields
Cursor keys, 3-3, 3-8, filler, 3-7, 5-2, 5-11,
3-10, 4-3 5-13
Data dictionary, 3-8 functionally dependent,
see also schema B-3 to B-4


Page Index-2


numeric, 3-7, 5-2 Mkini, 2-4, 4-4
text, 3-7, 5-2 Names
File specifications, 5-2 data base, 4-2, A-1
Files field, 4-2, A-1
character positions in, qualified field, 3-5,
3-7 4-2, 5-3, 5-7, 5-9,
data, 4-1, 5-2, 5-9, 5-11
5-14, 5-16 table, 4-2, A-1
def, 4-1 into clause, 5-14
distribution, 2-1, C-1 Normal form, B-2, B-4
idx, 4-1 Not
index, 4-1, 5-3, 5-6 keyword, 5-12 to 5-13,
maintenance, 3-12 5-17
schema, 4-1, 5-7 Numbers
Flying reform representation, 4-3
editor, 4-3 Open command, 3-3, 5-7
Functionally dependent Or
see fields keyword, 3-4,
Help, 3-2, 3-8, 4-4, 5-6 5-11 to 5-13
Help menu, 3-2 Output command, 5-7, 5-9
Home key, 4-3 Output window, 3-2
Import data, 4-2, 5-3 Pack command, 5-8
Index, 3-12, 5-3, Parentheses, 5-13
5-6 to 5-7, 5-14 Path names, 5-2
see also key Pc-dbms.ini, 2-3, 4-4
Initialization, 2-4, 4-4 Primary key, B-2 to B-5
Insert mode Printer, 5-7
editor, 4-2 to 4-3 Qualified field names
Installation, 2-2 see names
Into clause, 3-6, 3-12, Quote mark
5-7, 5-9, 5-14 text delimiter, 3-4,
Invoice, C-4 4-3, 5-6
Join Recall
relational operation, command, 3-6
3-5 to 3-6, 3-12, Record terminator, 5-2,
5-12, B-2 5-15
Key, 3-12, 5-16 Relational model, B-1
see also index Remove command, 3-8, 4-1,
Key command, 3-12, 5-6 5-8
Keyword, 4-2, A-1 Replace mode
see also truncation editor, 4-2 to 4-3, 5-6
Main screen, 3-2, Schema, 3-8, 3-10, 4-1,
3-9 to 3-11, 4-2, 5-3, 5-6 to 5-8,
4-4, 5-4 5-14 to 5-15
Menu Scroll, 3-5 to 3-7, 5-16
see help menu Select command, 3-3, 3-6,
Message window, 3-3 4-2, 5-3 to 5-4, 5-7,
Messages 5-9
error or warning, 3-3, Show command, 3-6 to 3-7,
4-4 5-7, 5-14 to 5-15


Page Index-3


Sort clause, 3-4, 5-3
5-13
maximum width, 5-14
Space, 4-3, 5-11, 5-13
Subdirectory, 2-3, 3-1
see also directory
Support, C-1
Tab key, 3-8, 5-6
Text string, 3-4
see also character
string
Tolerance command, 5-11,
5-17
Truncation
keyword, 3-6, 4-4, 5-1,
A-1
User supported software,
C-1
Warnings
see messages
Welcome screen, 3-1
Where clause, 3-4, 3-12,
5-3 to 5-5,
5-11 to 5-12
order of evaluation,
5-13
Wildcard, 3-4, 5-9
Word wrapping
editor, 3-2, 4-3