Electronic Journal of Polish Agricultural Universities (EJPAU) founded by all Polish Agriculture Universities presents original papers and review articles relevant to all aspects of agricultural sciences. It is target for persons working both in science and industry,regulatory agencies or teaching in agricultural sector. Covered by IFIS Publishing (Food Science and Technology Abstracts), ELSEVIER Science - Food Science and Technology Program, CAS USA (Chemical Abstracts), CABI Publishing UK and ALPSP (Association of Learned and Professional Society Publisher - full membership). Presented in the Master List of Thomson ISI.
Volume 7
Issue 1
Geodesy and Cartography
Available Online: http://www.ejpau.media.pl/volume7/issue1/geodesy/art-01.html


Dalibor Bartoněk



The measuring in field is the most commonly used activity in various industrial branches. To be the results useful the data must be stored in a suitable database. There is a problem: to use either one of most of commercial database systems or to create the special purpose designed database? This question is discussed and an own approach of the database design is presented in this paper. The goal is the designed database with minimum of memory capacity occupied and the most rapidly information provided. It is supposed that the basic database design rules e.g. to avoid redundant data (normalization), ensure that the relationships among attributes are represented or facilitate the checking of updates for violation of database integrity constraints are respected. A special optimization method for common attributes reduction, for storing strings of variable length and for database reorganization is used. The database model was implemented in the information system for anticorrosive protection of

Key words: Database, object, measured values, quantities, relational model, E-R diagram, entities, common and individual attributes, equivalent classes, overlay table, optimization..


The measuring of quantities on the object in field belongs to common activities in many spheres of individual usage. The gained values serve either to ensuring the run of certain devices or they inform the users e.g. in the frames of prophylactic check up. In order to take effective advantage of these values it is necessary to store it in optimal designed database. There are two possibilities: 1. to use a commercial database software product (dBASE, MS Access Oracle etc.), 2. to create own special purpose database in any programming language (Borland Pascal, Delphi, C etc.). The commercial database systems, however, were designed for general purpose with the wide offer of functions which is connected with 2 main problems:

Having discussed all “pros and cons“ the authors together with a group of potential users decided to make their own solution in Borland Pascal programming language (MS DOS application), later in Borland Delphi (application for Windows). The results were used in the system for anticorrosive protection of gas-pipeline.


Lets ponder about measuring on the objects of various types. On each of them we measure different quantities in different time. The moment of measuring can be:

  1. regularly (periodical e. g. monthly, quarterly, yearly etc.)

  2. irregularly that means the measuring are done at random according to our needs.

The value of the measured quantity is under these hypotheses a function of two variables:

v = f(t, p),        (1)


v is measured value,
t is the type of the object,
p is the period of the measuring.

The value of the measured quantity depends upon the type of the object and the period of the measuring. It means that of every type of the object various quantities depending upon the time of the measuring can be measured. In every period (monthly, yearly etc.) a different kind of quantities on the given type of the object of all types bat at the same time the required measuring plan which is the description of the individual period of the measuring further function relationship (1). There are several basic types of the object. Every object can be either of basic type or the combination of several basic types. This fact should be taken into consideration as well.


For the storage of all data measured in field we have decided to choose the most commonly used the relational database. Relational data model will contain these entities:

  1. Object and sub-objects with the ISA hierarchy,

  2. Period of the measuring,

  3. Measuring (with measured values),

  4. List of quantities representing the equation (1).

Object and period are the strong entity sets, whereas measuring and measured values are the weak entity sets, because they are dependent on the previous entities according to the equation (1). The list of quantities represents the relationship between period and measuring and realizes the function in the equation (1).

The objects attributes from the point of view of belonging to the objects can be divided into 3 classes:

  1. key attributes - Ak,

  2. common attributes for each type of the object - Ac,

  3. individual attributes, which distinguish the individual types of the objects - Ai. These attributes can be from the point of view of data stored length divided into two groups:

E-R diagram of the data model of the measuring in field shows figure 1.

Fig. 1. E-R diagram of the data model of the measuring in field

Ako, Akp and Akq – primary key attributes of the object, period and list of quantities,
Aco – common attributes of the object, Aio1 … Aion – individual attributes of the sub-object No 1, … ,n,
Adm – discriminator of the measuring entity, Adv – discriminator of the measured values entity,
Aom – other attributes of the measuring entity, Aov – other attributes of the measured values entity,
Aop – other attributes of the period entity, Aoq – other attributes of the list of quantities entity,

double rectangles – weak entity set, double ellipses – grouped attributes


The data model on Fig. 1 was transformed into tables of relational database according to the rules described in (Korth, H. F., Silberschatz, A., 1996), (pokorný, J., 1998). The tables are of two types:

