См. таблицу доменов.
3 Ограничение ссылочной целостности
Для всех внешних ключей определим действие на случай удаления или изменения родительских записей.
Работник
ВК: номер бригады ссылается на бригада(номер)
при изменении CASCADE, при удалении NO ACTION
Бригадир
ВК: номер бригады ссылается на бригада(номер)
при изменении CASCADE, при удалении NO ACTION
Телефон
ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент(название, адрес)
при изменении CASCADE, при удалении CASCADE
ВК: таб. номер работника ссылается на работник(таб. номер)
при изменении CASCADE, при удалении CASCADE
Накладная на заказ
ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент(название, адрес)
при изменении NO ACTION, при удалении NO ACTION
ВК: таб. номер начальника сбыта ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
Строка накладной на заказ
ВК: номер накладной ссылается на накладная на заказ(номер)
при изменении CASCADE, при удалении NO ACTION
ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)
при изменении NO ACTION, при удалении NO ACTION
Накладная на продажу
ВК: название фирмы-клиента, адрес фирмы-клиента ссылается на фирма-клиент(название, адрес)
при изменении NO ACTION, при удалении NO ACTION
ВК: таб. номер зав. складом ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
Строка накладной на продажу
ВК: номер накладной ссылается на накладная на продажу(номер)
при изменении CASCADE, при удалении NO ACTION
ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)
при изменении NO ACTION, при удалении NO ACTION
План работы
ВК: таб. номер начальника сбыта ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
ВК: номер бригады ссылается на бригада(номер)
при изменении CASCADE, при удалении NO ACTION
Строка плана работы
ВК: номер плана ссылается на план работы(номер)
при изменении CASCADE, при удалении NO ACTION
ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)
при изменении NO ACTION, при удалении NO ACTION
Объём товара
ВК: таб. номер бригадира ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)
при изменении NO ACTION, при удалении NO ACTION
Наличие древесины
ВК: номер делянки ссылается на делянка(номер)
при изменении CASCADE, при удалении NO ACTION
ВК: порода ссылается на древесина(порода)
при изменении NO ACTION, при удалении NO ACTION
Лесобилет
ВК: таб. номер зав. делянками ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
Строка лесобилета
ВК: номер лесобилета ссылается на лесобилет(номер)
при изменении CASCADE, при удалении NO ACTION
ВК: номер делянки ссылается на делянка(номер)
при изменении CASCADE, при удалении NO ACTION
Накладная на возврат делянки лесничеству
ВК: таб. номер зав. делянками ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
Строка накладной на возврат делянки лесничеству
ВК: номер накладной ссылается на накладная на возврат делянки лесничеству(номер)
при изменении CASCADE, при удалении NO ACTION
ВК: номер делянки ссылается на делянка(номер)
при изменении CASCADE, при удалении NO ACTION
Наличие
ВК: таб. номер работника ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
ВК: порода ссылается на древесина(порода)
при изменении NO ACTION, при удалении NO ACTION
Хранение
ВК: таб. номер зав. складом ссылается на работник(таб. номер)
при изменении CASCADE, при удалении NO ACTION
ВК: наименование продукции, цена продукции ссылается на продукция(наименование, цена)
при изменении NO ACTION, при удалении NO ACTION
4 Бизнес правила
Недопустимо, чтобы работники одновременно были приписаны к нескольким бригадам или один работник занимал несколько должностей, было несколько зав. складом, зав. делянками, нач. базы и нач. сбыта, бригады получали делянки, которые уже выпилены, чтобы количество древесины и продукции передавалось больше, чем есть в наличии и т.д.
IV Физическое проектирование
1. Введение контролируемой избыточности
1. Замена ПК семантически незначащими атрибутами
Т.к. значение первичного ключа «Таб. номер» для типа экземпляра сущности «Работник» освобождается, например, при увольнении работника, но в качестве внешнего ключа может встречаться в одной или нескольких дочерних таблицах, то были введён суррогатный ключ «Условный номер» и новый атрибут «Уволен» для логического удаления работника.
Чтобы не отслеживать уникальность первичного ключа в таблицах «Работник» и «Бригадир», они были объединены в одну таблицу «Работник». В то же время, для уменьшения объёма таблицы «Работник» совокупность значений атрибута «Должность» было выделено в отдельную таблицу «Должность» с полями «Условный номер», «Название» и «Занята». Последнее поле служит для контроля выполнения бизнес-правил.
Те же действия были проведены и в отношении типа сущности «Бригада».
Для таблиц «Фирма-клиент» и «Продукция» также были введены суррогатные ключи «Условный номер» и атрибут «Удалён» для повышения скорости выполнения запросов и логического удаления кортежей отношений.
Для таблиц «План работы», «Делянка» были введены дополнительные атрибуты «Выполнен» и «Выпилена» для контроля выполнения бизнес-правил.
В таблице «Древесина» первичный ключ «Порода» был заменён на суррогатный «Условный номер» для уменьшения объёма дочерних таблиц.
В таблице «Хранение» ввиду своей бесполезности был удалён внешний ключ «Таб. номер зав. складом».
2 Создание таблиц и реализация ограничений
Создание таблиц и реализация ограничений, в соответствие с ранее определённым набором отношений, при помощи выбранной СУБД. В качестве СУБД выберем InterBase 6.0.
Скрипты создания таблиц
Замечание: различного рода ограничения реализуются с помощью доменов, триггеров и хранимых процедур.
CREATE TABLE "Brigada" (
"Usl_nomer" INTEGER NOT NULL,
"Specialisacia" VARCHAR(20) NOT NULL,
"Nomer_Ceha" INTEGER,
"N_Brigada" INTEGER NOT NULL,
"Uv" "Bool" NOT NULL);
ALTER TABLE "Brigada" ADD CONSTRAINT "FK_Brigada" PRIMARY KEY ("Usl_nomer");
CREATE TABLE "Delanka" (
"Numer" "Nomer",
"Kvadrat" VARCHAR(5) NOT NULL,
"Plotschad" DOUBLE PRECISION NOT NULL,
"NBrigadir" INTEGER,
"Isp" SMALLINT DEFAULT 0 NOT NULL);
ALTER TABLE "Delanka" ADD CONSTRAINT "FK_Delanka" PRIMARY KEY ("Numer");
ALTER TABLE "Delanka" ADD CONSTRAINT "FK_N_Brigadir_Delanka" FOREIGN KEY ("NBrigadir") REFERENCES "Rabotnik" ("Usl_nomer") ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE "Dolgnost" (
"Zanata" "Bool",
"N_Dolgn" INTEGER NOT NULL,
"Name_Dolgn" VARCHAR(20) NOT NULL COLLATE PXW_CYRL);
ALTER TABLE "Dolgnost" ADD CONSTRAINT "FK_Dolgnost" PRIMARY KEY ("N_Dolgn");
CREATE TABLE "Drevesina" (
"Poroda" VARCHAR(20) NOT NULL,
"Usl_nomer" INTEGER NOT NULL);
ALTER TABLE "Drevesina" ADD CONSTRAINT "FK_Drevesina" PRIMARY KEY ("Usl_nomer");
CREATE TABLE "Hranenie" (
"N_Produkcia" INTEGER NOT NULL,
"Kolvo" DOUBLE PRECISION NOT NULL);
ALTER TABLE "Hranenie" ADD CONSTRAINT "FK_Hranenie" PRIMARY KEY ("N_Produkcia");
ALTER TABLE "Hranenie" ADD CONSTRAINT "FK_N_Produkcia_Hranenie" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;
CREATE TABLE "Klient" (
"Usl_nomer" INTEGER NOT NULL,
"Name" FIO COLLATE PXW_CYRL,
"Adres" "Adress" COLLATE PXW_CYRL,
"Uvolen" "Bool" NOT NULL);
ALTER TABLE "Klient" ADD CONSTRAINT "FK_Klient" PRIMARY KEY ("Usl_nomer");
CREATE TABLE "Lesobilet" (
"Numer" "Nomer",
"Data" DATE NOT NULL,
"N_Zav_Delankami" INTEGER NOT NULL);
ALTER TABLE "Lesobilet" ADD CONSTRAINT "PK_Lesobilet" PRIMARY KEY ("Numer");
ALTER TABLE "Lesobilet" ADD CONSTRAINT "FK_N_Zav_Del_Lesobilet" FOREIGN KEY ("N_Zav_Delankami") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;
CREATE TABLE "Naklad_Lesnichestvo" (
"Numer" "Nomer",
"Data" DATE NOT NULL,
"N_Zav_Delankami" INTEGER NOT NULL);
ALTER TABLE "Naklad_Lesnichestvo" ADD CONSTRAINT "PK_Naklad_Lesnichestvo" PRIMARY KEY ("Numer");
ALTER TABLE "Naklad_Lesnichestvo" ADD CONSTRAINT "FK_N_Zav_Del_Naklad_Lesn" FOREIGN KEY ("N_Zav_Delankami") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;
CREATE TABLE "Naklad_Prodaga" (
"Data" DATE NOT NULL,
"N_Firma" INTEGER NOT NULL,
"Numer" "Nomer" NOT NULL,
"N_Zav_Skladom" INTEGER NOT NULL);
ALTER TABLE "Naklad_Prodaga" ADD CONSTRAINT "PK_Naklad_Prodaga" PRIMARY KEY ("Numer");
ALTER TABLE "Naklad_Prodaga" ADD CONSTRAINT "FK_N_Firma_Nakl_Prodaga" FOREIGN KEY ("N_Firma") REFERENCES "Klient" ("Usl_nomer") ON UPDATE CASCADE;
ALTER TABLE "Naklad_Prodaga" ADD CONSTRAINT "FK_N_Zav_Skl_Naklad_Prodaga" FOREIGN KEY ("N_Zav_Skladom") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;
CREATE TABLE "Naklad_Zakaz" (
"Numer" "Nomer",
"Data" DATE NOT NULL,
"Srok" INTEGER NOT NULL,
"N_Firma" INTEGER NOT NULL,
"N_Nach_Sbita" INTEGER NOT NULL);
ALTER TABLE "Naklad_Zakaz" ADD CONSTRAINT "FK_Naklad_Zakaz" PRIMARY KEY ("Numer");
ALTER TABLE "Naklad_Zakaz" ADD CONSTRAINT "FK_N_Firma_Nakl_Zakaz" FOREIGN KEY ("N_Firma") REFERENCES "Klient" ("Usl_nomer") ON UPDATE CASCADE;
ALTER TABLE "Naklad_Zakaz" ADD CONSTRAINT "FK_N_Nach_Sbita_Nakl_Zakaz" FOREIGN KEY ("N_Nach_Sbita") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;
CREATE TABLE "Nalichie" (
"N_Brigadir" INTEGER,
"N_Nach_Baza" INTEGER,
"N_Drevesina" INTEGER NOT NULL,
"Kolvo" INTEGER NOT NULL);