RISC DISC 1/RISC_DISC_1.iso/pd_share/apps/powerbase/Docs/Create

Text File | 1994-10-01 | 25.5 KB | 485 lines

                        Creating a New Database
                        =======================

To create a new database you need to go through five steps:-

        (i)     Create the database application shell.
        (ii)    Design the record screen.
        (iii)   Specify the number of records the database is to contain.
        (iv)    Specify the primary key.
        (v)     Build the empty database.

Each of these will be described in the order just given.

Creating the database application shell
=======================================
You need to have Powerbase installed on the icon bar but with no database
open, i.e. ‘No data’ should appear under the icon. Click SELECT over the
icon and a save box will appear. Type in the name of your database and drag
the database icon to a directory viewer. Remember that it cannot exceed 10
characters, including the initial “!”. If you enter more the name will be
truncated and you could end up overwriting an existing database with a
similar name. You don’t actually need to enter the “!”; Powerbase will
insert it automatically.

If you open the newly-created application directory (SHIFT/double click) you
will find that it contains files called !Boot, !Run, !Sprites, chkspr and
Colours and four subdirectories called Indices, PrintJobs, PrintRes and
ValTables. All Powerbase applications require these objects to be present so
don’t delete any of them.

Designing the record screen
===========================
When you created the application shell you were left with a blank window on
screen and it is on this window that you must design your database record.
This is the lengthiest and trickiest part of setting up any database,
although efforts have been made to render it as painless as possible. If you
have closed the window just click on the Powerbase icon on the icon bar to
re-display it.

Clicking MENU over the window brings up the ‘New database’ menu on which
every item except ‘Design field’ and ‘Help’ is shaded at this stage. The
‘Design field’ choice leads to a dialogue box which allows you to specify the
characteristics of a field. First you must decide what category of field you
want to create. There are four such categories, selected via radio buttons:-

      (i) Data  (ii) External  (iii) Button  (iv) Check-box

For the present we will confine ourselves to the first, which is the default
selection. As well as the field category you must decide on the type of
field within the category. Most fields are of ‘Alphanumeric’ type and that
is offered as the default. We’ll see about other types later.

Decide on a name for the field and enter it in the ‘Descriptor’ icon. This
is the name which will appear on the record window and may be up to 40
characters long. You should also enter a ‘Tag’, which is limited to 4
characters. Next enter the ‘Data length’; the maximum number of characters
the field is to hold. Values up to 246 are allowed. Now click on ‘Create’
and the field will appear on the record window. It’s probably not where you
want it so use SELECT to drag the white rectangle to the position required.
When you drop the field in its new position you’ll see that the descriptor
falls into place too. If you want the descriptor somewhere other than to the
left of the data icon (above it, for example) then you can move the
descriptor by itself without the data icon moving.

Basically that’s all you actually need to do in order to create fields.
Mistakes may be corrected by clicking MENU over the field to be altered and
making the required changes. You then click on ‘Update’ rather than ‘Create’
(‘Create’ is shaded, in fact). A field may be deleted by clicking MENU over
it then clicking on ‘Remove’. To insert a field into the middle of a record
you must enter the number of the field which must follow the new one in the
space provided. Clicking ‘Create’ will then create the field in the
correct place in the field sequence. It isn’t sufficient to just drag the
fields around - the order in which they are accessed when the database is in
use won’t be altered by that.

You might want to change the sequence of fields without recourse to the
tedious method of deleting a field and then re-creating and inserting it.
There are two ways of doing this. Click MENU over an existing field to bring
up the dialogue box. We will call this the “current field”. Enter the number
of another existing field in the same place as was used above to specify the
insertion position of a new field. We’ll call this the “entered field”. The
two button ‘Swap with’ and ‘Renumber as’ may then be used. ‘Swap with’ does
exactly what you would expect: it swaps the positions of the current field
and the entered field in the field sequence.

The action of ‘Renumber as’ is more complex. The current field acquires the
number of the entered field. If this involves giving the current field a
lower number than previously the fields above the new position are all moved
up one place to open a gap for it, at the same time closing up the gap left
by moving the current field from its old position. If it is being given a
higher number the fields above its old position all move down one place,
closing up the gap left by its removal and opening a gap in the required
place farther up the sequence.

