сервер. Учебнометодическое пособие по выполнению пратических работ тольятти 2018 2 содержание
 Скачать 7.66 Mb.
|
|
Создание хранимой процедуры, обеспечивающей формирование агрегированных данных по поставкам для указанного интервала календарных дат Последовательность действий при создании процедуры аналогична описанной выше. Особенностью создаваемой процедуры является наличие параметров. Текст процедуры приведен на рис. 4.4. Рисунок 4.4 33 Процедуру нужно сохранить и затем запустить для проверки работоспособности. Используемый для этого запрос может иметь вид, приведенный на рис. 4.5 или 4.6. Рисунок 4.5 Рисунок 4.6 Создание хранимой процедуры, которая реализует различные операции модификации данных для таблицы Договоры Такая хранимая процедура должна обеспечить возможность создания нового договора или изменения параметров уже существующего договора или удаления уже существующего договора. Текст такой процедуры может иметь вид, приведенный на рис. 4.7. Процедуру нужно сохранить и затем запустить для проверки работоспособности. На рисунках 4.8 – 4.10 приведены варианты запуска процедуры в режиме создания договора, его модификации и удаления соответственно. При запуске процедуры в режиме модификации или удаления договора нужно четко определить номер договора, для которого выполняются эти операции. После каждого выполнения процедуры необходимо проверять состояние БД. 34 Рисунок 4.7 Рисунок 4.8 Рисунок 4.9 Рисунок 4.10 35 Создание и использование триггеров 1. Создание триггера, контролирующего наличие даты договора на поставку продукции Предположим, что при вводе данных в таблицу Договоры, в которой хранится информация о договорах на поставку продукции, поле ДатаДоговора, в котором хранится дата заключения договора, должно быть обязательно заполнено, причем в том случае, если при вводе нового договора это поле остается незаполненным, в него должна быть автоматически записана текущая дата. Эту задачу можно решить разными средствами, в том числе и с помощью триггера. Для получения доступа к перечню триггеров уровня таблицы нужно в списке таблиц открыть список объектов требуемой таблицы (в данном случае – Договоры) и в этом списке открыть пункт Triggers. В результате появится список триггеров (если триггеры для таблицы уже были созданы ранее). Для создания нового триггера выполним следующие действия. 1. Щелкнуть правой кнопкой мыши по пункту Triggers и в появившемся меню выбрать пункт New Trigger…. В результате будет создан шаблон кода триггера. Введем в него код, представленный на рис. 4.11. Рисунок 4.11 2. Для создания этого триггера нужно нажать кнопку Execute на панели инструментов. В том случае, если запрос выполнен успешно, в окне Messages появится сообщение Command(s) completed successfully. В этом случае окно запроса с текстом триггера можно закрыть, причем запрос сохранять в виде файла не нужно. Триггер должен появиться в списке триггеров таблицы. Если триггер отсутствует в списке, нужно щелкнуть правой кнопкой мыши по пункту Triggers и в появившемся меню выбрать пункт Refresh. 36 3. Для проверки работы триггера нужно добавить новый договор в список договоров. Это можно сделать, например, с помощью запроса, приведенного на рис. 4.12. После успешного выполнения запроса нужно проверить состояние таблицы Договоры. В поле ДатаДоговора записи, соответствующей новому договору должна быть записана текущая календарная дата. Рисунок 4.12 Создание триггера, контролирующего наличие данных о поставщике - юридическом лице В базе данных хранится как общая информация о поставщиках, так и информация, которая относится только к поставщикам – физическим лицам или поставщикам – юридическим лицам. Каждый поставщик может быть или юридическим, или физическим лицом. Это значит, что одновременное наличие данных о поставщике в таблицах ЮридическиеЛица и ФизическиеЛица не допускается с точки зрения требований логики управления бизнесом. Таким образом, возникает необходимость сложного контроля отношений ссылочной целостности. Для решения этой задачи создадим триггер, который при вводе информации в таблицу ФизическиеЛица будет контролировать наличие кода соответствующего поставщика в таблице ЮридическиеЛица и блокировать ввод данных о поставщике как о физическом лице в том случае, если уже имеются данные об этом поставщике как о юридическом лице. Последовательность действий при создании триггера аналогична описанной ранее. Текст триггера приведен на рис. 4.13. После ввода текста, триггер нужно сохранить, выполнив запрос. Затем нужно проверить работоспособность триггера. Для этого попробуем добавить в таблицу ФизическиеЛица данные о поставщике, который уже является юридическим лицом. Это можно сделать, например, с помощью запроса, приведенного на рис. 4.14. Такой запрос должен инициировать выполнение триггера. Это, в частности, выражается в результате выполнения запроса, который приведен на рис. 4.15. Кроме того, контроль состояния данных в таблице ФизическиеЛица должен подтвердить то, что новые данные в этой таблице не появились. 37 Рисунок 4.13 Рисунок 4.14 Рисунок 4.15 Составить отчет по проделанной работе. 38 Практическая работа №5. Создание и использование представлений Представления (view) – это одно из мощных средств языка SQL, предназначенное для реализации механизма подсхем пользователей БД. Представления позволяют скрыть от пользователей схему БД. Они представляют собой хранимые в базе данных запросы, выраженные операторами SELECT_;_для_некоторых_представлений_могут_применяться_операторы_INSERT,_UPDATE_и_DELETE'>SELECT. На базе одних представлений могут быть созданы новые представления, которые наследуют все свойства базовых представлений. Формировать представления могут пользователи с привилегиями SELECT для используемых в представлениях таблиц (базовых таблиц). Для пользователя представления предстают как объекты очень похожие на таблицы данных. Это выражается тем, что: обращение к представлениям осуществляется также как и к таблицам; ко всем представлениям применим оператор SELECT; для некоторых представлений могут применяться операторы INSERT, UPDATE и DELETE. Однако в соответствие со стандартом ANSI SQL/89 в Microsoft SQL Server таблицы данных и представления имеют некоторые различия: запрос, именованный через представление выполняется только в момент обращения к представлению; для представления невозможно определить ограничения целостности и первичный ключ; в операторе SELECT, на базе которого создается представление, нельзя устанавливать сортировку его результатов; не ко всем представлениям могут применяться операторы INSERT, UPDATE и DELETE. Представление может быть модифицировано (т.е. по отношению к нему можно использовать операторы INSERT, UPDATE и DELETE) в том, и только в том случае, если для оператора SELECT, на базе которого создано представление, выполняются каждое из следующих специфических условий: не используется служебное слово DISTINCT; при выполнении запроса данные извлекаются только из таблицы; в списке полей этого оператора отсутствуют арифметические выражения. Элементами списка могут быть только поля базовой таблицы или базового представления. В свою очередь на поля этого представления накладывается такое же ограничение; в запросе не применяются подзапросы; для результирующих данных не определено группирование. Параметр WITH CHECK OPTION используется при создании обновляемых представлений, ключевые слова LOCAL и CASCADED определяют границы проверок в случае, если представление определено с помощью другого представления. LOCAL ограничивает проверки текущим представлением, в то время как CASCADED вызывает проверки для родительских представлений. CASCADED используется по умолчанию. 39 1. Создание представления, позволяющего при просмотре списка договоров видеть название поставщика. Для создания представления следует выполнить следующую последовательность действий. 1. Щелкнуть правой кнопкой мыши по пункту Views и в появившемся меню выбрать пункт New View… 2. В появившемся списке таблиц выбрать таблицы Договоры, Поставщики, ЮридическиеЛица, ФизическиеЛица. Список таблиц закрыть. В результате появится графическое изображение таблиц, используемых в качестве источника данных для представления, и связей между ними (рис. 5.1). Рисунок 5.1 3. Щелкнуть правой кнопкой мыши по связи между таблицами Поставщики и ФизическиеЛица и выбрать пункт Select All Rows from Поставщики. Щелкнуть правой кнопкой мыши по связи между таблицами Поставщики и ЮридическиеЛица и выбрать пункт Select All Rows from Поставщики. В результате связи примут вид, приведенный на рис. 5.2. Рисунок 5.2 40 4. Выбрать поля таблиц, включаемые в результат запроса, поставив отметки для соответствующих полей (рис. 5.2). В результате текст запроса представления будет иметь вид, приведенный на рис. 5.3. Нажав на панели инструментов кнопку Execute SQL, можно получить результат запроса. Этот результат имеет определенный недостаток – данные поставщиков – юридических лиц и физических лиц находятся в разных полях. Этот недостаток можно исправить, изменив текст запроса (рис. 5.4). Рисунок 5.3 Рисунок 5.4 5. Сохранить представление с именем View_1 6. Проверить работу представления, для чего щелкнуть правой кнопкой мыши по имени представления и в появившемся меню выбрать команду «Open View». Проанализировать информацию, которая выводится с помощью представления. Создание обновляемого представления, позволяющего пользователю работать с ограниченными данными о поставщиках Предположим, что для определенных пользователей должна быть доступна не вся общая информация о поставщиках, а только информация о коде и адресе поставщика. При этом пользователь должен иметь возможность видеть данные поставщика как субъекта предпринимательской деятельности (для юр. лиц – наименование, для физ.лиц – фамилия, имя, отчество). При вводе нового поставщика вводится только информация о коде и адресе, а при необходимости корректировки данных пользователь может изменить только адрес поставщика. Последовательность действий при создании этого представления аналогична вышеописанной. Графическое изображение созданного представления приведено на рис. 5.5. Текст запроса представления приведен на рис. 5.6. Созданное представление сохранить с именем View_2. После сохранения проверить работу представления. 41 Рисунок 5.5 Рисунок 5.6 Для добавления данных в таблицу Поставщики с помощью представления нужно выполнить следующую последовательность действий. 1. Создать новый запрос, нажав кнопку New Query на панели инструментов 2. Ввести текст запроса (пример приведен на рис. 5.7). Код поставщика нужно указать с учетом состояния данных в таблице Поставщики. Рисунок 5.7 3. Выполнить запрос. 4. В случае успешного выполнения запроса проверить наличие записи о новом поставщике в таблице Поставщики Составить отчет по проделанной работе. 42 Практическая работа №6. Изучение основ работы со средствами контроля ссылочной целостности данных Перед началом выполнения работы рассмотрим краткий обзор основных положений, связанных с понятием ссылочной целостности данных (Referential Integrity) и особенностями использования механизмов ссылочной целостности. Связывание строк таблиц реляционной БД выполняется с помощью первичного (primary) и внешнего (foreign) ключей. Разработчик БД должен определить правила связывания данных в разных таблицах, выделив в них одно или более полей в качестве первичного или внешнего ключа. Как известно, первичный ключ позволяет однозначно идентифицировать любую строку таблицы. При выборе столбцов, которые будут входить в состав первичного ключа, необходимо следовать требованиям уникальности и минимальности. Следующий шаг в связывании таблиц — определение внешнего ключа. Внешний ключ создается в таблице, поля которой ссылаются на строки главной таблицы. Для каждой строки зависимой таблицы необходимо, чтобы значению внешнего ключа было сопоставлено значение первичного ключа. То есть нельзя вставлять в зависимую таблицу строки со значением внешнего ключа, не определенного в главной таблице. Однако допускается, что значение внешнего ключа в зависимой таблице будет не определено, т. е. внешний ключ будет хранить значение Null. Впоследствии это значение может быть изменено на корректное значение, соответствующее значению внешнего ключа в главной таблице. В отличие от первичного ключа, внешний ключ не должен быть уникальным. То есть в зависимой таблице может существовать множество строк, имеющих одинаковые значения для полей, сконфигурированных в качестве внешнего ключа. При этом разрешается устанавливать дополнительные ограничения целостности на поля, включенные во внешний ключ. Пользователь может установить ограничение целостности UNIQUE и тем самым гарантировать уникальность значений внешнего ключа. После того, как первичный и внешний ключи будут связаны, на данные в зависимой таблице будут наложены ограничения на значения полей, определенных в качестве внешнего ключа. При этом возникает необходимость как-то согласовывать изменения ключевых полей, осуществляемые в главной таблице, со значениями в зависимой таблице. Если не выполнять никаких дополнительных действий, то возможно нарушение целостности данных (в частности, нарушение целостности данных может выражаться в том, что строки в зависимых таблицах окажутся «потерянными», т.е. для них не будет сопоставлена ни одна строка главной таблицы). 43 Во избежание подобных проблем в SQL Server реализованы специальные механизмы, обеспечивающие автоматическую поддержку целостности данных. При попытке изменения (командой UPDATE) значения первичного ключа в главной таблице ядро SQL Server может вести себя следующим образом: - принудительное установление (Relation, Set Null). Когда значение первичного ключа главной таблицы изменяется, то SQL Server автоматически устанавливает значения внешних ключей во всех связанных строках в неопределенное значение (NULL). При этом теряется информация о том, с какой строкой главной таблицы были связаны строки зависимой таблицы. При изменении в главной таблице более одной строки в зависимых таблицах может образоваться несколько наборов строк с неопределенным значением внешнего ключа. Определить, какая строка зависимой таблицы с какой строкой главной таблицы была связана, станет невозможно. Разновидностью данного механизма можно считать механизм Set Default. В этом случае SQL Server автоматически устанавливает значения внешних ключей во всех связанных строках в некоторое заранее заданное значение; - ограничение (Restrict, No Action). В этом режиме SQL Server будет отвергать попытки изменения значения первичного ключа, если в зависимой таблице имеется хоть одна строка, связанная с изменяемой строкой главной таблицы. Изменение разрешается только в том случае, если ни в одной зависимой таблице не имеется ни одной строки, значение внешнего ключа которой совпадает со значением изменяемого первичного ключа. В общем случае, чтобы изменить значение первичного ключа, пользователь должен сам позаботиться о предварительном изменении значений связанных внешних ключей, например, установив их в неопределенное значение (NULL); - каскадирование (Cascading). Это самый удобный и гибкий режим, обеспечивающий автоматическое соблюдение целостности данных. При изменении значения первичного ключа в главной таблице SQL Server 2000 будет автоматически изменять значения всех связанных внешних ключей во всех строках зависимых таблиц. Эти изменения окажутся незаметны для пользователей. Все имеющиеся связи будут автоматически сохранены. Рассмотрены различные варианты поведения системы при попытке изменения значений первичного ключа. Однако необходимо также рассмотреть возможность удаления строк главной таблицы. Если в зависимых таблицах с первичным ключом удаляемой строки не была связана ни одна строка, то проблем нет. Если же такие строки в зависимых таблицах существуют, то необходимо выполнить удаление таким образом, чтобы обеспечить целостность данных. Возможны следующие варианты поведения SQL Server при удалении строк из главной таблицы: 44 - принудительное установление (Relation, Set Null). При удалении первичного ключа SQL Server будет автоматически устанавливать для всех связанных внешних ключей неопределенное значение (NULL). Впоследствии такие строки могут быть удалены вручную или связаны с другим первичным ключом. Разновидностью данного механизма можно считать механизм Set Default. В этом случае SQL Server автоматически устанавливает значения внешних ключей во всех связанных строках в некоторое заранее заданное значение; - ограничение (Restrict, No Action). Перед тем, как станет возможным удаление строки в главной таблице, ни в одной зависимой таблице не должно быть строки, имеющей то же значение внешнего ключа, что и первичный ключ удаляемой строки. Пользователь обязан либо удалить такие строки из зависимой таблицы, либо установить для них значение внешнего ключа в неопределенное значение (NULL), либо связать его с любым другим первичным ключом главной таблицы. - каскадирование (Cascading). В этом режиме система станет автоматически удалять все связанные строки из зависимых таблиц. Этот метод требует от пользователя минимального количества усилий. I. Изучение особенностей работы механизма ссылочной целостности No Action Рассмотрим особенности работы механизма ссылочной целостности No Action на примере отношений между таблицами Поставщики и Договоры, Поставщики и ФизическиеЛица, Поставщики и ЮридическиеЛица. Эти таблицы связаны между собой по полю КодПоставщика. В этой связи таблица Поставщики является родительской, а таблицы Договоры, ЮридическиеЛица, ФизическиеЛица – дочерними. Для изучения особенностей работы механизма ссылочной целостности выполним следующую последовательность действий. 1. В списке таблиц выбрать таблицу Поставщики, щелкнув по ней правой кнопкой мыши. В появившемся меню выбрать пункт Modify. В результате будет получен доступ к редактированию структуры таблиц. 2. Щелкнуть правой кнопкой мыши по любому полю таблицы и в появившемся меню выбрать пункт Relationships… В результате на экране появится окно Foreign Key Relationships (рис. 6.1). 45 Рисунок 6.1 3. Выбрать связь, соответствующую связи между таблицами Поставщики и Договоры. Раскрыв пункт Tables And Columns Specification можно увидеть информацию, показывающую, какие таблицы связаны и какие ключи при этом были использованы. 4. Далее перейдем к рассмотрению механизмов ссылочной целостности, используемых при удалении записей в таблице Поставщики или изменения ключевого значения (т.е. значения поля КодПоставщика). Раскроем пункт INSERT And UPDATE Specification (рис. 6.2). Как видно, механизм ссылочной целостности No Action установлен по умолчанию. Точно также нужно проверить механизм ссылочной целостности для связей между таблицами Поставщики и ФизическиеЛица и Поставщики и ЮридическиеЛица. Окно Foreign Key Relationships нужно закрыть. Также нужно закрыть окно, обеспечивающее доступ к структуре таблицы Поставщики. 46 Рисунок 6.2 5. Открыть таблицу Поставщики в режиме просмотра/редактирования данных. Для этого таблицу нужно выбрать в списке таблиц, щелкнув по ней правой кнопкой мыши и в появившемся меню выбрать пункт Open Table. Аналогично нужно открыть таблицы Договоры, ЮридическиеЛица и ФизическиеЛица. 6. Предположим, что в силу каких-то причин необходимо удалить поставщика с кодом 4. Выбрав соответствующую запись в таблице Поставщики, нажмите правую кнопку мыши и в меню выберите пункт Delete. Затем подтвердите удаление записи. После этого на экран будет выведено окно (рис. 6.3), информирующее пользователя о том, что удаление записи невозможно, т.к. на эту запись ссылаются записи в связанных таблицах. Рисунок 6.3 7. Таким образом, для того, чтобы удалить данного поставщика, нужно предварительно удалить все связанные с ним данные. Для этого нужно удалить 47 соответствующую запись из таблицы ЮридическиеЛица и проверить наличие договоров с этим поставщиком в таблице Договоры. Если такие договоры есть, их тоже нужно удалить (при этом нужно иметь в виду, что может возникнуть необходимость удаления и содержимого этих договоров). После этого нужно попытаться повторить попытку удаления поставщика с кодом 4. Если связанных с ним данных нет, поставщик будет удален. 8. Предположим, что в силу каких-то причин возникла необходимость для поставщика с кодом 5 изменить код на 7. Выбрав соответствующую запись в таблице Поставщики, измените код поставщика с 5 на 7. Затем попытайтесь перейти на предыдущую запись. После этого на экран будет выведено окно (рис. 6.4), информирующее пользователя о том, что изменение данных невозможно, т.к. на этот код ссылаются записи в связанных таблицах. Поскольку договоры с этим поставщиком отсутствуют, ссылка на него есть только в таблице ФизическиеЛица. Удалив эту запись, затем повторите попытку изменения кода поставщика с 5 на 7. Теперь эта операция должна пройти успешно. После этого нужно проверить содержимое таблиц и таблицы закрыть Рисунок 6.4 |