Обработка и создание базы данных

«ПЕТЕРБУРГСКИЙ  ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ  СООБЩЕНИЯ»

 

 

 

 

Кафедра «ИНФОРМАТИКА И ИНФОРМАЦИОННАЯ БЕЗОПАСНОСТЬ»

 

КУРСОВАЯ РАБОТА

НА ТЕМУ: «СОЗДАНИЕ  И ОБРАБОТКА БАЗ ДАННЫХ»

Вариант №17

 

 

 

 

 

 

Проверил профессор      Дергачёв А. И

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Санкт-Петербург

2011

СОДЕРЖАНИЕ

 

Введение

В курсовой работе рассматриваются основные особенности создания, редактирования, хранения баз данных в среде Microsoft Office. Сначала рассматриваются особенности реализации баз данных (БД) в приложении MS Excel, а затем в MS Access.

После уточнения формирования исходных данных на конкретном примере, заданном вариантом индивидуального задания, разрабатываются запросы. На основании  запросов определяются основные характеристики БД: количество полей, тип данных.

Реализация запросов и вычислений в MS Excel, в том числе с использованием макросов, позволяет продемонстрировать основные возможности работы с БД в среде на конкретных примерах иллюстрируется работа запросов в M Excel.

Проиллюстрированы варианты реализации запросов в M Access.

Постановка задачи

Спроектировать базу данных «Улицы Петербурга» в Excel и MS Access, содержащую следующие данные:

База данных должна содержать следующие  элементы:

- название улицы;

- длина;

- ширина проезжей части;

- наличие трамвайных путей;

- наличие контактной сети для троллейбусов;

- название района города, где  пролегает улица;

- информация о предприятии СпецАвтотранса, занимающегося уборкой улицы  (название, директор, телефон).

Функции, выполняемые информационной технологией:

1. Заполнение и редактирование таблиц базы данных.
2. Формирование списка улиц, имеющих трамвайные пути, площадь которых не менее запрашиваемой величины и имеющих контактную сеть для троллейбусов.
3. Формирование списка улиц, за чистотой на которых следит выбранное пользователем предприятие СпецАвтотранса, с подсчетом их количества и общей площади обслуживаемой территории с указанием фамилии директора и номера телефона предприятия.
4. Вывод сведений об улице, имеющей максимальную длину, с указанием названия, длины и района города.
5. Формирование отчета, включающего полную информацию об улицах с группировкой по районам и подсчетом средней протяженности улиц, проходящих по каждому району.

 

Реализация базы данных в Microsoft Excel

1. Заполнение и редактирование таблицы базы данных

Структура базы данных:

содержит 9 полей (Название улицы, Длина[м], Ширина [м], Трамвайные пути, Контактная сеть для троллейбусов, Район города, Название предприятия (в поле Название предприятия записаны названия фирм осуществляющие уборку улиц), Директор,  Телефон) и 34 записи.

Таблица заполняется записями с  помощью Стандартной формы для этого, выделяется диапазон ячеек, содержащий заголовки полей базы данных A2:I2 в меню Данные выбирается команда Форма.

Рисунок 1 – Форма заполнения базы данных

Результат:

Рисунок 2 - Заполненная база данных

2. Формирование списка улиц, имеющих трамвайные пути, площадь которых не менее запрашиваемой величины и имеющих контактную сеть для троллейбусов.

В качестве иллюстрации выполненной  фильтрации данных рассмотрим вариант  выборки улицы, площадь которой не меньше 50000.

• Для подсчета площади улиц добавляем еще одно поле Площадь улицы (м2)в ячейку J3 вводится расчетная формула для нахождения площади =B3*C3

Рисунок 3 – Расчет площади улицы

• Формула из ячейки J3 автокопированием копируется на весь диапазон J3:J36 (все лишние поля таблицы скрыты)

Рисунок 4 – Результат расчета  площади улиц

• Отображаются скрытые столбцы контекстное меню при выделении заголовков столбцов между скрытыми столбцами и выбираем команду Отобразить.
• Табличный курсор устанавливается на любом элементе списка и выполняется команда Фильтр->Автофильтр из меню Данные.
• В таблице рядом с названиями столбцов отобразятся кнопки раскрывающихся списков.
• Необходимо раскрыть список поля Трамвайные пути, по значению которого будет осуществляться выборка записей и выбрать из списка значение да.

Рисунок 5 – Применение фильтра  по полю Трамвайные пути