You can, if you wish, build up the record by creating more fields in the
same way and then go straight to the next main section (Specifying the
database size). If, however, you want more at this stage read on!

Re-sizing the bounding box
—————————————
Left to itself Powerbase makes all its field icons the same height and of a
suitable length to contain the number of characters specified in ‘Data
length‘. This may not be quite what you want. If the Data length is more
than about 70 the field runs off the right edge of the window. You may also
want to make the field taller for emphasis. (But NOT for displaying multiple
lines. Only fields of ‘Text block’ type can be multi-line. These are
discussed later.) There are two ways of altering the size of a field’s
bounding box:-

        (a) By dragging with ADJUST.
        (b) By typing in the required width and height in the ‘Data field’
        dialogue box. The units used are such that, in the most common
        screen modes such as 12 and 15, a character is 16 units wide and 32
        high and the screen itself is 1240 units wide and 1024 high. (N.B.
        the default height for bounding boxes is 48 units.)

Besides altering the size of the bounding box by typing the data directly,
you may adjust its position in the same way by entering the X and Y
co-ordinates of the lower left corner. The units are the same as for width
and height but the origin is the TOP left corner of the record window. This
means that the Y values are always negative.

More about tags and descriptors
———————————————-
Tags are of great importance in Powerbase. They are used when querying the
database to produce reports, export CSV files etc. and also by some internal
operations. Every writable field must have a tag and no two fields may have
the same tag. Descriptors are less important. Their main purpose is to
provide visible labels for fields and in some cases you may not need a
descriptor at all. e.g. If your database is to hold names and addresses you
might want the record to look like this:-

                           NAME Fred Bloggs
                        ADDRESS 27, Every Street
                                Anytown
                                Woolshire
                                WL4 7XZ

There are 5 fields here but, having given the second one the descriptor
ADDRESS, you don’t really want descriptors for the remaining 3 (except
perhaps POSTCODE for the last). It is quite in order to have null
descriptors like this, but your MUST give each field a tag. Suitable ones
might be NAME, ADD1, ADD2, ADD3, ADD4. (Remember they must be unique and not
more than 4 characters.)

The only cases in which you may omit the tag is where the field is simply an
explanatory label and contains no actual data so that there would be no
reason to include it in a query.

Other types of Data field
————————————-
So far we have only used fields of ‘Alphanumeric’ type. Clicking on the
“bump icons” to the left of the field type lets you cycle through the
various types available. These are:-

        (a) None                Accepts any printable character.
        (b) Alphanumeric        Accepts all letters and numerals and common
                                punctuation.
        (c) Upper case          Accepts capital letters and numerals only.
        (d) Number              Accepts numerals,+,- and . (decimal point).
        (e) Yes/No              Accepts Y and N only
        (d) Date                Accepts dates in the form dd-mm-yy or
                                dd-mm-yyyy.
        (e) Calculated          Can’t be typed into directly. Takes its
                                value from other (Numeric) fields.
        (f) Composite           Similar to a Calculated field, but for
                                textual rather than numeric data.
        (g) Time                Accepts times in the form hh:mm:ss up to
                                a maximum value of 23:59:59.

As an alternative to the “bump” icons you may click with SELECT or MENU on
the right-pointing arrow to the right of the field-type icon. This displays
a menu of field types from which to make your choice.
When ‘Numeric’ or ‘Calculated’ is selected, certain icons in the dialogue
box which are normally shaded become available. Thus, you can make such a
field of fixed-point type and Powerbase will modify whatever you type in so
that it is displayed to the specified number of decimal places. For
‘Numeric’ fields only, you may also specify an upper and lower limit for
numbers which may be entered in these fields.

A ‘Date’ field should be either 8 or 10 characters long in order to hold the
date in one of the two formats specified above. The separator in these dates
need not be a hyphen. Look in the Powerbase directory for a subdirectory
called ‘Resources’. Inside it is a file called ‘Config’. Load this into
Edit. There is a line specifying the character to be used as a date
separator. If you change this make sure it is the first character on the
line. Note that the actual TYPING IN of dates allows far more flexibility.
You may type ANY non-numeric character as a separator and Powerbase will
make sense of entries such as 4/5/87, duly converting it to 04-05-87 when
you press RETURN.

