P2: Логички и физички дизајн - Креирање база податоци (со SQL DDL)

Релациска шема

Легенда:

Болдирано = not null;

Подвлечено = primary key;

Црвена боја на букви = unique constraint;

Трансформација

Tip_dijabetes(id, ime)

Tip_insulin(id, ime)

Nacin_prigotvuvanje_hrana(id, ime)

Covek(id, embg, ime, prezime, pol, datum_na_ragjanje)

Doktor(id*)

*nadvoreshen kluc kon Covek(id)

Dijabeticar(id*, data_na_otkrivanje, tip_dijabetes_id^)

*nadvoreshen kluc kon Covek(id) ^nadvoreshen kluc kon Tip_dijabetes(id)

Datoteka_metapodatoci(id, ime, hash, opis, mime, tip_datoteka)

Faksimil(id*, doktor_id^, dijabeticar_id+)

*nadvoreshen kluc kon Datoteka_metapodatoci(id)

^nadvoreshen kluc kon Doktor(id)

+nadvoreshen kluc kon Dijabeticar(id)

Zdravstven_rezultat(id*, dijabeticar_id^)

*nadvoreshen kluc kon Datoteka_metapodatoci(id)

^nadvoreshen kluc kon Dijabeticar(id)

Medikament(id, ime, proizvoditel, tip_medikament)

Lenti_merenje_shekjer(id*)

*nadvoreshen kluc kon Medikament(id)

Insulin(id*, tip_insulin_id^)

*nadvoreshen kluc kon Medikament(id)

^nadvoreshen kluc kon Tip_insulin(id)

Inventar(dijabeticar_id*, medikament_id^, rok_na_traenje, kolicina)

*nadvoresen kluc kon Dijabeticar(id)

^nadvoresen kluc kon Medikament(id)

Zapis(dijabeticar_id*, data)

*nadvoresen kluc kon Dijabeticar(id)

Zapis_insulin((dijabeticar_id*, data)*)

*nadvoresen kluc kon Dijabeticar(id, data)

Zapis_insulin_doziran_Insulin((dijabeticar_id, data)*, insulin_id^, kolicina)

*nadvoresen kluc kon Dijabeticar(id, data) ^nadvoresen kluc kon Insulin(id)

Zapis_so_izmeren_shekjer((dijabeticar_id, data)*, vrednost)

*nadvoresen kluc kon Dijabeticar(id, data)

Zapis_hrana((dijabeticar_id, data)*)

*nadvoresen kluc kon Dijabeticar(id, data)

Hrana(id, ime, proizvoditel, glikemiski_indeks, proteini, shekjeri, masti, jaglehidrati, kalorii, nacin_prigotvuvanje_hrana_id*)

* nadvoresen kluc kon Nacin_prigotvuvanje_hrana(id)

Obrok((dijabeticar_id, data)*, hrana_id^)

*nadvoresen kluc kon Zapis_hrana(dijabeticar_id, data)

^nadvoresen kkluc kon Hrana(id)

DDL скрипта за бришење на табелите и креирање на табелите

set search_path = project;

DROP TABLE if exists obrok; DROP TABLE if exists hrana; DROP TABLE if exists zapis_hrana; DROP TABLE if exists zapis_so_izmeren_sekjer; DROP TABLE if exists zapis_insulin_doziran_insulin; DROP TABLE if exists zapis_insulin; DROP TABLE if exists zapis; DROP TABLE if exists inventar; DROP TABLE if exists insulin; DROP TABLE if exists lenti_merenje_shekjer; DROP TABLE if exists medikament; DROP TABLE if exists zdrastven_rezultal; DROP TABLE if exists faksimil; DROP TABLE if exists datoteka_metapodatoci; DROP TABLE if exists dijabeticar; DROP TABLE if exists doktor; DROP TABLE if exists covek; DROP TABLE if exists nacin_prigotvuvanje_hrana; DROP TABLE if exists tip_insulin; DROP TABLE if exists tip_dijabetes;

-- отстранување на шемата

drop schema if exists project;

-- креирање на шемата

create schema project;

-- се поставува патека односно шема во која ќе се извршуваат командите

set search_path = project;

-- tip_dijabetes definition

-- Drop table

CREATE TABLE tip_dijabetes (

id serial4 NOT NULL, ime varchar(256) NOT NULL, CONSTRAINT "PK_tip_dijabetes_id" PRIMARY KEY (id), CONSTRAINT "UNQ_Tip_dijabetes_ime" UNIQUE (ime)

);

-- tip_insulin definition

-- Drop table

