SAB/K2N 2023

Postavka

U sistemu bolničkog lečenja prate se informacije o lekarima koji rade u bolnici i pregledima koji su ti lekari obavili. Svaki pacijent može imati najviše jedan karton u svakoj od bolnica. Svaki karton se povremeno overava kada se upisuje naziv poslodavca preko koga je pacijent osiguran. U slučajevima kada je to potrebno pacijenti mogu biti hospitalizovani, tj. primljeni na bolničko lečenje (datum otpuštanja se popunjava tek nakon što se lečenje završi, a do tada je NULL).
Odgovarajuća šema baze je:

Mesto(IDMes, Naziv)

Bolnica (IDBol, Naziv, Kapacitet, Iskoriscenost, IDMes)

Pacijent(IDPac, Ime, JMBG, GodineStarosti, IDMes)

Lekar(IDLek, Ime, JMBG, Specijalizacija)

Karton(IdKar, IDPac, IDBol)

Pregled(IdPre, Datum, ObjektivniNalaz, DIjagnoza, IDKar, IdLek)

Hospitalizacija(IDHos, DatumPri, DatumOtp, Dijagnoza, KlinickaSlika, IDPac, IDBol)

Overa(IDKar, RedBr, DatumOd, NazivPoslodavca)

Radi(IDLek, IDBol)

Iskoriscenost: N- Niska, S - Srednja, V - Viskoa

Za posmatrani sistem sastaviti SQLServer proceduru AzurirajIskoriscenost(), koja postavlja atribut iskorišćenost u relaciji Bolnica. Iskorišćenost se postavlja na osnovu vrše (maksimalne) vrednosti popunjenosti bolnice u bilo kojem trenutku do sada. Konkretno, ukoliko je u bilo kom trenutku broj hospitalizovanih paicjenata bio preko 75% kapaciteta, ona je visoka. Ukoliko je manje ili jednako od 75%, a više od 25% onda je srednja. U suprotnom, ukoliko je manje ili jednako 25% onda je njena iskorišćenost niska. U dane boravka u bolnici treba uključiti datum prijema, ali ne i datum otpuštanja iz bolnice.

Rešenje

