50298

Создание и редактирование баз данных средствами MS Excel и MS Access

Курсовая

Информатика, кибернетика и программирование

Создание базы данных БД. Система управления базами данных CCESS EXCEL позволяет работать с базами данных.

Русский

2014-01-20

726 KB

16 чел.

Министерство Путей Сообщения Российской Федерации

Петербургский Государственный Университет Путей Сообщения

Кафедра ”Информатика и информационные технологии”

Пояснительная записка

к курсовой работе

«Создание и редактирование баз данных средствами

 MS Excel и MS Access »

Вариант №9

    Выполнил: 

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

2005
Оглавление

[1] 2005
Оглавление

[1.1] 1.1. Основные положения.

[1.2] СУММ(B3:B12), что позволяет подсчитать общую протяженность судоходной части исходя из того, какая протяженность каждой из имеющихся рек в отдельности.

[1.3] 1.4. Создание базы данных (БД).

[2] 2.Система управления базами данных ACCESS

[3] 2.1. Общие положения.

[3.1] 2.2. Разработка структуры БД.

[3.2] 2.3. Создание таблиц в режиме конструктора.

[3.3] 2.4. Заполнение таблиц данными (кроме вычисляемого поля).

[3.4] 2.5. Запросы на обновление

[3.5] 2.6. Запросы на выборку. Параметрические запросы.

[3.6] 2.7. Создание и редактирование форм

[3.7] 2.8. Создание и редактирование отчетов.

[4] 3. Список использованной литературы

1. Электронная таблица EXCEL.

1.1. Основные положения.

EXCEL позволяет работать с базами данных.Основное назначение EXCEL-работа с таблицами, содержащими формулы. Для обозначения объекта, представляющего электронную таблицу, в EXCEL принят термин рабочий лист. Рабочий лист-это множество ячеек, каждая из которых принадлежит некоторому столбцу и некоторой строке. Строки и столбцы идентифицируются, т.е. столбцы именуются, а строки нумеруются. По умолчанию рабочий лист имеет 256 столбцов с именами от А до IV и 65536 строки. Рабочий лист представлен на рисунке 1.

рис. 1

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

Спроектировать базу данных в  EXCEL,содержащую следующие данные.

Входные данные:

- Название реки;

- Протяженность судоходной части;

- Глубина фарватера;

- Количество месяцев навигации  в году;

- Название водоема, куда впадает река;

           - Название области, где река имеет наибольшую протяженность;

Выходные данные:

- Общая протяженность рек;

- Формирование списка рек, протяжённостью которых не ниже запрашиваемой величины

– Формирование списка рек, впадающих в выбранный пользователем водоём

Должны выполняться следующие функции:

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

2. Формирование списка рек, протяжённостью которых не ниже запрашиваемой величины.

3. Формирование списка рек, впадающих в выбранный пользователем водоём.

4. Подсчет общей протяженности рек по областям.

5. Формирование отчета, включающего: название реки,  число месяцев навигации  и глубину фарватера с указанием реки, имеющей наименьшее количество месяцев навигации в году.

.

1.3. Математическое описание задачи.

СУММ(B3:B12), что позволяет подсчитать общую протяженность судоходной части исходя из того, какая протяженность каждой из имеющихся рек в отдельности.

1.4. Создание базы данных (БД).

База данных - это интервал ячеек, формирующих список или базу данных. База данных в EXCEL- это список связанных данных, в котором строки данных являются записями, а столбцы- полями. Верхняя строка списка содержит название каждого из столбцов. Ссылка может быть задана либо как диапазон ячеек , либо как имя, соответствующему диапазону списка.

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

Сначала задаются имена полей БД, затем вводим данные в остальные ячейки. Вычисляемые поля заполняются в последнюю очередь. Заголовок таблицы целесообразно формировать с помощью надписи.

Ввод формул.

Любая формула начинается со знака =. Формулы могут содержать:

- числа и относительные ссылки =2*А2

- абсолютные ссылки =A3*$B$6

- функции = СУММ(A1:B5)

- имена ячеек = Стоимость * Льготы

Копировать ячейки можно несколькими способами:

  1.  Методом заполнения.

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

  1.   С помощью буфера обмена

   - Выделить диапазон копируемых ячеек

   - Правка- копировать

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

