Создание и обработка базы данных
Петербургский Государственный
Университет Путей Сообщения Императора Александра I
Кафедра «Информатика и Информационная
безопасность»
Пояснительная записка к курсовой
работе
На тему: «Создание и обработка базы
данных»
Вариант 23
Выполнил:
Студент группы СЖД-304
Храмцов Д.С.
Санкт-Петербург 2014
Оглавление
Введение
1. Постановка задачи
2. Электронная таблица
Excel
2.1 Построение графика функции
2.2 Заполнение таблицы
Excel
2.3 Вычисление полной
стоимости обслуживания в каждом городе
2.4 Формирование списка
городов, стоимость посещение которых превыашет стоимость, заданную
пользователем
2.5 Получение данных о
городе, в котором стоимость проживания в гостинице максимальна
3. Основные положения Microsoft Access
3.1 Заполнение таблиц
3.2 Вычисление полной
стоимости обслуживания в каждом городе
3.3 Формирование списка городов, стоимость посещение
которых превыашет стоимость, заданную пользователем
3.4 Получение данных о
городе, в котором стоимость проживания в гостинице максимальна
3.5 Создание отчета
Заключение
Список использованной
литературы
Введение
Рассматриваются основные особенности создания, редактирования, хранения и
передачи заказчику баз данных в среде MicrosoftOffice. Для получения оценок сначала рассматриваются особенности реализации
баз данных (БД) в приложении MExcel,
а затем в MAccess.
Послу уточнения формирования исходных данных на конкретном примере,
заданном вариантом индивидуального задания, разрабатываются запросы. На
основании запросов определяются основные характеристики БД: количество полей,
тип данных, точность используемых числовых значений и т.д.
Реализация запросов в MExcel,
в том числе с использованием макросов, позволяет определить основные
характеристики таблицы: количество ячеек. Затем на конкретных примерах
иллюстрируется работа запросов в М Excel.
Анализ исходной таблицы позволяет заключить о целесообразности её деления
на части с целью уменьшения условной площади (количества ячеек). На
рассматриваемом примере показана эффективность деления исходной таблицы на две
части.
Проиллюстрированы варианты реализации запросов в MAccess.
Основные положения обоснованы и проиллюстрированы.
1.
Постановка задачи
Спроектировать базу данных в Excel, содержащую следующие данные:
Входные данные:
·
наименование
города, входящего в маршрут;
·
стоимость проезда
до соответствующего города;
·
классность номера
гостиницы;
·
суточная
стоимость питания;
·
суточная
стоимость проживания;
·
количество дней
пребывания в городе;
·
стоимость
экскурсий по городу.
Выходные данные:
1. Заполнение и редактирование таблиц базы данных.
. Вычисление полной стоимости обслуживания в каждом городе.
. Формирование списка городов, стоимость посещения которых превышает
стоимость, заданную пользователем (наименование города, количество дней,
стоимость).
. Получение данных о городе, в котором стоимость проживания в гостинице
максимальна (наименование города, классность номера, стоимость проживания).
. Формирование отчёта с подведением итогов по каждому виду услуг и
указанием полной стоимости туристского маршрута и количества дней пребывания на
маршруте.
Примечание. Суточная стоимость питания и проживания в номерах одного
класса во всех городах одинакова.
2.
Электронная таблица Excel
2.1 Построение графика функции
Построение графика функции
Рис.
1
Для
этого вводим формулу в ячейке С5:
=ЕСЛИ(B5<0;B5/2;ЕСЛИ(И(B5>=0;B5<=1,4);(B5^2)-
*B5+6;ЕСЛИ(И(B5>1,4);-2*B5^(1/2))))
Затем
за уголок растягиваем до ячейки С22.
Программа
Excel входит в офисный пакет программ MicrosoftOffice и предназначена для подготовки и обработки
электронных таблиц под управлением операционной оболочки Windows.
Программа Excel относится к основным офисным компьютерным технологиям
обработки числовых данных.
На
пересечении столбца и строки располагается основной элемент таблицы - ячейка. В
любую ячейку можно ввести исходные данные - число, текст, а также формулу для
расчета производной информации. Ширину столбца или строки можно менять при
помощи мыши. При вводе данных в ячейку это происходит автоматически, т.е.
электронные таблицы являются «резиновыми».
Excel позволяет
пересчитывать значения элементов таблицы по формулам, строить по данным таблицы
различные графики, создавать простейшие базы данных и т.д.
В
Excel файл сохраняется как книга. Книга состоит из листов,
следовательно, Excel можно рассматривать как «трехмерную таблицу».
Рис. 2
База данных в Excel - это список
связанных данных, в котором строки данных являются записями, столбцы - полями.
Верхняя строка списка содержит название каждого из столбцов. Ссылка может быть
задана либо как диапазон ячеек, либо как имя, соответствующему диапазону
списка.
Для ввода информации в ячейки рабочего листа вначале следует сделать
ячейку активной, переместив в неё курсор и щелкнуть один раз правой кнопкой
мыши. После этого можно либо просто вводить данные с клавиатуры, либо щелкнуть
левой кнопкой мыши в строке формул над рабочим листом и после этого вводить
данные.
2.2 Заполнение таблицы Excel
2.3 Вычисление полной стоимости
обслуживания в каждом городе
Чтобы вычислить полную стоимость обслуживания в каждом городе, необходимо
создать новый столбец «» в ячейку первой строки данных вписываем формулу
((Суточная стоимость питания + Суточная стоимость проживания) * Количество дней
пребывания в городе + Стоимость экскурсий). Для этого выделяем ячейку первой
строки данных столбца «Полная стоимость» и в строке формул ставим знак равно,
пишем знак (, после этого кликаем на суточную стоимость первой гостиницы, пишем
знак +, кликаем на суточную стоимость проживания, пишем знак ), пишем знак *,
кликаем на количество дней пребывания в городе, пишем знак +, кликаем на стоимость
экскурсий, и нажимаем Enter.
Чтобы формула действовала во всех ячейках столбца, необходимо растянуть ячейку
с формулой за нижний правый уголок.
Рис. 3
2.4 Формирование списка городов,
стоимость посещение которых превыашет стоимость, заданную пользователем
Чтобы получить список городов, стоимость посещения которых превышает
стоимость, заданную пользователем нужно создать макрос. Для этого нажимаем
«Разработчик - Вставить - элементы управления формы» там выбираем первый значок
«кнопка». Растягиваем её в свободной ячейке и в выплывшем окне «назначить
макрос объекту» вводим название кнопки, а потом кликаем на «Записать».
Рис. 4
Во втором выплывшем окне «Запись макроса» вводим название макроса.
Рис. 5
Рис. 6
Для этого создаем кнопку, как в вышеописанном случае. Далее нажимаем
«Данные - Фильтр», кликаем на стрелочку рядом с названием ячейки «Полная
стоимость обслуживания - Текстовые фильтры - больше или равно», в выплывшем
окне «пользовательский автофильтр» в правой верхней строчке ставим «*»,
нажимаем «ок».
.5 Получение данных о городе, в котором стоимость проживания в гостинице
максимальна
Для того чтобы получить данные о городе, в котором стоимость проживания в
гостинице максимальна, необходимо создать макрос. Далее кликаем на вкладку
«Данные - Фильтр». В ячейке «Суточная стоимость проживания» щелкаем на
стрелочку рядом с именем ячейки, кликаем на «сортировка по убыванию», потом на
«числовые фильтры - первые 10», во выплывшем окне вместо цифры 10 ставим 1.
Потом нажимаем вкладку «Разработчик» и там «остановить запись». Затем щелкаем
правой кнопкой мыши на кнопку, и «назначить макрос». В выплывшем окне выбираем
наш макрос.
Рис. 7
Далее во вкладке «Разработчик» нажимаем «остановить запись», затем правой
кнопкой мыши щелкаем на «кнопку», «назначить макрос», выбираем наш макрос и
щелкаем на «править». В выплывшем окне ищем наш макрос, и в строке
ActiveSheet.ListObjects("Таблица3").Range.AutoFilter Field:=8,
Criteria1:="=*"» после «Criterial:=» пишем InputBox ("Введите стоимость"), предварительно стерев «"=*"».
Рис. 8
3.
Основные положения Microsoft Access
MicrosoftAccess - это система управления базами данных, предназначенная для
создания и обслуживания баз данных, обеспеченная доступа к данным и их
обработки.
База данных представляет собой организованную структуру, используемую для
хранения данных, т.е. любых сведений о явлениях, процессах, действиях и т.д.
Данные несут в себе информацию о событиях, происходящих в материальном мире, и
по сути являются зарегистрированными сигналами, возникшими в результате этих
событий.
Базы данных содержат различные объекты, основными из которых являются
таблицы. Структура простейшей базы данных соответствует структуре простейшей
базы данных являются поля и записи.
Создание таблиц в режиме конструктора
Таблицы - это основные объекты БД, хранят все данные базы, структуру базы
(поля, их типы, свойства). Для создания структуры первой таблицы выбрать
вкладку Создание - группа Таблицы - режим Конструктор таблиц. Последовательно
ввести поля первой таблицы с указанием их типа и свойств (размер поля, формат
поля, подпись и т.д.).
Для связи создаваемой таблицы с другими таблицами задать ключевое поле.
Для этого выделить поле и в группе Сервис выбрать кнопку Ключевое поле.
Структуры других таблиц создаются аналогично. Для редактирования
структуры таблиц используется режим Конструктор, переход к нему - группа
Главная - Режимы - Режим.
В работе созданы две таблицы. Ниже эти таблицы представлены в режиме
Конструктор.
После оформления таблиц, создаются межтабличные связи.
Рис. 9
Связи позволяют черпать данные из разных таблиц, обеспечивают целостность
данных. Для создания связей между таблицами выбрать вкладку Работа с базами
данных - группа Отношения - кнопка Схема данных, после чего появляется окно
«Схема данных».
Для добавления в связь таблиц в группе Связи выбирается кнопка
«Отобразить таблицу», после чего появляется окно «Добавление таблицы», где
выбираются таблицы для связи. В окне «Схема данных» становятся видны списки полей
этих таблиц. Для установки связи ключевое поле нужной таблицы «перетаскивается»
на аналогичное поле другой. Появляется диалоговое окно «Изменение связей», в
котором указаны связываемые поля, тип отношения (один-к-одному или
один-ко-многим). Необходимо включить переключатель для обеспечения целостности
данных. После установки связей в окне «Схема данных» видна образовавшаяся
связь. Контекстное меню линии связи (или кнопка Изменить связи в группе Сервис)
позволит редактировать или удалить связь.
Ниже представлены созданные межтабличные связи.
Рис. 9
.1 Заполнение таблиц
Ввод данных в таблицу может осуществляться как через формы, так и
непосредственно в режиме таблицы, переход к нему - группа Главная - Режимы -
Режим. Новая таблица не имеет записей, содержит только наименования полей.
В требуемую ячейку устанавливается курсор. Переход от поля к полю
осуществляется с помощью клавиши [Tab]; в предыдущее поле можно попасть с
помощью комбинации клавиш [Shift + Tab]. Посредством клавиш [вверх] и [вниз]
осуществляются перемещения между строками таблицы. Как только будет закончен
ввод записи, то есть строки таблицы, воспользовавшись клавишей [Tab], перейдем
к первой ячейке следующей строки, а Access автоматически сохранит только что
веденную запись.
Таблицы, заполненные данными, приведены ниже.
Рис. 10
Для удаления записей их предварительно необходимо промаркировать с
помощью селекторной колонки, а затем нажать на клавишу [Delete]. Данные,
занесенные в таблицы, можно копировать в буфер обмена, сортировать по
возрастанию или убыванию т.д.
3.2 Вычисление полной стоимости
обслуживания в каждом городе
При вычислении стоимости телефонов, необходимо создать запрос. Для этого
нажимаем - Создание - конструктор запросов.
Рис. 11
В выплывшем окне выбираем таблицу «Город» и нажимаем добавить, затем
выбираем таблицу «Обслуживание» и нажимаем добавить.
Рис. 12
Теперь в первом столбике в строке [Поле:] щелкаем на стрелочку и выбираем
«Название Города», во втором столбце «Количество дней пребывания в городе», в
третьем столбце необходимо ввести формулу: Полная стоимость обслуживания:
([Обслуживание]![Суточная стоимость проживания]+[Обслуживание]![Суточная
стоимость питания])*[Город]! [Количество дней пребывания в
городе]+[Город]![Стоимость экскурсий]
Рис. 13
После этого нажимаем крестик в правом углу и сохряняем изменения.
Затем выполняем Запрос.
Результат запроса:
Рис. 14
3.3 Формирование списка городов,
стоимость посещение которых превышает стоимость, заданную пользователем
Создаем запрос и выбираем 3 таблицы: «Город», «Обслуживание» и «Полная
стоимость обслуживания». В первом столбике выбираем «Название города», во
втором «Количество дней пребывания в городе», в третьем «Полная стоимость
обслуживания». Далее необходимо в третьем столбике в строке «Условия отбора»
ввести >=[Введите стоимость]. Сохраняем изменения.
Результат:
Рис. 15
3.4 Получение данных о городе, в котором стоимость проживания в
гостинице максимальна
Чтобы получить информацию о телефоне, который дает максимальную прибыль,
необходимо создать 2 запроса, один из которых будет вспомогательным. Заходим в
режим конструктора, выбираем таблицу «Обслуживание» и в первом столбце в первой
строке выбираем «Суточная стоимость проживания» и в третьей строке выбираем функцию
«Мах», сохраняем запрос.
Рис. 16
Результат:
Рис. 17
Чтобы вывести из всего нашего списка телефон с максимальной прибылью
необходимо связать таблицу и вспомогательный запрос. Для этого в таблице 2
«Обслуживание» нажимаем и зажимаем строчку «Суточная стоимость проживания» и
перетаскиваем его в «Вспомогательный запрос». Теперь в третьем столбике
выбираем «Вспомогательный запрос. Max-Суточная стоимость проживания». Сохраняем
запрос.
3.5 Создание отчета
Отчет оформляется в тех случаях, когда необходимо наглядно представить на
экране или распечатать сводную информацию по базе данных. Отчет можно создавать
с помощью конструктора или мастера отчетов. В отчетах можно группировать
информацию по нескольким уровням по любому выбранному полю, выполнять
сортировку по нескольким полям, выполнять вычисления по заданному полю.
Для создания отчета использовался Мастер отчетов (вкладка Создание -
группа Отчеты - Мастер отчетов). В появившемся окне «Создание отчетов» выбраны
из таблицы поля, которые войдут в будущий отчет (Название города, Стоимость
эксурсий, Суточная стоимость питания, Суточная стоимость проживания, Количество
дней пребывания в городе, Полная стоимость обслуживания), нажимаем «Далее».
Дальше выбираем «Название города», нажимаем «Далее». Нажимаем «Итоги», в
выплывшем окне выбираемставим галочку в столбце «Sum» напротив «Суточная стоимость проживания», «Суточная
стоимость питания» и «Стоимость экскурсий» нажимаем «ок», «далее», «далее»,
«готово».
стоимость запрос excel access
Заключение
Программы Microsoft Excel и Microsoft Access позволяют решить одну и ту же задачу двумя схожими
способами. Оба приложения позволяют выполнять разнообразные запросы для
сортировки данных, производить сложные расчеты, составлять отчеты по данным и
просматривать их в разных формах. Имеется одно терминологическое различие: в Microsoft Excel строка, а в Access запись. Microsoft Excel - это программа управления электронными
таблицами, в которых элементы данных хранятся в ячейках строк и столбцов,
образующих листы, а Microsoft Access представляет собой программу
управлениями базами данных. Основные задачи Microsoft Excel управление списками данных, например списками
адресов, сведений о персонале. В Microsoft Access данные хранятся в таблицах,
которые рассчитаны на выполнение сложных запросов данных, находящихся в других
таблицах и хранилищах, и даже в полях других таблиц.
Microsoft Excel удобно
использовать, если объем не слишком велик, данные представлены в простой форме,
и к ним достаточно создать одну таблицу.Access удобно использовать при работе с
большим объемом информации, и если требуется выполнить сложные запросы и
отчеты.
Работа с программами особых трудностей не вызвала. С учетом сути запросов
удобнее было работать в Microsoft Access, но работа в Microsoft Excel тоже не вызвала особых затруднений, хотя, на мой
взгляд, менее удобная для пользователя при поиске нужной ему информации.
Список использованной литературы:
1. Симонович С.В., Евсеев Г.А., Мураховский В.И., Бобровский
С.И. «Информатика. Базовый курс. СПБ, издательство «Питер», 2007.
2. Курс лекций по Информатике.