Визуальное моделирование баз данных

Схемы данных и модельно-ориентированный подход

Приложения баз данных - одни из самых распространенных программных систем. Электронная форма хранения данных, учет и обработка различной информации стали неотъемлемой частью бизнеса, делопроизводства, библиотечного, музейного дела и т. д. Данные в таких системах хранятся по многу лет, активно используются и изменяются. В связи с этим структура данных должна:

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

Хороший результат здесь не достигается "за один присест" - сели и спроектировали хорошую структуру данных. В данном случае применима базовая идея визуального моделирования: разработку ПО удобно проводить как процесс создания уточняющих друг друга моделей. Именно так происходит переход от предметной области к работающему ПО.

Модель "сущность-связь"

Итак, при создании структур баз данных принято использовать моделирование, а не сразу писать код, например, на SQL/DDL. Общепринятым способом моделирования структуры данных является модель "сущность-связь", предложенная Петером Ченом еще в 1976 году [8.1].

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

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

На рис. 8.1, а показаны две сущности - "Студент" и "Кафедра", - которые связаны отношением "Принадлежит". Еще точнее будет сказать, что студент принадлежит кафедре. Это пример направленного отношения между двумя сущностями.

На рис. 8.1, б показан пример равноправного отношения между двумя сущностями: "Преподаватель" и "Студент" связаны отношением "Экзамен". На рис. 8.1, в показан пример того, как в одном отношении могут участвовать более чем две сущности.

Редко бывает так, что сущность обозначает конкретный элемент предметной области - студента Васю, преподавателя Петрова А.В. Как правило, сущность обозначает всех возможных студентов, всех возможных преподавателей и т. д. Будем различать сущность-значение и сущность-тип, и аналогично - связи.

У сущностей-типов появляются атрибуты, которые аналогичны атрибутам классов в UML, а сами типы сущностей во многом похожи на классы. На рис. 8.1, г показано, какие атрибуты имеют сущностиНиже я буду называть сущности-типы просто сущностями - это звучит короче и благозвучнее. Тем более, что сущности-значения в рамках данного курса больше не понадобятся. "Студент" и "Кафедра". Например, ФИО - это набор из трех строк на русском языке, представляющих фамилию, имя и отчество студента. Номер курса - это атрибут, который имеет диапазон значений от единицы до шести. Атрибут "специальность" имеет значение из некоторого заданного списка специальностей и т. д.

В примерах, которые будут рассмотрены ниже, используется модель "сущность-связь", представленная в терминах диаграмм классов UML. При этом классы соответствуют типам сущностей, их атрибуты - атрибутам типов, а ассоциации - связям.

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

В процессе проектирования схему данных удобно представлять с помощью следующих моделей (см. рис. 8.2):

Далее следует реализация схемы данных в виде:

Пример концептуальной модели

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

Анализируя эту предметную область, можно выделить следующие сущности - "Студент", "Преподаватель", "Кафедра", "Отделение" и "Факультет", а также их отношения и атрибуты; для отношений показывается множественность. Важно, что в концептуальной модели нет типов атрибутов, а также ключей и индексов, сущности не нормализуются (то есть допускается наличие сложных атрибутов, например "Адрес" и "ФИО"). Все это нужно для того, чтобы такую модель можно было легко обсуждать со специалистами в той предметной области, для которой создается данное приложение, - секретарем декана, заместителем декана по учебной части и пр. Если в концептуальную модель будет добавлена лишняя программистская информация, то, как показывает опыт, она сразу перестанет быть понятной этим людям. В каждом случае этот "порог" может быть своим; он зависит от IT-компетентности специалистов предметной области, соответственно, диапазон используемых модельных средств может варьироваться.

Реалиазация отношения "многие-ко-многим" для реляционных СУБД

Этот вид отношения задает связь одного множества объектов с объектами другого множества. На UML-диаграммах такими являются связи, у которых с обоих концов множественность больше единицы - например, и там и там по звездочке, как у связи, соединяющей сущности "Преподаватель" и "Кафедра" на рис. 8.3. То есть на кафедре может работать много преподавателей, и один преподаватель может работать на многих кафедрах.

Отношение "многие-ко-многим", будучи удобным средством моделирования, не представимо напрямую в реляционной модели данных. Поэтому, рано или поздно, имея в виду, что наши модели схемы данных должны превратиться в структуру реляционных таблиц, это отношение нужно "раскрыть". Часто это целесообразно сделать при переходе от концептуальной модели к логической. И вот почему.

Рассмотрим пример. Слева на рис. 8.4 слева можно видеть пару сущностей из концептуальной модели - "Преподаватель" и "Кафедра", - которые связаны отношением "многие-ко-многим". Справа на этом же рисунке представлена диаграмма, где отношение "многие-ко-многим" раскрыто с помощью новой сущности и пары отношений "один-ко-многим".

