Разработка концептуально-логических моделей базы данных организации

5.1.1  Устранение  подсущностей и категорий

Подсущность и категории первой обобщающей сущности «Персона» устранены путем выделения в отдельные сущности категорий «Приемщик»,  «Мастер», «Клиент», «Выдавший», имеющих собственные атрибуты. При этом все выделенные сущности имею только один атрибут, который является идентификатором.

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

5.1.2  Устранение  явных связей типа «один ко  многим» 

Явные связи типа «один  ко многим» заменены неявными путем  копирования атрибутов-идентификаторов родительской сущности в множество атрибутов дочерней сущности. Такое копирование произведено для сущностей «Обувь в выдаче*Замечание», «Исполнение ремонта*Материал для ремонта», «Прием в ремонт*Обувь в приеме». В случае идентифицирующей связи скопированный идентификатор становится компонентом первичного ключа «Обувь в выдаче*Замечание», «Исполнение ремонта*Материал для ремонта», «Прием в ремонт*Обувь в приеме». 

5.2  Спецификации ограничений и правил

В данном подразделе ограничения и правила, сформулированные в разд. 4 для глобальной ER-модели, трансформированы применительно к реляционной модели, а именно:

– ограничения атрибутов сущностей (таблица 5.1);

– ограничения кортежей (таблица 5.2);

– ограничения уникальности (таблица 5.3);

– прочие ограничения (таблица 5.4);

– операционные правила (таблица 5.5);

– стандартные ссылочные правила (таблица 5.6) .

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

Таблица 5.1  

Ограничения атрибутов

Имя атрибута или агрегата	Тип	Размер	Границы или допустимые значения	Структура	Условие	Значение по умолчанию
1. ФИО клиента	строка рус. букв	<=50		1)
2. Дата приема	Дата				2)
3. Код приемщика	Положит. Целое		3)
4. Код клиента	Положит. Целое		4)

Примечания:

1)  Российские фамилии, имена, отчества (первая буква прописная, остальные — строчные; возможны двойные фамилии, разделенные дефисом, многословные имена, разделенные пробелами).

2)  Дата приема> Текущая дата – 100 лет; Дата приема < Текущая дата – 16 лет.

3) Код приемщика>0.

4) Код клиента>0.

 

Таблица 5.2.  

Ограничения кортежей

Группа атрибутов	Ограничение
1  Дата приема, Дата выдачи	Дата выдачи > Дата приема

Примечания: <если есть>

 

Таблица 5.3.  

Ограничения уникальности

Группа атрибутов	Среди каких экземпляров  имеет место уникальность
1  Код клиента	среди всех клиентов
2  Код мастера	среди всех мастеров
3  Код выдавшего	среди всех выдавших

Примечания: <если есть>

 

Таблица 5.4.  

Другие ограничения

Событие	Группа атрибутов	Ограничение
1 Вставка или обновление экз. сущн. Прием в ремонт	Дата приема	Календарные дни, 12 месяцев;

Примечания: <если есть>

 

Таблица 5.5.  

Операционные правила

Событие	Группа атрибутов	Ограничение
1 Удаление экз. сущн. Прием в ремонт	Атрибуты, относящиеся к обуви в приеме: Дата приема, Суть задачи ремонта, Код клиента, Код приемщика … и т.д.	При удалении записи о  каком-либо сотруднике все сведения о нем переносятся в архивную базу с указанием даты-времени, причины удаления и имени пользователя, выполнившего удаление. Эти сведения хранятся в архивной базе не менее 1 года, а затем  могут быть автоматически удалены

Примечания: <если есть>

 

Таблица 5.6.  

Стандартные ссылочные  правила

Родительская сущность	Дочерняя сущность	Правило удаления	Правило обновления
1  Клиент	Прием в ремонт	каскадное	каскадное
2  Мастер	Исполнение  ремонта	строгое	строгое
3  Замечание	Обувь в выдаче	каскадное	каскадное
4  Мастер	Обувь в выдаче	строгое	строгое
5  Материал	Исполнение  ремонта	каскадное	каскадное
6  Дата приема	Прием в ремонт	строгое	строгое

Примечания: <если есть>

 

5.4  Вывод

В результате проектирования глобальной реляционной модели, соответствующей глобальной ER-модели, получены спецификации, задающие 13 сущностей-таблиц. Разработанные спецификации ограничений и операционных правил включают все ограничения и правила, полученные на предыдущем этапе и трансформированные для реляционной модели; дополнительно на данном этапе введено 9 ограничений и 7 правил.

 

6   ПРОЕКТИРОВАНИЕ  МОДЕЛЕЙ ОТОБРАЖЕНИЯ

