ВлГУ, базы данных (лабораторные работы)

Лабораторная работа №1

СОЗДАНИЕ БАЗЫ ДАННЫХ MS SQL SERVER

Порядок выполнения работы

1. Загрузите СУБД.

2. Создайте новую базу данных с уникальным именем (см. п. 1), состоящую из четырех таблиц:

Продавцы (Код_продавца, ФИО_продавца, город_продавца, комиссионные_продавца, руководитель, план продаж);

Заказчики (Код_заказчика, ФИО_заказчика, город_заказчика, рейтинг заказчика, сумма_кредита); более высокий рейтинг указываетна большее предпочтение;

Заказы (номер_заказа, сумма_заказа, дата_заказа, код_продавца, код_заказчика);

Товар (Код_товара, Наименование_товара, цена, количество).

3. Создайте с помощью Management Studio таблицы Продавцы и Заказы вашей базы данных (см. п. 2).

4. Задайте в них первичные ключи (см. п. 4).

5.  Установите условие на значение любого столбца CHECK (см. п. 6).

6. Таблицы Заказчики и Товар создайте с помощью команды CREATE TABLE (см. п. 2, 3).

7. Создайте связи между таблицами (см. п. 7).

8. Создайте диаграмму вашей базы данных (см. п. 8).

9. Создайте скрипт вашей базы данных.

10. Введите данные во все созданные таблицы.

11. Убедитесь, что в вашей базе данных выполняются правила декларативной ссылочной целостности.

Содержание отчета

1. Цель работы.

2. Описание схемы базы данных

3. Описание процесса создания базы данных.

4. Описание процесса создания таблиц.

5. Описание процесса определения условий на значение.

Контрольные вопросы и задания

1. Из каких объектов состоит базы данных?

2. Каким образом может быть выполнено создание базы данных пользователем?

3. Назовите способы создания таблиц в SQL Server.

4. Как создаются таблицы в SQL Server пользователем?

5. Какие типы данных используются в SQL Server?

6. Как устанавливаются связи между таблицами в базе данных?

7. Как создаются первичные и внешние ключи?

8. Каким образом выполняется добавление и удаление ключей?

9. Укажите последовательность действий для определения условий на значения?

Лабораторная работа №2

МАНИПУЛИРОВАНИЕ ДАННЫМИ В MS SQL SERVER

Порядок выполнения работы

Откройте SQL Server Management Studio и выберите базу данных, созданную в предыдущей лабораторной работе.

1. Напишите команду SELECT, которая бы вывела номер заказа, сумму и дату для всех строк из таблицы "Заказы".

2. Напишите запрос, который вывел бы все строки из таблицы "Заказчики", для которых номер заказчика = 1001.

3. Напишите запрос, который вывел бы коды всех продавцов в текущем порядке из таблицы "Заказы" без каких бы то ни было повторений.

4. Напишите запрос к таблице "Заказчики", который содержит всех заказчиков с рейтингом =< 100, если они не находятся во Владимире.

5. Напишите запрос, который мог бы вывести все заказы на 3 или 4 апреля 2014 г.

6. Напишите запрос, который может вывести всех заказчиков, чьи имена начинаются с буквы, попадающей в диапазон от A до П.

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

8. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.

9. Напишите запрос, который выводил бы список заказчиков в нисходящем порядке. Вывод поля рейтинга должен сопровождаться именем  заказчика и его номером.

10. Напишите запрос, который бы выдавал имена продавца и заказчика для каждого заказа после номера заказа.

11. Напишите запрос, который бы выводил всех заказчиков, обслуживаемых продавцом с комиссионными выше 12 %. Выведите имя заказчика, имя  продавца и ставку его комиссионных.

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

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

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

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

16. Напишите запрос, который бы использовал оператор EXISTS  для извлечения всех продавцов, которые имеют заказчиков с рейтингом 300.

17. Напишите запрос, который бы выбирал всех заказчиков, чьи рейтинги равны или больше, чем любой (ANY) рейтинг заказчика.

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

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

20. Напишите команду, которая бы увеличила на двадцать процентов комиссионные всех продавцов, имеющих общие текущие порядки выше, чем 3000.

21. Напишите команду, которая бы удалила все заказы заданного заказчика из таблицы "Заказы".

22. Напишите команду, которая бы увеличила рейтинг всех заказчиков в Москве на 100.

23. Продавец X оставил компанию. Переназначьте его заказчиков продавцу Y.

Содержание отчета

1. Цель работы.

2. Описание схемы базы данных

3. Описание запросов и результатов запросов.

Контрольные вопросы

1. Дайте общую характеристику языку запросов Transact-SQL.

