3.4 Содержание раздела «Проектирование запросов на языке sql»

В результате выполнения курсового проекта требуется построить запросы на языке SQL, указанные в варианте задания. Учащимися формулируются запросы к базе данных. Приводится их естественно-языковое описание и представление в формате SQL с результатами выполнения.

Могут быть построены следующие виды запросов:

• запросы на выборку, включая параметрические;

• запросы на выборку с группировкой;

• запросы на создание таблиц и удаление записей из таблиц;

• запросы для переноса информации за предыдущие периоды в архив;

• перекрестные запросы;

• запросы, определяющие права доступа пользователей к данным.

Пример:

Основными запросами в курсовом проекте являются:

а) запрос, который применяется для состава меню:

Листинг 1 – Запрос, определяющий состав меню

SELECT Goods.gname, Me_G.mid AS Me_G_mid, Me_G.gid AS Me_G_gid, Menu.mid AS Menu_mid, Menu.mdate

FROM Menu INNER JOIN (Goods INNER JOIN Me_G ON Goods.gid = Me_G.gid) ON Menu.mid = Me_G.mid

WHERE (((Menu.mdate)=[Forms]![CreateMenu]![mdate]));

б) запрос на проверку занятости места:

Листинг 2 – Запрос проверяющий занятость места

SELECT Count(Zakaz.timed) AS Количество

FROM Zakaz INNER JOIN PL_Zak ON Zakaz.zid = PL_Zak.zid

GROUP BY Zakaz.zdate, PL_Zak.pid

HAVING (((Count(Zakaz.timed))=[Forms]![Zakaz]![ПолеСоСписком21]) AND ((Zakaz.zdate)=[Forms]![Zakaz]![zdate]) AND ((PL_Zak.pid)=[Forms]![Zakaz]![ПолеСоСписком24]));

в) перекрестный запрос, для отображения принадлежности мест к столам:

Листинг 3 – Перекрестный запрос отображающий принадлежность мест к столу.

TRANSFORM Count(T_P.kol) AS [Count-kol]

SELECT T_P.Tables.tid, Count(T_P.kol) AS [Итоговое значение kol]

FROM T_P

GROUP BY T_P.Tables.tid

PIVOT T_P.pid;

Далее в курсовом проекте описываются и обосновываются представления, хранимые процедуры, триггеры.

На второй стадии курсового проектирования выполняется разработка программы, реализующей автоматизированные функции пользователей и дополнительные функции администратора для обслуживания БД.

Разработка программы предполагает последовательное выполнение этапов технического и рабочего проектирования.

На этапе технического проектирования разрабатываются алгоритмы и принимаются решения по функциям, структуре, интерфейсам и защите программного обеспечения. В процессе технического проектирования программы, предназначенной для работы с базой данных, решаются следующие задачи.

1. Производится классификация и распределение автоматизируемых функций по пунктам меню главного диалогового окна пользователя. В один класс следует включать функции, объединяемые общей логикой применения или общими используемыми объектами.

2. Выделяются наиболее употребительные функции, которые целесообразно представить кнопками в общих для многих экранных форм панелях инструментов.

3. Выбираются функции, для эффективной реализации которых целесообразно использовать хранимые процедуры. Для сложных функций разрабатываются алгоритмы, в необходимых случаях представляемые блок-схемами и текстовыми описаниями.

4. Решаются вопросы динамики (изменения объема) БД. Определяются условия и режим (автоматически или пользователем) архивирования неактуальных данных. Создается перечень функций, необходимых для архивирования базы и работы с архивом.

5. Вводятся дополнительные функции, обеспечивающие защиту данных от разрушения при программных и аппаратных отказах, путем автоматического или вызываемого пользователем копирования информации на резервный носитель, а также восстановления БД с копии.

6. В системах для многих пользователей определяются полномочия (права) пользователей по работе с функциями и данными. Устанавливается необходимый уровень изоляции транзакций, выполняющих функции и хранимые процедуры. В необходимых случаях предусматриваются индивидуальные блокировки данных в операторах транзакций.

7. Решается вопрос защиты программы от постороннего доступа и выбирается комплекс мер по организации защиты программы и отдельных функций.

8. Определяется тип диалога в виде одно- или многодокументального интерфейса для функций пользователя. Разрабатываются экранные формы для организации диалога при выполнении функций и структура создаваемых программой документов (отчетов, справок, писем и т.д.). Создание макетов экранных форм и отчетов следует выполнять средствами визуального программирования, если они имеются в выбранном инструменте разработки, совмещая этим этапы технического и рабочего программирования.

9. Разрабатываются описания экранных форм и контекстных подсказок, подключаемых в качестве оперативной помощи (Help) пользователю.

