Структура базы данных
Аналогично работает триггер на удаление записи из REPAIRS, с той лишь разницей, что при удалении строки с параметром REPAIR_DONE='Y' счетчик ремонтов уменьшается на 1: В таблице работает автоинкремент поля REPAIRTYPE_CODE, который обеспечивают созданный нами генератор и триггер, срабатывающий при вставке в таблицу новой строки: Для автоматического инкремента поля MARK_CODE в базе данных создан… Читать ещё >
Структура базы данных (реферат, курсовая, диплом, контрольная)
База данных была создана в менеджере баз данных InterBase/Firebird. Данный менеджер был выбран, поскольку я уже знаком с его функциональностью и особенностями используемого SQL-диалекта, что в большой степени облегчает работу. Хотя возможности Firebird и не велики по сравнению, скажем, с Microsoft SQL Server, их вполне достаточно для реализации всех задач, которые могут возникнуть в процессе разработки проекта.
Далее представлен код для генерации разработанных нами на этапе анализа таблиц в новой базе Firebird.
1. Таблица 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.
2. Таблица 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.
3. Таблица 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.
4. Таблица 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);
5. Таблица 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.
6. Таблица 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 в вычислимое поле, такой вариант, на мой взгляд, не имеет существенных преимуществ перед триггерами и кроме того усложнил бы структуру приложения для работы с базой данных.
7. Таблица MONTHS.
Код месяца. | Название месяца. | |
CREATE TABLE MONTHS (.
MONTH_NAME Varchar (10),.
MONTH_CODE Integer NOT NULL,.
PRIMARY KEY (MONTH_CODE).
);