Лабораторная работа №2. Изучение команды SELECT – запрос из нескольких источников

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

 

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

Задания для самостоятельной работы

Разработать запросы в соответствии с предложенными в табл. 1 вариантами:

Таблица 1

 

№ варианта	Номера задач
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

 

Разработать запрос, результатом которого будет таблица со столбцами...

1. фамилия, должность.

2. название проекта, название типа проекта.

3. имя, фамилия, название отдела.

4. название отдела, должность, фамилия. Упорядочить по названию отдела, должности, фамилии.

5. фамилия, должность, название отдела и его расположение. Упорядочить по месту расположения отдела, название отдела и фамилии.

6. место расположения отдела, фамилия. Упорядочить по месту расположения отдела, фамилии.

7. название проекта, роль сотрудника в проекте, фамилия сотрудника. Упорядочить по названию проекта, роли сотрудника в проекте, фамилии.

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

9. название отделения и фамилия сотрудников отделений компании в г. ИВАНОВО.

10.название проекта, роль сотрудника в проекте. В таблицу должны попасть проекты и соответствующие роли сотрудника Петрова.

11.название должности, количество сотрудников.

12.название отделения, количество сотрудников.

13.фамилия, количество завершенных проектов, в которых участвовал сотрудник.

14. название проекта, количество сотрудников задействованных в проекте.

15. отделение компании, количество проектов, в которых участвуют сотрудники отделения.

16. название города, количество проектов, в которых участвуют сотрудники отделения.

17.название проекта, роль в проекте, количество сотрудников указанной роли в указанном проекте.

18. название должности. В таблицу должны попасть только неактуальные должности, т.е. должности на которых в данный момент не работает ни один из сотрудников.

19. название роли в проекте. В таблицу должны попасть только неактуальные роли, т.е. роли которые не использовались ни в одном из проектов.

20. название отделения. В таблицу должны попасть только неактуальные отделения, т.е. отделения в которых в данный момент отсутствуют сотрудники.

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

22.название роли в проекте. В таблицу должны попасть только роли, которые использовались в проектах не более двух раз.

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

24.название должности. В таблицу должны попасть только две наиболее популярные должности. Для решения используйте команду TOP.

25.название роли в проекте. В таблицу должны попасть только две наиболее популярные роли. Для решения используйте команду TOP.

26.название отделения. В таблицу должны попасть только два отделения с самым большим числом сотрудников. Для решения используйте команду TOP.

27. фамилия сотрудника, фамилия непосредственного начальника. В случае если у сотрудника нет непосредственного начальника, вместо его фамилии указывается NULL.

28.фамилия и ежемесячный доход с учетом надбавки сотрудника с минимальным ежемесячным доходом. Неустановленная надбавка означает отсутствие надбавки.

29.фамилия и ежемесячный доход с учетом надбавки сотрудника с минимальным ежемесячным доходом. Неустановленная надбавка означает, что надбавка составляет 50% от зарплаты.

30.фамилия и ежемесячный доход с учетом надбавки сотрудника с максимальным ежемесячным доходом. Неустановленная надбавка означает, что надбавка составляет 50% от зарплаты.

31.фамилия и ежемесячный доход с учетом надбавки сотрудника с максимальным ежемесячным доходом. Неустановленная надбавка означает отсутствие надбавки.

 

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

1. Какова последовательность и назначение ключевых полей команды SELECT?

2. Принцип организация связи между таблицами в запросах по нескольким источникам?

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

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

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

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

7. Ключевое слово команды SELECT, задающее условие отбора групп строк?

 

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

Отчет о лабораторной работе должен включать: цель работы, постановку задачи, перечень всех разработанных SQL запросов.

 

 

4.3 Лабораторная работа №3.
Разработка структуры базы данных. Первая часть

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

Задания для самостоятельного решения

1. Разработать структуру базы данных для хранения приведенного универсального отношения для приведенных ниже вариантов универсальных отношений. Все таблицы разработанной базы данных должны находиться в нормальной форме Бойса-Кодда.

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

3. Разработать даталогическую схему базы данной.

 

Вариант 1

Видео прокат

Клиент	Фильм	Дата выдачи	Дата возврата	Жанр
Иванов	Такси-2	02.01.2004	03.01.2004	комедия
Иванов	Разрушитель	02.01.2004	05.01.2004	боевик
Сидоров	Разрушитель	06.01.2004		боевик
Петров	Такси	01.01.2004		комедия
Петров	Брат	05.01.2004	18.01.2004	боевик
Петров	Такси-2	02.01.2004	08.01.2004	комедия
Иванов	Такси-2	02.01.2005	04.01.2005	комедия
…

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

 

