Смекни!
smekni.com
Информатика

Методология проектирования баз данных (стр. 6 из 6)

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;

3 Создание вторичных индексов

Для повышения производительности в таблицах «Работник», «Продукция», «Клиент» были созданы вторичные индексы.

Скрипты создания вторичных индексов

CREATE INDEX "Rabotnik_IDX1" ON "Rabotnik" ("R_Fio");

CREATE INDEX "Rabotnik_IDX2" ON "Rabotnik" ("Adres");

CREATE INDEX "Produkcia_IDX1" ON "Produkcia" ("Name");

CREATE INDEX "Produkcia_IDX2" ON "Produkcia" ("Cena");

CREATE UNIQUE INDEX "Klient_IDX1" ON "Klient" ("Name");

CREATE INDEX "Klient_IDX2" ON "Klient" ("Adres");

4 Права доступа

Транзакции 1,2 выполняет отдел кадров

Транзакции 3-6 - зав. делянками

Транзакции 9,7 - нач. базы

Транзакции 8,11,12 - нач. сбыта

Транзакции 10,13 - зав. складом

Приведем примеры таблиц и хранимых процедур.

Листинг процедуры добавления накладной на заказ товара

procedure TDob_Nakl_Zakaz.Button1Click(Sender: TObject);

var i,j:Integer;

Ok:Boolean;

begin

Ok:=False;

if(not DM4.IBTrans_Write.Active)

then DM4.IBTrans_Write.StartTransaction;

j:=0;

if((StringReplace(MaskEdit1.EditText,' ','',[rfReplaceAll])<>'') and

(StringReplace(MaskEdit2.EditText,' ','',[rfReplaceAll])<>''))

then

begin

DM4.IBSP_Dob_Nakl_Z.ParamByName('Name').AsString:=

ComboBox1.Text;

DM4.IBSP_Dob_Nakl_Z.ParamByName('Adres').AsString:=

Edit2.Text;

DM4.IBSP_Dob_Nakl_Z.ParamByName('Nomer').AsInteger:=

StrToInt(StringReplace(MaskEdit1.Text,' ','',[rfReplaceAll]));

DM4.IBSP_Dob_Nakl_Z.ParamByName('Srok').AsInteger:=

StrToInt(StringReplace(MaskEdit2.Text,' ','',[rfReplaceAll]));

DM4.IBSP_Dob_Nakl_Z.ParamByName('Data').AsDate:=

DateTimePicker1.Date;

try

DM4.IBSP_Dob_Nakl_Z.Prepare;

DM4.IBSP_Dob_Nakl_Z.ExecProc;

Ok:=True;

if(Ok) then

begin

for i:=1 to ValueListEditor1.RowCount-1 do

begin

if(StringReplace(StringReplace(ValueListEditor1.Values[ValueListEditor1.Keys[i]],' ','',[rfReplaceAll]),'.',',',[rfReplaceAll])<>'')

then

begin

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('NNakl_Zakaz').AsInteger:=

StrToInt(StringReplace(MaskEdit1.EditText,' ','',[rfReplaceAll]));

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('Naimen').AsString:=

ValueListEditor1.Keys[i];

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('Kolvo').AsFloat:=

StrToFloat(StringReplace(StringReplace(ValueListEditor1.Values[ValueListEditor1.Keys[i]],' ','',[rfReplaceAll]),'.',',',[rfReplaceAll]));

DM4.IBSP_Dob_Str_Nakl_Z.ParamByName('Cena').AsFloat:=

StrToFloat(ListBox1.Items[i-1]);

DM4.IBSP_Dob_Str_Nakl_Z.Prepare;

DM4.IBSP_Dob_Str_Nakl_Z.ExecProc;

j:=j+1;

end;

end

end;

if((Ok) and (j<>0))

then

DM4.IBTrans_Write.Commit

else ShowMessage('Ошибка добавления накладной !');

except

on E: Exception do

begin

if(Pos('Накладная с таким номером уже есть !!!',E.Message)<>0) then

ShowMessage(' Накладная с таким номером уже есть !')

else