A ‘Calculated’ field is used when you want the record to automatically
display the result of a calculation based on other fields. Similarly, a
‘Composite’ field might be used to display a person’s name in the form
FORENAME SURNAME when the data actually typed in uses separate fields for
SURNAME and FORENAME. You don’t specify at this stage how the field value is
derived; that’s done when the database is actually working.

A ‘Time’ field (like a date) is checked for validity and (again like a date)
you may be fairly flexible in how you actually type the values in. If you
enter 3.45;9 it will be reformatted as 03:45:09. The colon separator may be
changed by editing the ‘Config’ file. Arithmetic may be performed on times
held in this type of field (see ‘Calculate’).

Other field categories
———————————
Only the ‘Data’ category of field has been discussed so far. The other three
will now be dealt with.

External fields
———————-
External fields allow you to link a Powerbase record to pieces of data of a
size and type which make them unsuitable for inclusion in a ‘Data’ field.
Such items are sometimes called “BLOBs” (Binary Large Objects) in the PC
world. The field types in this category and the types of data they “hold”
are as follows:-

        (a) Text                Plain text files, such as Edit creates.
        (b) Sprite              Sprite files, such as Paint creates.
        (c) Draw                Drawings such as Draw creates.
        (d) Text block          Plain text files, as for (a).
        (e) Picture             Sprite files, as for (b).

When you create a field of type ‘Text’, ‘Sprite’ or ‘Draw’ it appears on the
record window as a button bearing a small version of the icon for Edit,
Paint or Draw respectively. Files of the appropriate type may be dropped on
these buttons, whereupon the file is copied into a special system of
subdirectories within the database application. Unlike fields of the ‘Data’
category the data doesn’t become part of the ‘Database’ file within the
application; the text, sprite or drawfile retains its identity and may be
exported for editing in the appropriate application. Clicking on the button
in the record window will display the file if the filer knows the
whereabouts of the relevant editor (Edit, Paint or Draw).

‘Text block’ and ‘Picture’ fields take this a step further by actually
displaying a text or sprite file on the record window. The bounding box of
the icon needs to be of suitable size to hold the text or picture. In the
case of a ‘Text block’ too small a box will cause the text to appear
truncated. None is actually lost; it just can’t all be displayed. Too small
a box for a ‘Picture’ field will cause the sprite to spread beyond its
boundaries. (N.B. To display the same sprite on each record, e.g. a company
logo, define the field as of type ‘Logo’, not ‘Picture’. The inclusion of
logos is described in the file ‘Calculate’ which may seem an odd place for
it but there is a reason!)

Check-box fields
————————
These are fields whose status alternates between two values when clicked
over with SELECT. Three types are defined:-

        (a) Cross/tick  Displays a cross by default. A click changes it to a
                        tick. A second click changes it back to a cross.
        (b) Null/tick   Similar to (a), but first state is an empty box.
        (c) Null/star   Similar to (b), but second state is a star.

Check-boxes provide the fastest way of entering true/false or yes/no type
data. If you examine the ‘ValStrings’ file inside the Powerbase directory
you will find strings associated with each of these three types the latter
parts of which read, respectively:-

                QNo,Yes
                Q ,Yes
                Q ,*

These specify what will actually appear in a print-out when a check-box
field is included in a query. You may change them if you wish, but don’t
omit the initial Q and take care not to alter the remainder of the string.

Stamp fields
——————
These are of the following types:

        (a) Record number       Database record number.
        (b) Sequence number     A unique value which is numbered upwards
                                from a base value set by the user.
        (c) Time                Time at which record was created.
        (d) Date                Date on which record was created.
        (e) Date and time       Date and time of record creation.
        (f) Day                 Day on which record was created (in the form
                                Mon,Tue etc or day of month as 10,24 etc).
        (g) Month               Month in which record was created (as either
                                a string; Jan,Feb etc or a number; 1,2 etc).
        (h) Year                Year in which record was created (as a
                                four-digit number, e.g. 1993).
        (i) Last altered        Records the date and time when a record is
                                first created and updates it when the record
                                is subsequently altered. Merely displaying
                                the record does NOT cause updating to occur.
        (j) Logo                Allows a sprite to be included as a logo on
                                every record. (See note below.)