• Второй критерий выборки записей задается в поле Площадь улицы [м2]. Из раскрывающегося списка выбирается вариант задание условия и в раскрывшемся диалоговом окне задания условия задаем нужное значение.

Рисунок 6 – Применение фильтра по полю Площадь улицы [м2]

Рисунок 7 – Условия фильтрации

• Третий критерий выборки необходимо раскрыть список поля Контактная сеть для троллейбуса, по значению которого будет осуществляться выборка записей и выбрать из списка значение Да.

Рисунок 8 – Применение фильтра  по полю Контактная сеть для троллейбуса

Визуальный контроль результатов:

Рисунок 9 – Результат фильтрации данных

3. Формирование списка улиц, за чистотой на которых следит выбранное пользователем предприятие СпецАвтотранса, с подсчетом их количества и общей площади обслуживаемой территории с указанием фамилии директора и номера телефона предприятия.

Сформируем список для предприятия  ОАО “Анекр”.

• В меню Данные выбирается команда Сводная таблица
• На первом шаге мастера формирования Сводной таблицы выбирается источник данных для отчета

Рисунок 10 – Шаг1 мастера сводных таблиц

• На втором шаге мастера формирования Сводной таблицы задается диапазон с исходными данными

Рисунок 11 – Шаг2 мастера сводных  таблиц

- далее формируется макет сводной  таблицы

Рисунок 12 – Макет сводной таблицы

 

• Выбирается месторасположение Сводной таблицы

Рисунок 13 – Шаг3 мастера сводных  таблиц

• В сформированной таблице в поле Название предприятия выбираем нужное предприятие ОАО “Анекр”

Рисунок 14 – Применение фильтра  по полю Название предприятия

Визуальный контроль результатов:

Рисунок 15 – Результат формирования списка

4. Вывод сведений об улице, имеющей максимальную длину, с указанием названия, длины и района города.

• Для формирования сведений об улице сформируем диапазон критериев для расширенного фильтра

Рисунок 16 – Диапазон критериев

• В меню Данные выбирается команда Фильтр->Расширенный фильтр
• Задаются параметры расширенного фильтра

Рисунок 17 – Параметры расширенного фильтра

Сформированный сведения:

Рисунок 18 – Результат сведения об улице, имеющей максимальную длину

5. Формирование отчета, включающего полную информацию об улицах с группировкой по районам и подсчетом средней протяженности улиц, проходящих по каждому району.

• В меню Данные выбирается команда Сводная таблица
• На первом шаге мастера формирования Сводной таблицы выбирается источник данных для отчета

Рисунок 19 - Шаг1 мастера сводных  таблиц

• На втором шаге мастера формирования Сводной таблицы задается диапазон с исходными данными

Рисунок 20 – Выбор диапазона

- далее формируется макет сводной  таблицы

Рисунок 21 – Макет сводной таблицы

• Выбирается месторасположение Сводной таблицы

Рисунок 22 – Выбор места размещения отчета

Сформированный отчет:

Рисунок 21 – Отчет

6. Создание макроса

Создадим макрос вычисления Площади  улицы

• Выделяем ячейку J3
• В меню Сервис выбираем команду Макрос->Начать запись

Рисунок 22 – Начало записи макроса

• Выполняем действия по вычисления площади улицы
• В меню Сервис выбираем команду Макрос->Остановить запись
• Далее  «Сервис – Настройки – Команды – Макросы» создаем новую панель инструментов и переносим кнопку записка макроса на панель инструментов

Рисунок 23 – Создание кнопки для запуска макроса

• Теперь если выделить следующую ячейку для расчета, например, ячейку J4, нажимаем кнопку на панели Макросы и получаем результат расчета.

7. Построение диаграммы

Построение круговой диаграммы  «Площадь улицы»

• Выделяем столбец таблицы «Площадь улицы».
• Запускаем Мастер диаграмм (Вставка – Диаграмма…).
• Выбираем тип диаграммы.
• Выбираем закладку Ряд и устанавливаем соответствующие значения в полях ввода. 

Рисунок 24 – Исходные данные

• Выбираем закладку Подписи данных и устанавливаем флажок на закладке Значения

Рисунок 25 – Подписи данных

• Последний шаг мастера позволяет выбрать один из вариантов расположения графика: на отдельном листе рабочей книги (новом) или на текущем листе.

Рисунок 26 – Готовая диаграмма

 

Построение гистограммы «Ширина  [м]»