As for the static tables, the most complicated problem was with the creation of tables of object and sub-objects (ISA hierarchy). To avoid data redundancy the method of attributes overlay was used. The principle consists in dividing the sub-objects into basic types so that any sub-object can be composed by combination of one or some objects of these basic types. Thus the number of sub-objects is very (approximately by one order) reduced. Then is the overlay table created the structure of his is shown in Fig. 2. Rows in overlay table consists attributes Aio1 – A ion, in columns are basic types of sub-objects. In the individual cell we write either symbol “*” (string date type) or “#” (other date type). Another reduction of the database structure is by creation of overlay table achieved. The process consists in 2 phases:

  1. Deleting all of the columns in the table 1, which have the null values in all own rows. Objects of these types are sufficiency covered by attributes in the basic table of object (common attributes). We obtain a new table.

  2. In the new table we step by step join columns according these rules:

    1. We join these columns that have in his rows the some symbols.

    2. Further we join columns that is different in n symbols only according these criterion:


n is number of attributes, in which are lower-level entity sets Oj and Ok (sub-objects) different,
m is number of attributes, in which are lower-level entity sets Oj and Ok (sub-objects) the same,
ci is length of i. attribute (common for lower-level entity sets Oj and Ok) in Byte,
di is length of i. attribute (in which are lower-level entity sets Oj and Ok different) in Byte,
tOj is estimation of frequency of occurrence lower-level entity sets Oj in database,
tOk is estimation of frequency of occurrence lower-level entity sets Ok in database,

Table 1. Attributes overlay table


















After the reduction process every row in the table 1 must contain at least one character * or # (completely overlay). Columns in the table 1 represent equivalent classes of sub-objects. We can define 2n tables in the database, where n is number of columns in the table 1. N tables are for attributes of string date type (there are a special compression method for string saving used on the internal database level) – see Fig. 3, n tables for attributes of non-string attributes) – see Fig. 4. Internal structure of the database for saving records of objects is in Fig. 2 and. We use two files; first for the basic data e.g. common attributes Aco1, that must have a value (not null), second file contains two groups of attributes: Aco2 (common attributes, that may be null value), Aio, (individual attributes of object type). The field “Info object” uses trigger for record resize. The measuring data are saved also in two files. First file for all attributes of measuring Am = Ako + Adm + Aom, second file for measured values. This file has 3 parts: 1. “info-measuring” serves for trigger to record resize, 2. “bitmap” is sequence of bits (flags) corresponding to measured values. If the flag bit is zero, the correspondent value is null, if it is “1”, the correspondent value is valid. D1 is length of bitmap, D2 is length of array of measured values.

Dynamic table for measured values is created with the help of the table of quantities list part of which is shown in the table 2. This table realizes the dependency in equation (1). The quantity Qi is loaded into the dynamic table of measured quantities if is measured on one of the sub-objects types <T1, … Tm> and in one of the periods <P1, … Pn> written in the row - see table 4.

Fig. 2. Internal structure of object records saved in the file

Table 2. Part of text attributes of sub-object type

Fig. 3. Compression method of saving the text attributes into the file

Fig. 4. Method of saving non-text attributes and measured values into the file

Table 3. Measured quantities

Table 4. Measured quantities


Sub-object types

Periods of measuring











At present the database model was updated to run uder the Windows operating system. The MS Access was used as the base relational database system. The source code of the application is written in Borland Delphi and it is connected with the MS Access by ODBC interface through the data modul. The conception is shown in Fig. 5.

Fig. 5. Conception of the current database solution


The database model was used in the information system for anticorrosive gas-pipeline protection in gas-works in the Czech Republic. This system with the name GAS-ACOR has already been running for more then 10 years and it had been upgraded according to the users needs. First version was made in Borland Pascal for MS-DOS. The last upgrade consisted in introducing module for universal file print in the network environment. This module was created in Borland Delphi. The authors provide the users with help in the form of hot line or personally.

Last year it has been worked on the converting of the GAS-ACOR system from the MS-DOS system into Windows. Source modules are made in Borland Delphi. The testing run of the new system is operated in South-Moravia gasworks in Brno. The introduction of GAS-ACOR into practice will mean a considerable time saving and the increase of the efficiency of the anticorrosive protection.


  1. Korth H.F, Silberschatz, A., (1996), Database System Concepts. Third Edition. McGRAW-HILL.

  2. Pokorný, J., (1998), Databázová abeceda. SCIENCE, Veletiny, [in Czech].

  3. Sedláček, S., Bartoněk, D., (1996), GAS – ACOR. User manual. SHINE, Brno, [in Czech].

Dalibor Bartoněk
Institute of Geodesy, Faculty of Civil Engineering
University of Technology
Veveří 95 662 37 Brno Czech Republic
tel. 541147204, fax: 541147218
e-mail: bartonek.d@fce.vutbr.cz

Responses to this article, comments are invited and should be submitted within three months of the publication of the article. If accepted for publication, they will be published in the chapter headed ‘Discussions’ in each series and hyperlinked to the article.