WOJSKOWA AKADEMIA TECHNICZNA

BAZY DANYCH

SPRAWOZDANIE Z ZADANIA LABORATORYJNEGO

Temat : Szpital

Wykonał: Bartłomiej Nowak

Grupa: I2Y3S1

Prowadzący ćwiczenie: . dr inż. Robert Waszkowski

Model konceptualny:

Model fizyczny:

1. Tworzenie bazy:

create table "Lozka" (

NR_LOZKA INTEGER not null,

ID_SALI INTEGER not null,

constraint PK_LOZKA primary key (NR_LOZKA)

);

create index "FK_Sala_Lozko_FK" on "Lozka" (

ID_SALI ASC

);

create table "Lozko_Pacjent" (

NIP INTEGER not null,

NR_LOZKA INTEGER not null,

constraint PK_LOZKO_PACJENT primary key (NIP, NR_LOZKA)

);

create index "Lozko_Pacjent_FK" on "Lozko_Pacjent" (

NIP ASC

);

create index "Lozko_Pacjent2_FK" on "Lozko_Pacjent" (

NR_LOZKA ASC

);

create table "Oddzialy" (

NR_ODDZIALU INTEGER not null,

RODZAJ VARCHAR2(20),

constraint PK_ODDZIALY primary key (NR_ODDZIALU)

);

create table "Pacjenci" (

NIP INTEGER not null,

IMIE_PACJENTA VARCHAR2(20) not null,

NAZWISKO_PACJENTA VARCHAR2(20) not null,

PESEL INTEGER not null,

constraint PK_PACJENCI primary key (NIP)

);

create table "Personel" (

ID_PERSONELU INTEGER not null,

NR_ODDZIALU INTEGER not null,

IMIE VARCHAR2(20),

NAZWISKO VARCHAR2(20),

STANOWISKO VARCHAR2(20),

constraint PK_PERSONEL primary key (ID_PERSONELU)

);

create index "FK_Oddzial_Personel_FK" on "Personel" (

NR_ODDZIALU ASC

);

create table "Personel_Zabieg" (

ID_PERSONELU INTEGER not null,

ID_ZABIEGU INTEGER not null,

constraint PK_PERSONEL_ZABIEG primary key (ID_PERSONELU, ID_ZABIEGU)

);

create index "Personel_Zabieg_FK" on "Personel_Zabieg" (

ID_PERSONELU ASC

);

create index "Personel_Zabieg2_FK" on "Personel_Zabieg" (

ID_ZABIEGU ASC

);

create table "Sale" (

ID_SALI INTEGER not null,

NR_ODDZIALU INTEGER not null,

constraint PK_SALE primary key (ID_SALI)

);

create index "FK_Oddzial_Sala_FK" on "Sale" (

NR_ODDZIALU ASC

);

create table "Zabiegi" (

ID_ZABIEGU INTEGER not null,

NIP INTEGER not null,

NAZWA VARCHAR2(20) not null,

KOSZT INTEGER not null,

constraint PK_ZABIEGI primary key (ID_ZABIEGU)

);

create index "FK_Pacjent_Zabieg_FK" on "Zabiegi" (

NIP ASC

);

alter table "Lozka"

add constraint FK_LOZKA_FK_SALA_L_SALE foreign key (ID_SALI)

references "Sale" (ID_SALI);

alter table "Lozko_Pacjent"

add constraint FK_LOZKO_PA_LOZKO_PAC_PACJENCI foreign key (NIP)

references "Pacjenci" (NIP);

alter table "Lozko_Pacjent"

add constraint FK_LOZKO_PA_LOZKO_PAC_LOZKA foreign key (NR_LOZKA)

references "Lozka" (NR_LOZKA);

alter table "Personel"

add constraint FK_PERSONEL_FK_ODDZIA_ODDZIALY foreign key (NR_ODDZIALU)

references "Oddzialy" (NR_ODDZIALU);

alter table "Personel_Zabieg"

add constraint FK_PERSONEL_PERSONEL__PERSONEL foreign key (ID_PERSONELU)

references "Personel" (ID_PERSONELU);

alter table "Personel_Zabieg"

add constraint FK_PERSONEL_PERSONEL__ZABIEGI foreign key (ID_ZABIEGU)

references "Zabiegi" (ID_ZABIEGU);

alter table "Sale"

add constraint FK_SALE_FK_ODDZIA_ODDZIALY foreign key (NR_ODDZIALU)

