Базы данных

Автор: Пользователь скрыл имя, 14 Февраля 2013 в 18:48, курсовая работа

Описание работы

Курстық жобаны әзірлеуде пайдаланылатын ДҚБЖ Microsoft SQL Server 2008 R2 ең көп таралған және ДҚБЖ мұмкіншіліктері зор, клиент-сервер технологиясымен жұмыс істей реляциялық деректер қоры болып табылады.
Осы курстық жобаның мақсаты болып тапсырманың жеке варианты бойынша (вариант №4 – ішкі жүйе «Жұмысшылар») деректер қорын және клиенттік қосымшаны құру болып табылады.

Содержание

Кіріспе
4
1 Тапсырма
5
2 Деректер қоры құрылымын өңдеу
6
2.1 Пәндік аумақтың сипаттамасы
6
2.2 Ақпараттық ағындарды талдау
7
2.3 Инфологиялық модельді құру
8
2.3.1 Мәндерді нормализациялау процедурасы
14
2.4 Даталогиялық модельдің құрылуы
14
2.5 Мәліметтер базасын іске асыру үшін техникалық-бағдарламалық құралдарды және клиент қосымшасын таңдау
27
3 «Жұмысшылар» мәліметтер базасын құру
28
3.1 Мәліметтер базасының құрылымының сипаттамасы
28
3.2 Мәліметтер базасының кесте қасиеттерінің сипаттамасы
28
3.3Мәліметтер базасының кестелерінің арасындағы қатынастың және мәліметтің бүтінді шарттарының сипаттамасы
34
3.4 Сақталатын процедура сипаттамасы
37
4 Ақпараттық жүйенiң қолданбалы интерфейсiнiң жасалуы
40
4.1 Қолданушы мәзірі
40
4.2 Мәліметтер базасына мәліметтерді өзгерту, қосу, өшіру, көру үшін формалар
41
4.3 Мәліметтер базасының сұраныстарының дайындалуы
44
4.4 Есептеу негіздерінің құрылуы
46
4.5 Сақталатын процедурамен жұмыс
48
4.6 Анықтамалық жүйе
49
Қорытынды
50
Қолданылған әдебиеттердің тізімі
51
Қосымша А
52
Қосымша Б
74

Скачать полностью (6.56 Мб) Сколько стоит заказать работу?

Работа содержит 1 файл

Курсовая МОЯ.doc

— 7.23 Мб (Скачать)

Edit1.Clear;

Exit;

end;

Form2.SQL ('SELECT kod_podrazdeleniya,naimenovanie_podrazdeleniya,tab_nomer_rab_nach_podrazd FROM Podrazdeleniya' );