Процесс заполнения БД с помощью формы.

  1.  создать заголовок таблицы Главная.
  2.  задать имена полей БД.
  3.  выделить ячейки A2:F2 (заголовки) и выполнить команду Данные - Форма. После этого появится форма (рис 2). Теперь надо ввести данные во все ячейки, кроме вычисляемого поля, переход осуществляется с помощью кнопки(tab) или мышью. Заполнив первую запись нажать на кнопку далее, также и с остальными записями.
  4.  закончив вводить данные, щелкнуть на кнопке закрыть.
  5.  ввести в строку «общая стоимость» формулу.

Форма приведена на рисунке 2

рис.2

                   

Заполненная таблица приведена на рис. 3

рис. 3

Название реки

Протяжённость судоходной части

Глубина фарватера

Количество месяцев навигации в году

Название водоёма, куда река впадает

Название области, где река имеет наибольшую протяженность

Волга

3000

150

6

Каспийское

Европейская равнина

Амур

5000

200

10

Японское

Дальний Восток

Обь

4200

190

5

Море Лаптевых

Восточная Сибирь

Лена

4500

120

6

Берингово

Западная Сибирь

Енисей

3800

130

6

Карское

Западная Сибирь

Урал

1200

70

5

Малые озера

Урал

Уренгой

700

80

8

Японское

Дальний Восток

Грязная

500

30

3

Карское

Западная Сибирь

Вонючая

30

10

3

Берингово

Западная Сибирь

Нева

250

40

11

Финский залив

Северо-запад

Итого:

23180

Для отображения формул вместо результатов надо выполнить команду  Сервис - Параметры и на вкладке Вид поставить флажок около параметра Формула. Таблица с формулами приведена на рисунке 4.

рис. 4

Название реки

Протяжённость судоходной части

Глубина фарватера

Количество месяцев навигации в году

Название водоёма, куда река впадает

Название области, где река имеет наибольшую протяженность

Волга

3000

150

6

Каспийское

Европейская равнина

Амур

5000

200

10

Японское

Дальний Восток

Обь

4200

190

5

Море Лаптевых

Восточная Сибирь

Лена

4500

120

6

Берингово

Западная Сибирь

Енисей

3800

130

6

Карское

Западная Сибирь

Урал

1200

70

5

Малые озера

Урал

Уренгой

700

80

8

Японское

Дальний Восток

Грязная

500

30

3

Карское

Западная Сибирь

Вонючая

30

10

3

Берингово

Западная Сибирь

Нева

250

40

11

Финский залив

Северо-запад

Итого:

=СУММ(B2:B12)

1.5. Поиск данных.

Для поиска данных необходимо поместить табличный курсор внутрь таблицы и выполнить команду  Данные – Фильтр – Автофильтр. При этом в каждой ячейке строки заголовков полей появляется кнопка раскрывающегося списка.

Рассмотрим поиск данных на примере поиска рек, протяженность судоходной части которых, удовлетворяет запрашиваемой величине. Для этого надо раскрыть список поля “Протяженность судоходной части” и выбрать команду (Условие…).

Результат выполнения данного примера расположен на рисунке 5.

рис. 5

                                                                                                                                 рис.6

Волга

3000

150

6

Каспийское

Европейская равнина

Амур

5000

200

10

Японское

Дальний Восток

Обь

4200

190

5

Море Лаптевых

Восточная Сибирь

Лена

4500

120

6

Берингово

Западная Сибирь

Енисей

3800

130

6

Карское

Западная Сибирь

23180

1.6 Использование функций для получения итоговых значений.

Для подсчета общей протяженности судоходной части рек используется функция СУММ. Для этого табличный курсор устанавливаем в ячейку В14. Затем выполняю команду Вставка – Функция и выбираем функцию СЧЕТЕСЛИ, методом протаскивания выделяем требуемый диапазон ячеек ( E3:E16 ).

                                                                                                                                  рис.8

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

Макрос - это программа, записанная на встроенном в EXCEL языке VISUAL BASIC for application (VBA).  Макрос может быть написан программистом или создан автоматически макрорекордером. Макрос, созданный этим макрорекордером, запоминает с момента его записи все действия пользователя, в том числе и ошибочные.

