Создание таблиц и реализация ограничений

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);

ALTER TABLE "Nalichie" ADD CONSTRAINT "FK_N_Brigadir" FOREIGN KEY ("N_Brigadir") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Nalichie" ADD CONSTRAINT "FK_N_Drevesina_Nalichie" FOREIGN KEY ("N_Drevesina") REFERENCES "Drevesina" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Nalichie" ADD CONSTRAINT "FK_N_Nach_Bazi" FOREIGN KEY ("N_Nach_Baza") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Nalichie_Drevesina" (

 "N_Drevesina" INTEGER NOT NULL,

 "N_Delanka" INTEGER NOT NULL,

 "Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Nalichie_Drevesina" ADD CONSTRAINT "FK_Nalichie_Drevesina" PRIMARY KEY ("N_Drevesina", "N_Delanka");

ALTER TABLE "Nalichie_Drevesina" ADD CONSTRAINT "FK_N_Delanka_Nal_Drev" FOREIGN KEY ("N_Delanka") REFERENCES "Delanka" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Nalichie_Drevesina" ADD CONSTRAINT "FK_N_Drevesina_Nal_Drev" FOREIGN KEY ("N_Drevesina") REFERENCES "Drevesina" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Objem" (

 "N_Brigadir" INTEGER NOT NULL,

 "N_Produkcia" INTEGER NOT NULL,

 "Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Objem" ADD CONSTRAINT "FK_Objem" PRIMARY KEY ("N_Brigadir", "N_Produkcia");

ALTER TABLE "Objem" ADD CONSTRAINT "FK_N_Brigadir_Objem" FOREIGN KEY ("N_Brigadir") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Objem" ADD CONSTRAINT "FK_N_Produkcia_Objem" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Plan" (

 "Numer" "Nomer",

 "Data" DATE NOT NULL,

 "Srok" INTEGER NOT NULL,

 "N_Brigada" INTEGER NOT NULL,

 "Objem_Drevesini" DOUBLE PRECISION NOT NULL,

 "Isp" SMALLINT DEFAULT 0 NOT NULL,

 "N_Nach_Sbita" INTEGER NOT NULL);

ALTER TABLE "Plan" ADD CONSTRAINT "FK_Plan" PRIMARY KEY ("Numer");

ALTER TABLE "Plan" ADD CONSTRAINT "FK_N_Brigada_Plan" FOREIGN KEY ("N_Brigada") REFERENCES "Brigada" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Plan" ADD CONSTRAINT "FK_N_Nach_Sbita_Plan" FOREIGN KEY ("N_Nach_Sbita") REFERENCES "Rabotnik" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Produkcia" (

 "Usl_nomer" INTEGER NOT NULL,

 "Name" FIO,

 "Cena" DOUBLE PRECISION NOT NULL,

 "Ed_Izm" VARCHAR(10) NOT NULL,

 "Old" SMALLINT DEFAULT 0 NOT NULL);

ALTER TABLE "Produkcia" ADD CONSTRAINT "FK_Produkcia" PRIMARY KEY ("Usl_nomer");

CREATE TABLE "Rabotnik" (

 "Usl_nomer" INTEGER NOT NULL,

 "Tab_numer" "Tab_nomer",

 "R_Fio" FIO COLLATE PXW_CYRL,

 "Adres" "Adress" COLLATE PXW_CYRL,

 "Data_Postup" DATE NOT NULL,

 "Data_Nazn" DATE,

 "Dolgnost" INTEGER,

 "N_Brigadi" INTEGER,

 "Uvolen" "Bool" NOT NULL);

ALTER TABLE "Rabotnik" ADD CONSTRAINT "FK_Rabotnik" PRIMARY KEY ("Usl_nomer");

ALTER TABLE "Rabotnik" ADD CONSTRAINT "FK_N_Brigada" FOREIGN KEY ("N_Brigadi") REFERENCES "Brigada" ("Usl_nomer") ON UPDATE CASCADE;

ALTER TABLE "Rabotnik" ADD CONSTRAINT "FK_N_Dolgnost" FOREIGN KEY ("Dolgnost") REFERENCES "Dolgnost" ("N_Dolgn") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Lesn" (

 "N_Naklad_Lesn" INTEGER NOT NULL,

 "N_Delanka" INTEGER NOT NULL);

