CC BY
83
№ 3(21) 2009 В. Г. Мокрозуб
Синтаксис запросов конечных пользователей к реляционной базе данных
Основой многих современных информационных систем, применяемых в различных прикладных областях, являются реляционные или постреляционные базы данных, задачей которых является хранение данных и их обработка по запросам пользователей. В статье представлен способ организации диалога и запросов конечных пользователей к реляционным базам данных на понятном им языке. Описана последовательность интерпретации строки запроса пользователя в синтаксисе SQL.
Основным языком программирования современных реляционных баз данных (РБД) является язык структурированных запросов — Structured Query Language, SQL. При этом программист должен, по возможности, учесть все пожелания конечных пользователей по обработке данных и получению отчетов этой обработки. При появлении новых запросов часто необходимо вносить добавления в существующие программы, так как пользователь не знает SQL, а программные продукты генерации отчетов (например, Crystal Reports) являются средствами программиста, а не конечного пользователя, так как требуют не только знания основ программирования, но и знания структуры базы данных.
Целью проведенного исследования являлась разработка простого синтаксиса запросов конечных пользователей и интерпретация этих запросов в синтаксис SQL. В найденных автором публикациях, посвященных этой теме, в большей степени описывается [1], что надо сделать для того, чтобы конечный пользователь мог формировать запросы к базе данных на понятном ему языке, и в меньшей степени [2], — как это сделать с минимальными затратами на программирование.
Переходя к дальнейшим рассуждениям, разделим РБД на два класса в зависимости от структуры:
• традиционные базы данных или базы данных с плоскими таблицами;
• базы данных типа ОБЪЕКТ—АТРИБУТ— ЗНАЧЕНИЕ.
Под традиционными РБД будем понимать базы данных, в которых сущности представлены широкими таблицами. Например, информационная сущность «Сдачи цехом продукции на склад» характеризуется атрибутами: Продукт, Количество, Цех_изготовитель, Дата_из-готовления, Дата_сдачи и др. Тогда в РБД будет существовать отношение (таблица), в которой с учетом нормализации представлены все перечисленные атрибуты (поля).
Базы данных типа ОБЪЕКТ—АТРИБУТ— ЗНАЧЕНИЕ представлены тремя основными таблицами: Реестр объектов, Реестр атрибутов; Значения атрибутов объектов. Последняя таблица имеет связи с первыми двумя по типу «многие к одному».
Организация диалога в традиционных базах данных
Представим нормализованную РБД БЭТ в следующем виде:
БЭТ = {К,,..., И,,..., И,}, , = У, И; = {РК,, РК !, й1},
РК , = {РК,.....РК,.....РК и}, ] = й,
Э, = {Э,,.....й,к.....Э,к }, к = 1, К,
где И, — ,-е отношение (таблица);
I — число отношений в базе данных;
РК, — ключевой атрибут ,-го отношения;
РК, — множество атрибутов для внешних
ключей ,-го отношения;
95
№3(21)2009
I
<и
>а о
£
0 а
sr
1
Se >а
1
i «о о
S3 §
с ■о
if ы
х
§
«о
53
о &
J— число атрибутов внешних ключей; D/ — множество атрибутов данных /-го отношения;
K— число атрибутов данных (индекс / у J и K для упрощения записи не указан).
Обозначим W =Wu...,Wt ,...,WT, t = 1/T,
i
Wt € U D/ — множество атрибутов, по которым
могут осуществляться запросы или выводиться отчеты. Под элементарным условием p запроса будем понимать запись:
p=< a, f, z >,
где a — атрибут a € W;
z — множество значений атрибута; f — условие (равно—не равно, больше— меньше и т. д.), например,
< Город = 'Москва' > или < Дата= '01.01.2008' >.
Запрос пользователя S представим как совокупность элементарных запросов, соединенных логическими операторами И, ИЛИ, НЕ:
S = F(pi,...,pm,...,рм), m = 1,M,
где F — функция, связывающая элементарные условия запроса;
m — номер элементарного условия запроса; M — число элементарныхусловий запроса, например, S=р1 И(р2 ИЛИ р3)И(НЕр4).
Отчет запроса компонуется по элементам множества B, составленного из элементов множества W:
B = {B1, B2};
B1 = b1,...,ы,...,b]},Ы € W,с = IC;
B2 = {ф 1 (Ь2).....ф, (Ь2).....ф L (Ь2)},
b2 £ W, Ь2 £ B1, I = й,
где B1 — множество атрибутов данных, по которым составляется отчет запроса, без применения к ним агрегатных функций (Sum, Мах и др.);
b21, l = 1,L — множество атрибутов данных, по которым составляется отчет с применением агрегатных функций; Фl, l = 1,L — агрегатные функции, применяемые к элементам b21.
В1 = {Продукт, Город}, В2 = ^ит(Количество)}.
Итак, формирование запроса пользователя и получение результата разбивается на этапы ввода:
• элементарных условий
{р1,..., pm,..., Рм}, m=1, M;
• строки запроса
S = F(p1,...,pm,...,pm), m = 1,M;
• полей отчета
B1 = b1,...,bj},...,b\, b]c € W, с=1C;
• полей отчета
B2 = {ф 1 (b2).....ф I (bf).....ф L (b2)},
b2 € W, b2 £ B1, I = 1Д;
• выполнение запроса и получение отчета. Для преобразования строки запроса S
в формат SQL элементарные запросы, сформированные пользователем в виде < a, f,z >, представляются в виде < aiD, f,zID>, где aiD — наименование поля первичного ключа для a; ziD — множество значений поля первичного ключа для множества значений атрибута z. Например, элементарное условие:
< Город = 'Москва' >
в программной реализации необходимо заменить на
< ЮГород = ЮМосква >,
где ЮМосква — значение ключевого поля для значения «Москва».
В то же время элементарное условие < Дата > 01.01.2008 > остается без изменения. В первом случае условие для ID, во втором случае —условие для значения. Преобразованную таким образом строку запроса S назовем SI.
Для организации диалога с пользователем при вводе элементарных условий необходимо обеспечить выбор атрибута a и ввод (выбор) множества значений атрибута z. Для выбора a создадим таблицу атрибутов запросов G = {GID, A, AID}.
Значениями (доменом) поля Aявляется множество W={W,..., Wt,..., WT}. Домен поля AID —
96
№3(21)2009
наименования ключевых полей для W={W1,...,Wt,..., WT}, если элементарное условие для ID или наименование поля, если элементарное условие для значения.
Элементарные условия сохраняются в таблице Y{Имя_условия, A, AID, f,Z, ZID}.
Создадим V{PK1,..., PK,, ..., PKn ,W,..., Wt,..., WT} — представление (View), по которому составляется отчет. Тогда запрос на SQL можно записать как:
select B from V where SI group by B1 order by B1.
Рассмотрим разработку программы диалога на примере отгрузки готовой продукции потребителям. Каждая единица готовой продукции изготавливается определенным цехом. База данных представляется как:
BDT = {R1, R2, R3, R 4, R5},
где R1 (IDR1, ЮПокупатель, ЮПродукт, Количество, Дата_отгрузки) — отгрузка продукции; R2 (ЮПродукт, ЮЦех, Наименование_про-дукта) — продукты;
R3 (ЮПокупатель, ЮГород, Наименование_ покупателя) — покупатели; R4(IDГород, Наименование_города) — города;
R5 (IDЦех, Наименование_цеха) — цеха.
Таким образом, отгрузка характеризуется шестью атрибутами: Покупатель, Продукт Количество, Дата отгрузки, Цех-производитель, Город.
Конечный пользователь может сформулировать запрос на получение отчета по любой комбинации атрибутов, например, отчет по отгрузке продуктов, выпущенных цехами 1,3,10, в Москву, Липецк, Тамбов в январе 2008 и 2009 годов. Форма отчетов по этому запросу может быть разнообразной, например, Продукт-Количество, Город-Продукт-Количество, Продукт-Город-Количество.
Множество W = = {Наименование_покупателя, Наименование_продукта, Наименовнаие_города, Наименование_цеха, Дата_отгрузки, Количество}.
Для рассматриваемого примера таблица атрибутов запросов 6 выглядит следующим образом (табл. 1).
Таблица 1
Таблица атрибутов запросовG
IDG Наименование покупателя AID
1 IDПокупатель
2 Наименование_продукта ЮПродукт
3 Наименование_цеха ЮЦех
4 Наименование_города ЮГород
5 Дата_отгрузки Дата_отгрузки
б Количество Количество
3
IT
3=
Представление, по которому составляется отчет, V{PK1,...,PK,,...,PKn, W1,..., Wt,..., WT} = = IDR1, ЮПокупатель, Наименование_покупате-ля, ЮПродукт, Наименование_продукта, ЮГород, Наименование_города, Ю_цех, Наименова-ние_цеха, Дата_отгрузки, Количество} получается в результате запроса:
select IDR1, ЮПокупатель,
Наименование_покупателя, ЮПродукт, Наименование_продукта, IDГород,
Наименование_города, ID_цех, Наименование_цеха, Дата_отгрузки, Количество from R1,R2,R3,R4,R5,
where R1 .ЮПокупатель= R3 .IDПокупатель and R1 .ЮПродукт= R2 .IDПродукт and R2 .ЮЦех= R5 .IDЦех and R3 .IDГород=R 4 .IDГород.
Строка запроса пользователя представляет собой логическое выражение из элементарных условий. Например, запрос «Отгрузка продуктов, выпущенных цехами 1,3,10, (ЮЦех — 2, 5, 8 соответственно) в города Москва, Липецк, Тамбов (ЮГород — 10,11,12 соответственно) в январе 2008 года и январе 2009 года» будет выглядеть так:
S = F(Р, ,Р2,Рз , Р4, Р5,Рб) = = p И Р2 И (Р3 И Р4 ИЛИ Р5 И Рб),
где Р! — цех = (цех1, цех 3, цех 10);
97
№3(21)2009
Таблица 2
Таблица (отношение) Y
Имя условия A AID f Z ZID
1 2 3 4 5 6
p1 Наименование_цеха ЮЦех = цеха: 1, 3,10 2,5,8
p2 Наименование_города ЮГород = Москва, Липецк, Тамбов 10,11,12
p3 Дата_отгрузки Дата_отгрузки > 01.01.2008 01.01.2008
p 4 Дата_отгрузки Дата_отгрузки < 31.01.2008 31.01.2008
p5 Дата_отгрузки Дата_отгрузки > 01.01.2009 01.01.2009
p6 Дата_отгрузки Дата_отгрузки < 31.01.2009 31.01.2009
p2 — Город = (Москва, Липецк, Тамбов), p3 — Дата_отгрузки > 01.01.2008; p4 — Дата_отгрузки < 31.01.2008 p5 — Дата_отгрузки > 01.01.2009; p6 — Дата_отгрузки < 31.01.2009.
Ввод каждого элементарного условия < a, f,z > состоит из следующих действий:
• выбор атрибута a из таблицы атрибутов запросов G;
• назначение условия f;
^ • ввод значения или множества значений z.
| При этом создается табл. 2, столбцы 1,2,4,5
<§ которой представляются пользователю.
S Далее пользователь вводит строку условий
>§ запроса S = p1 И p2 И (p3 И p4 ИЛИ p5 И p6).
£ Предполагается, что элементарные логиче-
ir ские выражения он составлять умеет. Практика
g показала, что достаточно получасового обуче-
* ния пользователей любого уровня знаний.
й Поля отчета (поля представления V) также вы-
g бираются пользователем из таблицы атрибутов
§ запросов G. В результате создаются множества: 2
| B = {Наименование_продукта,
g Наименование_цеха, Бит(Количество)}, if
g B1 = {Наименование_продукта,
* Наименование_цеха}, 53
g, В2 = {Бит(Количество)}. §
2 Строка условий запроса Sна основании таб-
5е лицы Y (табл. 2) преобразуется в следующее:
5 SI = p/1 and p/2 and (p/3 and p/4 or p/5 and pi6),
где pi1 — Юцех in zi1, zi1 = (2,5, 8);
pi2 — ЮГород in zi2, zi2 = (10,11,12); pi3 —Дата_отгрузки > zi3, zi3 = 01.01.2008; pi4 — Дата_отгрузки < zi4, zi4 = 31.01.2008; pi5 — Дата_отгрузки > zi5, zi5 = 01.01.2009; pi6 — Дата_отгрузки < zi6, zi6 = 31.01.2009.
В результате формируем запрос: select B from V where SI group by B1 order by B1
Организация диалога в базах данных типа ОБЪЕКТ—АТРИБУТ—ЗНАЧЕНИЕ АТРИБУТА
Примерами баз данных с подобной структурой являются: база технического оборудования предприятия, справочник свойств веществ и др. Запрос к базе данных заключается в поиске объектов, атрибуты которых удовлетворяют заданным условиям. Например, в базе оборудования предприятия найти все емкостные аппараты, у которых имеется якорная мешалка и объем которых больше 10 м3. База данных BDO в этом случае может быть представлена как:
BDO={O, S, SO, SZ},
где O(IDO, Наименование_объекта) — объекты; SW(IDSW, Наименование_атрибута) — атрибуты;
SO(IDSO, IDO, IDSW, Значение_атрибута, IDSZ) — значения атрибутов конкретных объектов; SZ(IDSZ,IDSW, Возможное_значение) — список значений атрибутов.
98
№3(21)2009
Отношение Б1 вводится для тех атрибутов, значения которых выбираются из определенного списка, например, тип мешалки аппарата выбирается из списка: лопастная, турбинная, рамная и др.
Таблица атрибутов запросов здесь присутствует в явном виде, SW, и ее не надо создавать, как это было ранее. Также не надо создавать представление V, так как его роль отведена таблицам О и SO.
Последовательность составления запроса пользователем и его обработку рассмотрим на следующем примере. В базе оборудования завода найти аппараты объемом менее 10 м3 с турбинной мешалкой.
SI=pi1 and pi2,
где р/п — exists (select * from SO where O. IDO=SO. IDO and SO. IDS = 5 and SO. IDSZ = 15); p/2 — exists (select * from SO where O. IDO=SO. IDO and SO. IDS = 7 and SO. Значение_атри-бута <10).
Представить программно элементарные условия р/ не составляет особого труда, так как они содержат постоянную часть
«exists (select * from SO where O. IDO=SO.IDO and»
и переменную, которая легко выводится из табл. 3.
IT
3=
Таблица 3
Таблица (отношение) Y
Имя A AID f Z ZID
условия ($.Наименование_атрибута) (S. IDS) ^.Возможное_значение (SZ.IDS
или константа) или Null)
1 2 3 4 5 6
Pi Тип мешалки 5 = Турбинная 15
P2 Объем аппарата м3 7 < 10 NULL
Запрос пользователя S будет состоять из двух элементарных условий, соединенных логическим И: «объем аппарата меньше 10 м3» И «тип мешалки — турбинная».
Ввод каждого элементарного условия < а, f,г > состоит из следующих действий:
• выбор атрибута а из таблицы атрибутов SW;
• назначение условияf;
• ввод значения г или выбор его из таблиц Б7.
При этом создается отношение /(табл.3), столбцы 1, 2, 4, 5 которой предоставляются пользователю для составления запроса.
Далее пользователь составляет запрос: S=Р1 И Р2,
где р1 —Тип мешалки=турбинная; р2 — Объем аппарата < 10.
Строка условий запроса на основании таблицы / (табл. 3) преобразуется в строку:
Таким образом, запрос на поиск объектов, удовлетворяющих SI, можно записать в виде:
select * from O where SI.
Эффективность методики показала трехлетняя эксплуатация корпоративной информационной системы ОАО «Пигмент», построенной с использованием предлагаемого подхода.
СПИСОК ЛИТЕРАТУРЫ
1. Биряльцев Е. В., ГусенковА.М. Онтологии реляционных баз данных. лингвистический аспект / Компьютерная лингвистика и интеллектуальные технологии: Труды международной конференции «Диалог 2007» (Бекасово, 30 мая — 3 июня 2007 г.). М.: Изд-во РГГУ, 2007.
2. Курбатов С. С. Автоматизированное построение естественно-языкового интерфейса для реляционных баз данных / Новости искусственного интеллекта / Анахарсис. 2002. № 2 .
99