Рассмотрим создание макроса на примере поиска рек, протяженность судоходной части которых удовлетворяют запрашиваемой величине. Для  этого надо:

- табличный курсор поместить внутрь таблицы

- выполнить команду Сервис- Макрос - Начать запись

- в окне диалога «Запись макроса» (рис.9) задать имя макроса, в данном случае «Река».

рис.9

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

- Выполнить действия, которые записывает макрос

- Остановить запись макроса.

Запуск макроса.

- Восстановить исходное состояние таблицы путем снятия автофильтра

- Убедиться, что табличный курсор находится внутри таблицы

- Выполнить команду Сервис- Макрос- Макросы, выделить требуемый макрос и щелкнуть на кнопке “ Выполнить ”

рис10

Типичный командный макрос следующие элементы:

- Операторы sub и end sub располагаются в конце и начале макроса

- Имя макроса следует после оператора sub

- Тело макроса, то есть часть макроса, заключенная между операторами sub и end sub ,оно состоит из последовательности операторов, каждый из которых соответствует выполненному во время записи макроса действию.

Для удобства работы создается третий макрос, возвращающий таблицу в исходное состояние.

- Команда Сервис - Макрос - Начать запись

- Присвоить макросу имя

- Команда Данные - Фильтр – Отобразить все

- Остановить запись макроса

1.8. Назначение макросов объектам,

Для назначения макроса конкретному объекту объект помещается на лист.

Выделяется правой клавишей мыши и из контекстного меню выбирается команда Назначить макрос.

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

- Выполняется команда Вид – Панели инструментов – Формы, на экране появится панель инструментов.

- Щелкнуть на элементе кнопка и методом протаскивания сформировать кнопку на листе.

- На экране появится окно назначить макрос объекту, в котором надо выделить требуемый макрос и нажать ОК.

- Пока кнопка выделена на листе, можно изменить ее имя, а затем щелкнуть вне кнопки.

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

Для построения диаграммы надо проделать следующие действия:

-  Выделить диапазон ячеек с данными, необходимыми для построения диаграммы

– Щелкнуть на кнопке Мастер диаграмм или выполнить команду вставка – диаграмма.

Существует 4 шага мастера диаграмм:

1. выбирается тип диаграмм (круговая ) и ее вид.

2. выбирается диапазон данных.

3. выбираются различные параметры оформления диаграммы: заголовки, легенда (добавить легенду, справа); подписи данных (доля).

4.определяется место расположения диаграммы.

Построенная диаграмма приведена на рис.11

рис.11

2.Система управления базами данных ACCESS

2.1. Общие положения.

Microsoft Access является СУБД , предназначенной для создания и обслуживания баз данях , обеспечения доступа к данным и их обработки.

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

Реляционная база данных представляет собой множество взаимосвязанных двухмерных таблиц- реляционных таблиц, называемых также отношениями, в каждой из которых содержатся сведения об одной сущности автоматизируемой предметной области.

Таблица состоит из полей (столбцов) и записей (строк). Таблицы связаны по ключевому полю, которое позволяет однозначно идентифицировать каждую запись. Значения в ключевом поле не должны повторяться.

База данных ACCESS включает в себя следующие объекты:

- Таблицы, запросы, схемы данных, имеющие непосредственное отношение к базе данных;

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

Базы данных ACCESS работают со следующими типами данных:

- Текстовый - тип данных, используемый для обычного неформатированного текста ограниченного размера(до 255 символов)

- Поле мемо - специальный тип данных для хранения больших объемов текста(до 65535) символов

- Дата/время-тип данных для хранения календарных дат и текущего времени

- Числовой - тип данных для хранения действительных чисел

- Денежный - для хранения денежных сумм

- Счетчик- специальный тип данных для уникальных натуральных чисел с автоматическим наращиванием

- Логический.

2.2. Разработка структуры БД.

Необходимо создать таблицу. В ней будут размещены следующие поля:

- Название реки;

- Протяженность судоходной части;

- Глубина фарватера;

- Количество месяцев навигации  в году;

- Название водоема, куда впадает река;

           - Название области, где река имеет наибольшую протяженность;

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

Для создания новой таблицы в окне базы данных надо выбрать объект Таблицы и нажать кнопку Создать. В открывшемся окне Новая таблица выбирается Конструктор.

После этого появляется окно Таблица 1:таблица, в которой определяется структура таблицы базы данных.

Для определения поля в окне Таблица задаются Имя поля, тип данных, Описание –краткий комментарий. А также свойства поля в разделе Свойства поля.

На рисунках 12 приведены таблицы без значений вычисляемого поля.

                                        

Рис. 12   

.                                                                        

2.4. Заполнение таблиц данными (кроме вычисляемого поля).

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

Если при заполнении таблицы  была изменена ее структура, то при закрытии ACCESS потребует подтверждения изменений.

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

2.5. Запросы на обновление

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

В задании необходимо обновить записи поля “Протяженность судоходной части”.

Чтобы создать запрос на обновление, надо сначала создать запрос на выборку, который затем преобразуется в окне конструктора запросов в запрос на обновление при выборе команды Обновление контекстного меню, появляющегося при нажатии кнопки Тип запроса на панели инструментов конструктора запросов, или команды меню Запрос->Обновление.

В ячейку строки Обновление вводится формула: [Новая протяженность] и выполняется команда Запрос->Запуск. После этого  ACCESS предупредит, что будут обновлены значения какого-то количества записей. Результаты изменения  данных можно увидеть в таблице, выбранной в качестве источника записей. При добавлении данных  в таблицу надо каждый раз открывать запрос.

2.6. Запросы на выборку. Параметрические запросы.

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

Рассмотрим принцип формирования запроса на примере таблицы “Дата оплаты”.

Для этого:

- В окне базы данных активизировать объект Запросы и нажать Создать

- В окне Новый запрос выбрать режим Конструктор, после чего на экране появится окно Запрос на выборку , а также диалоговое окно Добавление таблицы, где надо выделить таблицу «Протяженность» и щелкнуть на кнопке Добавить

- Перенести из таблицы, расположенной в верхней части экрана, необходимые поля в строку поле бланка запроса; в данном случае надо перенести все поля, для этого двойным щелчком на заголовке таблицы выделяем все поля и перетаскиваем их в первую ячейку строки Поле

- В строку условие отбора в ячейку поля Дата оплаты вводится значение >1200

- Закрыть запрос, сохранить его, задать имя «Запрос2».

- При открытии запроса в окне базы данных на экран выводится результирующая таблица запроса (рис.14)

рис.14

Параметрический запрос является частным случаем запроса на выборку.

Пользователь может вводить значение поля в диалоговом режиме в процессе выполнения запроса.

Имя параметра может задаваться непосредственно в поле Условие отбора в квадратных скобках. При выполнении запроса это имя появится в диалоговом окне Введите значение параметра (рис.16)

Рассмотрим создание параметрического запроса Необходимо выбрать телефоны, которые оплачиваются по льготному тарифу (рис.17). Назовем этот запрос «Льготы».

рис.15

рис.16

рис.17

2.7. Создание и редактирование форм

Формы являются основным средством создания диалогового интерфейса приложения пользователя. Они служат для ввода данных, а также для их просмотра в удобном для пользователя виде.

Для создания формы в окне базы данных в области Объекты выбирается закладка Формы, после этого надо нажать на кнопку Создать. В диалоговом окне Новая форма (рис. 18) выбирается режим Автоформа: в столбец.

Рис.18

На экране появится форма, на которой отображены сведения о названии рек.  (рис.19).

Рис.19

После этого форма сохраняется и ей присваивается имя.

Для создания ленточной формы в окне Новая форма выбирается режим Автоформа: ленточная. В качестве источника данных можно взять таблицу или запрос.

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

2.8. Создание и редактирование отчетов.

Отчеты служат для форматированного ввода данных на печатающие устройства.

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

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

Редактирование структуры отчета выполняется в режиме Конструктора. Приемы редактирования такие же, что и для форм.

С помощью мастера отчетов я создал отчет на основании таблицы Льготы. Отчет приведен на рисунке 22

Рис.22

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

  1.  Хомоненко А.Д. самоучитель MICROSOFT WORD 2000.- СПб,2002-.560 стр.
  2.  Долженков В., Ю.Колесников. Справочник MICROSOFT ACCESS- СПб,1999.-480 стр.
  3.  Долженков В., Ю.Колесников. Самоучитель  MICROSOFT Excel 2000- СПб,1999.-356 стр.

