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

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

 

 

 

 

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

 

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

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

Вариант №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 записей)

 

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