Лабораторна робота №5
Тема: Проектування інформаційної бази даних за допомогою ER-Win.
Мета: Навчитися проектувати інформаційну базу даних за допомогою ER-Win.
Хід роботи:
Висновок. Я навчився проектувати інформаційну базу даних за допомогою ER-Win.
Лабораторна робота №6
Тема: Генерація бази даних для СУБД Inter Base.
Мета: Навчитися здійснювати генерацію бази даних для СУБД Inter Base.
Хід роботи:
Лістинг кода (SQL)
CREATE TABLE Priparati (
Kod_priparaty CHAR(18) NOT NULL, Nazva_priparaty CHAR(18),
Data_vigotovlennya DATE, Termin_pridatnosti DATE
);
CREATE UNIQUE INDEX XPKPriparati ON Priparati ( Kod_priparaty );
ALTER TABLE Priparati ADD PRIMARY KEY (Kod_priparaty);
CREATE TABLE Diagnozi (
Kod_diagnozi CHAR(18) NOT NULL, Kod_priparaty CHAR(18), terminologia CHAR(18),
termin_likyvannya CHAR(18) );
CREATE UNIQUE INDEX XPKDiagnozi ON Diagnozi
( Kod_diagnozi ); ALTER TABLE Diagnozi ADD PRIMARY KEY (Kod_diagnozi);
CREATE TABLE Likari ( kod_likarya CHAR(18) NOT NULL, Kod_priparaty CHAR(18),
PIB CHAR(18), Kod_pasporty CHAR(18), Tel._Nomer CHAR(18), Misto_prozhivannya CHAR(18)
);
CREATE UNIQUE INDEX XPKLikari ON Likari
( kod_likarya );
ALTER TABLE Likari ADD PRIMARY KEY (kod_likarya);
CREATE TABLE Klienti ( Kod_klienta CHAR(18) NOT NULL, Kod_diagnozi CHAR(18),
kod_likarya CHAR(18), PIB CHAR(18), Kod_pasporty CHAR(18), Tel._Nomer CHAR(18),
Misto_prozhivannya CHAR(18) );
CREATE UNIQUE INDEX XPKKlienti ON Klienti
( Kod_klienta );
ALTER TABLE Klienti ADD PRIMARY KEY (Kod_klienta);
CREATE TABLE Kvitancia (
Kod_kvitancii CHAR(18) NOT NULL, Kod_klienta CHAR(18),
Vsogo_do_oplati FLOAT, Data_vidachi DATE,
PIB_Klienta CHAR(18), Diagnoz CHAR(18) );
CREATE UNIQUE INDEX XPKKvitancia ON Kvitancia
( Kod_kvitancii );
ALTER TABLE Kvitancia ADD PRIMARY KEY (Kod_kvitancii);
ALTER TABLE Diagnozi ADD FOREIGN KEY (Kod_priparaty) REFERENCES Priparati;
ALTER TABLE Likari ADD FOREIGN KEY (Kod_priparaty) REFERENCES Priparati;
ALTER TABLE Klienti ADD FOREIGN KEY (Kod_diagnozi) REFERENCES Diagnozi;
ALTER TABLE Klienti ADD FOREIGN KEY (kod_likarya) REFERENCES Likari;
ALTER TABLE Kvitancia ADD FOREIGN KEY (Kod_klienta) REFERENCES Klienti;
CREATE EXCEPTION ERWIN_PARENT_INSERT_RESTRICT "Cannot INSERT Parent table because Child table exists.";
CREATE EXCEPTION ERWIN_PARENT_UPDATE_RESTRICT "Cannot UPDATE Parent table because Child table exists.";
CREATE EXCEPTION ERWIN_PARENT_DELETE_RESTRICT "Cannot DELETE Parent table because Child table exists.";
CREATE EXCEPTION ERWIN_CHILD_INSERT_RESTRICT "Cannot INSERT Child table because Parent table does not exist.";
CREATE EXCEPTION ERWIN_CHILD_UPDATE_RESTRICT "Cannot UPDATE Child table because Parent table does not exist.";
CREATE EXCEPTION ERWIN_CHILD_DELETE_RESTRICT "Cannot DELETE Child table because Parent table does not exist.";
CREATE TRIGGER tD_Priparati FOR Priparati AFTER DELETE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN
update Likari
set
Likari.Kod_priparaty = NULL
where
Likari.Kod_priparaty = OLD.Kod_priparaty;
update Diagnozi
set
Diagnozi.Kod_priparaty = NULL
where Diagnozi.Kod_priparaty = OLD.Kod_priparaty;
END !!
CREATE TRIGGER tU_Priparati FOR Priparati AFTER UPDATE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN IF
(OLD.Kod_priparaty <> NEW.Kod_priparaty) THEN
BEGIN update Likari set
Likari.Kod_priparaty = NULL
where Likari.Kod_priparaty = OLD.Kod_priparaty; END
IF (OLD.Kod_priparaty <> NEW.Kod_priparaty) THEN
BEGIN update Diagnozi set
Diagnozi.Kod_priparaty = NULL
where Diagnozi.Kod_priparaty = OLD.Kod_priparaty;
END END !!
CREATE TRIGGER tD_Diagnozi FOR Diagnozi AFTER DELETE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN
update Klienti
set
Klienti.Kod_diagnozi = NULL
where Klienti.Kod_diagnozi = OLD.Kod_diagnozi; END !!
CREATE TRIGGER tI_Diagnozi FOR Diagnozi AFTER INSERT AS
DECLARE VARIABLE numrows INTEGER;
BEGIN update Diagnozi
set Diagnozi.Kod_priparaty = NULL
where not exists (
select * from Priparati
where NEW.Kod_priparaty = Priparati.Kod_priparaty
) and
Diagnozi.Kod_diagnozi = NEW.Kod_diagnozi;
END !!
CREATE TRIGGER tU_Diagnozi FOR Diagnozi AFTER UPDATE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN
IF (OLD.Kod_diagnozi <> NEW.Kod_diagnozi) THEN
BEGIN update Klienti
set Klienti.Kod_diagnozi = NULL
where Klienti.Kod_diagnozi = OLD.Kod_diagnozi; END update Diagnozi
set Diagnozi.Kod_priparaty = NULL
where
not exists ( select * from Priparati where NEW.Kod_priparaty = Priparati.Kod_priparaty
) and Diagnozi.Kod_diagnozi = NEW.Kod_diagnozi; END !!
CREATE TRIGGER tD_Likari FOR Likari AFTER DELETE AS
DECLARE VARIABLE numrows INTEGER; BEGIN
update Klienti set Klienti.kod_likarya = NULL
where Klienti.kod_likarya = OLD.kod_likarya; END !!
CREATE TRIGGER tI_Likari FOR Likari AFTER INSERT AS
DECLARE VARIABLE numrows INTEGER; BEGIN
update Likari set Likari.Kod_priparaty = NULL
where not exists ( select * from Priparati
where NEW.Kod_priparaty = Priparati.Kod_priparaty
) and Likari.kod_likarya = NEW.kod_likarya; END !!
CREATE TRIGGER tU_Likari FOR Likari AFTER UPDATE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN IF (OLD.kod_likarya <> NEW.kod_likarya) THEN
BEGIN update Klienti set Klienti.kod_likarya = NULL
where Klienti.kod_likarya = OLD.kod_likarya; END
update Likari set Likari.Kod_priparaty = NULL
where
not exists ( select * from Priparati
where NEW.Kod_priparaty = Priparati.Kod_priparaty
) and Likari.kod_likarya = NEW.kod_likarya; END !!
CREATE TRIGGER tD_Klienti FOR Klienti AFTER DELETE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN update Kvitancia
set Kvitancia.Kod_klienta = NULL
where Kvitancia.Kod_klienta = OLD.Kod_klienta; END !!
CREATE TRIGGER tI_Klienti FOR Klienti AFTER INSERT AS
DECLARE VARIABLE numrows INTEGER;
BEGIN update Klienti
set Klienti.Kod_diagnozi = NULL
where not exists (
select * from Diagnozi
where NEW.Kod_diagnozi = Diagnozi.Kod_diagnozi
) and Klienti.Kod_klienta = NEW.Kod_klienta;
update Klienti set
Klienti.kod_likarya = NULL
where not exists ( select * from Likari
where
NEW.kod_likarya = Likari.kod_likarya
) and Klienti.Kod_klienta = NEW.Kod_klienta; END !!
CREATE TRIGGER tU_Klienti FOR Klienti AFTER UPDATE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN IF (OLD.Kod_klienta <> NEW.Kod_klienta) THEN
BEGIN update Kvitancia
set Kvitancia.Kod_klienta = NULL
where Kvitancia.Kod_klienta = OLD.Kod_klienta; END
update Klienti set Klienti.Kod_diagnozi = NULL
where not exists ( select * from Diagnozi
where NEW.Kod_diagnozi = Diagnozi.Kod_diagnozi
) and Klienti.Kod_klienta = NEW.Kod_klienta;
update Klienti
set Klienti.kod_likarya = NULL
where
not exists (
select * from Likari
where NEW.kod_likarya = Likari.kod_likarya
) and Klienti.Kod_klienta = NEW.Kod_klienta;
END !!
CREATE TRIGGER tI_Kvitancia FOR Kvitancia AFTER INSERT AS
DECLARE VARIABLE numrows INTEGER;
BEGIN update Kvitancia
set Kvitancia.Kod_klienta = NULL
where
not exists ( select * from Klienti
where NEW.Kod_klienta = Klienti.Kod_klienta
) and Kvitancia.Kod_kvitancii = NEW.Kod_kvitancii; END !!
CREATE TRIGGER tU_Kvitancia FOR Kvitancia AFTER UPDATE AS
DECLARE VARIABLE numrows INTEGER;
BEGIN update Kvitancia
set Kvitancia.Kod_klienta = NULL
where not exists (
select * from Klienti where NEW.Kod_klienta = Klienti.Kod_klienta
) and Kvitancia.Kod_kvitancii = NEW.Kod_kvitancii;
END !!
Результат роботи:
Висновок: Я навчився здійснювати генерацію бази даних для СУБД Inter Base.