• Выделяем столбец таблицы «Ширина [м]».
• Запускаем Мастер диаграмм (Вставка – Диаграмма…).
• Выбираем тип диаграммы.
• Выбираем закладку Ряд и устанавливаем соответствующие значения в полях ввода.
• Последний шаг мастера позволяет выбрать один из вариантов расположения графика: на отдельном листе рабочей книги (новом) или на текущем листе.

Рисунок 27 – Готовая диаграмма

Реализация базы данных в Microsoft Access

1. Заполнение и редактирование таблицы базы данных

Структура базы данных:

В конфигураторе создано три  таблицы

Рисунок 28 – Схема данных

 

Таблица Предприятия СпецАвтотранса, содержит данные о предприятиях занимающихся уборкой улиц (содержит 7 записей)

Для поля Телефон задана маска ввода 000\-00\-00

 

Рисунок 29 – Конструктор таблицы  Предприятия СпецАвтотранса

 

Рисунок 30 – Таблица Предприятия  СпецАвтотранса

 

Таблица Районы города, содержит названия всех районов города (содержит 18 записей)

 

Рисунок 31 – Конструктор таблицы  Районы города

 

Рисунок 32 – Таблица Районы города

 

Таблица Улицы Петербурга, содержит данные об улицах Санкт-Петербурга разных районов города (содержит 34 записей)

 

• В конфигураторе для поля Код района задана подстановка из таблицы Районы города
• Для задания подстановки при указании типа данных для поля Код района выбирается Мастер подстановок
• На первом шаге работы мастера выбирается источник данных это таблица Районы города

Рисунок 33 – Создание подстановки

• На втором шаге работы мастера выбираем необходимы поля для подстановки

Рисунок 34 – Второй шаг создания подстановки

• На третьем шаге работы мастера выбирается вид сортировки записей

Рисунок 35 – Третий шаг создания подстановки

 

• Задается ширина столбцов

Рисунок 36 – Четвертый шаг создания подстановки

 

• Выбирается столбец подстановки

Рисунок 37 – Пятый шаг создания подстановки

 

Рисунок 38 – Конструктор таблицы  Улицы Петербурга

 

• для поля Код предприятия задана подстановка из таблицы Предприятия СпецАвтотранса
• Для задания подстановки при указании типа данных для поля Код предприятия выбирается Мастер подстановок
• На первом шаге работы мастера выбирается источник данных это таблица Предприятия СпецАвтотранса

 

Рисунок 39 – Создание подстановки

Рисунок 40 – Первый шаг создания подстановки

• На втором шаге работы мастера выбираем необходимы поля для подстановки

 

Рисунок 41 – Второй шаг создания подстановки

• На третьем шаге работы мастера выбирается вид сортировки записей

Рисунок 42 – Сортировка в подстановке

• Задается ширина столбцов

Рисунок 43 – Создание подстановки

• Выбирается столбец подстановки

Рисунок 44 – Создание подстановки

Рисунок 45 – Создание подстановки

Рисунок 46 – Конструктор таблицы  Улицы Петербурга

Рисунок 47 – Таблица Улицы Петербурга

2. Формирование списка улиц, имеющих трамвайные пути, площадь которых не менее запрашиваемой величины и имеющих контактную сеть для троллейбусов.

В качестве иллюстрации выполненной  фильтрации данных рассмотрим вариант  выборки улицы, площадь которой  не меньше 50000.

Формирование запроса:

• Выбирается объект Запросы
• Выбирается команда Создать запрос в режиме конструктора
• В качестве источника данных выбирается таблица Улицы Петербурга
• Выбираются необходимые поля для формирования запроса
• Используя Построитель выражений добавляется поле Площадь улицы (м2) содержащее выражение результатом решения которого является значение площади улицы

Рисунок 48 – Построитель выражения

• Для полей задаются критерии отбора записей
• Для поля Площадь улицы (м2) критерий >=50000 для поля Трамвайные пути значение ДА, для Контактная сеть для троллейбуса ДА.

Конструктор запроса:

Рисунок 49 – Конструктор запроса

• Сохранение запроса Запрос1

Результат запроса:

Рисунок 50 – Результат запроса

3. Формирование списка улиц, за чистотой на которых следит выбранное пользователем предприятие СпецАвтотранса, с подсчетом их количества и общей площади обслуживаемой территории с указанием фамилии директора и номера телефона предприятия.

Сформируем список для предприятия  ОАО “Анекр”.

 

Формирование запроса:

• Выбирается объект Запросы
• Выбирается команда Создать запрос в режиме конструктора
• В качестве источника данных выбирается таблица Улицы Петербурга и Предприятия СпецАвтотранса
• Выбираются необходимые поля для формирования запроса
• Для поля Название предприятия задается критерии отбора записей
• Сохранение запроса Запрос2