USE [BolnickoLecenje]
GO
/** Object:  StoredProcedure [dbo].[AzurirajIskoriscenost]    Script Date: 4/27/2024 4:32:22 PM **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AzurirajIskoriscenost]
	@IdBol int
AS
BEGIN
	DECLARE @iskoriscenost DECIMAL(10,2)

	DECLARE @tabela TABLE (id int, dolazak datetime, odlazak datetime)
	INSERT INTO @tabela
	SELECT h.IDHos, h.DatumPri, COALESCE(h.DatumOtp, getdate())
	FROM Hospitalizacija h
	WHERE h.IDBol = @IdBol

	DECLARE @maks INT

	DECLARE @rezultatTabela TABLE(id int, broj int) 
	INSERT INTO @rezultatTabela 
	SELECT t1.id, COUNT(*)
	FROM @tabela t1 JOIN @tabela t2 ON (t2.dolazak BETWEEN t1.dolazak AND t1.odlazak)
	GROUP BY t1.id

	SELECT @maks = MAX(broj) FROM @rezultatTabela

	SELECT @iskoriscenost = Kapacitet FROM Bolnica WHERE IDBol = @IdBol

	SET @iskoriscenost = (@maks+0.0)/@iskoriscenost

	print(@maks)
	UPDATE Bolnica
	SET Iskoriscenost = CASE WHEN @iskoriscenost > 0.75 THEN 'V'
							 WHEN @iskoriscenost > 0.25 THEN 'S'
							 ELSE 'N' END
	WHERE IDBol = @IdBol

END

Testiranje

-- Kreiranje baze podataka
CREATE DATABASE BolnickoLecenje;
GO

-- Korišćenje baze podataka BolnickoLecenje
USE BolnickoLecenje;
GO

-- Kreiranje tabele Mesto
CREATE TABLE Mesto (
    IDMes INT PRIMARY KEY,
    Naziv NVARCHAR(50)
);

-- Kreiranje tabele Bolnica
CREATE TABLE Bolnica (
    IDBol INT PRIMARY KEY,
    Naziv NVARCHAR(100),
    Kapacitet INT,
    Iskoriscenost CHAR(1),
    IDMes INT,
    FOREIGN KEY (IDMes) REFERENCES Mesto(IDMes)
);

-- Kreiranje tabele Pacijent
CREATE TABLE Pacijent (
    IDPac INT PRIMARY KEY,
    Ime NVARCHAR(50),
    JMBG NVARCHAR(13),
    GodineStarosti INT,
    IDMes INT,
    FOREIGN KEY (IDMes) REFERENCES Mesto(IDMes)
);

-- Kreiranje tabele Lekar
CREATE TABLE Lekar (
    IDLek INT PRIMARY KEY,
    Ime NVARCHAR(50),
    JMBG NVARCHAR(13),
    Specijalizacija NVARCHAR(100)
);

-- Kreiranje tabele Karton
CREATE TABLE Karton (
    IdKar INT PRIMARY KEY,
    IDPac INT,
    IDBol INT,
    FOREIGN KEY (IDPac) REFERENCES Pacijent(IDPac),
    FOREIGN KEY (IDBol) REFERENCES Bolnica(IDBol)
);

-- Kreiranje tabele Pregled
CREATE TABLE Pregled (
    IdPre INT PRIMARY KEY,
    Datum DATE,
    ObjektivniNalaz NVARCHAR(MAX),
    DIjagnoza NVARCHAR(MAX),
    IDKar INT,
    IdLek INT,
    FOREIGN KEY (IDKar) REFERENCES Karton(IdKar),
    FOREIGN KEY (IdLek) REFERENCES Lekar(IDLek)
);

-- Kreiranje tabele Hospitalizacija
CREATE TABLE Hospitalizacija (
    IDHos INT PRIMARY KEY,
    DatumPri DATE,
    DatumOtp DATE,
    Dijagnoza NVARCHAR(MAX),
    KlinickaSlika NVARCHAR(MAX),
    IDPac INT,
    IDBol INT,
    FOREIGN KEY (IDPac) REFERENCES Pacijent(IDPac),
    FOREIGN KEY (IDBol) REFERENCES Bolnica(IDBol)
);

-- Kreiranje tabele Overa
CREATE TABLE Overa (
    IDKar INT,
    RedBr INT,
    DatumOd DATE,
    NazivPoslodavca NVARCHAR(100),
    FOREIGN KEY (IDKar) REFERENCES Karton(IdKar)
);

-- Kreiranje tabele Radi
CREATE TABLE Radi (
    IDLek INT,
    IDBol INT,
    FOREIGN KEY (IDLek) REFERENCES Lekar(IDLek),
    FOREIGN KEY (IDBol) REFERENCES Bolnica(IDBol)
);

-- Popunjavanje tabela Mesto, Bolnica, Pacijent, Lekar - Primer podataka
INSERT INTO Mesto (IDMes, Naziv) VALUES
(1, 'Beograd'),
(2, 'Novi Sad'),
(3, 'Niš');

INSERT INTO Bolnica (IDBol, Naziv, Kapacitet, Iskoriscenost, IDMes) VALUES
(1, 'Klinički centar Srbije', 1000, 'V', 1),
(2, 'Klinički centar Novi Sad', 800, 'S', 2),
(3, 'Klinički centar Niš', 700, 'N', 3);

INSERT INTO Pacijent (IDPac, Ime, JMBG, GodineStarosti, IDMes) VALUES
(1, 'Marko Marković', '0101991734567', 45, 1),
(2, 'Ana Anić', '0202992834567', 35, 2),
(3, 'Petar Petrović', '0303993934567', 50, 3);

INSERT INTO Lekar (IDLek, Ime, JMBG, Specijalizacija) VALUES
(1, 'Milica Milenković', '0404994834567', 'Opšta medicina'),
(2, 'Stefan Stefanović', '0505995934567', 'Hirurgija'),
(3, 'Jovana Jovanović', '0606996034567', 'Pediatrija');

-- Dodavanje ostalih podataka u preostale tabele prema potrebi
-- Popunjavanje tabele Karton - Primer podataka
INSERT INTO Karton (IdKar, IDPac, IDBol) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3);

-- Popunjavanje tabele Pregled - Primer podataka
INSERT INTO Pregled (IdPre, Datum, ObjektivniNalaz, DIjagnoza, IDKar, IdLek) VALUES
(1, '2024-04-20', 'Normalan nalaz.', 'Prehlada', 1, 1),
(2, '2024-04-22', 'Upala slepog creva.', 'Akutni abdomen', 2, 2),
(3, '2024-04-25', 'Visoka temperatura.', 'Grip', 3, 3);

-- Popunjavanje tabele Hospitalizacija - Primer podataka
INSERT INTO Hospitalizacija (IDHos, DatumPri, DatumOtp, Dijagnoza, KlinickaSlika, IDPac, IDBol) VALUES
(1, '2024-04-20', NULL, 'Prehlada', 'Pacijent ima simptome prehlade.', 1, 1),
(2, '2024-04-22', NULL, 'Upala slepog creva', 'Pacijent ima jake bolove u stomaku.', 2, 2),
(3, '2024-04-25', NULL, 'Grip', 'Pacijent ima visoku temperaturu i kašalj.', 3, 3);

-- Popunjavanje tabele Overa - Primer podataka
INSERT INTO Overa (IDKar, RedBr, DatumOd, NazivPoslodavca) VALUES
(1, 1, '2024-04-20', 'RFZO'),
(1, 2, '2024-04-22', 'RFZO'),
(2, 1, '2024-04-22', 'Dunav osiguranje'),
(3, 1, '2024-04-25', 'Univerzal osiguranje');

-- Popunjavanje tabele Radi - Primer podataka
INSERT INTO Radi (IDLek, IDBol) VALUES
(1, 1),
(2, 2),
(3, 3);