10. Продумывается способ установки программной системы на компьютер пользователя и ее переустановки в случае отказа компьютера.

На этапе рабочего проектирования создается структура программной системы. Структура программы должна строиться по модульному принципу так, чтобы самостоятельные функции были реализованы отдельными модулями или методами объектно-ориентированного программирования и допускали автономные изменения. Функции распределяются по программным модулям.

Определяется способ вызова (запуска) головной программы пользователем. При этом необходимо, чтобы приложение, изменяющее среду операционной системы и СУБД, восстанавливало ее после окончания работы.

Программирование начинается с создания пользовательского меню и продолжается последовательной разработкой, подключением и отладкой отдельных функций и хранимых процедур, начиная с функций ввода и редактирования данных.

Программы, реализующие функции пользователя, должны в начале контролировать выполнимость функции (наличие всех данных, готовность устройств и т.д.) и сообщать о недостающих ресурсах, а в процессе выполнения перехватывать исключения среды программирования и СУБД, заменяя их понятными пользователю сообщениями.

Для создания приложения могут быть использованы любые технологии работы с базами данных и средства программирования. Приложение может быть создано и по WEB технологии с ориентацией на Internet Explorer 6.0. При программировании следует максимально использовать инструментальные средства выбранной системы разработки приложений. Выбирая среду разработки надо иметь в виду, что использованные средства должны быть доступны при защите проекта в компьютерном классе.

Решения, принятые на этапах технического и рабочего проектирования, подробно отражаются в разделе «Реализация законченного приложения, работающего с созданной базой данных» пояснительной записки. В разделе должна быть обоснована и описана организация диалогов пользователя и структура программы, приведена спецификация программных модулей (экранных форм, отчетов, подпрограмм и т.д.) с указанием имени, назначения, параметров, способов и мест вызова. Для модулей, реализующих логические и вычислительные задачи, приводятся блок-схемы и тексты программ или хранимых в базе процедур.

Далее подробно рассмотрим оформление в пояснительной записке описание процедур, триггеров, функций, ограничений.

Разработка хранимых процедур (Stored Procedure)

Хранимая процедура представляет собой подпрограмму, расположенную на сервере и вызываемую из приложения клиента. Использование таких процедур увеличивает скорость доступа к базе данных по следующим причинам:

• вместо текста запроса, который может быть достаточно длинным, серверу передается по сети относительно короткое обращение к хранимое процедуре;

• хранимая процедура, в отличии от запроса, не требует предварительной синтаксической проверки;

Пример описания хранимых процедур

Выполненные хранимые процедуры можно разделить на две группы: те, которые используются только в администраторской части программы, те, которые используются только в клиентской части.

1. Разработка процедур для администраторской части программы

В администраторской части приложения используется одна хранимая процедура:

COPY_SOURCE_IN_NEW_NABOR

Данная хранимая процедура предназначена для копирования справочника «Источники финансовых средств» из одного набора данных в другой.

2. Разработка процедур для клиентской части программы

В клиентской части приложения используются следующие хранимые процедуры:

OSTATOK_PLAN

DELETE_FROM_DEPARTAMENT

DELETE_FROM_DEPARTAMENTITEMS

DELETE_FROM_KLASSIFICATION

DELETE_FROM_KLASSIFICATIONITEMS

DELETE_FROM_KLASSIFICATIONITEMSMATERIAL

DELETE_FROM_SOURCE

Хранимая процедура OSTATOK_PLAN предназначена для подсчета остатка запланированных денежных средств, при вводе данных во время составления годового плана государственных закупок.

Хранимая процедура DELETE_FROM_DEPARTAMENT позволяет удалить запись из справочника подразделений, предварительно удалив связные данные из всех таблиц.

Хранимая процедура DELETE_FROM_DEPARTAMENTITEMS позволяет удалить запись из справочника отделов, предварительно удалив связные данные из всех таблиц.

Хранимая процедура DELETE_FROM_KLASSIFICATION позволяет удалить запись из справочника статей расходов, предварительно удалив связные данные из всех таблиц.

Хранимая процедура DELETE_FROM_KLASSIFICATIONITEMS позволяет удалить запись из справочника подстатей расходов, предварительно удалив связные данные из всех таблиц.

Хранимая процедура DELETE_FROM_KLASSIFICATIONITEMSMATERIAL позволяет удалить запись из справочника предметов закупок, предварительно удалив связные данные из всех таблиц.

Хранимая процедура DELETE_FROM_SOURCE позволяет удалить запись из справочника источников финансовых средств, предварительно удалив связные данные из всех таблиц.

