ДонГТУ, базы данных (лабораторный практикум)

ЛАБОРАТОРНАЯ РАБОТА № 1

Тема: сервер баз данных MySQL, управление базами данных, таблицами, индексами.

Цель работы: закрепить теоретический материал по работе с базами данных, таблицами, индексами.

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

1. Создайте таблицы базы данных в соответствии со своим вариантом. Типы данных для полей выберите самостоятельно.

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

3. Заполните таблицы данными. Количество строк в таблицах должно быть не менее количества, указанного в квадратных скобках в задании.

4. Создайте трех пользователей и предоставьте им соответствующие привилегии. Имена пользователей начинайте с названия вашей базы данных (например, service_admin):

– администратор базы данных — все права на управление Вашей базой данных (включая привилегии Super);

– аудитор (имеет возможность только просмотра всех таблиц);

– оператор справочников имеет право добавлять, удалять и редактировать строки только таблиц-справочников (содержащих только первичные ключи);

– оператор данных имеет право добавлять, удалять и редактировать строки основных таблиц (содержащих внешние ключи).

5. После выполнения всего задания создайте резервную копию базы данных.

Варианты заданий

Вариант 1. База данных банка

Сотрудники (КодСотрудника, ФИО, ДатаРождения, Пол, Адрес, Телефон, Паспорт, Должность) [10 записей].

Должности (КодДолжности, НазДолжности, Оклад, Обязанности) [5 записей].

ТипыВкладов (КодТипа, НазТипа, МинСрок, МинСумма, Ставка, ДопУсловия) [5 записей].

Вкладчики   (КодВкладчика,  ФИО,   Адрес,   Телефон,   Паспорт) [7 записей].

Вклады (Вкладчик, ДатаВклада, СрокВклада, ТипВклада, СуммаВклада, Возвращено, Сотрудник) [10 записей].

Вариант 2. База данных больницы

Врачи (КодВрача, ФИО, ДатаРождения, Пол, Адрес, Телефон, Паспорт) [5 записей].

Лекарства (КодЛекарства, НазЛекарства, Показания, Противопоказания, Цена) [10 записей].

Болезни (КодБолезни, НазБолезни, Симптомы, Осложнения) [10 записей].

Рецепты (Болезнь, Лекарства) [15 записей].

Пациенты (КодПациента, ДатаРрождения, Пол, Адрес, Телефон) [10 записей].

Лечение (Пациент, ДатаОбращения, Болезнь, Врач, ДатаВыздоровления) [30 записей].

Вариант 3. База данных отеля

Номера (КодНомера, Номер, Вместимость, Описание, Стоимость) [10 записей].

ВидыУслуг (КодУслуги, НазУслуги, Описание, Стоимость) [5 записей].

Клиенты (КодКлиента, ФИО, ДатаРождения, Паспорт) [5 записей].

Поселение (ДатаПоселения, Клиент, Номер, ДатаВыезда) [30 записей].

Обслуживание (Дата, Клиент, Услуга) [15 записей].

Вариант 4. База данных аэропорта

Города (КодГорода, НазГорода) [5 записей].

Самолеты (КодСамолета, Марка, Вместимость, ДатаВыпуска) [10 записей].

Рейсы (КодРейса, Дата, ГородОтправки, ГородНазначения, Самолет, Продолжительность) [20 записей].

Пассажиры (КодПассажира, Фамилия, Имя, Отчество, Паспорт, Телефон) [15 записей].

ПроданныеБилеты (Рейс, Пассажир, Место, Цена).

Вариант 5. База данных проката дисков

Сотрудники (КодСотрудника, ФИО, ДатаРождения, Адрес, Паспорт) [5 записей].

Жанры (КодЖанра, НазЖанра, Описание) [5 записей].

Диски (КодДиска, НазваниеФильма, ГодВыпуска, Жанр, ЦенаЗаСутки) [15 записей].

Клиенты (КодКлиента, ФИО, Пол, Адрес, Телефон, Паспорт) [10 записей].

Прокат (Клиент, Диск, Сотрудник, ДатаВзятия, ДатаВозвращения, ОтметкаОбОплате) [30 записей].

Вариант 6. База данных библиотеки

Жанры (КодЖанра, НазЖанра, Описание) [5 записей].

Издательства (КодИздательства, НазИздательства, Адрес) [5 записей].

Книги (КодКниги, НазКниги, ПервыйАвтор, Издательство, ГодИздания, Жанр) [15 записей].

Читатели (КодЧитателя, ФИО, ДатаРождения, Адрес, Телефон) [10 записей].

Выдача (Книга, Читатель, ДатаВыдачи, ДатаВозвращения) [30 записей].

Вариант 7. База данных радиостанции

Сотрудники (КодСотрудника, ФИО, ДатаРождения, Пол, Адрес, Телефон, Паспорт) [5 записей].

Исполнители (КодИсполнителя, Название (ФИО), Описание) [10 записей].

