Аблязов В.И., Редько С.Г. Проектирование баз данных Microsoft Office Access 2003

Подождите немного. Документ загружается.

Санкт-Петербургский государственный политехнический университет

Факультет инноватики

Рекомендовано к использованию

в учебном процессе

Протокол № 12 от 16.02.09

заседания Учебно-методического совета

Председатель УМС ФИ, проф.

О.В. Колосова

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

по выполнению лабораторных работ

Проектирование баз данных в среде Microsoft Office Access 2003

Для студентов, обучающихся

по направлению высшего профессионального образования

220600-Инноватика

и специальности 220601 - Управление инновациями

Составили:

профессор, д.т.н. С.Г. Редько

доцент, к.т.н. В.И. Аблязов

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

Оглавление

1. РЕЛЯЦИОННАЯ БАЗА ДАННЫХ MICROSOFT ACCESS........................................... 4

1.1. Понятие базы данных....................................................................................................... 4

1.2. Особенности базы данных Access.................................................................................... 4

1.3. Структура базы данных ................................................................................................... 5

1.4. Ключевые поля и связи...................................................................................................10

1.4.1 Первичный и внешний ключ.............................................................................10

1.4.2 Типы связей........................................................................................................11

1.5. Целостность данных ........................................................................................................11

1.6. Задание и отображение данных......................................................................................12

1.6.1 Распределение данных по полям..............Ошибка! Закладка не определена.

1.6.2 Типы данных......................................................................................................12

1.6.3 Формат отображения данных............................................................................12

2. РАЗРАБОТКА СТРУКТУРЫ БАЗЫ ДАННЫХ И СОДЕРЖИМОГО ТАБЛИЦ......15

2.1. Этапы проектирования таблиц......................................................................................15

2.2. Создание таблиц...............................................................................................................15

2.2.1 Создание таблицы при помощи мастера таблиц ..............................................16

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

2.2.3 Создание таблицы путем ввода данных............................................................18

2.2.4 Задание маски ввода в режиме конструктора...................................................18

2.3. Формирование схемы данных ........................................................................................20

3. ФОРМИРОВАНИЕ ЗАПРОСОВ.......................................................................................22

3.1. Особенности запросов и фильтров.................................................................................22

3.2. Работа с фильтрами .........................................................................................................23

3.3. Типы запросов ..................................................................................................................23

3.4. Создание запросов............................................................................................................24

3.4.1 Простой Запрос на выборку с помощью Мастера запросов...........................24

3.4.2 Запросы в режиме Конструктора ......................................................................25

3.4.3 Групповые операции..........................................................................................26

3.4.4 Запросы с параметрами .....................................................................................28

3.4.5 Запись условий...................................................................................................30

3.4.6 Перекрестный запрос.........................................................................................30

3.4.7 Использование вычисляемых полей в запросах...............................................32

3.4.8 Запросы на изменение .......................................................................................34

4. СОЗДАНИЕ ФОРМ ............................................................................................................34

4.1. Режимы работы с формами ............................................................................................35

4.2. Создания Автоформ на основе таблицы или запроса .................................................35

4.3. Создания форм в режиме мастера форм .......................................................................36

4.4. Создание формы в режиме конструктора.....................................................................38

4.4.1 Структура формы...............................................................................................38

4.4.2 Панели окна в режиме конструктора ................................................................39

5. ФОРМИРОВАНИЕ ОТЧЕТОВ.........................................................................................39

5.1. Режимы работы с отчетами ............................................................................................40

5.2. Автоматическое создание отчета на основе таблицы или запроса ...........................40

5.3. Создание отчета с помощью мастера ............................................................................41

6. ПОРЯДОК ПРОВЕДЕНИЯ ЛАБОРАТОРНЫХ РАБОТ...............................................45

7. ОЦЕНКА ПРОВЕДЕНИЯ РАБОТ....................................................................................46

Приложение 1 ..........................................................................................................................47

Приложение 2 ..........................................................................................................................51

Первичный цикл проектирования баз данных в среде Microsoft Office Ac-

cess 2003 включает в себя выполнение следующих работ:

Лабораторная работа 1. Общие понятия и особенности построения реля-

ционной базы данных Microsoft Access. Структура БД.

Лабораторная работа 2. Конструирование таблиц.

Лабораторная работа 3. Формирование запросов.

Лабораторная работа 4. Создание форм данных и формирование отчетов.

1. РЕЛЯЦИОННАЯ БАЗА ДАННЫХ MICROSOFT ACCESS

1.1. Понятие базы данных

В широком смысле «база данных» — это совокупность описаний объ-

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

и представления.

Традиционно все системы управления БД (СУБД) классифицируются в

зависимости от модели данных, которая лежит в их основе. Принято выделять

иерархическую, сетевую и реляционную модели данных. Соответственно го-

ворят об иерархических, сетевых и реляционных СУБД. Важнейшими из БД,

безусловно, являются реляционные (англ. relation – связь, отношение). Реля-

ционные базы данных фактически стали промышленным стандартом.

Функциональное назначение реляционной СУБД Access:

 хранить и систематизировать данные (это позволяет легко ориенти-

роваться и быстро находить нужные сведения);

 обновлять данные: автоматически или с использованием удобных

форм данных;

 формировать отчеты, собирающие данные из различных таблиц в

удобной форме и по заданным критериям.

СУБД MS Access предназначена для создания настольных и офис-

ных БД и для которых характерны:

 несложная структура данных (десятки таблиц);

 небольшое число пользователей в локальной сети (единицы, десятки

рабочих мест) или на отдельном компьютере.

1.2. Особенности базы данных Access

В реляционной БД Access все хранимые данные сгруппированы в виде

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

ный тип (особенность) объекта и состоит из строк (записей) и столбцов (по-

лей). Каждая таблица БД имеет уникальное имя, не повторяющееся внутри

базы данных. Каждый столбец имеет имя, записываемое в верхней части таб-

лицы. Различные таблицы могут иметь столбцы с одинаковыми именами. В

отличие от столбцов, строки не имеют имен, а количество логически не ог-

раничено.

Для целей получения различных вопросов и ответов из первичных таб-

лиц БД служат запросы (объединение данных из нескольких таблиц, ил ка-

кие-то вычисления), а также формы и отчеты.

Все данные одной базы данных Microsoft Access хранятся в одном файле

с расширением «.mdb» .

В рамках этого файла существуют следующие объекты:

 таблицы - для хранения данных;

 запросы к базе данных - для поиска, выбора и сортировки требуе-

мых данных с помощью стандартных функций. Запросы выполняются с по-

мощью конструктора запросов и с помощью языка баз данных SQL, и Visual

Basic;

 формы - предназначены для просмотра, добавления и изменения

данных в таблицах, вывода на экран данных из одной или более таблиц с ис-

пользованием макета, выбранного в мастере форм или созданного пользовате-

лем самостоятельно в режиме конструктора. Форма может содержать кнопки

для выбора действий, открывающие другие объекты или автоматически вы-

полняющие другие задачи;

 отчеты - для анализа и печати данных в определенном формате;

 страницы доступа к БД через Интернет или интрасеть для про-

смотра, обновления и анализа данных.

Макеты этих объектов устанавливаются и изменяются в режиме конст-

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

пользование мастеров баз данных.

СУБД MS Access обеспечивает импорт данных из других программ

(Excel, Lotus 1-2-3, Paradox и т.д.).

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

Если для создания электронных таблиц Excel достаточно ввести первые

данные - прототип бухгалтерской книги c листами из электронных таблиц, то

для начала работы с Access необходимо предварительно спроектировать базу

данных (БД).

Разработка БД начинается с разработки ее структуры, которая отражает

функционирование самого объекта, для которого создается БД. Поэтому до

начала создания БД нужно ясно представить, какая информация должна при-

сутствовать в базе данных, что она характеризует и каким образом организу-

ются связи между информационными массивами. Необходимо четко осознать

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

порядок ее использования.

Для упрощения понимания системной структуры и логики работы базы

данных, её целесообразно представить состоящей из отдельных подсистем

(таблиц), каждая из которых несет определенную функциональную нагрузку.

Например, подсистема (таблица) Поступление_товара характеризует

все, что касается поступления: Код_поступления (ключевое поле), Да-

та_поступления, Товар (код), Количество, Сотрудник (код), принявший товар

и т.д.

