Проектирование информационных систем

 

На рис. 21 показан пример созданного таким образом запроса  к таблицам Customer и Executed_Contract.

 

 

 

Рис. 21.

 

 

Для создания запроса в  отдельной вкладке используется кнопка «Создать запрос» панели инструментов «Стандарт» или пункт «Создать запрос»  контекстного меню, вызываемого для  выбранной БД. Далее запрос пишется  вручную или создается в Конструкторе запросов (меню Запрос/Создать запрос в редакторе, см. пример на рис.21).

 

После формирования запроса  и нажатия на кнопку «Ok» текст запроса переносится на вкладку. После чего он может быть выполнен, а также сохранен в отдельном файле с расширением sql. Пример показан на рис.22.

 

 

 

Рис. 22.

 

 

Для того чтобы ранее сохраненный  запрос мог быть выполнен, надо открыть  файл с запросом. При открытии файла (меню Файл/Открыть/Файл) устанавливается  соединение с сервером. Для выполнения запроса необходимо указать полные имена используемых в запросе  таблиц (Имя_БД.dbo.Имя_таблицы). Например, furniture.dbo.Customer.

 

Другой вариант указания для какой именно базы данных предназначены SQL-команды - использование команды  use < имя базы данных>, которая пишется до других SQL-команд. (В этом случае префикс dbo не указывается.)

 

 

Запрос с группровкой и агрегатными функциями

 

 

Для включения в запрос секции GROUP BY в конструкторе запросов в контекстном меню «Области схемы» выбирается пункт «Добавить группу по». При этом в «Области условий» добавляется столбец «Группировка». Выбор агрегатной функции для  требуемого поля происходит из выпадающего  списка (см. рис. 23).

 

 

 

Рис. 23.

 

 

Сформированный запрос и  результат его выполнения показан  на рис.24.

 

 

 

Рис. 24.

 

 

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

 

.Вызвать контекстное меню  «Области запроса» и выбрать  команду «Отправить результат  в ? в файл» (см. рис. 25);

 

.Вызвать контекстное меню  «Области результата» и выбрать  команду «Сохранить результаты  как…».

 

 

 

Рис. 25.

 

 

Запросы на изменение данных

 

 

Существуют три вида запросов, изменяющих содержимое таблиц базы данных:

 

·запрос на добавление (insert);

 

·запрос на обновление (update);

 

·запрос на удаление (delete).

 

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

 

Для этого в Конструкторе запросов после добавления таблицы  Current_Contract и выбора необходимых полей в контекстном меню «Области схемы» выбирается пункт «Изменить тип» ? «Вставить результаты». Затем из списка таблиц выбираем таблицу-адресат «Executed_Contract» и добавляем фильтр для поля Cur_End_Data (см. рис. 26).

 

 

 

Рис. 26.

 

 

Записи, добавленные в  таблицу Executed_Contract, должны быть удалены из таблицы Current_Contract. Для этого аналогичным образом создатется запрос на удаление.Server использует расширенную версию языка SQL, которая называется Transact-SQL, или T-SQL. T-SQL позволяет создавать пакеты из нескольких запросов, которые последовательно выполняются на сервере и их результаты вместе возвращаются клиенту.

 

Команда GO определяет, когда  пакет передается на сервер. Пример пакета из указанных выше запросов на вставку и удаление показан  на рис. 27.

 

 

Рис. 27.

 

 

Вложенные запросы

 

 

Пример. Найти заказчиков, имеющих максимальное количество заказов.

 

Вначале создается запрос, возвращающий количество заказов, имеющихся  у каждого заказчика. (Рис. 28)

 

 

 

Рис. 28.

 

 

Далее этот запрос используется в секции FROM запроса, определяющего  максимальное количество заказов у  одного заказчика

 

 

 

Рис. 29.

 

Окончательный вид запроса, дающего ответ на вопрос, сформулированный в примере, представлен на рис. 30. Здесь запрос, показанный на рис.29, используется в секции WHERE при определении условия  для выбора фамилии заказчика  с максимальным количеством заказов; при этом запрос на подсчет количества заказов у каждого заказчика  используется дважды.

 

 

 