Жанры (КодЖанра, НазЖанра, Описание) [5 записей].

Записи (КодЗаписи, НазЗаписи, Исполнитель, Год, Жанр, ДатаЗаписи, Время) [15 записей].

Вещание (Дата, Время, Сотрудник, Запись) [30 записей].

Вариант 8. База данных таксопарка

Водители (КодВодителя, ФИО, ДатаРождения, Пол, Адрес, Телефон, Паспорт) [10 записей].

Автомобили (КодАвто Марка, РегНомер, ГодВыпуска, Водители) [10 записей].

Марки (КодМарки, НазМарки, ТехнХаракт, Стоимость) [5 записей].

Тарифы (КодТарифа, НазТарифа, Размер) [4 записи].

Вызовы (Дата, Время, Телефон, Расстояние, Тариф, Авто) [30 записей].

Вариант 9. База данных туристического агентства

Отели (КодОтеля, НазваниеОтеля, Город, Адрес, Количество звезд) [5 записей].

Клиенты (КодКлиента, ФИО, ДатаРождения, Пол, Адрес, Телефон, Паспорт) [10 записей].

ДопУслуги (КодУслуги, НазУслуги, Описание, Цена) [5 записей].

Путевки (КодПутевки, ДатаПоселения, ДатаВыезда, Гостиница, Стоимость, Клиент) [30 записей]

Предоставляемые услуги (Путевка, Услуга, Количество) [15 записей].

Вариант 10. База данных страховой компании

Группы клиентов (КодГруппы, НазГруппы, Описание) [5 записей].

Клиенты (КодКлиента, ФИО, ДатаРождения, Пол, Адрес, Паспорт, Группа) [15 записей].

Филиалы (КодФилиала, НазФилиала, Адрес, Телефон) [5 записей].

ВидыПолисов (КодВида, НазВида, РазмерВзноса, СуммаВыплаты) [5 записей].

Полисы (КодПолиса, ДатаОформления, ВидПолиса,  СрокДействия, Клиент, Филиал) [30 записей].

Вариант 11. База данных сервисного центра

Запчасти (КодЗапчасти, НазЗапчасти, Функции, Цена) [10 записей].

ВидыНеисправностей (КодВида, НазВида, Описание) [5 записей].

Ремонт (Неисправность, Запчасть, Количество) [10 записей].

Магазины (КодМагазина, НазМагазина, Адрес, Телефон) [10 записей].

Заказы (ДатаЗаказа, ДатаВозвращения, Магазин, СерийныйНомер, Неисправность, Гарантия) [30 записей].

Вариант 12. База данных транспортной компании

Водители (КодВодителя, ФИОВодителя, ДатаРождения, Адрес, Телефон) [5 записей].

МаркиАвто (КодМарки, НазМарки, ТехнХарактеристики) [5 записей].

Автомобили (КодАвто, Марка, РегНомер, ГодВыпуска, Водитель) [5 записей].

Грузы (КодГруза, НазГруза, Тариф) [15 записей].

Рейсы (Дата, Заказчик, ПунктОтправления, ПунктНазначения, Груз, Количество, Авто, Доставлено) [30 записей].

Вариант 13. База данных деканата

Студенты (НомерСтудБилета, Фамилия, Имя, Отчество, ДатаРождения) [20 записей].

Кафедры (КодКафедры, НазКафедры Факультет) [5 записей].

Преподаватели (ТабНомер, Фамилия, Имя, Отчество, Кафедра) [10 записей].

Предметы (КодПредмета, Название, Преподаватель, КоличествоЧасов) [10 записей].

Ведомость (Студент, Предмет, ДатаСдачи, Оценка) [35 записей].

Вариант 14. База данных строительной компании

Материалы (КодМатериала, НазваниеМатериала, Описание, Цена) [10 записей].

ВидыРабот (КодВида, НазВида, Описание, Стоимость) [10 записей].

Смета (ВидРаботы, Материал, Количество) [25 записей].

Заказчики (КодЗаказчика, ФИО, Адрес, Телефон, Паспорт) [10 записей].

Заказы (Заказчик, ВидРаботы, ДатаНачала, ДатаОкончания, ФактОплаты) [35 записей].

Вариант 15. База данных риэлтерской фирмы

Сотрудники (КодСотрудника, ФИО, ДатаРождения, Пол, Адрес, Паспорт) [5 записей].

ВидыУслуг (КодУслуги, НазУслуги, Описание, Цена) [5 записей].

ВидыКвартир (КодВида, НазВида, Описание) [5 записей].

Квартиры (КодКвартиры, Вид, Адрес, КоличествоКомнат, Площадь, Цена, ФИОВладельца) [15 записей].

Договоры (Дата, Квартира, Услуга, Сотрудник) [30 записей].

Вариант 16. База данных рекламного агентства

Сотрудники (КодСотрудника, ФИО, ДатаРождения, Пол, Адрес, Телефон) [5 записей].

