Create New Database

Use this option to create a new database to your own design. It is worth spending a little time thinking about the information that you wish to store, and the form in which you want to enter and retrieve information.

No long-term harm is done if you do not get your design correct first time, since it is easy to restructure a database (one of the other options on the above menu). A few notes beside you whilst you create the new database will help to ensure that you do not omit anything.

There are four main stages to the design of a new database:

Define name and date format

Define data items

Define the indexing structure and file size

Reserve space on disk

First give the pathname of the database you want to create.Use the <F4–Select> to check on existing databases. If you choose a name that already exists, you are warned, and asked if you want to overwrite it.

Define Name and Date Format

Title - Enter a descriptive file title here, up to 30 characters in length. This database name will appear in the headings of all reports printed using this database. It is not the same as the database pathname.

Date Format - You are now prompted to specify the date format to use within this database. The UK date format in the form DD/MM/YY as shown above is displayed. This represents two characters each for the day, month and year. For example, the UK date of 29th September 1996 is printed and entered as "29/09/96”. In fact you have a choice of two other formats for dates used with this database:

MM/DD/YY (This is the date format used in the USA)

YY/MM/DD (This is sometimes used in Scandinavian countries)

The format that you specify here must be used throughout this particular database whenever data is entered into a date item.

Auth-Level – This prompt asks for the authority level that must be set as a minimum against the user before they can access this database. Authority levels run from 0-9 where 0 is the highest authority and 9 the lowest.

You now go to the next stage in which you define all the individual data items to be held in the records of this particular database.

Define Data Items

During this second stage you specify the data items that make up the record in this database.Each definition screen allows you to specify of up to 16 data items, to a maximum of 160 data items per record.The list of data item types you can use are shown at the bottom of the screen for reference.

You must enter a name for each data item; define the type of data that is to be held in that data item (alphabetic, numeric, date type, etc.); and in some cases provide additional information such as the length of the data item.

When designing the record layout, you may wish to leave some data items unused to make the input screen easier to read. This does not affect either the size of the record or the amount of disk space required for the database.

If you key <Escape> during the data item definition screen you are given a number of options concerning the database design, including the options to abandon the database, or to complete the definition — see Database Definition Options below.

Data Item Names

Data item names can be up to 13 characters long. Try to use a descriptive alphabetic name for each data item. This makes it easier for a user subsequently to know what data to enter here. For example, a data item that will be used to hold a date of birth can simply be called DATE-OF-BIRTH. Note that both capital (upper case) and small (lower case) letters may be used in the name, but it is recommended that you use capital letters throughout.

Data items fall into one of five categories: free-format data items, numeric data items, date data items, calculated data items, and table look-up data items. See Configuration Guide appendix D for a full description of the data item types.

Data item types are described using single letters (upper or lower case may be used here, but are translated to capital letters). For some the maximum length of the data item must also be specified, whereas for others this is implied by the data item type.

Free Format Data Types

X Free-format data item, up to 64 characters in length. Used for names, address lines, and other descriptive information. Key X for this item type, and give the maximum number of characters to be reserved for this data item under Text Size.

Y Free-format numeric data item, up to 8 digits, input validated as being numeric only. Key Y for the item type and specify the number of digits under Text Size.

? Accepts only Y or N, or it may be left blank.Key the item type as "?”. No size is required here since a single character is used both to store the value in the record and when displaying the data item.

Numeric Data Types

J Numeric, holds values between ± 9999,999,999.9999

K Numeric, holds values between ± 999,999.99

L Numeric, holds values between ± 9,999,999,999.99

M Numeric, holds values between ± 99,999,999.9999

N Numeric, holds values between 0–9999

R Numeric, holds values between ± 99,999,999.999999

Key the item type, (J, K, L, M, N or R). Size is not needed here since the system stores these numbers in compacted form in ten-character data items (J, L and M types), six-character data items (K type) or two-character data items (N type). Note that ‘R’ items cannot be used in calculated fields or be totalled.

Date Types

D This represents a date in the format already specified (see above). Key D.Again, no size is required, as the date is stored economically in two characters.

E This represents a date in the format already specified (see above), except that the year is held as a four-character field.Key E. Again, no size is required, as the date is stored in four characters.