Данный раздел, являющийся заключительным этапом проектирования концептуальных моделей базы данных, посвящен разработке моделей отображения, связывающих глобальную реляционную модель (разработанную на этапе 5) с внешними иерархическими моделями (разработанными на этапе 2). Сначала разрабатываются иерархии представлений, соответствующие внешним моделям, а затем прорабатывается внутренняя структура отдельных представлений, входящих в состав иерархий. Результаты проектирования приведены в документе 2012.654600.БД.КР.24.МО «Модели отображения».

6.1  Разработка иерархий представлений

В данном подразделе разрабатываются  иерархии представлений (виртуальных сущностей), соответствующие внешним моделям. Для каждой внешней иерархической модели строится соответствующая эквивалентная иерархия представлений. Корню иерархии соответствует сущность внешней модели, а узлам — многозначные агрегаты или атрибуты.

6.1.1  Функция 1 «Прием в ремонт»

Иерархия представлений  для этой функции включает 4 представления, организованных в виде 3-уровневой  древовидной структуры. Корневое представление «vПрием в ремонт» имеет 1 потомка: «vОбувь в приеме», которому в качестве параметра с вышестоящего уровня передается атрибут «Рег. номер приема».

6.2  Разработка  внутренних моделей представлений

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

6.2.1  Функция 1 «Прием в ремонт»