Дополнительные услуги (КодУслуги, НазваниеУслуги, Описание, Стоимость) [5 записей].

Заказчики (КодЗаказчика, НазваниеЗаказчика, Адрес, Паспорт) [15 записей].

Заказы (КодЗаказа, ДатЗаказа, ДатаВыполнения, Заказчик, ФактУплаты) [20 записей].

Смета (Заказ, Услуга, Сотрудник) [35 записей].

Вариант 17. База данных компьютерной фирмы

ВидыКомплектующих (КодВида, НазВида, Описание) [5 записей].

Комплектующие (КодКомпл, НазваниеКомпл, Вид, ДатаВыпуска, Характеристики, СрокГарантии) [15 записей].

Заказчики (КодЗаказчика, Фамилия, Адрес, Телефон, Паспорт) [10 записей].

Заказы (КодЗаказа, ДатаЗаказа, ДатаВыполнения, Заказчик, ФактУплаты) [20 записей].

Спецификации (Заказ, Комплектующие, Количество, Цена) [35 записей].

Вариант 18. База данных ГАИ

Звание (КодЗвания, НазЗвания, Надбавка) [5 записей].

Сотрудники (КодСотрудника, ФИО, ДатаРождения, Адрес, Звание) [5 записей].

МаркиАвто (КодМарки, НазваниеМарки, Производитель) [5 записей].

Водители (КодВодителя, ФИО, ДатаРождения, Адрес, Паспорт, НомерВодительПрав, ДатаВыдачи, СрокДействия) [20 записей].

Автомобили (КодАвто, Марка, Цвет, ДатаВыпуска, Водитель, РегНомер, НомерВыдал) [30 записей].

Вариант 19. База данных кинотеатра

Страны (КодСтраны, НазваниеСтраны) [10 записей].

Жанры (КодЖанра, НазваниеЖанра, Описание) [5 записей].

Фильмы (КодФильма, Название, Режиссер, Жанр, Страна, Время, Ограничение) [10 записей].

Сеансы (КодСеанса, Дата, ВремяНачала, Фильм) [20 записей]. ПроданныеБилеты (Сеанс, Место, Цена) [35 записей].

Вариант 20. База данных автосалона

ТипыКузова (КодТипа, НазваниеТипа, Описание) [5 записей].

Автомобили (КодАвто, Марка, ТипКузова, Цвет, Год Выпуска, Цена) [30 записей].

Сотрудники (КодСотрудника, ФИО, ДатаРождения, Адрес) [5 записей].

Клиенты (КодКлиента, Фамилия, Адрес, Телефон, Паспорт) [20 записей].

Продажи (ДатаЗаказа, ДатаПродажи, Авто, Клиент, Сотрудник) [15 записей].

Требования к отчету

Отчет оформляется на стандартных листах белой бумаги формата А4 (210x297 мм). Текст пишется с одной стороны листа чернилами синего или черного цвета или печатается на принтере. Бланк отчета готовится во время домашней подготовки к работе и должен содержать:

– данные о студенте: фамилия и инициалы, шифр группы;

– тему и цель работы;

– дату выполнения работы;

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

После выполнения работы к отчету подшиваются: распечатка таблиц базы данных (в виде SQL-запроса), распечатка схемы базы данных. Отчет должен быть выполнен аккуратно. Неаккуратно выполненные отчеты, а также ксерокопии чужих отчетов или их фрагментов к защите не допускаются.

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

1. Приведите определения понятий: банк данных, база данных, СУБД, реляционная база данных.

3. Приведите основные характеристики отношения.

4. Что представляет собой домен и каково его основное назначение?

5. Перечислите виды ключей в реляционной базе данных и их назначение.

6. Каково назначение языка SQL? Какой стандарт SQL действует на настоящий момент?

7. Приведите синтаксис оператора создания базы данных. Что представляют собой опции CHARACTER SET и COLLATE?

8. Что представляет собой временная таблица и для чего может понадобиться ее использование?

9. Приведите синтаксис оператора CREATE TABLE.

10. Приведите синтаксис оператора ALTER TABLE.

11. Приведите основные типы данных MySQL и их характеристики.

12. Как создать первичный ключ в таблице с помощью SQLкоманд?

13. Как создать внешний ключ в таблице с помощью SQL-команд?

14. Приведите синтаксис команды для удаления объектов базы данных?

15. Дайте определение понятию индекс. Для чего он используется?

16. Какие типы индексов существуют в СУБД MySQL?

17. Способы создания индексов средствами SQL-команд.

18. В каких таблицах MySQL содержится информация о привилегиях пользователей.

19. Синтаксис оператора GRANT. Можно ли предоставить привилегии пользователю на уровне поля?

20. Синтаксис оператора REVOKE. Что делает этот оператор?

ЛАБОРАТОРНАЯ РАБОТА № 2

Тема: SQL-запросы, триггеры, хранимые процедуры.