CREATE TABLE tip_insulin (

id serial4 NOT NULL, ime varchar(256) NOT NULL, CONSTRAINT "PK_Tip_insulin_id" PRIMARY KEY (id), CONSTRAINT "UNQ_Tip_insulin_ime" UNIQUE (ime)

);

-- nacin_prigotvuvanje_hrana definition

-- Drop table

CREATE TABLE nacin_prigotvuvanje_hrana (

id serial4 NOT NULL, ime varchar(256) NOT NULL, CONSTRAINT "PK_Nacin_prigotvuvanje_hrana_id" PRIMARY KEY (id), CONSTRAINT "UNQ_Nacin_prigotvuvanje_hrana_ime" UNIQUE (ime)

);

-- Drop table

CREATE TABLE covek (

id serial4 NOT NULL, embg char(13) NOT NULL, ime varchar(256) NOT NULL, prezime varchar(256) NOT NULL, pol bpchar(1) NOT NULL, datum_na_ragjanje date NOT NULL,

CONSTRAINT "PK_covek_id" PRIMARY KEY (id), CONSTRAINT "UNQ_covek_embg" unique(embg), CONSTRAINT "CHK_covek_pol" CHECK ((pol = ANY (ARRAY['M'::bpchar, 'Z'::bpchar])))

);

-- doktor definition

-- Drop table

CREATE TABLE doktor (

id int4 NOT NULL, CONSTRAINT "PK_doktor_id" PRIMARY KEY (id), CONSTRAINT "FK_doktor_id" FOREIGN KEY (id) REFERENCES covek(id)

);

-- dijabeticar definition

-- Drop table

CREATE TABLE dijabeticar (

id int4 NOT NULL, data_na_otkrivanje date NOT NULL, tip_dijabetes_id int4 NOT NULL, CONSTRAINT "PK_dijabeticar_id" PRIMARY KEY (id), CONSTRAINT "FK_dijabeticar_id" FOREIGN KEY (id) REFERENCES covek(id), CONSTRAINT "FK_dijabeticar_tip_dijabetes_id" FOREIGN KEY (tip_dijabetes_id) REFERENCES tip_dijabetes(id)

);

-- datoteka_metapodatoci definition

-- Drop table

CREATE TABLE datoteka_metapodatoci (

id serial4 NOT NULL, ime varchar(400) NOT NULL, hash varchar(400) NOT NULL, opis varchar(1000) NOT null default , mime varchar(300) NOT NULL, tip_datoteka bpchar(1) NOT NULL,

CONSTRAINT "PK_datoteka_metapodatoci_id" PRIMARY KEY (id), CONSTRAINT "UNQ_datoteka_metapodatoci_hash" UNIQUE (hash), CONSTRAINT "CHK_datoteka_metapodatoci_tip_datoteka" CHECK ((tip_datoteka = ANY (ARRAY['F'::bpchar, 'Z'::bpchar])))

);

-- faksimil definition

-- Drop table

CREATE TABLE faksimil (

id int4 NOT NULL, doktor_id int4 NOT NULL, dijabeticar_id int4 NOT NULL, CONSTRAINT "PK_faksimil_id" PRIMARY KEY (id), CONSTRAINT "FK_dijabeticar_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id), CONSTRAINT "FK_doktor_id" FOREIGN KEY (doktor_id) REFERENCES doktor(id), CONSTRAINT "FK_faksimil_id" FOREIGN KEY (id) REFERENCES datoteka_metapodatoci(id)

);

-- zdrastven_rezultal definition

-- Drop table

CREATE TABLE zdrastven_rezultat (

id int4 NOT NULL, dijabeticar_id int4 NOT NULL, CONSTRAINT "PK_zdrastven_rezultat_id" PRIMARY KEY (id), CONSTRAINT "FK_zdrastven_rezultat_dijabeticar_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id), CONSTRAINT "FK_zdrastven_rezultat_id" FOREIGN KEY (id) REFERENCES datoteka_metapodatoci(id)

);

-- medikament definition

-- Drop table

CREATE TABLE medikament (

id serial4 NOT NULL, ime varchar(256) NOT NULL, proizvoditel varchar(256) NOT NULL, tip_medikament bpchar(1) NOT NULL, CONSTRAINT "CHK_Medikament_tip_medikament" CHECK ((tip_medikament = ANY (ARRAY['L'::bpchar, 'I'::bpchar]))), CONSTRAINT "PK_Medikament_id" PRIMARY KEY (id), constraint "UNQ_medikament_ime" unique(ime)

);

-- lenti_merenje_shekjer definition

