Проектирование базы данных для отдела продаж автосалона методом нисходящего проектирования

Введение

Для разработки и проектирования систем БД используются различные средства моделирования. Конечной целью проектирования, очевидно, является БД. В теории проектирования ИС предметную область принято рассматривать в трех видах:

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

Процесс проектирования разбивается на три этапа:

Концептуальное проектирование.
Собираются и анализируются данные. Редактируются требования к ним.
Для этого проводятся следующие мероприятия:
1. Обследование предметной области
2. Выявление всех фрагментов
3. Моделирование представлений

После этого произведенных действий получается концептуальная модель, которая чаще всего представляется в виде «Сущность-связь»

Логическое проектирование.
Преобразование требований к данным в структуры данных. В результате получаем СУБД-ориентированную структуру БД.
Физическое моделирование.
На данном этапе определяются особенности хранения данных, способов доступа и т.п.

Метод нисходящего проектирования

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

В результате получается иерархическая схема, которая показывает состав и подчиненность отдельных функций. Эта схема также носит название функциональной структуры алгоритма приложения (ФСА).

При построении ФСА приложения надо придерживаться следующей последовательности действий:

Определить цели автоматизации предметной области
Установить состав приложений, которые обеспечивают реализацию поставленных целей
Уточнить характер взаимосвязи приложений и их основные характеристики
Определить необходимые функции обработки данных
Выполнить декомпозицию функций до необходимой структурной сложности

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

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

ER-модель

Модель Сущность-Связь (ER-модель) (англ. entity-relationship model или entity-relationship diagram ) — это модель данных, позволяющая описывать концептуальные схемы. Она предоставляет графическую нотацию, основанную на блоках и соединяющих их линиях, с помощью которых можно описывать объекты и отношения между ними какой-либо другой модели данных. В этом смысле ER-модель является мета-моделью данных, то есть средством описания моделей данных.

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

ER-модель является одной из самых простых визуальных моделей данных (графических нотаций). Она позволяет обозначить структуру «крупными мазками», в общих чертах. Это общее описание структуры называется ER-диаграммой или онтологией выбранной предметной области (area of interest).

На этапе перехода к реализации данной ER-диаграммы в виде реальной информационной системы или программы, происходит отображение ER-модели в более детальную модель данных реляционной (объектной, сетевой, логической, или др.) базы данных, которая называется физической моделью данных по отношению к исходной ER-диаграмме.

Основные понятия ER-диаграмм:

Сущность – класс однотипных объектов информация о которых должна быть учтена в моделях
Экземпляр сущности – представитель сущности
Атрибут сущности - именованная характеристика, являющаяся некоторым свойством сущности
Ключ сущности – неизбыточный набор атрибутов, значения которых в совокупности являются уникальными для каждого экземпляра сущности
Связь – некоторая ассоциация между двумя сущностями
Разделяют три типа связей:
1. один-к-одному – экземпляр первой сущности связан только с одним экземпляром второй сущности
2. один-ко-многим - экземпляр первой сущности связан с несколькими экземплярами второй сущности. Сущность со стороны «один» называется родительской, а со стороны «много» - дочерней
3. много-ко-многим – каждый экземпляр первой сущности может быть связан с несколькими экземплярами другой сущности. Является временным видом связи.

Каждая связь обладает одной из двух модальностей:

Может
Должен

В ER-диаграммах, как и в реляционных моделях БД существует понятие нормальных форм.

Первая НФ – устраняются повторяющиеся атрибуты и группы атрибутов, т.е. выявление неявных сущностей.
Вторая НФ – устраняются атрибуты зависящие от части уникального идентификатора. Такая часть уникального идентификатора определяет отдельную сущность
Третья НФ – устраняются атрибуты, которые зависят от атрибутов не входящих в уникальный идентификатор

Рассмотрим построение ER-диаграммы на примере выполняемой работы:

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

Даталогическая модель

Модель предметной области должна быть представлена в терминах модели конкретной СУБД – в моем случае MS Access. Данная стадия носит название логического моделирования БД. Результатом выполнения этой стадии является концептуальная схема БД. Не все виды связей могут быть реализованы в логичекой модели. На данном этапе требуется преобразовать ER-диаграмму в реляционную схему.

