4. Implementácia databázy
4.1 Tvorba databázy
Na realizáciu databázy som si vybrala databázový systém ORACLE.
4.1.1 Tvorba entít
1. entita: KLIENTI
CREATE table "KLIENTI" (
"ID_KLIENT" NUMBER(10,0) NOT NULL,
"MENO" VARCHAR2(50) NOT NULL,
"RODNE_CISLO" NUMBER(10,0) NOT NULL,
"ULICA" VARCHAR2(50) NOT NULL,
"CISLO_DOMU" NUMBER(10,0) NOT NULL,
"PSC" VARCHAR2(5) NOT NULL,
"MESTO" VARCHAR2(50) NOT NULL,
constraint "KLIENTI_PK" primary key ("ID_KLIENT")
)
/
CREATE sequence "KLIENTI_SEQ"
/
CREATE trigger "BI_KLIENTI"
before insert on "KLIENTI"
for each row
begin
select "KLIENTI_SEQ".nextval into :NEW.ID_KLIENT from dual;
end;
/
2. entita: DRUH_UVERU
CREATE table "DRUH_UVERU" (
"ID_DRUH_UV" NUMBER(10,0) NOT NULL,
"NAZOV" VARCHAR2(50) NOT NULL,
"ZABEZPECENIE" VARCHAR2(50) NOT NULL,
"ODMENA" NUMBER(10,0) NOT NULL,
constraint "DRUH_UVERU_PK" primary key ("ID_DRUH_UV")
)
/
CREATE sequence "DRUH_UVERU_SEQ"
/
CREATE trigger "BI_DRUH_UVERU"
before insert on "DRUH_UVERU"
for each row
begin
select "DRUH_UVERU_SEQ".nextval into :NEW.ID_DRUH_UV from dual;
end;
/
3. entita: POBOCKA
CREATE table "POBOCKA" (
"ID_POBOCKA" NUMBER(10,0) NOT NULL,
"MESTO" VARCHAR2(50) NOT NULL,
constraint "POBOCKA_PK" primary key ("ID_POBOCKA")
)
/
CREATE sequence "POBOCKA_SEQ"
/
CREATE trigger "BI_POBOCKA"
before insert on "POBOCKA"
for each row
begin
select "POBOCKA_SEQ".nextval into :NEW.ID_POBOCKA from dual;
end;
/
4.entita : PRACOVNICI
CREATE table "PRACOVNICI" (
"ID_PRACOVNIK" NUMBER(10,0) NOT NULL,
"MENO" VARCHAR2(50) NOT NULL,
"RODNE_CISLO" NUMBER(10,0) NOT NULL,
"ID_POBOCKA" NUMBER(10,0) NOT NULL,
"ID_DRUH_UV" NUMBER(10,0) NOT NULL,
constraint "PRACOVNICI_PK" primary key ("ID_PRACOVNIK")
)
/
CREATE sequence "PRACOVNICI_SEQ"
/
CREATE trigger "BI_PRACOVNICI"
before insert on "PRACOVNICI"
for each row
begin
select "PRACOVNICI_SEQ".nextval into :NEW.ID_PRACOVNIK from dual;
end;
/
ALTER TABLE "PRACOVNICI" ADD CONSTRAINT "PRACOVNICI_FK"
FOREIGN KEY ("ID_POBOCKA")
REFERENCES "POBOCKA" ("ID_POBOCKA")
/
ALTER TABLE "PRACOVNICI" ADD CONSTRAINT "PRACOVNICI_FK2"
FOREIGN KEY ("ID_DRUH_UV")
REFERENCES "DRUH_UVERU" ("ID_DRUH_UV")
/
5. entita: UVERY
CREATE table "UVERY" (
"ID_CISLO_UVERU" NUMBER(10,0) NOT NULL,
"ID_KLIENT" NUMBER(10,0) NOT NULL,
"VYSKA_UVERU" NUMBER(10,0) NOT NULL,
"DOCERPANIE_UV" VARCHAR2(50) NOT NULL,
"UROKOVA_SADZBA" VARCHAR2(50) NOT NULL,
"ZIADOSTI" VARCHAR2(50) NOT NULL,
"ZAVAZKY" VARCHAR2(50) NOT NULL,
"ID_PRACOVNIK" NUMBER(10,0) NOT NULL,
constraint "UVERY_PK" primary key ("ID_CISLO_UVERU")
)
/
CREATE sequence "UVERY_SEQ"
/
CREATE trigger "BI_UVERY"
before insert on "UVERY"
for each row
begin
select "UVERY_SEQ".nextval into :NEW.ID_CISLO_UVERU from dual;
end;
/
ALTER TABLE "UVERY" ADD CONSTRAINT "UVERY_FK"
FOREIGN KEY ("ID_KLIENT")
REFERENCES "KLIENTI" ("ID_KLIENT")
/
ALTER TABLE "UVERY" ADD CONSTRAINT "UVERY_FK2"
FOREIGN KEY ("ID_PRACOVNIK")
REFERENCES "PRACOVNICI" ("ID_PRACOVNIK")
/
6. entita: ZIADOSTI
CREATE table "ZIADOSTI" (
"ID_ZIADOSTI" NUMBER(10,0) NOT NULL,
"DRUH_Z" VARCHAR2(80) NOT NULL,
"DATUM_Z" DATE NOT NULL,
"POPIS" VARCHAR2(150),
constraint "ZIADOSTI_PK" primary key ("ID_ZIADOSTI")
)
/
CREATE sequence "ZIADOSTI_SEQ"
/
CREATE trigger "BI_ZIADOSTI"
before insert on "ZIADOSTI"
for each row
begin
select "ZIADOSTI_SEQ".nextval into :NEW.ID_ZIADOSTI from dual;
end;
/
7. entita: UROKOVE SADZBY
CREATE table "UROKOVE_SADZBY" (
"ID_US" NUMBER(10,0) NOT NULL,
"ID_CISLO_UVERU" NUMBER(10,0) NOT NULL,
"ZAKLADNA_US" NUMBER(10,0) NOT NULL,
"RIZIKOVA_MARZA" NUMBER(10,0) NOT NULL,
"SPRAC_MARZA" NUMBER(10,0) NOT NULL,
"OBCHODNA_MARZA" NUMBER(10,0) NOT NULL,
"DATUM_OBNOVY_US" DATE NOT NULL,
constraint "UROKOVE_SADZBY_PK" primary key ("ID_US")
)
/
CREATE sequence "UROKOVE_SADZBY_SEQ"
/
CREATE trigger "BI_UROKOVE_SADZBY"
before insert on "UROKOVE_SADZBY"
for each row
begin
select "UROKOVE_SADZBY_SEQ".nextval into :NEW.ID_US from dual;
end;
/
ALTER TABLE "UROKOVE_SADZBY" ADD CONSTRAINT "UROKOVE_SADZBY_FK"
FOREIGN KEY ("ID_CISLO_UVERU")
REFERENCES "UVERY" ("ID_CISLO_UVERU")
/
V priebehu vytvárania databázy došlo k určitým zmenám, ktoré sme museli poopravovať. Zmeniť pôvodnú entitu UVERY lebo potrebujeme vymazať atribúty úroková sadzba a ziadosti a nahradiť ich inými. Najprv sme vymazali obsah entity UVERY príkazom DELETE.
Potom sme vymazali potrebné atribúty príkazom DROM COLUMM . Potom sme vytvorili nové atribúty s názvom úrokové sadzby a žiadosti a zadali sme aj dodatočne cudzie kľúče z nových entít. Tie sme si najprv založili.
Vymazanie obsahu entity UVERY, kvôli zmene atribútov
DELETE FROM UVERY
Vymazanie atribútov ziadosti a urokova sadzba
alter table "UVERY" drop column
"ZIADOSTI"
/
alter table "UVERY" drop column
"UROKOVA_SADZBA"
/
Vytvorenie nových atribútov a doplnenie cudzích kľúčov
ALTER TABLE UVERY ADD ID_ZIADOSTI NUMBER (10,0)
ALTER TABLE UVERY ADD FOREIGN KEY(ID_ZIADOSTI)REFERENCES ZIADOSTI(ID_ZIADOSTI)
Ešte sme si zmenili typ v atribúte docerpanie_uv z varchar2 na date
alter table "UVERY" modify
("DOCERPANIE_UV" DATE )
/
4.1.2 Zadávanie údajov
Naplnenie entity POBOCKA
INSERT INTO POBOCKA (ID_POBOCKA,MESTO) VALUES(1,'BRATISLAVA')
INSERT INTO POBOCKA (ID_POBOCKA,MESTO) VALUES(2,'TRNAVA')
INSERT INTO POBOCKA (MESTO) VALUES('TRENCIN')
___________________________________________________________________________
Naplnenie entity DRUH_UVERU
INSERT INTO DRUH_UVERU (NAZOV,ZABEZPECENIE,ODMENA) VALUES('HU','70',150)
INSERT INTO DRUH_UVERU (NAZOV,ZABEZPECENIE,ODMENA) VALUES('USU70','70',120)
___________________________________________________________________________
Naplnenie entity KLIENTI
INSERT INTO KLIENTI (MENO,RODNE_CISLO,ULICA,CISLO_DOMU,PSC,MESTO) VALUES('KATARINA VALOVA',7962116151,'LACHOVA',1,'85103','BRATISLAVA')
INSERT INTO KLIENTI (MENO,RODNE_CISLO,ULICA,CISLO_DOMU,PSC,MESTO) VALUES('ZUZANA HUCOVA',8962136656,'BAGAROVA',13,'01303','ZILINA')
___________________________________________________________________________
Naplnenie entity PRACOVNICI
INSERT INTO PRACOVNICI (MENO,RODNE_CISLO,ID_POBOCKA,ID_DRUH_UV) VALUES('MARTIN DRAHOS',7408120443,1,1)
INSERT INTO PRACOVNICI (MENO,RODNE_CISLO,ID_POBOCKA,ID_DRUH_UV) VALUES('JAN SILO',6711146892,2,6)
___________________________________________________________________________
Naplnenie entity UVERY
INSERT INTO UVERY (ID_KLIENT,VYSKA_UVERU,DOCERPANIE_UV,UROKOVA_SADZBA,ZIADOSTI,ZAVAZKY,ID_PRACOVNIK) VALUES(5,1050000,'13.OKTOBER 2009','4,56','NIE','LIST_VLASTNICTVA',2)
INSERT INTO UVERY (ID_KLIENT,VYSKA_UVERU,DOCERPANIE_UV,UROKOVA_SADZBA,ZIADOSTI,ZAVAZKY,ID_PRACOVNIK) VALUES(2,2350000,'17.NOVEMBER 20089','5,7','ANO','LIST_VLASTNICTVA,KUPNA_ZMLUVA',3)
Znovu naplnenie entity UVERY
INSERT INTO UVERY (ID_KLIENT,VYSKA_UVERU,DOCERPANIE_UV,ZAVAZKY,ID_PRACOVNIK,ID_ZIADOSTI) VALUES(5,35000,'13-10-09','LIST_VLASTNICTVA',2,3)
INSERT INTO UVERY (ID_KLIENT,VYSKA_UVERU,DOCERPANIE_UV,ZAVAZKY,ID_PRACOVNIK) VALUES(2,72300,'17-11-09','LIST_VLASTNICTVA,KUPNA ZMLUVA',3)
___________________________________________________________________________
Naplnenie entity ZIADOSTI
INSERT INTO ZIADOSTI (DRUH_Z, DATUM_Z, POPIS) VALUES('PREDCASNE SPLATENIE','15-10-09','K OBNOVE US')
INSERT INTO ZIADOSTI (DRUH_Z, DATUM_Z, POPIS) VALUES('MIMORIADNA SPLATKA','15-10-09','6600 EUR, PONECHAT VYSKU SPLATKY')
___________________________________________________________________________
Naplnenie entity UROKOVE SADZBY
INSERT INTO UROKOVE_SADZBY (ID_CISLO_UVERU, ZAKLADNA_US, RIZIKOVA_MARZA, SPRAC_MARZA, OBCHODNA_MARZA, DATUM_OBNOVY_US) VALUES (22, 3.1, 0.3, 0.2, 0.6, '15-10-10')
INSERT INTO UROKOVE_SADZBY (ID_CISLO_UVERU, ZAKLADNA_US, RIZIKOVA_MARZA, SPRAC_MARZA, OBCHODNA_MARZA, DATUM_OBNOVY_US) VALUES (23, 3.21, 0.42, 0.2, 0.51, '17-11-10')