Цель работы: закрепить теоретический материал по работе с SQL-запросами, триггерами, хранимыми процедурами.

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

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

2. Распечатайте SQL-тексты запросов, триггера и процедуры, а также результаты их выполнения.

Варианты заданий

Вариант 1. База данных банка

1. Создать запрос, который выбирает сотрудников в возрасте от 30 до 50 лет (КодСотрудника, ФИО, Возраст, НазДолжности). Список отсортировать сначала в соответствии с должностью (по алфавиту), а затем в соответствии с уменьшением возраста.

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

3. Создать запрос, который выводит количество принятых вкладов по годам (Год, КоличествоВкладов). Список отсортировать по уменьшению количества вкладов.

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

5. Используя текст запроса из п.1, создать хранимую процедуру, которая принимает два значения возраста сотрудников и выводит список сотрудников, чей возраст находится между значениями этих параметров.

Вариант 2. База данных больницы

1. Создать запрос, который выводит информацию о всех пациентах с именем, начинающимся на буквы «П» или «К». Список отсортировать по алфавиту (ФИО, Возраст, Адрес, Телефон).

2. Создать запрос, который выводит суммарные расходы на лекарства за предыдущий календарный год (НазЛекарства, СуммарныеРасходы). Список отсортировать по сокращению расходов.

3. Создать запрос, выводящий список из трех болезней, которыми чаще всего болеют пациенты зимой (НазБолезни, Месяц, Количество пациентов).

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

5. Используя текст запроса из п.1, создать хранимую процедуру, которая в качестве параметра принимает букву и выводит список пациентов, имя которых начинается на эту букву.

Вариант 3. База данных отеля

1. Создать запрос, который рассчитывает выручку отеля по месяцам (Месяц, Выручка). Список отсортировать по уменьшению выручки.

2. Создать запрос, выводящий список из трех клиентов, которые потратили наибольшее количество денег за предыдущий месяц (ФИО, Сумма). Список отсортировать па алфавиту.

3. Создать запрос, который выводит виды услуг ценой от 200 до 500 руб. (НазУслуги, Описание, Цена).

4. Создать триггер, который при удалении записи о клиенте записывает информацию о нем в таблицу «Архив» (ФИО, Паспорт, ДатаУдаления, КтоУдалил).

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

Вариант 4. База данных аэропорта

1. Создать запрос, выводящий пункты назначения, в которые за последние две недели не было осуществлено ни одного рейса (КодГорода, НазГорода). Список отсортировать по наименованию городов (по алфавиту).

2. Создать запрос, который выводит список самолетов вместимостью от 150 людей (Марка, Вместимость, ДатаВыпуска). Список отсортировать в порядке уменьшения даты выпуска.

3. Создать запрос, который выводит список из пяти пассажиров, которые чаще всех летают летом.

4. Создать триггер, который при вставке записи в таблицу «ПроданныеБилеты» заносит запись в таблицу «ЖурналУчета» (ДатаПродажи, Пользователь, ПунктНазначения, Цена, Место).

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

Вариант 5. База данных проката дисков

1. Создать запрос, который выводит список сотрудников, отсортированный по алфавиту (все поля).

2. Создать запрос, выводящий список клиентов, которые имеют диски на руках (ФИО, Телефон, КоличествоДисков). Список отсортировать по уменьшению количества дисков.

3. Создать запрос, выводящий информацию о 2 жанрах, которые пользуются наибольшей популярностью у мужчин (НазЖанру, КоличествоЗапросов).

4. Создать триггер, который при заполнении даты возвращения диска заносит информацию о прокате в таблицу «Возвращения» (ФИОклиента, НазваниеФильма, Дата взятия, ДатаВозвращения, СтоимостьПроката).

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

Вариант 6. Базы данных библиотеки

1. Создать запрос, выводящий три жанра, которые пользовались наибольшей популярностью за прошлый месяц (НазЖанра, КоличествоВыдач). Список отсортировать по уменьшению количества выдач.

2. Создать запрос, который выводит список читателей в возрасте от 30 лет (ФИО, Возраст, Адрес, Телефон). Список отсортировать по увеличению возраста.

3. Создать запрос, выводящий список книг, которыми пользовались больше месяца (НазКниги, ФИОЧитателя, ДлительностьЧтения).

4. Создать триггер, который в случае добавления записи в таблицу «Читатели» копирует информацию о читателе в таблицу «Мужчины» или «Женщины» в зависимости от пола читателя. Таблицы должны содержать поля: КодЧитателя, ФИО, Возраст.

5. Используя текст запроса из п.2, создать хранимую процедуру, которая принимает в качестве параметра возраст и выводит список читателей, чей возраст превышает введенное значение.

Вариант 7. База данных радиостанции

1. Создать запрос, выводящий список сотрудников с фамилиями, которые начинают на буквы от «О» до «Я» (ФИО, Дата рождения, Пол). Список отсортировать по алфавиту.