Рис. 30.

 

 

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

 

Выражения в секции COMPUTE должны совпадать с выражениями в  списке выборки команды SELECT и в  этой секции нельзя использовать псевдонимы столбцов.

 

 

 

Рис. 31.

 

 

В запросе на рис.32 используются три агрегатные функции, вычисляющие  количество заказов, общую стоимость  заказов и среднюю стоимость  одного заказа для каждого клиента. При этом используется информация как  о текущих заказах, находящаяся  в таблице с именем Current_Contract, так и о выполненных ранее заказах из таблицы с именем Executed_Contract. Для этого в секции FROM помещена команда UNION, объединяющая в один набор данных результаты двух отдельных запросов к указанным выше таблицам.

 

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

 

 

 

Рис. 32.

 

Пользовательские функции  и процедуры

 

 

Кроме отдельных SQL-запросов СУБД MS SQL Server позволяет использовать для обработки данных и такие средства как системные и пользовательские хранимые процедуры, триггеры, пользовательские функции.

 

 

Пользовательские функции

 

 

Пользуясь средствами языка T-SQL, пользователь может создавать  собственные функции, которые будут  сохраняться в его базе данных. Эти функции затем могут вызываться в командах SELECT и других выражениях языка T-SQL. Функции могут иметь  входные параметры любого типа кроме  text, image, table.

 

Существует три типа пользовательских функций. Функция типа inline возвращает в качестве результата значение типа table и тело этой функции должно состоять из одной команды SELECT. Рассмотрим создание и вызов функции такого типа на примере вычисления стоимости выполненных заказов для заказчика с данной фамилией и инициалами. (Имена заказчиков находятся в столбце Cus_name таблицы Customer, а стоимости в столбце Exec_cost таблицы Executed_Contract.)

 

Создание функции происходит при выполнении команды Create Function. Для получения текста этой команды в обозревателе объектов последовательно выбирается база данных, для нее пункты Программирование, Функции, Функции, возвращающие табличное значение. Для последнего пункта вызывается контекстное меню, содержащее команду «Создать встроенную функцию, возвращающую табличное значение…». Эта команда выводит на экран окно с шаблоном команды Create Function, в которой должен быть размещен код создаваемой функции. После удаления из шаблона комментариев и вставки необходимых команд и конкретных имен получаем код команды создания пользовательской функции типа inline на языке T-SQL, представленный на рис. 33. (При этом для создания запроса SELECT, который находится в теле функции, был использован редактор запросов.)

 

Пользовательская функция  добавляется в базу данных после  запуска команды создания функции  на выполнение. Имя функции появляется в обозревателе объектов после его  обновления.

 

 

 

Рис. 33.

 

 

После создания пользовательская функция может быть использована, например, следующим образом. В обозревателе объектов для имени этой функции  вызывается контекстное меню, в котором  находится команда «создать сценарий для функции». Затем через подменю  происходит конкретизация команды ? «используя SELECT» ? «В новом окне редактора  запросов». В результате на экране появляется окно с кодом команды SELECT, которая  в нашем примере после замены входного параметра функции summa на конкретное значение Быков О.О. получает вид, показанный на рис. 34.

 

Результат выполнения этого  запроса показан в нижней части  рис. 34.

 

 

 

Рис. 34.

 

 

По существу, созданная  в рассмотренном примере пользовательская функция в приведенном выше варианте ее использования, может быть интерпретирована как «Запрос с параметром»  в терминологии СУБД Access.

 

 

Скалярные функции

 

 

 

Рис. 35.

 

 

 

Рис. 36.

 

 

Хранимые процедуры

 

 

Хранимая процедура - это  последовательность компилированных  операторов T-SQL, хранящихся в системной  базе данных SQL Server. Хранимые процедуры предварительно откомпилированы, поэтому эффективность их выполнения выше, чем у обычных запросов. После первого выполнения компилированный план процедуры хранится в быстродействующем кэше в оперативной памяти, что существенно повышает скорость выполнения запросов. Другое их преимущество состоит в том, что пользователь может получить право выполнения хранимой процедуры, даже если он не имеет права доступа к тем объектам, к которым обращается процедура.

 

