Синтаксис запросов конечных пользователей к реляционной базе данных

№ 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