ALTER TABLE "Stroka_Lesn" ADD CONSTRAINT "FK_Stroka_Lesn" PRIMARY KEY ("N_Naklad_Lesn", "N_Delanka");

ALTER TABLE "Stroka_Lesn" ADD CONSTRAINT "FK_N_Delanka_Stroka_Lesn" FOREIGN KEY ("N_Delanka") REFERENCES "Delanka" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Lesn" ADD CONSTRAINT "FK_N_Nakl_Lesn_Stroka_Lesn" FOREIGN KEY ("N_Naklad_Lesn") REFERENCES "Naklad_Lesnichestvo" ("Numer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Lesobilet" (

 "N_Lesobilet" INTEGER NOT NULL,

 "N_Delanka" INTEGER NOT NULL);

ALTER TABLE "Stroka_Lesobilet" ADD CONSTRAINT "FK_Stroka_Lesobilet" PRIMARY KEY ("N_Lesobilet", "N_Delanka");

ALTER TABLE "Stroka_Lesobilet" ADD CONSTRAINT "FK_N_Delanka_Str_Bilet" FOREIGN KEY ("N_Delanka") REFERENCES "Delanka" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Lesobilet" ADD CONSTRAINT "FK_N_Lesobilet_Str_Bilet" FOREIGN KEY ("N_Lesobilet") REFERENCES "Lesobilet" ("Numer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Plana" (

 "N_Plan" INTEGER NOT NULL,

 "N_Produkcia" INTEGER NOT NULL,

 "Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Stroka_Plana" ADD CONSTRAINT "FK_Stroka_Plana" PRIMARY KEY ("N_Plan", "N_Produkcia");

ALTER TABLE "Stroka_Plana" ADD CONSTRAINT "FK_N_Plan_Stroka_Plana" FOREIGN KEY ("N_Plan") REFERENCES "Plan" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Plana" ADD CONSTRAINT "FK_N_Produkcia_Stroka_Plana" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Prodaga" (

 "N_Naklad_Prodaga" INTEGER NOT NULL,

 "N_Produkcia" INTEGER NOT NULL,

 "Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Stroka_Prodaga" ADD CONSTRAINT "FK_Stroka_Prodaga" PRIMARY KEY ("N_Naklad_Prodaga", "N_Produkcia");

ALTER TABLE "Stroka_Prodaga" ADD CONSTRAINT "FK_N_Produkcia_Str_Prod" FOREIGN KEY ("N_Produkcia") REFERENCES "Produkcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Stroka_Zakaz" (

 "N_Naklad_Zakaz" INTEGER NOT NULL,

 "N_Produkcia" INTEGER NOT NULL,

 "Kolvo" DOUBLE PRECISION NOT NULL);

ALTER TABLE "Stroka_Zakaz" ADD CONSTRAINT "FK_Stroka_Zakaz" PRIMARY KEY ("N_Naklad_Zakaz", "N_Produkcia");

ALTER TABLE "Stroka_Zakaz" ADD CONSTRAINT "FK_N_Nakl_Z_Stroka_Zakaz" FOREIGN KEY ("N_Naklad_Zakaz") REFERENCES "Naklad_Zakaz" ("Numer") ON UPDATE CASCADE;

ALTER TABLE "Stroka_Zakaz" ADD CONSTRAINT "FK_N_Produkcia_Stroka_Zakaz" FOREIGN KEY ("N_Produkcia") REFERENCES "rodukcia" ("Usl_nomer") ON UPDATE CASCADE;

CREATE TABLE "Telefon" (

 "Nomer" "Nomer_Tlf" NOT NULL,

 "N_Firma" INTEGER,

 "N_Rabotnik" INTEGER);

ALTER TABLE "Telefon" ADD CONSTRAINT "PK_Telefon" PRIMARY KEY ("Nomer");

ALTER TABLE "Telefon" ADD CONSTRAINT "FK_N_Rabotnik_Tlf" FOREIGN KEY ("N_Rabotnik") REFERENCES "Rabotnik" ("Usl_nomer") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Telefon" ADD CONSTRAINT "FK_N_firma_Tlf" FOREIGN KEY ("N_Firma") REFERENCES "Klient" ("Usl_nomer") ON DELETE CASCADE ON UPDATE CASCADE;