Хранимая процедура создается  с помощью оператора CREATE PROCEDURE. Для  создания хранимой процедуры соответствующей  командой контекстного меню открывается  шаблон кода, в котором задаются имя, параметры и SQL-код тела процедуры. Затем созданный код запускается  на выполнения для проверки синтаксиса и компиляции.

 

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

 

После обновления в обозревателе объектов добавляется ярлык созданной  процедуры.

 

 

 

Рис. 37.

 

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

 

В первом случае появляется окно с таблицей параметров процедуры, в котором задаются значения входных  параметров (в рассматриваемом примере  значение параметра @type=стол, рис. 38).

 

 

 

Рис. 38.

 

 

После этого появляется код  сценария выполнения процедуры, запуск которого дает результат (рис.39).

 

 

 

Рис. 39.

 

 

Триггеры

 

 

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

 

По умолчанию все триггеры (INSERT, DELETE и UPDATE) срабатывают после  выполнения оператора изменения  данных. Эти триггеры называются триггерами AFTER (после),. Кроме того в SQL Server используются триггеры INSTEAD OF (вместо), которые выполняются вместо оператора предполагаемого изменения данных.

 

Создание триггера, аналогично пользовательским функциям и процедурам, можно осуществить на основе шаблона, вызываемого контекстным меню. На рис. 40 приведен пример триггера, созданного для контроля бизнес-правила: «нельзя  удалять сведения о выполненном  заказе, если его стоимость превышает 100».

 

 

 

Рис. 40.

 

 

При попытке удалить информацию из таблицы Executed_Contract о выполненном заказе на сумму, превышающую 100, появляется окно-сообщение (см. рис. 41).

 

 

 

Рис. 41.

 

Заключение

 

 

Описанные выше и проиллюстрированные  действия по созданию макета информационной системы представляют собой только начальный этап разработки полноценной  ИС. При этом информационная модель системы служит основой для проектирования схемы базы данных, а функциональная модель определяет набор SQL-запросов и  процедур.

 

Следует отметить, что описанных  выше возможностей недостаточно для  разработки ИС, ориентированной на конечного пользователя. Во-первых, такая ИС должна обладать удобным  для конечного пользователя интерфейсом  и максимально облегчать выполнение некоторых рутинных операций (ввод исходных данных, формирование отчетов  и др.). Во-вторых, необходимую для  конечного пользователя функциональность трудно реализовать в полном объеме в рамках языка SQL-запросов.

 

Поэтому для разработки прикладной части ИС используют такие современные  системы программирования, как Delphi, Visual Studio, обладающие большими наборами компонент для создания пользовательских интерфейсов и библиотеками для реализации необходимой функциональности.

 

 

Приложение

 

 

Требования к проекту  по курсу «Информационные системы»

 

Разработка прототипа  ИС в среде SQL Server 2005

 

1.Анализ предметной области  с учетом ПО-информации и гипотетической  ПП-информации и построение концептуальных моделей:

 

·создать информационную модель в виде ER-диаграммы;

 

·создать функциональная модель в виде набора DF-диаграмм, со словарем данных и миниспецификацией одного из процессов.

 

.Построение логической  модели ИС:

 

·описать структуру реляционных  таблиц;

 

·проанализировать функциональные зависимости между атрибутами отношений ;

 

·привести отношения к  третьей нормальной форме.

 

.Реализация прототипа  ИС в SQL Server 2005:

 

·создать структуру и  заполнить данными таблицы БД средствами SQL Server Management Studio Express;

 

·создать диаграмму БД;

 

·создать несколько SQL-запросов (с использованием агрегатных функций; сортировка; удаление, добавление данных и т.д.);

 

·разработать несколько  пользовательских функций (возвращающих табличное значение, скалярное значение).