Разработка приложения для базы данных «Музыкальная школа»

      end;

   with IBQuery3 do

      Begin

        Sql.Text := 'Select * From Specia';

        open;

      end;

      DBGrid3.DataSource.DataSet.First;

      While not DBGrid3.DataSource.DataSet.Eof do

      begin

 

        ComboBox1.Items.Add(DBGrid3.DataSource.DataSet.Fields[1].Value);

        ComboBox2.Items.Add(DBGrid3.DataSource.DataSet.Fields[1].Value);

        DBGrid3.DataSource.DataSet.Next;

 

      end;

  except

    on E: Exception do

    begin

       Application.MessageBox(PChar(E.Message), 'Ошибка', MB_ICONERROR);

       Halt;

    end;

  end;

  EnabledEdit(false);

  EnabledEditSt(false);

  EnabledEditSp(false);

end;

 

procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);

begin

IBQuery1.Close;

IBQuery2.Close;

IBQuery3.Close;

IBDatabase1.Connected:=False;

end;

 

procedure TForm1.Button1Click(Sender: TObject);

begin

FNew:=True;

  Edit1.Text := '';

  Edit2.Text := '';

  Edit3.Text := '';

  ComboBox1.ItemIndex := -1;

  EnabledEdit(true);

  Edit1.SetFocus;

end;

 

procedure TForm1.Button3Click(Sender: TObject);

var ind:integer;

begin

FNew := False;

with IBQuery1 do

  begin

    Edit1.Text := FieldByName('Nameprepod').AsString;

    Edit2.Text := FieldByName('fnameprepod').AsString;

    Edit3.Text := FieldByName('Otchestvo').AsString;

    Ind :=  comboBox1.Items.IndexOf(FieldByName('specialnost').AsString);

    ComboBox1.ItemIndex:=ind;

  end;

  EnabledEdit(true);

  Edit1.SetFocus;

end;

 

procedure TForm1.Button4Click(Sender: TObject);

begin

