The UNIBASE Data Dictionary defines all the tables or files known to UNIBASE as well as the relationships between tables and all the calculations or rules that may be applicable to each table.
It is most important that the information in the Data Dictionary is correct as all UNIBASE programs use the Data Dictionary to find out about the files or table structure of an application.
The Data Dictionary is a text file called dict.dat in the current working directory where an application is to be run.
Alternative Data Dictionaries can be used by, setting the environment variable DICTIONARY to the name of the alternate Data Dictionary. Some commands also have an option -Y for specifying a new Data Dictionary.
Best practice is to put each table definition in separate files in /usr/local/app/<application>/dct as <tablename>.dct. Eg staff.dct. ubdict will take all these files, strip comments and excess white space. And combine them into a minified dict.dat file.
An index of dict.dat called dict.com is created when a table definition is accessed by any of the UNIBASE programs. This is done automatically whenever dict.dat is newer than dict.com.
The Data Dictionary consists of a series of file definitions separated by an arbitrary amount of comments. In keeping with the rest of UNIBASE, the Data Dictionary ignores (ie treats as comments) anything it doesn’t understand. While it gives great freedom in setting in house documentation standards it also means that care should be taken to ensure the structure is correct.
As protection against accidental corruption or mistakes during maintenance, UNIBASE checks that the Data Dictionary definition of a file matches the stored structure. If there is any discrepancy this is reported immediately and the program will not run until it has been corrected.
A second file “dict.com” maintains an index of table entries in “dict.dat”. This speeds up program initialisation. “dict.com” is maintained automatically by UNIBASE and you don’t need to or indeed should not access or modify it directly.
2.1 Table Definitions
A “Table Definition” consists of a table name followed by four sections that define the different aspects of a table. These are the Fields or Attributes of a table; the Keys or Indexes by which it is sorted; the Associations of Joins that exist between different tables; and any restrictions that may be used to define a Table Subset.
Every table will have a name and a set of field definitions. It will normally have a set of Keys. Most tables will have Associations but only some will have restrictions.
A “Table Definition” commences with a line that starts with a ‘:’ followed by a SPACE. All lines of text from this line to the end of the Table Definition are significant and must follow the rules for defining tables. Each section “Table Definition” is terminated by a line starting with ‘>’. Consequently, ‘>’ cannot be used as the start of any table or field name.
Example of format for a Table Definition
: table name
list of field specifications
>
list of key definitions
>
list of associations (or joins)
>
list of class conditions
>
2.1.1 Simple Table Names
A simple, or normal table name consists of a ‘:’ followed by a SPACE followed by a table name.
Table names may be any legal file name on your system. They may be preceded by pathnames, but remember that two files, one ending in ‘.dat’ and one ending in ‘.key’ are created for every UNIBASE table and your legal filename must allow for these extensions.
: stock
: /usr/data/orders
2.1.2 Alias Table Names
As well as Simple Table Names, UNIBASE also understands ‘alias’ tales names, ie another name for the same table. An alias table can be considered as a different table for the real table. This means that the same table can be accessed by two or more names. An Alias Table Name is any name followed by ‘,’ (and doesn’t have to be a legal file name for your system) followed by the real name (which does have to be legal).
Alias Table Names are particularly useful when a table must be used for two different purposes in the one report, form, or screen, eg. you might join to a table to get the total of some numeric field and you also want to report line by line from the table.
Tables with large numbers of joins that join back to themselves at some stage can sometimes confuse UNIBASE. Alias Tables without joins can be used to break any join loops when this causes a problem.
When defining an Alias Table you do not need to use the same field definitions as the original table. You are free to remap the fields into different fields of different sizes. The only rule is that the total stored record length must be the same.
Alias Tables can have a completely different set of non-stored fields (ie calculations or expressions). Data is stored in UNIBASE tables as ASCII text so it is quite allowable to have a number in one file and refer to fields that are part of the number (eg digits or the whole number part in an alias).
Alias Tables must also have Key Definitions that correspond to the real Key Definitions. Again this is because the stored information in the Alias Table and the real table must be equivalent.
Associations and Restrictions however do not need to agree as this information is not actually stored and therefore may vary between definitions. In fact the Restriction mechanism is usually used to represent some subset of the real table.
Note that Alias Tables are not real tables and therefore cannot and do not need to be created separately.
: stock,/usr/data/stock
“stock” is an alias to the real file “/usr/data/stock”
: orderList,orders
where “orderList” is an alias to the real file “order”
The Alias Table mechanism is one of the most important programming tools provided by UNIBASE. The correct use of Alias Tables is at the heart of UNIBASE non-procedural programming.
2.1.3 Including table definitions from one table in another
Alias tables are duplicate definitions for simple tables. When the table definitions are the same there is a short cut mechanism to make building the definitions easy and reliable.
<table
ie < followed by the table to include.
This is available in the attribute and key definitions only. Eg
: current,customer <customer > <customer > > >
The advantage, aside from less typing, is reliability. If the definition of customer changes than so does the definition of current.
Of course extra attributes can be defined after the included table.
However you cannot add extra keys as this would change the stored information.
2.1.4 Alias table definitions support macros
When multiple associations to a table are required it is not unusual to map some attribute names to new values. In Unibase this is done by using macro definitions. Like macros in other languages, these are not processing instructions but rather simple text substitutions.
Substitutions are indicated by a “%” followed by a number being the index of the argument to be used in the replacement. As these are text substitutions they can be used anywhere.
In a simple table declaration the macros arguments are placed after the table name and can be used anywhere. eg
: customer name PX customerId, 6 S %1, 30 %2 > %1 > > >
An alias table can make use of the customer definition by including it as follows:
: currentCustomer,customer <customer current PX > <customer current > > >
2.1.5 Table stubs
Often there is a common set of attributes to be shared between tables. These might be stored attributes or calculated attributes.
An easy way to include these in definitions is to create a table stub. These are table definitions that do not have any indexes. Eg
: staffAttributes name, 30 dateOfBirth, 8 DC > > >
Now we can have a staff table with an alias for supervisors. The table will have an attribute that is called staffNo in the staff table and supervisor in the supervisor table. Eg
: staff
staffNo, 6 S
<staffAttributes
>
staffNo
>
>
>
: supervisor,staff
supervisor, 6 S
<staffAttributes
>
supervisor
>
>
>
2.1.6 Help Messages
You can enter two kinds of Help Messages in the Data Dictionary. Table and attribute help. Help Messages are a set of lines starting with ‘#’ and the rest of the line is the help message. UNIBASE programs display Help Messages whenever the user types the <HELP> key. The Help Message is displayed in the middle of the screen with a box around it.
Lines commencing with the ‘#’ immediately following the table name definition make up the Help Message for the table.
The first line of the Help Message is a short message that may be displayed directly about the function keys (in the case of field help) and where additional help is provided (lines 2 onwards) the first line is also used as a help subject on the help menu.
The additional help lines are displayed in a box in the centre of the screen if selected from the help menu.
Help messages for attributes are also used in web applications. In this case the entire help message will be displayed as a tooltip if the user hovers over an input field. The first line is not required in this case.
: stock
#STOCK FILE DESCRIPTION
#STOCK FILE
#
#This file holds all stock
#master records
2.2 Attribute Definitions
The list of “Attribute Definitions” is a list of items as defined below. It is the first section immediately following the table name and optional table help. If any specification in the list can’t be understood UNIBASE programs will complain and stop.
The list of “Attribute Definitions” may also include all the fields from another file. This enables you to set up, eg. a tax calculation and then to include the one definition in any file that requires it. To include all the fields from another file, insert a line starting with a ‘<’ followed be the file name from which the fields are to come into your list. The fields will be included at the point where the ‘<’ include line is.
An Attribute Definition may be followed immediately by a Help Message. (see section .) Field Definitions are used in the Data Dictionary and also in the Report Generator (ubreport) and Forms Prompt (ubprompt).
Example Field Definition Layout
Attribute Name followed by a ‘,’ Attribute Width in Characters An Optional ‘*’ and the number of array elements Attribute Type Optional information specific to the attribute Optional Help Message
stock no, 10 P9 address, 20*3 PX #ONE OF THREE ADRESS LINES phone, 12 P(99)9999-9999 #TELEPHONE NUMBER # #The telephone number is an #area code in ‘()’ followed by #the eight digit code, eg. (02)1234-5678 price, 5 N2 quantity, 6 N0 stock value, 9 E2 [price] * [quantity] sales tax, 4 N1 20
2.2.1 Attribute Names
Field names can be anything that doesn’t start with a ‘>’, ‘#’ or a ‘*”, and doesn’t contain a ‘,’, ‘.’, or a ‘:’. It is also a good idea not to have them starting with a space or a tab, although they may contain spaces or tabs.
For compatibility with web applications it is a good idea to use only alpha numeric values for names. Mixed case and _ are good choice. Eg camel names like stockNo.
Attribute Names can be any length and as they may contain spaces don’t forget the ‘,’ as the end.
Every attribute in a record must be given a name.
Tables that contain the same information in two different attributes, which are going to be used to ‘join’ or associate the two tables, must have the same names, eg. “stock number” maybe used to relate an order line and a stock line.
The same attribute name may be used for fields in different tables that will not be joined. For example, “description” maybe used in many different tables even though it cannot be used to relate the files.
name order no date of purchase 123456 stockNo
2.2.2 Attribute Width
The field width must be chosen to accommodate the longest value excepted. In the case of text fields this is relatively easy to do. In the case of numbers a little care is required. Numbers are printed and stored with ‘,’ between every three digits, a decimal point and with any leading ‘-‘ sign, eg. “1,234.0”, “2,435,654.09856”, “-10”. These extra characters must be included in the field width. In addition, if the field is going to be summed for subtotals or grand totals then the length of the largest expected total should be used.
If the field width is not large enough UNIBASE will indicate an overflow by filling the fields with “*”s.
StockNo, 10 C onHand, 7 N0 name, 30
If “quantity on hand” is 6 characters wide with no decimal places then
Largest positive number: 99,999
Largest negative number: -9,999
2.2.3 Arrays
If you want to specify an array of attributes this can be done by following the attribute width with a ‘*’ and the array size. Each array element will have the same name as the Attribute Definition followed by a SPACE and then an index into the array
postal address, 30*3 PX
This will create three fields: “postal address 1”, “postal address 2”, “postal address 3”.
If an array of numbers is specified, UNIBASE supports a shorthand way of expressing the array, eg.
sales, 5*3 N0
Then the attributes available are “sales 1”, “sales 2” and “sales 3”.
The sum of an array is in the array name without and index. In this case the sum of the sales would be just “sales”. Some of the operators used to make expressions will also work on the name of the array, in which case the name refers to all elements of the array.
2.2.4 Attribute Types
Every field has a type. This helps UNIBASE to verify input, format output and process information.
In some cases the Attribute Type is followed by further information. An Attribute Type is indicated by a single capital letter followed by any additional information that may be required.
An arbitrary amount of with space (SPACES or TABS) can be used to separate the attribute width from the attribute type.
2.2.4.1 Text
This is the default attribute type (ie the attribute type if no attribute type is given). An attribute field will accept any letters, didgits, special characters, etc.
description, 30
2.2.4.2 Pictures
Pictures specify a format for the text, it indicates allowable characters for each character position and any mappings such as lower case to upper case. Note that the last pattern character will be used for the remainder of the attribute if all positions are not defined.
In a Picture the following characters have special meaning:
x: any character
X: any character, except the lower case letters are turned into capitals
s: any alphabetical character or a space
S: as for “s” with automatic capitalisation
a: any alphabetical character
A: as for “a” with automatic capitalisation
9: any digit
0: any digit or space
Any other character is placed into the field and cannot be changed, eg. the picture for a stock number that is one capital letter followed by three digits a slash and another digit would have the picture “PA999/9”. If the field is longer than the picture, that last character of the picture is assumed for the rest of the field.
telephone, 13 P(99)9999-9999
2.2.4.3 Booleans
This is a special one character attribute. It may contain any capital letter, digit or special character (including spaces). Boolean attributes are specified as a “B” followed by the allowable characters, eg sex could be “BMF” or “BFM”, marital status might be “BSMWD”. The first character is used as the default value.
Example of Booleans Field Type
marital status, 1 BSMWD
2.2.4.4 Dates
A date “D” field is six characters wide and is displayed as eight characters with “.” between the day and month, and month and year, eg. “05.04.79”
Example of “D” Date Field Type
date due, 6 D
A date “DC” field is eight characters wide and is displayed as ten characters with “.” between the day and month, and month and year, eg. “05.04.2001”
Example of “DC” Date Field Type
date due, 8 DC
Dates are stored in reverse and thus maybe used as part of a key.
2.2.4.5 Foreign Keys
A Foreign Key field must have a value that exists as part of a key in another table. Foreign Key fields are specified by an “F” followed by the name of the foreign key table, eg. if “area” is one of the codes in the table “areacode” then this can be specified by “Fareacode”.
Note there can be no spaces before the table name. UNIBASE works out how to find the value by the attribute names, which must be the same in the two tables. The attribute name must also occur as the first part of a key in the “Foreign Key” table.
Example of Foreign Key Field Type
: areacode area, 3 PX description, 30 > *area > > > :customer name, 30 PX address, 30*3 telephone, 13 P(99)9999-9999 area, 3 Fareacode > *name > > >
2.2.4.6 Numbers
Number attributes are specified with an “N” followed by the number of decimal places and an optional default value. Numbers are stored and displayed with commas between every three digits left of the decimal point and this must be taken into account when calculating the field width.
Numbers may also be specified as NF in which case there are no default places after the decimal point and the number is entered in much the same way as other numbers, except that the number of decimal places, the number typed, are not a default value.
qty on hand, 6 N0 price, 5 N2 tax rate, 2 N0 20 qty ordered, 6 NF
2.2.4.7 Dollars
Dollar fields are number fields. Dollar fields are specified by using a ‘$’ as the field type. This is just a shorthand way of specifying N2 as the field type.
Example of Currency Type Fields
price, 5 $
NB This attribute type is deprecated and the recommended usage is N2 (or whatever decimal places your currency requires).
2.2.4.8 Sequences
A sequence attribute is one that is automatically numbered by the UNIBASE system. It is typically used for automatic generation of invoice or order numbers, etc. During add operations any Sequence attribute on the screen will be set to the next sequence number for a file. The starting Sequence number for a table may be set using the UNIBASE program ubmodtab. The Sequence number is padded with ‘0’ characters on the left and will fill the width specified for the field. A Sequence number can also be given to a particular record using ubsetseq.
Example of Sequence Field Type
Order no, 6 S
2.2.4.9 Unions
Unions are not stored attributes.
A Union field consists of a number of parts of other attributes, or complete attributes that are put together to form a new field.
Union attributes may also be composed of strings (text enclosed in “”), dates (~ or @ followed by a date in DD.MM.YY(YY) format), and shell commands (a shell command enclosed in“).
Importantly, Union fields can be used in an association definition.
Example of a Union Type Fields
: stock group, 2 P9 item, 3 P9 supp code, 3 Fsupplier stockcode, 8 U [group][item][supp code] user, 10 U `who am I |awk ‘[print $1]’` valid supp, 4 U “y” [supp code] > *group,item,supp code > > > : stocklist, stock stock code, 8 P9 group, 2 U [stock code:1-2] item, 3 U [stock code:3-5] supp code, 3 U [stock code:6-8] > *stock code > > >
2.2.4.10 Expressions
Expressions are not stored attributes.
An Expression attribute is a numeric attribute whose value is determined by evaluating the given expression. To specify an “Expression” the type of expression (see below) is follow by the number of places after the decimal point and then the expression for the value. As Expression attributes aren’t stored they may be placed anywhere in the attribute list. They may also be removed from the Table Definition at any time. If an Expression depends on another expression, the order in which the Expressions are declared is not important.
Any Attribute Type may be used in and Expression and UNIBASE will try to make use of it in the context of the Expression. For example if you try to add a text attribute to a number attribute, UNIBASE will try to interpret the text as a number. UNIBASE will add days to dates, subtract dates to get days, etc.
UNIBASE expressions are made up of numbers, strings, fields, and operators. Numbers are as you would expect, eg. 1, 2.5, 1000.423. Strings are text enclosed in “”, eg “HELLO”, “015”. Fields are field definitions, eg [stock no][date][price].
Operators maybe arithmetic, comparative, logical or unary (one argument only).
The “S” operator is the substring operator. Its value is true (1) if its left operand contains its right operand.
“ABCXYZ” S “CXY” is true
If [areas] is “R1,R2,C1” then [areas] S “R2” is true (1) while [areas] S “X1” is false (0)
Logical Operators:
& And
| Or
The result of a comparison or logical operation is 0 (false) or 1 (true). In general 0 means false and nonzero means true. The result of a logical and (&) is true if both operands (the things being added) are true, otherwise it is false. The result of a logical or (|) is true if either or both its operands are true, and false if neither is true. The result of comparative and logical operators can be used by the arithmetic operators.
SQRT Square root EXP Exponential (e) LOG Logarithm base e LOG10 Logarithm base 10 SIN, COS, TAN, ASIN, ACOS, ATAN, SINH, COSH, TANH
SIN, COS, etc are Unary operators rather than functions. This means that SQRT 4 is 2 and SQRT [mean] is also valid. If a more complex expression is to be operated on then it should be inclosed in ‘(‘ and ’)’
SQRT ([value] – [mean]) [qty] * [price] ([amount] >= 0) * [amount] ([a] > 50.7) + ([a] > 123.87) [date] + 7 > [system.date]
As a result of comparative and logical operators is 0 or 1 this property can be used to separate values (ie this replaces the more traditional if-then-else language construction), eg ([amount>=0)*[amount], will have the value of [amount], if [amount] is >= 0, or 0 if [amount] is < 0. Because the operator ‘*’ takes precedence over ‘>=’ it is necessary to put parentheses, ‘(‘ and ‘)’ around the comparison.
Operators have precedence, ie some are more important than others and will therefore be evaluated first. If two operators have the same precedence they will be evaluated from left to right, eg 1+2*3 is 7 not 9. To change the order of evaluation brackets can be used eg. (1+2)*3 is 9
Groups of Operators in Order of Precedence
^ *, / +, - <, <=, >=, >, =, !=, S &, |
2.2.4.10.1 Set Operations
The comparative operators (<, <=, >=, >, =, !=) can also be used on arrays.
If the filed definition is:
area, 3*5 PX
“AAA” < [area] is true if “AAA” is less than all the elements of area.
ie. [area 1],[area 2],[area 3],[area 4] and [area 5] Similarly for <=, >=, >
“AAA”=[area] is true if “AAA” is equal to one of the elements of area while “AAA”!=[area] is true if “AAA” is not equal to any of the elements of [area]
2.2.4.10.2 Secondary Tables
When the comparative operators are used on fields from secondary files they will work on the sum of the field (for expressions and numbers) that are not defined. To get a similar result to the set operations requires a trick described:
Define an expression filed in the secondary file whose width can accommodate the maximum number of secondary records you are expecting (typically 2 or 3 characters). The expression should be the comparison required, eg from an order files, if [ordline,qty]>10 is the set operation required, then define a filed:
ordqty, 2 E0 [pty]>10
in the record ‘ordline’. This field will add on for every line where qty >10. From the order file:
[ordline.ordqty]>0
will be true if any line of ordline have qty >10.
2.2.4.10.3 Calculation Optimisation
With some operators, the result of an operation can be known from the value of one of its operands, eg a&b is always false if a is false. In this case UNIBASE will not try to evaluate the right operand (even if it is another expression or involves a join). The operators that take these shortcuts are &,|,*,/
a&b Will skip b if a is false
a|b Will skip b if a is true
a*b Will skip b if a is 0
a/b Will skip b if a is 0
2.2.4.10.4 Normal Expressions
Expressions written with filed type “E” are Normal Expressions. When printing reports or asking for sums of these expressions the sum of each calculation will be printed.
Example of Using Normal Expressions
: stock stock no, 10 P9 description, 30 price, 10 N4 on hand, 5 N0 value on hand, 10 E2 [price]*[on hand] > *stock no > > > : stock H STOCK LIST STOCK NO DESCRIPTION PRICE ON HAND VALUE > R [stock no] [description][price][on hand][value on hand] > T
[value on hand]
>
2.2.4.10.5 Totals
A Total field is the same as an Expression field except that when printing subtotals and totals the expression is recalculated. In the case of Expression fields the subtotals are the sums of the expressions. To specify a total fields use “T” followed by the number of places after the decimal point and then the expression for the value. This type of field is used where a subtotal is an average (for example). The sum of the average is not the same as the average of the sums.
: stock stock no, 10 P9 description, 30 price, 10 N4 on hand, 5 N0 value on hand, 10 E2 [price]*[on hand] av stock price, 10 T4 [value on hand]/[on hand] > *stock no > >
The following report will show the desired result of total stock value on hand and average value of each stock line. Note that it will simply add up the average stock prices we would have the sum of the price column so we need to recalculate the average price when the total is printed by taking the sum of the “value on hand” and dividing by the total number of units – “on hand”
Example of Total Expression Report
: stock
H
STOCK LIST
STOCK NO DESCRIPTION PRICE ON HAND VALUE
>
R
[stock no] [description] [price][on hand] [value on hand]
>
T
[average stock price] [value on hand]
>
2.2.4.10.6 Accumulators
Accumulators are expression fields that add their current value to the expression at every record. They are reset to zero at every subtotal. This gives a running total column. They are defined in the same way as the E and T types above. But using an A as the field type.
Example of an Accumulator Type Field
balance, 15 A2 [amount]
2.2.4.10.7 Julian (date)
Julian expressions return a date as their value. The field width should always be 6. When calculating dates you need to keep in mind the rules for combining dates and days to come up with a date as a result. NB: UNIBASE will stop you doing something meaningless with dates as it doesn’t try to interpret the result as a date until after the calculation has been completed. So here are the rules:
+ A date plus a number of days gives a date. A date plus a date is meaningless
– A date minus a number of days gives a date. A date minus a date gives days
* A date times 0 or 1 gives a date. A date times anything else is meaningless
/ A date divided by 1 gives a date. A date divided by anything else is meaningless
date quoted, 6 D
valid for, 3 N0
date expired, 6 J [date quoted] +[valid for]
2.2.4.10.8 Graphic
Graphic fields are expressions where the result is represented as ‘*’s. To specify a graph field use “G” followed by the units per star, eg is the field width is 20 and the unites per star is 50 then 400 would be represented by 8 ‘*’s followed by 12 spaces.
staff sales, 20 G100 [weekly sales]
2.2.4.10.9 Zero Suppression
Expressions may sometimes be required as ‘zero suppression’ is if the value is 0, nothing (or SPACE to be precise) is printed instead of 0 or 0.00 which would normally be printed. This is particularly helpful when a number must be placed in one of several columns. To make a filed zero suppressed the field type (“E”, “T” or “J”) should be immediately followed by a “Z”.
Julian expressions that are zero suppressed will be printed as a blank date “ . . ”
2.2.4.10.10 Invisible Text
Invisible Text is ordinary text that is displayed on the screen as a series of ‘*’ for security reasons. However, it will appear in reports as the actual text.
The main use for this field type is the entry of passwords or secret codes in ubprompt screens. Eg. ubprompt may be used to input a user name and password before calling a remote (eg mainframe) computer. As the password is not to be displayed, the invisible text type should be used.
Invisible text is indicated by the letter ‘I’.
Example of Invisible Text Field
password, 8 I
2.2.4.10.11 Counters
A counter is a field similar to a sequence number (see section ), except that it is not set automatically by UNIBASE. Counters are numbers, left filled with ‘0’. They may be set to a value, incremented, and decremented. Other operations (such as ‘*’ an d’/’) are not valid.
Counters are indicated by the type ‘C’.
transaction number, 6 C
2.2.5 Help Messages
Lines commencing with ‘#’ immediately following a Field Definition make p the Field Help Message.
Help Messages for fields work in exactly the same way as help messages for files. (see section )
Example of Field Help Messages
: stock
prod number, 10 P9
#PRODUCT NUMBER
#PRODUCT NUMBER
#
#The product number is all digits.
2.3 Keys
A “Key” definition is simply a list of fields separated by commas. There may be as many parts as you like and there may be as many keys defined for any file as you like.
All the fields in a “Key” definition must be stored fields. You cannot use expressions, unions or fields from other files, but you can use sub fields of stored fields.
It is also possible to specify some keys as unique, ie two records cannot have the same value for this key. To designate a key as unique a, the first character of the “Key” definition must be a ‘*’. The unique specification may be added or removed from the Data Dictionary at will. Your application will still run, but without the uniqueness check.
It is also a good idea to specify as unique all keys that you know are unique as this can substantially speed up joins and may protect valuable data during delete and change operations. IMPORTANT – As this can have a wide ranging impact on the accuracy of your data, unique keys should always be declared as such with a ‘*’.
order no
name
area,name
supplier,type,stock code
stock code,1-2,supplier
Example of Unique Key Definitions
*name
*area,name
2.4 Associations
The list of associations tells the UNIBASE programs how to find related information from other tables, eg. from a name table you may want to be able to access orders from the order table and invoices from the invoice table. The common fields between all these tables being a field called customer. The list of associations in the name table would include these tow tables.
The general form of an association is {<assoc type>}<table>{<key def>}where table is the table being joined to or associated with, and there is an optional list of comma separated fields to be used in the association.
2.4.1 Normal Associations
For an association to take place there must be a key in the table being associated that starts with the fields given in the association. UNIBASE uses this information to locate data in other tables.
In a typical relational database a programmer decides on two sets of records (relations) and then asks for them to be joined specifying a common field or fields. UNIBASE on the other hand is the programmer. The association information is the knowledge that UNIBASE uses to decide which set of records to join and when (just as the programmer would).
Similarly, the unique key information is used again as knowledge about how tables relate. It is useful to stop duplicating entries, but it is more important that it can be used to decide system wide updates as the result of updating one table. Understanding this properly can help keep your records very accurate and much easier to maintain. If you don’t understand this strange things will happen (typically records will mysteriously disappear or be changed).
: order
order no, 6 S
customer, 30 Fname
date placed, 6 D
stock no, 10 P9
>
*order no
customer,order no
>
>
>
: name
customer, 30
address, 30*3
>
*name
>
order customer
>
>
To access a field from another file you simply refer to the filename and the field separated by a ‘.’ In the following example the cost of an item is calculated using the price in the stock file.
Example of Getting a Field from Another File
: stock
stock no, 10 P9
description, 30
price, 10 N2
>
*stock no
>
>
>
: order
order no, 6 S
customer no, 30
stock no, 10 Fstock
quantity, 3 N0
value, 10 E2 [stock.price]*[quantity]
>
*order no
>
>
>
In the above example you will notice that we did not specify the association in the Association section of the Table Definition. This is because a foreign key will also define an association and ‘stock no’ is a foreign key to “stock”.
In UNIBASE you specify either a Foreign Key or an Association, but not both. UNIBASE will complain that the table is joined twice if you do.
2.4.2 Fuzzy Associations
In relational databases the join is based on an exact match. However in many instances it is more valuable to join on inexact matched. In the field of Artificial Intelligence, for example UNIBASE combines both joins or associations and fuzzy or inexact joins. Exact joins take place when the join key is made up of non-numeric fields (text, etc.). Fuzzy joins take place when one of more of the fields in the join is numeric (including calculations). The joined record is then the one where the join key is less than or equal to the join value. Fuzzy joins don’t add mathematically to the capability of UNIBASE because any inexact value can be made exact with some conditional expressions. The power of fuzzy joins is that tables of values (coefficients) can be edited by the end user, rather than building calculations into a program. UNIBASE programs can be set up and the fuzzy join used to select table entries for calculation.
For example, let’s consider the case of deducting tax from someone’s earnings. The tax office supplies a table that specifies a formula for calculating tax and a set of parameters to use in the formula for different rates of pay. The general formula is:
tax payable = tax income * a+b
and a table of incomes, a’s and b’s is provided.
Let “taxrate” be a table:
| Taxable income | a | b |
| 0.00 | 0.0000 | 0.0000 |
| 94.00 | 0.2441 | 22.9595 |
| 151.00 | 0.4776 | 53.8523 |
Let “payroll” be a table of employee numbers and taxable incomes:
| Employee# | Taxable income |
| 0123 | 57.00 |
| 0124 | 100.00 |
| 0125 | 94.00 |
| 0126 | 170.00 |
Then let “payroll” fuzzy association to “taxrate” produce a table:
| Employee# | Taxable income | a | b |
| 0123 | 57.00 | 0.0000 | 0.0000 |
| 0124 | 100.00 | 0.2441 | 22.9595 |
| 0125 | 94.00 | 0.2441 | 22.9595 |
| 01256 | 170.00 | 0.4776 | 53.8523 |
UNIBASE does a fuzzy association whenever an expression or number is one of the fields used to associate.
2.4.3 Summation Join
One of the most important reasons 4GLs have interfaces to C, Pascal or some similar built-in language is to do looping. In many cases it is necessary to execute some sort of ‘for’ or ‘while’ loop to calculate something. UNIBASE does not need looping because it uses a new type of join (or more correctly – association) that we have called the summation join.
With normal joins if we take two tables: SUPPLIER (S#,NAME) and ORDERS(S#,PART,VALUE) with values:
0001 ABC PTY LTD
0002 XYZ PTY LTD
and
0001 10100 100.00
0001 10101 120.00
0001 10200 110.00
0002 10101 130.00
and we do a normal join we get a new table:
0001 ABC PTY LTD 10100 100.00
0001 ABC PTY LTD 10101 120.00
0001 ABC PTY LTD 10200 110.00
0002 XYZ PTY LTD 10101 130.00
and then do things like summing the order values we need some sort of loop or explicit summing instruction.
With the summation join we get only tow records in the new table:
0001 ABC PTY LTD 10200 330.00
0002 XYZ PTY LTD 10101 130.00
We get one record for each record in the table SUPPLIER and any numeric (or calculated) fields from the table ORDERS are summed and the sum becomes the value of the attribute in the join record. Any non-numeric fields in ORDERS have the value of the last record in the join. This effectively eliminates looping. It also leads to a more natural expression in UNIBASE screens and reports.
It is most important to not that the summation join is nor commutative, ie SUPPLIER join to ORDERS is not the same as ORDERS joined to SUPPLIER.
2.4.4 Referential Integrity
“Referential Integrity” is a technical term. It means that if a value in the database is changed then all references to that value will also change. In UNIBASE this means that all tables with the same field will have to have that field changed, ie changing one table may mean that many tables will have to be changed.
UNIBASE decides which tables have to be modified by looking at the joins from the table being edited to other tables. If one or more parts of the key used to join the two tables, has changed then UNIBASE will go to the joined table and change the appropriate fields in all joined records. This procedure is recursive (that is, it can do it all itself) in that changing values in one table may mean that values in a table to which the second table is joined may also need to be changed. In the case of a complex database this operation could take a noticeable amount of time. But it is better than doing it manually. Some of the joins will to want to be changed, eg. a join to a code field will not want the code to change whenever the code in a table referencing it changes. UNIBASE will only change values in other tables if the join key in not unique. It is therefore very IMPORTANT that unique keys on code tables, name tables, etc are flagged as unique.
The other side to “Referential Integrity” is deletions. When a record is deleted there are often other records that must be deleted at the same time. In this case it si not always obvious, which should go, eg. joins from an order header to and order line table would normally imply that removing the order heading means removing the order lines. However a join from a debtor table to a journal normally does not imply deleting journal entries just because the debtor name has been deleted.
To solve the problem UNIBASE will only delete records from joined tables where the join is on the primary (ie first) key and the key is not unique. In most cases the records you expect to be deleted will be and the records you don’t want deleted will not be. However it is a good idea to try out deletions in a complex database as a experiment to make sure there are no unwanted side effects before putting the program into production.
Deletion is also recursive so care should be taken in setting unique key indicators to stop records disappearing unexpectedly.
UNIBASE does not support “Referential Integrity” between tables that have no explicit joins.
2.5 Classes
A Class or Subset of a table is defined by applying a set of conditions to restrict the records available from a table. Classes should be used to match the kinds of objects described during the design phase, eg when designing an “out of stock” report a table called “out of stock” is described. It may be all those items for which stock on hand is zero or stock on hand plus on order is zero.
The important thing is that UNIBASE gives you the facility to describe this object “out of stock” so that screens and reports can work with it. The alternative is to put conditions on screen and reports that restrict “out of stock” records. However you are then not dealing with an object “out of stock” rather an object “stock” to which you apply restrictions, Instead, the object “out of stock” will automatically use the correct definition.
Classes are a very powerful tool and if used extensively they will substantially improve your design and reduce your maintenance costs.
Classes should always be applied to Alias Files.
The general form of a class definition is a list of expressions. Each expression must be true (non zero) for a record to be included in a class.
2.5.1 Static Classes
Generally there are two types of classes: static and dynamic. Static classes are ones whose membership doesn’t change when viewed by any user under any conditions. There is no difference in the way static or dynamic class is defined, however it is important to realise the difference as different users at the same time may see different Subsets. “out of stock” as described above is a static class as everyone will see the same set of records.
Example of Static Class Definition
:out of stock,stock
stock no, 10 P9
description, 30
price, 10 N4
qty on hand, 5 N0
>
*stock
>
>
[qty on hand]=0
>
2.5.2 Dynamic Classes
A “Dynamic Class” is one, which many look different to different users or under different conditions, eg “my orders” would be a dynamic class. There is no difference in the definition of static and dynamic classes, however, understanding them will improve your designs.
Example of Dynamic Class Definition
:my orders,orders
order no, 6 S
customer, 30 Fcustomer
date, 6 D
salesman, 5
>
*order no
>
>
[salesman]=[system,user]
>