ShowMessage('Ошибка БД');

DM4.IBTrans_Write.Rollback;

end;

end;

end

else ShowMessage('Введите номер накладной или срок выполнения !');

end;

Листинг процедуры добавления лесобилета

procedure TDob_Lesobilet.Button1Click(Sender: TObject);

var i,j:Integer;

begin

if(not DM1.IBTrans_Write.Active)

then DM1.IBTrans_Write.StartTransaction;

j:=0;

try

if(Kol_Del=0) then

begin

if(Trim(MaskEdit1.EditText)<>'') then

begin

DM1.IBSP_Dob_Lesobilet.ParamByName('Nomer').AsInteger:=

StrToInt(Trim(MaskEdit1.EditText));

DM1.IBSP_Dob_Lesobilet.ParamByName('Data').AsDate:=

DateTimePicker1.Date;

DM1.IBSP_Dob_Lesobilet.Prepare;

DM1.IBSP_Dob_Lesobilet.ExecProc;

Ok:=True;

MaskEdit1.ReadOnly:=True;

Kol_Del:=Kol_Del+1;

end

else

begin

ShowMessage('Введите номер лесобилета !');

Ok:=False;

end

end;

if((Ok) and (Trim(MaskEdit2.EditText)<>'') and

(Trim(MaskEdit3.EditText)<>'*') and (Trim(MaskEdit4.EditText)<>','))

then

begin

DM1.IBSP_Dob_Delanki.ParamByName('N_Delanka').AsInteger:=

StrToInt(Trim(MaskEdit2.EditText));

DM1.IBSP_Dob_Delanki.ParamByName('Kvadrat').AsString:=

Trim(MaskEdit3.EditText);

DM1.IBSP_Dob_Delanki.ParamByName('Plotschad').AsFloat:=

StrToFloat(Trim(MaskEdit4.EditText));

DM1.IBSP_Dob_Delanki.Prepare;

DM1.IBSP_Dob_Delanki.ExecProc;

DM1.IBSP_Dob_Str_Bilet.ParamByName('NLesobilet').AsInteger:=

StrToInt(Trim(MaskEdit1.EditText));

DM1.IBSP_Dob_Str_Bilet.ParamByName('NDelanka').AsInteger:=

StrToInt(Trim(MaskEdit2.EditText));

DM1.IBSP_Dob_Str_Bilet.Prepare;

DM1.IBSP_Dob_Str_Bilet.ExecProc;

for i:=1 to ValueListEditor1.RowCount-1 do

begin

if(ValueListEditor1.Values[ValueListEditor1.Keys[i]]<>'') then

begin

if(StrToFloat(ValueListEditor1.Values[ValueListEditor1.Keys[i]])>0)

then

begin

DM1.IBSP_Dob_Nal_Drevesini.ParamByName('Kolvo').AsFloat:=

StrToFloat(ValueListEditor1.Values[ValueListEditor1.Keys[i]]);

DM1.IBSP_Dob_Nal_Drevesini.ParamByName('Poroda').AsString:=

ValueListEditor1.Keys[i];

DM1.IBSP_Dob_Nal_Drevesini.ParamByName('Nomer').AsInteger:=

StrToInt(Trim(MaskEdit2.EditText));

DM1.IBSP_Dob_Nal_Drevesini.Prepare;

DM1.IBSP_Dob_Nal_Drevesini.ExecProc;

end

end

else

j:=j+1;

end

end;

if((Ok) and (j<i)) then

DM1.IBTrans_Write.Commit;

except

on E: Exception do

begin

if(Pos('Лесобилет с таким номером уже существует !!!',E.Message)<>0) then

ShowMessage(' Лесобилет с таким номером уже существует !')

else if(Pos(‘Делянка с таким номером уже существует !!!',E.Message)<>0) then

ShowMessage(' Делянка с таким номером уже существует !')

else

ShowMessage('Ошибка БД !');

DM1.IBTrans_Write.Rollback;

end;

end

end;

 
назад
1
2
3
4
5
6
НАПИШИТЕ НАМ
Copyright © Smekni.com. All rigths reserved.