Размещено на http://www.allbest.ru/

Петербургский Государственный Университет Путей Сообщения Императора Александра 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. Вычисление полной стоимости обслуживания в каждом городе.

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

4. Получение данных о городе, в котором стоимость проживания в гостинице максимальна (наименование города, классность номера, стоимость проживания).

5. Формирование отчёта с подведением итогов по каждому виду услуг и указанием полной стоимости туристского маршрута и количества дней пребывания на маршруте.

Примечание. Суточная стоимость питания и проживания в номерах одного класса во всех городах одинакова.

2. Электронная таблица Excel

2.1 Построение графика функции

Построение графика функции

Рис. 1

Для этого вводим формулу в ячейке С5:

=ЕСЛИ(B5<0;B5/2;ЕСЛИ(И(B5>=0;B5<=1,4);(B5^2)-

-4*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

Для этого создаем кнопку, как в вышеописанном случае. Далее нажимаем «Данные - Фильтр», кликаем на стрелочку рядом с названием ячейки «Полная стоимость обслуживания - Текстовые фильтры - больше или равно», в выплывшем окне «пользовательский автофильтр» в правой верхней строчке ставим «*», нажимаем «ок».

2.5 Получение данных о городе, в котором стоимость проживания в гостинице максимальна

Для того чтобы получить данные о городе, в котором стоимость проживания в гостинице максимальна, необходимо создать макрос. Далее кликаем на вкладку «Данные - Фильтр». В ячейке «Суточная стоимость проживания» щелкаем на стрелочку рядом с именем ячейки, кликаем на «сортировка по убыванию», потом на «числовые фильтры - первые 10», во выплывшем окне вместо цифры 10 ставим 1. Потом нажимаем вкладку «Разработчик» и там «остановить запись». Затем щелкаем правой кнопкой мыши на кнопку, и «назначить макрос». В выплывшем окне выбираем наш макрос.

Рис. 7

Далее во вкладке «Разработчик» нажимаем «остановить запись», затем правой кнопкой мыши щелкаем на «кнопку», «назначить макрос», выбираем наш макрос и щелкаем на «править». В выплывшем окне ищем наш макрос, и в строке ActiveSheet.ListObjects("Таблица3").Range.AutoFilter Field:=8, Criteria1:="=*"» после «Criterial:=» пишем InputBox ("Введите стоимость"), предварительно стерев «"=*"».

Рис. 8

3. Основные положения Microsoft Access

MicrosoftAccess - это система управления базами данных, предназначенная для создания и обслуживания баз данных, обеспеченная доступа к данным и их обработки.

База данных представляет собой организованную структуру, используемую для хранения данных, т.е. любых сведений о явлениях, процессах, действиях и т.д. Данные несут в себе информацию о событиях, происходящих в материальном мире, и по сути являются зарегистрированными сигналами, возникшими в результате этих событий.

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

Создание таблиц в режиме конструктора

Таблицы - это основные объекты БД, хранят все данные базы, структуру базы (поля, их типы, свойства). Для создания структуры первой таблицы выбрать вкладку Создание - группа Таблицы - режим Конструктор таблиц. Последовательно ввести поля первой таблицы с указанием их типа и свойств (размер поля, формат поля, подпись и т.д.).

Для связи создаваемой таблицы с другими таблицами задать ключевое поле. Для этого выделить поле и в группе Сервис выбрать кнопку Ключевое поле.

Структуры других таблиц создаются аналогично. Для редактирования структуры таблиц используется режим Конструктор, переход к нему - группа Главная - Режимы - Режим.

В работе созданы две таблицы. Ниже эти таблицы представлены в режиме Конструктор.

После оформления таблиц, создаются межтабличные связи.

Рис. 9

Связи позволяют черпать данные из разных таблиц, обеспечивают целостность данных. Для создания связей между таблицами выбрать вкладку Работа с базами данных - группа Отношения - кнопка Схема данных, после чего появляется окно «Схема данных».

Для добавления в связь таблиц в группе Связи выбирается кнопка «Отобразить таблицу», после чего появляется окно «Добавление таблицы», где выбираются таблицы для связи. В окне «Схема данных» становятся видны списки полей этих таблиц. Для установки связи ключевое поле нужной таблицы «перетаскивается» на аналогичное поле другой. Появляется диалоговое окно «Изменение связей», в котором указаны связываемые поля, тип отношения (один-к-одному или один-ко-многим). Необходимо включить переключатель для обеспечения целостности данных. После установки связей в окне «Схема данных» видна образовавшаяся связь. Контекстное меню линии связи (или кнопка Изменить связи в группе Сервис) позволит редактировать или удалить связь.

Ниже представлены созданные межтабличные связи.

Рис. 9

3.1 Заполнение таблиц

Ввод данных в таблицу может осуществляться как через формы, так и непосредственно в режиме таблицы, переход к нему - группа Главная - Режимы - Режим. Новая таблица не имеет записей, содержит только наименования полей.

В требуемую ячейку устанавливается курсор. Переход от поля к полю осуществляется с помощью клавиши [Tab]; в предыдущее поле можно попасть с помощью комбинации клавиш [Shift + Tab]. Посредством клавиш [вверх] и [вниз] осуществляются перемещения между строками таблицы. Как только будет закончен ввод записи, то есть строки таблицы, воспользовавшись клавишей [Tab], перейдем к первой ячейке следующей строки, а Access автоматически сохранит только что веденную запись.

Таблицы, заполненные данными, приведены ниже.

Рис. 10

Для удаления записей их предварительно необходимо промаркировать с помощью селекторной колонки, а затем нажать на клавишу [Delete]. Данные, занесенные в таблицы, можно копировать в буфер обмена, сортировать по возрастанию или убыванию т.д.

3.2 Вычисление полной стоимости обслуживания в каждом городе

При вычислении стоимости телефонов, необходимо создать запрос. Для этого нажимаем - Создание - конструктор запросов.

Рис. 11

В выплывшем окне выбираем таблицу «Город» и нажимаем добавить, затем выбираем таблицу «Обслуживание» и нажимаем добавить.

Рис. 12

Теперь в первом столбике в строке [Поле:] щелкаем на стрелочку и выбираем «Название Города», во втором столбце «Количество дней пребывания в городе», в третьем столбце необходимо ввести формулу: Полная стоимость обслуживания: ([Обслуживание]![Суточная стоимость проживания]+[Обслуживание]![Суточная стоимость питания])*[Город]! [Количество дней пребывания в городе]+[Город]![Стоимость экскурсий]

Рис. 13

После этого нажимаем крестик в правом углу и сохряняем изменения.

Затем выполняем Запрос.

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

Рис. 14

3.3 Формирование списка городов, стоимость посещение которых превышает стоимость, заданную пользователем

Создаем запрос и выбираем 3 таблицы: «Город», «Обслуживание» и «Полная стоимость обслуживания». В первом столбике выбираем «Название города», во втором «Количество дней пребывания в городе», в третьем «Полная стоимость обслуживания». Далее необходимо в третьем столбике в строке «Условия отбора» ввести >=[Введите стоимость]. Сохраняем изменения.

Результат:

Рис. 15

3.4 Получение данных о городе, в котором стоимость проживания в гостинице максимальна

Чтобы получить информацию о телефоне, который дает максимальную прибыль, необходимо создать 2 запроса, один из которых будет вспомогательным. Заходим в режим конструктора, выбираем таблицу «Обслуживание» и в первом столбце в первой строке выбираем «Суточная стоимость проживания» и в третьей строке выбираем функцию «Мах», сохраняем запрос.

Рис. 16

Создаем новый запрос. Выбираем 2 таблицы «Город» и «Обслуживание» и щелкаем на вкладку «Запросы», выбираем наш вспомогательный запрос и закрываем выплывшее окно. В первом столбике выбираем «Название города», во втором «Классность номера».

Результат:

Рис. 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 удобно использовать, если объем не слишком велик, данные представлены в простой форме, и к ним достаточно создать одну таблицу.

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

Работа с программами особых трудностей не вызвала. С учетом сути запросов удобнее было работать в Microsoft Access, но работа в Microsoft Excel тоже не вызвала особых затруднений, хотя, на мой взгляд, менее удобная для пользователя при поиске нужной ему информации.

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

1. Симонович С.В., Евсеев Г.А., Мураховский В.И., Бобровский С.И. «Информатика. Базовый курс. СПБ, издательство «Питер», 2007.

2. Курс лекций по Информатике.

Размещено на Allbest.ru