Содержание
- 1 Страницы
- 2 четверг, 24 октября 2013 г.
- 2.1 Запросы к базе данных Библиотека
- 2.2 Нужно выбрать читателей, которые имеют задолженность более 4 месяцев.
- 2.3 Определить читателей, у которых на руках находятся книги на общую сумму более 100 руб
- 2.4 Определить книгу, которая была наиболее популярной весной 2000 года.
- 2.5 Выбрать книгу, для которой наибольшее количество экземпляров находится "на полках" (не выданы читателям).
Нужно создать в СУБД « MS SQL Server 2005» базу данных «Библиотека».
Запускаем « MS SQL Server 2005 MSE », соединяемся с сервером, переходим в обозреватель объектов.
В инспекторе объектов переходим в раздел «Базы данных» и через контекстное меню создаем новую базу. Далее задаем нехитрые начальные параметры: имя, расположение.
Далее находим только что созданную базу, открываем и заходим в раздел «Библиотека – Таблицы». Вызываем контекстное меню на разделе «Таблицы» и выбираем создание. Для разминки создаем таблицу «Авторы».
Имя столбца
таблицы
Тип данных, размер поля,
наличие ключа
Int , первичный ключ, счетчик
Заполним таблицу фамилиями.
Рис. 1. Заполненная таблица «Авторы»
Следующей создаем таблицу «Книги».
Имя столбца
таблицы
Тип данных, размер поля,
наличие ключа
Помощь студентам. ИТ – новости, теория и практика
Страницы
четверг, 24 октября 2013 г.
Запросы к базе данных Библиотека
База данных «Библиотека»
Минимальный список характеристик, которые должна иметь база данных:
Автор книги, название, год издания, цена, количество экземпляров, краткая аннотация;
Номер читательского билета, ФИО, адрес и телефон читателя, дата выдачи книги читателю и дата сдачи книги читателем, отметка о выбытии.
Книга имеет много экземпляров и поэтому может быть выдана многим читателям.
Запросы на выборку:
- Выбрать книгу, для которой наибольшее количество экземпляров находится "на полках" (не выданы читателям).
- Выбрать читателей, которые имеют задолженность более 4 месяцев.
- Определить книгу, которая была наиболее популярной весной 2000 года.
- Определить читателей, у которых на руках находятся книги на общую сумму более 100 руб.
Вот и добрался я, наконец, до этих, головоломных на первый взгляд, запросов. Несмотря на то, что некоторые из них выглядят нерешаемыми, задача легко решается с помощью нескольких запросов. Но, если писать на чистом SQL, пытаясь уложиться в один запрос, то будет посложнее. Ну, попытаемся взять количеством))
Итак, создаем базу данных из трех таблиц – Книги, Читатели и Выдача.
Схема базы данных на рисунке:
Нужно выбрать читателей, которые имеют задолженность более 4 месяцев.
SELECT Читатели.ФИО, Выдача.Дата_выдачи, Книги.Название
FROM Читатели INNER JOIN (Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги) ON Читатели.Номер_билета = Выдача.Номер_билета
WHERE (((DateDiff("m",[Выдача]![Дата_сдачи],Date()))>4) AND ((IsNull([Выдача]![Дата_сдачи_факт]))=Yes));
Определить читателей, у которых на руках находятся книги на общую сумму более 100 руб
SELECT Читатели.ФИО, Sum(Книги.Цена) AS [Sum-Цена]
FROM Читатели INNER JOIN (Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги) ON Читатели.Номер_билета = Выдача.Номер_билета
WHERE (((IsNull([Выдача]![Дата_сдачи_факт]))=Yes))
GROUP BY Читатели.ФИО
HAVING (((Sum(Книги.Цена))>100));
Определить книгу, которая была наиболее популярной весной 2000 года.
Нужно выявить книгу (книги), которую выдавали наибольшее количество раз весной 2000 года.
Сделаем три запроса. Сначала посчитаем, сколько раз выдавали каждую книгу в нужный период.
Затем найдем максимальное значение.
Найдем нужную книгу по максимальному значению выдач.
Вот такой вариант пришел в голову. Если кто-то делает одним запросом, молодец.
Первый запрос (Количество):
SELECT Книги.[Шифр книги], Count(Выдача.Дата_выдачи) AS [Count-Дата_выдачи], Книги.Автор, Книги.Название
FROM Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги
WHERE (((Выдача.Дата_выдачи) Between #3/1/2000# And #5/31/2000#))
GROUP BY Книги.[Шифр книги], Книги.Автор, Книги.Название;
SELECT Max(Количество.[Count-Дата_выдачи]) AS [Max-Count-Дата_выдачи]
FROM Количество;
SELECT Количество.[Шифр книги], Количество.Автор, Количество.Название, Количество.[Count-Дата_выдачи]
FROM Максимум, Количество
WHERE (((Количество.[Count-Дата_выдачи])=[Максимум]![Max-Count-Дата_выдачи]));
Выбрать книгу, для которой наибольшее количество экземпляров находится "на полках" (не выданы читателям).
SELECT Max([Количество]-[Выдано]) AS [Не выдано]
FROM Книги;
SELECT Книги.Автор, Книги.Название, [Книги]![Количество]-[Книги]![Выдано] AS [Не выдано]
FROM Книги, [Не выдано 1]
WHERE ((([Книги]![Количество]-[Книги]![Выдано])=[Не выдано 1]![Не выдано]));
SELECT Книги.Автор, Книги.Название, Книги.Количество, Count(Книги.[Шифр книги]) AS [Count-Шифр книги], Книги.[Шифр книги]
FROM Книги INNER JOIN Выдача ON Книги.[Шифр книги] = Выдача.Щифр_книги
WHERE (((IsNull([Выдача]![Дата_сдачи_факт]))=Yes))
GROUP BY Книги.Автор, Книги.Название, Книги.Количество, Книги.[Шифр книги];
SELECT Книги.[Шифр книги], Книги.Автор, Книги.Название, Книги.Количество, [Не выдано 2].[Count-Шифр книги], IIf(IsNull([Count-Шифр книги]),[Книги]![Количество],[Книги]![Количество]-[Count-Шифр книги]) AS [На полке]
FROM Книги LEFT JOIN [Не выдано 2] ON Книги.[Шифр книги] = [Не выдано 2].[Шифр книги];
SELECT Max([Не выдано 2 -1].[На полке]) AS [Max-На полке]
FROM [Не выдано 2 -1];
SELECT [Не выдано 2 -1].[Шифр книги], [Не выдано 2 -1].Автор, [Не выдано 2 -1].Название, [Не выдано 2 -1].[На полке]
FROM [Не выдано 2 -1], [Не выдано 2 максимум]
WHERE ((([Не выдано 2 -1].[На полке])=[Не выдано 2 максимум]![Max-На полке]));
Рисунок 3 – Таблица "Информация о книге"
Таблица 12 Таблица "Выдача книг"
| № | Поле | Тип | Размер | Описание |
| 1 | Код выдачи | Счетчик | Код выдачи книги | |
| 2 | Шифр книги | Числовой | 5 | Ссылка на библиотечный шифр книги |
| 3 | Код читательского билета | Числовой | 5 | Ссылка на код читательского билета |
| 4 | Дата выдачи книги | Дата | Краткий формат даты По умолчанию =Date() | Дата выдачи книги |
| 5 | Дата возврата книги | Дата | Краткий формат даты | Дата возврата книги |
Рисунок 4 – Таблица "Выдача книг"
Таблица 13 Таблица "Бронирование книг"
| № | Поле | Тип | Размер | Описание |
| 1 | Код брони | Счетчик | 5 | Код брони книги |
| 2 | Шифр книги | Числовой | 5 | Ссылка на библиотечный шифр книги |
| 3 | Код читательского билета | Числовой | 5 | Ссылка на код читательского билета |
| 4 | Дата заказа | Дата | Краткий формат даты По умолчанию =Date() | Дата заказа книги |
Рисунок 5 – Таблица "Бронирование книг"
Таблица 14 Таблица "Издательства"
| № | Поле | Тип | Размер | Описание |
| 1 | Код издательства | Счетчик | Код издательства | |
| 2 | Наименование | Текстовый | 60 | Наименование издательства |
| 3 | Код города | Числовой | 5 | Ссылка на город издательства |
Рисунок 6 – Таблица "Издательства"
Таблица 15 Таблица "Города"
| № | Поле | Тип | Размер | Описание |
| 1 | Код города | Счетчик | Код города | |
| 2 | Наименование | Текстовый | 60 | Наименование города |
Рисунок 7 – Таблица "Города"
Таблица 16 Таблица "Авторы"
| № | Поле | Тип | Размер | Описание |
| 1 | Код автора | Счетчик | Код автора | |
| 2 | Фамилия | Текстовый | 60 | Фамилия автора |
| 3 | Имя | Текстовый | 60 | Имя автора |
| 4 | Отчество | Текстовый | 60 | Отчество автора |
Рисунок 8 – Таблица "Авторы"
Таблица 17 Таблица "Авторы книг"
| № | Поле | Тип | Размер | Описание |
| 1 | Код записи | Счетчик | Код записи | |
| 2 | Шифр книги | Числовой | 5 | Ссылка на шифр книги |
| 3 | Код автора | Числовой | 5 | Ссылка на код автора |
Рисунок 9 – Таблица "Авторы книг"
Расставим связи между таблицами (рисунок 10).
Рисунок 10 – Схема данных
Все таблицы связаны между собой связью типа "Один-ко-многим". На примере таблиц "Издательства" и "Города" это означает, что одно издательство может иметь только один город, но в таблице "Издательства" может присутствовать множество записей таблицы "Города". Т.е. разные издательства могут иметь одинаковые названия городов.
Аналогично связаны между собой остальные таблицы.
5. Основные характеристики используемой СУБД
В результаты мы получили СУБД, обладающую рядом характеристик.
Разработанная СУБД позволяет выполнять простейшие операции с данными:
– добавлять в таблицу одну или несколько записей;
– удалять из таблицы одну или несколько записей;
– обновлять значения некоторых полей в одной или нескольких записях;
– находить одну или несколько записей, удовлетворяющих заданному условию.
Разработанная СУБД организует хранение информации таким образом, чтобы ее было удобно:
– искать нужные сведения,
– делать любые выборки,
– осуществлять сортировку в любом порядке.
6. Разработка меню, форм, инструментальных панелей и др.
Разработаем формы для каждой из таблиц и занесем в них данные.
Рисунок 11 – Форма "Авторы"
Рисунок 12 – Форма "Города"
Рисунок 13 – Форма "Издательства"
Рисунок 14 – Форма "Информация о книге"
Форма "Информация о книге" имеет подчиненную форму "Авторы книг", через которую можно назначить несколько авторов одной книге.
Рисунок 15 – Форма "Информация о читателе"
Рисунок 19 – Форма "Бронирование книг"
В данной форме требуется ввести либо "Шифр книги" либо выбрать наименование книги. Второе поле база данных установит самостоятельно.
Рисунок 20 – Форма "Выдача книг"
Создадим главную кнопочную форму.
Рисунок 21 – Форма "Главная кнопочная форма"
7. Разработка запросов
1) Вывод сведений о книгах, взятых определенным читателем.
Рисунок 22 – Запрос "Вывод сведений о книгах, взятых определенным читателем"
2) Сведения о читателях, у которых находится определенная книга.
При построении данного запроса исключаются книги, которые были возвращены читателями, т.е. присутствует дата возврата.
Рисунок 23 – Запрос "Сведения о читателях, у которых находится определенная книга"
3) Сведения о читателе, прочитавшем за определенный интервал времени максимальное количество книг.
Данный запрос будет создан при помощи двух запросов.
Рисунок 24 – Запрос "Количество книг"
На основании него создадим другой запрос.
Рисунок 25– Запрос "Сведения о читателе, прочитавшем за определенный интервал времени максимальное количество книг"
4) Сведения о наличии определенной книги в фондах библиотеки.
Рисунок 26 – Запрос "Сведения о наличии определенной книги в фондах библиотеки"
5) Вывод сведений о домашнем адресе и телефоне определенного читателя.
Рисунок 27 – Запрос "Вывод сведений о домашнем адресе и телефоне определенного читателя"
8. Примеры обработки запросов
Приведем результаты запросов.
Рисунок 28 – Результат запроса "Вывод сведений о домашнем адресе и телефоне определенного читателя"
Рисунок 29 – Результат запроса "Вывод сведений о книгах, взятых определенным читателем"
Рисунок 30 – Результат запроса "Сведения о наличии определенной книги в фондах библиотеки"
Рисунок 31 – Результат запроса "Сведения о читателе, прочитавшем за определенный интервал времени максимальное количество книг"
Рисунок 32 – Результат запроса "Сведения о читателях, у которых находится определенная книга"
9. Разработка отчета
Разработаем отчет: Заказы на бронирование книг с группировкой и сортировкой по датам заказа.
Рисунок 33 – Макет отчета "Бронирование книг"