Подсистема Товары характеризует все, что касается каждого товара,

(но не его поступления): Код_товара (ключевое поле), Стоимость (единицы),

Размер, Вес, Производитель, Группа, гарантийный срок, особенности приме-

нения и т.п.

Иными словами, каждая подсистема описывает какой-то вполне опреде-

ленный объект (предмет, тему, функцию).

Весь массив первичных (исходных) данных распределен по этим под-

системам. Подсистемы состоят из отдельных таблиц.

Например, подсистема Поступление может состоять из двух таблиц,

основной Поступление и вспомогательной Уточнение:

Поступление

Код_поступл

Дата Код_Поставщика

Код_склада Код_сотрудн

Пост_1

25.12.09 Пост-к_1 Скл_1 Сотр_1

Пост_2

25.12.09 Пост-к_2 Скл_2 Сотр_2

Пост_3

27.12.09 Пост-к_1 Скл_3 Сотр_1

Пост_4

29.12.09 Пост-к_3 Скл_2 Сотр_3

Пост_5

12.01.10 Пост-к_4 Скл_3 Сотр_3

Пост_6

12.01.10 Пост-к_1 Скл_1 Сотр_4

Пост_7

14.01.10 Пост-к_5 Скл_4 Сотр_2

Уточнение

Код_уточнен

Код_поступл

Код_Товара Количество Замечания

Уточн_1

Пост_1 Тов_1 344 Нет

Уточн_2

Пост_1 Тов_2 12 Нет

Уточн_3

Пост_3 Тов_3 156 Нет

Уточн_4

Пост_4 Тов_2 2 Да

Уточн_5

Пост_5 Тов_4 17 Нет

Уточн_6

Пост_5 Тов_5 234 Нет

Уточн_7

Пост_5 Тов_6 90 Да

Уточн_8

Пост_6 Тов_7 67 Нет

Уточн_9

Пост_7 Тов_2 44 Нет

Уточн_10

Пост_7 Тов_8 81 Нет

В простейшем случае. Подсистема может состоять из одной таблицы.

Каждая такая таблица имеет первичное ключевое поле (выделен жирным), и

одно или несколько внешних ключевых полей (выделены курсивом).

Назначение, состав и содержание подсистем (таблиц) студент формиру-

ет самостоятельно. Такими подсистемами (объектами) могут являться: Со-

трудники, Книги, Автомобили, Товары, Заказчики, Поступле-

ние_товаров, Успеваемость_студентов, Проведение звнятий, Посеще-

ние_лекций и т.п.

Отдельные строки этих первичных таблиц, заполняются последователь-

но и в дальнейшем не должны менять свои первоначальные значения. Поэто-

му, например, целесообразно в БД библиотеки разделять по разным таблицам

фиксацию выдачи и приема (возврата) книг:

Выдача

Код_выдачи

Дата Код_Студента Код_книги Код_сотрудн

Выд_1

20.11.09 Студ_1 Кн_1 Сотр_1

Выд_2

20.11.09 Студ_2 Кн_1 Сотр_1

Выд_3

22.11.09 Студ_1 Кн_2 Сотр_2

Возврат

Код_возврата

Дата Код_выдачи Код_сотрудн

Возвр_1

20.01.10 Выд_2 Сотр_1

Возвр_2

22.01.10 Выд_3 Сотр_1

Возвр_3

24.01.10 Выд_1 Сотр_2

Заполнение этих первичных таблиц желательно осуществляется по-

строчно: в соответствии с новыми поступлениями, при появлении новых то-

варов, приемом новых сотрудников, фиксированием новых дорожных проис-

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

вание дорожных происшествий и наказания по этим нарушениям.

Объем вводимой информации по количеству записей должен быть дос-

таточным, чтобы отразить все возможные возникающие ситуации, позволяю-

щие понять и детализировать процесс функционирования, а также устранить

неточности и огрехи в структуре. Подобные огрехи наглядно проявляются,

например, при появлении повторяющихся «ситуаций». Например, повторяю-

щаяся «фамилия», поступление одинакового товара от разных поставщиков,

неоднократная сдача однорго и того же экзамена и т.п.

После распределения данных по типам (темам) определяются связи ме-

жду таблицами. Схема подобной структуры приведена в приложении 1.