F Forecast date. This allows forecasting by adding a number to a date (D-type or E-type) or comparing the system date to a date item already defined. Both the relevant data items must have been specified previously in the record. There are eleven different types of forecast, with different types of result:

Forecast type D – to the date (Base) add a number of days (Plus) and show the output in the standard date format defined for this database

Forecast type E – as for forecast type D above, except that the year is always shown with four characters, not two

Forecast type W – to the date (Base) add a number of weeks (Plus) and show the output as YYWW — year and week number in the year

Forecast type M – to the date (Base) add a number of months (Plus) and show the output as YYMM — year and month

Forecast type A – compare the date (Base) to the current system (today’s) date and give the result as the number of days since the base date to today

Forecast type 1 – compare the date (Base) to the current system (today’s) date and give the result as the number of years since the base date to today. If less than a year, the answer is zero. If between 1 and two years, result is 1

Forecast type 2 – compare the date (Base) to the current system (today’s) date and give the result as the number of months since the base date to today, ignoring any years. The result is always between zero and 11

Forecast type 3 – give the day of the week for the date (Base) in the text form SUNDAY, MONDAY, TUESDAY etc. (capital letters)

Forecast type 4 – give the day of the week for the date (Base) in the text form Sunday, Monday, Tuesday etc. (capital and lower case letters)

Forecast type 5 – give the day of the week for the date (Base) in numeric form 1 for Sunday, 2 for Monday, 3 for Tuesday, etc.

Forecast type 6 – to the date (Base) add a number of weeks (Plus) and show the output as just the week number in the relevant (differs from type W). Week 1 is always the week in which 1^stJanuary falls

Key F for the data item type; and input the values for forecast Type, Base and Plus, keying <Enter> after each value. For example, "Type D Base 11 Plus D12” would take the date found in data item 11, and add to this the number of days found in data item 12, displaying the result in the usual date format.Note that the number of days to be added usually comes from an N-type number item; if it is taken from, say, an L-type item, the decimal places in that item are ignored. Instead of using the value in a data item for "Plus”, the value of a constant can be used — to use the value in constant number 5, type "C5”.The use of a negative constant value will have the effect of calculating backwards from the base date rather than forwards.

Calculated Data Types

* Calculated Item. No data is entered or stored for this data type, since the program calculates the contents from the formula that you specify. You may key up to five elements in the calculation, specifying for each element the operator; either D for data item or C for constant (see below) plus the data item number within the record, or constant number.

The data items used must exist as prior-numbered items in the database, must be numeric (excluding Y/R-types), but can themselves be calculated items. In addition, you can use a ?-type item in a calculation. If the value of the ? item is Y, then the calculation treats the value as 1, otherwise the calculation treats the value as zero.

For example:

+ D12 / D3 * D14

...takes the value to be found in data item 12 (which must therefore be one of the numeric data item types), divides this by the value of the third data item, and multiplies the result of this by the value in the 14th data item. Complex calculations can be achieved by defining data items to hold intermediate values, and using these in a later calculation

Instead of using the value held in a data item as a term in the calculation, you may specify one of the 100 constants held for each database (see below). Thus, if you had stored the value "12” as the tenth constant, you could specify /C10in your calculation. This would be the same as dividing by 12, and is necessary because you cannot specify a constant value in a calculation in any other way

Two special calculation types use "#” and "@” as operators. The former sums a set of consecutive items in the file; the latter supplies an average of such a set. For # and @ functions, specify the first and last data item numbers to be included, prefixing with # or @ followed by D plus the data item number.The data item numbers used for the start and end of the range must relate to numeric data items. All numeric items of any type (except Y/R types) within the inclusive range are used by the program to calculate the result.

e.g. # D12 # D22

@ D23 @ D46

All numeric items of any type (except Y/R types) within the inclusive range are used by the program to calculate the result.

In addition, for Diamond and Premier systems, there are a number of special terms that may be used with the calculation, all of which start with "!C”. These are fully described in the Configuration Guide appendix D.

% This is similar to a calculated item (*) but the result is multiplied by 100 and shown with a percentage sign. The facilities of # and @ may not be used, however.Key the data item type %. Specify the elements of the formula (up to five) as described for * type items above.

