use master;
IF DB_ID('OsobaVztah') is not null
DROP  DATABASE OsobaVztah;

GO

IF DB_ID('OsobaVztah') is null
CREATE DATABASE OsobaVztah;

GO

USE OsobaVztah;

GO

CREATE TABLE Osoba (
	id INT NOT NULL PRIMARY KEY,
	meno VARCHAR(10) NOT NULL,
	priezvisko VARCHAR(20) NOT NULL,
	rodne_priezvisko VARCHAR(20),
	dat_nar DATE NOT NULL,
	dat_smrti DATE,
	pohlavie CHAR(1) NOT NULL CHECK (pohlavie IN ('m','z')), 
	vyska DEC(4,1) CHECK (vyska BETWEEN 30.0 AND 250.0), 
	vaha DECIMAL(4,1),		-- To iste ako DEC(4,1)
	otec INT ,
	matka INT ,
	CONSTRAINT fk_otec FOREIGN KEY (otec) REFERENCES Osoba(id),
	CONSTRAINT fk_matka FOREIGN KEY (matka) REFERENCES Osoba(id)
);

GO

CREATE TABLE Vztah
( 
  id      INT  NOT NULL PRIMARY KEY, 
  id_on   INT  NOT NULL,
  id_ona  INT  NOT NULL,
  od      DATETIME NOT NULL,
  do      DATETIME,
  CONSTRAINT fk_on FOREIGN KEY (id_on) REFERENCES Osoba(id),
  CONSTRAINT fk_ona FOREIGN KEY (id_ona) REFERENCES Osoba(id),
);

INSERT Osoba VALUES(1, 'Adam', 'Prvy', NULL, '1918.05.11', '1968.10.01', 'm', 180.0, 80.0, NULL, NULL);
INSERT Osoba VALUES(2, 'Eva', 'Prva', 'Druha', '1919.1.9', '1988.7.22', 'z', 160.0, 60.0, NULL, NULL);
INSERT Osoba VALUES(3, 'Zoly', 'Mudry', NULL, '1918.4.7', '19900923', 'm', 175.5, 75, NULL, NULL);
INSERT Osoba VALUES(4, 'NASta', 'Kovacova', 'Rostova', '1928.2.5', '1965.3.11', 'z', 155.0, 99, NULL, NULL); 
INSERT INTO Osoba (id, priezvisko, meno, rodne_priezvisko, dat_nar, dat_smrti, pohlavie, vyska, vaha, otec, matka )
   VALUES(5,'Urban' , 'Jozef', NULL, '1922.10.19', NULL, 'm', 199.5, Null, NULL, NULL); -- meno vs. priezvisko
INSERT INTO Osoba (id,meno, priezvisko, rodne_priezvisko, dat_nar, dat_smrti, pohlavie, vyska, vaha, otec, matka )
VALUES(6, 'Maria', 'Urbanova', 'Novakova', '1937.12.8', NULL, 'z', 172.5, 57.5, 1, 2 ),
(7, 'Patrik', 'Novak', 'Novak', '1945.6.19', NULL, 'm', 182.5, 89.5, 1, 2 ),
(8, 'Patricia','Novakova', 'Haluskova', '1952.1.8', NULL, 'z', 143.5, 35, NULL, NULL),
(9, 'Michal', 'Kovac', 'Kovac', '1942.4.10', NULL, 'm', 167.0, 88, 3, 2 );
INSERT Osoba VALUES(10,'Roman', 'Kovac', 'Kovac', '1948.5.20', NULL, 'm', 179.5, 78.5, 3, 4 ), -- aj tak sa da :)
(11,'Peter', 'Horvath', 'Horvath', '1959.7.2', '2000.12.31','m', 193.0, 110.5,NULL, NULL);
INSERT Osoba VALUES(12,'Lucia', 'Horvathova','Urbanova', '1959.1.13', NULL, 'z', 156.5, 45.5, 5, 6 );
INSERT Osoba VALUES(13,'Urban', 'Urban', 'Urban', '1957.3.31', NULL, 'm', 138.2, 24.5, 5, 6 );
INSERT Osoba VALUES(14,'DASa', 'Novakova', 'Novakova', '1970.7.17', NULL, 'z', 167.0, 55.0, 7, 8 );
INSERT Osoba VALUES(15,'Viera', 'Silna', 'Novakova', '1973.2.13', NULL, 'z', 169.5, 63.0, 7, 8 );
INSERT Osoba VALUES(16,'Vladimir','Silny', 'Silny', '1974.8.1', '2002.12.4', 'm', 175.5, 73.0, NULL, NULL);
INSERT Osoba VALUES(17,'Milena', 'Slaba', 'Slaba', '1979.9.14', NULL, 'z', 164.0, 64.0, NULL, NULL);
INSERT Osoba VALUES(18,'Jan', 'Horvath', 'Horvath', '1982.1.16', NULL, 'm', 159.5, 65.5, 11, 12 );
INSERT Osoba VALUES(19,'Zuzana', 'Silna', 'Silna', '2002.3.1', NULL, 'z', 158.5, 60.0, 16, 15 );
INSERT Osoba VALUES(20,'Zuzana', 'Slaba', 'Slaba', '1999.12.16', NULL, 'z', 171.5, 54.5, 16, 17 );
INSERT Osoba VALUES(21,'Zuzana', 'Prava', 'Prava', '1990.11.26', NULL, 'z', 170.5, 60.5, 16, 17 );
INSERT Osoba VALUES(22,'Zuzana', 'Lava', 'Lava', '1931.01.14', NULL, 'z', 195.5, 58.5, 16, 17 );
INSERT Osoba VALUES(23,'Zuzana', 'Stredna', 'Stredna', '1945.04.08', NULL, 'z', 150.5, 87, 16, 17 );

INSERT INTO Vztah VALUES (1,1, 2, '1937.6.1',  '1967.5.11' );
INSERT Vztah VALUES (2,3, 2, '1967.5.12', '1988.7.22' );
INSERT Vztah VALUES (3,3, 4, '1938.12.2', '1965.3.11' );
INSERT Vztah VALUES (4,5, 6, '1953.11.11', NULL       );
INSERT Vztah VALUES (5,7, 8, '1970.7.22', '1975.9.1'  );
INSERT Vztah VALUES (6,11,12,'1980.3.4',  '2000.12.31');
INSERT Vztah VALUES (7,16,15,'1997.7.31', '2002.12.4' );

SELECT * FROM Vztah;
SELECT * FROM Osoba;