-- Drop table

CREATE TABLE lenti_merenje_shekjer (

id int4 NOT NULL, CONSTRAINT "PK_Lenti_merenje_shekjer_id" PRIMARY KEY (id), CONSTRAINT "FK_Lenti_merenje_shekjer_id_Medikament_id" FOREIGN KEY (id) REFERENCES medikament(id)

);

-- insulin definition

-- Drop table

CREATE TABLE insulin (

id int4 NOT NULL, tip_insulin_id int4 NULL, CONSTRAINT "PK_Insulin_id" PRIMARY KEY (id), CONSTRAINT "FK_Insulin_id_Medikament_id" FOREIGN KEY (id) REFERENCES medikament(id), CONSTRAINT "FK_Insulin_tip_insulin_id_Tip_insulin_id" FOREIGN KEY (tip_insulin_id) REFERENCES tip_insulin(id)

);

-- inventar definition

-- Drop table

CREATE TABLE inventar (

dijabeticar_id int4 NOT NULL, medikament_id int4 NOT NULL, rok_na_traenje date NOT NULL, kolicina int4 NOT NULL, CONSTRAINT "PK_inventar_id" PRIMARY KEY (dijabeticar_id, medikament_id, rok_na_traenje), CONSTRAINT "FK_dijabeticar_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id), CONSTRAINT "FK_medikament_id" FOREIGN KEY (medikament_id) REFERENCES medikament(id)

);

-- zapis definition

-- Drop table

CREATE TABLE zapis (

dijabeticar_id int4 NOT NULL, "data" date NOT NULL, CONSTRAINT "PK_zapis_id" PRIMARY KEY (dijabeticar_id, data), CONSTRAINT "FK_zapis_id" FOREIGN KEY (dijabeticar_id) REFERENCES dijabeticar(id)

);

-- zapis_insulin definition

-- Drop table

CREATE TABLE zapis_insulin (

dijabeticar_id int4 NOT NULL, "data" date NOT NULL, CONSTRAINT "PK_zapis_insulin_id" PRIMARY KEY (dijabeticar_id, data), CONSTRAINT "FK_zapis_insulin_id" FOREIGN KEY (dijabeticar_id,"data") REFERENCES zapis(dijabeticar_id,"data")

);

-- zapis_insulin_doziran_insulin definition

-- Drop table

CREATE TABLE zapis_insulin_doziran_insulin (

dijabeticar_id int4 NOT NULL, "data" date NOT NULL, insulin_id int4 NOT NULL, kolicina int4 NOT NULL, CONSTRAINT "PK_zapis_insulin_doziran_insulin" PRIMARY KEY (dijabeticar_id, data, insulin_id), CONSTRAINT "FK_zapis_insulin_doziran_insulin" FOREIGN KEY (dijabeticar_id,"data") REFERENCES zapis_insulin(dijabeticar_id,"data"), CONSTRAINT "FK_zapis_insulin_doziran_insulin_id" FOREIGN KEY (insulin_id) REFERENCES insulin(id)

);

-- zapis_so_izmeren_sekjer definition

-- Drop table

CREATE TABLE zapis_so_izmeren_sekjer (

dijabeticar_id int not null, data date not null, vrednost decimal(4,1) not null,

constraint "PK_zapis_so_izmeren_sekjer_id" primary key(dijabeticar_id, data), constraint "FK_zapis_so_izmeren_sekjer_id" foreign key(dijabeticar_id, data) references zapis(dijabeticar_id, data)

);

-- zapis_hrana definition

-- Drop table

CREATE TABLE zapis_hrana (

dijabeticar_id int not null, data date not null,

constraint "PK_zapis_hrana_id" primary key(dijabeticar_id, data), constraint "FK_zapis_hrana_id" foreign key(dijabeticar_id, data) references zapis(dijabeticar_id, data)

); -- hrana definition

-- Drop table

CREATE TABLE hrana (

id serial not null, ime varchar(500) not null, proizvoditel varchar(1000) not null default , glikemiski_indeks int not null check (glikemiski_indeks>=0 and glikemiski_indeks<=100), proteini decimal(3,1) not null check (proteini>=0 and proteini<=100), shekjeri decimal(3,1) not null check (shekjeri>=0 and shekjeri<=100), masti decimal(3,1) not null check (masti>=0 and masti<=100), jaglehidrati decimal(3,1) not null check (jaglehidrati>=0 and jaglehidrati<=100), kalorii int not null check (kalorii>=0), nacin_prigotvuvanje_hrana_id int not null,

constraint "PK_hrana_id" primary key(id), constraint "FK_hrana_nacin_prigotvuvanje_hrana_id" foreign key(nacin_prigotvuvanje_hrana_id) references nacin_prigotvuvanje_hrana(id), constraint "CHK_hrana_shekejeri" check (shekjeri <= jaglehidrati), CONSTRAINT "CHK_hrana_proteini_jaglehidrati_masti" check(proteini+jaglehidrati+masti > 0 AND proteini+jaglehidrati+masti <= 100)

);

