Конструирование запросов к БД средствами MS Access

Создание базы данных «Библиотека» средствами MS Access

Пусть база данных «Библиотека» содержит следующие отношения (в скобках указаны атрибуты, курсивом выделен первичный ключ):

1) Книги (Шифр, Название, Авторы, Город, Издательство, Год_изд, Кол_страниц, Цена);

2) Экземпляр (Инв_номер, Шифр, Код_хранения);

3) Место_хранения (Код_хранения, Хранилище, Стеллаж, Полка);

4) Вкладыш (Код_вкладыша, Инв_номер, Выдана, Возвращена, Ном_билета);

5) Читатели (Ном_билета, ФИО, Адрес, Телефон, Дата_рождения).

Схема связей представлена на рис. 2.1.

Книги

Шифр

Название

Авторы

Город

Издательство

Год_изд

Кол_страниц

Цена

Экземпляр

Вкладыш

Код_вкладыша

Инв_номер

Выдана

Возвращена

Ном_билета

Читатели

Место_хранения

Рис. 2.1. Схема связей БД «Библиотека»

Выполните задание по шагам:

1. Создайте таблицы в режиме Конструктора (табл. 2.1 – 2.3).

Таблица 2.1

Таблица «Книги»

Таблица 2.2

Таблица «Читатели»

Таблица 2.3

Таблица «Экземпляр»

Остальные таблицы создайте аналогично.

2. Установите связи по внешним и первичным ключам с помощью подстановок.

Откройте таблицу «Экземпляр» в режиме Конструктора. Установите курсор на поле «Шифр», в столбце «Тип данных» выберите «Мастер подстановок» и в появившемся диалоговом окне установите переключатель «Объект подстановки будет использовать значения из таблицы или запроса». Далее выберите таблицу, содержащую исходные значения (Книги), и определите доступные поля: Шифр, Название, Авторы.

Аналогично создайте остальные связи.

3. Создайте схему данных.

На вкладке «Работа с базами данных» в группе «Показать/Скрыть» щелкните кнопку «Схема данных». С помощью появившегося контекстного меню добавьте все таблицы. Проверьте правильность установления связей по первичным и внешним ключам. Если задание 2 выполнено верно, то все таблицы будут соединены таким же образом, как указано на рис. 2.1.

Уточните типы связей между таблицами. Двойным щелчком по связи вызовите диалоговое окно, установите обеспечение целостности данных.

4. Заполните базу данных.

Внесите в каждую таблицу не менее 10 записей. Заполните таблицы в следующем порядке:

Читатели

Книги

Место_хранения

Экземпляр

Вкладыш

Конструирование запросов к БД средствами MS Access

Для выполнения лабораторной работы необходимо предварительно создать базу данных «БИБЛИОТЕКА». База данных должна содержать отношения, показанные на рис. 5.1.

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

Откройте БД «БИБЛИОТЕКА». В окне Базы данных выберите объект «Запросы». Создайте следующие запросы с помощью Конструктора:

1. Создание простого запроса выбора:

1.1. Создаем запрос, отвечающий на вопрос: Какие книги пользовались спросом?

- выберите вкладку «Запросы»;

- нажмите кнопку «Создать» и укажите режим «Конструктор»;

- произведите операцию соединения нескольких таблиц, добавив к бланку запроса таблицы «КНИГИ», «ЭКЗЕМПЛЯР», «ВКЛАДЫШ»;

- закройте окно «Добавление таблиц»;

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

1.3. Сохраните запрос под именем «СПРОС» (в меню «Файл» выберите пункт «Сохранить как»);

1.4. Закройте оболочку запроса с сохранением изменений;

1.5. Выполните запрос и убедитесь, что в нем отображается вся необходимая информация (кнопка «Открыть»). В запросе отразятся только те книги, которые выдавались читателям.

2. Формирование простого запроса с критерием поиска:

2.1. Создайте новый запрос выбора на основе запроса «СПРОС»: Какие из книг, пользующихся спросом, находятся на руках у читателей?

- выберите вкладку «Запросы» - «Создать» - «Конструктор»;

- произведите операцию соединения нескольких таблиц, добавив к бланку запроса таблицу «ВКЛАДЫШ» и запрос «СПРОС»;

- закройте окно «Добавление таблиц»;