Первый шаг преобразования – превращение каждой сущности в таблицу (отношение). Каждое свойство становится столбцом таблицы.

Второй шаг – преобразование связей во внешние ключи.

После выполнения этих двух шагов я получил следующую схему:

Физическая модель

После получения концептуальной схемы можно перейти к созданию самой БД.

В данном случае БД представлена семью таблицами.

Клуб – 1 строка – один клуб
Тренер – 1 строка – один тренер
Вид спорта – 1 строка – один вид спорта (название)
Соревнование – 1 строка – одно соревнование
Организатор – 1 строка – один организатор
Стадионы – 1 строка – один стадион
Спортсмен – 1 строка – 1 спортсмен

Типы данных

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

Таблица Спортсмен

Столбец

Тип данных

Ограничение

ФИО

String[100]

NOT_NULL

Группа крови

String[10]

NOT_NULL

Вес (кг)

Byte

NOT_NULL

Дата рождения

String[100]

NOT_NULL

Спортивное звание

String[100]

NOT_NULL

Вид спорта

String[100]

NOT_NULL

Состоит в клубе

String[100]

NOT_NULL

Тренируется у

String[100]

NOT_NULL

Таблица Соревнование

Столбец

Тип данных

Ограничения

Вид спорта

String[100]

NOT_NULL

Дата проведения

Date

NOT_NULL

Организатор

String[100]

NOT_NULL

Победитель(и)

String[100]

NOT_NULL

Место проведения

String[100]

NOT_NULL

Таблица Тренер

Столбец

Тип данных

Ограничения

ФИО

String[100]

NOT_NULL

Тренирует по

String[100]

NOT_NULL

Стаж

String[100]

NOT_NULL

Дата рождения

Date

NOT_NULL

Спортивное звание

String[100]

NOT_NULL

Работает в

String[100]

NOT_NULL

Таблица Организатор

Столбец

Тип данных

Ограничения

ФИО / Название организации

String[100]

NOT_NULL

Таблица Стадионы

Столбец

Тип данных

Ограничения

Название

String[100]

NOT_NULL

Вместительность (чел)

Longint

NOT_NULL

Адрес

String[150]

NOT_NULL

Таблица Вид спорта

Столбец

Тип данных

Ограничения

Название

String[100]

NOT_NULL

Таблица Клуб

Столбец

Тип данных

Ограничения

Название

String[100]

NOT_NULL

Стадион

String[100]

NOT_NULL

SQL-запросы

Получить перечень стадионов, вмещающих не более 30000 зрителей.

SELECT Стадионы.Название, Стадионы.[Вместительность (чел)]

FROM Стадионы

WHERE [Вместительность (чел)] >= 30000;

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

SELECT Спортсмен.ФИО, Спортсмен.[Вид спорта]

FROM Спортсмен

WHERE [Вид спорта].Value="Бокс";

Получить список спортсменов, тренирующихся у заданного тренера.

SELECT Спортсмен.ФИО, Спортсмен.[Тренируется у]

FROM Спортсмен

WHERE Спортсмен.[Тренируется у].Value="Вакурин Е.Е.";

Получить список тренеров указанного спортсмена.

SELECT Спортсмен.ФИО, Спортсмен.[Тренируется у]

FROM Спортсмен

WHERE Спортсмен.ФИО="Родинов Г.К.";

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

SELECT Соревнование.Организатор, Соревнование.[Вид спорта]

FROM Соревнование

WHERE Соревнование.Организатор="Росспорт";

Получить список призеров указанного соревнования.

SELECT Соревнование.[Вид спорта], Соревнование.[Победитель(и)]

FROM Соревнование

WHERE Соревнование.[Вид спорта]="Шахматы";

Получить список тренеров по определенному виду спорта.

SELECT Тренер.ФИО, Тренер.[Тренирует по]

FROM Тренер

WHERE Тренер.[Тренирует по] = "Бокс";

Список литературы

Базы Данных – ФОРУМ: ИНФРА-М, 2003 – 352с. Голицына О.Л., Максимов Н.В., Попов И.И.
ru.wikipedia.org
Базы данных. Учебник для вузов - Корона-принт, 2003 – 630с. Автор: А. Д. Хомоненко, В. М. Цыганков, М. Г. Мальцев
Базы данных – Финансы и Статистика, 2003 – 592с. Диго С.М.