2. Создать запрос, выводящий информацию об исполнителях, которые за последние полгода не записали ни одной песни (Название (ФИО), Описание).

3. Создать запрос, который выводит пять записей, которые чаще всего ставят в утреннее (до 12-00) время (НазЗаписи, Исполнитель, Жанр, КоличествоРотаций). Список отсортировать по уменьшению количества ротаций.

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

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

Вариант 8. База данных таксопарка

1. Создать запрос, выводящий список водителей, которые родились весной (ФИО, Дата рождения, Пол). Список отсортировать по дате рождения.

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

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

4. Создать триггер, который в случае изменения размера тарифа в таблице «Тарифы» заносит информацию об изменениях в таблицу «УчетТарифов» (Дата изменения, Пользователь, СтарыйТариф, НовыйТариф, ПроцентИзменения).

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

Вариант 9. База данных туристического агентства

1. Создать запрос, выводящий клиентов, которые не пользовались дополнительными услугами (ФИО, ДатаПоселения, ДатаВыезда).

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

3. Создать запрос, который выводит список путевок стоимостью больше 10000 руб. (КодПутевки, Гостиница, Клиент, ДатаПоселения, Стоимость). Список отсортировать сначала по гостиницам (по алфавиту), а потом по уменьшению стоимости путевки.

4. Создать триггер, который в случае удаления гостиницы переносит информацию о ней в таблицу «Архив» (КодГостиницы, НазваниеГостиницы, Город, Адрес, КоличествоЗвезд, ДатаУдаления, КтоУдалил).

5. Используя текст запроса из п.3, создать хранимую процедуру, которая принимает в качестве параметра стоимость путевки и выводит список путевок, стоимость которых превышает введенное значение.

Вариант 10. База данных страховой компании

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

2. Создать запрос, который выводит данные об оформленных полисах сроком действия от одного года (КодПолиса, ДатаОформления, СрокДействия, ФИОклиента, НазФилиалу). Список отсортировать в порядке уменьшения срока действия.

3. Создать запрос, выводящий список филиалов, которые в проходящем месяце не оформили ни одного полиса (НазФилиала, Адрес, Телефон).

4. Создать триггер, который в случае добавления новой записи в таблицу «Полисы» значения, которое вводится в поле «СуммаВыплаты» увеличивает на 30%.

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

Вариант 11. База данных сервисного центра

1. Создать запрос, выводящий список из трех неисправностей, которые случались чаще всего за прошлый месяц (НазНеисправности, Количество). Список отсортировать в порядке уменьшения количества случаев.

2. Создать запрос, выводящий список запчастей, цена которых варьируется от 250 до 500 руб. Список отсортировать в соответствии с ростом цены.

3. Создать запрос, который подсчитывает стоимость уже проведенных ремонтов (тех, для которых заполнена дата возвращения). Вывести поля: ДатаЗаказа, Магазин, Неисправность, Стоимость.

4. Создать триггер, который в случае добавления новой записи в таблицу «Заказа» будет проверять, если этот вид неисправности уже десять раз встречается в этой таблице, то будет заносить информацию о нем в таблицу «Нехватка» (КодВида, НазВида, Описание, ДатаЗаноса).

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

Вариант 12. База данных транспортной компании

1. Создать запрос, выводящий четыре вида грузов, которые чаще всего перевозили в течение последнего полугодия (НазГруза, Количество рейсов). Список отсортировать по алфавиту.

2. Создать запрос, который подсчитывает среднюю стоимость перевозок по месяцам (Месяц, СредняяСтоимость).

3. Создать запрос, который выводит список водителей, старше 50 лет (ФИО, ДатаРождения, Возраст). Список отсортировать по увеличению возраста.

4. Создать триггер, который записывает информацию о выполненных рейсах по перевозке грузов весом больше 100 кг в таблицу «ГабаритныеГрузы» (Дата, НазГруза, Вес, СтоимостьДоставки).

5. Используя текст запроса из п.2, создать хранимую процедуру, которая принимает в качестве параметра номер месяца и выводит среднюю стоимость перевозок за указанный месяц.

Вариант 13. База данных деканата

1. Создать запрос, выводящий список преподавателей, которые ведут больше одного предмета (ФИО, НазКафедры, Количество). Список отсортировать сначала по наименованию кафедры, а потом по ФИО преподавателя (по алфавиту).

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

3. Создать запрос, выводящий список студентов, которые не сдали ни одного предмета (НомерСтудБилета, ФИО).

4. Создать триггер, который заносит данные о предметах, которые ведут больше чем один преподаватель, в таблицу «Совместители» (КодПредмета, Название, ФИОПреподавателя).

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

Вариант 14. База данных строительной компании

1. Создать запрос, который подсчитывает средние расходы на материалы по каждому заказчику (ФИО, СредниеРасходы). Список отсортировать по алфавиту.