В данном случае новой сущностью является "Ставка". При этом на кафедре может быть много ставок, но каждая ставка принадлежит ровно одной кафедре. И у одного преподавателя может быть много ставок, но одна ставка принадлежит только одному преподавателю. Очевидно, что диаграмма слева эквивалентна диаграмме справа. С одним исключением.

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

Таким образом, наличие в предметной области этой важной информации требует, чтобы это отношение "многие-ко-многим" было раскрыто раньше, чем в физической модели - например, при переходе от концептуальной модели к логической.

Пример логической модели

На рис. 8.5 показан тот же фрагмент предметной области, что и на рис. 8.3, но "расписанный" в терминах логической модели.

Каковы отличия моделей, представленных на рис. 8.3 и рис. 8.5? На первый взгляд видно, что появилось больше сущностей, а у атрибутов уже есть типы. Но это далеко не все.

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

Пример физической модели

Диаграмма, представленная на рис. 8.6, описывает физическую модель, соответствующую концептуальной и логической моделям с рис. 8.3 и рис. 8.5. Эта диаграмма создана в Microsoft Visual Studio 2005 и ориентирована на реализацию схемы базы данных на СУБД Microsoft SQL Server.

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

Реализация отношения "один-ко-многим" для реляционных СУБД

Все отношения "один-ко-многим" в физической модели реализованы через вторичные ключи. В качестве примера рассмотрим сущности "Персона" и "Адрес", представленные на рис. 8.7.

Сущность "Персона" представляется таблицей Person, сущность "Адрес" - таблицей Address. Фрагмент на SQL/DDL, соответствующий реализации сущности "Персона", выглядит так:

CREATE TABLE [Person](
	[Id] [int] NOT NULL,
	[FirstName] [varchar](20) NULL,
	[SecondName] [varchar](50) NULL,
	[Patronymic] [varchar](20) NULL,
	[Phone] [varchar](15) NULL,
	[AddressId] [int] NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY([Id] ASC),
 CONSTRAINT [FK_Person_Address] FOREIGN KEY([AddressId]) 
   REFERENCES [Address] ([Id])
)

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

СУБД сама следит за ссылочной целостностью, не позволяя ситуаций, когда удаляется запись из таблицы Address, на которую ссылаются некоторые записи из таблицы Person, а значения этих ссылок не меняются.

Так реализуется отношение 1:0..*. Если же нужно реализовать отношение 0..1:0..*, то нужно позволить вторичному ключу в таблице Person иметь значение NULL.

Реализация отношений 1:0..1 и наследования для реляционных СУБД

Рассмотрим следующий пример. Сущность "Персона" связана отношением 1:0..1 с сущностью "Преподаватель". Это означает, что преподаватель всегда должен быть связан с персоной, но персона не обязана быть преподавателем, а может быть, например, студентом. Сущность "Персона" представляется таблицей Person, сущность "Преподаватель" - таблицей Teacher. Отношение 1:0..1 можно реализовать так:

Соответствующая спецификация таблицы Teacher на SQL/DDL представлена ниже:

CREATE TABLE [Teacher](
	[Id] [int] NOT NULL,
	[Degree] [tinyint] NULL,
	[Rank] [tinyint] NULL,
 CONSTRAINT [PK_Teacher] PRIMARY KEY([Id] ASC),
 CONSTRAINT [FK_Teacher_Person] FOREIGN KEY([Id]) 
   REFERENCES [Person] ([Id])
)

Теперь о наследовании. Заменим его отношением 1:0..1, как это показано на рис. 8.8.

Каждая запись-потомок обязательно имеет запись-предка. Однако такая реализация не ограничивает вхождения записи предка в две записи потомка из разных таблиц-потомков. Следовательно, на эту пару ассоциаций нужно наложить дополнительное ограничение - альтернативность, - которое означает, что если для одной записи таблицы Person реализуется одна из этих ассоциаций, то другая уже не может реализоваться. Кроме того, наша реализация наследования допускает, чтобы сущность "Персона" была абстрактной - в таблице Person могут быть записи, которые не входят в состав каких-либо записей таблиц Teacher и Student. Читателю предлагается самостоятельно подумать, как снять оба этих ограничения.

Реализация агрегирования для реляционных СУБД

Для агрегирования будет предложена очень простая семантика: запись-агрегат следит за своими записями-частями в том смысле, что при удалении целого все его части также автоматически удаляются. Реализуется это через директиву каскадного удаления SQL/DDL - ON CASCADE DELETE, - которая добавляется к описанию вторичного ключа, определяющего соответствующую ассоциацию. Если читателю хочется создать иную семантику для агрегирования, то пусть он сам подумает о том, какую именно и как ее реализовать.

Спецификация структуры данных на SQL/DDL

По физической модели, представленной на рис. 8.5, Microsoft Visual Studio 2005 генерирует код на SQL, который описывает схему базы данных нашего приложения. Фрагменты этого кода были уже представлены выше. Ниже приводится полная спецификация на языке SQL/DDL для нашего примера.

CREATE TABLE [Faculty](
	[Id] [int] NOT NULL,
	[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Faculty] PRIMARY KEY([Id] ASC)
) 

