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

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

Для агрегирования будет предложена очень простая семантика: запись-агрегат следит за своими записями-частями в том смысле, что при удалении целого все его части также автоматически удаляются. Реализуется это через директиву каскадного удаления 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.

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

1. Чем, на ваш взгляд, в модели "сущность-связь" отличаются сущности от связей? Попробуйте привести пограничные примеры, когда одна и та же информация может быть представлена и как сущность, и как связь.
2. Чем отличаются сущности-типы и сущности-значения? Есть ли аналогичное разделение для связей?
3. Дайте определение концептуальной, логической и физической моделей. При этом отталкивайтесь от тех задач, которые призваны решать эти модели в проектах. Обратите внимание на те категории специалистов, для которых эти модели создаются.
4. Объясните, чем физическая модель схемы данных отличается от полной спецификации на SQL/DDL.
5. Что такое отношение "один-ко-многим"?
6. Что такое отношение "многие-ко-многим"?
7. Что такое отношение 1:0..1?
8. Постарайтесь объяснить, почему отношение "многие-ко-многим" не представимо "напрямую" в реляционной модели и его нужно "раскрывать".
9. Почему бывает целесообразно раскрывать отношение "многие-ко-многим" при переходе от концептуальной модели к логической? Когда это целесообразно делать раньше? А когда позже?
10. Расскажите о реализации отношения "многие-ко-многим" в реляционных СУБД.
11. Расскажите о реализации отношения "один-ко-многим" (1:0..*) в реляционных СУБД.
12. Расскажите о реализации отношения 0..1:0..* в реляционных СУБД.
13. Расскажите о реализации отношения 0..1:1 в реляционных СУБД.
14. Расскажите о простейшей семантике агрегирования в реляционных СУБД и о том, как она реализуется.
15. Подумайте над иной семантикой агрегирования и предложите ее реализацию.
16. Расскажите об использовании отношения 0..1:1 при реализации наследования в реляционных СУБД. Расскажите о недостатках этой реализации.
17. Попробуйте предложить другую реализацию наследования в реализационных СУБД.
18. Реализуйте в реляционных СУБД отношение 0..1:0..1. Приведите собственные примеры таких отношений.
19. Подумайте над тем, какая еще информация, отсутствующая в физической модели, может появиться в полной спецификации схемы базы данных на SQL/DDL.