2. Создать запрос, который выводит список незаконченных работ (ФИОЗаказчика, ВидРаботы, ДатаНачала). Список отсортировать в соответствии с увеличением даты начала работ.

3. Создать запрос, который выводит перечень материалов с ценой от 250 до 520 руб.

4. Создать триггер, который будет отслеживать историю регистрации сотрудников — в случае изменения адреса регистрации записывать информацию в таблицу «Регистрация» (ФИО, ДатаРождения, НовыйАдрес, ДатаИзменения).

5. Используя текст запроса из п.3, создать хранимую процедуру, которая принимает в качестве параметров две цены и выводит список материалов, цена которых варьируется в заданном диапазоне.

Вариант 15. База данных риэлтерской фирмы

1. Создать запрос, который выводит список сотрудников, серия паспорта которых «ЕК» (ФИО, ДатаРождения, Паспорт). Список отсортировать по алфавиту.

2. Создать запрос, который рассчитывает максимальное количество комнат среди квартир каждого вида (НазВида, КоличествоКомнат).

3. Создать запрос, выводящий информацию о договорах, которые были заключены весной прошлого года (Дата, ФИОклиента, АдресКвартиры, НазУслуги, СуммаДоговора). Список отсортировать в соответствии с уменьшением суммы договора.

4. Создать триггер, который в случае удаления информации о квартире перемещает записи о договорах, связанные с ней, в таблицу «Архив» (КодКвартиры, Адрес, ВидУслуги, ДатаУдаления).

5. Используя текст запроса из п.1, создать хранимую процедуру, которая в качестве параметра принимает идентификатор серии паспорта и выводит список сотрудников, серия паспорта которых совпадает с введенным значением.

Вариант 16. База данных рекламного агентства

1. Создать запрос, который выводит список неуплаченных заказов за последний месяц (ДатаЗаказа, НазваниеЗаказчика, ДатаВыполнения). Список отсортировать в соответствии с уменьшением даты заказа.

2. Создать запрос, выводящий список из трех сотрудников, которые предоставили наибольшее количество услуг по заказам (ФИО, Количество услуг).

3. Создать запрос, который отбирает услуги стоимостью от 400 до 1000 руб. (НазУслуги, Описание, Стоимость). Список отсортировать в порядке уменьшения стоимости.

4. Создать триггер, который при добавлении нового сотрудника копирует данные о нем в таблицу «МолодыеСотрудники» (ФИО, Возраст, Адрес, Пол), если его возраст не превышает 35 лет.

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

Вариант 17. База данных компьютерной фирмы

1. Создать запрос, который выводит суммарное количество заказанных комплектующих каждого вида (НазВида, Количество). Список отсортировать по уменьшению количества.

2. Создать запрос, который выводит список комплектующих со сроком гарантии больше одного года (Название, НазВида, Марка, ДатаВыпуска, СрокГарантии). Список отсортировать сначала по виду (по алфавиту), а потом в соответствии с увеличением срока гарантии.

3. Создать запрос, который выводит список из трех заказчиков, которые заказали на наименьшую сумму за последние две недели (Фамилия, Адрес, Сумма).

4. Создать триггер, который в случае установления отметки «Оплачен» копирует данные о выполненных заказах в таблицу «ВыполненныеЗаказы» (ДатаВыполнения, ФИОзаказчика).

5. Используя текст запроса из п.3, создать хранимую процедуру, которая в качестве параметров принимает две даты и выводит список из трех заказчиков, которые заказали на наименьшую сумму в интервале времени между введенными значениями.

Вариант 18. База данных ГАИ

1. Создать запрос, который подсчитывает количество автомобилей каждой марки (НазваниеМарки, Количество). Список отсортировать в соответствии с уменьшением количества.

2. Создать запрос, который выводит список водителей с просроченными водительскими правами (ФИО, НомерВодителяПрава, ДатаВыдачи, СрокДействия).

3. Создать запрос, который формирует зарплатную ведомость за прошлый календарный месяц (ФИО, Оклад, Надбавки, Всего).

4. Создать триггер, который в случае добавления нового автомобиля записывает в таблицу «ЧерныеАвто» (КодАвто, НазваниеМарки, ДатаВыпуска, РегНомер) информацию о нем, если цвет нового авто черный.

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

Вариант 19. База данных кинотеатра

1. Создать запрос, выводящий список фильмов, на которые не было продано ни одного билета (НазваниеФильма, НазваниеЖанра, Длительность). Список отсортировать в соответствии с увеличением длительности.

2. Создать запрос, выбирающий пять стран, которые выпустили наибольшее количество фильмов за последний год (НазваниеСтраны, КоличествоФильмов). Список отсортировать по алфавиту.

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

4. Создать триггер, который в случае удаления фильма заносит информацию о нем в таблицу «Архив» (НазваниеФильма, НазЖанру, ДатаУдаления, Пользователь)

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

Вариант 20. База данных автосалона