references "Oddzialy" (NR_ODDZIALU);

alter table "Zabiegi"

add constraint FK_ZABIEGI_FK_PACJEN_PACJENCI foreign key (NIP)

references "Pacjenci" (NIP);

1. Tabele po wypełnieniu:

Pacjenci:

Lozka: Lozko_Pacjent:

Zabiegi: Personel_Zabiegi:

Personel:

Oddziały: Sale:

1. Widoki:

CREATE materialized VIEW "zabiegi_info"

build immediate

refresh force

WITH rowid

next sysdate+(1/(24*60))

AS (

SELECT P.NIP, P.IMIE_PACJENTA, P.NAZWISKO_PACJENTA, COUNT(Z.ID_ZABIEGU) ILOSC_ZABIEGOW, SUM(Z.KOSZT)

FROM "Zabiegi" Z

JOIN "Pacjenci" P

ON (P.NIP=Z.NIP)

GROUP BY P.NIP, P.IMIE_PACJENTA, P.NAZWISKO_PACJENTA

);

Wynik zapytania : Select * From „zabiegi_info”

create or replace VIEW "personel_info"

AS

SELECT P.ID_PERSONELU, P.IMIE, P.NAZWISKO, COUNT(Z.ID_ZABIEGU) ILOSC_ZABIEGOW

FROM "Personel" P

JOIN "Personel_Zabieg" Z

ON (P.ID_PERSONELU=Z.ID_PERSONELU)

GROUP BY P.ID_PERSONELU, P.IMIE, P.NAZWISKO

Wynik zapytania : Select * From „personel_info”:

create or replace VIEW "sale_info"

AS

SELECT O.RODZAJ ODDZIAL, COUNT(DISTINCT S.ID_SALI) "ILOSC SAL", COUNT(DISTINCT L.NR_LOZKA) "ILOSC LOZEK", COUNT(DISTINCT P.ID_PERSONELU) "ILOSC PERSONELU"

FROM "Oddzialy" O ,"Sale" S, "Lozka" L, "Personel" P

WHERE O.NR_ODDZIALU=S.NR_ODDZIALU

AND S.ID_SALI=L.ID_SALI

AND P.NR_ODDZIALU=O.NR_ODDZIALU

GROUP BY O.RODZAJ

Wynik zapytania : Select * From „sale_info”:

Triggery:

create or replace trigger "LOZKA_T1"

AFTER

insert on "Lozka"

for each row

begin

dbms_output.put_line('Wstawiono kolejne lozko');

end;

Po dodaniu kolejnego łóżka do tabeli wypisuje on komunikat:

create or replace TRIGGER zmiana_lozka

AFTER UPDATE ON "Lozko_Pacjent"

FOR EACH ROW

BEGIN

dbms_output.put_line('Stare lozko: ' || :OLD.NR_LOZKA);

dbms_output.put_line('Nowe lozko: ' || :NEW.NR_LOZKA);

END;

Po zmianie łóżka pacjentowi (z dziesiątego na pierwsze) wypisuje:

1. Funkcje:

Poniższe funkcje znajdują się w pakiecie „PAKIET”:

FUNCTION ILOSC_ZABIEGOW(LICZBA in INT)

RETURN int

IS

cnumber number;

BEGIN

SELECT COUNT(ID_ZABIEGU) INTO cnumber

FROM "Personel_Zabieg"

WHERE ID_PERSONELU=LICZBA;

RETURN (cnumber);

END;

Wynik uruchomienia po zapytaniu: SELECT "PAKIET".ILOSC_ZABIEGOW('10') FROM DUAL

FUNCTION LACZNY_KOSZT(LICZBA in INT)

RETURN int

IS

cnumber number;

BEGIN

SELECT SUM(KOSZT) INTO cnumber

FROM "Zabiegi"

WHERE NIP=LICZBA;

RETURN (cnumber);

END;

Wynik uruchomienia po zapytaniu: SELECT "PAKIET".LACZNY_KOSZT('101') FROM DUAL

1. Procedury:

PROCEDURE ZMIEN_LOZKO(id_lozka in int ,osoba in int)

IS

BEGIN

UPDATE "Lozko_Pacjent" SET NR_LOZKA=id_lozka

where NIP=osoba;

end;

Wyniki uruchomienia dla zapytania: begin

PAKIET.ZMIEN_LOZKO('2','101');

end;