Вариант 2

Биржа

Акц. Общество	Всего акций	Держатель	Кол-во акций	Тип акционера
ОАО НК "ЮКОС"		Петров А.А.		Физ. Лицо
ОАО НК "ЮКОС"		УК "Капитал"		Юр. Лицо
ОАО "Самараэнерго"		УК "Капитал"		Юр. Лицо
ОАО "Самараэнерго"		Сидоров А.А.		Физ. Лицо
ОАО "Инвертарь"		Федорчук Е.Е.		Физ. Лицо
…

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

 

Вариант 3

Биржа труда

Вакансия	Фирма	Зарплата	Кол-во вакансий	Тел. Фирмы
Сторож	ООО "Полет"			222-222
Грузчик	ООО "Полет"			222-222
Грузчик	ООО "Смак"			333-333
Инженер	ЗАО "ГРНВ"			444-444
Водитель	ЗАО "ГРНВ"			444-444
Инженер	ООО "Смак"			333-333
…

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

 

Вариант 4

Коктейли

Название	Ингредиент	Кол-во ингредиента	Цена	Тип
Фантазия	Сок томатный			Безалк.
Фантазия	Сок лимонный			Безалк.
Фантазия	Текила			Алк.
Сочный букет	Сок лимонный			Безалк.
Сочный букет	Сок апельсиновый			Безалк.
Б-52	Текила			Алк.
Б-52	Сок лимонный			Безалк.
Отвертка	Водка			Алк.
Отвертка	Сок лимонный			Безалк.
…

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

 

Вариант 5

Урожай

Совхоз	Выработка, т	Элеватор	Объем хранилища, т	Зерна на элеваторе, т
«Светлый путь»		«Ветер»
«Светлый путь»		«Озерский»
«Светлый путь»		«Первый»
«Заря»		«Ветер»
«Заря»		«Озерский»
«Заветы»		«Озерский»
«Заветы»		«Ветер»
…

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

 

Вариант 6

Фитнес центр

Услуга	Клиент	Посещений в неделю	Стоимость услуги	Скидка, %
Тренажерный зал	Петров
Сауна	Петров
Йога	Петров
Танцы	Петров
Тренажерный зал	Сидоров
Йога	Сидоров
Йога	Иванов
…

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

 

Вариант 7

Овощная база

Товар	Поставщик	Цена за кг.	Вес, т	Телефон
Картошка	Совхоз «Заря»
Свекла	Совхоз «Заря»
Лук	Совхоз «Заря»
Картошка	Совхоз «Заветы»
Свекла	Совхоз «Заветы»
Свекла	ЧП Федоров
Лук	ЧП Федоров

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

 

Вариант 8

Оборудование

Оборудование	Дата монтажа	Параметр	Размерность	Номинальное значение
Котел №1	10.11.1990	Расход воды	т/ч
Котел №1	10.11.1990	Расход пара	т/ч
Котел №1	10.11.1990	Температура пара	Град. С
Котел №2	01.01.2000	Расход воды	т/ч
Котел №2	01.01.2000	Расход пара	т/ч
Котел №2	01.01.2000	Температура пара	Град. С
Турбина №1	01.01.2000	Расход пара	т/ч
Турбина №1	01.01.2000	Температура пара	Град. С
Турбина №2	13.02.2001	Температура пара	Град. С
…

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

 

Вариант 9

Курортная карта

Процедура	Кол-во посещений	Пациент	Врач	Категория
Массаж		Петров	Зубков	Высшая
Ванны		Петров	Сидоров	I категория
Физиотерапия		Петров	Задорнов	II категория
Ванны		Иванов	Зубков	Высшая
Физиотерапия		Иванов	Задорнов	II категория
Массаж		Кусков	Сидоров	I категория
Массаж		Валуев	Зубков	Высшая
Ванны		Валуев	Задорнов	II категория
…

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

 

Вариант 10

ОСАГО

VIN	Марка	Лица допущенные к управлению	Страхователь	Стаж
	БМВ-520	Петров	-
	БМВ-520	Сидоров	+
	ВАЗ-2111	Сидоров	-
	ВАЗ-2111	Петров	-
	ВАЗ-2111	Иванов	+
	ВАЗ-2107	Иванов	+
	ВАЗ-2107	Сидоров	+
…

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

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

1. Что означает выражение «полная функциональная зависимость» в отношении полей таблицы?