Form2.InsertQuery('INSERT INTO Podrazdeleniya(kod_podrazdeleniya,naimenovanie_podrazdeleniya,tab_nomer_rab_nach_podrazd) '

+ ' VALUES ('''+Edit1.Text+''','''+Edit2.Text+''','''+IntTostr(ComboBox1.ItemIndex+1)+''')');

MessageBox(Form19.Handle,'Данные добавлены!','Кадры',mb_Ok+mb_IconInformation);

Form19.Close;

Edit1.Clear;

Edit2.Clear;

end;

end.

Бұйрықтар кестесіне мәлімет қосу:

unit add10;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, DB, ADODB, StdCtrls, Buttons;

type

TForm20 = class(TForm)

Label1: TLabel;

Label2: TLabel;

Label3: TLabel;

Edit1: TEdit;

Edit2: TEdit;

BitBtn1: TBitBtn;

BitBtn2: TBitBtn;

ComboBox1: TComboBox;

ADODataSet1: TADODataSet;

DataSource1: TDataSource;

ComboBox2: TComboBox;

Label4: TLabel;

Edit3: TEdit;

Label5: TLabel;

procedure BitBtn1Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form20: TForm20;

implementation

uses Unit2;

{$R *.dfm}

procedure TForm20.BitBtn1Click(Sender: TObject);

begin

if Edit1.Text='' then

begin

MessageBox(Form20.Handle,'Вы не ввели № приказа!','Кадры',mb_Ok+mb_IconWarning);

Edit1.SetFocus;

exit;

end;

if Edit2.Text='' then

begin

MessageBox(Form20.Handle,'Вы не ввели дату!','Кадры',mb_Ok+mb_IconWarning);

Edit2.SetFocus;

exit;

end;

if Edit1.Text='0' then

begin

MessageBox(Form20.Handle,'№ приказа должен быть больше целым положитительным числом!','Кадры',mb_Ok+mb_IconWarning);

Edit1.SetFocus;

exit;

end;

ADODataSet1.Active:=False;

ADODataSet1.CommandText:='select * from Prikazy where nomer_prikaza='''+Edit1.Text+'''';

ADODataSet1.Active:=True;

if ADODataSet1.RecordCount<>0 then

begin

MessageDlg('Такой № приказа уже есть в таблице!', mtError, [mbOk], 0);

Edit1.Clear;

Exit;

end;

Form2.SQL ('SELECT nomer_prikaza,data,tab_nomer_rabotnika,kod_tipa_prikaza,tekst_prikaza FROM Prikazy' );

Form2.InsertQuery('INSERT INTO Prikazy(nomer_prikaza,data,tab_nomer_rabotnika,kod_tipa_prikaza,tekst_prikaza) '

+ ' VALUES ('''+Edit1.Text+''','''+Edit2.Text+''','''+Edit3.Text+''','''+IntTostr(ComboBox1.ItemIndex+1)+''','''+IntTostr(ComboBox2.ItemIndex+1)+''')');

MessageBox(Form20.Handle,'Данные добавлены!','Кадры',mb_Ok+mb_IconInformation);

Form20.Close;

Edit1.Clear;

Edit2.Clear;

end;

end.

Есептеулермен жұмыс жасау:

unit Otchet;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, Menus, DB, Grids, DBGrids, ADODB, StdCtrls,comobj;

type

TForm5 = class(TForm)

ADODataSet1: TADODataSet;

DBGrid1: TDBGrid;

DataSource1: TDataSource;

MainMenu1: TMainMenu;

N1: TMenuItem;

i1: TMenuItem;

Ij1: TMenuItem;

N111: TMenuItem;

ADODataSet2: TADODataSet;

ADODataSet3: TADODataSet;

Button1: TButton;

procedure SQL(query:string);

procedure ExportExcel;

procedure N111Click(Sender: TObject);

procedure FormClose(Sender: TObject; var Action: TCloseAction);

procedure i1Click(Sender: TObject);

procedure Ij1Click(Sender: TObject);

procedure Button1Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form5: TForm5;

m:integer;

implementation

uses Unit2, Otchet1, Otchet2;

{$R *.dfm}

procedure TForm5.ExportExcel;

var

i,j,index: Integer;

MyExcel,sheet: Variant;

begin

try

MyExcel:=CreateOleObject('Excel.Application');

except

MessageBox(handle,pchar('У Вас не та версия Excel или Excel не установлен!!!'),pchar('Ошибка экспорта'),16);

exit;

end;

MyExcel := CreateOleObject('Excel.Application');

MyExcel.Visible := true;

MyExcel.WorkBooks.Add(-4167);

MyExcel.WorkBooks[1].WorkSheets[1].name := 'Отчет';

sheet:=MyExcel.WorkBooks[1].WorkSheets['Отчет'];

index:=4;

with DataSource1.DataSet do

begin

case m of

1: begin

sheet.rows[1].Font.Bold:=True;

sheet.rows[1].Font.Color:= clblue;

sheet.rows[1].Font.Size:= 14;

sheet.rows[3].Font.Bold:=True;

sheet.columns[1].ColumnWidth:= 40;

sheet.columns[2].ColumnWidth:= 20;

sheet.columns[3].ColumnWidth:= 20;

sheet.columns[4].ColumnWidth:= 20;

sheet.cells[1,1]:= 'Список уволенных в текущем году сотрудников предприятия пенсионного возраста';

sheet.cells[3,1]:= 'ФИО работника';

sheet.cells[3,2]:= 'Дата рождения';

sheet.cells[3,3]:= 'Пол';

sheet.cells[3,4]:= 'Дата увольнения';

end;

2: begin

sheet.rows[1].Font.Bold:=True;

sheet.rows[1].Font.Color:= clblue;

sheet.rows[1].Font.Size:= 14;

sheet.rows[3].Font.Bold:=True;

sheet.columns[1].ColumnWidth:= 40;

sheet.columns[2].ColumnWidth:= 30;

sheet.columns[3].ColumnWidth:= 30;

sheet.columns[4].ColumnWidth:= 20;

sheet.columns[5].ColumnWidth:= 30;

sheet.rows[1].Font.Bold:=True;

sheet.rows[1].Font.Color:= clred;

sheet.rows[1].Font.Size:= 14;

sheet.rows[3].Font.Bold:=True;

sheet.cells[1,1]:= 'Пособия ' ;

sheet.cells[3,1]:= 'ФИО работника';

sheet.cells[3,2]:= 'кол-во иждивенцов';

sheet.cells[3,3]:= 'начисление пособий ';

end;

3: begin

sheet.rows[1].Font.Bold:=True;

sheet.rows[1].Font.Color:= clblue;

sheet.rows[1].Font.Size:= 14;

sheet.rows[3].Font.Bold:=True;

sheet.columns[1].ColumnWidth:= 10;

sheet.columns[2].ColumnWidth:= 50;

sheet.columns[3].ColumnWidth:= 20;

sheet.columns[4].ColumnWidth:= 15;

sheet.columns[5].ColumnWidth:= 20;

sheet.cells[1,1]:= 'Приказ';

sheet.cells[3,1]:= 'ФИО работника';

sheet.cells[3,2]:= 'Номер приказа';

sheet.cells[3,3]:= ' дата приказа';

sheet.cells[3,4]:= ' текст приказа';

end;

for i:=1 to RecordCount do

begin

for j:=1 to FieldCount do

sheet.cells[index,j]:= fields[j-1].asstring;

inc(index);

Next;

end;

Next;

end;

procedure TForm5.SQL(query:string);

begin

ADODataSet1.Active:=False;

ADODataSet1.CommandText:=query;

ADODataSet1.Active:=True;

end;

procedure TForm5.FormClose(Sender: TObject; var Action: TCloseAction);

begin

Application.Terminate;

end;

procedure TForm5.i1Click(Sender: TObject);

begin

m:=1;

SQL ('select Rabotnik.FIO_rabotnika, convert(varchar,Rabotnik.data_rozhdeniya,101) as "дата рождения",Rabotnik.pol,Rabotnik.data_uvolneniya '

+ ' From Rabotnik where DATEDIFF(year,data_rozhdeniya,getdate()) >= 58 and pol=''жен'' '

+ ' or DATEDIFF(year,data_rozhdeniya,getdate()) >= 63 and pol=''муж'' '

+ ' and DATEPART(YEAR,Rabotnik.data_uvolneniya) = DATEPART(YEAR,GETDATE())');

end;

procedure TForm5.Ij1Click(Sender: TObject);

var

i : integer;

begin

m:=2;

Form5.ADODataSet3.Active:=False;

Form5.ADODataSet3.CommandText:='SELECT Podrazdeleniya.naimenovanie_podrazdeleniya FROM Podrazdeleniya ORDER BY naimenovanie_podrazdeleniya ';

Form5.ADODataSet3.Active:=True;

Form5.ADODataSet3.First;

for i:=1 to Form5.ADODataSet3.RecordCount do

begin

form7.ComboBox1.Items.Add(Form5.ADODataSet3.FieldByName('naimenovanie_podrazdeleniya').AsString);

Form5.ADODataSet3.Next;

end;

Form7.ComboBox1.ItemIndex:=0;

Form7.Show;

end;

procedure TForm5.N111Click(Sender: TObject);

var

i : integer;

begin

m:=3;

Form5.ADODataSet2.Active:=False;

Form5.ADODataSet2.CommandText:='SELECT Prikazy.nomer_prikaza FROM Prikazy ORDER BY nomer_prikaza ';

Form5.ADODataSet2.Active:=True;

Form5.ADODataSet2.First;

for i:=1 to Form5.ADODataSet2.RecordCount do

begin

form6.ComboBox1.Items.Add(Form5.ADODataSet2.FieldByName('nomer_prikaza').AsString);

Form5.ADODataSet2.Next;

end;

form6.ComboBox1.ItemIndex:=0;

Form6.Show;

end;

procedure TForm5.Button1Click(Sender: TObject);

begin

ExportExcel;

application.ProcessMessages;

MessageBox(handle,pchar('Экспорт в Excel завершен!'),pchar('Экспорт завершен'),64);

end;

end.

unit Otchet1;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls;

type

TForm6 = class(TForm)

ComboBox1: TComboBox;

Button1: TButton;

Label1: TLabel;

procedure FormClose(Sender: TObject; var Action: TCloseAction);

procedure Button1Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form6: TForm6;

implementation

uses Otchet;

{$R *.dfm}

procedure TForm6.FormClose(Sender: TObject; var Action: TCloseAction);

begin

//Application.Terminate;

end;

procedure TForm6.Button1Click(Sender: TObject);

var

s:string;

begin

s:=(ComboBox1.Text);

Form5.SQL ('select FIO_rabotnika, nomer_prikaza, data, tekst_prikaza from Rabotnik, Prikazy '

+ ' where Prikazy.nomer_prikaza='''+s+''' and Rabotnik.tab_nomer_rabotnika=Prikazy.tab_nomer_rabotnika') ;

Form6.Hide;

end;

end.

unit Otchet2;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls;

type

TForm7 = class(TForm)

ComboBox1: TComboBox;

Button1: TButton;

Label1: TLabel;

procedure Button1Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form7: TForm7;

implementation

uses Otchet;

{$R *.dfm}

procedure TForm7.Button1Click(Sender: TObject);

var

s:string;

begin

s:=(ComboBox1.Text);

Form5.SQL( 'SELECT Rabotnik.FIO_rabotnika,Rabotnik.kol_izhdevencov, Rabotnik.oklad*0.1*Rabotnik.kol_izhdevencov as "Начисление пособий" '

+ ' From Rabotnik,Podrazdeleniya where Podrazdeleniya.naimenovanie_podrazdeleniya='''+s+''' '

+ ' and Rabotnik.kod_podrazdeleniya=Podrazdeleniya.kod_podrazdeleniya and kol_izhdevencov>=1' );

Form7.Hide;

end;

end.

Процедуралармен жұмыс:

unit procedures;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, DB, ADODB, Grids, DBGrids, Menus, StdCtrls;

type

TForm8 = class(TForm)

DBGrid1: TDBGrid;

ADODataSet1: TADODataSet;

DataSource1: TDataSource;

MainMenu1: TMainMenu;

N1: TMenuItem;

N221: TMenuItem;

Ij1: TMenuItem;

N2: TMenuItem;

I1: TMenuItem;

ADODataSet2: TADODataSet;

ADODataSet3: TADODataSet;

Button1: TButton;

ADOQuery1: TADOQuery;

procedure SQL(query:string);

procedure N221Click(Sender: TObject);

procedure Ij1Click(Sender: TObject);

//procedure I1Click(Sender: TObject);

procedure Button1Click(Sender: TObject);

procedure N2Click(Sender: TObject);

procedure I1Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form8: TForm8;

implementation

uses Unit2, procedure2, procedure4;

{$R *.dfm}

procedure TForm8.SQL(query:string);

begin

ADODataSet1.Active:=False;

ADODataSet1.CommandText:=query;

ADODataSet1.Active:=True;

end;

procedure TForm8.N221Click(Sender: TObject);

begin

SQL ('SELECT Shtatnoe_raspisanie.kol_vakansii, Shtatnoe_raspisanie.obwee_kol_edenic '

+ ' FROM Shtatnoe_raspisanie where Shtatnoe_raspisanie.obwee_kol_edenic>0 and Shtatnoe_raspisanie.kol_vakansii>0 ');

end;

procedure TForm8.Ij1Click(Sender: TObject);

var

i : integer;

begin

Form8.ADODataSet2.Active:=False;

Form8.ADODataSet2.CommandText:=' SELECT naimenovanie_obrazovaniya FROM Obrazovanie ORDER BY naimenovanie_obrazovaniya ';

Form8.ADODataSet2.Active:=True;

Form8.ADODataSet2.First;

for i:=1 to Form8.ADODataSet2.RecordCount do

begin

Form9.ComboBox1.Items.Add(Form8.ADODataSet2.FieldByName('naimenovanie').AsString);

Form8.ADODataSet2.Next;

end;

Form9.ComboBox1.ItemIndex:=0;

Form8.ADODataSet2.Active:=False;

Form8.ADODataSet2.CommandText:=' SELECT naimenovanie_podrazdeleniya FROM Podrazdeleniya ORDER BY naimenovanie_podrazdeleniya ';

Form8.ADODataSet2.Active:=True;

Form8.ADODataSet2.First;

for i:=1 to Form8.ADODataSet2.RecordCount do

begin

Form9.ComboBox2.Items.Add(Form8.ADODataSet2.FieldByName('naimenovanie_podrazdeleniya').AsString);

Form8.ADODataSet2.Next;

end;

Form9.ComboBox2.ItemIndex:=0;

Form9.Show;

end;

procedure TForm8.Button1Click(Sender: TObject);

begin

Form8.Close;

end;

procedure TForm8.N2Click(Sender: TObject);

var

n: string;

begin

SQL('SELECT MAX(kod_kategorii_dolzhnosti) FROm Kategoriya_dolzhnostei');

n:=DBGrid1.Fields[0].AsString;

ADOQuery1.Active:=false;

ADOQuery1.SQL.Clear;

try

ADOQuery1.SQL.Add('exec procKategoriya_dolzhnostei '+n);

ADOQuery1.ExecSQL;

except

ADOQuery1.Active := False;

end;

SQL('SELECT kod_kategorii_dolzhnosti ''Код категории должности'', naimenovanie_kategorii_dolzhnosti ''Наименование'' FROm Kategoriya_dolzhnostei');

end;

procedure TForm8.I1Click(Sender: TObject);

var

i : integer;

begin

Form8.ADODataSet3.Active:=False;

Form8.ADODataSet3.CommandText:=' SELECT naimenovanie_podrazdeleniya FROM Podrazdeleniya ORDER BY naimenovanie_podrazdeleniya ';

Form8.ADODataSet3.Active:=True;

Form8.ADODataSet3.First;

for i:=1 to Form8.ADODataSet3.RecordCount do

begin

Form10.ComboBox1.Items.Add(Form8.ADODataSet3.FieldByName('naimenovanie_podrazdeleniya').AsString);

Form8.ADODataSet3.Next;

end;

Form10.ComboBox1.ItemIndex:=0;

Form8.ADODataSet3.Active:=False;

Form8.ADODataSet3.CommandText:=' SELECT naimenovanie_dolzhnosti FROM Dolzhnosti ORDER BY naimenovanie_dolzhnosti ';

Form8.ADODataSet3.Active:=True;

Form8.ADODataSet3.First;

for i:=1 to Form8.ADODataSet3.RecordCount do

begin

Form10.ComboBox2.Items.Add(Form8.ADODataSet3.FieldByName('naimenovanie_dolzhnosti').AsString);

Form8.ADODataSet3.Next;

end;

Form10.ComboBox2.ItemIndex:=0;

Form10.Show;

end;

end.

unit procedure2;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls;

type

TForm9 = class(TForm)

ComboBox1: TComboBox;

ComboBox2: TComboBox;

Label1: TLabel;

Label2: TLabel;

Button1: TButton;

procedure Button1Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form9: TForm9;

implementation

uses procedures;

{$R *.dfm}

procedure TForm9.Button1Click(Sender: TObject);

var

s,t:string ;

begin

s:=(ComboBox1.Text);

t:=(ComboBox2.Text);

Form8.SQL( ' select Rabotnik.FIO_rabotnika,Obrazovanie.naimenovanie,Podrazdeleniya.naimenovanie_podrazdeleniya '

+ ' from Rabotnik, Obrazovanie,Podrazdeleniya where Podrazdeleniya.naimenovanie_podrazdeleniya='''+t+''' '

+ ' and Obrazovanie.naimenovanie='''+s+''' and Obrazovanie.kod_vida_obrazovaniya=Rabotnik.kod_vida_obrazovaniya '

+ ' and Podrazdeleniya.kod_podrazdeleniya=Rabotnik.kod_podrazdeleniya ' );

Form9.Hide;

end;

end.

unit procedure4;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls;

type

TForm10 = class(TForm)

ComboBox1: TComboBox;

Label1: TLabel;

Label2: TLabel;

ComboBox2: TComboBox;

Button1: TButton;

procedure Button1Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form10: TForm10;

implementation

uses procedures;

{$R *.dfm}

procedure TForm10.Button1Click(Sender: TObject);

var

s,t:string ;

begin

s:=(ComboBox1.Text);

t:=(ComboBox2.Text);

Form8.SQL( ' select obwee_kol_edenic, kol_vakansii, obwee_kol_edenic-kol_vakansii[kolichestvo] '

+ ' from Shtatnoe_raspisanie, Podrazdeleniya, Dolzhnosti where Podrazdeleniya.naimenovanie_podrazdeleniya='''+s+''' '

+ ' and Dolzhnosti.naimenovanie_dolzhnosti='''+t+''' and Podrazdeleniya.kod_podrazdeleniya=Shtatnoe_raspisanie.kod_podrazdeleniya' );

Form10.Hide;

end;

end.

Сұраныстармен жұмыс жасау:

unit Zapros;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls, Menus, Grids, DBGrids, DB, ADODB;

type

TForm3 = class(TForm)

MainMenu1: TMainMenu;

N1: TMenuItem;

i1: TMenuItem;

N218601: TMenuItem;

N2: TMenuItem;

N35000550001: TMenuItem;

i2: TMenuItem;

ADODataSet1: TADODataSet;

DBGrid1: TDBGrid;

DataSource1: TDataSource;

procedure SQL(query:string);

procedure FormClose(Sender: TObject; var Action: TCloseAction);

procedure i1Click(Sender: TObject);

Информация о работе Базы данных