Changes between Version 6 and Version 7 of RelationalDesign

Timestamp:

12/14/23 18:21:00 (10 months ago)

Author:

090810

Comment:

--

RelationalDesign

@@ -96 +96 @@
 
 == 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 скрипта за полнење на табелите со податоци
+
+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
 [[Image(schema.png)]]