2. Что означает выражение «таблица находится в первой нормальной форме»?

3. Что означает выражение «таблица находится во второй нормальной форме»?

4. Что означает выражение «таблица находится в третьей нормальной форме»?

5. Что означает выражение «таблица находится в нормальной форме Бойса-Кодда»?

6. Что такое первичный ключ? Составной первичный ключ?

7. Что такое внешний ключ?

8. Что такое универсальное отношение?

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

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

 

4.4 Лабораторная работа №3.
Разработка структуры базы данных. Вторая часть

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

Задания для самостоятельного решения

1. Создать структуру базы данных, разработанную в результате выполнения лабораторной работы №2, в среде СУБД с применением команд управления структурой. Желательно дать таблицам и их полям названия латинскими буквами, например транслитерацией. В таблицах должны быть определены первичный и внешние ключи.

2. Заполнить таблицы БД с применением команд манипулирования данными.

3. Разработать запрос, выводящий на экран универсальное отношение.

4. Создать представление, выводящее на экран универсальное отношение.

5. Разработать запрос, указанный после универсального отношения (см. задание к лабораторной работе №2).

 

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

9. Что означает выражение «полная функциональная зависимость» в отношении полей таблицы?

10.Что означает выражение «таблица находится в первой нормальной форме»?

11.Что означает выражение «таблица находится во второй нормальной форме»?

12.Что означает выражение «таблица находится в третьей нормальной форме»?

13.Что означает выражение «таблица находится в нормальной форме Бойса-Кодда»?

14.Что такое первичный ключ? Составной первичный ключ?

15.Что такое внешний ключ?

16.Что такое универсальное отношение?

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

Отчет о лабораторной работе должен включать: цель работы, постановку задачи, даталогическую схему базы данных, полученной при выполнении лабораторной работы №2 и разрабатываемые SQL команды и запросы.

 

4.5 Лабораторная работа №3.
Разработка системы протоколирования операций над данными реляционной таблицы с использованием триггеров

Цель работы: ознакомиться с системой триггеров современных СУБД, приобрести практические навыки по разработке триггеров и реализации системы протоколирования на их основе.

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

 

Задание для самостоятельного решения:

1. Создать таблицу исходных данных «mydata» (команда CREATE TABLE), содержащую следующие атрибуты:

a. ID (integer) – уникальный идентификатор, являющийся первичным ключом таблицы.

b. STRDATA (varchar(100)) – строковый атрибут

c. INTDATA (integer)– числовой атрибут

d. FLOATDATA (float) – числовой атрибут

2. Создать дополнительную таблицу «mydata1», содержащую все поля исходной таблицы и поле CHANGETIME (datetime), являющегося первичным ключом.

3. Разработать триггеры, обеспечивающие следующую функциональность системы протоколирования: при изменении и удалении данных исходной таблицы осуществляется создание резервной записи в таблице «mydata1» содержащей предыдущие значения атрибутов, поле CHANGETIME заполняется системным временем (функция GETDATE ()).

4. Ввести в исходную таблицу целочисленное поле (команда ALTER TABLE). С помощью соответствующих триггеров обеспечить автоматическое заполнение данного поля следующими значениями: при вставке новой записи поле инициализируется значением 0. При изменении данных строки значение увеличивается на единицу, тем самым поле хранит текущее количество изменений каждой строки таблицы.

5. Создать дополнительную таблицу «mydata2», содержащую все поля исходной таблицы и поле CHANGETIME (datetime), являющееся первичным ключом.

6. Разработать триггер уровня строки, обеспечивающий при изменении данных исходной таблицы создание резервной записи в таблице «mydata2», содержащей предыдущие значения атрибутов, поле CHANGETIME заполняется системным временем (функция GETDATE ()), при этом резервная копия создается только в случае, если выполняется дополнительное условия (см. табл. 2).

 

Таблица 2

Вариант	Условие
	Уникальный идентификатор записи изменяется.
	Значение поля INTDATA становится меньше.
	Если длина строки в поле STRDATA увеличивается.
	Значение поля INTDATA меняет знак.
	Сумма полей INTDATA и FLOATDATA увеличивается.
	Значение поля FLOATDATA устанавливается в NULL.
	Значения всех полей кроме ID изменяется.
	Изменение значений полей не происходит.

 

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

1. Принцип функционирования Триггеров?

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

3. При удалении таблицы, что происходит с триггерами, связанными с ней?

4. Что такое составная SQL-команда?

 

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

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