CC BY
38
DIALOGUE ORGANIZATION WITH USERS IN INFORMATION SYSTEMS OF PRODUCTION MANAGEMENT
V.G. Mokrozub, Yu.V. Nemtinova, S.V. Morozov
Department "Automated Designing of Technological Equipment",
TSTU; mokr@mail.gaps.tstu.ru
Represented by a Member of the Editorial Board Professor V.I. Konovalov
Key words and phrases: information system; relational database; user’s query; output format.
Abstract: The article considers dialogue organization between users and information systems based on relational databases, using programming language understandable to users. It also describes the means of conversion of a query line to operators of SQL language.
Introduction
Modern management systems contain great volumes of information which is processed and provided in certain format on users’ queries. At the process of information systems design it is impossible to predict all possible combinations of future queries. Hence, an information system should allow users to make queries using understandable language and to provide results in a format defined by user.
Nowadays the most often used model of information systems is the relational model. The basic programming language of modern relational databases (RDB) is Structured Query Language (SQL).
The purpose of this article is developing of simple queries syntax and their conversion to SQL syntax. The publications on this topic mainly describe what has to be done to formulate users’ queries using understandable language and to lesser extend what has to be done to minimize programming burden [1, 2].
For the purpose of our discussion we divide RDB into two classes depending on their structure:
- traditional databases or databases with flat tables;
- databases of OBJECT-ATTRIBUTE-VALUE type.
By traditional RDB we will indicate databases where entities are presented by wide tables. For example, the information entity "Product delivery to a warehouse by production unit" is characterized by attributes: "Product", "Quantity",
"Production_Unit", "Date_of_manufacturing", "Delivery_date", etc. Here RDB is represented by relation (table) where all listed attributes (fields) are presented.
Databases of OBJECT-ATTRIBUTE-VALUE type are represented by three basic tables: "Register of objects", "Register of attributes", "Values of objects’ attributes". Last table has connections with the first two of “many to one” type.
1. Dialogue Organization in Traditional Databases
Let's present normalized RDB BDT as follows:
BDT = {R1,..., R,,..., R7 }, i = 17;
R, = {PK,, FK, , D, };
FKi = {FK,i,..,FKy,...,FKj }, j = 1J;
D, = {D,i,..., Dik ,...DK }, k = 1K;
where R, - i-th relation (table); 7 - number of relations in a database; PK, - key attribute of i-th relation; FK, - set of attributes for external keys of i-th relation; J -number of attributes for external keys; Di - set of data attributes of i-th relation; K -number of data attributes (indices i, J and K are set aside).
____ j
Lets put W = {W1,...,Wt,..., WT}, t = 1,T, Wt e UD, - set of attributes, which can
i
be used to make a query or for data output. By elementary condition of a query p we will understand the notion p =< a, f, z > , where a - attribute a e W , z - set of attribute values, f - condition (equal, not equal, greater, lesser, etc.), for example < City = = 'Moscow' > or < Data = '01.01.2008' >.
User’s inquiry S is represented by a set of elementary queries, which are connected by logic operators AND, OR, NOT:
S = F(p1,..., pm,..., pm ), m = 1, M;
where F - function which connects elementary conditions of queries; M - number of elementary conditions; for example S = p1 AND (p2 OR p^) AND (NOT p4).
The output of a query is composed by elements of set B, which consists of elements of set W:
B = {B1, B2};
B1 = {b1,...^,...,bC}, b1c eW, c = 1C;
B2 = {cp^2),..., 9/ (b/2),..., (bj2)}, bl eW, bf * B1, l = U;
where B1 - set of data attributes, which are components of output, and where such aggregate functions as (Sum, Max and others) are not applicable; b2t , l = 1,L - set of data attributes, which are components of output, and where aggregate functions are applicable; p/ , l = 1, L - aggregate functions, which are applicable to elements bf . For
example 51={Product, City}, B2 ={Sum(Amount)}.
The process of making a query and obtain its results is divided into the following steps:
- input of elementary conditions, {p1,..., po,..., po };
- input of a query line, S = F(p1,..., pm,..., pM), m = 1, M;
- input of output fields B1 = {bj,...b^,...,bC}, blc e W, c = 1,C;
- input of output fields B2 = {p1(b12),...,pi(b2),...,pL(bj?)}, bf e W, bf e B1,
l = 1L;
- execution of a query and obtaining an output.
To convert a query line S into SQL format elementary queries formulated as
< a, f, z > , are represented as < ajD, f, zjD > , where ajD - name of primary key for a; zJD - set of primary key values field for set of attributes z. For example, elementary condition < City = 'Moscow' > in programming has to be changed for < IDCity = = IDMoscow >, where IDMoscow - value of key field of value 'Moscow'. At the same time elementary condition < Data > 01.01.2008 > remains unchanged. In the first case we name the elementary condition as ‘condition for 7D’, in the second case as ‘condition for value’. Converted query line Sis named S7 .
In order to organize the dialogue with users at elementary conditions input it is necessary to provide the choice of attribute a and input (choice) of attribute values z . For a we create an attribute table G = {G7D, A, AID}. Values (domains) of the field A are given bye set W = {W1,...,Wt,...,Wt}. Domain of the field AID are names of key fields for W = {W1,..., Wt,..., Wt }, if it is an elementary condition for 7D or names if it is an elementary conditions for value. Elementary conditions are kept in the table Y {Condition _ name, A, AID, f, Z, Z7D}.
Let’s put V{PK1,...,PK,,...,PKn,W1,...,Wt,...,WT } - representation (View), by which output is composed. Then a query on SQL can be written as:
select B from V where S7 group by B1 order by B1.
Let’s examine the program dialogue on the example of products’ delivery to consumers. Each product is produced by defined production unit. The database is represented as
BDT = {R1, R2, R3, R4, R5},
where R1 (IDR1, IDConsumer, IDProduct, Quantity, Delivery_date) - delivery of a product; R2 (IDProduct, IDProduction_unit, Product_title) - products; R3 (IDConsumer, IDCity, Consumer_title) - consumers; R4 (IDCity, City_title) - cities; R5 (ID_Production_unit, Production_unit_title) - production floors.
Thus, delivery is characterized by six attributes "Consumer", "Product", "Quantity", "Delivery date", "Production unit", "City".
User can formulate a query to get an output with any combination of attributes, for example, delivery report for products manufactured by unit 1, 3, 10 in Moscow, Lipetsk, Tambow in January 2008 and January 2009. Forms of output can be various, for example, Product-Quantity, City-Product-Quantity, Product-City-Quantity, etc.
Set W ={Consumer_title, Product_title, City_title, Production_unit_title, Delivery_date, Quantity }.
For the given example attribute table of queries G is presented as (Tab. 1).
To get an output V{PK1,...,PKi,..., PKn ,W1,...,Wt,..., WT} = V{IDR1,
IDConsumer, Consumer_title, IDProduct, Product_title, IDCity, City_title, ID_Production_unit, Production_unit Delivery_date, Quantity} the query has to be as follows:
Query attributes G
IDG A AID
1 Consumer title IDConsumer
2 Product title IDProduct
3 Production unit title IDProduction unit
4 City_title IDCity
5 Delivery_date Delivery_date
6 Quantity Quantity
/select IDR1, IDConsumer, Consumer_title, IDProduct, Product_titlc?\ IDCity, City_title, ID_Production_unit, Production_unit_title,
Delivery_date, Quantity from Ri,R2,R3,R4,R5 where Ri .IDConsumer = R3 .IDConsumer and Ri .IDProduct = R2 .IDProduct and R2 .IDProduction_unitr = R5 .IDProduction_unit
and R3 .IDCtiy = R4 .IDCity.
3 4
Query line is represented by logic formulation of elementary conditions. For example, query line “delivery of products manufactured by unit 1, 3, 10, (IDProduction_unit - 2,5,8 respectively) in Moscow, Lipetsk, Tambov (IDCity -10,11,12 respectively), in January 2008 and January 2009” will be represented as follows:
S = F(P1,P2,P3,P4,P5,p6) = P1 AND P2 AND (P3 ANDP4 OR P5 AND p6), where p1 - production unit = (production unit 1, production unit 3, production unit 10); P2 - City = (Moscow, Lipetsk, Tambov); p3 - Delivery_date > 01.01.2008; p4 -Delivery_date < 31.01.2008; P5 - Delivery_date > 01.01.2009; P6 - Delivery_date <
< 31.01.2009.
Input of every elementary condition < a, f, z > consists of the following steps:
- attribute choice a from the table of query attributes G;
- set the condition, f
- input of value or set of values, z.
Together with this we create Tab. 2, columns 1, 2, 4, 5 which are presented to user.
Table 2
Table (Relation) Y
Condition A AID f Z ZID
•—1 <n m 1-n ю Production_flo or_title City_title Delivery _date Delivery _date Delivery _date Delivery _date IDProduction _unit IDCtiy Delivery _date Delivery _date Delivery _date Delivery _date > < > < production unit 1, production floor 3, production floor 10 Moscow, Lipetsk, Tambov 01.01.2008 31.01.2008 01.01.2009 31.01.2009 2, 5, 8 10, 11, 12 01.01.2008 31.01.2008 01.01.2009 31.01.2009
Than user inputs condition query line S = p1 AND p2 AND( p3 AND p4 OR p5 AND p6).
Undoubtedly users have to be able to compose logical expressions. Practical experience shows that half hour training allows any user to get acquainted with this aspect.
Output fields (fields of V representation), are also chosen by users from a table of attributes G. As a result we obtain the following sets: B = {Product_title,
Production_unit_title, sum (Quantity)}, B1 = {Ptoduct_title, Production_unit_title},
B2 = {sum(Quantity)}.
Query condition line S based on table Y (Table 2) is converted into line SI :
SI = pz'1 AND pi2 AND (p/3 AND p/4 OR p/5 AND p^),
where pi1 - IDproduction_unitr in zi1; pi2 - IDCity in zi2 ; pi3 - Delivery_date > > zi3 ; pi4 - Delivery_date < zi4; pi5 - Delivery_date > zi5; pi6 - Delivery_date <
< zi6; zi1 = (2, 5, 8); zi2 = (10, 11, 12); zi3 = 01.01.2008; zi4 = 31.01.2008; zi5 = 01.01.2009; zi6 = 31.01.2009.
As a result we formulate a query:
■-----------------------------------------------------------------
select B from V where SI group by B1 order by B1.
2. Dialogue Organization in Databases of Object-Attribute-Attribuye Value Type
Examples of databases with this type of structure are database of equipment, reference of substances’ properties, etc.
The essence of database query is searching of such objects whose attributes satisfy to required conditions. For example, making a search of capacitive apparatus which have anchor-impeller mixer and volume more than 10 cubic meters in the database of equipment. Database BDO in this case can be presented as
BDO = {O, S, SO, SZ },
where O(IDO, Object_title) - objects; SW (IDSW, Attribute_title) - attributes; SO (IDSO, IDO, IDSW, Attribute_value, IDSZ) - attribute values of the concrete objects; SZ (IDSZ,IDSW, Probable_value) - list of attribute values.
Relation SZ is entered for those attributes, whose values are chosen from a concrete list, for example mixer type is selected form the list: blade, turbine, frame, etc
Table of attributes is presented in explicit form (SW) and it is not necessary to create it as described above. Also there is no need to create representation V, as its role is executed by tables O and SO.
The sequence of making a query and its proceeding will be examined on the following example. Make a search of capacitive apparatus which have turbine mixer and volume less than 10 m3 in the base of equipment.
Query S will consist of two elementary condition connected by logical AND : "volume of apparatus is less than 10 m3." "mixer type - turbine".
Input of each elementary condition < a, f, z > consist of the following steps:
- attribute choice a from attribute table SW;
- set of conditions, f
- input of value z or their choice from the table SZ.
Together with this relation Y (Tab. 3) is created, and its columns 1, 2, 4, 5 are presented to users.
Table (Relation) Y
Condition name A (S.Attribute_name) AID (S.IDS) f Z (SZ.Possible_value or constant) ZID (SZ.IDS or Null)
1 2 3 4 5 6
Pi Mixer type 5 = Turbine 15
P2 Device volume, cubic m. 7 < 10 Null
Than user formulates a query
S = pi AND P2 ,
where Pi - Mixer_type = turbine; P2 - Device volume < 10.
Query condition line based on table Y (See Tab. 3) is converted into line:
SI = pii AND pi2 ,
where pi1 - exists ^select * from SO where O.IDO = SO.IDO and SO.IDS = 5 and SO.IDSZ = 15); pi2 - exists ^select * from SO where O.IDO = SO.IDO and SO.IDS = = 7 and SO.Attribute_value < 10).
It is not complicated to create elementary conditions pi as they have similar part "exists ^select * from SO where O.IDO = SO.IDO and" and variable part, which is composed based on Tab. 3.
Thus a query on object search, which satisfy conditions SI, can be written in the
form:
Fig. 1. Dialogue organization for equipment query setup
Fig. 1 shows a sequence of dialogue organization in the information system of repair management system of public corporation "Pigment" [3], which contain equipment database. Numbers above the arrows 1, 2, ..., 8 signifies sequence of user’s actions.
Conclusion
The described above means of query setup was used by the authors in the frame of corporate information system of public company "Pigment" at sales department for delivery information search and at the plant engineer department for search of equipment with given characteristics. Three years use of this approach has proved its effectiveness. This approach is of universal use and can be implemented in information systems of various purposes.
Работа выполнена в рамках проекта НК-421(2) Федеральной целевой программы «Научные и научно-педагогические кадры инновационной России на 2009-2013 годы», направление «Информатика».
References
1. Биряльцев, Е.В. Онтологии реляционных баз данных. Лингвистический аспект [Электронный ресурс] / Е.В. Биряльцев, А.М. Гусенков // Междунар. конф. по компьютерной лингвистике : тр. междун. конф. «Диалог 2007». - Режим доступа : http://www.dialog-21.ru/dialog2007/materials/html/08.htm. - Загл. с экрана.
2. Курбатов, С.С. Автоматизированное построение естественно-языкового интерфейса для реляционных баз данных / С.С. Курбатов // Новости искусств. интеллекта. - 2002. - № 2. - С. 17-21.
3. Мокрозуб, В.Г. Процедурные и информационно-логические модели планирования выпуска продукции и ремонтов технологического оборудования многоассортиментных производств / В.Г. Мокрозуб, С.Я. Егоров, В.А. Немтинов // Информац. технологии в проектировании и производстве. - 2009. - № 2. -С. 72-76.
Организация диалога с пользователями в информационных системах менеджмента предприятия
В.Г. Мокрозуб, Ю.В. Немтинова, С.В. Морозов
Кафедра «Автоматизированное проектирование технологического оборудования», ГОУВПО «ТГТУ»; mokr@mail.gaps.tstu.ru
Ключевые слова и фразы: запрос пользователя; информационная система; реляционная база данных; формат вывода.
Аннотация: Рассматривается организация диалога пользователей с информационными системами, основанными на реляционных базах данных, на языке, понятном пользователю. Описаны способы преобразования строки запроса пользователя в операторы языка 8рЬ.
Organisation des Dialoges mit den Nutzern in den Informationssytemen des Betriebsmanagementes
Zusammenfassung: Es wird die Organisation des Dialoges der Nutzer mit den Informationssystemen, die sich auf die Relationsdatenbänke stützen, betrachtet. Es sind die Verfahren der Transformation der Zeile der Nutzersanfrage in die Operatoren der SQL-Sprache beschrieben.
Organisation du dialogue avec les usagers dans les systèmes informatiques du management de l’entreprise
Résumé: Est examinée l’organisation du dialogue des usagers avec les systèmes informatiques fondés sur les bases de relation sur la langue compréhensible à l’usager. Sont décrits les moyens de la transformation de la ligne de la demande de l’usager dans l’opérateur de la langue SQL.
Авторы: Мокрозуб Владимир Григорьевич - кандидат технических наук, доцент, профессор кафедры «Автоматизированное проектирование технологического оборудования»; Немтинова Юлия Владимировна - кандидат экономических наук, старший преподаватель кафедры «Автоматизированное проектирование технологического оборудования»; Морозов Сергей Владимирович - магистрант кафедры «Автоматизированное проектирование технологического оборудования», ГОУ ВПО «ТГТУ».
Рецензент: Немтинов Владимир Алексеевич - доктор технических наук, профессор, заведующий кафедрой «Автоматизированное проектирование технологического оборудования», ГОУ ВПО «ТГТУ».
