Курсовая работа: Создание базы данных для организации
Dialogs, DB,
DBCtrls, StdCtrls, IBCustomDataSet, IBQuery, Mask;
type
TForm6 =
class(TForm)
DBLookupComboBox1:
TDBLookupComboBox;
DataSource1:
TDataSource;
Edit1: TEdit;
Button1:
TButton;
Label1:
TLabel;
Label2:
TLabel;
IBQuery1:
TIBQuery;
DataSource2:
TDataSource;
DBEdit1:
TDBEdit;
procedure
FormClose(Sender: TObject; var Action: TCloseAction);
procedure
Button1Click(Sender: TObject);
procedure
FormShow(Sender: TObject);
private
{ Private
declarations }
public
{ Public
declarations }
end;
var
Form6:
TForm6;
s:string;
implementation
uses Unit3;
{$R *.dfm}
procedure
TForm6.FormShow(Sender: TObject);
begin
DataSource1.DataSet.Append;
end;
procedure
TForm6.Button1Click(Sender: TObject);
var s:
string;
begin
Form6.IBQuery1.Active:=false;
s:='select
MONEY from movie where movie.id = '+ Edit1.Text;
Form6.IBQuery1.SQL.text:=s;
Form6.IBQuery1.Active:=true;
Form6.DBEdit1.DataField:='MONEY';
DataSource1.DataSet.FieldByName('DEN').AsString:=Form6.DBEdit1.Text;
DataSource1.DataSet.FieldByName('ID_M').AsString:=Edit1.Text;
DataSource1.DataSet.Post;
DataSource1.DataSet.Append;
end;
procedure
TForm6.FormClose(Sender: TObject; var Action: TCloseAction);
begin
DataSource1.DataSet.Cancel;
end;
end.
Unit7:
interface
uses
Windows,
Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,
StdCtrls;
type
TForm7 =
class(TForm)
ComboBox1:
TComboBox;
Button1:
TButton;
Edit1: TEdit;
ComboBox2:
TComboBox;
procedure
ComboBox2Change(Sender: TObject);
procedure
Button1Click(Sender: TObject);
procedure
ComboBox1Change(Sender: TObject);
private
{ Private
declarations }
public
{ Public
declarations }
end;
var
Form7:
TForm7;
tb,k:string;
implementation
uses Unit2;
{$R *.dfm}
procedure
TForm7.ComboBox1Change(Sender: TObject);
begin
if
Combobox1.Items.Strings[Combobox1.ItemIndex]='Фильмы' then
begin
tb:='MOVIE';
Combobox2.Items.Clear;
Combobox2.Items.Add('ID');
Combobox2.Items.Add('NAME_FILM');
Combobox2.Items.Add('DIRECTOR');
Combobox2.Items.Add('KOL');
Combobox2.Items.Add('MONEY');
Combobox2.Items.Add('GANR');
Combobox2.Items.Add('DESCRIPTION');
end else
begin
tb:='CLIENT';
Combobox2.Items.Clear;
Combobox2.Items.Add('ID_C');
Combobox2.Items.Add('FIO');
Combobox2.Items.Add('PASPORT');
end;
if
Combobox1.Items.Strings[Combobox1.ItemIndex]='Счета' then
begin
tb:='DEAL';
Combobox2.Items.Clear;
Combobox2.Items.Add('ID_D');
Combobox2.Items.Add('ID_M');
Combobox2.Items.Add('CL_ID');
Combobox2.Items.Add('DEN');
Combobox2.Items.Add('D_D');
end;
end;
procedure
TForm7.Button1Click(Sender: TObject);
var
zapros:
string;
begin
if (k =
'ID_C') or (k = 'ID') or (k = 'ID_D') or(k='KOL') or (k='MONEY') or(k='ID_M')
or (k='CL_ID')
then
zapros:='SELECT
* from '+tb+' where '+k+'='+Edit1.Text
else
zapros:='SELECT
* from '+tb+' where '+k+' LIKE '+'''%'+Edit1.Text+'%''';
Form2.IBQuery1.SQL.Text:=zapros;
Form2.DataSource4.DataSet.Active:=true;
Form7.Close;
Form2.PageControl1.ActivePage:=
Form2.PageControl1.Pages[4];
Form2.FocusControl(Form2.PageControl1);
end;
procedure
TForm7.ComboBox2Change(Sender: TObject);
begin
k:=Combobox2.Items.Strings[Combobox2.ItemIndex];
end;
end.
Скрипты:
DOMAINS:
CREATE DOMAIN
D_GANR AS
VARCHAR(10)
CHARACTER SET NONE
NOT NULL
CHECK (VALUE
IN ('comedy', 'action', 'melodramm', 'fantasy', 'horror'))
COLLATE NONE
Tables:
CLIENT:
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE
GENERATOR GEN_CLIENT_ID_C;
CREATE TABLE
CLIENT (
ID_C INTEGER,
FIO VARCHAR(50)
NOT NULL,
PASPORT VARCHAR(50)
);
/******************************************************************************/
/**** Primary
Keys ****/
/******************************************************************************/
ALTER TABLE
CLIENT ADD PRIMARY KEY (ID_C);
/******************************************************************************/
/**** Triggers
****/
/******************************************************************************/
SET TERM ^;
/******************************************************************************/
/**** Triggers
for tables ****/
/******************************************************************************/
/* Trigger:
TRIG_CLIENT */
CREATE
TRIGGER TRIG_CLIENT FOR CLIENT
ACTIVE BEFORE
INSERT POSITION 0
AS BEGIN
IF (NEW.id_c
IS NULL) THEN NEW.id_c = GEN_ID(gen_client_id_c,1);
END
^
DEAL:
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE
GENERATOR GEN_DEAL_ID;
CREATE TABLE
DEAL (
ID_D INTEGER,
ID_M INTEGER
NOT NULL,
CL_ID INTEGER
NOT NULL,
DEN NUMERIC(4,2),
D_D DATE
);
/******************************************************************************/
/**** Primary
Keys ****/
/******************************************************************************/
ALTER TABLE
DEAL ADD PRIMARY KEY (ID_D);
/******************************************************************************/
/**** Foreign
Keys ****/
/******************************************************************************/
ALTER TABLE
DEAL ADD FOREIGN KEY (CL_ID) REFERENCES CLIENT (ID_C) ON UPDATE CASCADE;
ALTER TABLE
DEAL ADD FOREIGN KEY (ID_M) REFERENCES MOVIE (ID) ON DELETE CASCADE ON UPDATE
CASCADE;
/******************************************************************************/
/**** Triggers
for tables ****/
/******************************************************************************/
/* Trigger:
DEC_MONEY */
CREATE
TRIGGER DEC_MONEY FOR DEAL
ACTIVE AFTER
INSERT POSITION 0
AS
declare
variable x integer;
begin
Select
Count(New.cl_id) from DEAL
into:x;
if (:x >
3) then
begin
update Deal
set Deal.den
= (DEAL.den - DEAL.den/100*10)
where
DEAL.id_d = New.id_d;
end
end
/* Trigger:
SUB_MOVIE */
CREATE
TRIGGER SUB_MOVIE FOR DEAL
ACTIVE AFTER
INSERT POSITION 0
AS
begin
update movie
set movie.kol
= movie.kol - 1
where
movie.id = new.id_m;
end
/* Trigger:
TRIG_DEAL_BI */
CREATE
TRIGGER TRIG_DEAL_BI FOR DEAL
ACTIVE BEFORE
INSERT POSITION 0
AS BEGIN
IF (NEW.Id_d
IS NULL) THEN NEW.Id_d = GEN_ID(Gen_DEAL_ID,1);
END
MOVIE:
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE
GENERATOR GEN_DEAL_ID;
CREATE TABLE
DEAL (
ID_D INTEGER,
ID_M INTEGER
NOT NULL,
CL_ID INTEGER
NOT NULL,
DEN NUMERIC(4,2),
D_D DATE
);
/******************************************************************************/
/**** Primary
Keys ****/
/******************************************************************************/
ALTER TABLE
DEAL ADD PRIMARY KEY (ID_D);
/******************************************************************************/
/**** Foreign
Keys ****/
/******************************************************************************/
ALTER TABLE
DEAL ADD FOREIGN KEY (CL_ID) REFERENCES CLIENT (ID_C) ON UPDATE CASCADE;
ALTER TABLE
DEAL ADD FOREIGN KEY (ID_M) REFERENCES MOVIE (ID) ON DELETE CASCADE ON UPDATE
CASCADE;
/******************************************************************************/
/**** Triggers
for tables ****/
/******************************************************************************/
/* Trigger:
DEC_MONEY */
CREATE
TRIGGER DEC_MONEY FOR DEAL
ACTIVE AFTER
INSERT POSITION 0
AS
declare
variable x integer;
begin
Select
Count(cl_id) from DEAL where cl_id = New.cl_id group by cl_id
into:x;
if (:x >
3) then
begin
update Deal
set Deal.den
= (DEAL.den - DEAL.den/100*10)
where
DEAL.id_d = New.id_d;
end
end
/* Trigger:
SUB_MOVIE */
CREATE
TRIGGER SUB_MOVIE FOR DEAL
ACTIVE AFTER
INSERT POSITION 0
AS
begin
update movie
set movie.kol
= movie.kol - 1
where
movie.id = new.id_m;
end
/* Trigger:
TRIG_DEAL_BI */
CREATE
TRIGGER TRIG_DEAL_BI FOR DEAL
ACTIVE BEFORE
INSERT POSITION 0
AS BEGIN
IF (NEW.Id_d
IS NULL) THEN NEW.Id_d = GEN_ID(Gen_DEAL_ID,1);
END
EXCEPTION:
CREATE
EXCEPTION NO_DELETE 'Нельзя удалить фильм если он имеется на складе!';
Страницы: 1, 2, 3, 4, 5, 6, 7, 8 |
