САБ/К2Н 2023

Поставка

У систему болничког лечења прате се информације о лекарима који раде у болници и прегледима који су ти лекари обавили. Сваки пацијент може имати највише један картон у свакој од болница. Сваки картон се повремено оверава када се уписује назив послодавца преко кога је пацијент осигуран. У случајевима када је то потребно пацијенти могу бити хоспитализовани, тј. примљени на болничко лечење (датум отпуштања се попуњава тек након што се лечење заврши, а до тада је NULL).
Одговарајућа шема базе је:

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

За посматрани систем саставити SQLServer процедуру AzurirajIskoriscenost(), која поставља атрибут искоришћеност у релацији Болница. Искоришћеност се поставља на основу врше (максималне) вредности попуњености болнице у било којем тренутку до сада. Конкретно, уколико је у било ком тренутку број хоспитализованих паицјената био преко 75% капацитета, она је висока. Уколико је мање или једнако од 75%, а више од 25% онда је средња. У супротном, уколико је мање или једнако 25% онда је њена искоришћеност ниска. У дане боравка у болници треба укључити датум пријема, али не и датум отпуштања из болнице.

Решење

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

Тестирање

-- 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);