Реферат Проектирование базы данных 2
Работа добавлена на сайт bukvasha.net: 2015-10-28Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
Федеральное агентство по делам образования и науки Российской Федерации
Южно-Уральский государственный университет
Кафедра систем управления
ПОЯСНИТЕЛЬНАЯ ЗАПИСКА К КУРСОВОМУ ПРОЕКТУ
ПО КУРСУ БАЗЫ ДАННЫХ
Вариант №6
Нормоконтролёр: Руководитель:
Долбенков В.И ______________ Долбенков В.И._______________
Автор: студент группы ПС-343
Терентьева А.О. ____________
Работа защищена на оценку_____
«___»_______________2008 г
Челябинск
2008
СОДЕРЖАНИЕ
Введение…………………………………………………………………………2
Задание…………………………………………………………………………...3
1. Проектирование базы данных……………………………………………….4
1.1. Анализ предметной области
1.2. Информационно-логическая модель
1.3. Физическая модель
2. Реализация базы данных в Microsoft Acess
3 Реализация базы данных в СУБД Microsoft Access. 8
3.1 Структура таблиц, ключи и индексы.. 8
3.2 Связи между таблицами. 13
3.3 Основные запросы.. 14
3.3.1 Список всех книг с фамилиями авторов, сгруппированный по областям знаний. 14
3.3.4 Список покупателей, сгруппированный по городам. 20
3.3.5 Средний ежемесячный объем заказов. 22
3.3.6 Средний ежемесячный объем заказов по каждому покупателю.. 24
3.3.7 Ежемесячный объем продаж книг каждого наименования. 26
3.3.8 Бестселлер прошлого года. 27
3.4 Стандартные формы и отчеты.. 29
3.4.1 Форма «Список всех книг с фамилиями авторов, сгруппированный по областям знаний». 29
3.4.2 Форма «Список покупателей, сгруппированный по городам». 31
3.4.3 Отчет «Проданные книги, сгруппированные по покупателям». 32
Литература. 34
Введение
База данных — это единое, большое хранилище данных, которое однократно определяется, а затем используется одновременно многими пользователями — представителями разных подразделений. Вместо разрозненных файлов с избыточными данными здесь все данные собраны вместе с минимальной долей избыточности. База данных уже не принадлежит какому-либо единственному отделу, а является общим корпоративным ресурсом. Причем база данных хранит не только рабочие данные этой организации, но и их описания.
Система управления базами данных (СУБД) -программное обеспечение, с помощью которого пользователи могут определять, создавать и поддерживать базу данных, а также осуществлять к ней контролируемый доступ.
СУБД позволяет создать базу данных, вставлять, обновлять, удалять и извлекать информацию из базы данных. Наличие централизованного хранилища всех данных и их описаний позволяет использовать язык DML как общий инструмент организации запросов, который иногда называют языком запросов (query language). Наличие языка запросов позволяет устранить присущие файловым системам ограничения, при которых пользователям приходится иметь дело только с фиксированным набором запросов или постоянно возрастающим количеством программ, что порождает другие, более сложные проблемы управления программным обеспечением. Наиболее распространенным типом непроцедурного языка является язык структурированных запросов (Structured Query Language — SQL), который в настоящее время определяется специальным стандартом и фактически является обязательным языком для любых реляционных СУБД.
СУБД Microsoft Access — наиболее широко используемая в среде Microsoft
Windows реляционная СУБД, Microsoft Access — типичная СУБД для персональных компьютеров, обеспечивающая хранение, сортировку и поиск данных для множества приложений. В СУБД Access для создания таблиц, запросов, форм и отчетов предусмотрен графический интерфейс пользователя (Graphical User Interface — GUI); для разработки настраиваемых приложений с базой данных есть инструментальные средства, использующие макроязык Microsoft Access или язык VBA (Microsoft Visual Basic for Applications). Кроме того, в СУБД Access предусмотрены программы, называемые мастерами (Wizards), которые упрощают многие из процессов формирования приложений с базой данных, проводя пользователя через ряд диалоговых окон в запросно-ответном режиме. В СУБД Access предусмотрены также конструкторы (Builders), которые могут помочь пользователю сформировать синтаксически правильные выражения, например операторы и макрокоманды языка SQL.
Задание
Создать базу данных для консалтингового агентства. Построить инфологическую модель на основе метода «Сущность – связь». В полученных ER-диаграммах необходимо устранить возможные аномалии.Реализовать полученную модель базы данных в Microsoft Access: структура таблиц, ключи и индексы; связи между таблицами; основные запросы; стандартные формы и отчеты.
Запросы и отчеты должны быть составлены, чтобы предоставлять пользователю базы данных следующую информацию:
- Список клиентов, сгруппированный по городам;
- Отчет по контрактам;
- Список контрактов по отдельной услуге;
- Список контрактов, сгруппированный по виду услуги за прошедший год;
- Три самых важных клиента (принесших наибольшую прибыль);
- Список работников, отсортированный в обратном порядке в зависимости от величины суммы контрактов;
- Ежемесячная сумма контрактов на услуги каждого вида;
- Годовой отчет по сумме, полученной за оказанные услуги;
- Запрос на создание архивной таблицы и запрос на исключение данных из основной таблицы контрактов, выполненных 3 года назад и более.
1. Проектирование базы данных
Существуют два основных подхода к проектированию систем баз данных: нисходящий и восходящий. При восходящем подходе работа начинается с самого нижнего уровня атрибутов (т.е. свойств сущностей и связей), которые на основе анализа существующих между ними связей группируются в отношения, представляющие типы сущностей и связи между ними. Восходящий подход в наибольшей степени приемлем для проектирования простых баз данных с относительно небольшим количеством атрибутов. Однако использование этого подхода существенно усложняется при проектировании баз данных с большим количеством атрибутов, установить среди которых все существующие функциональные зависимости довольно затруднительно. Поскольку концептуальная и логическая модели данных для сложных баз данных могут содержать от сотен до тысяч атрибутов, очень важно выбрать подход, который помог бы упростить этап проектирования. Кроме того, на начальных стадиях формулирования требований к данным в крупной базе данных может быть трудно установить все атрибуты, которые должны быть включены в модели данных.
Более подходящей стратегией проектирования сложных баз данных является использование нисходящего подхода. Начинается этот подход с разработки моделей данных, которые содержат несколько высокоуровневых сущностей и связей, затем работа продолжается в виде серии нисходящих уточнений низкоуровневых сущностей, связей и относящихся к ним атрибутов. Нисходящий подход демонстрируется в концепции модели "сущность-связь". В этом случае работа начинается с выявления сущностей и связей между ними, интересующих данную организацию в наибольшей степени.
Кроме этих подходов для проектирования баз данных могут применяться другие подходы, например, подход "от общего к частному" или "смешанная стратегия проектирования". Подход "от общего к частному" напоминает восходящий подход, но отличается от него тем, что вначале выявляется набор основных сущностей с последующим расширением круга рассматриваемых сущностей, связей и атрибутов, которые взаимодействуют с первоначально определенными сущностями. В смешанной стратегии сначала используются восходящий и нисходящий подходы для создания разных частей модели, после чего все подготовленные фрагменты собираются в единое целое.
Выделим основные этапы, на основании которых перейдем от предметной области к реализации БД средствами СУБД Access:
1. анализ предметной области;
2. информационно- логическое проектирование на основе сущность – связь;
3. создание физической модели;
4. БД и приложения, реализованные на конкретной программно-аппаратной основе.
1.1. Анализ предметной области
Под предметной областью понимается часть реального мира, которая отражается в проектировании баз данных. Опишем нашу предметную область: консалтинговое агентство предоставляет маркетинговые услуги – консультации - в области аудита, экспресс-анализа и бизнес-планирования для промышленных предприятий. Контракт подписывается каждым клиентом по каждой услуге. Он включает: фамилию клиента, название компании клиента, вид услуги, дату подписания, дату начала работ, дату завершения работ, дату оплаты и сумму контракта. Список услуг включает: код услуги, вид услуги. Данные на клиентов включают: имя клиента, фамилию клиента, название компании клиента, город, адрес, номер телефона. В консалтинговом агентстве назначается менеджер проекта по каждому контракту. Данные на менеджеров проекта содержат: имя и фамилию работника, его номер телефона.
1.2. Информационно-логическая модель
ER-моделирование представляет собой нисходящий подход к проектированию базы данных, который начинается с выявления наиболее важных данных, называемых сущностями (entities). Затем в модель вносятся дополнительные сведения, например, указывается информация о сущностях, называемая атрибутами (attributes), а также все ограничения, относящиеся к сущностям, связям и атрибутам. Затем устанавливаются связи (relationships) между данными, которые должны быть представлены в модели.
Экземпляр сущности -однозначно идентифицируемый объект, который относится к сущности определенного типа. Каждый однозначно идентифицируемый объект типа сущности, который относится к сущности определенного типа, называется просто экземпляром сущности (entity occurrence).
Атрибуты содержат значения, которые описывают каждый экземпляр сущности и составляют основную часть информации, сохраняемой в базе данных. Связь – зависимость между атрибутами двух или более сущностей.
По заданию необходимы следующие сущности и атрибуты сущностей:
| Клиент |
| Имя клиента |
| Фамилия клиента |
| Название компании клиента |
| Город |
| Адрес |
| Номер телефона |
Таблица 1.1.
Атрибуты сущностей
| Контракт | ||
| Фамилия клиента | ||
| Название компании клиента | ||
| Вид услуги | ||
| Дата подписания | ||
| Дата начала работ | ||
| Дата начала работ | ||
| Дата оплаты | ||
| Сумма контракта | ||
| | ||
| Менеджер |
| Фамилия работника |
| Имя работника |
| Номер телефона |
| Услуга |
| Код услуги |
| Вид услуги |
Кратность -количество возможных экземпляров сущности некоторого типа, которые могут быть связаны с одним экземпляром сущности другого типа с помощью определенной связи. Ограничения кратности описывают способ формирования связи между сущностями. Одной из важных частей моделирования предприятия является обеспечение того, чтобы в модели были выявлены и представлены все соответствующие ограничения предметной области.
Наиболее распространенной степенью связи является двухсторонняя. Двухсторонние связи обычно обозначаются как связи "один к одному" (1:1),"один ко многим" (1:М) или "многие ко многим" (М:N). Также используется понятие «класс принадлежности» экземпляров сущности, он может быть необязательным (на ER-диаграммах обозначается цифрой 0) – если какой-либо экземпляр одной сущности не связан ни с одним экземпляром другой сущности, или обязательным (1) – если все экземпляры одной сущности связаны хотя бы с одним экземпляром другой сущности. Определим связи между заданными сущностями и классы принадлежности:
1)
|
|
|
Рис.1.1. Связь между двумя сущностями
2) сущности «Услуга» и «Контракт» имеют вид связи «один ко многим» т.е. на одну и ту же услугу может быть заключено несколько разных контрактов. Сущность «Контракт» имеет обязательный класс принадлежности, так как не может быть контрактов без предмета его заключения. «Услуга» имеет необязательный класс принадлежности, т.к. могут быть услуги, по которым не заключено ещё ни одного контракта.
|
|
|
Рис 1.2. Связь между двумя сущностями
3) сущности «Контракт» и «Менеджер» также имеют вид связи «один ко многим», т.к. один менеджер может вести несколько контактов, а для каждого контракта назначается только один менеджер. Сущность «Контракт» имеет обязательный класс принадлежности, так как не может быть контрактов, по которым не назначены менеджеры. «Менеджер» имеет класс принадлежности 0, т.е. могут быть менеджеры ещё не ведущие контрактов.
|
|
|
Рис. 1.3. Связь между двумя сущностями
Связи между сущностями проектируемой базы данных представлены на рис. 1.4.
Рис. 1.4. Концептуальная модель базы данных. Связи между сущностями.
Теперь необходимо задать ключи сущностей. Потенциальный ключ - атрибут или минимальный набор атрибутов, который однозначно идентифицирует каждый экземпляр типа сущности. Это означает, что потенциальный ключ не может содержать значения NULL. Первичныйключ - потенциальный ключ, который выбран для однозначной идентификации каждого экземпляра сущности определенного типа. Выбор первичного ключа сущности осуществляется с учетом суммарной длины атрибутов, минимального количества необходимых атрибутов в ключе, а также наличия гарантий уникальности его значений в текущий момент времени и в обозримом будущем, а так же с целью минимизации времени работы со строками.
Таблица Услуги имеет в своем составе уникальное для каждой строки поле, это код услуги. Таблицам Клиент, Контракт и Менеджер в качестве первичных ключей лучше всего назначить некоторый уникальный числовой идентификатор записи: номер клиента, номер контракта, код менеджера.
Сущности Договор и Платеж связаны с другими сущностями по типу «один ко многим». В реляционных базах данных связи между таблицами осуществляются посредством первичных ключей. Поле подчиненной таблицы, по которому осуществляется связь, называется внешним ключом главной таблицы. Таким образом, таблица Договор имеет внешние ключи: Код клиента, Код вида рекламной продукции, Код сотрудника. Таблица Платеж имеет в качестве внешнего ключа поле Номер договора из таблицы Договор.
Логическая модель для связи отношений представлена на рисунке 2 (жирным отмечены первичные ключи, курсивом - внешние ключи).
Рисунок 2 Схема связей между отношениями
2.2 Физическая модель базы данных
Эта модель описывает данные средствами конкретной СУБД. На этом этапе отношение, разработанное с помощью преобразованных ER-диаграмм, превращается в таблицы. Главными вопросами физического проектирования модели являются:
1) Оптимизация времени основных запросов;
2) Обеспечение безопасности выполнения запросов базы данных.
Для повышения эффективности, то есть для ускорения операций поиска или сортировки записей таблицы по значению используют специальные объекты, которые называются индексами. Индекс содержит набор записи из 2 элементов: значение ключевого поля и указатель на соответствующую запись в таблице. Индекс упорядочен по значению ключевого поля, что позволяет системе быстро находить нужные значения. В реляционных СУБД таблицы всегда индексируются по полям первичного ключа.
Так как наша проектируемая база данных «Издательский дом» не большого объема, то индексы использовать не будем.
Для ускоренного поиска записей таблицы необходимо избежать ошибок при вводе данных в таблицу. Это возможно применением условий на возможные значения поля. Например, для удобства ввода данных для таких полей как денежные суммы, даты задаем определенный формат представления информации: денежный с двумя знаками после десятичной точки, краткий формат даты.
Обеспечение безопасности баз данных осуществляется на нескольких уровнях. Во-первых, определяются группы пользователей или отдельные пользователи с различными правами доступа к объектам базы данных. В-вторых, происходит сохранение файла базы данных при различных операциях изменения структуры базы данных, запросов, создания новых таблиц.
3 Реализация базы данных в СУБД Microsoft Access
3.1 Структура таблиц, ключи и индексы
Создадим все таблицы («Книги», «Заказы», «Клиенты») в режиме конструктора: нажимаем на кнопке Создание таблицы в режиме конструктор. В появившемся окне конструктора таблиц определим для каждой таблицы имя поля, тип данных, свойства поля. Конструкторы всех таблиц представлены на рисунках 3 – 5.
Рисунок 3 Конструктор таблицы «Книги»
Рисунок
4
Конструктор таблицы «Заказы»
Рисунок 5 Конструктор таблицы «Клиенты»
Одно из полей таблицы назначим ключевым. Значение в этом поле однозначно определяет запись. Это поле должно быть назначено Обязательным и Индексированным (без повторений). Чтобы назначить это поле ключевым, отметим поле и щелкнем на инструменте Ключ. Закроем окно Конструктора таблиц для сохранения структуры таблицы и зададим имя таблице в окне запроса. Ключевым в таблице «Книги» назначим поле Код книги, в таблице «Заказы» - Дата заказа, в таблице «Клиенты» - Наименование клиента.
Введем данные в таблицы. Для этого выбираем таблицу и нажимаем на кнопку Открыть.
Таблицы с данными представлены на рисунках 6-8.
Рисунок
6
Данные таблицы «Книги»
Рисунок 7 Данные таблицы «Заказы»
Рисунок 8 Данные таблицы «Клиенты»
3.2 Связи между таблицами
Установление связей между таблицами позволяет контролировать целостность и достоверность информации.
Постоянные связи между таблицами были установлены ранее. Для реализации связей таблиц базы данных в СУБД MS Access необходимо нажать в Строке меню кнопку Схема данных. После этого добавляем все ране созданные нами таблицы. Связи между таблицами установим с помощью мыши по методу «зацепить и перетянуть». Зацепим поле Код книги в таблице «Книги»и протащим к такому же полю в таблице «Заказы».Чтобы обеспечить целостности данных вокне диалогаставим флажок напротив Обеспечение целостности данных. После этого на схеме появляется линия, соединяющая соответствующие поля таблиц. Аналогично установим связь таблиц«Заказы» и «Клиенты» по полю Наименование клиента.
Рисунок 9 Окно схемы данных
3.3 Основные запросы
Запрос – процесс обращения пользователя к базе данных с целью ввода, получения или изменения информации в базе данных. Запрос позволяет создать набор записей из данных, находящихся в одной или нескольких таблицах, и использовать его как источник данных для формы или отчета. Запрос может быть выполнен двумя способами: по образцу QBE и при использовании алгоритмического языка SQL. Запросы можно использовать для выполнения следующих операций: вставка новых записей, удаление записей, изменение значений, создание новых полей. Также с помощью запросов можно решать некоторые задачи, связанные с проведением вычислений над данными, хранящимися в Access-таблицах.
Выполним следующие запросы:
- Список всех книг с фамилиями авторов, сгруппированный по областям знаний;
- Проданные книги, сгруппированные по покупателям;
- Книги, проданные конкретному покупателю;
- Список покупателей, сгруппированный по городам;
- Средний ежемесячный объем заказов;
- Средний ежемесячный объем заказов по каждому покупателю;
- Ежемесячный объем продаж книг каждого наименования;
- Бестселлер прошлого года.
3.3.1 Список всех книг с фамилиями авторов, сгруппированный по областям знаний
Создадим запрос «Список всех книг с фамилиями авторов, сгруппированный по областям знаний». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Таблицы выбираем таблицу «Книги», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: Название книги, Автор книги, Область знаний. Для того чтобы поместить эти поля в бланк запроса, нужно дважды нажать кнопкой мыши на имени поля в таблице либо перетянуть название поля из таблицы в бланк запроса, либо выбрать необходимые поля в списке названий полей в бланке запроса.
Для группировки списка по области знаний необходимо в бланке запроса в поле сортировка для поля Область знаний выбрать сортировку по возрастанию. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Список всех книг с фамилиями авторов, сгруппированный по областям знаний» представлен на рисунке 10.
Рисунок 10 Конструктор запроса «Список всех книг с фамилиями авторов, сгруппированный по областям знаний»
Результат выполнения запроса «Список всех книг с фамилиями авторов, сгруппированный по областям знаний» представлен на рисунке 11.
Рисунок 11 Результат выполнения запроса «Список всех книг с фамилиями авторов, сгруппированный по областям знаний»
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 12).
Рисунок 12
SQL
реализация запроса
3.3.2 Проданные книги, сгруппированные по покупателям
Создадим запрос «Проданные книги, сгруппированные по покупателям». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Таблицы выбираем таблицы «Книги» и «Заказы», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: Количество книг, Наименование клиента из таблицы «Заказы» и поле Название книги из таблицы «Книги».
Так как требуется выдать общее количество проданных книг для каждого клиента, используем на панели инструментов пиктограмму Групповые операции (со значком S). В бланке запроса появляется строка Групповые операции. В раскрывающемся списке этой строки выберем требуемое вычисление для группы записей. В нашем случае это вычисление суммы (SUM) для группы записей поля Количество книг.
Для группировки списка по покупателям необходимо в бланке запроса в поле сортировка для поля Наименование клиента выбрать сортировку по возрастанию. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Проданные книги, сгруппированные по покупателям» представлен на рисунке 12.
Рисунок 12 Конструктор запроса «Проданные книги, сгруппированные по покупателям»
Результат выполнения запроса «Проданные книги, сгруппированные по покупателям» представлен на рисунке 13.
Рисунок 13 Результат выполнения запроса «Проданные книги, сгруппированные по покупателям»
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 14).
Рисунок 14
SQL
реализация запроса
3.3.3 Книги, проданные конкретному покупателю
Создадим запрос «Книги, проданные конкретному покупателю». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Запросы выбираем запрос «Проданные книги, сгруппированные по покупателям», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: Наименование клиента, Название книги.
Так как требуется выдать книги, проданные конкретному в бланке запроса в поле условие для поля Наименование клиента используем условие отбора – например, если покупатель Евдокимов Д., тогда «Like Евдокимов Д». Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Книги, проданные конкретному покупателю» представлен на рисунке 15.
Рисунок 15 Конструктор запроса «Книги, проданные конкретному покупателю»
Результат выполнения запроса «Книги, проданные конкретному покупателю» представлен на рисунке 16.
Рисунок 16 Результат выполнения запроса «Книги, проданные конкретному
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 17).
Рисунок 17
SQL
реализация запроса
3.3.4 Список покупателей, сгруппированный по городам
Создадим запрос «Список покупателей, сгруппированный по городам». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Таблицы выбираем таблицу «Клиенты», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: Наименование клиента, Город. Для того чтобы поместить эти поля в бланк запроса, нужно дважды нажать кнопкой мыши на имени поля в таблице либо перетянуть название поля из таблицы в бланк запроса, либо выбрать необходимые поля в списке названий полей в бланке запроса.
Для группировки списка по области знаний необходимо в бланке запроса в поле сортировка для поля Город выбрать сортировку по возрастанию. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Список покупателей, сгруппированный по городам представлен на рисунке 18.
Рисунок 18 Конструктор запроса «Список покупателей, сгруппированный по городам
Результат выполнения запроса «Список покупателей, сгруппированный по городам представлен на рисунке 19.
Рисунок 19 Результат выполнения запроса «Список покупателей, сгруппированный по городам»
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 20).
Рисунок 20
SQL
реализация запроса
3.3.5 Средний ежемесячный объем заказов
Создадим запрос «Средний ежемесячный объем заказов». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Таблицы выбираем таблицы «Книги» и «Заказы», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: Количество книг из таблицы «Заказы» и поле Код книги, Название книги из таблицы «Книги».
Так как требуется выдать средний ежемесячный объем заказов, используем на панели инструментов пиктограмму Групповые операции (со значком S). В бланке запроса появляется строка Групповые операции. В раскрывающемся списке этой строки выберем требуемое вычисление для группы записей. В нашем случае это вычисление среднего значения (AVG) для группы записей поля Количество книг. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Средний ежемесячный объем заказов» представлен на рисунке 21.
Рисунок 21 Конструктор запроса «Средний ежемесячный объем заказов»
Результат выполнения запроса «Средний ежемесячный объем заказов» представлен на рисунке 22.
Рисунок 22 Результат выполнения запроса «Средний ежемесячный объем заказов»
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 23).
Рисунок 23
SQL
реализация запроса
3.3.6 Средний ежемесячный объем заказов по каждому покупателю
Создадим запрос «Средний ежемесячный объем заказов по каждому покупателю». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Таблицы выбираем таблицу «Заказы», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: Количество книг, Наименование клиента из таблицы «Заказы».
Так как требуется выдать средний ежемесячный объем заказов по каждому покупателю, используем на панели инструментов пиктограмму Групповые операции (со значком S). В бланке запроса появляется строка Групповые операции. В раскрывающемся списке этой строки выберем требуемое вычисление для группы записей. В нашем случае это вычисление среднего значения (AVG) для группы записей поля Количество книг, а для поля Наименование клиента оставим значение группировка. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Средний ежемесячный объем заказов по каждому покупателю» представлен на рисунке 24.
Рисунок 24 Конструктор запроса «Средний ежемесячный объем заказов по каждому покупателю»
Результат выполнения запроса «Средний ежемесячный объем заказов по каждому покупателю»представлен на рисунке 25.
Рисунок 25 Результат выполнения запроса «Средний ежемесячный объем заказов по каждому покупателю»
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 26).
Рисунок 26
SQL
реализация запроса
3.3.7 Ежемесячный объем продаж книг каждого наименования
Создадим запрос «Ежемесячный объем продаж книг каждого наименования». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Таблицы выбираем таблицы «Книги» и «Заказы», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: Дата заказа из таблицы «Заказы» и поля Название книги, Код книги из таблицы «Книги».
Так как требуется выдать ежемесячный объем продаж книг каждого наименования, то в бланке запроса создадим поле, в котором будет вычисляться объем продаж каждого наименования. Для создания такого поля в свободном столбце конструктора запросов записывают новое имя поля, знак “:”, а потом выражение, которое использует имена других полей и вычисляет необходимое значение. Например, вычислим объем продаж книг, если есть поля Оптовая цена книги и Количество книг. В новом столбце запишем:
Объём продаж: Заказы![Количество книг]*Книги![оптовая цена]
Сначала записываются имена таблиц, а после символа ! записываются имена полей в квадратных скобках.
Так как требуется выдать объем продаж книг каждого наименования за конкретный месяц, то в бланке запроса в поле условие для поля Дата заказа используем условие отбора – например, если за февраль, тогда запишется <#01.03.2006# And >#01.02.2006#. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Ежемесячный объем продаж книг каждого наименования» представлен на рисунке 27.
Рисунок 27 Конструктор запроса «Ежемесячный объем продаж книг каждого наименования»
Результат выполнения запроса «Ежемесячный объем продаж книг каждого наименования» представлен на рисунке 28.
Рисунок 28 Результат выполнения запроса «Ежемесячный объем продаж книг каждого наименования»
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 29).
Рисунок 29
SQL
реализация запроса
3.3.8 Бестселлер прошлого года
Создадим запрос «Бестселлер прошлого года». Для этого нужно перейти на вкладку Запросы, и выбрать Создание запроса в режиме конструктора. В окне Добавление таблицы на вкладке Запросы выбираем запрос «Проданные книги, сгруппированные по покупателям», нажимаем кнопку Добавить. Закроем окно Добавление таблицы. Теперь выберем те поля таблицы, которые необходимо включить в запрос. Выберем поля: SUM-Количество книг, Название книги.
Так как требуется выдать книгу, проданную максимальное количество раз, то используем на панели инструментов пиктограмму Групповые операции (со значком S). В бланке запроса появляется строка Групповые операции. В раскрывающемся списке этой строки выберем требуемое вычисление для группы записей. В нашем случае это вычисление суммы (SUM) для группы записей поля Количество книг, а для поля Название книги оставим значение группировка. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Так как нужно выдать самую популярную книгу, то необходимо в бланке запроса в поле сортировка для поля SUM-Количество книг выбрать сортировку по убыванию. Для запуска запроса нажимаем пиктограмму Запуск на панели Конструктор запросов.
Конструктор запроса «Бестселлер прошлого года» представлен на рисунке 30.
Рисунок 30 Конструктор запроса «Бестселлер прошлого года»
Результат выполнения запроса «Бестселлер прошлого года» представлен на рисунке 31.
Рисунок 31 Результат выполнения запроса «Бестселлер прошлого года»
Для просмотра SQL-реализации запросов необходимо на панели Конструктор запросов выбрать Вид – Режим
SQL (рисунок 32).
Рисунок 32
SQL
реализация запроса
3.4 Стандартные формы и отчеты
3.4.1 Форма «Список всех книг с фамилиями авторов, сгруппированный по областям знаний»
Формы представляют собой объект базы данных, в котором разработчик размещает объекты управления, принимающие действия пользователя или служащие для ввода, отображения или изменения данных. Формы позволяют видеть столько данных из таблицы, сколько потребуется. При этом формат представления также можно выбрать по своему усмотрению. При работе с Формой мы наблюдаем те же данные, что и в таблице, но представленные в другом виде. Если производится редактирование данных в таблице, то Access обновляет соответствующую информацию в Форме.
Создадим форму для запроса «Список всех книг с фамилиями авторов, сгруппированный по областям знаний». Для этого нужно перейти на вкладку Формы, и выбрать Создание формы с помощью мастера. Выберем все поля запроса «Список всех книг с фамилиями авторов, сгруппированный по областям знаний», далее выберем внешний вид формы - в один столбец, после этого определим стиль формы – стандартный, нажимаем Готово. Конструктор формы Список всех книг с фамилиями авторов, сгруппированный по областям знаний» представлен на рисунке 33.
Рисунок 33 Конструктор формы «Список всех книг с фамилиями авторов, сгруппированный по областям знаний»
Форма «Список всех книг с фамилиями авторов, сгруппированный по областям знаний» представлена на рисунке 34.
Рисунок 34 Форма «Список всех книг с фамилиями авторов, сгруппированный по областям знаний»
3.4.2 Форма «Список покупателей, сгруппированный по городам»
Создадим форму для запроса «Список покупателей, сгруппированный по городам». Для этого нужно перейти на вкладку Формы, и выбрать Создание формы с помощью мастера. Выберем все поля запроса «Список покупателей, сгруппированный по городам», далее выберем внешний вид формы - ленточный, после этого определим стиль формы, нажимаем Готово. Конструктор формы «Список покупателей, сгруппированный по городам» представлен на рисунке 35.
Рисунок 35 Конструктор формы «Список покупателей, сгруппированный по городам»
Форма «Список покупателей, сгруппированный по городам» представлена на рисунке 36.
Рисунок 36 Форма «Список покупателей, сгруппированный по городам»
3.4.3 Отчет «Проданные книги, сгруппированные по покупателям»
При работе с БД часто бывает нужно распечатать необходимую информацию. Это можно сделать при помощи отчетов. Отчеты предназначены исключительно для вывода данных на печать. В отчетах приняты специальные меры для группирования вводимых данных для вывода специальных элементов оформления, характерных для печатных документов (верхний и нижний колонтитулы, номера страниц, служебная информация о времени создания отчета и т.п.).
Создадим отчет о проданных книгах, сгруппированных по покупателям. Для этого нужно перейти на вкладку Отчеты, и выбрать Создание отчета с помощью мастера. Выберем все поля запроса «Проданные книги, сгруппированные по покупателям», далее выберем уровни группировки. Так как требуется создать о проданных книгах, сгруппированных по покупателям, то выбираем группировку наименованию клиента. В следующем окне выбираем порядок сортировки поля «Sum-количество книг» по возрастанию. Далее выбираем вид макета - структура 1 и стиль отчета – деловой. Даем название отчету «Проданные книги, сгруппированные по покупателям». Нажимаем Готово.
Конструктор отчета «Проданные книги, сгруппированные по покупателям» представлен на рисунке 37.
Рисунок 37. Конструктор отчета «Список всех напечатанных книг»
Литература
1. Базы данных: модели, разработка, реализация / Т.С.Карпова.-Спб.:Питер,2001.-304с.:ил.
2. Заикин И. А., Учебное пособие «Основы работы в Access», 2003
3. Марков А.С., Лисовский К.Ю. Базы данных. Введение в теорию и методологию: Учебник.-М.: Финансы и статистика.2006.-512с.:ил.
4. Методические указания к лабораторным работам по курсу «Базы данных» Microsoft Access, В.И.Долбенков, 2005
5. Методология проектирования баз данных средствами MS Access: Учебно-методическое пособие по курсу «Базы данных», 2005