CREATE TABLE [Address](
	[Id] [int] NOT NULL,
	[Street] [varchar](50) NULL,
	[Build] [varchar](50) NULL,
	[Appartment] [varchar](50) NULL,
	[Registration] [tinyint] NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY([Id] ASC)
)

CREATE TABLE [Teacher](
	[Id] [int] NOT NULL,
	[Degree] [tinyint] NULL,
	[Rank] [tinyint] NULL,
 CONSTRAINT [PK_Teacher] PRIMARY KEY([Id] ASC)
)
CREATE TABLE [Student](
	[Id] [int] NOT NULL,
	[StudyGroup] [varchar](50) NULL,
	[Course] [tinyint] NULL,
	[Profession] [varchar](50) NULL,
	[DepartmentId] [int] NOT NULL,
	[ChairId] [int] NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY([Id] ASC)
)
CREATE TABLE [Department](
	[Id] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[FacultyId] [int] NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY([Id] ASC)
)
CREATE TABLE [Chair](
	[Id] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[DepartmentId] [int] NOT NULL,
 CONSTRAINT [PK_Chair] PRIMARY KEY([Id] ASC)
)
CREATE TABLE [Position](
	[TeacherId] [int] NOT NULL,
	[ChairId] [int] NOT NULL,
	[Name] [varchar](20) NULL,
	[Work] [varchar](20) NULL,
 CONSTRAINT [PK_Position] PRIMARY KEY([TeacherId] ASC,	[ChairId] ASC)
)
CREATE TABLE [Person](
	[Id] [int] NOT NULL,
	[FirstName] [varchar](20) NULL,
	[SecondName] [varchar](50) NULL,
	[Patronymic] [varchar](20) NULL,
	[Phone] [varchar](15) NULL,
	[AddressId] [int] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY([Id] ASC)
)
ALTER TABLE [Teacher] ADD CONSTRAINT [FK_Teacher_Person] 
	FOREIGN KEY([Id]) REFERENCES [Person] ([Id])

ALTER TABLE [Student] ADD CONSTRAINT [FK_Student_Chair] 
	FOREIGN KEY([ChairId]) REFERENCES [Chair] ([Id]) ON DELETE CASCADE

ALTER TABLE [Student] ADD CONSTRAINT [FK_Student_Department] 
FOREIGN KEY([DepartmentId]) REFERENCES [Department] ([Id])

ALTER TABLE [Student] ADD CONSTRAINT [FK_Student_Person] 
FOREIGN KEY([Id]) REFERENCES [Person] ([Id])

ALTER TABLE [Department] ADD CONSTRAINT [FK_Department_Faculty] 
FOREIGN KEY([FacultyId]) REFERENCES [Faculty] ([Id]) ON DELETE CASCADE

ALTER TABLE [Chair] ADD CONSTRAINT [FK_Chair_Department] 
FOREIGN KEY([DepartmentId]) REFERENCES [Department] ([Id]) ON DELETE CASCADE

ALTER TABLE [Position] ADD CONSTRAINT [FK_Position_Position] 
FOREIGN KEY([ChairId]) REFERENCES [Chair] ([Id])

ALTER TABLE [Position] ADD CONSTRAINT [FK_Position_Teacher] 
FOREIGN KEY([TeacherId]) REFERENCES [Teacher] ([Id])

ALTER TABLE [Person] ADD CONSTRAINT [FK_Person_Address] 
FOREIGN KEY([AddressId]) REFERENCES [Address] ([Id])

/* BEGIN HANDLE-WRITTEN CODE */
CREATE PROCEDURE [InsertStudent]
  @Id int, @FirstName varchar(20) null, @SecondName varchar(20) null,
    @Patronymic varchar(20) null, @Phone varchar(15) null, @AddressId int null,
    @StudyGroup varchar(50) null, @Course tinyint null, @Profession varchar(50) null, 
    @DepartmentId int null, @ChairId int null
  AS BEGIN

    INSERT INTO Person (Id, FirstName, SecondName, Patronymic, Phone, AddressId)
    VALUES (@Id, @FirstName, @SecondName, @Patronymic, @Phone, @AddressId);

    INSERT INTO Student(Id, StudyGroup, Course, Profession, DepartmentId, ChairId)
    VALUES (@Id, @StudyGroup, @Course, @Profession, @DepartmentId, @ChairId)

  END
/* END HANDLE-WRITTEN CODE */

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

Об инструментальных средствах

На настоящий момент почти все СУБД поддерживают разработку физической модели схем баз данных с автоматической генерацией конечного кода - Microsoft Visual Studio, Oracle и т. д. Имеются также специальные модельные средства, поддерживающие кроме физической модели также и логическую. Одним из лидеров здесь является пакет Erwin компании Computer Associates [8.6]. Концептуальные модели схем баз данных часто создаются в общих, универсальных UML-средах типа IBM Rational Rose.

Контрольные вопросы