Содержание
Введение
1. Постановка задачи
2. Реализация поставленной задачи
3. Алгоритм построения запроса
4. Тестирование и пример работы программы
Заключение
Список использованных источников
Введение
Целью данной курсовой работы является разработка программы, способной на основе отношений строить запросы на извлечение информации посредством визуального построителя запросов.
На современном этапе развития информационных технологий, базы данных, а также умение пользователей получать нужную информацию приобретает всё большее значение. Для реляционных баз данных, средством получения данных, удовлетворяющих необходимым условиям, является язык SQL. Однако изучение языка SQL требует дополнительных навыков, знания внутренней структуры связей между таблицами. Хотя, в принципе, пользователь не обязан знать эти вещи.
Исходя из этого, появились различные визуальные построители запросов, которые освободили пользователей от необходимости знать SQL. Пользователь просто указывает необходимые поля, условия и т.д., а программа сама формирует SQL запрос. Это и является целью курсовой работы.
1. Постановка задачи
На сегодняшний день существует много программных продуктов обеспечивающих визуальное построение запросов на выборку данных – MS Access, SQL Builder, Free Sql и т.д. У каждого продукта своя функциональность, они работают с разными форматами данных.
За пример я взял построитель запросов в пакете MS Access.
На выходе программы должен быть запрос SQL на выборку данных, то есть: select … from … where… order by… .
Данные о таблицах базы данных (имена таблиц, атрибуты, первичные и внешние ключи) хранятся в системном каталоге.
Кратко о структуре запроса на выборку.
В разделе select содержаться имена полей, которые должны присутствовать в результирующем отношении. Может присутствовать идентификатор distinct, указывающий на исключение дубликатов из результата.
В разделе from содержаться имена таблиц, которые нужно использовать при построении результата. Одно отношение может указываться в запросе, как разные таблицы, поэтому в программе необходимо предусмотреть поддержку псевдонимов таблиц.
Раздел where позволяет указать условия, которым должны соответствовать кортежи результирующего отношения. Необходимо сделать так, чтобы при наличии внешних ключей, программа автоматически добавляла в запрос строку о равенстве по внешним ключам.
В разделе where можно выделит следующие типы условий, поддерживаемые программой:
Нужна помощь в написании курсовой?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.
1. Соответствие шаблону. Это условие применимо не ко всем типам данных, а лишь к строке и дате. К числу его применять нельзя, других типов данных в реализации системного каталога не предусмотрено. Это также необходимо предусмотреть. Соответствие шаблону проверяется при помощи служебного слова like.
2. Принадлежность диапазону. Это условие применимо ко всем перечисляемым типам данных.
Оформляется следующим образом: between val1 and val2.
3. Сравнение. Сравнивать можно поля с полями и поля со значениями. Сравнению поддаются также все поддерживаемые типы данных (число, строка, дата).
Так как необходимо обеспечить проверку на соответствие типов полей и значений друг с другом, можно принять следующее утверждение: проверить соответствие между собой полей с разными типами программа не должна позволять, а если поле сравнивается с каким либо значением, то это значение далее рассматривается, как значения типа, такого же как у поля.
В программе не реализована проверка значения поля на принадлежность множеству, а также визуальное построение запросов с группировкой (group by) и вложенных подзапросов с различными кванторами.
2. Реализация поставленной задачи
В программе реализовано 3 юнита.
Первый отвечает за формирование целостной структуры запроса.
Второй — за добавление и удаление таблиц из списка используемых для выборки данных, а также за изменение псевдонимов таблиц.
Третий – за назначение условий на поля таблиц.
Для хранения информации о таблицах в более удобном виде созданы следующие типы данных:
TAtrib = record
name,typ: string;
end;
TTables = record
name, psevdo : string;
atribs: array [0..10] of TAtrib;
end;
TArTables = array [0..10] of TTables;
Существует два массива Tables, UseTables: TArTables.
Массив Tables отвечает за хранение информации об имеющихся таблицах. Его инициализация происходит при создании формы.
В поле name нулевого элемента массива содержится информация о количестве таблиц. В поле name (нулевого элемента) вложенного массива атрибутов содержится информация о количестве атрибутов у данной таблицы. Это облегчает поиск данных в массиве.
Инициализация массива происходит в процедуре CreateTableList(var tables:TArTables), где сначала с помощью функции GetListTable (из модуля SysCat) получается список имен таблиц, разделенных символом #13. Они заполняются, потом определяется количество таблиц, а затем для каждой таблицы вызывается функция GetListAttr для получения имен атрибутов (имена которых также по спецификации модуля SysCat разделены символом #13) и GetTypeStr для получения типа атрибута.
Массив UseTables отвечает за используемые при построении результата таблицы, так как мы можем строить запрос лишь по одной таблице, или использовать в запросе одну и ту же таблицу с разными псевдонимами. Этот массив соответствует разделу from запроса. Его содержимое определяется во втором юните.
На главной форме создан список Listbox1, в котором отражаются имена и псевдонимы используемых таблиц. Чтобы добавить таблицу необходимо нажать на кнопку AddTable. При этом управление перейдет во второй модуль.
Здесь мы можем добавить таблицу в массив используемых. В выпадающем списке Combobox1 представлены имена всех доступных таблиц, которые получены из массива Tables, сформированного ранее. Можно указать псевдоним таблицы, однако это не обязательно.
При добавлении таблицы в список используемых, в массиве Tables, по имени находится таблица, и все данные о ней копируются в очередной элемент списка используемых таблиц. При этом псевдоним таблицы проверяется на уникальность. То есть просматривается список используемых таблиц, и если находится таблица с таким же псевдонимом (или когда две одинаковые таблицы не имеют псевдонима), пользователю выдается сообщение и требуется повторный ввод псевдонима до тех пор, пока не будет введен уникальный псевдоним. Также, если при добавлении таблицы с псевдонимом обнаруживается, что в списке используемых таблиц уже существует такая же таблица, но без псевдонима, пользователь также информируется об этом.
При добавлении таблицы (то есть когда мы попали на вторую форму путем нажатия кнопки AddTable), кнопки изменения и удаления недоступны.
Эти функции становятся доступными в режиме редактирования, в который можно перейти, если дважды щелкнуть на названии таблицы в списке ListBox.
В этом режиме можно либо изменить псевдоним, либо удалить таблицу из списка используемых, поэтому выпадающий список всех таблиц заблокирован.
Нужна помощь в написании курсовой?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.
Реализовано две функции поиска таблиц: по имени (findTable_n) и по псевдониму (findTable_p), которые последовательно просматривают переданный массив и возвращают или 0, или индекс найденного элемента. При изменении, поиск таблицы сначала осуществляется по старому псевдониму (если такой был), а если псевдонима не было, то по имени (с условием равенства псевдонима пустой строке). Затем псевдоним заменятся на новый, а также заменяется везде, где это псевдоним (или имя) был использован.
При удалении таблицы из списка используемых производится поиск таблицы, а затем, все последующие элементы просто сдвигаются на один элемент назад, и после этого следует уменьшение количества таблиц в нулевом элементе массива.
Следует отметить, что в списке необходимо хранить только таблицы, действительно используемые при построении, иначе в разделе from появится еще одна таблица и результат будет некорректным.
На главной форме расположены 2 таблицы.
Верхняя — отвечает за первоначальной вывод сведений обо всех таблицах в памяти (из массива Tables). При этом дополнительно используются функции GetForeignKey и IsPrimaryKey из модуля SysCat для определения внешних и первичных ключей соответственно.
Собственно процесс построения запроса происходит во второй таблице.
Каждый столбец таблицы отвечает за одно поле какой-либо таблицы и условия, налагаемые на это поле. Предполагается, что на одно поле может быть наложено только одно условие из перечисленных в 1-ой части.
Каждая строка таблицы соответствует разделу запроса.
В первой строке таблицы указывается имя таблицы (from), во второй – имя поля, в третьей – тип сортировки (order by), в четвертой – вывод на результат (select), в пятой – условие (where).
Для удобства пользователя ячейки таблицы имеют разный вид и функциональность в соответствии от назначения.
В ячейках первых трех строк находятся выпадающие списки ComboBox.
Определен массив Combo: array [1..20,0..2] of TCombobox.
Его инициализация (создание объектов) происходит при создании формы. Каждому объекту передается область соответствующей ему ячейки, родителем назначается главная форма, а владельцем — StringGrid2.
Combo[i,j]:=TComboBox.Create(form1.StringGrid2);
Combo[i,j].BoundsRect := StringGrid2.CellRect(i,j);
Combo[i,j].Top:=combo[i,j].Top + StringGrid2.Top + 2;
Combo[i,j].Left:=combo[i,j].Left + StringGrid2.Left + 2;
Combo[i,j].Visible:=false;
Combo[i,j].Parent:=Form1;
Combo[i,j].Tag:=i;
Combo[i,j].OnChange:=form1.ComboBoxChange;
Нужна помощь в написании курсовой?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.
Процедуры обработки события OnChange всех объектов происходят в одной процедуре ComboBoxChange.
В неё передается параметр sender: TObject, в соответствии с которым определяются координаты объекта в массиве (и в таблице соответственно).
Изменение в объекте Combobox первой строки порождают изменения в содержимом Combobox того же столбца второй строки, то есть в выпадающем списке появляются имена атрибутов таблицы, выбранной выше.
Причем, если у таблицы был псевдоним, то в выпадающем списке будет представлен именно псевдоним, а не имя.
Выпадающий список появляется только после выборы соответствующей ячейки и после выбора имени пропадает, а выбранное значение передается в ячейку Stringgrid, на области которой отображался ComboBox.
В третьей строке таблицы расположены элементы массива Combo, отвечающие за сортировку результирующего отношения и имеют по умолчанию 2 значения: “по возрастанию” и “по убыванию”.
Как и выпадающие списки первой строки, остальные также появляются при нажатии на ячейку и пропадают после выбора значения, передавая его в ячейку Stringgrid.
Элементами четвертой троки таблицы являются объекты типа TCheckBox, собранные в массиве Check: array [1..20] of TCheckBox;
Проставленная в соответствующем столбце галочка будет означать, что данный атрибут отношения, указанный в этом столбце, должен содержаться в результирующем отношении.
Последняя строка отвечает за условие, накладываемое на это поле. Само условие задается на 3-й форме.
На форме находятся 3 переключателя Radiobutton. Каждый из них соответствует одному типу условия. Значения с полей ввода и выпадающих списков считываются в зависимости от выбранного типа условия.
При работе с третьей формой, программа определяет тип атрибута, для которого задается условие.
Если этот тип – число, тогда при попытке сохранить условие на соответствие шаблону, программа выдаст сообщение о несоответствии типов. Необходимо отметить, что программа не содержит средств проверки синтаксической достоверности выражения после like. То есть правила, что % — любая последовательность символов и т.п. остаются на усмотрение пользователя.
При задании условия принадлежности интервалу, если тип атрибута строка, тогда значения границ интервала будут заключены в апострофы в результирующей строке запроса, если нет, тогда будут перенесены в строку в явном виде.
При задании условия сравнения, пользователь выбирает знак (>,<,=, >=, <=) и тип значения, с которым будет сравнивать поле.
Этот тип задается в выпадающем списке с полями “поле” и “значение”.
На форме существуют 2 элемента в одном месте, поле ввода и выпадающий список.
Нужна помощь в написании курсовой?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.
Если для сравнения было выбрано «поле”, то поле ввода станет невидимым, а в выпадающем списке появятся все поля используемых отношений (в формате имя/псевдоним_таблицы . имя_атрибута), с которыми можно сравнить текущий атрибут, то есть все атрибуты, которые по типу соответствуют текущему, для этого просматривается весь массив UseTAbles.
Если для сравнения было выбрано «значение”, тогда видимым станет поле ввода, и введенное значение воспримется в зависимости от типа атрибута в апострофах или без них.
Для редактирования накладываемых на поля условий требуется лишь нажать на соответствующую ячейку Stringgrid. При этом на появившейся форме будут все значения, введенные для этого поля ранее.
Достигается это следующим образом.
Существует лишь один экземпляр формы 3 для всех столбцов. Но определен тип записи, так называемый “образ формы”, который содержит всю необходимую информацию для того, чтобы восстановить исходный вид формы для соответствующего атрибута. Такой информацией является индекс выбранного RadioButton, а также содержание полей всех элементов (полей ввода и выпадающих списков).
TImageForm=record
IndexRadio:integer;
edit1,edit2,edit3,edit4:string;
combo1,combo2,combo3:string;
usl:string;
end;
frm:array[1..20] of TImageForm;
При нажатии кнопки “Оk” в соответствующий элемент массива заносятся текущие данные с формы, а при выборе ячейки таблицы отвечающей за условие, значения из “образа формы” передаются её полям.
Массив frm изначально инициализируется нулями.
В записи TImageForm предусмотрено поле usl, которое не относится непосредственно к внешнему виду формы. В нем хранится условие, которое задается данной формой.
Формирование этой строки происходит в соответствии с выбранным переключателем Radiobutton и составляется из служебных слов (таких как like и between) и значений полей ввода.
3. Алгоритм построения запроса
Итак, когда заданы все условия, выбраны отношения и атрибуты, программа строит сам запрос.
Процесс построения запроса происходит в цикле, в котором последовательно просматриваются все столбцы нижней таблицы.
Однако этому предшествует несколько проверок.
Программа построена таким образом, что если выбраны отношения, атрибуты на вывод, но не задано ни одного условия, это воспринимается как попытка построить внешнее полное соединение (outter full join). Внешнее соединение строится на основе заданных внешних ключей только в том случае, когда задано 2 разные таблицы и ни задано условий. Но если заданы 2 одинаковые таблицы с разными псевдонимами, то строится обычный запрос с разделом where.
Необходимо заметить, что если не будет задано ни одного условия и в таблицах не будет определено внешних ключей, то будет построено декартово произведение двух отношений.
Нужна помощь в написании курсовой?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.
Если все-таки строится обычный запрос, то в цикле, параллельно строятся все разделы запроса.
Итерация цикла происходит следующим образом:
Если в текущем просматриваемом столбце поставлена галка, то этот атрибут добавляется в переменную select : string в формате имя/псевдоним_таблицы . имя_атрибута.
Если третья строка не пуста, то если в зависимости от её значения в переменную orderby : string добавляется имя/псевдоним_таблицы. имя_атрибута и если выбрано упорядочивание по убыанию добавляется служебное слово desc.
Формирование раздела where зависит от типа строящегося запроса, и если строится внешнее соединение, этот раздел начинается с “on”, иначе с “where”. Далее просматривается соответствующий итерации элемент массива frm, в котором содержатся условия, и последовательно добавляются в переменную where , разделенные скобками и словом “and” (в случае с внешним соединением эти элементы просто будут пустыми).
После окончания цикла в раздел where необходимо добавить связи по внешним ключам (процедура AddForCom). Для этого просматривается весь список используемых отношений UseTables, и для каждого её атрибута вызывается функция getforeignkey из модуля SysCat. Но Эта функция вернет имя таблицы и имя атрибута, а если была задействована не одна подобная таблица, то необходим псевдоним, поэтому приходится еще раз просматривать UseTAbles и добавлять в where связи со всеми таблицами с заданным именем, то есть каждый внешний ключ связывается со всеми таблицами, с этим именем.
Формирование раздела from идет отдельно и зависит от того, как прошли проверки на внешнее соединение. В from добавляются все таблицы и псевдонимы UseTAbles. Если строится внешнее соединение, то ожидается, что в этом разделе будет всего два имени (имя_таблицы as псевдоним) разделенных фразой “outer full join”.
Если стоит галка на CheckBox “Исключая дубли”, то к разделу select добавится слово distinct.
После этого все переменные проверяются на пустоту и собираются в одну, в которой и содержится корректно составленный SQL запрос.
4. Тестирование и пример работы программы
Допустим имеется 2 отношения: Сотрудник (Номер_Удостоверения, ФИО, Адрес), и Должности (Номер_сорудника, Должность, зарплата), причем Должности.Номер_сотрудника является внешним ключом для Сотрудник.Номер_удостоверения.
Пусть нам необходимо найти сотрудника, который получает больше остальных среди всех, чье ФИО начинается на “Ъ”.
Для решения нам будет необходимо добавить одну таблицу Сотрудник и 2 таблицы Должности (с разными псевдонимами). Отношению сотрудник псевдоним можно не задавать.
Зададим условие на атрибут Сотрудник.ФИО.
Зададим Условие на t1.зарплата.
Поставим галку исключая дубли.
В результате, получился следующий запрос:
select distinct Сотрудник.ФИО from Сотрудник, Должности as t1, Должности as t2 where (Сотрудник.ФИО like ‘Ъ%’) and (t1.Зарплата>=t2.Зарплата) and (Сотрудник.Номер_удовстоврения=t1.Номер_сотрудника)and (Сотрудник.Номер_удовстоврения=t2.Номер_сотрудника).
Заключение
Таким образом, в ходе проделанной курсовой работы было создана программа, позволяющая создавать запросы на выборку данных без знания SQL. Программа обладает довольно удобным интерфейсом, но не способна строить Вложенные подзапросы и запросы на группировку. Визуальные построители запросов являются конечно очень удобным средством, однако по моему мнению, чтобы строить эффективные, быстродействующие запросы, без знания SQL все равно не обойтись. В итоге проведенной работы можно считать, что поставленные задачи были выполненными, а цель достигнута.
Список использованных источников
1. Дейт К. Дж. Введение в системы баз данных;
2. ПoлУилтoн, ДжoнКoлби, «Ввeдeниe в SQL», 2006г.
3. Хомоненко А.Д. Работа с базами данных в DELPHI, 3-е издание // А.Д. Хомоненко, В.Э. Гофман – СПб.: БХВ-Петербург 2005, – 623 с.
4. Хомоненко А.Д. Базы данных // А.Д. Хомоненко– СПб.: Корона-Принт 2004.
5. Microsoft Access 2002 Шаг за Шагом// — Москва – Эком 2002.
Нужна помощь в написании курсовой?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.