2.2. Выберите нужные поля для запроса (шифр, название, авторы, издательство, город, год_изд, цена, возвращена);

2.3. Введите условие отбора в строку «Условие отбора»: в поле «Возвращена» укажите «IS NULL» (пусто);

2.4. Сохраните запрос под именем «НА РУКАХ»;

2.5. Выполните запрос.

3. Редактирование простого запроса выбора. Получим ответ на вопрос: Какие из книг дороже 100 руб. находятся на руках у читателей?

3.1. Откройте запрос «НА РУКАХ» в режиме «Конструктор»;

3.2. Добавьте дополнительные условия отбора: в поле «Цена» указать условие «> 100»;

3.3. Сохраните изменения «НА РУКАХ 100»;

3.4. Выполните запрос.

4. Создание параметрического запроса:

4.1. Создайте запрос: Какие книги на руках у читателя с ном_билета=Х?

4.2. Выберите для запроса следующие таблицы «КНИГИ», «ЧИТАТЕЛИ», «ЭКЗЕМПЛЯР», «ВКЛАДЫШ»;

4.3. Выберите нужные поля для запроса (ном_билета, шифр, название, авторы, издательство, город, год_изд, возвращена);

4.4. Задайте параметр условия в квадратных скобках в строке «Условие отбора» в поле «ном_билета» – [Введите ном_билета];

4.5. Сохраните запрос под именем «НА РУКАХ У ЧИТАТЕЛЯ»;

4.6. Выполните запрос, вводя значения параметра в диалоге.

5. Создание запроса на удаление. Запросы на удаление удобно формировать в два этапа: сформировать запрос выбора с нужными параметрами запроса и преобразовать его в запрос на удаление только после того, как убедитесь в точности сформулированного условия выбора:

5.1. Сформируйте запрос выбора «Удаление читателя» с нужными полями (ном_билета, ФИО). Определите параметр критерия отбора: в поле «ном_билета» укажите в квадратных скобках [Введите ном_билета];

5.2. Проверьте выбор по критерию. Для этого выполните запрос;

5.3. Преобразуйте запрос выбора в запрос на удаление (если выбор соответствует критерию): откройте запрос выбора «Удаление читателя» в режиме конструктора и преобразуйте его в запрос на удаление (в меню «Запрос» выберите команду «Удаление»).

MS Access исключит из бланка запроса строки «Сортировка» и «Вывод на экран» и вставит строку «Удаление». Условие отбора не должно измениться.

5.4. Закройте запрос с сохранением изменений;

5.5. Выполните запрос: удалите из базы любого читателя. Результатом выполнения запроса будет удаление из таблицы «ЧИТАТЕЛИ» указанного в параметре отбора «ном_билета», а также заказов этого читателя из таблицы «ВКЛАДЫШ». После выполнения запроса отменить операцию удаления невозможно.

6. Создание запроса на обновление. Запросы на обновление удобно формировать в два этапа: сформировать запрос выбора с нужными параметрами запроса и преобразовать его в запрос на обновление только после того, как убедитесь в точности сформулированного условия выбора.

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

6.2. В строку «Условие отбора» поля «шифр» ввести параметр запроса [Введите шифр];

6.3. Сохраните запрос под именем «Переоценка»;

6.4. Выполните запрос выбора «Переоценка»;

6.5. Если условие отбора выполняется, преобразуйте запрос выбора в запрос на обновление: откройте запрос в режиме конструктора и преобразуйте его в запрос на обновление (в меню «Запрос» выберите команду «Обновление»);

6.6. В строку «Обновление» поля «Цена» введите выражение: [Цена]*[Переоценка];

6.7. Сохраните запрос «Переоценка»;

6.8. Выполните созданный запрос с увеличением цены на 10% и уменьшением цены на 10%.

7. Самостоятельная работа. Создайте следующие запросы с помощью «Конструктора»:

1) Какие книги лежат в хранилище «Х»?

2) Какие книги из хранилища «Y» не возвращены?

3) Кто из читателей не вернул книги?

4) Кто из читателей взял книгу автора «Y», называющуюся «X»?

5) Какие книги читают читатели моложе 25 лет?

6) Какие книги были взяты сегодня?

7) В каком хранилище искать книгу «Х»?

8) Кто из читателей взял те же книги, что и читатель «Х»?

Не нашли, что искали? Воспользуйтесь поиском по сайту: