Основы проектирования реляционных баз данных в среде СУБД MS ACCESS

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

3.   Проектирование БД «Автосалон»

Этап 1. Определение цели создания БД.

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

Целью создания БД Автосалон является автоматизация учета информации по продаже автомобилей.

Функции БД Автосалон:

·  хранение информации о клиентах автосалона, о продаваемых автомобилях и сформированных заказах;

·  обновление и добавление информации (о клиентах, автомобилях и заказах);

·  анализ информации по различным срезам (клиент, автомобиль, заказ);

·  выдача итоговой информации в виде отчетов.

На основе анализа предметной области выделяют примерный перечень информации проектируемой БД:

·  сведения о клиенте автосалона;

·  технические характеристики автомобиля (модель, мощность двигателя, количество дверей, цвет кузова, обивка салона, дополнительное оснащение и т.п.);

·  сведения о сделке (цена автомобиля, транспортные и предпродажные издержки, дата заказа, скидка, сведения об оплате и т.п.).

Этап 2. Выделение информационных объектов предметной области.

На втором этапе проектирования БД составляют описание предметной области в виде реквизитов, извлекаемых из первичных документов – источников загрузки БД:

·  фамилия, имя, отчество клиента автосалона;

·  дата рождения, адрес, телефон клиента;

·  модель автомобиля;

·  характеристики автомобиля (мощность двигателя, количество дверей, цвет кузова, обивка салона, дополнительное оснащение и т.п.);

·  заводская цена автомобиля;

·  издержки (транспортные и предпродажные);

·  дата формирования заказа;

·  предоставляемые скидки, %;

·  оплачено.

Функциональный анализ реквизитов позволяет выделить два информационных объекта: Клиенты и Автомобили (рис. 2.2).

Рис. 2.2. Информационные объекты предметной области

«Продажа автомобилей»

Этап 3. Определение логической структуры БД.

Реальные отношения между информационными объектами являются отношениями «многие-ко-многим», которые непосредственно не поддерживаются реляционными СУБД. Поэтому их следует трансформировать в отношения «один-ко-многим» путем ввода объекта-связки Заказы.

Для установления связей каждому объекту назначается ключ (ключевое поле). При этом первичные ключи объектов Клиенты и Автомобили должны присутствовать как внешние ключи в объекте Заказы (рис. 2.3).

Рис. 2.3. Отношения 1:∞ между информационными объектами

В соответствии с понятиями реляционной СУБД каждому информационному объекту в проектируемой БД будет соответствовать отдельная таблица (Клиенты, Автомобили, Заказы).

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

Таким образом, между таблицами Клиенты и Заказы, а также между таблицами Автомобили и Заказы устанавливаются отношения «один-ко-многим», которые поддерживаются реляционной СУБД.

На основе функционального анализа реквизитов определяют примерный состав и структуру данных базовых таблиц (табл.  2.1).

Таблица 2.1

Состав и структура данных базовых таблиц

При этом выполняются основные требования к содержанию таблиц:

1.  Каждая таблица содержит информацию только на одну тему.

2.  Информация в таблицах не дублируется.

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

При разработке полей для каждой таблицы необходимо учитывать:

1.  Каждое поле должно быть связано с темой таблицы.

2.  Не включать в таблицу данные, которые являются результатом вычисления.

3.  Информацию следует разбивать на наименьшие логические единицы (например, поля Индекс, Страна, Населенный пункт, Почтовый адрес, а не общее поле Адрес).

В результате выполнения теоретических этапов 1–3 получают проект логической структуры БД Автосалон (рис. 2.4), соответствующей информационно-логической модели предметной области.

¥

¥

1

1

                           

Рис. 2.4. Проект логической структуры БД Автосалон

4.  Реализация БД «Автосалон»

Этап 4. Создание таблиц БД средствами СУБД MS Access.

4.1. Загрузить СУБД MS Access. Создать файл БД (Файл/Создать/ Новая БД…). Сохранить БД в рабочей папке, присвоив имя avto.mdb.

4.2. Выбрать в окне БД вкладку Таблицы.

4.3. Создать макет таблицы Автомобили в режиме Конструктора (рис. 2.5).

Рис. 2.5. Макет таблицы Автомобили

Примерное содержание макета таблицы Автомобили (имена полей, их свойства и типы данных) приведено в табл. 2.2.

Таблица 2.2

Примерное содержание макета таблицы Автомобили

Имя поля	Тип данных	Описание (необязательный параметр)	Свойства поля (определяют правила сохранения, отображения и обработки данных в поле)
1	2	3	4
Код модели	Числовой	Ключевое поле, код модели по заводскому каталогу	Индексированное поле: Да/Совпадения не допускаются Ключевое поле задается в менюПравка/Ключевое поле
Модель	Текстовый	Тип кузова	Размер поля: 20 Значение по умолчанию: Corolla Индексированное поле: Да/Совпадения допускаются (одна и та же модель может встречаться в БД многократно с различными вариантами оснащения)
Мощность	Текстовый	Мощность двигателя (кВт/л.с.)	Размер поля: 10 Индексированное поле: Нет
Цвет	Текстовый	Цвет кузова	Размер поля: 20 Индексированное поле: Нет
Количество дверей	Числовой	2 или 4	Размер поля: Байт Формат: Основной Число десятичных знаков: 0 Значение по умолчанию: 4 Условие на значение: 2 Or 4 Сообщение об ошибке: Данное поле может содержать только 2 или 4 Индексированное поле: Нет

Окончание табл. 2.2

1	2	3	4
Коробка передач	*Мастер подстановок, фиксированный набор значений	Автоматика, ручная	Размер поля: 12 Значение по умолчанию: Ручная Условие на значение: «Ручная» Or «Автоматика» Сообщение об ошибке: Допустимы только значения «Ручная» или «Автоматика» Индексированное поле: Нет
Обивка	*Мастер подстановок	Велюр, Кожа, Ткань	Размер поля: 10 Индексированное поле: Нет Для Мастера подстановок выбрать фиксированный набор значений (рис. 2.6)
Другое оснащение	Поле Memo	Дополнительные аксессуары	Значение по умолчанию: Радио/плейер, раздвижная крыша
Заводская цена	Денежный	Заводская продажная нетто-цена	Формат: Денежный Число десятичных знаков: Auto Индексированное поле: Нет
Транспортные издержки	Денежный	Издержки на доставку	Формат: Денежный Число десятичных знаков: Auto Индексированное поле: Нет
Предпродажные издержки	Денежный	Издержки на предпродажную подготовку	Формат: Денежный Число десятичных знаков: Auto Значение по умолчанию: 105 Индексированное поле: Нет
Специальная модель	Логический	Спец. модель или стандартная	Формат: Да/Нет

UUU Внимание!UUU Все поля, за исключением поля Другое оснащение, должны быть обязательными для заполнения (Свойство Обязательное поле: Да).

*Мастер подстановок позволяет создать для указанных полей список, из которого выбирается значение (см. рис. 2.6).

Рис. 2.6. Мастер подстановок с фиксированным набором значений

4.4. Сохранить созданный макет таблицы под именем Автомобили.

4.5. В режиме Таблицы добавить в таблицу Автомобили 3 записи:

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

Примерное содержание макета таблицы Клиенты (имена полей, их свойства и типы данных) приведено в табл. 2.3.

UUU Внимание!UUU Поля Код клиента, Фамилия, Страна должны быть обязательными для заполнения (Свойство Обязательное поле: Да).

Таблица 2.3

Примерное содержание макета таблицы Клиенты

Имя поля	Тип данных	Описание (необязательный параметр)	Свойства поля (определяют правила сохранения, отображения и обработки данных в поле)
Код клиента	Счетчик	Ключевое поле, уникальный номер клиента в БД	Индексированное поле: Да/Совпадения не допускаются Ключевое поле задается в меню Правка/Ключевое поле
Фамилия	Текстовый	Фамилия	Размер поля:      40, Индексированное поле: Да/Совпадения допускаются
Имя	Текстовый	Имя	Размер поля:      20, Индексированное поле: Нет
Отчество	Текстовый	Отчество	Размер поля:      25,
Индекс	Текстовый	Почтовый индекс	Размер поля:      6, Индексированное поле: Нет
Страна	Текстовый	Название страны	Размер поля:      20, Значение по умолчанию:                                 Беларусь Индексированное поле: Да/Совпадения допускаются
Населенный пункт	Текстовый	Название населенного пункта	Размер поля:      40, Значение по умолчанию:                                      Минск Индексированное поле: Да/Совпадения допускаются
Почтовый адрес	Текстовый	Почтовый адрес (улица, дом, квартира)	Размер поля:      50, Индексированное поле: Нет
Телефон	Текстовый	Контактный телефон	Размер поля:      20, Индексированное поле: Нет

4.7. Сохранить созданный макет таблицы под именем Клиенты.

4.8. В режиме Таблицы добавить в таблицу Клиенты 3 записи (значения полей задать самостоятельно).

4.9. Создать макет таблицы Заказы в режиме Конструктора.

Примерное содержание макета таблицы Заказы (имена полей, их свойства и типы данных) приведено в табл. 2.4.

UUU Внимание!UUU Все поля, за исключением поля Скидка, должны быть обязательными для заполнения (Свойство Обязательное поле: Да).

Таблица 2.4

Примерное содержание макета таблицы Заказы

*Используя Мастер подстановок, сформировать для полей Код клиента и Код модели список значений из таблиц Клиенты и Автомобили:

·  для поля Код клиента выбрать для столбца подстановки 2 поля – Код клиента и Фамилия (установить флажок Скрыть ключевое поле);

·  для поля Код модели выбрать для столбца подстановки 2 поля – Код модели и Модель (сбросить флажок Скрыть ключевое поле).

4.10. Сохранить созданный макет таблицы под именем Заказы.

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

4.12. Сформировать схему БД (установить связи между таблицами БД в соответствии с информационно-логической моделью предметной области):

·  выполнить команду Сервис/Схема данных;

·  в диалоговом окне Добавление таблицы последовательно добавить все три таблицы.

UUU Внимание!UUU Окно не отображается, если в таблице Заказы для внешних ключей Код модели и Код клиента сформированы списки подстановок из соответствующих таблиц.

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

UUU Внимание!UUU Если связь уже сформирована при помощи Мастера подстановок, то необходимо вызвать диалоговое окно Изменение связей (выделить связь,КЗМ/Изменить связь…или меню Связи/Изменить связь…).

·  в диалоговом окне установить флажок Обеспечение целостности данных (рис. 2.7);

Рис. 2.7. Формирование связи между таблицами Клиенты и Заказы

·  аналогично установить связь между таблицами Автомобили и Заказы, перетащив ключевое поле Код модели из главной таблицы Автомобили на соответствующее поле таблицы-связки Заказы. В диалоговом окне Изменение связей установить флажок Обеспечение целостности данных;

·  сохранить макет схемы данных (рис. 2.8).

Рис. 2.8. Схема БД Автосалон

5.  Завершить работу с СУБД MS Accеss.

6.  Оформить отчет о выполнении работы.

44444   Содержание отчета   44444

1.  Титульный лист.

2.  Название и цель работы.

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

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