1. Создать запрос, который подсчитывает среднюю цену автомобилей каждого типа кузова (ТипКузова, СредняяЦена). Список отсортировать по убыванию цены.

2. Создать запрос, выводящий список автомобилей, которые были проданы осенью (ДатаПродажи, Марка, Цвет, Цена, ФИОзаказчика, ФИОсотрудника). Список отсортировать сначала по маркам (по алфавиту), а потом по росту цены.

3. Создать запрос, который выводит список клиентов, которые купили более одного автомобиля (Фамилия, Адрес, Телефон, Количество).

4. Создать триггер, который в случае изменения цены на авто заносит информацию об изменении в таблицу «УчетЦен» (ДатаИзменения, КодАвто, Марка, СтараяЦена, НоваяЦена, ПроцентИзменения).

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

Требования к отчету

Отчет оформляется на стандартных листах белой бумаги формата А4 (210x297 мм). Текст пишется с одной стороны листа чернилами синего или черного цвета или печатается на принтере.

Бланк отчета готовится во время домашней подготовки к работе и должен содержать:

– данные о студенте: фамилия и инициалы, шифр группы;

– тему и цель работы;

– дату выполнения работы;

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

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

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

1. Для чего предназначен оператор SELECT?

2. Общий синтаксис оператора SELECT.

3. Что означает ключевое слово DISTINCT в операторе SELECT?

4. Какое ключевое слово используется для сортировки строк в запросе?

5. Как интерпретируется в различных частях оператора SELECT символ «*»?

6. Для чего используется конструкция INNER JOIN, LEFT JOIN, RIGHT JOIN, их синтаксис.

7. Какие виды операций в операторе SELECT вы знаете?

8. Назначение предиката HAVING.

9. Перечислите агрегирующие функции SQL и дайте их краткое описание.

10. Для чего используется предикат LIMIT. Его синтаксис.

11. Какие вы знаете функции преобразования данных? Функции управления потоком выполнения?

12. Назначение оператора INSERT. Общий синтаксис.

13. Сравните операторы обновления данных UPDATE и REPLACE.

14. Какая особенность применения многотабличного оператора UPDATE?

15. Чем оператор DELETE отличается от оператора TRUNCATE?

16. Для чего используются триггеры?

17. Какие вы знаете типы триггеров? Когда они срабатывают?

18. Что такое хранимая процедура?

19. Какой оператор используется для вызова хранимой процедуры и каков его синтаксис?

20. Что такое хранимая функция, каково ее отличие от хранимой процедуры?

ЛАБОРАТОРНАЯ РАБОТА № 3

Тема: программирование баз данных в Visual C#.NET.

Цель работы: закрепить теоретический материал по программирование баз данных в Visual C#.NET.

Задание к лабораторной работе

Разработайте приложение для управления БД, которая была создана Вами при выполнении первой и второй лабораторных работ.

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

2. Обеспечьте возможность вывода результатов запросов, которые были созданы Вами в лабораторной работе №2, в компонент DataGridView.

3. Используя результаты третьего запроса, создайте отчет на основе компонента WebBrowser. Отчет должен выводиться на отдельной форме.

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

5. Запрограммируйте приложение. Обеспечьте условия для минимизации ошибок пользователя. Обеспечьте кеширование изменений. Создайте систему предупреждений для пользователя в случае возникновения ошибки.

6. Обеспечьте печать данных из таблиц и отчета.

Требования к отчету

Отчет оформляется на стандартных листах белой бумаги формата А4 (210x297 мм). Текст пишется с одной стороны листа чернила синего или черного цвета или печатается на принтере.

Бланк отчета готовится во время домашней подготовки к работе и должен содержать:

– данные о студенте: фамилия и инициалы, шифр группы;

– тему и цель работы;

– дату выполнения работы;

– набор данных, необходимых для выполнения работы: задача к работе, код программы, короткие теоретические сведения о функциях и библиотеках Visual C# .NET, которые использовались при подготовке отчета.

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

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

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

1. Перечислите уровни объектной модели ADO.NET.

2. Опишите жизненный цикл соединения с базой данных MySQL.

3. Параметры строки соединения с базой данных. Назначение параметра Integrated Security.

4. Какие Вы знаете события объекта Connection?

5. Назначение объекта Command.

6. Чем отличаются методы ExecuteNonQuery(), ExecuteScalar(), ExecuteReader()?

7. Какие Вы знаете способы создания объекта Command?

8. Поддерживает ли объект Command команды с параметрами?

9. Что определяет свойство Direction параметра команды?

10. Как установить значение параметра?

11. Назначение свойства SourceVersion.

12. Синтаксис параметров для СУБД MySQL?

13. Какой объект ADO.NET предназначенный исключительно для чтения данных?

14. Назначение объекта DataAdapter.

15. Как привязать текстовое поле к полю таблицы базы данных?

16. Назначение и структура объекта DataSet.

17. Методы заполнения объекта DataSet.