Like ‘Calculated’ and ‘Composite’ fields none of the above is editable by
the user; all are automatically “stamped” by Powerbase at the time a record
is first entered. When using (a) or (b) make sure the ‘Data length’ fill-in
box contains a large enough value to accommodate the longest number which
will be encountered. In the case of (b) the base value from which the
sequence numbers begin is entered in the ‘Numeric min’ box. For the other
types the required field length is already known by Powerbase and the
‘Data length’ box is therefore shaded.

Type (d) fields may display the date in any of three formats:

        (i)     Sun,01 Aug 1993         (called “Date stamp”)
        (ii)    01-08-93                (called “Date stamp8”)
        (iii)   01-08-1993              (called “Date stamp10”)

(ii) and (iii) are identical to the formats in which Powerbase displays
dates of the ordinary editable-data type (see “Other types of data field”,
above). The numbers refer to the field length occupied by the date stamp.

Type (j) fields require the name of the sprite to be entered as the tag of
the field to be used as a logo. This means that the sprite name is limited
to four characters. You may have several logo fields on your record, all the
required sprites being included in a file called ‘UsrSprites’ which is
placed inside the database directory. There is a bug in this feature which
results in an Address exception error if the UsrSprites file is already
present when the field is created. The procedure is to create the logo
field with the appropriate sprite name as a tag, create the empty database
then, finally, copy your UsrSprites file into the database directory.

Button fields
——————-
Any or all of the control buttons on the Powerbase keypad may be made to
appear on the record window itself. They have exactly the same functions as
their keypad equivalents. There are also a few extras (Print, Exit, Quit and
Directory) which have no keypad equivalent. The first three duplicate the
actions of ‘Print’ on the main menu and ‘Close database’ and ‘Quit’ on the
icon bar menu. ‘Directory’ enables you to open a filer window by
clicking on the button. The descriptor may be used to give the name of
the associated directory and the link is established by dropping the
directory onto the button in the same way that files are linked to
fields of ‘External’ type. These button fields allow you to build a
customised database which lets the user use only the features you want
him/her to have access to, since the keypad and menus can then be
suppressed.

Specifying the database size
============================
Immediately beneath the ‘Data field’ entry on the menu is one called
‘Default database’. Choosing this is by far the fastest way of getting a
database up and running. Its action is to create three files inside the
application directory. These are called ‘Form’ (which holds the record
design), ‘PrimaryKey’ and ‘Database’ (which will ultimately contain the
entered records). The number of records in the database is set to 100, with
25 as the amount by which this should increase when the database becomes
full. The primary key is defined as the first four characters of the first
writable field. The database is opened and a blank record displayed ready
for data entry.

Since you can always alter such things as the database size and primary key
structure later, you might wish to use these defaults while you experiment
with the database. If, however, you want to set the database size yourself
at this stage proceed as follows:-

(1) Save the ‘Form’ file by following the menu option and clicking on ‘OK’
or typing RETURN when the save box appears. The pathname is correctly set
for saving the file inside your database application.

(2) You will now see that the ‘Database size’ choice is no longer shaded and
may be used to reach the dialogue box in which you specify the number of
records in the database and the increment for expanding the database when it
becomes full.

Specifying the primary key
==========================
The primary key (or any other key) is derived from a record field called a
key field. The key field is selected by either clicking on the “bump” icons
which allow you to cycle through the field tags, or by choosing from a menu.

There is a choice of two mechanisms whereby the key is derived from the key
field:
       (1) The division between words in the field may be ignored (this
           being the default).
       (2) Characters may be taken from one or more words in the field.