Первичные таблицы являются основой БД.

Создание первичных таблиц БД разделяют на два этапа.

На первом - определяются: состав полей, их имена, последовательность

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

индексы таблицы и другие свойства полей.

На втором этапе производится заполнение их данными (создание запи-

сей таблицы).

Данные в таблицах распределяются по столбцам, которые называют по-

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

число полей фиксировано (естественно, может быть изменено), а число запи-

сей не ограничено.

Каждая таблица должна иметь уникальное для данной базы имя. Как

правило, таблица описывает отдельный тип объекта и состоит из набора запи-

сей, определяющих этот тип, например, набор сведений о сотрудниках, фа-

культетах или товарах.

В таблицах, по возможности, не должны содержаться записи, в которых

много повторяющихся данных, так как при этом не только неоправданно уве-

личивается объем информации, но и усложняется процесс изменения и поиска

данных. Для исключения повторений такая информация выделяется в отдель-

ные более простые взаимосвязанные таблицы.

Например, данные о сотрудниках хранятся в таблице Сотрудники:

Сотрудники

Код_сотр

Фамилия Имя Год_рожд

Пол

Отдел Нач_отд Тел_отд

Ст_1 Иванов Иван 1987 м 221 Культов 5527564

Ст_2 Петрова Мария 1986 ж 222 Полосова

2944211

Ст_3 Сидоров Игнат 1987 м 221 Культов 5527564

Ст_4 Кикадзе Карина

1988 ж 221 Культов 5527564

Ст_5 Мимадзе Ашот 1989 м 222 Полосова

2944211

Ст_6 Клычков Артем 1980 м 223 Фисенко 5341322

Ст_7 Шлычков

Петр 1985 м 223 Фисенко 5341322

Ст_8 Клыкова Юдифь

1990 ж 222 Полосова

2944211

Ст_9 Тыков Арен 1989 м 223 Фисенко 5341322

Ст_10 Черняк Юрий 1984 м 221 Культов 5527564

Ст_11 Беляк Алена 1986 ж 223 Фисенко 5341322

Ст_12 Карабан Юлия 1988 ж 222 Полосова

2944211

Здесь в каждой строке (запись) содержатся данные об одном объекте

(сотруднике), а в каждом столбце (поле) – характеристика объекта. Причем в

поле Нач_отд и Тел_н_отд много повторяющихся данных. Улучшение струк-

туры БД, в данном случае исключение многократно повторяющейся инфор-

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

цы на две взаимосвязанные – Сотрудники_2 и Отделы следующим образом:

Сотрудники_2

Код_сотр

Фамилия Имя Год_рожд

Пол

Код_отд

Ст_1 Иванов Иван 1987 м Отд_1

Ст_2 Петрова Мария 1986 ж Отд_2

Ст_3 Сидоров Игнат 1987 м Отд_1

Ст_4 Кикадзе Карина

1988 ж Отд_1

Ст_5 Мимадзе Ашот 1989 м Отд_2

Ст_6 Клычков Артем 1980 м Отд_3

Ст_7 Шлычков

Петр 1985 м Отд_3

Ст_8 Клыкова Юдифь

1990 ж Отд_2

Ст_9 Тыков Арен 1989 м Отд_3

Ст_10 Черняк Юрий 1984 м Отд_1

Ст_11 Беляк Алена 1986 ж Отд_3

Ст_12 Карабан Юлия 1988 ж Отд_2

Отделы

Код_отд Отдел Нач_отд Тел_отд

Отд_1 221 Культов 5527564

Отд _2 222 Полосова

2944211

Отд _3 223 Фисенко 5341322

Распределение информации по отдельным таблицам требует определен-

ных знаний и опыта.

Сформулируем основные рекомендации формирования исходных

таблиц БД.

1. Каждая таблица должна характеризовать определенную функцио-

нальную нагрузку. Например, таблица Поступление_товара характеризует все,

что касается поступления товаров. Ключевым полем является

Код_поступления, Следующие поля будут характеризовать это поступление:

Дата_поступления, Товар (код_тов), Количество, Сотрудник (код_сотр), при-

нявший товар и т.д.

Таблица Товары характеризует все, что касается каждого товара:

Код_товара (ключевое поле), Стоимость (единицы), Размер, Вес, Производи-

тель, Группа, гарантийный срок, особенности применения и т.д.

2. Для каждого существенного типа сведений следует создать от-

дельное поле. Например, в таблице Заказчики это могут быть поля: название

компании, адрес, город, страна, номер телефона. В каждом поле данные

должны быть только одного типа.

3. Информацию целесообразно разбивать на минимальные логиче-

ские компоненты. Например, «Имя» и «Фамилия» сотрудников удобно раз-

бить на два поля, что облегчит сортировку по фамилиям (именам).

4. В значениях ключевого поля целесообразно использовать, какие-

то смысловые (текстовые или знаковые) обозначения. Например, «Сотр_1»,

«Сотр_2», и т.д. - в ключевом поле таблицы «Сотрудники»; или «Пост_1»,

«Пост _2», и т.д. - в ключевом поле таблицы «Поступление_товаров».

Использование счетчиков в качестве ключевых полей, конечно, допус-

тимо, но не позволяет сделать наглядным функциональную принадлежность

(к конкретной таблице) данного ключевого поля. Особенно это негативно

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

внешних ключевых полей (из разных таблиц). Это в дальнейшем может при-

вести, не только к существенным неудобствам, но и к ошибкам.

5. В исходных таблицах БД не следует приводить вычисляемые дан-

ные, которые являются результатом вычислений или выражений (сочетание

математических и логических операторов). Например, если в таблицах имеют-

ся поля, содержащие «Цену» и «Количество» каких то товаров, то не следует

создавать поле, содержащее произведение значений этих полей. Различные

вычисления следует реализовывать только в запросах.

6. Такие характеристики, как: возраст, опыт работы, время работы на

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

лицах указывать не в числовом формате, а в формате дата/время (например,

23.05.2010 или 14.06.2010 14:53.), чтобы исключить необходимость ежеднев-

ного (ежеминутного) ручного обновления этих данных.

7. Структура (состав полей) исходных таблиц должна формироваться

таким образом, чтобы заполнение каждой строки осуществлялось последова-

тельно и чтобы в дальнейшем не возникала необходимость в корректировке

(обновлении, заполнении) ранее заполненных строк (полей).

Поэтому, целесообразно разнести в разные таблицы, например, инфор-

мацию о выдаче и сдаче книг в библиотеке.

8. Необходимо сохранять неизменными в разных таблицах присво-

енные полям имена.

9. Для тестовой проверки правильности функционирования базы и

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

предусмотреть включение в БД информации отражающей различные вариан-

ты возможных ситуаций. Например, одинаковые фамилии сотрудников, по-

ступления одинаковых товаров от различных (если это возможно) поставщи-

ков, продажа покупателю одного и того же товара в один и тот же день и т.п.

10. Не следует повторять уже имеющиеся в ранее заполненных таб-

лицах, данные. Каждое поле, за исключением, естественно, внешних ключей,

заносится только в одну таблицу.

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

и те же данные будут многократно появляться в нескольких записях. Напри-

мер, при включении полей, «Нач_отд» и «Тел_отд» в таблицу «Сотрудники»

эта информация будет повторяться во многих записях. Если же эти поля по-

местить в новую таблицу «Отделы», то эта информация появится только один

в каждом отделе.

12. Не следует создавать поля для данных, состоящих из нескольких

элементов. Это не позволит в дальнейшем проводить эффективный анализ

имеющееся информации.

Например, «Имя» и «Фамилия» сотрудников удобно разбить на два по-

ля, что облегчит сортировку по фамилиям (именам).

Если в таблице фиксируется поставка товаров, а количество различных

типов товаров не фиксировано, то перечни поступающих в каждой поставке

типов товаров (с указанием количества самих товаров) целесообразно фикси-

ровать в отдельной таблице.

1.4. Ключевые поля и связи

1.4.1 Первичный и внешний ключ

Каждая таблица в реляционной базе данных должна иметь хотя бы одно

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

запись (строку). Такое поле называется полем первичного ключа или первич-

ным ключом (primary key).

Первичный ключ – это ключевое поле (иногда список полей) в табли-

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

обязательно для заполнения. В этом поле каждая запись уникальна, то есть не

допускаются повторения.