Запросы на языке SQL и иллюстрация их выполнения в СУБД Access
(a) выдать список преподавателей, которые в разное время использовали на занятиях издания заданного вида для заданного курса;
SELECT Преподаватель_В_Библиотеке.ФИО
FROM Преподаватель_В_Библиотеке INNER JOIN Издание ON Преподаватель_В_Библиотеке.Наименование = Издание.Наименование
WHERE (((Преподаватель_В_Библиотеке.Названипе_Курса)="Физика") AND ((Издание.Вид_Издания)="учебник"));
b) выдать список названий курсов, для которых используется литера-
тура, хранящаяся в заданном зале;
SELECT Преподаватель_В_Библиотеке.Названипе_Курса
FROM Преподаватель_В_Библиотеке INNER JOIN Место_Хранения ON Преподаватель_В_Библиотеке.Наименование = Место_Хранения.Наименование
WHERE (((Место_Хранения.Номер_Зала)=1));
c) выдать список преподавателей заданной кафедры, которые пользо-
вались читальными залами в заданный период времени;
SELECT Преподаватель_В_Библиотеке.ФИО, Преподаватель_В_Библиотеке.Дата_Выдачи
FROM Преподаватель_В_Библиотеке INNER JOIN Преподаватель ON Преподаватель_В_Библиотеке.ФИО = Преподаватель.ФИО
WHERE (((Преподаватель_В_Библиотеке.Дата_Выдачи)>#5/11/2013# And (Преподаватель_В_Библиотеке.Дата_Выдачи)<#5/20/2013#) AND ((Преподаватель.Номер_Кафедры)=28));
d) сформировать количественное распределение наименований изданий
по курсам (с указанием максимального количества выданных экземп-
ляров);
SELECT Sum(Преподаватель_В_Библиотеке.Количество_Экз) AS [Sum-Количество_Экз], Преподаватель_В_Библиотеке.Наименование
FROM Преподаватель_В_Библиотеке
GROUP BY Преподаватель_В_Библиотеке.Наименование, Преподаватель_В_Библиотеке.Названипе_Курса
HAVING (((Преподаватель_В_Библиотеке.Названипе_Курса)="Физика"))
ORDER BY Sum(Преподаватель_В_Библиотеке.Количество_Экз) DESC;
e) сформировать список наименований изданий, которые использовались
для проведения занятий более чем по одному курсу;
1) SELECT DISTINCT Преподаватель_В_Библиотеке.Названипе_Курса, Преподаватель_В_Библиотеке.Наименование
FROM Преподаватель_В_Библиотеке
GROUP BY Преподаватель_В_Библиотеке.Названипе_Курса, Преподаватель_В_Библиотеке.Наименование;
2) SELECT Count([Запрос (e)].Названипе_Курса) AS [Count-Названипе_Курса], [Запрос (e)].Наименование
FROM [Запрос (e)]
GROUP BY [Запрос (e)].Наименование
HAVING (((Count([Запрос (e)].Названипе_Курса))>1));
f) сформировать количественное распределение наименований изданий
по залам.
1) SELECT Место_Хранения.Номер_Зала, Sum(Преподаватель_В_Библиотеке.Количество_Экз) AS [Sum-Количество_Экз1]
FROM Преподаватель_В_Библиотеке INNER JOIN Место_Хранения ON Преподаватель_В_Библиотеке.Наименование = Место_Хранения.Наименование
GROUP BY Место_Хранения.Номер_Зала
ORDER BY Место_Хранения.Номер_Зала;
2) SELECT Место_Хранения.Номер_Зала, Count(Преподаватель_В_Библиотеке.Наименование) AS [Count-Наименование]
FROM Преподаватель_В_Библиотеке INNER JOIN Место_Хранения ON Преподаватель_В_Библиотеке.Наименование = Место_Хранения.Наименование
GROUP BY Место_Хранения.Номер_Зала;
Ответы на контрольные вопросы.
1. Дайте определение нормальной формы, которой как минимум должно удовлетворять каждое отношение.
Нормальная форма заключается в том, что старшая нормальная форма включает в себя свойства всех предыдущих, при этом, обладает своими отличительными признаками.
1НФ – имеет первичный ключ, атрибут или совокупность атрибутов, которые уникально характеризуют каждую запись. Каждый кортеж (строка) содержит в себе только одно значение для каждого из атрибутов.
2. Определите назначение методов нормализации данных.
Нормализацию данных можно представить, как метод анализ отношений на основе обнаружения первичного ключа и его функциональных и транзитивных зависимостей. Избавление от частичных функциональных и транзитивных зависимостей происходит за счет декомпозиции отношений.
3. Назовите типы аномалий, которые могут возникать в отношениях с избыточными данными. Приведите примеры, используя Вариант Задания.
Аномалии вставки. В реляционную таблицу нельзя добавить, например, преподавателя, если он не зафиксирован еще ни на одной кафедре.
Аномалии удаления. При удалении из реляционной таблицы, например, кафедры, удалится информация о всех преподавателях этой кафедры.
Аномалии модификации. При изменении, например, номера зала, информация о том, в каком зале хранится определенное наименование издания - пропадет.
4. Дайте определение функциональной зависимости. Перечислите функциональные зависимости в Варианте Задания.
Функциональная зависимость описывает связь между атрибутами отношения: если в отношении R, содержащем атрибуты А и В, атрибут В функционально зависит от атрибута А, то каждое отдельное значение атрибута А связано только с одним значением атрибута В.
В Варианте Задания можно выделить следующие зависимости:
Вид издания функционально зависит от Наименования издания. (Вид издания – Наименование издания)
Номер зала функционально зависит от Наименования издания. (Номер зала – Наименование издания)
Номер кафедры функционально зависит от Ф.И.О. преподавателя. (Номер кафедры - Ф.И.О. преподавателя)
5. Сформулируйте понятие полной функциональной зависимости и покажите, как оно связано с 2НФ. Приведите пример, используя Вариант Задания.
Реляционная таблица находится во 2НФ - если она удовлетворяет определению 1НФ и все ее атрибуты, которые не входят в первичный ключ, полностью функционально связаны с ним.
Реляционное отношение в 1НФ:
Преподаватель в библиотеке (Ф.И.О., Кафедра, Название курса, Наименование, Вид издания, Номер зала, Дата выдачи, Количество экземпляров)
Некоторые атрибуты в этом отношении функционально зависят от части первичного ключа, а именно: атрибуты Номер зала и вид издания зависят от значения атрибута Наименование издания, а атрибут кафедра зависит от значения атрибута Ф.И.О. преподавателя.
Таким образом, из исходного отношения необходимо выделить 3 проекции – отношение «Преподаватель» с первичным ключом Ф.И.О., отношение «Издания» с первичным ключом Наименование и отношение «Место хранения» с первичным ключом Наименование.
Получим новые отношения:
Преподаватель (Ф.И.О, кафедра)
Издание (Наименование, Вид издания)
Место хранения (Наименование, Номер зала)
6. Сформулируйте понятие транзитивной зависимости и покажите, как оно связано с 3НФ.
Реляционная таблица находится в 3НФ – если она удовлетворяет определению 2НФ и ни один из ее не ключевых атрибутов не связан функциональной зависимостью с любым другим не ключевым атрибутом.
В данном варианте задания нет транзитивной зависимости.
7. Сформулируйте для Варианта Задания запрос с использованием предложения HAVING.
Сформировать количественное распределение наименований изданий
по курсам:
SELECT Sum(Преподаватель_В_Библиотеке.Количество_Экз) AS [Sum-Количество_Экз], Преподаватель_В_Библиотеке.Наименование
FROM Преподаватель_В_Библиотеке
GROUP BY Преподаватель_В_Библиотеке.Наименование, Преподаватель_В_Библиотеке.Названипе_Курса
HAVING (((Преподаватель_В_Библиотеке.Названипе_Курса)="Физика"))
8. Поясните, может ли (каким образом) быть улучшен проект БД, полученный после декомпозиции универсального отношения Варианта Задания.
Проект БД может быть улучшен введением дополнительных полей в отношения. Например, в отношение Место_Хранения можно ввести дополнительные поля Номер_Стеллажа и Номер_ Полки, для более удобной работы библиотекарей.
Проект БД может быть улучшен введением дополнительных отношений. Например, отношение Библиотекарь. Это отношение можно ввести, например, для дальнейшего сбора статистики работы библиотекарей.
Изменения.
Преподаватель в библиотеке (Ф.И.О., Название курса, Наименование, Дата выдачи, Количество экземпляров)
Преподаватель (Ф.И.О, кафедра)
Издание (Наименование, Вид издания, Номер зала)
d) сформировать количественное распределение наименований изданий
по курсам (с указанием максимального количества выданных экземп-
ляров);
SELECT Преподаватель_В_Библиотеке.Названипе_Курса, Count(Преподаватель_В_Библиотеке.Наименование) AS [Число наименований], Max(Преподаватель_В_Библиотеке.Количество_Экз) AS [Максимальное число выданных экз]
FROM Преподаватель_В_Библиотеке
GROUP BY Преподаватель_В_Библиотеке.Названипе_Курса;
e) сформировать список наименований изданий, которые использовались
для проведения занятий более чем по одному курсу;
f) сформировать количественное распределение наименований изданий
по залам.
SELECT Издание.Номер_Зала, Count(Издание.Наименование) AS [Количество наименований]
FROM Издание
GROUP BY Издание.Номер_Зала;
