CODE:

create database spec_porad; USE spec_porad; create table diagnoza( Id int NOT NULL, Nazov varchar(50) NOT NULL UNIQUE, Popis varchar(500), PRIMARY KEY(Id), ) create table zaradenie ( Id int NOT NULL, Zaradenie varchar(150) UNIQUE, PRIMARY KEY (Id), ) create table mesto( Id int NOT NULL, Mesto varchar(50) NOT NULL, PSC int NOT NULL check(PSC >= 0 and PSC <= 99999), PRIMARY KEY(Id), CONSTRAINT mesto_u UNIQUE (Mesto, PSC) ) create table ulica( Id int NOT NULL, Ulica varchar(50) NOT NULL, PRIMARY KEY(Id) ) create table miesto( Id int NOT NULL, Ulica int NOT NULL, Mesto int NOT NULL, Cislo int NOT NULL, PRIMARY KEY(Id), FOREIGN KEY (Mesto) references mesto(Id) ON UPDATE CASCADE, FOREIGN KEY (Ulica) references ulica(Id) ON UPDATE CASCADE, CONSTRAINT miesto_u UNIQUE (Ulica, Mesto, Cislo) ) create table osoba ( Id int NOT NULL, Meno varchar(25) NOT NULL, Priezvisko varchar(50) NOT NULL, Datum_narodenia date NOT NULL check(Datum_narodenia > '1900-01-01 00:00:00.00'), Vek smallint NOT NULL check(Vek >= 0 and Vek <= 150), Bydlisko int NOT NULL, Telefon int NOT NULL check(Telefon >= 900000000 and Telefon <= 9999999999), CONSTRAINT osoba_pk PRIMARY KEY (Id), FOREIGN KEY (Bydlisko) references miesto(Id) ON UPDATE CASCADE, CONSTRAINT osoba_menopr_u UNIQUE (Meno,Priezvisko,Datum_narodenia) ); create table typ( Id int NOT NULL, Vlastnost varchar(30) NOT NULL, PRIMARY KEY(Id), Platba bit NOT NULL DEFAULT 0, Miesto int NOT NULL, FOREIGN KEY(Miesto) references miesto(Id) ON UPDATE CASCADE, CONSTRAINT typ_un UNIQUE (Vlastnost, Platba, Miesto) ) create table klient ( Id int NOT NULL, Dat_pos_nav date NOT NULL, Typ bit NOT NULL, Diag int NOT NULL, Poznamky varchar(500), PRIMARY KEY (Id), FOREIGN KEY (Id) REFERENCES osoba(Id) ON UPDATE CASCADE, FOREIGN KEY (Diag) REFERENCES diagnoza(Id)ON UPDATE CASCADE ); create table poradca ( Id int NOT NULL, Typ bit NOT NULL, Dat_nastupu date NOT NULL, Dat_ukoncenia date NOT NULL, Email varchar(50) NOT NULL UNIQUE, Zaradenie int NOT NULL, PRIMARY KEY (Id), FOREIGN KEY (Id) REFERENCES osoba(Id) ON UPDATE CASCADE, FOREIGN KEY (Zaradenie) REFERENCES zaradenie(Id) ON UPDATE CASCADE ); create table Navsteva ( Id int NOT NULL, Trvanie_m smallint NOT NULL, Datum date NOT NULL DEFAULT GETDATE(), Cas decimal(4,2) NOT NULL, Poznamka varchar(100), PRIMARY KEY(Id), Klient int NOT NULL, Poradca int NOT NULL, Typu int NOT NULL, FOREIGN KEY (Klient) REFERENCES klient(Id), FOREIGN KEY (Poradca) REFERENCES poradca(Id), FOREIGN KEY (Typu) REFERENCES typ(Id) ON UPDATE CASCADE, ) INSERT INTO mesto VALUES (1,'Sp.Vlachy',05361), (2,'Krompachy',05333), (3,'Kosice',03312); INSERT INTO ulica VALUES (1, 'Stara'), (2, 'Nova'), (3, 'Skareda'); INSERT INTO diagnoza VALUES (1,'Schizofrenia', ''), (2,'Demencia', 'Problemy s'), (3, 'Alkoholizmus', 'Kazdy den aspon 4 pohariky'); INSERT INTO miesto VALUES (1,2,3,33), (2,1,2,54), (3,3,1,23), (4,3,2,66); INSERT INTO osoba VALUES (1,'Matko','Kubko', DATEADD(day,-6750,GETDATE()), DATEDIFF(yy,DATEADD(day,-6750,GETDATE()),GETDATE()),2,0900123456), (2,'Jozko','Palko', DATEADD(day,-8964,GETDATE()), DATEDIFF(yy,DATEADD(day,-8964,GETDATE()),GETDATE()),3,0900321654), (3,'Peter','Maly', DATEADD(day,-9954,GETDATE()), DATEDIFF(yy,DATEADD(day,-9954,GETDATE()),GETDATE()),1,0911123654); INSERT INTO klient VALUES (1,GETDATE(),0,1, 'hmmm'), (2,GETDATE(),1,1,'taky blbec to je'); INSERT INTO zaradenie VALUES (1,'Pracovnik na polovicny uvazok'), (2,'Pracovnik na plny uvazok'), (3,'Externy zamestnanec'), (4,'Vypomoc'); INSERT INTO poradca VALUES (1,1,DATEADD(day,-355,GETDATE()),DATEADD(day,+800,GETDATE()),'nic@cin.ks',3); INSERT INTO typ VALUES (1,'Na sude',1,3), (2,'V kancelarii',0,4), (3,'Dakde daco',1,2); INSERT INTO Navsteva VALUES (1,5,GETDATE(),11.30,'jozko prisiel na kavu',1,1,1), (2,30,GETDATE(),10.00,'peto nic nerobil',2,1,2), (3,8,GETDATE(),15.25,'daco si zabudol',1,1,1); select * from Navsteva; select * from typ; select * from osoba; select * from klient; select * from poradca; select * from zaradenie; select * from miesto; select * from mesto; select * from ulica; INSERT INTO Navsteva VALUES (4,8,GETDATE(),15.25,'daco si zabudol',1,3,1);