2. Запишите базовую конструкцию команды SELECT.

3. Какие операторы используются в предложении WHERE для отбора записей?

4. Дайте определение подзапроса.

5. Какие типы подзапросов вы знаете?

6. Укажите правила формирования запросов.

Лабораторная работа №3

ПРЕДСТАВЛЕНИЯ, ХРАНИМЫЕ ПРОЦЕДУРЫ И ТРИГГЕРЫ

Порядок выполнения работы

1. Откройте SQL Server Management Studio и выберите базу данных, созданную в предыдущей лабораторной работе.

2. Создайте представление, которое бы показывало всех заказчиков, имеющих самые высокие оценки.

3. Создайте представление, которое бы показывало номер продавца в каждом городе.

4. Создайте представление, которое бы показывало каждого продавца с многочисленными заказчиками.

5. Создайте представление, которое показывало фамилии продавцов, не выполняющих план продаж.

6. Создайте представление, которое бы показывало всех заказчиков из указанного города с рейтингом больше 100.

7. Создайте представление,; которое бы показывало фамилии продавцов и их общую сумму комиссионных.

8. Создайте представление, которое бы показывало сумму заказов по каждому городу заказчика.

9. Создайте представление таблицы "Продавцы" с именем Commissions (Комиссионные). Это представление должно включать только поля Код_продавца, ФИО_продавца и Комиссионные_продавца. С помощью этого представления можно будет вводить или изменять комиссионные, но только для значений между 0,1 и 0,2.

10. Напишите хранимую процедуру, которая увеличивает комиссионные у тех продавцов, сумма продаж которых превышает среднюю сумму продаж всей компании.

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

12.  Напишите хранимую процедуру, которая при выполнении заказа автоматически изменяла количество товара в таблице "Товар".

13.  Напишите триггер, который при добавлении или обновлении таблицы "Заказчики" проверял условие: значение рейтинга должно быть положительным и не превосходить 500.

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

15. Напишите триггер, который запрещает удалять запись из таблицы "Заказы", если с ней связаны записи из других таблиц.

Содержание отчета

1. Цель работы.

2. Описание схемы базы данных.

3. Описание представлений и результатов выполнения представлений.

4. Описание хранимых процедур и результатов выполнения хранимых процедур.

5. Описание триггеров и результатов выполнения триггеров.

6. Описание курсоров и результатов выполнения курсоров.

Контрольные вопросы и задания

1. С какой целью в базах данных используются представления?

2. Как создаются представления в SQL Server?

3. Какие операции можно выполнять при работе с представлениями?

4. С какой целью в базах данных используются хранимые процедуры?

5. Поясните, в чем состоит достоинство применения хранимых процедур?

6. Как осуществляется создание, вызов и удаление хранимых процедур?

7. Что представляют собой триггеры, как они создаются?

8. Что представляют собой курсоры, как они создаются?

9. Как осуществляется работа с курсорами в SQL Server?

Лабораторная работа №4

АДМИНИСТРИРОВАНИЕ И ОБЕСПЕЧЕНИЕ БЕЗОПАСНОСТИ БАЗ ДАННЫХ В MS SQL SERVER

Порядок выполнения работы

1. Создайте резервную копию БД с помощью команд языка SQL.

2. Сохраните файлы вашей БД на каком-либо носителе. Для этого, используя команду Detach Database, отключите вашу базу от сервера.

3. Удалите вашу БД с сервера и восстановите ее двумя способами (используя резервную копию БД с применением команды Attach), используя стандартные функции Enterprise Manager.

4. Проделайте всё указанное в п. 4, используя команды языка SQL.

5. Создайте в вашей БД пользователя, который имеет право только просматривать содержимое таблиц. Проверьте это с помощью Query Analyzer.

6. Создайте пользователя, которому запрещено просматривать данные из таблиц ''Продавцы'' и ''Заказчики'' и удалять данные из таблиц товары и заказы. Проверьте это с помощью запросов, используя Query Analyzer.

Содержание отчета

1. Цель работы.

2. Описание процесса создания резервной копии БД.

3. Описание процесса отключения/подключения БД.

4. Описание процесса восстановления БД из резервной копии.

5. Описание процесса, создания пользователей в БД и описание прав пользователей, созданных в вашей БД.

6. Используя SQL запросы, продемонстрировать результаты работы в БД под разными пользователями.

Контрольные вопросы

1. Команды SQL для создания резервной копии БД.

2. Команды SQL для создания восстановления БД из резервной копии.

3. Какой командой можно изменить размер БД?

4. Что такое роль в SQL server?

5. Команды для физического удаления данных из БД.

6. Опишите процесс создания нового пользователя в БД.