Try

  With IBQuery4 do

  Begin

    If FNew Then

   sql.Text := 'execute procedure proc_into_prepod(-1,'''+Edit1.Text+''','''+

                Edit2.Text+''','''+Edit3.Text+''','+ComboBox1.Text+')'

  else

     sql.Text := 'execute procedure proc_into_prepod('+ibquery1.fieldByName('id').AsString+','''+Edit1.Text+''','''+Edit2.Text+

                ''','''+Edit3.Text+''','+ComboBox1.Text+')';

  Transaction.StartTransaction;

  ExecSQL;

  Transaction.Commit;

  Transaction.Active:=False;

  end;

  IBQuery1.Close;

  IBQuery1.open;

Except

on E: Exception do

    begin

       if IBQuery4.Active Then IBQuery4.Transaction.Rollback;

       Application.MessageBox(PChar(E.Message), 'Ошибка', MB_ICONERROR);

    end;

End;

EnabledEdit(false);

end;

 

procedure TForm1.Button2Click(Sender: TObject);

begin

if Application.MessageBox('Продолжить удаление?', 'Удаление',

    MB_ICONQUESTION + MB_YESNO) = IDNO then

    Exit;

Try

  With IBQuery4 do

  Begin

    sql.Text := 'delete from prepod where id ='+ibquery1.fieldByName('id').AsString;

  Transaction.StartTransaction;

  ExecSQL;

  Transaction.Commit;

  Transaction.Active:=False;

  end;

  IBQuery1.Close;

  IBQuery1.open;

Except

on E: Exception do

    begin

       if IBQuery4.Active Then IBQuery4.Transaction.Rollback;

      Application.MessageBox(PChar(E.Message), 'Ошибка', MB_ICONERROR);

    end;

End;

End;

procedure TForm1.Button5Click(Sender: TObject);

begin

  EnabledEdit(false);

end;

 

procedure TForm1.EnabledEdit(const AValue: Boolean);

begin

  Edit1.Enabled := AValue;

  Edit2.Enabled := AValue;

  Edit3.Enabled := AValue;

  ComboBox1.Enabled := AValue;

  Button1.Enabled := not AValue;

  Button2.Enabled := not AValue;

  Button3.Enabled := not AValue;

  Button4.Enabled := AValue;

  Button5.Enabled := AValue;

end;

 

procedure TForm1.EnabledEditSp(const AValue: Boolean);

begin

  SpCode.Enabled := AValue;

  SpNamePrepod.Enabled := AValue;

  SpSpec.Enabled := AValue;

 

  Button11.Enabled := not AValue;

  Button12.Enabled := not AValue;

  Button13.Enabled := not AValue;

  Button14.Enabled := AValue;

  Button15.Enabled := AValue;

end;

 

procedure TForm1.EnabledEditSt(const AValue: Boolean);

begin

  StAdress.Enabled := AValue;

  DateTimePicker1.Enabled := AValue;

  StFname.Enabled := AValue;

  StName.Enabled := AValue;

  StParentName.Enabled := AValue;

  StPhone.Enabled:=AValue;

  StPrepod.Enabled:=AValue;

  ComboBox2.Enabled := AValue;

 

  Button6.Enabled := not AValue;

  Button7.Enabled := not AValue;

  Button8.Enabled := not AValue;

  Button9.Enabled := AValue;

  Button10.Enabled := AValue;

end;

 

procedure TForm1.Button6Click(Sender: TObject);

begin

  FNewSt:=True;

  StAdress.Text := '';

  DateTimePicker1.Date := 30121899;

  StFname.Text := '';

  StName.Text := '';

  StParentName.Text := '';

  StPhone.Text:='';

  StPrepod.Text:='';

  ComboBox2.ItemIndex := -1;

  EnabledEditSt(true);

  StName.SetFocus;

end;

 

procedure TForm1.Button7Click(Sender: TObject);

var ind:integer;

begin

FNewSt := False;

with IBQuery2 do

  begin

     StAdress.Text := FieldByName('HomeAdress').AsString;

     DateTimePicker1.Date := FieldByName('Date1').AsDateTime;

     StFname.Text := FieldByName('FirstName').AsString;;

     StName.Text := FieldByName('Name').AsString;;

     StParentName.Text := FieldByName('ParentName').AsString;;

     StPhone.Text := FieldByName('PhoneNumber').AsString;;

     StPrepod.Text:= FieldByName('Nameprepod').AsString;;

    Ind :=  comboBox2.Items.IndexOf(FieldByName('specialnost').AsString);

    ComboBox2.ItemIndex:=ind;

  end;

  EnabledEditSt(true);

  StName.SetFocus;

end;

 

procedure TForm1.Button8Click(Sender: TObject);

begin

if Application.MessageBox('Продолжить удаление?', 'Удаление',

    MB_ICONQUESTION + MB_YESNO) = IDNO then

    Exit;

Try

  With IBQuery5 do

  Begin

    sql.Text := 'delete from student where id ='+ibquery2.fieldByName('id').AsString;

  Transaction.StartTransaction;

  ExecSQL;

  Transaction.Commit;

  Transaction.Active:=False;

  end;

  IBQuery2.Close;

  IBQuery2.open;

Except

on E: Exception do

    begin

       if IBQuery5.Active Then IBQuery5.Transaction.Rollback;

       Application.MessageBox(PChar(E.Message), 'Ошибка', MB_ICONERROR);

    end;

End;

 

end;

 

procedure TForm1.Button9Click(Sender: TObject);

begin

Try

  With IBQuery5 do

  Begin

    If FNewSt Then begin

   sql.Text := 'execute procedure proc_into_student(-1,'''+StName.Text+''','''+

                StFname.Text+''','''+StParentName.Text+''','+ComboBox2.Text+','''+DateToStr(DateTimePicker1.date)+''','''+StAdress.Text+''','''+StPhone.Text+''','''+ StPrepod.Text+''')';

   ShowMessage(sql.Text);

   end

   else

     sql.Text := 'execute procedure proc_into_student('+ibquery2.fieldByName('id').AsString+','''+StName.Text+''','''+

                StFname.Text+''','''+StParentName.Text+''','+ComboBox2.Text+','''+DateToStr(DateTimePicker1.date)+''','''+StAdress.Text+''','''+StPhone.Text+''','''+

                StPrepod.Text+''')';

     ShowMessage(sql.Text);

  Transaction.StartTransaction;

  ExecSQL;

  Transaction.Commit;

  Transaction.Active:=False;

  end;

  IBQuery2.Close;

  IBQuery2.open;

Except

on E: Exception do

    begin

       if IBQuery5.Active Then IBQuery5.Transaction.Rollback;

       Application.MessageBox(PChar(E.Message), 'Ошибка', MB_ICONERROR);

    end;

End;

EnabledEditSt(false);

 

end;

 

procedure TForm1.Button10Click(Sender: TObject);

begin

EnabledEditSt(false);

end;

 

procedure TForm1.Button11Click(Sender: TObject);

begin

  FNewSp:=True;

  SpCode.Text := '';

  SpSpec.Text := '';

  SpNamePrepod.Text := '';

  EnabledEditSp(true);

  SpCode.SetFocus;

end;

 

procedure TForm1.Button12Click(Sender: TObject);

 

begin

FNewSp := False;

with IBQuery3 do

  begin

     SpCode.Text := FieldByName('CODE').AsString;

     SpSpec.Text := FieldByName('NaimSpec').AsString;;

     SpNamePrepod.Text := FieldByName('NamePrepod').AsString;;

  end;

  EnabledEditSp(true);

  SpCode.SetFocus;

 

end;

 

procedure TForm1.Button13Click(Sender: TObject);

begin

if Application.MessageBox('Продолжить удаление?', 'Удаление',

    MB_ICONQUESTION + MB_YESNO) = IDNO then

    Exit;

Try

  With IBQuery6 do

  Begin

    sql.Text := 'delete from specia where id ='+ibquery3.fieldByName('id').AsString;

  Transaction.StartTransaction;

  ExecSQL;

  Transaction.Commit;

  Transaction.Active:=False;

  end;

  IBQuery3.Close;

  IBQuery3.open;

Except

on E: Exception do

    begin

       if IBQuery6.Active Then IBQuery6.Transaction.Rollback;

       Application.MessageBox(PChar(E.Message), 'Ошибка', MB_ICONERROR);

    end;

End;

end;

 

procedure TForm1.Button14Click(Sender: TObject);

begin

Try

  With IBQuery6 do

  Begin

    If FNewSp Then begin

   sql.Text := 'execute procedure proc_into_specia(-1,'+SpCode.Text+','''+

                SpSpec.Text+''','''+SpNamePrepod.Text+''')';

   ShowMessage(sql.Text);

   end

   else

     sql.Text := 'execute procedure proc_into_specia('+ibquery3.fieldByName('id').AsString+','+SpCode.Text+','''+

                SpSpec.Text+''','''+SpNamePrepod.Text+''')';

     //ShowMessage(sql.Text);

  Transaction.StartTransaction;

  ExecSQL;

  Transaction.Commit;

  Transaction.Active:=False;

  end;

  IBQuery3.Close;

  IBQuery3.open;

Except

on E: Exception do

    begin

       if IBQuery6.Active Then IBQuery6.Transaction.Rollback;

       Application.MessageBox(PChar(E.Message), 'Ошибка', MB_ICONERROR);

    end;

End;

EnabledEditSp(false);

 

end;

 

procedure TForm1.Button15Click(Sender: TObject);

begin

EnabledEditSp(false);

end;

 

procedure TForm1.Button16Click(Sender: TObject);

begin

Close();

end;

 

end.

 

 

 

 

 

 

 

 

 

 

 

 

 

Приложение Б

(обязательное)

 

Рисунок Б.1 – Главное  окно программы.

 

Рисунок Б.2 – Вкладка «Специальности»

 

Рисунок Б.3 – Заполнение полей ввода данных

Рисунок Б.4 – Результат добавления новой записи

Рисунок Б.5 – Владка «Преподаватели»

 

Рисунок Б.6 – редактирование записи таблицы

Рисунок Б.7 – сохранение изменений

 

Рисунок Б.8 – вкладка «Студенты»

 

Рисунок Б.9 – подтверждение удаления записи

 

Рисунок Б.10 – Таблица студенты после удаления записи 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Приложение В

(рекомендуемое)

 

SQL – скрипт

 

/******************************************************************************/

/***          Generated by IBExpert 2011.04.25 08.06.2013 2:01:54           ***/

/******************************************************************************/

 

SET SQL DIALECT 3;

 

SET NAMES WIN1251;

 

CONNECT 'HOME57:C:\BD\MusicSchool.fdb' USER 'SYSDBA' PASSWORD 'masterkey';

 

/******************************************************************************/

/***                                Domains                                 ***/

/******************************************************************************/

 

CREATE DOMAIN VARCHAR2 AS

DATE;

 

 

 

/******************************************************************************/

/***                               Generators                               ***/

/******************************************************************************/

 

CREATE GENERATOR GEN_PREPOD_ID;

SET GENERATOR GEN_PREPOD_ID TO 4;

 

CREATE GENERATOR GEN_SPECIA_ID;

SET GENERATOR GEN_SPECIA_ID TO 5;

 

CREATE GENERATOR GEN_STUDENT_ID;

SET GENERATOR GEN_STUDENT_ID TO 3;

 

SET TERM ^ ;

 

 

 

/******************************************************************************/

/***                           Stored Procedures                            ***/

/******************************************************************************/

 

CREATE PROCEDURE PROC_INTO_PREPOD (

    FIN_ID INTEGER,

    FIN_NAMEPREPOD VARCHAR(20),

    FIN_FNAMEPREPOD VARCHAR(30),

    FIN_OTCESTVO VARCHAR(40),

    FIN_SPEC SMALLINT)

AS

BEGIN

  EXIT;

END^

 

CREATE PROCEDURE PROC_INTO_SPECIA (

    FIN_ID INTEGER NOT NULL,

    FIN_CODE SMALLINT NOT NULL,

    FIN_NAIMSPEC VARCHAR(100),

    FIN_NAMEPREPOD VARCHAR(40))

AS

BEGIN

  EXIT;

END^

 

 

CREATE PROCEDURE PROC_INTO_STUDENT (

    FIN_ID INTEGER,

    FIN_NAME VARCHAR(30),

    FIN_FIRSTNAME VARCHAR(20),

    FIN_PARENTNAME VARCHAR(40),

    FIN_SPEC SMALLINT,

    FIN_DATE1 DATE,

    FIN_HOMEADRESS VARCHAR(60),

    FIN_PHONENUMBER VARCHAR(10),

    FIN_NAMEPREPOD VARCHAR(30))

AS

BEGIN

  EXIT;

END^

 

SET TERM ; ^

 

 

 

/******************************************************************************/

/***                                 Tables                                 ***/

/******************************************************************************/

 

 

 

CREATE TABLE PREPOD (

    ID           INTEGER NOT NULL,

    NAMEPREPOD   CHAR(30) NOT NULL,

    FNAMEPREPOD  CHAR(20),

    OTCHESTVO    CHAR(40),

    SPECIALNOST  SMALLINT NOT NULL

);

 

 

CREATE TABLE SPECIA (

    ID          INTEGER NOT NULL,

    CODE        SMALLINT NOT NULL,

    NAIMSPEC    CHAR(100),

    NAMEPREPOD  CHAR(40)

);

 

 

CREATE TABLE STUDENT (

    ID           INTEGER NOT NULL,

    NAME         CHAR(30),

    FIRSTNAME    CHAR(20),

    PARENTNAME   CHAR(40),

    SPECIALNOST  SMALLINT,

    DATE1        VARCHAR2,

    HOMEADRESS   CHAR(60),

    PHONENUMBER  CHAR(10),

    NAMEPREPOD   CHAR(30)

);

 

INSERT INTO SPECIA (ID, CODE, NAIMSPEC, NAMEPREPOD) VALUES (1, 101, 'nak                                                                                                 ', 'qd                                      ');

INSERT INTO SPECIA (ID, CODE, NAIMSPEC, NAMEPREPOD) VALUES (5, 32, '12                             33                                                                   ', '21                                      ');

 

COMMIT WORK;

 

INSERT INTO PREPOD (ID, NAMEPREPOD, FNAMEPREPOD, OTCHESTVO, SPECIALNOST) VALUES (4, '12312                         ', '12312               ', '123                                     ', 101);

 

COMMIT WORK;

 

INSERT INTO STUDENT (ID, NAME, FIRSTNAME, PARENTNAME, SPECIALNOST, DATE1, HOMEADRESS, PHONENUMBER, NAMEPREPOD) VALUES (3, '121                           ', '121                 ', '12  1                                   ', 101, '2013-05-31', '2               1                                           ', '12        ', '12                            ');

 

COMMIT WORK;

 

/******************************************************************************/

/***                           Unique Constraints                           ***/

/******************************************************************************/

 

ALTER TABLE SPECIA ADD CONSTRAINT UNQ1_SPECIA UNIQUE (CODE);

/******************************************************************************/

/***                              Primary Keys                              ***/

/******************************************************************************/

 

ALTER TABLE PREPOD ADD CONSTRAINT PK_PREPOD_PRIMAR_K PRIMARY KEY (ID);

ALTER TABLE SPECIA ADD CONSTRAINT PK_SPECIA_PRIMAR_K PRIMARY KEY (ID);

ALTER TABLE STUDENT ADD CONSTRAINT PK_STUDEN_PRIMAR_K PRIMARY KEY (ID);

/******************************************************************************/

/***                              Foreign Keys                              ***/

/******************************************************************************/

 

ALTER TABLE PREPOD ADD CONSTRAINT FK_STUDENT_1 FOREIGN KEY (SPECIALNOST) REFERENCES SPECIA (CODE);

ALTER TABLE STUDENT ADD CONSTRAINT FK_STUDENT_2 FOREIGN KEY (SPECIALNOST) REFERENCES SPECIA (CODE);

/******************************************************************************/

/***                                Triggers                                ***/