Каталог Рефератов - Информационная система военного округа

	Информационно-образоательный портал
	Рефераты, курсовые, дипломы, научные работы,



МЕНЮ\|

поиск

Информационная система военного округа

ollback transaction

return

end

/* Restrict child "Tehnika" when parent "Podrazdelenie" deleted */

if exists (select 1 from [Tehnika] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''Tehnika''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

/* Restrict child "Voorugenie" when parent "Podrazdelenie" deleted */

if exists (select 1 from [Voorugenie] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''Voorugenie''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

/* Restrict child "Soorugenia" when parent "Podrazdelenie" deleted */

if exists (select 1 from [Soorugenia] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''Soorugenia''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

Set quoted_identifier off

/* Roles permissions */

6. Создание базы данных с помощью CASE Studio 2.21

7. Программа

Запросы:

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, chast.Mesto, chast.tip_i_nomer_obedinenia, chast.nazvanie_armii, sostav.FIO, sostav.Zvanie, sostav.Kod_slugashego FROM chast, sostav WHERE chast.nazvanie_armii=:par1 AND sostav.Nomer_podrazdelenia=chast.Nomer_podrazdelenia

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, chast.Mesto, chast.tip_i_nomer_obedinenia, chast.nazvanie_armii, sostav.FIO, sostav.Zvanie, sostav.Kod_slugashego FROM chast, sostav WHERE chast.Nomer_podrazdelenia=sostav.Nomer_podrazdelenia AND sostav.Tip_sostava=:par1

SELECT Nazvanie_chasti, Nomer_podrazdelenia, Mesto FROM chast WHERE Nomer_podrazdelenia=:par1

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, tehnika.Tip_tehniki, tehnika.kol_vo_tehniki FROM chast, tehnika WHERE chast.Nomer_podrazdelenia=tehnika.Nomer_podrazdelenia AND chast.Nomer_podrazdelenia=:par1

SELECT Soorugenia.Nomer_coorugenia, Soorugenia.Nomer_disloc_obedinenia, Chast.Nazvanie_chasti FROM Soorugenia, chast WHERE chast.Nomer_podrazdelenia=Soorugenia.Nomer_podrazdelenia AND chast.Nomer_podrazdelenia=:par1

SELECT nomer_coorugenia, COUNT(Nomer_disloc_obedinenia) as col FROM Soorugenia GROUP BY nomer_coorugenia HAVING COUNT(Nomer_disloc_obedinenia)>:par1

SELECT chast.Nomer_podrazdelenia, tehnika.kol_vo_tehniki, tehnika.tip_tehniki FROM chast, tehnika WHERE chast.Nomer_podrazdelenia=tehnika.Nomer_podrazdelenia AND tehnika.kol_vo_tehniki>:par1 ORDER BY chast.Nomer_podrazdelenia

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, Voorugenie.Tip_voorug, Voorugenie.kol_vo_voorug FROM chast, Voorugenie WHERE chast.Nomer_podrazdelenia=Voorugenie.Nomer_podrazdelenia AND chast.Nomer_podrazdelenia=:par1

SELECT sostav.special, COUNT(sostav.FIO) as col FROM Chast, sostav WHERE chast.Nomer_podrazdelenia=sostav.Nomer_podrazdelenia GROUP BY sostav.special HAVING COUNT(sostav.FIO)>:par1

SELECT chast.Nomer_podrazdelenia, chast.Nazvanie_chasti, sostav.FIO, sostav.Zvanie, sostav.Kod_slugashego FROM Chast, sostav WHERE chast.Nomer_podrazdelenia=sostav.Nomer_podrazdelenia AND sostav.special=:par1

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, chast.Mesto, Voorugenie.Tip_voorug, Voorugenie.kol_vo_voorug FROM Chast, Voorugenie WHERE chast.Nomer_podrazdelenia= Voorugenie.Nomer_podrazdelenia AND Voorugenie.Tip_voorug=:par1 AND Voorugenie.kol_vo_voorug>10

SELECT nazvanie_armii, COUNT(Nomer_podrazdelenia) AS col FROM chast GROUP BY nazvanie_armii HAVING COUNT(nazvanie_armii)>=ALL(SELECT COUNT(nazvanie_armii) FROM Chast GROUP BY nazvanie_armii)

8. Текст программы

UNIT1:

//---------------------------------------------------------------------------

#include <vcl.h>

#pragma hdrstop

#include "Unit2.h"

#include "Unit1.h"

#include "Unit3.h"

//---------------------------------------------------------------------------

#pragma package(smart_init)

#pragma resource "*.dfm"

TForm1 *Form1;

int colcount;

TQRDBText *qrdbed[10];

TQRLabel *qrlabl[10];

TDBEdit *dbed[10];

TLabel *labl[10];

//---------------------------------------------------------------------------

__fastcall TForm1::TForm1(TComponent* Owner)

: TForm(Owner)

{

Label5->Caption=ADOTable2->TableName;

Label4->Caption=ADOTable1->TableName;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button3Click(TObject *Sender)

{

ADOQuery1->Active=false;

AnsiString zapros;

DataSource3->DataSet=ADOQuery1;

zapros=ComboBox1->Text;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add(zapros);

if (ADOQuery1->Parameters->Count!=0)

ADOQuery1->Parameters->ParamByName("par1")->Value=Edit1->Text;

ADOQuery1->ExecSQL();

ADOQuery1->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button4Click(TObject *Sender)

{

TLocateOptions Opts;

Opts.Clear();

Opts<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("Nomer_podrazdelenia",Edit2->Text,Opts);

ADOTable1->Delete();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Form1->ADOTable1->Active=false;

DBText1->DataField="";

Form2->DBEdit1->DataField="";

Form2->DBEdit2->DataField="";

Form2->DBEdit3->DataField="";

Form2->DBEdit4->DataField="";

Form2->DBEdit5->DataField="";

Form2->DBEdit6->DataField="";

Form2->DBEdit7->DataField="";

//-----------------------------

Form3->QRDBText1->DataField="";

Form3->QRDBText2->DataField="";

Form3->QRDBText3->DataField="";

Form3->QRDBText4->DataField="";

Form3->QRDBText5->DataField="";

Form3->QRDBText6->DataField="";

Form3->QRDBText7->DataField="";

ADOTable1->TableName=ComboBox2->Text;

Label4->Caption=ADOTable1->TableName;

ADOTable1->Active=true;

colcount=ADOTable1->Fields->Count;

//------------------------------------------

dbed[1]=Form2->DBEdit1;

dbed[2]=Form2->DBEdit2;

dbed[3]=Form2->DBEdit3;

dbed[4]=Form2->DBEdit4;

dbed[5]=Form2->DBEdit5;

dbed[6]=Form2->DBEdit6;

dbed[7]=Form2->DBEdit7;

labl[1]=Form2->Label1;

labl[2]=Form2->Label2;

labl[3]=Form2->Label3;

labl[4]=Form2->Label4;

labl[5]=Form2->Label5;

labl[6]=Form2->Label6;

labl[7]=Form2->Label7;

//------------------------------------------------

qrdbed[1]=Form3->QRDBText1;

qrdbed[2]=Form3->QRDBText2;

qrdbed[3]=Form3->QRDBText3;

qrdbed[4]=Form3->QRDBText4;

qrdbed[5]=Form3->QRDBText5;

qrdbed[6]=Form3->QRDBText6;

qrdbed[7]=Form3->QRDBText7;

qrlabl[1]=Form3->QRLabel1;

qrlabl[2]=Form3->QRLabel2;

qrlabl[3]=Form3->QRLabel3;

qrlabl[4]=Form3->QRLabel4;

qrlabl[5]=Form3->QRLabel5;

qrlabl[6]=Form3->QRLabel6;

qrlabl[7]=Form3->QRLabel7;

//------------------------------------------------

for(int i=1;i<=colcount;i++)

{

dbed[i]->Visible=true;

labl[i]->Visible=true;

dbed[i]->DataSource=DataSource1;

dbed[i]->DataField=ADOTable1->Fields->FieldByNumber(i)->FieldName;

labl[i]->Caption=ADOTable1->Fields->FieldByNumber(i)->FieldName;

}

for(int i=colcount+1;i<=7;i++)

{

dbed[i]->Visible=false;

labl[i]->Visible=false;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button2Click(TObject *Sender)

{

ADOTable2->Active=false;

ADOTable2->TableName=ComboBox3->Text;

Label5->Caption=ADOTable2->TableName;

ADOTable2->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button5Click(TObject *Sender)

{

Form2->Show();

TLocateOptions Opts;

Opts.Clear();

Opts<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("Nomer_podrazdelenia",Edit2->Text,Opts);

if (ADOTable1->TableName==WideString("sostav"))

{

TLocateOptions Opts1;

Opts1.Clear();

Opts1<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("kod_slugashego",Edit2->Text,Opts1);

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button6Click(TObject *Sender)

{

ADOTable1->Append();

Form2->Show();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button7Click(TObject *Sender)

{

DataSource3->DataSet=ADOStoredProc1;

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("CREATE PROCEDURE proc2;1 as SELECT nazvanie_armii, COUNT(Nomer_podrazdelenia) AS col FROM chast GROUP BY nazvanie_armii HAVING COUNT(nazvanie_armii)>=ALL(SELECT COUNT(nazvanie_armii) FROM Chast GROUP BY nazvanie_armii)");

ADOStoredProc1->ProcedureName="proc2;1";

ADOQuery1->ExecSQL();

ADOStoredProc1->Active=false;

ADOStoredProc1->ExecProc();

ADOStoredProc1->Active=true;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add ("drop procedure proc2");

ADOQuery1->ExecSQL();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button8Click(TObject *Sender)

{

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("Create trigger trig on Obedinenie for update as begin if update([nazvanie_armii]) begin UPDATE chast SET chast.nazvanie_armii=inserted.nazvanie_armii FROM chast, deleted, inserted WHERE chast.nazvanie_armii=deleted.nazvanie_armii end end");

ADOQuery1->ExecSQL();

ADOTable1->Active=false;

Form2->DBEdit1->DataField="";

Form2->DBEdit2->DataField="";

Form2->DBEdit3->DataField="";

Form2->DBEdit4->DataField="";

Form2->DBEdit5->DataField="";

Form2->DBEdit6->DataField="";

Form2->DBEdit7->DataField="";

DBText1->DataField="";

DBText1->DataSource=DataSource1;

DBText1->DataField="nazvanie_armii";

ADOTable1->TableName="Obedinenie";

Label4->Caption=ADOTable1->TableName;

ADOTable1->Active=true;

ADOTable2->Active=false;

ADOTable2->TableName="Chast" ;

Label5->Caption=ADOTable2->TableName;

ADOTable2->Active=true;

DBText1->DataField="nazvanie_armii";

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button9Click(TObject *Sender)

{

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("DROP TRIGGER trig");

ADOQuery1->ExecSQL();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button11Click(TObject *Sender)

{

ADOTable1->Active=false;

ADOTable1->Active=true;

ADOTable2->Active=false;

ADOTable2->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button10Click(TObject *Sender)

{

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("UPDATE Obedinenie SET nazvanie_armii=:nd1 WHERE nazvanie_armii=:nd2");

ADOQuery1->Parameters->ParamByName("nd1")->Value=Edit3->Text;

ADOQuery1->Parameters->ParamByName("nd2")->Value=DBText1->Caption;

ADOQuery1->ExecSQL();

ADOTable1->Active=false;

ADOTable1->Active=true;

ADOTable2->Active=false;

ADOTable2->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button12Click(TObject *Sender)

{

for(int i=1;i<=colcount;i++)

{

qrdbed[i]->DataSet=Form1->ADOTable1;

qrdbed[i]->DataField=ADOTable1->Fields->FieldByNumber(i)->FieldName;

qrlabl[i]->Caption=ADOTable1->Fields->FieldByNumber(i)->FieldName;

qrdbed[i]->Visible=true;

qrlabl[i]->Visible=true;

}

for(int i=colcount+1;i<=7;i++)

{

qrdbed[i]->Visible=false;

qrlabl[i]->Visible=false;

}

Form3->QRLabel13->Caption=Label4->Caption;

Form3->QuickRep1->Preview();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button13Click(TObject *Sender)

{

Form3->QuickRep1->Print();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button14Click(TObject *Sender)

{

TLocateOptions Opts;

Opts.Clear();

Opts<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("Nomer_podrazdelenia",Edit2->Text,Opts);

}

//---------------------------------------------------------------------------

UNIT2:

#include <vcl.h>

#pragma hdrstop

#include "Unit1.h"

#include "Unit2.h"

//---------------------------------------------------------------------------

#pragma package(smart_init)

#pragma resource "*.dfm"

TForm2 *Form2;

//---------------------------------------------------------------------------

__fastcall TForm2::TForm2(TComponent* Owner)

: TForm(Owner)

{

}

//---------------------------------------------------------------------------

void __fastcall TForm2::Button2Click(TObject *Sender)

{

Form1->ADOTable1->Post();

Hide();

}

//---------------------------------------------------------------------------

void __fastcall TForm2::Button1Click(TObject *Sender)

{

Hide();

}

//---------------------------------------------------------------------------

9. Список литературы

1. "Базы данных: основы, проектирование, использование", М.П. Малыхина, СПб.: БХВ-Петербург, 2004. - 512 с.: ил.

2. "SQL Server 2000 Программирование", в 2 ч./Р. Вьейра: Часть I; Пер. с англ.; Под ред. С.М. Молявко. - М.: Бином. Лаборатория знаний, 2004. - 735 с., ил.

Страницы: 1, 2, 3

© 2003-2013
Рефераты бесплатно, курсовые, рефераты биология, большая бибилиотека рефератов, дипломы, научные работы, рефераты право, рефераты, рефераты скачать, рефераты литература, курсовые работы, реферат, доклады, рефераты медицина, рефераты на тему, сочинения, реферат бесплатно, рефераты авиация, рефераты психология, рефераты математика, рефераты кулинария, рефераты логистика, рефераты анатомия, рефераты маркетинг, рефераты релиния, рефераты социология, рефераты менеджемент.