18. Может ли объект DataSet содержать несколько таблиц БД?

19. Как осуществить фильтрацию данных в объекте DataSet?

20. Какие элементы управления могут отображать информацию из базы данных?

21. Как метод Update объекта DataAdapter определяет, какую команду (INSERT, UPDATE, DELETE) применить к измененной строке?

22. Как установить связь между таблицами в объекте DataSet?

23. Как настроить элемент управления DataGridView на отображение данных таблицы БД?

24. Какие компоненты предлагает Visual C# .NET для создания отчетов?

25. С помощью какого свойства компоненту WebBrowser задается текст страницы отчета?

ЛАБОРАТОРНАЯ РАБОТА № 4

Тема: программирование сетевых баз данных в Visual C# .NET.

Цель работы: научиться создавать сетевые приложения с использованием технологии ASP.NET для работы с базами данных.

Задание к лабораторной работе

Разработайте Web-приложение для управления БД, которая была создана Вами при выполнении 1-й и 2-й лабораторных работ.

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

Доступ к справочным таблицам должен осуществляться через связанные элементы управления. Для доступа к основной таблице используйте элемент управления DataGrid. Для навигации по записям таблиц БД установите кнопки Button.

Используя результаты запроса с параметрами, создайте отчет. Отчет должен выводить на отдельной форме.

Запрограммируйте приложение. Обеспечьте условия для минимизации ошибок пользователя. Обеспечьте кеширование изменений. Создайте систему предупреждений для пользователя в случае возникновения ошибки.

Требования к отчету

Отчет оформляется на стандартных листах белой бумаги формата А4 (210x297 мм). Текст пишется с одной стороны листа чернила синего или черного цвета или печатается на принтере.

Бланк отчета готовится во время домашней подготовки к работе и должен содержать:

– данные о студенте: фамилия и инициалы, шифр группы;

– тему и цель работы;

– дату выполнения работы;

– набор данных, необходимых для выполнения работы: задача к работе, код программы, короткие теоретические сведения о функциях и библиотеках Visual Basic.NET, которые использовались при подготовке отчета.

После выполнения работы к отчету подшивается распечатка результатов.

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

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

1. Что такое привязка данных в ASP.NET?

2. Что представляет собой простая привязка данных?

3. Как осуществить простую привязку данных?

4. Что представляет собой сложная привязка данных?

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

6. Как осуществить привязку данных с помощью DataReader?

7. Для чего используется кеширование данных в ASP.NET и какие его типы существуют?

8. Добавление данных в таблицу с помощью DataTable, приведите пример.

9. Добавление данных в таблицу с помощью MySqlCommand, приведите пример.

10. Обновление строки в таблице с ипользованием DataRow, приведите пример.

11. Обновление строки в таблице с ипользованием MySqlCommand, приведите пример.

12. Какие Вы знаете способы удаления строки из набора данных? Перечислите их, приведите примеры.

13. Основное назначение транзакции в ADO.NET?

14. Как реализовать транзакцию в программе на C#?

15. Каким образом можно осуществить перемещение по строкам в компоненте GridView?

ЛАБОРАТОРНАЯ РАБОТА № 5

Тема: Работа с базами данных в Java.

Цель работы: научиться работать с базами данных с использованием технологии JDBC и языка программирования JAVA.

Задание к лабораторной работе

Разработайте программу, которая обеспечивает работу с базой данных:

1. Извлечение данных с помощью запросов.

2. Занесение новых данных.

3. Редактирование данных.

4. Удаление записей.

Требования к отчету

Отчет оформляется на стандартных листах белой бумаги формата А4 (210x297 мм). Текст пишется с одной стороны листа чернилами синего или черного цвета или печатается на принтере.

Бланк отчета готовится во время домашней подготовки к работе и должен содержать:

данные о студенте: фамилия и инициалы, шифр группы; тему и цель работы;

дату выполнения работы;

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

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

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

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

1. Что представляет собой JDBC и для чего он используется?

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

3. Приведите схему архитектуры ODBC.

4. Как подключиться к серверу MySQL с использованием JDBC?

5. Как осуществить загрузку драйвера базы данных с использованием JDBC?

6. Какие сведения о БД можно получить с помощью объекта класса DatabaseMetaData?

7. Приведите синтаксис создания объекта Connection?

8. Назначение класса Statement, его отличие от PreparedStatement и CallableStatement.

9. Назначение класса PreparedStatement, его отличие от Statement и CallableStatement.

10. Назначение класса CallableStatement, его отличие от PreparedStatement и CallableStatement.

11. Что является результатом выполнения метода executeUpdate объекта Statement?

12. Для выполнения каких операторов языка SQL используется метод executeUpdate?

13. Использование параметров совместно с PreparedStatement.

14. Какие методы используются для доступа к данным, содержащимся в объекте ResultSet?

15. Опишите методы объекта  ResultSet для перемещения по набору данных.

16. Какую информацию содержит исключение SQLException?