-- obrok definition

-- Drop table

CREATE TABLE obrok (

dijabeticar_id int not null, data date not null, hrana_id int not null, kolicina int not null check (kolicina>0),

constraint "PK_obrok_id" primary key(dijabeticar_id, data, hrana_id), constraint "FK_obrok_dijabeticar" foreign key(dijabeticar_id, data) references zapis_hrana(dijabeticar_id, data), constraint "FK_obrok_hrana_id" foreign key(hrana_id) references hrana(id)

);

-- DISJOINT CHECK CONSTRAINT ZA DATOTEKA_METAPODATOCI

create function fn_validiraj_medikament_disjoint(

p_id int, p_tip_medikament char

) returns boolean language sql

returns null on null input as $$ select coalesce ((select 1 from medikament m where m.id = p_id and tip_medikament = p_tip_medikament), 0) = 1; $$;

alter table lenti_merenje_shekjer add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_medikament_lenti_merenje_shekjer" check (fn_validiraj_medikament_disjoint(lenti_merenje_shekjer.id, 'L'));

alter table insulin add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_medikament_insulin" check (fn_validiraj_medikament_disjoint(insulin.id, 'I'));

-- DISJOINT CHECK CONSTRAINT ZA DATOTEKA_METAPODATOCI

create function fn_Validiraj_datoteka_metapodatoci_disjoint(

p_faksimil_id int , p_tip_datoteka char

) returns int as $$

select coalesce (

(select 1 from datoteka_metapodatoci where id = p_faksimil_id and tip_datoteka = p_tip_datoteka ), 0

)

$$ language sql RETURNS NULL ON NULL INPUT;

alter table faksimil add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_datoteka_metapodatoci_faksimil" check(fn_Validiraj_datoteka_metapodatoci_disjoint(id, 'F')=1); alter table zdrastven_rezultat add constraint "CHK_Validiraj_kreiran_prethoden_zapis_vo_datoteka_metapodatoci_za_zdravstven_rezultat" check(fn_Validiraj_datoteka_metapodatoci_disjoint(id, 'Z')=1);

DML скрипта за полнење на табелите со податоци

set search_path = project;

insert into tip_dijabetes(ime) values ('tip 1') , ('tip 2');

insert into tip_insulin(ime) values ('RAPID') , ('BASAL');

insert into nacin_prigotvuvanje_hrana (ime) values ('svezho') , ('zamrznato') , ('pecheno') , ('vareno') , ('dinstano') , ('przheno') , ('konzenvirano');

insert into covek(embg, ime, prezime, pol, datum_na_ragjanje) values ('0101994000000', 'Kristijan', 'Ristovski', 'M', '1994-01-01') , ('0911996123456', 'Nikola', 'Torbovski', 'M', '1996-11-09') , ('1809004111111', 'Marija', 'Trajkovska', 'Z', '2004-04-18') , ('110200299999', 'Luka', 'Markovski', 'M', '2002-02-11') , ('0606999546872', 'Marionka', 'Japonka', 'Z', '1999-06-06') , ('1708021333333', 'Nina', 'Jadrovska', 'Z', '2021-08-17');

insert into dijabeticar (id, data_na_otkrivanje, tip_dijabetes_id) values (2, '1996-11-09', 1) , (3, '2010-01-15', 2) , (6, '2022-09-01', 1);

insert into doktor (id) values(1) , (4) , (5);

insert into datoteka_metapodatoci (ime, hash, opis, mime, tip_datoteka) values ('zdravstven_rezultat_01_02_2023.pdf', '30e9b774c4691c38f0e3251545d1e238', , 'application/pdf', 'Z') , ('faksimil_09_08_2021.pdf', 'f75be9d5c7ee578aeec5fef7005f78a3', , 'application/pdf', 'F') , ('faksimil_19_09_2020.pdf', 'a96ef978bcd93a0813000886b09d99b5', 'Faksimil izdaden na 19-09-2020', 'application/pdf', 'F') , ('zdravstven_rezultat_08_07_2022.pdf', '86e014467d5d5a21645bc17fb4bf328a', 'Opis za zdravstven rezultat...', 'application/pdf', 'Z') , ('faksimil_07_12_2005.pdf', '0ef75e84f52a9793326dbf3f36fec367', , 'application/pdf', 'F') , ('zdravstven_rez_07_01_2001.pdf', '3e7a11c3f47cf8cc55fcf5e7f5bce614', ':)', 'application/pdf', 'Z');

