|
Содержание работы или список заданий
|
1. ЛР № 1.
2. ЛР № 3.
3. ЛР № 4 - задания 1,2,5.
ЛАБОРАТОРНАЯ РАБОТА № 1
СИСТЕМНЫЕ ПРЕДСТАВЛЕНИЯ В БАЗАХ ДАННЫХ
SQL SERVER
В этой лабораторной работе мы познакомимся с некоторыми сис-темными представлениями, позволяющими получить доступ к данным о структуре базы данных SQL Server.
Важнейшей особенностью баз данных является то, что они, кроме пользовательских данных, хранят также и метаданные, описывающие структуры самой базы данных. Один из способов доступа к метаданным - использование системных представлений, которые в SQL Server нахо-дятся в схемах INFORMATION_SCHEMA и sys как пользовательских, так и системных баз данных. Выбирая данные из системного представ-ления, можно получить информацию о базе данных, ее объектах и т.д. (рис.1).
Рисунок 1 – Системные представления и пример их использования
В этой лабораторной мы будем использовать ранее созданную «библиотечную» базу, которая в примерах называется MyLib.
Получение информации о файлах и файловых группах
Для любой базы данных в СУБД SQL Server должен быть создан как минимум один файл журнала и один файл для данных. Но для боль-ших баз данных, как правило, используют больше файлов. Кроме того, файлы данных могут быть организованы в несколько файловых групп. Данные о файлах позволяет получить представление sys.database_files, о файловых группах - sys.filegroups.
Запустите Management Studio и выполните подключение к экземп-ляру SQL Server. По документации SQL Server ознакомьтесь со столбца-ми системных представлений sys.database_files
(https://msdn.microsoft.com/ru-ru/library/ms174397.aspx) и sys.filegroups (https://msdn.microsoft.com/ru-ru/library/ms187782.aspx). Выполните приведенный ниже код (предполагается, что созданная ранее база называется MyLib):
use [MyLib]; go select * from sys.database_files; select * from sys.filegroups;
go
Разберитесь, как организовано хранение этой базы данных. Найди-те аналогичную информацию, используя графический интерфейс SQL Server Management Studio (в свойствах базы данных разделы Файлы (Files) и Файловые группы (FileGroups)).
Задание 1. Напишите запрос, выводящий расположение файла (файлов) журнала на диске. Чтобы отобрать нужные записи, исполь-зуйте условие на столбец type или type_desc.
Задание 2. Напишите запрос, подсчитывающий количество фай-ловых групп вашей базы данных, доступных на чтение и запись.
Получение информации о схемах, таблицах и представлени-ях
Используя системные представления sys.schemas, sys.tables, sys.views, получите сведения о схемах, таблицах и представлениях, оп-ределенных в вашей базе данных.
С помощью приведенного ниже кода создайте новую схему:
CREATE SCHEMA newsch;
Проверьте, что данные о созданной схеме отображаются в пред-ставлении sys.schemas.
Задание 3. В схеме newsch создайте представление vAuthor, со-держащее один столбец, в котором перечислены все авторы, упомяну-тые в таблице Book. Выберите данные из представления (не забудьте, что newsch не является схемой по умолчанию, поэтому надо указывать имя представления вместе с схемой).
Задание 4. Измените определенное ранее представление таким образом, чтобы список авторов выводился без повторений. Снова выбе-рите данные из представления.
Задание 5. Используя рассмотренные ранее системные представ-ления, посчитайте количество представлений в схеме newsch.
ЛАБОРАТОРНАЯ РАБОТА № 3
ОСНОВЫ УПРАВЛЕНИЯ ТРАНЗАКЦИЯМИ В SQL SERVER
В этой лабораторной мы поработаем с транзакциями в СУБД SQL Server. Тема это достаточно обширная, разберем базовые понятия. Задания выполним на использовавшейся в предыдущих работах «библиотечной» базы данных (проще использовать таблицу BookInLib).
Задание 1. Обновите две или более записи одним оператором update, так чтобы одно из изменений не могло быть произведено (транзакция должна быть целиком откачена).
Задание 2. Оформите те же по сути изменения в виде отдельных операторов, проверьте их работу.
Задание 3. Измените данные так, чтобы они были в первоначальном стоянии (в графическом интерфейсе или написав соответствующий код); оформите последовательность из одного оператора update и одного оператора insert в виде именованной явной транзакции, по-отдельности эти операторы должны выполняться успешно; транзакцию завершите откатом, проверьте результат.
Задание 4. Между операторами update и insert кода транзакции, созданной на предыдущем шаге, поставьте оператор select, выбирающий все записи из соответствующей таблицы; после отката транзакции выполните аналогичный select; убедитесь, что внутренний select «видит» производимые изменения, т.е. внутри транзакции эти данные доступны.
Задание 5. Дополните транзакцию точкой сохранения, установленной после внутреннего select; выполните откат до точки сохранения (в ROLLBACK TRAN указываете имя точки сохранения, а не транзакции), ваш код должен выглядеть примерно так:
BEGIN TRAN Tr1
UPDATE BookInLib ...;
SELECT * FROM BookInLib;
SAVE TRAN SaveP1;
INSERT INTO BookInLib ...;
ROLLBACK TRAN SaveP1;
SELECT * FROM BookInLib;
Задание 6. Обратите внимание, что на момент окончания задания 5 у вас транзакция не завершена; откройте новое окно и выполните в нем следующий код:
use MyLib1; --здесь имя вашей базы GO
SELECT * FROM BookInLib;
Запрос должен «зависнуть», ожидая пока запущенная в задании 5 транзакция не снимет с записей блокировки. Это связано с тем, что используемый по умолчанию уровень изоляции – READ COMMITTED – не дает произвести «грязное чтение», т.е. чтение параллельно выполняющейся задачей данных, измененных еще не завершенной транзакцией. Завершите запущенную в задании 5 транзакцию откатом, убедитесь, что запущенный в другом окне select прочитал данные из таблицы, при этом данные соответствуют состоянию после отката транзакции.
Задание 7. Выполните действия, аналогичные предыдущему примеру, также откатив транзакцию к точке сохранения, но пока не завершая ее; в параллельном окне выполните запрос
SELECT * FROM BookInLib with (NOLOCK);
Указание NOLOCK разрешает чтение результатов незафиксированных транзакций. Таким образом, мы не ожидали окончания выполнения параллельной транзакции (запрос отработал сразу), но получили «грязное чтение» – был считан результат, незафиксированной транзакции, которую мы планировали отменить, и вы должны увидеть изменения, выполненные оператором UPDATE.
Подробнее о табличных указаниях, к которым относится NOLOCK, можно узнать здесь: https://msdn.microsoft.com/ru-ru/library/ms1873 73.aspx.
Сравнивая результаты выполнения заданий 6 и 7, обратите внимание на то, что в одном случае мы пожертвовали скоростью выполнения (select в параллельном окне ожидал окончания транзакции), а в другом – целостностью данных (в результате грязного чтения были прочитаны данные, внесенные отмененной в итоге транзакцией). Что и когда допустимо – зависит от предметной области и решаемой задачи.
Например, если вы решаете задачу проведения оплаты заказа в Интернет-магазине, для вас должно быть важнее обеспечение целостности данных.
А если для большого Интернет-магазина вы считаете среднюю сумму заказа за текущий год по всем заказам всех клиентов, допустимо посчитать это значение с учетом заказов, находящихся в данный момент в процессе оплаты (незавершенные транзакции). Даже если в итоге какой-то из этих заказов клиент оплатить не сможет и произойдет отмена, то отдельный заказ на итоговый результат оказывает очень небольшое влияние. В этом случае скорость выполнения запроса, затрагивающего большое количество записей, будет более приоритетна.
РАБОТА С ПЕРЕМЕННЫМИ И ВРЕМЕННЫМИ
ТАБЛИЦАМИ В SQL SERVER. ОПЕРАТОРЫ УПРАВЛЕНИЯ
ПОРЯДКОМ ВЫПОНЕНИЯ ПРОГРАММЫ
Лабораторная работа посвящена вопросам использования переменных и управления порядком выполнения программ в Transact SQL.
В заданиях будет использоваться та же «библиотечная» база, что и в предыдущих лабораторных.
Задание 1. Объявите три целочисленные переменные. Первой из них присвойте значение, равное количеству книг в таблице Book, второй – минимальный год издания книги из этой таблицы; третьей – максимальный. Выведите оператором print строку «В таблице Book N книг, изданных с X по Y годы.», где вместо N, X и Y подставлены соответствующие значения. Переменным присваивайте значения по отдельности в операторах SET. При формировании выводимой строки не забудьте про преобразование типов. Случай, когда ни у одной книги не задано значение года издания мы не рассматриваем.
Задание 2. Перепишите результат выполнения задания 1 таким образом, чтобы все три переменные получали значения в результате выполнения одного оператора SELECT (использование SELECT для присваивания значений переменным рассматривалось в лекциях).
Задание 5. Создайте табличную переменную @Books, по структуре соответствующую таблице Book (ключевое поле определяйте как int, без identity). Заполните ее данными из таблицы Book, меняя значение поля Publisher так, что:
- если книга издательства «Политехника», название издательства не меняем;
- если в поле Publisher в таблице Book стоит NULL, в @Books в соответствующее поле записывается «издательства нет»;
- во всех других случаях в поле Publisher в @Books записывается «издательство не Политехника».
Для выполнения этого задания используйте оператор case. После заполнения выберите данные из табличной переменной. С помощью ключевого слова GO завершите пакет заданий. Убедитесь, что табличная переменная перестала существовать.
|