& Grouped Items. Up to three items can be grouped to make a larger item (up to a maximum of 64 characters). Only data types X, Y, C, N, T, ?, & and = may be grouped together. Specify the data item name; the item type; and then each of the data item numbers, keying <Enter> after each one. No storage space is used for this data item type.

A common example of the use of a grouped item is to set up three X type items for TITLE, INITIALS, and SURNAME, and then specify a grouped item for FULLNAME. For example:

4 TITLE X(8) Mrs

5 INITIALS X(10) J E

6 SURNAME X(20) Smith

7 FULLNAME &4 5 6 Mrs J E Smith

A separator (such as a full-stop) may be specified, or left blank if none is required. You cannot subsequently key any information into the FULLNAME data item, since the program automatically puts it together for you.

A grouped item may optionally be "fixed”. This means that the second and third components of the grouped item always begin in the same position, instead of starting immediately after the separator.The default is not fixed. Suppose, for example, that a serial number consists of a product code, a date code and a sequential number in a database. Records in a database might look like this:

Product Date Number Combined (fixed, separator /)

A425 JULY 0353 A425/JULY/0353

B7 JULY 1423 B7 /JULY/1423

C64 AUG 1567 C64 /AUG /1567

The effect of the fixed group in the example, using / as a separator, is that JULY is always in the same position within the grouped item, whether the first item has one, two, three or four characters. It therefore becomes feasible to select on the grouped item on the basis of JULY in positions 6–9, which would not be possible if the group were not fixed.

= Sub-item.This type of item is the reverse of a grouped item in that it can be used to break down the elements of a data item into smaller data items. You specify the data item number, the offset (the character at which to start copying), and the number of characters to be copied. You can specify not only text (X-type and Y-type) data items, but also the K-, L-, M-, J, *- and %-type ­numeric items.

For example, suppose data item number 7 holds a reference code where the first three characters denote the area and the last 6 characters denote a membership number separated by an oblique, in the form LON/123456. Other data items can be defined to separate out these elements:

Data Name Type Definition Comments

AREA = 7 1 3 Data item 7, starts at position one, three characters long

MEMBER = 7 5 6 Data item 7, starts at position five, six characters long

Table Look-up Data Types

T Look-up table of descriptive values. Up to 9999 table entries of up to 32 characters each can be stored for any database. The table becomes a part of the database itself. An example of the use of a table is to give standard descriptions to records in a database without having to key the description in every time. A table value of "2” could call up the description "Petrol fuelled farm implement” for example. This saves considerable time and space where sets of standard descriptive data are required for a database, and ensures consistency when you want to analyse the database by this data item. You can search on the value "2”, and know that you will get a definite match — no chance of missing a record because someone misspelled "Petrol”.

Key T for type; and then specify the range of table entries to be used in this item, for example: 15 - 35.

Several data items in one database may be defined to use tables. Each may use a separate range of table numbers, or share a range with other data items.

C Look-up table of constant values. Irrespective of the number of records in the database, up to 100 numeric values can be stored in a separate look-up table associated with each specific database.

Values may be in the range -300.00 to +300.00, and may be used to store (for example) currency exchange rates, or the rates of VAT. These constants can be used as terms in calculated data items (see data item type * above).

In addition, a data item can itself be defined to use these constants. Thus, a data item defined as C-type can take a value between 1 and 100, to represent the constant table number. The value of that data item for printing purposes is then that of the associated constant, rather than the number entered. If the value in the constant table is changed, then any use of this constants table entry within records automatically uses this new value.

For example, the %age rate of commission, COMM-RATE, payable to an agent could be defined as a C-type item.If the number "14” were entered, then the rate of commission payable would be the value found in the 14th entry of the constant table, and not the value "14%”. It is easier to change just a set of constants if the commission scale changes, than to go through all the agent records changing a value in each.

Key C as the item type; and then specify which range of entries from the constant table is to be used in this data item. As with table entries, more than one data item may be defined as a constant table look-up, and data items can share ranges of constants.

Completing an Item Definition Screen

As you complete the sixteen data item on the screen you are asked:

If you answer ‘NO’ you are returned to the top of the screen so that you can make amendments to the information displayed. Note that you can use the upward and downward arrows to move between items to do this.

If you answer ‘YES’ or ‘CANCEL’ (or if you <Escape> from the question) you are asked:

A reply of ‘YES’ takes you forward to the next screen to allow you to continue to define more data items. A reply of ‘NO’ takes you to the final steps in defining your database profile, as described under Data Items are Complete below.

Database Definition Options

The third stage in the definition process concerns the structuring and size of the database. The screen shown above is displayed when you key <Escape> from within a Define Data Items screen:

Display the Last Screen Again - This simply returns you to the top of the screen on which you were working.

Return to the First Screen - This takes you back to the first of the item definition screens, which show data items 1–16. From there you can use the next option to advance to each subsequent screen.

Go to the Next Screen - This puts you at the start of the next item definition screen. If you have yet to define any items in the data item range covered by this screen, then it is blank.

List Datanames to the Screen - This can be helpful in a complex database to remind you which items you have already defined. It is a quicker substitute for returning through the item definition screens. You may use the <Page Down> and <Page Up> keys as well as the up and down arrow keys to review the database structure so far.

Enquire Record Size Status - This shows the following parameters for the database:

Number of data items specified so far. You may define a maximum of 160 items

The record size so far. The maximum size of a record is 2048 characters (represented by the sum of the record size, not the printed size — numeric items take less space in the record than when they are printed). This is of value if there is a danger that the maximum record size might be exceeded

Number of records per block. Records are written into and read from blocks that are 1024 characters long (for the technically-minded, data is physically written to disks in 512-character blocks, and we use a multiple of this for our purposes). The number of records per block is of little interest, except that the more records that fit into a block, the faster the subsequent processing of the file

Unused characters per block. If the number of records does not exactly fit into a block, then the spare space at the end is wasted. In these days of cheap storage, this may not matter. However, if a record were to take 513 characters, then 511 characters would be wasted in each block. Even the most profligate of us would probably want to see if we could not save a character somewhere in our record design, so that we could pack two records per block!

Data Items are Complete - This asks some final questions about the database preparatory to committing the definition to the disk. If you <Escape> from the first question on this screen, you are asked if you want to abandon the database.

The name of your database shows in the top heading, and the size of the record you have designed (in characters) and the number of records per database 1024-character block, are displayed against points numbers one and two on this screen.

Each database block contains an exact number of records, so if your record size is 100 characters long, there will be exactly 10 records per block, with only 24 characters wasted at the end. The maximum size of a record is in fact two Datafile blocks, i.e. 2048 characters. If a record is defined to be between 1025 and 2048, then two Datafile 1024-character blocks will be required for each record.

Now come some questions you must answer: Note that you can return back to the database definition process during these questions by using the <F7–Option> key.

Prime-index Dataname - You may specify a prime index by entering the data item name or number, and <Enter>.You can use the <F4–Select> key to choose the item you want to use. A prime index provides a means of accessing records very quickly and it must be unique for each record. If no prime index is required simply leave this blank. For a prime index you may use data item types X (though only the first 16 characters can be used for the index ¾ 32 in Premier systems) Y, D, C, N, and T. Do not use an item such as a surname as prime index — it is unlikely to be unique!

Secondary index (1)

Secondary index (2)

Either one or two secondary indexes may be specified if required. These provide fast access to records, but the information held in the items need not be unique. For example, you may wish to access records by a data item SURNAME where the surname of several records in the database might be identical. Data items X, Y, D, C, N and T may be used here.

If secondary indexes are not required, just press <Enter> over the blank items. Again, you may use the <F4–Select> function to choose the data items you want.

Access by relative record number - It may or may not help you to access the records in the database by their record numbers, but you can make this choice here. Record numbers are sequentially allocated by programs when new records are added. If you have no indexes at all, you must use the record number, of course.

Number of records to be reserved - You must specify the maximum number of records you expect to hold in this database.Should you subsequently need more space, then use the Expand Number of Records option below to achieve this. Enter the number of records you require in the database, and key <Enter>. The number of bytes (characters) required to reserve the database on disk is shown.

Once the above settings are updated you are prompted