An option switch allows you to choose (1) or (2). With the first choice you
specify only the length of the key; the contents of the four Word icons must
all be set to ZERO. With the second choice you specify the number of
characters to be taken from each of up to four words and need not specify
the actual key length at all. Suppose the key field contains a person’s
name. The following examples compare the keys which would be assigned with a
key length of 8, firstly by ignoring word breaks and secondly by using 4
letters from each of words 1 and 2.

        Bloggs Fred             BLOGGSFR        BLOGFRED
        Doe Joe                 DOEJOE##        DOEJOE##
        Percival Jean           PERCIVAL        PERCJEAN
        Simpson Janet           SIMPSONJ        SIMPJANE


Key fields need to be chosen with care. An ideal key field is one whose
contents would never be repeated in another record. Powerbase allows you to
enforce this condition if you wish (see “Passwords”). Occasional repetitions
need not be serious, but a field which can have only a few “values” is
usually a poor choice. A customer number or membership number is the sort of
thing we are looking for, but your database may not contain anything like
that.  In a database of school pupils the pupil’s name would be a good
choice of key, but the form teacher’s name would not, since only a small
number of names would be involved, each appearing on the record of many
pupils.

Suppose we decide to use a person’s name, stored surname first, as a key. We
can hardly use the whole name; it would be far too long. The first four
letters would be more appropriate but, since this is the start of the
surname, we might have a lot of Smiths or, in Wales, a VERY large number of
repetitions of Jones or Evans! To get round this problem, Powerbase allows
you to construct an alphanumeric key from characters taken from up to four
successive words. You will seldom need to go as far as this. In this case
a five-letter key made up from the first four letters of the surname and one
letter of the forename would be good enough for most purposes. Smith Peter
and Smith Janet would have the keys SMITP and SMITJ. Duplication can still
occur, but not often enough to be a serious problem. It isn’t normally
sensible to build a numeric key from several bits in this way and Powerbase
won’t let you do so. Such a key consists of the numeric value of the chosen
key field.

There are databases where no field seems suitable as a key field. Consider
a database of classical music. There would be a field for the composer’s
name and one for the name of the work. Neither is much use on its own: the
former would contain possibly hundreds of occurrences of Mozart and
Beethoven and the latter would have numerous repetitions of Symphony No.5
or String Quartet in D minor.

To deal with such situations Powerbase allows you to use a composite
alphanumeric key, based on two fields in combination. A second group of
icons allows you to cycle through the tags for the second field, or choose
it from a menu.

Imagine the two fields placed end to end, with a single space between them.
The key may then be based on the first four words of the composite field. We
might, for the music database, use all four words and take 4,3,3,2
characters respectively from them.  The following two works then give the
keys shown:

                BEETHOVEN  Symphony No 5:       BEETSYMNO5
                MOZART Piano concerto 23:       MOZAPIACON23

Note the following points in these examples:

        (a) If a word is shorter than the number of letters assigned to it
        (“No” in the first example) then the whole word is used but no
        padding is inserted.

        (b) It may be necessary to leave out an insignificant word (“No” in
        the second example, so that a significant one (“23”) comes in the
        first four words of the composite field.

Once you have decided on the structure of your primary key, go to the
dialogue box from the ‘Primary key’ menu entry. Click on the first icon
until the tag of the required field is shown. Do the same with the second if
specifying a composite key. Enter the numbers of letters assigned to each
word.

Another choice to be decided is if the indexing is to pay any attention to
whether letters are upper or lower case. By default indexing is NOT
case-sensitive, all keys being forced to upper case before insertion in the
index and search strings being similarly forced to upper case. Thus if a
record has the word “Horse” as the contents of a key field and the first
four letters are used as the key then the entry in the index will be “HORS”
and you may search for it by entering “HORS”, “hors”, “Hors”, “hOrS” etc.

This might not be what you want. If you require indexing to be case-
sensitive then select the switch labelled ‘Case sensitive’. The forcing to
upper case described above does not then take place - keys are inserted and
strings are searched for “as is”. The field containing “Horse” will be
indexed as “Hors” and only that precise combination of upper and lower case
letters will successfully find it. Alphabetic keys will be ordered according
to the ASCII values of the letters. Since lower-case letters come later in
the ASCII table than upper-case ones a record containing “dog” would appear
AFTER one containing “Horse”, whereas one containing “Dog” would come before
“Horse”.

Once you are satisfied click OK and your database will be created. The
record window is redrawn and you may start entering data at once.