insert into zdrastven_rezultat (id, dijabeticar_id) values (1, 2) , (4, 3) , (6, 6);

insert into faksimil (id, doktor_id, dijabeticar_id) values (2, 4, 2) , (3, 4, 2) , (5, 5, 6);

insert into medikament (ime, proizvoditel, tip_medikament) values ('Lantus', 'Sanofi Aventis', 'I') , ('Apidra', 'Sanofi Aventis', 'I') , ('Novo Rapid', 'Novo Nordisk', 'I') , ('Tyson Bio AC200', 'Tyson Bioresearch', 'L') , ('Accu-Chek Active', 'Roche Diabetes Care', 'L') , ('OneTouch Ultra', 'Lifescan Medical Devices', 'L');

insert into lenti_merenje_shekjer (id) values (4) , (5) , (6);

insert into insulin (id, tip_insulin_id) values (1, 2) , (2, 1) , (3, 1);

insert into inventar (dijabeticar_id, medikament_id, rok_na_traenje, kolicina) values (2, 1, '2023-05-18', 5) , (2, 1, '2025-09-17', 4) , (2, 3, '2022-10-18', 1) , (3, 5, '2024-12-12', 7) , (6, 6, '2023-01-19', 13) , (6, 6, '2023-05-06', 3);

insert into hrana (ime, proizvoditel, glikemiski_indeks, proteini, jaglehidrati, shekjeri, masti, kalorii, nacin_prigotvuvanje_hrana_id) values ('leb na zar', 'stara furna', 20, 8, 60, 1.3, 1, 300, 3) , ('leb', 'furna dime', 20, 9, 40, 1, 2, 312, 3) , ('oriz', 'kamko', 13, 10, 55, 7, 8, 201, 4) , ('makaroni', 'divela', 7, 11, 51, 8, 3, 135, 3) , ('tuna', 'blue sea', 1, 22, 13, 2, 18, 400, 7) , ('kulen', 'zlatibor', 9, 21, 15, 3, 31, 350, 1);

-- 2 3 6 -- '1996-11-09' '2010-01-15' '2022-09-01'

insert into zapis (dijabeticar_id, "data") values (2, '1999-02-08') , (2, '2000-01-01') , (2, '2001-08-09') , (3, '2010-05-09') , (3, '2010-09-09') , (3, '2012-10-10') , (6, '2022-10-01') , (6, '2022-11-10') , (6, '2022-12-30');

insert into zapis_so_izmeren_sekjer (dijabeticar_id, "data", vrednost) values (2, '1999-02-08', 11.5) ,(2, '2000-01-01', 9.8) , (2, '2001-08-09', 3.1) , (3, '2010-09-09', 8.1) , (3, '2012-10-10', 2.3) , (6, '2022-11-10', 7.9);

insert into zapis_insulin (dijabeticar_id, "data") values (2, '1999-02-08') , (2, '2000-01-01') , (2, '2001-08-09') , (3, '2010-09-09') , (6, '2022-10-01') , (6, '2022-12-30');

insert into zapis_insulin_doziran_insulin (dijabeticar_id, "data", insulin_id, kolicina) values (2, '1999-02-08', 1, 15) , (2, '2000-01-01', 1, 7) , (2, '2001-08-09', 2, 1) , (3, '2010-09-09', 3, 10) , (6, '2022-10-01', 2, 9) , (6, '2022-12-30', 2, 6);

insert into zapis_hrana (dijabeticar_id, "data") values (2, '1999-02-08') , (2, '2001-08-09') , (3, '2010-09-09') , (6, '2022-10-01') , (6, '2022-12-30');

insert into obrok (dijabeticar_id, "data", hrana_id, kolicina) values (2, '1999-02-08', 1, 100) , (2, '1999-02-08', 6, 20) , (2, '1999-02-08', 2, 16) , (2, '2001-08-09', 2, 35) , (3, '2010-09-09', 4, 125) , (3, '2010-09-09', 5, 111) , (6, '2022-10-01', 1, 37) , (6, '2022-10-01', 5, 86) , (6, '2022-12-30', 5, 97) , (6, '2022-12-30', 1, 172);

Релациски дијаграм изваден од DBeaver