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

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

Лучший способ изучить теорию – попытаться решить какую-нибудь практическую задачу. Например, такую: создание списка произошедших ДТП в городе Чита.

• Анализ предметной области

Предметная область: общая картотека произошедших ДТП в городе Чита.

Фраза «общая» добавлена не просто так. Дело в том, что для разных пользователей описание одного и того же объекта будет требовать разного набора свойств. Например, нас интересуют общие сведения о произошедших ДТП: кто участвовал в ДТП, где произошло и когда, а сведения, касающиеся тонкостей работы сотрудника Госавтоинспекции, нас интересовать не будет.

При построении ИС необходимы сведения о людях, местах, событиях и понятиях, т.е. об объектах.

Под объектом будем понимать элемент ИС, информацию о котором сохраняется в ИС (рис.2).

Рисунок 2. Виды объектов в БД

При совершении ДТП ИС может содержать такие объекты: сообщение о ДТП, общие сведения о ведении дела по ДТП, участники ДТП, свидетели ДТП, авто участников ДТП и т.п.

Каждый объект обладает набором свойств, которые запоминаются в ИС.

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

Пример.

Класс объектов «модель автомобиля» будет иметь одинаковый набор свойств, описывающих характеристики автомобиля, и каждая модель будет иметь различные значения этих характеристик (см. рис.3).

Свойства, характеризующие объект называются атрибутами объекта.

Пример.

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

Каждый атрибут имеет свое имя – идентификатор.

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

Совокупность характеристик объекта – это запись, тогда под таблицей будем понимать некоторую структурированную информацию, содержащую характеристики объекта или класса объектов (рис.3).

Домен – это набор допустимых значений поля.

Выделим объекты рассматриваемой предметной области и их свойства, т.е. атрибуты.

1. Сообщение о ДТП

Атрибуты:

1. дата совершенного ДТП;

2. время совершенного ДТП;

3. адрес совершенного ДТП;

4. район совершенного ДТП;

5. фамилия, имя и отчество человека сообщившего о ДТП;

6. телефон человека сообщившего о ДТП.

Вынесем их в отдельную таблицу «Сообщения о совершенных ДТП».

2. Общие сведения о ведении дела по ДТП

Атрибуты:

1. округ занимающий данным ДТП;

2. номер дела (открывающие дела нумеруются для облегчения задачи составления картотеки);

3. название вида совершенного ДТП (столкновение, наезд на стоящее транспортное средство, наезд на препятствие, наезд на пешехода, наезд на велосипедиста, падение пассажира);

4. число раненых;

5. число погибших.

Вынесем их в отдельную таблицу «Общие сведения о ведении дела по ДТП».

3. Участники ДТП

Атрибуты:

1. фамилия, имя и отчество участника ДТП;

2. дата рождения участника ДТП;

3. место жительство участника ДТП;

4. телефон участника ДТП;

5. серия и номер водительского удостоверения;

6. статус участника (ранен, погиб, здоров);

7. название авто страховой компании (ОРАНТ, Спасские ворота, Альфа, Военно-страховая компания, Ингосстрах, Росгосстрах, Первая страхования компания, Московская страховая компания, нет).

Вынесем их в отдельную таблицу «Участники ДТП».

4. Свидетели ДТП

Атрибуты:

1. фамилия, имя и отчество свидетеля ДТП;

2. дата рождения свидетеля ДТП;

3. место жительство свидетеля ДТП;

4. телефон свидетеля ДТП.

Вынесем их в отдельную таблицу «Свидетели ДТП».

5. Авто участников ДТП

Атрибуты:

1. государственный номер автомобиля участника ДТП;

2. название марки (УАЗ, ИЖ, ВАЗ, ГАЗ, TOYOTA, HONDA, NISSAN, FORD, MAZDA, MERCEDES, BMW и т.п.);

3. год выпуска автомобиля участника ДТП;

4. цвет автомобиля участника ДТП.

Вынесем их в отдельную таблицу «Авто участников ДТП».

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

Таким образом, мы получили следующие таблицы (см. рис.4).

Сообщения о совершенных ДТП

дата

время

адрес

район

фамилия сообщившего

имя сообщившего

отчество сообщившего

телефон сообщившего

Общие сведения о ведении дела по ДТП

округ занимающийся ДТП

номер дела

вид ДТП

число раненных

число погибших

Участники ДТП

фамилия

отчество

имя

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

место жительства

телефон

серия и номер водительского удостоверения

статус участника

название авто страховой компании

Свидетели ДТП

фамилия

отчество

имя

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

место жительства

телефон

Авто участников ДТП

государственный номер

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

год выпуска

цвет

Рисунок 3. Объекты проектируемой БД

• Нормализация и устранение ошибок

Нормализация отношений – это процесс построения оптимальной структуры таблиц и связей в реляционной БД (процесс уменьшения избыточности информации).

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

Цели, которые преследуются при построении наиболее эффективной структуры данных:

• обеспечить быстрый доступ к данным;

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

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

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

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

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

Можно выделить следующие виды ключевых полей (рис.5).

Рисунок 4. Виды ключевых полей

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

В таблице «Сообщения о совершенных ДТП» для того, чтобы однозначно идентифицировать сообщение о совершенном ДТП введем ключевое поле «номер сообщения»¹ (см. рис.6).

В таблице «Общие сведения о ведении дела по ДТП» для того, чтобы однозначно идентифицировать номер веденного дела поле «номер дела» будем использовать в качестве ключевого.

После того, как мы ввели ключевые поля в таблицы «Сообщения о совершенных ДТП» связи как таковой не организовалось, но если учесть, что сведений о совершенных ДТП может поступить несколько и не по каждому сообщению заводиться дело, а только по первому, то необходимо добавить в таблицу «Общие сведения о ведении дела по ДТП» поле «номер сообщения» (рис.6). Введенное поле будет являться составным ключевым полем.

Рисунок 5. Пример нормализации отношений между таблицами «Сообщения о совершенных ДТП» и «Общие сведения о ведении дела по ДТП»

На данный промежуток времени в городе Чита имеются 4 действующих административных округа, каждый из которых характеризуется своими атрибутами, например, названием и телефоном, поэтому целесообразно административные округа вынести в отдельную таблицу «ГИБДД», а в таблице «Общие сведения о ведении дела по ДТП» сделать составное поле, ссылающееся на номер округа, т.е. поле «номер округа». Для того, чтобы однозначно идентифицировать тот или иной административный округ создадим еще ключевое поле «номер округа» (рис.7) в таблице «ГИБДД».

Рисунок 6. Пример нормализации отношений между таблицами «ГИБДД» и «Общие сведения о ведении дела по ДТП»

Перед тем как преступить к участнику ДТП, нужно обязательно указать участником какого дела он является, поэтому введем поле «номер дела» (составное ключевое поле) в таблице «Участники ДТП», которое будет ссылаться на одноименное поле из таблицы «Общие сведения о ведении дела по ДТП» (рис.8).

Для того, чтобы однозначно идентифицировать участника ДТП введем поле «номер участника», которое сделаем ключевым. Глядя на запись, в которой идет речь об участнике ДТП, мы должны знать, кто он потерпевший или виновник и является ли он пешеходом, водителем, мотоциклистом или велосипедистом (рис.8).

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

• «Группы участников ДТП», в которой будут следующие атрибуты: номер группы и название группы, где поле «номер группы» является ключевым;

• «Виды участников ДТП» с атрибутами номер группы, номер вида, название вида, в которой поле «номер вида» является ключевым, а поле «номер группы» (составное ключевое поле) ссылается на поле с одноименным названием из таблицы «Группы участников ДТП».

Поле «название авто страховой компании» в таблице «Участники ДТП» – это один из атрибутов, который характеризует объект «Авто страховые компании», поэтому создадим таблицу «Авто страховые компании» со следующими атрибутами: номер авто страховой компании, название, адрес, телефон и эмблема, где поле «номер авто страховой компании» сделаем ключевым (рис.8). В таблице «Участники ДТП» поле «название авто страховой компании» – это составное ключевое поле.

Рисунок 7. Пример нормализации отношений для таблицы «Участники ДТП»

При совершении ДТП могут быть свидетели, поэтому в таблице «Свидетели ДТП» необходимо добавить поле «номер дела» (составное ключевое поле), чтобы связать свидетеля с делом, по которому он является.

Для однозначной идентификации свидетеля в таблице «Свидетели ДТП» целесообразно ввести поле «номер свидетеля», которое необходимо сделать ключевым (рис.9).

Рисунок 8. Пример нормализации отношений между таблицами «Свидетели ДТП» и «Общие сведения о ведении дела по ДТП»

При совершении ДТП имеет место хотя бы один автомобиль¹, для того чтобы однозначно идентифицировать автомобиль дополнительных полей вводить не стоит достаточно сделать поле «государственный номер» ключевым, поскольку данное поле является уникальным (двух машин с одним государственным номер быть не может), а также необходимо добавить поле «номер участника» (составное ключевое поле), чтобы связать авто с участником ДТП (рис.10).

Для того, чтобы создать поле «название марки», в таблице «Авто участников ДТП», необходимо создать две таблицы «Группы авто» и «Производители авто» (рис.10).

• «Группы авто», в которой будут следующие атрибуты номер группы и название группы, где поле «номер группы» является ключевым;

• «Производители авто» характеризуется следующими атрибутами: номером группы, номером марки и названием марки. Поле «номер марки» будет ключевым, а поле «номер группы» – составным ключевым полем, поскольку оно ссылается на одноименное название из таблицы «Группы авто».

Рисунок 9. Пример нормализации отношений для таблицы «Авто участников ДТП»

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

Рисунок 10. Нормализованная информационная модель предметной области