If you reply No, you are returned to the start of this screen to make any changes you want to make. If you reply Y, you are then asked:

No returns you to the data item definition stage, so that you can make further changes. If you reply <Escape> to the above question, you are returned to the Database Definition menu without writing the database to disk, thus abandoning it.

Yes to the above question now writes your database to disk.This may take a short while. It is an automatic process during which the space necessary for the database records and any indexes defined is reserved on disk. If there is insufficient room on the disk, the program alerts you with a message.You can either return to make the database smaller by specifying fewer records, or else abandon it altogether. As the space is reserved, the percentage of the process displays in the lower right-hand side of the screen. Once this reaches 100%, your new database is ready.

Apply Advanced Features to this Database (Premier only)

Premier systems allow you to apply some extra parameters to this file, known as Advanced Features.

If you respond ‘N’ the database creation is complete, if respond ‘Y’ a series of further options are available.

Add New Records: User-Id / Date / Time - Here you may specify three items into which to put the user name, date and time when a record is added into the database.

Extra Selection Items - A Datafile Premier option allows you to add up to six further items onto the <F4–Select> key to look up a record. These are not indexed, and the search is sequential through the file.The two secondary indexes must both be used before you can use this feature.

Extra Overflow - In a multi-user system where many people are adding records to a prime-indexed database, the index overflow area is likely to need tidying more frequently, which can hold up processing. To avoid this, define some extra overflow blocks (set to 2 to activate), so that the index will not need to be tidied until the system can gain exclusive access to the files.

Only Tidy From System – When using extra flow blocks the ‘overflow’ will keep getting bigger until an exclusive lock can be achieved and the index can be tidied. The bigger the overflow the longer the tidy will take.If a file, such as a batch code file, adds thousands of records per day this can mean that the system may tidy an index at an inconvenient time for this pause (say at 4pm when trying to finish deliveries for the courier). Setting this option prevents the tidy occurring when an exclusive lock is achieved unless the user SYSTEM achieves the exclusive lock. Generally this item should only be set where a night-time process is configured to tidy/audit the file.

Fast Indexes - This is a special facility, active in ProFiler only, whereby two independent databases are kept to hold the secondary index data for fast retrieval. Reply Y here to set on a fast index for a particular secondary index. You must separately create the fast index files themselves.

The purpose of this feature is speed up secondary indexes searches on large files.Secondary indexes use an area within the file definition to hold a copy of the secondary index item for every record in the file. This area is then scanned sequentially when looking for matching items. This is why we can allow for the occurrence of a search argument anywhere in the index — "office” to find "Office Products, as well as "Karslake Office Supplies”, for example. It is fast, but can still take a noticeable amount of time for larger files — more than 500,000 records, say.

The fast index feature works through another database for each index, to hold solely the secondary index item and its associated prime index item. The secondary index item is made the prime index on this file, which makes a lookup very fast indeed.

Note the following points about fast secondary indexes:

A fast index file structure must be exactly two items – a prime-index as item ‘1’ and a secondary index as item ‘2’. The prime-index is equal to the secondary index in the main file, the secondary index equal to the prime-index value in the main file.

A fast index file must have exactly the same number of records as the main file

The main file must have a filename of seven or fewer characters

A fast index file must be created in the same sub-folder as the main file

The name of the secondary index is the same as the main file, suffixed with "2” for the first secondary index, and "3” for the second secondary index

You should enable the index overflow blocks for each secondary index file

Fast indexes are maintained dynamically, and are recreated from scratch when the main file is audited. You must do an initial audit on the main file when you first set up a fast index

For example, a fast lookup could be attached to the second of the two secondary indexes (POST-CODE) of the main file called MEMBER.DFD. The second database thus has just the two items, POST-CODE as its prime index, and MEMBER-ID as the second item in the file. The index filename is MEMBER3.DFD.

Name and Address - This defines the first and last of a block of items that can be given special treatment in the applications. If defined in the file, then in applications such as the sales ledger, you can use the <F5–InsLn> and <F6–DelLn> keys to insert a line in the block, moving the current and subsequent lines down a line; or delete a line in the block, moving the ones below up a line.

Once complete you are asked to confirm the above details are OK and, if Yes, you are returned to the menu.