Листинг 4 – Хранимая процедура DELETE_FROM_DEPARTAMENT.

CREATE procedure dbo.[DELETE_FROM_DEPARTAMENT] (@Setid int,@msg varchar(150))

as

DECLARE @id_dep int

SET @id_dep=(Select id From Departament where NameDep=@msg and SetId=@Setid)

Delete From RPlanGod where Setid=@setid and id_dep_dep in (Select id From DepartamentItems where Setid=@Setid and Masid=@id_dep)

Delete From TotalRPlan where Setid=@setid and id_dep_dep in (Select id From DepartamentItems where Setid=@Setid and Masid=@id_dep)

Delete From RPlan where Setid=@setid and id_dep_dep in (Select id From DepartamentItems where Setid=@Setid and Masid=@id_dep)

Delete From DepartamentItems where Masid=@id_dep

Delete From Departament where id=@id_dep

Разработка представлений (View)

Представление (View) является логической виртуальной таблицей, записи в которую отобраны с помощью инструкции SELECT. Преимущество просмотра заключается в том, что можно один раз отобрать записи и использовать их в дальнейшем, без повторного выполнения инструкции SELECT. Это выгодно при частом выполнении одинаковых запросов, особенно тех, в которых реализованы сложные условия отбора.

Пример описания представлений(View)

Автоматизированная информационная система «Формирование годового плана государственных закупок» для построения отчетов использует следующие разработанные представления:

VIEW_FOR_GodPlan

VIEW_FOR_GodPlan_Kazna

VIEW_FOR_GodPlan_NoKazna

VIEW_FOR_izmenen

VIEW_Ful_totalPlan

VIEW_full_plan

VIEW_GodPlan_Svon_podrazd

VIEW_klassification_for_otchet_izmenen

VIEW_klassification_for_otchet_rplan

VIEW_klassificationItems_for_izmenen

VIEW_klassificationItems_for_otchet_rplan

VIEW_LplanGog

VIEW_Material

VIEW_otchet_GodPlan_Svod

View_Source

Например запрос VIEW_GodPlan_Svon_podrazd позволяет уменьшить нагрузку на приложение при формировании отчетов см. листинг 2.

Листинг 5 – Запрос VIEW_GodPlan_Svon_podrazd.

CREATE VIEW dbo.VIEW_GodPlan_Svon_podrazd

AS

SELECT TOP 100 PERCENT dbo.KlassificationItemsMaterial.Name, dbo.KlassificationItemsMaterial.Edinica, dbo.KlassificationItemsMaterial.Summa,

#temp1.kol, #temp1.kol * dbo.KlassificationItemsMaterial.Summa AS TotalSum, #temp1.Quarter1, #temp1.Quarter2, #temp1.Quarter3, #temp1.Quarter4,

dbo.Source.Source, dbo.Source.Kazna, #temp1.material_id, #temp1.Setid, dbo.KlassificationItemsMaterial.Summa AS Summed,

dbo.DepartamentItems.Otdel,dbo.DepartamentItems.id

FROM (SELECT material_id, Setid, id_dep_dep, SUM(kol) AS kol, SUM(Quarter1) AS Quarter1, SUM(Quarter2) AS Quarter2, SUM(Quarter3) AS Quarter3,

SUM(Quarter4) AS Quarter4, id_source

FROM (SELECT material_id, kol, Quarter1, Quarter2, Quarter3, Quarter4, Setid, id_user, id_dep_dep, id_source

FROM RplanGod) #temp

GROUP BY material_id, Setid, id_dep_dep, id_source) #temp1 INNER JOIN

dbo.KlassificationItemsMaterial ON dbo.KlassificationItemsMaterial.id = #temp1.material_id INNER JOIN

dbo.Source ON #temp1.id_source = dbo.Source.id INNER JOIN

dbo.DepartamentItems ON #temp1.id_dep_dep = dbo.DepartamentItems.id

Where kol<>0

ORDER BY dbo.KlassificationItemsMaterial.Name,dbo.DepartamentItems.Otdel

Разработка триггеров

Триггер представляет собой процедуру, которая находится на сервере базы данных и вызывается автоматически при модификации записей. В отличие от хранимых процедур, триггеры нельзя вызывать из приложения клиента, а также передавать им параметры и получать результат.

Пример описания триггеров

В разрабатываемой базе данных используются следующие триггеры:

Для таблиц Rplan, Lplan, TotalRPlan, TotalLplan разработан триггер удаления записей, если в поле Summ пользователь заносит значение равное нулю. Такая запись никак не влияет на расчеты, производимые приложением клиентом, а только занимает лишнее место.

