Автор работы: Пользователь скрыл имя, 14 Сентября 2014 в 19:03, курсовая работа
Целью данной курсовой работы является:
Углубление теоретических и практических знаний в области баз данных и систем управления базами данных;
К задачам курсовой работы можно отнести следующее:
Разработка базы данных в РСУБД Firebird;
Создание приложения, основанного на клиент-серверной технологии;
Создание веб-приложения.
Введение
Разработка б.д. для ИС «Библиотека»
Разработка клиентского приложения
3.Создание Web приложения. Web-доступ.
Заключение
Список использованных источников
/*****************************
/***
/*****************************
CREATE INDEX FAMILIYA_CHITATELYA ON CHITATEL (FAMILIYA_CHITATELYA);
CREATE INDEX ULICA ON CHITATEL (ULICA);
/*****************************
/***
Triggers for tables
/*****************************
/* Trigger: CHITATEL_BI0 */
CREATE OR ALTER TRIGGER CHITATEL_BI0 FOR CHITATEL
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
IF(NEW.nomer_chitatelya is null ) then
NEW.nomer_chitatelya=gen_id(
end
^
SET TERM ; ^
/* Fields descriptions */
COMMENT ON COLUMN CHITATEL.NOMER_DOMA IS
'VALUE>0';
/*****************************
/***
/*****************************
Библиотекарь
CREATE TABLE BIBLIOTEKOR (
NOMER_BIBLIOTEKORYA D_INDEXTYPE NOT NULL /* D_INDEXTYPE = INTEGER NOT NULL */,
NOMER_OTDELA D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */,
FAMILIYA_BIBLIOTEKORYA D_NAME /* D_NAME = VARCHAR(45) NOT NULL */,
NAME_BIBLIOTEKORYA D_NAME /* D_NAME = VARCHAR(45) NOT NULL */
);
/*****************************
/***
/*****************************
ALTER TABLE BIBLIOTEKOR ADD CONSTRAINT PK_BIBLIOTEKOR PRIMARY KEY (NOMER_BIBLIOTEKORYA);
/*****************************
/***
/*****************************
ALTER TABLE BIBLIOTEKOR ADD CONSTRAINT FK_BIBLIOTEKOR_1 FOREIGN KEY (NOMER_OTDELA) REFERENCES OTDEL_KNIG (NOMER_OTDELA);
/*****************************
/***
/*****************************
CREATE INDEX FAM ON BIBLIOTEKOR (FAMILIYA_BIBLIOTEKORYA);
/*****************************
/***
/*****************************
SET TERM ^ ;
/*****************************
/***
Triggers for tables
/*****************************
/* Trigger: BIBLIOTEKOR_BI0 */
CREATE OR ALTER TRIGGER BIBLIOTEKOR_BI0 FOR BIBLIOTEKOR
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
IF(NEW.nomer_bibliotekorya is null) then
NEW.nomer_bibliotekorya=gen_
end
^
SET TERM ; ^
/* Fields descriptions */
COMMENT ON COLUMN BIBLIOTEKOR.NOMER_
'VALUE>0';
COMMENT ON COLUMN BIBLIOTEKOR.NOMER_OTDELA IS
'VALUE>0';
Книги
CREATE TABLE KNIGI (
NOMER_KNIGI D_INDEXTYPE NOT NULL /* D_INDEXTYPE = INTEGER NOT NULL */,
NOMER_OTDELA D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */,
NAZVANIE_KNIGI D_NAME /* D_NAME = VARCHAR(45) NOT NULL */,
KOL_VO_STRANIC D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */,
AVTOR D_NAME /* D_NAME = VARCHAR(45) NOT NULL */
);
/*****************************
/***
/*****************************
ALTER TABLE KNIGI ADD CONSTRAINT PK_KNIGI PRIMARY KEY (NOMER_KNIGI);
/*****************************
/***
/*****************************
ALTER TABLE KNIGI ADD CONSTRAINT FK_KNIGI_1 FOREIGN KEY (NOMER_OTDELA) REFERENCES OTDEL_KNIG (NOMER_OTDELA);
/*****************************
/***
/*****************************
CREATE INDEX "_KNIGA" ON KNIGI (NAZVANIE_KNIGI);
/*****************************
/***
/*****************************
SET TERM ^ ;
/*****************************
/***
Triggers for tables
/*****************************
/* Trigger: KNIGI_BI0 */
CREATE OR ALTER TRIGGER KNIGI_BI0 FOR KNIGI
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
IF(NEW.nomer_knigi is null) then
NEW.nomer_knigi=gen_id(gen_
end
^
/* Trigger: KNIGI_HIST_BI0 */
CREATE OR ALTER TRIGGER KNIGI_HIST_BI0 FOR KNIGI
ACTIVE BEFORE INSERT POSITION 0
AS
declare variable nomer_knigi_tr smallint;
declare variable nomer_otdela_tr smallint;
declare variable nazvanie_knigi_tr varchar(45);
declare variable kol_vo_stranic_tr smallint;
declare variable avtor_tr varchar(45);
declare variable IZMEN_TR varchar(45);
declare variable DATA_TR date;
begin
DATA_TR=current_date;
if (inserting) then
begin
DATA_TR=current_date;
nomer_knigi_tr=new.nomer_
IZMEN_TR='добавление';
nomer_otdela_tr=new.nomer_
nazvanie_knigi_tr=new.
kol_vo_stranic_tr=new.kol_vo_
avtor_tr=new.avtor;
end
end
^
SET TERM ; ^
/* Fields descriptions */
COMMENT ON COLUMN KNIGI.NOMER_KNIGI IS
'VALUE>0';
COMMENT ON COLUMN KNIGI.NOMER_OTDELA IS
'VALUE>0';
COMMENT ON COLUMN KNIGI.KOL_VO_STRANIC IS
'VALUE>0';
Исторя
CREATE TABLE NEW_TABLE (
ID SMALLINT NOT NULL,
NOMER_KNIGI SMALLINT,
NOMER_OTDELA SMALLINT,
NAZVANIE_KNIGI VARCHAR(45),
KOL_VO_STRANIC SMALLINT,
AVTOR VARCHAR(45),
IZMEN VARCHAR(45),
DATA_TR DATE
);
/*****************************
/***
Primary Keys
/*****************************
ALTER TABLE NEW_TABLE ADD CONSTRAINT PK_NEW_TABLE_1 PRIMARY KEY (ID);
Отдел книг
CREATE TABLE OTDEL_KNIG (
NAZVANIE_OTDELA D_NAME /* D_NAME = VARCHAR(45) NOT NULL */,
NOMER_OTDELA D_INDEXTYPE NOT NULL /* D_INDEXTYPE = INTEGER NOT NULL */,
KOL_VO_KNIG D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */,
TELEFON D_TEL /* D_TEL = VARCHAR(15) */,
NOMER_BIBLIOTEKORYA D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */
);
/*****************************
/*** Primary Keys
/*****************************
ALTER TABLE OTDEL_KNIG ADD CONSTRAINT PK_OTDEL_KNIG PRIMARY KEY (NOMER_OTDELA);
/*****************************
/***
/*****************************
CREATE INDEX NAZV ON OTDEL_KNIG (NAZVANIE_OTDELA);
/*****************************
/***
/*****************************
SET TERM ^ ;
/*****************************
/***
Triggers for tables
/*****************************
/* Trigger: OTDEL_BI0 */
CREATE OR ALTER TRIGGER OTDEL_BI0 FOR OTDEL_KNIG
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
IF(NEW.nomer_otdela is null) then
NEW.nomer_otdela=gen_id(gen_
end
^
SET TERM ; ^
/* Fields descriptions */
COMMENT ON COLUMN OTDEL_KNIG.NOMER_OTDELA IS
'VALUE>0';
COMMENT ON COLUMN OTDEL_KNIG.KOL_VO_KNIG IS
'VALUE>0';
COMMENT ON COLUMN OTDEL_KNIG.NOMER_BIBLIOTEKORYA IS
'VALUE>0';
Выдача книг
CREATE TABLE VIDA4A_KNIG (
NOMER_ZAPISI D_INDEXTYPE NOT NULL /* D_INDEXTYPE = INTEGER NOT NULL */,
NOMER_KNIGI D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */,
NOMER_BIBLIOTEKORYA D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */,
NOMER_CHITATELYA D_INDEXTYPE /* D_INDEXTYPE = INTEGER NOT NULL */,
DATA_VYDA4I_KNIGI D_DATETYPE /* D_DATETYPE = DATE NOT NULL */,
DATA_VOZVRATA_KNIGI D_DATETYPE /* D_DATETYPE = DATE NOT NULL */
);
/*****************************
/***
/*****************************
ALTER TABLE VIDA4A_KNIG ADD CONSTRAINT PK_VIDA4A_KNIG PRIMARY KEY (NOMER_ZAPISI);
/*****************************
/***
/*****************************
ALTER TABLE VIDA4A_KNIG ADD CONSTRAINT FK_VIDA4A_KNIG_1 FOREIGN KEY (NOMER_KNIGI) REFERENCES KNIGI (NOMER_KNIGI);
ALTER TABLE VIDA4A_KNIG ADD CONSTRAINT FK_VIDA4A_KNIG_2 FOREIGN KEY (NOMER_BIBLIOTEKORYA) REFERENCES BIBLIOTEKOR (NOMER_BIBLIOTEKORYA);
/*****************************
/***
/*****************************
SET TERM ^ ;
/*****************************
/***
Triggers for tables
/*****************************
/* Trigger: VYDACHA_KNIG_BI0 */
CREATE OR ALTER TRIGGER VYDACHA_KNIG_BI0 FOR VIDA4A_KNIG
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
IF(NEW.nomer_zapisi is null) then
NEW.nomer_zapisi=gen_id(gen_
end^
SET TERM ; ^
/* Fields descriptions */
COMMENT ON COLUMN VIDA4A_KNIG.NOMER_ZAPISI IS
'VALUE>0';
COMMENT ON COLUMN VIDA4A_KNIG.NOMER_KNIGI IS
'VALUE>0';
COMMENT ON COLUMN VIDA4A_KNIG.NOMER_
'VALUE>0';
COMMENT ON COLUMN VIDA4A_KNIG.NOMER_CHITATELYA IS
'VALUE>0';
Представления
/* View: KNIGI_VIEW */
CREATE VIEW KNIGI_VIEW(
NOMER_KNIGI,
NOMER_OTDELA,
NAZVANIE_KNIGI,
KOL_VO_STRANIC,
AVTOR)
AS
select knigi.nomer_knigi,otdel_knig.
from knigi,otdel_knig
where knigi.nomer_otdela=otdel_knig.
;
/* View: VOZVRAT_VIEW */
CREATE VIEW VOZVRAT_VIEW(
FAMILIYA_CHITATELYA,
IMYA_CHITATELYA,
OTCHESTVO,
NOMER_CHITATELYA,
NOMER_KNIGI,
NAZVANIE_KNIGI,
DATA_VOZVRATA_KNIGI)
AS
select chitatel.familiya_chitatelya,
vida4a_knig.nomer_chitatelya,
vida4a_knig.data_vozvrata_
from chitatel,vida4a_knig,knigi
where chitatel.nomer_chitatelya=
;
Хранимые процедуры Редактирование и удаление данных
begin
INSERT INTO chitatel (chitatel.familiya_chitatelya,
chitatel.ulica,chitatel.nomer_
VALUES (:familiya,:name,:otchestvo,:
suspend;
end
begin
INSERT INTO bibliotekor (bibliotekor.nomer_otdela,
bibliotekor.familiya_
bibliotekor.name_
VALUES (:n_otdel,:familiya,:name);
suspend;
end
begin
insert into vida4a_knig(nomer_knigi,nomer_
nomer_bibliotekorya,data_
values(:n_knigi,:n_biblio,:n_
suspend;
end
begin
insert into otdel_knig(otdel_knig.
otdel_knig.TELEFON,otdel_knig.
values(:nazvanie,:kol_knig,:
suspend;
end
BEGIN
INSERT INTO knigi (knigi.nomer_otdela,knigi.
VALUES (:n_otdela,:new_nazvanie,:kol_
suspend;
end
BEGIN
DELETE FROM vida4a_knig
WHERE (nomer_zapisi = :n_zapisi);
END
BEGIN
DELETE FROM otdel_knig
WHERE (nomer_otdela = :n_otdela);
END
BEGIN
DELETE FROM knigi
WHERE (nomer_knigi = :n_knigi);
END
BEGIN
DELETE FROM bibliotekor
WHERE (nomer_bibliotekorya = :n_biblio);
END
BEGIN
DELETE FROM chitatel
WHERE (nomer_CHITATELYA = :n_CHITAT);
END
BEGIN
UPDATE chitatel
SET nomer_chitatelya=:n_chit,
nomer_doma =:n_doma,
familiya_chitatelya =:familiya,
imya_chitatelya =:name,
otchestvo=:otchestvo,
ULICA=:ulica,
telefon=:tel
WHERE(nomer_chitatelya =:n_chit);
when sqlcode -530 do
exception key_ne_ice;
end
BEGIN
UPDATE bibliotekor
SET nomer_bibliotekorya =:n_biblio,
nomer_otdela =:n_otdela, familiya_bibliotekorya =:familiya,
name_bibliotekorya=:name
WHERE(nomer_bibliotekorya =:n_biblio);
End
BEGIN
UPDATE vida4a_knig
SET nomer_zapisi=:n_zapisi,
nomer_knigi =:n_knigi,
nomer_chitatelya =:n_chit,
nomer_bibliotekorya =:n_biblio,
data_vyda4i_knigi=:data_vyd,
data_vozvrata_knigi=:data_
WHERE(nomer_zapisi =:n_zapisi);
when sqlcode -530 do
exception key_ne_ice;
end
BEGIN
UPDATE otdel_knig
SET nazvanie_otdela=:name_ot,
nomer_otdela =:n_otel,
kol_vo_knig =:kol_knig,
nomer_bibliotekorya =:n_biblio,
telefon=:tel
WHERE(nomer_otdela =:n_otel);
when sqlcode -530 do
exception key_ne_ice;
end
BEGIN
if (:KOL_VO_STRANIC<=0) then exception stranicy;
else
BEGIN
UPDATE knigi
SET nomer_knigi =:n_knigi,
nomer_otdela =:n_otdela, nazvanie_knigi =:new_nazvanie,
kol_vo_stranic =:kol_vo_stranic,
avtor = :avtor
WHERE(nomer_knigi =:N_KNIGI);
when sqlcode -530 do
exception key_ne_ice;
end
END
Домены
CREATE DOMAIN D_ADRES AS
VARCHAR(40) CHARACTER SET WIN1251
NOT NULL
COLLATE WIN1251;
CREATE DOMAIN D_DATETYPE AS
DATE
NOT NULL;
CREATE DOMAIN D_INDEXTYPE AS
INTEGER
NOT NULL;
COMMENT ON DOMAIN D_INDEXTYPE IS
'VALUE>0';
CREATE DOMAIN D_NAME AS
VARCHAR(45) CHARACTER SET WIN1251
NOT NULL
COLLATE WIN1251;
CREATE DOMAIN D_TEL AS
VARCHAR(15) CHARACTER SET WIN1251
COLLATE WIN1251;
//----------------------------
#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
#include "Unit2.h"
#include "Unit3.h"
//----------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//----------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//----------------------------
void __fastcall TForm1::Edit1Change(TObject *Sender)
{
if (RadioButton1->Checked==true)
{Set<TLocateOption,0,1> flags;
flags << loPartialKey;
if (!DataModule2->IBTable2->
DataModule2->IBTable2->Locate(
if (RadioButton2->Checked==true)
{Set<TLocateOption,0,1> flags;
flags << loPartialKey;
if (!DataModule2->IBTable2->
DataModule2->IBTable2->Locate(
if (RadioButton3->Checked==true)
{Set<TLocateOption,0,1> flags;
flags << loPartialKey;