Корневое представление  «vПрием в ремонт» строится путем эквисоединения сущностей «Персона» (по атрибуту «код приемщика»), «Персона» (по атрибуту «код клиента». Виртуальные атрибуты в этом представлении не предусмотрены.

Представление второго  уровня иерархии «vОбувь в приеме» строится путем эквисоединения сущностей «Прием в ремонт*Обувь в приеме» и «Справочник» по атрибутам «Код» и «Тип».Виртуальные атрибуты в этом представлении не предусмотрены.

6.3  Вывод

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

 

1. SQL-код создания базы данных

CREATE TABLE [Персона] (

[Код персоны] NUMERIC NOT NULL,

[ФИО] VARCHAR(50) NOT NULL,

)

CREATE TABLE [Справочник] (

[Код] NUMERIC NOT NULL,

[Тип] VARCHAR (20) NOT NULL,

[Название] VARCHAR (20) NOT NULL, 

)

CREATE TABLE [Прием в ремонт] (

[Рег.номер приема] NUMERIC NOT NULL,

[Дата приема] DATETIME NOT NULL,

    [Код клиента] NUMERIC NOT NULL,

[Код приемщика] NUMERIC NOT NULL ,

[Суть задачи ремонта] VARCHAR(100) NOT NULL,

)

CREATE TABLE [Прием в ремонт*Обувь в приеме] (

[НПП обуви в приеме] NUMERIC NOT NULL,

    [Код вида  обуви] NUMERIC NOT NULL,

    [Код вида  характеристики] NUMERIC NOT NULL,

    [Тип вида  характеристики] VARCHAR (20) NOT NULL,

    [Тип вида обуви] VARCHAR (20) NOT NULL,

    [Рег.номер  приема] NUMERIC NOT NULL,

)

CREATE TABLE [Выдача отремонтированной обуви] (

[Рег.номер выдачи] NUMERIC NOT NULL,

[Код мастера] NUMERIC NOT NULL,

[Рег.номер приема] NUMERIC NOT NULL, 

)

CREATE TABLE [Выдача отремонтированной обуви*Обувь в выдаче] (

[НПП обуви в выдаче] NUMERIC NOT NULL,

[Рег.номер выдаче] NUMERIC NOT NULL,

[Код выдавшего] NUMERIC NOT NULL,

    [Дата приема] DATETIME NOT NULL,

)

CREATE TABLE [Обувь в выдаче*Замечание] (

    [НПП претензии] NUMERIC NOT NULL,

    [НПП обуви  в выдаче] NUMERIC NOT NULL,

[Дата устранения претензии] DATETIME NOT NULL,

[Содержание претензии] VARCHAR (100) NOT NULL,

[Код вида претензии] NUMERIC NOT NULL,

[Тип вида претензии] VARCHAR (20) NOT NULL,

)

CREATE TABLE [Исполнение ремонта] (

[Рег.номер исполнения] NUMERIC NOT NULL,

[Начало/окончание ремонта] DATETIME NOT NULL,

[Рег.номер приема] NUMERIC NOT NULL,

[Код мастера] NUMERIC NOT NULL,

[Суть задачи ремонта] VARCHAR (100) NOT NULL,

[НПП обуви в приеме] NUMERIC NOT NULL, 

)

CREATE TABLE [Исполнение ремонта*Материал для ремонта] (

[Код материала] NUMERIC NOT NULL,

[Рег. номер исполнения] NUMERIC NOT NULL,

[Код вида материала] NUMERIC NOT NULL,

[Тип вида материала] VARCHAR (20) NOT NULL,

)

 

ALTER TABLE [Персона]

ADD CONSTRAINT PK_Персона

PRIMARY KEY ([Код персоны])

 

ALTER TABLE [Справочник]

ADD CONSTRAINT PK_Справочник

PRIMARY KEY ([Код], [Тип])

 

ALTER TABLE [Прием в ремонт]

ADD CONSTRAINT PK_ПриемВР  

            PRIMARY KEY ([Рег.номер приема])

 

ALTER TABLE [Прием в ремонт*Обувь в приеме]

ADD CONSTRAINT PK_ПриемВРоб

PRIMARY KEY ([НПП обуви в  приеме])

 

ALTER TABLE [Выдача отремонтированной обуви]

ADD CONSTRAINT PK_ВыдачаОТРоб

PRIMARY KEY ([Рег.номер выдачи])

 

 

ALTER TABLE [Выдача отремонтированной обуви*Обувь в выдаче]

ADD CONSTRAINT PK_ВыдачаОООвВ

PRIMARY KEY ([НПП обуви в  выдаче])

 

ALTER TABLE [Обувь в выдаче*Замечание]

ADD CONSTRAINT PK_ОвВЗ

PRIMARY KEY ([НПП претензии])

 

ALTER TABLE [Исполнение ремонта]

ADD CONSTRAINT PK_ИспР

PRIMARY KEY ([Рег.номер исполнения])

 

ALTER TABLE [Исполнение ремонта*Материал для ремонта]

ADD CONSTRAINT PK_ИспРМдР

PRIMARY KEY ( [Код материала])

 

 

ALTER TABLE [Прием в ремонт]

ADD CONSTRAINT FK_Клиент

FOREIGN KEY ([Код клиента])

REFERENCES [Персона] ([Код персоны])

ON UPDATE CASCADE

ON DELETE CASCADE

ALTER TABLE [Прием в ремонт]

ADD CONSTRAINT FK_Приемщик

FOREIGN KEY ([Код приемщика])

REFERENCES [Персона] ([Код персоны])

ON UPDATE NO ACTION

ON DELETE NO ACTION

 

ALTER TABLE [Прием в ремонт*Обувь  в приеме]

ADD CONSTRAINT FK_ОбувьВприеме

FOREIGN KEY ([Код вида обуви], [Тип вида обуви])

REFERENCES [Справочник] ([Код], [Тип])

ON UPDATE CASCADE

            ON DELETE CASCADE

 

ALTER TABLE [Прием в ремонт*Обувь  в приеме]

ADD CONSTRAINT FK_ОбувьВприем

FOREIGN KEY ([Код вида характеристики], [Тип вида характеристики])

REFERENCES [Справочник] ([Код], [Тип])

ON UPDATE NO ACTION

ON DELETE NO ACTION

 

ALTER TABLE [Прием в ремонт*Обувь  в приеме]

ADD CONSTRAINT FK_ОбувВприем

FOREIGN KEY ([Рег.номер приема])

REFERENCES [Прием в ремонт] ([Рег.номер приема])

ON UPDATE CASCADE

            ON DELETE CASCADE

 

ALTER TABLE [Выдача отремонтированной обуви]

ADD CONSTRAINT FK_ВдчОТРоб

FOREIGN KEY ([Код мастера])

REFERENCES [Персона] ([Код персоны])

ON UPDATE CASCADE

            ON DELETE CASCADE

 

ALTER TABLE [Выдача отремонтированной  обуви]

ADD CONSTRAINT FK_ВдчОоб

FOREIGN KEY ([Рег.номер приема])

REFERENCES [Прием в ремонт] ([Рег.номер приема])

ON UPDATE NO ACTION

            ON DELETE NO ACTION

 

 

ALTER TABLE [Обувь в выдаче*Замечание]

ADD CONSTRAINT FK_Зам

FOREIGN KEY ([Код вида претензии],[Тип вида претензии])

REFERENCES [Справочник] ([Код], [Тип])

ON UPDATE CASCADE

            ON DELETE CASCADE

ALTER TABLE [Обувь в выдаче*Замечание]

ADD CONSTRAINT FK_Замеч

FOREIGN KEY ([НПП обуви в  выдаче])

REFERENCES [Выдача отремонтированной обуви*Обувь в выдаче] ([НПП обуви в выдаче])

ON UPDATE CASCADE

            ON DELETE CASCADE

 

ALTER TABLE [Исполнение ремонта]

ADD CONSTRAINT FK_ИспРем

FOREIGN KEY ([Код мастера])

REFERENCES [Персона] ([Код персоны])

ON UPDATE CASCADE

           ON DELETE CASCADE

 

ALTER TABLE [Исполнение ремонта]

ADD CONSTRAINT FK_ИсплРем

FOREIGN KEY ([Рег.номер приема])

REFERENCES [Прием в ремонт] ([Рег.номер приема])

ON UPDATE NO ACTION

            ON DELETE NO ACTION

 

ALTER TABLE [Исполнение ремонта]

ADD CONSTRAINT FK_ИсплРм

FOREIGN KEY ([НПП обуви в приеме])