Для таблиц RplanGod, LplanGod разработан триггер удаления записей имеющих значения равные нулю. Удаление записи производиться во время добавления либо редактирования записи, если поля [kol], [Quarter1], [Quarter2], [Quarter3], [Quarter4] содержат значения равные нулю.

Для таблиц Rplan, Lplan разработан триггер, с помощью которого автоматически после добавления новой записи, либо редактирования уже существующей, пользователем, пересчитывается итоговая сумма, которая затем записывается в таблицу TotalRplan и таблицу TotalLplan соответственно.

Разработка функций, определяемых пользователем (User Defined Functions)

Функция, определяемая пользователем, представляет собой обычную функцию, написанную на языке транзакций SQL. С помощью функций, определяемых пользователем можно вернуть таблицу, что нельзя сделать с помощью хранимых процедур.

Пример описания функций

В разрабатываемой базе данных используется одна функция:

function Check_Ostatok_Quarter

Функция предназначена для вывода значений остатков предметов закупок, уже введенных в базу данных, для дальнейшей проверки на возможность проведения операции со знаком минус.

Обеспечение целостности (Check Constraints)

На этапе физического проектирования должна решаться такая серьезная проблема, как обеспечение безошибочности и точности информации, хранящейся в БД. Это называется обеспечением целостности базы данных.

Обеспечением целостности базы данных называется система мер, направленных на поддержание правильности данных в базе в любой момент времени.

В СУБД целостность данных обеспечивается набором специальных предложений, называемых ограничениями целостности.

Ограничения целостности — это набор определенных правил, которые устанавливают допустимость данных и связей между ними. Ограничения целостности в большинстве случаев определяются особенностями предметной области. Ограничения целостности могут относиться к разным объектам БД: атрибутам (полям), записям, отношениям, связям между ними и т. п. Для полей могут использоваться следующие виды ограничений.

1) Тип и формат поля автоматически допускают ввод только данных определенного типа. Выбор типа поля Date в формате dd.mm.yy позволит пользователю ввести только шесть чисел. При этом первая пара цифр не сможет превысить в лучшем случае значения 31, а вторая — 12.

2) Задание диапазона значений, как правило, используется для числовых полей. Диапазон допустимых значений может быть ограничен с двух сторон (закрытый диапазон), а может с какой-то одной: верхней или нижней (открытый диапазон).

3) Недопустимость пустого поля позволяет избежать появления в БД «ничейных» записей, в которых пропущены какие-либо обязательные атрибуты.

4) Задание списка значений позволяет избежать излишнего разнообразия данных, если его можно ограничить.

5) Проверка на уникальность значения какого-то поля позволяет избежать записей-дубликатов.

Целостность данных должна быть обеспечена для поддержки внутреннего логического соответствия данных. В современных распределенных базах данных поддержка целостности обеспечивается как на уровне клиентского приложения, так и централизованно, при этом логика поддержания целостности хранится в самой базе данных.

Декларативная целостность обеспечивается путем указания в описании данных ограничений целостности и отношений между таблицами. Ограничения целостности могут быть представлены как ограничения значений – диапазон, шаблон значений, значение из определенного множества. Отношения между таблицами могут задаваться путем указания первичных ключей и ключей связи. СУБД сохраняет определенные декларативно свойства данных и отношения между таблицами в процессе ввода данных, их обновления и удаления.

Существуют три типа декларативной целостности данных:

– целостность сущности (таблицы) требует, чтобы все записи в таблице имели уникальный идентификатор – первичный ключ,

– целостность столбцов – ограничения на данные, которые могут быть занесены в столбец,

– ссылочная целостность – это связь между таблицами, обеспечиваемая системой первичный – внешний ключ. Запись в родительской таблице не может быть удалена, а первичный ключ изменен, если в дочерней таблице существует связанная запись. Нельзя ввести в дочернюю таблицу внешний ключ, отсутствующий в родительской таблице.

Процедурная целостность данных обеспечивается с помощью триггеров и хранимых процедур. Триггеры запускаются автоматически при изменении или удалении данных, целостность которых обеспечивается триггерами.

Пример описания обеспечения целостности

Как описывалось выше, в таблицах RplanGod и LplanGod хранятся данные о годовом плане государственных закупок. В полях [Quarter1], [Quarter2], [Quarter3], [Quarter4] хранится информация о количестве предметов закупок в разрезе кварталов. В поле [Kol] хранится информация о количестве за год. Естественно сумма значений по кварталам должна равняться значению за год в целом.

Обеспечение такой целости данных для этих таблиц полностью возложено на СУБД Microsoft SQL Server, по следующей формуле:

[Kol] = [Quarter1] + [Quarter2] + [Quarter3] + [Quarter4]