8

  1.  

 

А также другие работы, которые могут Вас заинтересовать

30290. Основные методы художественной литературы. Романтизм. Философская основа (учения Шеллинга, Шопенгауэра) 30.5 KB
  Романтизм. Характер художественного конфликта в романтизме Классические страны романтизма – Германия Англия Франция. Романтизм – это одно из крупнейших направлений в европейской и американской литре 1819 вв получившее всемирное значение и распространение. Романтизм – это то что впоследствии будет называться французской революцией под лозунгами свобода равенство братство.
30291. Основные методы художественной литературы. Романтизм. Тип героя. Понятия романтической тайны, романтической иронии 23 KB
  Понятия романтической тайны романтической иронии Романтизм последний “большой стиль†в истории искусства то есть последнее направление проявившее себя во всех областях духовной деятельности и художественного творчества: в изобразительных искусствах музыке литературе. Романтизм высшая точка в развитии гуманистического искусства начатого в эпоху Возрождения когда человек был провозглашен мерилом всех вещей. Мир для романтиков тайна загадка познать которую может только откровение искусства.
30293. Основные методы художественной литературы. Реализм. Многообразие подходов к проблеме реализма в литературоведении. Просветительский реализм 29 KB
  Реализм. Многообразие подходов к проблеме реализма в литературоведении. Просветительский реализм. 1 Реализм это художественное направление имеющее целью возможно ближе передавать действительность стремящееся к максимальному правдоподобию.
30294. Основные методы художественной литературы. Модернизм - идейные основы и творческая практика. Основные течения. Идейные основы и художественная практика акмеизма 29.5 KB
  Идейные основы и художественная практика акмеизма. Для акмеизма была характерна крайняя аполитичность полное равнодушие к злободневным проблемам современности. Но если в поэзии символизма определяющим фактором являлась мимолетность сиюминутность бытия некая тайна покрытая ореолом мистики то в качестве краеугольного камня в поэзии акмеизма был положен реалистический взгляд на вещи. Главные идеи акмеизма были изложены в программных статьях Н.
30295. Основные методы художественной литературы. Модернизм - идейные основы и творческая практика. Основные течения. Идейные основы и художественная практика футуризма 23.5 KB
  Философскими предпосылками появление модернистического исва считаются: труды философа Нитше а также исследования Шопенгауэра. По мнению Нитше вся система миров культуры находится в состоянии глубочайшего кризиса. Нитше для активации духовн исканий чела создает острую словесную провокацию: он пишет о мифич сверхчеле кот должен прийти на смену обычному челу слабому потребителю. Для Нитше это норма а совершить усилия – это сверхнорма.
30296. Типология реализма в историческом аспекте (первобытный, ренессансный). Характеристика каждого этапа развития метода реализма 22.5 KB
  Идейная основа: Сознание эпохи перехода от Средневековья к Новому времени соотносимой с вызреванием капиталистического строя в недрах феодального Доминанта: Абсолютизация человеческой личности в ее целостности; представление о человеке как о единстве разумного и чувственного как о свободном существе с беспредельными творческими возможностями Основа эстетики: Антропоцентризм принцип Человек – мера всех вещей Характерные черты: Гиперисторизм идеалов связанный с концепцией родового человека; стихийный синкретизм мировосприятия выражающийся...
30297. Типология реализма в историческом аспекте. Классический реализм 19 века 26 KB
  Классический реализм 19 века. В тридцатые годы XIX века в ряде европейских литератур утверждаются эстетические принципы критического реализма XIX века. Открытием критического реализма XIX века было изображение социального характера создание образа воплощавшего типические черты современного общества. Однако такое документально точное изображение среды реализма XIX века означало собой лишь подготовительную стадию в художественном освоении жизненных обстоятельств.
30298. Типология реализма в историческом аспекте. Социалистический реализм 26.5 KB
  Социалистический реализм являясь основным методом советской художественной литературы и литературной критики требует от художника правдивого историческиконкретного изображения действительности в её революционном развитии. Причём правдивость и историческая конкретность художественного изображения действительности должны сочетаться с задачей идейной переделки и воспитания в духе социализма. В изображении действительности показать процесс исторического развития который в свою очередь должен соответствовать материалистическому пониманию...