Выполнение лабораторной работы
Выполнить вставку тестовых данных в таблицы, созданные в ходе выполнения лабораторной работы 1. В строках, вставляемых в таблицы, должны быть данные как удовлетворяющие, так и не удовлетворяющие условиям запросов, приведенных в варианте задания. В случае внесения в таблицы ошибочных данных произвести их корректировку операторами update и delete. При обнаружении недочетов в структуре БД произвести ее корректировку с помощью alter table.
Содержание отчета
Содержание отчета:
— схема БД (если изменялась);
— наборы данных, содержащихся в таблицах БД;
— примеры использования insert, update и delete для корректных и некорректных данных (нарушающих ограничения и ссылочную целостность);
— примеры update и delete, вызывающих каскадные изменения и удаление данных;
— примеры использования alter table для корректировки структуры таблиц.
Варианты заданий
Варианты заданий приведены в ПРИЛОЖЕНИИ.
Лабораторная работа 3. Разработка запросов на языке QBE
Язык QBE
Язык QBE (Query By Example) является языком запросов к базам данных, предназначенным для составления запросов пользователями, не являющимися специалистами в области разработки программного обеспечения. В настоящее время реализации языка можно найти в форме графических конструкторов запросов в таких продуктах как Microsoft Access (далее по тексту — Access), Microsoft Power Query для Excel или OpenOffice Base.
Импорт в Access БД MS SQL
Для подключения Access к БД MS SQL необходимо выполнить следующие действия:
(i) ЗапуститьAccess —Пуск\Все программы\Microsoft Office\Microsoft Access 2010.
(ii) Создать БД Access — Файл\Создать\Имя файла — Университет\Создать.
(iii) Создать источник данных ODBC — Пуск\Панель управления\Администрирование\Источники данных (ODBC)\Добавить и ответить на вопросы мастера (сервер — SQL Server, имя источника — Университет, БД по умолчанию — Университет).
(iv) Импортировать БД в Access — Внешние данные\База данных ODBC\Источник данных компьютера\Университет и выбрать таблицы для импорта.
При импорте БД может происходить потеря части определений таблиц. Для восстановления первичных ключей необходимо открыть таблицу в конструкторе (команда Конструктор контекстного меню таблицы в окне Все объекты Access), выбрать столбец и выполнить команду Ключевое поле контекстного меню.
Для восстановления внешних ключей необходимо:
(i) Пометить их как индексируемые поля — выбрать столбец в конструкторе таблицы и установить значение свойства Индексированное поле в Да (Допускаются совпадения).
(ii) Проверить соответствие типов, аналогом identity в Access являются столбцы Счетчик, реализуемые на основе типа данных Длинное целое. Если внешние ключи, ссылающиеся на первичные ключи типа Счетчик будут иметь тип, отличный от Длинное целое, при создании связей таблиц могут происходить ошибки.
(iii) Создать схему БД (рис. 1) — Работа с базами данных\Схема данных и выбрать все имеющиеся таблицы. Далее необходимо создать связи путем перетаскивания первичного ключа на внешний или наоборот.
Рис. 1.
Разработка запросов
Разработки запросов на языке QBE в Access выполняется в конструкторе запросов (Создание\Конструктор запросов), при создании нового запроса Access предлагает выбрать подмножество таблиц БД, из которых будет осуществляться выборка данных. Вызов конструктора для изменения сохраненного запроса осуществляется командой Конструктор из контекстного меню запроса в окне Все объекты Access.
Пример запроса, возвращающего дисциплины, изучаемые на 4-м факультете, приведен на рис. 2. Для задания выбираемых значений и условий отбора необходимо перетащить имя столбца Наименование из таблицы Дисциплина и имя столбца Факультет из таблицы Группа в нижнюю часть окна в строку Поле:. Логическое выражение, задающее условие отбора, указывается в нижней части окна в строке Условие отбора:. Если (как в приведенном примере) между таблицами существуют связи, условий, определяющих правила соединения таблиц, задавать не нужно. В противном случае необходимо будет дополнить запрос условиями, показывающими равенство первичных и внешних ключей. Для выполнения запроса и получения результирующего набора данных необходимо выполнить команду меню ! Выполнить. Для возврата в режим конструктора используются команды Режим\Конструктор. По умолчанию Access не исключает повторяющиеся значения в результирующем наборе данных, для их исключения необходимо вызвать команду Свойства… контекстного меню и установить свойство Уникальные значения в состояние Да.
Рис. 2.
Некоторые запросы требуют включения одной и той же таблицы дважды в конструктор запроса (аналог псевдонимов таблиц в языке SQL). Для добавления таблицы к запросу необходимо выполнить команду контекстного меню Добавить таблицу…. Пример запроса, возвращающего студентов, получивших одинаковые оценки по БД и ФиЛП приведена рис. 3. В приведенном примере различные записи таблицы Оценка берутся под псевдонимами Оценка и Оценка_1, а связанные с ними различные записи таблицы Успеваемость — под псевдонимами Успеваемость и Успеваемость_1.
Рис. 3.
Существуют случаи, когда запрос должен вернуть не сами данные, а результаты их обработки, полученные с помощью агрегатных функций. Пример запроса, возвращающего количество дисциплин, сданных заданным студентом, приведен на рис. 4. Для реализации агрегатных функций необходимо выполнить команду Итоги на вкладке Конструктор, после чего выбрать требуемую агрегатную функцию: count — подсчет количества записей, avg — вычисление среднего, min — нахождение минимума, max — нахождение максимума, sum — суммирование значений в столбце. Сохраним запрос под именем Сдал_Сыроежкин (Файл\Сохранить объект как). Если в приведенном запросе очистить поле Условие отбора:, запрос вернет количество дисциплин, сданных каждым из студентов. Сохраним запрос под именем Сдали_Всего.
Рис. 4.
Рис. 5.
Источником данных для выполнения запроса может быть не только таблица, но и другой запрос. Пример запроса, возвращающего студентов, сдавших столько же дисциплин, сколько сдал Сыроежкин, приведен на рис. 5.