Конструктор запроса:

Рисунок 51 – Конструктор запроса

• На основе созданного запроса создается отчет для подсчета итоговых данных

Рисунок 52 – Конструктор отчета

Результат:

При запуске запроса пользователю будет предложено ввести название предприятия, которое занимается уборкой улиц, данные о которых надо получить.

Рисунок 53 – Критерий отбора записей

Рисунок 54 – Результаты отчета

4. Вывод сведений об улице, имеющей максимальную длину, с указанием названия, длины и района города.

Формирование запроса:

• Выбирается объект Запросы
• Выбирается команда Создать запрос в режиме конструктора
• В качестве источника данных выбирается таблица Улицы Петербурга и Районы города
• Выбираются необходимые поля для формирования запроса
• Для поля Длина(м) задается условие отбора
• Сохранение запроса Запрос3

Конструктор запроса:

Рисунок 55 – Конструктор запроса

Результат запроса:

Рисунок 56 – Результат запроса

5. Формирование отчета, включающего полную информацию об улицах с группировкой по районам и подсчетом средней протяженности улиц, проходящих по каждому району.

Формирование отчета:

• Выбирается объект Отчеты
• Выбирается команда Создать отчета с помощью мастера
• В качестве источника данных выбирается таблица Районы города, Улицы Петербурга, Предприятия СпецАвтотранса
• Выбираются необходимые поля для формирования отчета

Рисунок 57 – Выбор источника данных

• На следующем шаге работы мастера создания отчетов задается вид представления данных

Рисунок 58 – Выбор представления данных

• Задается сортировка данных и добавляются итоги по полю Длина (м)

Рисунок 59 – Задание сортировки

Рисунок 60 – Задание итогов

Рисунок 61 – Выбор макета

Рисунок 62 – Выбор стиля

• Сохранение отчета Улицы Петербурга

Конструктор отчета:

Рисунок 63 – Конструктор отчета

Результат отчета:

Рисунок 64 – Результат отчета

Заключение

Программы Microsoft Excel и Microsoft Access позволяют решить одну и ту же задачу двумя немного схожими способами. Оба приложения  позволяют выполнять разнообразные запросы для сортировки и фильтровки данных, производить сложные расчеты для получения необходимых данных, составлять отчеты по данным и просматривать их в разных вариантах. В обеих программах данные организованы в виде столбцов, или полей, в которых хранится информация определенных видов или типов данных, то есть в виде таблиц, что упрощает работу с ними. Только в Excel достаточно создать одну таблицу, а в Access необходимо создавать несколько. Но при желании в Microsoft Excel тоже можно составлять несколько таблиц на разных листах. Первая ячейка вверху каждого столбца отводится для метки столбца. Имеется одно терминологическое различие: в Microsoft Excel строка, в Microsoft Access запись. Microsoft Exсel – это программа управления электронными таблицами, в которых элементы данных хранятся в ячейках строк и столбцов, образующих листы, а Microsoft Access представляет собой программу управления базами данных. Наиболее типичной задачей, выполняемой в Microsoft Exсel, является управление списками данных, например списками адресов или сведений о персонале или технике. В Microsoft Access же данные хранятся в таблицах, которые очень похожи на листы Microsoft Excel, но рассчитаны на выполнение сложных запросов данных, находящихся в других таблицах и хранилищах, и даже в полях других таблиц.

Microsoft Excel удобно использовать, если  объем данных не слишком велик,  данные представлены в простой форме, и к ним достаточно создать одну таблицы.

Microsoft Access часто используется, при  работе с большим объемом информации, и если требуется выполнить  сложные запросы и отчеты.

Работа с программами особых трудностей не вызвала. Учитывая небольшой объем обрабатываемой информации удобнее было работать в Excel, но Access тоже понравился. На мой взгляд, Access предоставляет больше возможностей при обработке информации. В этой программе дольше происходит первичная обработка данных (создание БД, запросов, и отчетов), но после удобнее пользоваться ими за счет возможности запрашивать только то, что необходимо. Excel тоже позволяет фильтровать данные, но таблица сохраняет все столбцы, включая не нужные в данный момент.

Список использованной  литературы

1. Симонович С.В., Евсеев Г.А., Мураховский В. И., Бобровский С.И. Информатика. Базовый курс. СПб, издательство «Питер», 2007.
2. Электронный самоучитель: http://www.taurion.ru
3. Конспект по информатике.