- •Кочкина м.А., Жуков в.О. Проектирование баз данных с использованием субд Access
- •Содержание
- •Введение
- •Лабораторная работа №1 Разработка инфологической модели и создание структуры реляционной базы данных
- •Консультанты Поставщики
- •Продажи Автомобили
- •Поставки Покупатели
- •Лабораторная работа № 2 Создание таблиц базы данных
- •Лабораторная работа № 3 Установка связей между таблицами
- •Лабораторная работа № 4 Создание форм для таблиц базы данных
- •Лабораторная работа № 5 Создание кнопочных форм базы
- •Лабораторная работа № 6 Создание сложных форм
- •Сводные таблицы и сводные диаграммы
- •Лабораторная работа № 7 Создание простых запросов на выборку
- •Лабораторная работа № 8 Создание запросов на изменение данных
- •Лабораторная работа № 9 Перекрестные запросы и групповые операции над данными
- •Лабораторная работа № 10 Запрос на создание новой таблицы
- •Лабораторная работа № 11 Создание отчетов
- •Лабораторная работа № 12 Импорт и экспорт данных
- •Лабораторная работа № 13 Макросы и модули
- •Библиотека
- •Анонсы фильмов на неделю
- •Индивидуальные задания
- •Заключение
- •Список литературы
Лабораторная работа №1 Разработка инфологической модели и создание структуры реляционной базы данных
Инфологическая модель предметной области представляет эту область в виде набора информационных объектов и их структурных связей.
Предметная область нашей будущей базы: деятельность автосалона. В базе должны храниться сведения о том, кто и когда приобрел автомобиль, кто занимался работой с клиентами, информация о поставках машин на склад. В таблицах 1 и 2 изображены примерные данные, которые должны отражаться в системе.
Информационный объект- это модель некоторого реального объекта, процесса или явления, представленная в виде совокупности логически связанных реквизитов. В реляционной модели данных каждый информационный объект описывается отдельной таблицей. Т.о. информация в базе хранится в виде набора взаимосвязанных таблиц, которые принято называть отношениями.
Свойства реляционной модели:
Каждая таблица состоит из однотипных строк и имеет уникальное имя;
Строки таблицы отличаются друг от друга хотя бы одним значением, что позволит однозначно идентифицировать каждую из них;
Столбцы таблицы имеют уникальное имя, и в каждом из них размещаются однородные значения данных;
Порядок следования столбцов и строк не имеет значения;
На пересечении строки и столбца таблицы всегда имеется строго одно значение (или NULL- пустое значение);
Определим перечень информационных объектов, связи между ними, используя метод нормализации отношений.
Нормализация отношений- это формальный аппарат ограничений, накладываемых на формирование таблиц, который позволяет устранить дублирование информации, уменьшить время на ее ввод и корректировку, отследить противоречивости в данных.
Теория нормализации основана на концепции нормальных форм. В теории реляционных баз данных обычно выделяются: первая нормальная форма (1 Н.Ф.), 2 Н.Ф., 3 Н.Ф., нормальная форма Бойса-Кодда, 4 Н.Ф., 5 Н.Ф. (или нормальная форма проекции-соединения). При этом каждая следующая нормальная форма сохраняет в себе свойства предыдущих и, в некотором смысле, лучше их. На практике третья нормальная форма отношений в большинстве случаев достаточна, и привидением к ней процесс проектирования базы обычно заканчивается.
Ограничения первой нормальной формы: значения всех атрибутов отношения атомарны (неделимы). Данное требование является базовым в классической реляционной модели, поэтому любая реляционная таблица, по определению, уже находится в 1 Н.Ф. В таблицах 1 и 2 это требование не выполняется- на пересечении строк и столбцов встречается более одного значения. В таблицах 3 и 4 эта ситуация исправлена, таблицы удовлетворяют всем ограничениям первой нормальной формы.
Обозначим ключевые поля в таблицах.
Ключевое поле (простой ключ)- это поле, каждое значение которого однозначно идентифицирует одну из записей. Все значения ключевого столбца уникальны. Не будем рассматривать тривиальный случай, когда в качестве ключевых значений берутся порядковые номера записей. Если каждая запись таблицы однозначно идентифицируется значениями нескольких полей, то эти поля образуют составной ключ.
Таблица 3. «Продажи». Ни одно из предложенных в ней полей не может рассматриваться в качестве простого ключа- все поля содержат повторяющиеся значения: покупатель может неоднократно совершить покупку; каждый консультант работает с несколькими клиентами; одну и ту же марку машин могут приобрести несколько покупателей; в один и тот же день может быть совершено несколько сделок. Выявим части составного ключа. Ими могут быть, например, поля «Дата продажи» и «ФИО покупателя»- зная два этих значения, мы узнаем и данные о покупателе, и сведения о машине, которую он приобрел, и кто из консультантов работал с данным клиентом (при допущении, что клиент приобретает не более одного авто в день) В таблице ключевые поля выделены двойной рамкой.
Таблица 4. «Поставки». В один день мы можем принять поставку от нескольких поставщиков; один поставщик неоднократно доставляет авто в салон, машины одной марки могут быть приняты от разных поставщиков – обойтись одним простым ключом не получится. Определим составной ключ. Его части составят поля «Дата поставки», «Название поставщика» и «Марка авто». Зная эти данные, однозначно определим значения и всех остальных полей одной из записей (сколько авто этой марки мы получили, по какой цене и пр.)
Отношение находится во второй нормальной форме, если выполняются требования первой нормальной формы и каждый неключевой атрибут находится в полной функциональной зависимости от ключа (зависит от всех его частей).
Что бы перейти от первой нормальной формы ко второй, необходимо:
1. Определить, какие из неключевых атрибутов зависят от всех частей составного ключа и оставить их в таблице;
2. Выявить, от каких частей ключа зависят остальные неключевые атрибуты и вынести их в отдельные таблицы: часть ключа (одно или несколько полей) + поля, находящиеся в зависимости от этой части. Тем самым каждое неключевое поле окажется в полной функциональной зависимости от ключа (состоящего из одной или нескольких частей).
Рассмотрим таблицу 3. От всех частей ключа (и от значения поля «ФИО покупателя», и от «Даты продажи») зависят поля «Марка авто», «Техн. характеристики», «Рис», «Цвет», «Цена», «ФИО консультанта», «Стаж работы», «Адрес консультанта», «Тел», «Дата рождения», «Дети». Эти поля оставляем в исходной таблице. Поля же «Адрес покупателя» и «Телефон покупателя» зависят только от одной части ключа – от поля «ФИО покупателя». Выносим их в отдельную таблицу «Покупатели». Соединяем обе таблицы линией связи (см. рис 1).
Рассмотрим таблицу 4. От всех частей ключа (от значения поля «Дата поставки», «Название поставщика» и «Марка авто») зависят только поля «Количество» и «Цена». Поля «Адрес поставщика» и «Телефон поставщика» зависят от части ключа «Название поставщика». Поля «Техн. характеристики авто» и «Рисунок» зависят от части «Марка авто». В итоге таблица разделится на 3 составляющие (см. рис 2). Назовем таблицы «Поставки», «Поставщики» и «Автомобили».
Отношение находится в третьей нормальной форме, если выполняются требования второй нормальной формы и каждый неключевой атрибут нетранзитивно зависит от первичного ключа.
Функциональная зависимость атрибутов Х и Y называется транзитивной, если существует такой атрибут Z, что имеется функциональная зависимость X ->Z и Z->Y.
Что бы перейти от второй нормальной формы к третьей, необходимо:
Выявить поля, от которых зависят другие неключевые поля;
Создать новую таблицу для каждого такого поля и группы зависящих от него полей;
Удалить перемещенные поля их исходной таблицы, оставив лишь те из них, которые станут внешними ключами.
Рассмотрим рис 1. Поля «Техн. характеристика» и «Рисунок» зависят от ключа не на прямую, а через неключевой атрибут «Марка авто». Выносим эти поля в отдельную таблицу «Автомобили». Связываем разделившиеся части по полю «Марка авто». Поля «Стаж работы», «Адрес консультанта», «Тел», «Дата рождения», «Дети» зависят от неключевого поля «ФИО консультанта». Так же выносим их в отдельную таблицу «Консультанты». Получим набор таблиц с рис 3.
Рассмотрим рис 2. Здесь не наблюдается ни одной цепочки транзитивных зависимостей, поэтому вторая нормальная форма отношений является одновременно и третьей нормальной формой (рис 4)
Таблица 1. Продажи
Покупатели |
Авто |
Цвет |
Цена |
Дата |
Консультанты салона | |||||||||
ФИО |
Адрес |
Тел |
Марка |
Техн. характ. |
Рис |
ФИО |
Стаж |
Адрес |
Тел |
Дата рожд |
Дети | |||
Воронов |
Парковый 3 |
333 |
Logan |
1.5 тонн |
… |
синий |
10000 |
10.09.06 |
Иванов |
1 год |
Юрша 5 |
111 |
21.09.70 |
да |
Laguna |
2 тонны |
… |
черный |
25000 |
14.10.06 |
Петров |
3 года |
Мира 7 |
222 |
17.01.75 |
нет | |||
Смирнов |
Ленина 55 |
444 |
Megane |
1.8 тонн |
… |
черный |
16000 |
10.09.06 |
Иванов |
1 год |
Юрша 5 |
111 |
21.09.70 |
да |
Logan |
1.5 тонн |
… |
красный |
10000 |
20.10.06 | |||||||||
Конев |
Свиязева 6 |
555 |
Symbol |
1.7 тонн |
… |
бордо |
12000 |
10.09.06 |
Петров |
3 года |
Мира 7 |
222 |
17.01.75 |
Нет
|
… |
Таблица 2. Поставки
Поставщики |
Авто |
Количество |
Цена |
Дата поставки | ||||
Название |
Адрес |
Тел |
Марка |
Техн. Характ. |
Рис | |||
Автотрейд |
Россия, … |
1234567
|
Logan |
1.5 тонн |
… |
4 |
9000 |
07.09.06 |
Symbol |
1.7 тонн |
… |
2 |
11000 |
07.09.06 | |||
Logan |
1.5 тонн |
… |
3 |
9000 |
15.09.06 | |||
Renault |
Франция, … |
7654321 |
Megane |
1.8 тонн |
… |
2 |
14000 |
5.08.06 |
Megane |
1.8 тонн |
… |
5 |
14000 |
15.09.06 | |||
Laguna |
2 тонны |
… |
2 |
23000 |
15.09.06 | |||
… |
Таблица 3. Продажи. 1НФ
ФИО пок-ля |
Адрес пок-ля |
Тел |
Марка Авто |
Техн. характ. |
Рис |
Цвет |
Цена |
Дата продажи |
ФИО конс. |
Стаж |
Адрес конс. |
Тел конс. |
Дата рожд |
Дети |
Воронов |
Парковый 3 |
333 |
Logan |
1.5 тонн |
… |
синий |
10000 |
10.09.06 |
Иванов |
1 год |
Юрша 5 |
111 |
21.09.70 |
да |
Воронов |
Парковый 3 |
333 |
Laguna |
2 тонны |
… |
черный |
25000 |
14.10.06 |
Петров |
3 года |
Мира 7 |
222 |
17.01.75 |
нет |
Смирнов |
Ленина 55 |
444 |
Megane |
1.8 тонн |
… |
черный |
16000 |
10.09.06 |
Иванов |
1 год |
Юрша 5 |
111 |
21.09.70 |
да |
Смирнов |
Ленина 55 |
444 |
Logan |
1.5 тонн |
… |
красный |
10000 |
20.10.06 |
Иванов |
1 год |
Юрша 5 |
111 |
21.09.70 |
да |
Конев |
Свиязева 6 |
555 |
Symbol |
1.7 тонн |
… |
бордо |
12000 |
10.09.06 |
Петров |
3 года |
Мира 7 |
222 |
17.01.75 |
Нет |
… |
Таблица 4. Поставки. 1НФ
Название пост-ка |
Адрес пост-ка |
Тел пост-ка |
Марка авто |
Техн. характ. |
Рис |
Количество |
Цена |
Дата поставки |
Автотрейд |
Россия, … |
1234567
|
Logan |
1.5 тонн |
… |
4 |
9000 |
07.09.06 |
Автотрейд |
Россия, … |
1234567
|
Symbol |
1.7 тонн |
… |
2 |
11000 |
07.09.06 |
Автотрейд |
Россия, … |
1234567
|
Logan |
1.5 тонн |
… |
3 |
9000 |
15.09.06 |
Renault
|
Франция, … |
7654321 |
Megane |
1.8 тонн |
… |
2 |
14000 |
5.08.06 |
Renault |
Франция, … |
7654321 |
Megane |
1.8 тонн
|
… |
5 |
14000 |
15.09.06 |
Renault |
Франция, … |
7654321 |
Laguna |
2 тонны |
… |
2 |
23000 |
15.09.06 |
… |
Продажи
-
ФИО
пок-ля
Дата
продажи
Марка
Авто
Техн. характ.
Рис
Цвет
Цена
ФИО
конс.
Стаж
Адрес
конс.
Тел
конс.
Дата рожд
Дети
Воронов
10.09.06
Logan
1.5 тонн
…
синий
10000
Иванов
1 год
Юрша 5
111
21.09.70
да
Воронов
14.10.06
Laguna
2 тонны
…
черный
25000
Петров
3 года
Мира 7
222
17.01.75
нет
Смирнов
10.09.06
Megane
1.8 тонн
…
черный
16000
Иванов
1 год
Юрша 5
111
21.09.70
да
Смирнов
20.10.06
Logan
1.5 тонн
…
красный
10000
Иванов
1 год
Юрша 5
111
21.09.70
да
Конев
10.09.06
Symbol
1.7 тонн
…
бордо
12000
Петров
3 года
Мира 7
222
17.01.75
нет
…
Покупатели
-
ФИО
пок-ля
Адрес
пок-ля
Тел
Воронов
Парковый 3
333
Смирнов
Ленина 55
444
Конев
Свиязева 6
555
…
Рис 1. Схема Покупатели-Продажи. 2НФ
Поставки
-
Название
пост-ка
Дата поставки
Марка авто
Количество
Цена
Автотрейд
07.09.06
Logan
4
9000
Автотрейд
07.09.06
Symbol
2
11000
Автотрейд
15.09.06
Logan
3
9000
Renault
5.08.06
Megane
2
14000
Renault
15.09.06
Megane
5
14000
Renault
15.09.06
Laguna
2
23000
…
Поставщики Автомобили
-
Название
пост-ка
Адрес
пост-ка
Тел
пост-ка
Марка авто
Техн. характ.
Рис
Автотрейд
Россия, …
1234567
Logan
1.5 тонн
…
Symbol
1.7 тонн
…
Renault
Франция, …
7654321
Megane
1.8 тонн
…
Laguna
2 тонны
…
…
…
Рис 2. Схема Поставки-Поставщики-Автомобили. 2НФ
Продажи Автомобили
-
ФИО
пок-ля
Дата
продажи
Марка
Авто
Цвет
Цена
ФИО
конс.
Марка
Авто
Техн. характ.
Рис
Воронов
10.09.06
Logan
синий
10000
Иванов
Logan
1.5 тонн
…
Воронов
14.10.06
Laguna
черный
25000
Петров
Laguna
2 тонны
…
Смирнов
10.09.06
Megane
черный
16000
Иванов
Megane
1.8 тонн
…
Смирнов
20.10.06
Logan
красный
10000
Иванов
Symbol
1.7 тонн
…
Конев
10.09.06
Symbol
бордо
12000
Петров
…
…
Покупатели Консультанты
-
ФИО
пок-ля
Адрес
пок-ля
Тел
ФИО
конс.
Стаж
Адрес
Тел
Дата рожд
Дети
Воронов
Парковый 3
333
Иванов
1 год
Юрша 5
111
21.09.70
да
Смирнов
Ленина 55
444
Петров
3 года
Мира 7
222
17.01.75
нет
Конев
Свиязева 6
555
…
…
Рис 3. Схема Продажи-Покупатели-Автомобили-Консультанты. 3 НФ
Поставки
-
Название
пост-ка
Дата поставки
Марка авто
Количество
Цена
Автотрейд
07.09.06
Logan
4
9000
Автотрейд
07.09.06
Symbol
2
11000
Автотрейд
15.09.06
Logan
3
9000
Renault
5.08.06
Megane
2
14000
Renault
15.09.06
Megane
5
14000
Renault
15.09.06
Laguna
2
23000
…
Поставщики Автомобили
-
Название
пост-ка
Адрес
пост-ка
Тел
пост-ка
Марка авто
Техн. характ.
Рис
Автотрейд
Россия, …
1234567
Logan
1.5 тонн
…
Symbol
1.7 тонн
…
Renault
Франция, …
7654321
Megane
1.8 тонн
…
Laguna
2 тонны
…
…
…
Рис 4. Схема Поставки-Поставщики-Автомобили. 3 НФ
Поставки Поставщики
-
Название
пост-ка
Дата поставки
Марка авто
Количество
Цена
Название
пост-ка
Адрес
Тел
Автотрейд
07.09.06
Logan
4
9000
Автотрейд
Россия, ..
1234567
Автотрейд
07.09.06
Symbol
2
11000
Автотрейд
15.09.06
Logan
3
9000
Renault
Франция,
7654321
Renault
5.08.06
Megane
2
14000
Renault
15.09.06
Megane
5
14000
…
Renault
15.09.06
Laguna
2
23000
Покупатели Автомобили
-
ФИО
пок-ля
Адрес
пок-ля
Тел
Марка авто
Техн. характ.
Рис
Воронов
Парковый 3
333
Logan
1.5 тонн
…
Конев
Свиязева 6
555
Symbol
1.7 тонн
…
Megane
1.8 тонн
…
Laguna
2 тонны
…
Продажи Консультанты
-
ФИО
пок-ля
Дата
продажи
Марка
Авто
Цвет
Цена
ФИО
конс.
ФИО
конс.
Стаж
Адрес
Тел
Дата рожд
Дети
Воронов
10.09.06
Logan
синий
10000
Иванов
Иванов
1 год
Юрша 5
111
21.09.70
да
Воронов
14.10.06
Laguna
черный
25000
Петров
Петров
3 года
Мира 7
222
17.01.75
нет
Смирнов
10.09.06
Megane
черный
16000
Иванов
Рис 5. Схема 3НФ
Смирнов
20.10.06
Logan
красный
10000
Иванов
Конев
10.09.06
Symbol
бордо
12000
Петров
Так как мы решили отразить в базе информацию как о поставках товара, так и о его продаже, свяжем обе схемы по таблице «Автомобили» (рис 5). Действительно, ведь мы и поставляем, и продаем товар одного ассортимента.
Мы определили информационные объекты модели: «Консультанты»; «Автомобили»; «Покупатели»; «Поставщики»; «Поставки»; «Продажи» (рис 6.)