Анализ предметной области
Первоначальный анализ предметной области выявил в системе четыре основных сущности:
Вид станка
Вид ремонта
Станок
Ремонт
Вид станкаопределяется параметрами: страна, год выпуска, марка.Станокхарактеризуется кодом станка, видом станка, и количеством ремонтов, сделанных с данным станком. Между видом станка и станком очевидно имеется не идентифицирующая связь «родитель-потомок».
Вид ремонтаидентифицируется названием, продолжительностью и стоимостью. Для удобства к сущности также добавлен параметр «примечания». Сущность «Ремонт» задается видом ремонта, кодом станка и датой начала ремонта и является подчиненной сущностям «Станок» и «Вид ремонта».
Построим по данному описанию инфологическую модель данных с помощью пакета ERWin:
Модель адекватно описывает предметную область задачи, однако с точки зрения проектирования базы данных имеет ряд недостатков. В частности, при удалении информации о каком-либо виде станка есть вероятность потерять также данные о стране или марке. В целях нормализации для хранения информации о странах и марках создадим отдельные сущности.
С точки зрения управления системой, я считаю, в сущность «Ремонт» необходимо добавить параметр-флаг, указывающий на завершенность или незавершенность ремонта. Это позволит контролировать не только факт получения заказа на ремонт станка, но и его ход его выполнения, поскольку в реальной практике вполне может возникнуть ситуация, когда фирма по какому либо заказу не сможет уложиться в установленные сроки.
Модифицируем инфологическую модель с учетом данных замечаний:
Используя полученную инфологическую модель, разработаем полную атрибутивную модель данных. Определим для каждой сущности типы данных параметров и внешние ключи.
Страна:
КодСтраны (суррогатный ключ): Integer
Название: Varchar(50)
Марка:
КодМарки (суррогатный ключ): Integer
Название марки: Varchar(10)
ВидСтанка:
КодВидаСтанка (суррогатный ключ): Integer
ГодВыпуска:Numeric(4)
КодСтраны (внешний ключ): Integer
КодМарки (внешний ключ): Integer
Станок:
КодСтанка (ключ): Varchar(10)
КоличествоРемонтов: Integer
КодВидаСтанка (внешний ключ): Integer
ВидРемонта:
КодВидаРемонта (суррогатный ключ): Integer
Длительность: Integer
Название ремонта: Varchar(20)
Стоимость: Decimal(10,2)
Примечания: Varchar(100)
Ремонт:
КодСтанка (внешний ключ, ключевое поле): Varchar(10)
КодВидаРемонта (внешний ключ, ключевое поле): Integer
ДатаНачала (ключевое поле): Date
Завершен?: Char(1) – должен принимать только значения 'Y' или 'N'
Примечания: Varchar(100)
Полная атрибутивная модель будет иметь вид:
Воспользуемся инструментом ERWin и создадим по данной инфологической модели физическую модель данных для базы данных типа InterBase:
В структуру добавлена одна вспомогательная таблица MONTHS, которая в будущем облегчит создание запросов.
Структура базы данных
База данных была создана в менеджере баз данных InterBase/Firebird. Данный менеджер был выбран, поскольку я уже знаком с его функциональностью и особенностями используемого SQL-диалекта, что в большой степени облегчает работу. Хотя возможности Firebird и не велики по сравнению, скажем, с Microsoft SQL Server, их вполне достаточно для реализации всех задач, которые могут возникнуть в процессе разработки проекта.
Далее представлен код для генерации разработанных нами на этапе анализа таблиц в новой базе Firebird.
Таблица COUNTRY
|
Код страны |
Название |
|
|
|
CREATE TABLE COUNTRY (
COUNTRY_NAME Varchar(50),
COUNTRY_CODE Integer NOT NULL,
PRIMARY KEY (COUNTRY_CODE)
);
CREATE UNIQUE INDEX XPKCOUNTRY ON COUNTRY(COUNTRY_CODE);
Для данной таблицы создан генератор и триггер для автоинкремента поля COUNTRY_CODE.
CREATE GENERATOR GEN_COUNTRY_ID;
SET GENERATOR GEN_COUNTRY_ID TO 0
CREATE OR ALTER TRIGGER TR_GEN_COUNTRY_ID FOR COUNTRY
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF (NEW.COUNTRY_CODE IS NULL) THEN
NEW.COUNTRY_CODE=GEN_ID(GEN_COUNTRY_ID,1);
end
Таблица MARK
|
Код марки |
Название |
|
|
|
CREATE TABLE MARK (
MARK_NAME Varchar(10) NOT NULL,
MARK_CODE Integer NOT NULL,
PRIMARY KEY (MARK_CODE)
);
CREATE UNIQUE INDEX XPKMARK ON MARK(MARK_CODE);
Для автоматического инкремента поля MARK_CODEв базе данных создан генератор целых чисел и триггер на вставку новой записи в таблицу:
CREATE GENERATOR GEN_MARK_ID;
SET GENERATOR GEN_MARK_ID TO 0
CREATE OR ALTER TRIGGER TR_GEN_MARK_ID FOR MARK
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF (NEW.MARK_CODE IS NULL) THEN
NEW.MARK_CODE=GEN_ID(GEN_MARK_ID,1);
end
Таблица MACHINE_TYPE
|
Код вида станка |
Код страны |
Год выпуска |
Код марки |
|
|
|
|
|
CREATE TABLE MACHINE_TYPE (
MACHINETYPE_CODE Integer NOT NULL,
COUNTRY_CODE Integer NOT NULL,
MACHINETYPE_YEAR Numeric(4,0),
MARK_CODE Integer NOT NULL,
PRIMARY KEY (MACHINETYPE_CODE)
);
ALTER TABLE MACHINE_TYPE ADD FOREIGN KEY (COUNTRY_CODE) REFERENCES COUNTRY(COUNTRY_CODE);
ALTER TABLE MACHINE_TYPE ADD FOREIGN KEY (MARK_CODE) REFERENCES MARK(MARK_CODE);
CREATE INDEX XIF1MACHINE_TYPE ON MACHINE_TYPE(COUNTRY_CODE);
CREATE INDEX XIF2MACHINE_TYPE ON MACHINE_TYPE(MARK_CODE);
CREATE UNIQUE INDEX XPKMACHINE_TYPE ON MACHINE_TYPE(MACHINETYPE_CODE);
Автоинкремент поля MACHINETYPE_CODEобеспечивают генератор целых чисел и триггер на вставку записи в таблицу:
CREATE GENERATOR GEN_MACHINE_TYPE_ID;
SET GENERATOR GEN_MACHINE_TYPE_ID TO 0
CREATE OR ALTER TRIGGER TR_GEN_MACHINE_TYPE_ID FOR MACHINE_TYPE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF (NEW.MACHINETYPE_CODE IS NULL) THEN
NEW.MACHINETYPE_CODE=GEN_ID(GEN_MACHINE_TYPE_ID,1);
end
Таблица MACHINE
|
Код станка |
Код типа станка |
Количество ремонтов |
|
|
|
|
CREATE TABLE MACHINE (
MACHINETYPE_CODE Integer NOT NULL,
MACHINE_REPNUM Integer,
MACHINE_CODE Varchar(10) NOT NULL,
PRIMARY KEY (MACHINE_CODE)
);
ALTER TABLE MACHINE ADD FOREIGN KEY (MACHINETYPE_CODE) REFERENCES MACHINE_TYPE(MACHINETYPE_CODE);
CREATE INDEX XIF1MACHINE ON MACHINE(MACHINETYPE_CODE);
CREATE UNIQUE INDEX XPKMACHINE ON MACHINE(MACHINE_CODE);
Таблица REPAIRTYPE
|
Код вида ремонта |
Название |
Длительность |
Стоимость |
Примечания |
|
|
|
|
|
|
CREATE TABLE REPAIRTYPE (
REPAIRTYPE_NAME Varchar(20),
REPAIRTYPE_CODE Integer NOT NULL,
REPAIRTYPE_DURATION Integer,
REPAIRTYPE_COST Decimal(10,2),
REPAIRTYPE_NOTE Varchar(100),
PRIMARY KEY (REPAIRTYPE_CODE)
);
CREATE UNIQUE INDEX XPKREPAIRTYPE ON REPAIRTYPE(REPAIRTYPE_CODE);
В таблице работает автоинкремент поля REPAIRTYPE_CODE, который обеспечивают созданный нами генератор и триггер, срабатывающий при вставке в таблицу новой строки:
CREATE GENERATOR GEN_REPAIRTYPE_ID;
SET GENERATOR GEN_REPAIRTYPE_ID TO 7
CREATE OR ALTER TRIGGER TR_GEN_REPAIRTYPE_ID FOR REPAIRTYPE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF (NEW.REPAIRTYPE_CODE IS NULL) THEN
NEW.REPAIRTYPE_CODE=GEN_ID(GEN_REPAIRTYPE_ID,1);
end
Таблица REPAIR
|
Код ремонта |
Код станка |
Дата начала |
Выполнен? |
Примечания |
|
|
|
|
|
|
CREATE TABLE REPAIR (
REPAIRTYPE_CODE Integer NOT NULL,
MACHINE_CODE Varchar(10) NOT NULL,
REPAIR_STARTDATE Date DEFAULT CURRENT_DATE NOT NULL,
REPAIR_DONE Char(1) DEFAULT 'N' NOT NULL,
REPAIR_NOTE Varchar(100),
CONSTRAINT PK_REPAIR PRIMARY KEY (MACHINE_CODE,REPAIRTYPE_CODE,REPAIR_STARTDATE)
);
ALTER TABLE REPAIR ADD FOREIGN KEY (MACHINE_CODE) REFERENCES MACHINE(MACHINE_CODE);
ALTER TABLE REPAIR ADD FOREIGN KEY (REPAIRTYPE_CODE) REFERENCES REPAIRTYPE(REPAIRTYPE_CODE);
CREATE INDEX XIF2REPAIR ON REPAIR(REPAIRTYPE_CODE);
CREATE INDEX XIF3REPAIR ON REPAIR(MACHINE_CODE);
CREATE UNIQUE INDEX XPKREPAIR ON REPAIR(REPAIRTYPE_CODE,MACHINE_CODE,REPAIR_STARTDATE);
По условиям задачи данные, содержащиеся в этой таблице, используются в таблице MACHINE, описывающей характеристики станков. От количества ремонтов некоторого станка, зарегистрированных в таблицеREPAIRS(иными словами, от количества записей с конкретным значениемMACHINE_CODEи полемREPAIR_DONEсо значением'Y'), зависит значение поляMACHINE_REPNUM(то есть, собственно, количество ремонтов) соответствующей записи вMACHINE. Поэтому нам необходимо создать механизм, который обеспечивал бы соответствие информации изMACHINEданным вREPAIR. Для этих целей были написаны следующие триггеры:
На вставку записи в таблицу REPAIR:
CREATE OR ALTER TRIGGER TR_REPAIR_AI FOR REPAIR
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if (new.REPAIR_DONE = 'Y')
then begin
update MACHINE
set MACHINE_REPNUM = MACHINE_REPNUM+1
where MACHINE_CODE = new.MACHINE_CODE;
end
end
При вставке в таблицу новой записи триггер проверяет, если поле REPAIR_DONEравно'Y'(то есть, если ремонт станка считается завершенным). Когда данное условие выполняется, триггер ищет в таблицеMACHINEзапись с таким жеMACHINE_CODE, как во вносимой записи, и прибавляет 1 к счетчику ремонтов этой записи.
Аналогично работает триггер на удаление записи из REPAIRS, с той лишь разницей, что при удалении строки с параметромREPAIR_DONE='Y'счетчик ремонтов уменьшается на 1:
CREATE OR ALTER TRIGGER TR_REPAIR_AD FOR REPAIR
ACTIVE AFTER DELETE POSITION 0
AS
begin
if (old.REPAIR_DONE = 'Y')
then begin
update MACHINE
set MACHINE_REPNUM = MACHINE_REPNUM-1
where MACHINE_CODE = old.MACHINE_CODE;
end
end
Наконец, триггер на изменение записи учитывает все возможные ситуации, которые могут повлиять на значение счетчика ремонтов:
CREATE OR ALTER TRIGGER TR_REPAIR_AU FOR REPAIR
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
if (new.MACHINE_CODE = old.MACHINE_CODE) then begin
if ((new.REPAIR_DONE = 'Y') and (old.REPAIR_DONE = 'N')) then begin
update MACHINE
set MACHINE_REPNUM = MACHINE_REPNUM+1
where MACHINE_CODE = new.MACHINE_CODE;
end else if ((new.REPAIR_DONE = 'N') and (old.REPAIR_DONE = 'Y')) then begin
update MACHINE
set MACHINE_REPNUM = MACHINE_REPNUM-1
where MACHINE_CODE = new.MACHINE_CODE;
end
end else if (new.MACHINE_CODE <> old.MACHINE_CODE) then begin
if (old.REPAIR_DONE = 'Y') then begin
update MACHINE
set MACHINE_REPNUM = MACHINE_REPNUM-1
where MACHINE_CODE = old.MACHINE_CODE;
end
if (new.REPAIR_DONE = 'Y') then begin
update MACHINE
set MACHINE_REPNUM = MACHINE_REPNUM+1
where MACHINE_CODE = new.MACHINE_CODE;
end
end
end
С помощью этих триггеров обеспечивается соответствие данных о ремонтах и станках. Хотя данную задачу можно было решить и с помощью превращения MACHINE_REPNUMв вычислимое поле, такой вариант, на мой взгляд, не имеет существенных преимуществ перед триггерами и кроме того усложнил бы структуру приложения для работы с базой данных.
Таблица MONTHS
|
Код месяца |
Название месяца |
|
|
|
CREATE TABLE MONTHS (
MONTH_NAME Varchar(10),
MONTH_CODE Integer NOT NULL,
PRIMARY KEY (MONTH_CODE)
);