create database TEMPUS
go

use TEMPUS
go

create schema tmp authorization dbo -- myslim ze tu vubec nemsi byt tp=tempus
go

create table tmp.sazba
(
id_sazba int identity(1,1) primary key,
nazev_pozice varchar (100),
vyse_sazby int
)

create table tmp.majetek
(
id_majetek int identity(1,1) primary key,
nazev varchar (50),
cena int
)

create table tmp.riziko
(
id_riziko int identity(1,1) primary key,
nazev varchar (50),
popis varchar (200)
)

create table tmp.WP
(
id_wp int identity(1,1) primary key,
popis varchar (200),
doba_realizace varchar (50)
)

create table tmp.riziko_WP
(id_riziko int,
id_wp int,
foreign key (id_riziko) references tmp.riziko (id_riziko),
foreign key (id_wp) references tmp.WP (id_wp),
constraint pk_riziko_wp primary key (id_riziko, id_wp)
)

create table tmp.cestovne
(
id_cestovne int identity(1,1) primary key,
pocet_osob int,
naklady int
)

create table tmp.zdroj
(
id_zdroj int identity(1,1) primary key,
id_cestovne int,
id_majetek int,
foreign key(id_cestovne) references tmp.cestovne (id_cestovne),
foreign key(id_majetek) references tmp.majetek (id_majetek)
)

create table tmp.aktivita
(
id_aktivita int identity(1,1) primary key,
popis varchar (200),
id_zdroj int,
foreign key(id_zdroj) references tmp.zdroj (id_zdroj)
)

create table tmp.aktivita_na_WP
(
id_wp int,
id_aktivita int,
foreign key(id_wp) references tmp.WP (id_wp),
foreign key(id_aktivita) references tmp.aktivita (id_aktivita),
constraint pk_aktivita_na_WP primary key (id_wp, id_aktivita)
)

create table tmp.EU_zeme
(
id_EU_zeme int identity(1,1) primary key
)

create table tmp.nonEU_zeme
(
id_nonEU_zeme int identity(1,1) primary key
)

create table tmp.zeme
(
id_zeme int identity(1,1) primary key,
nazev_zeme varchar (100),
skratka_zeme varchar (80),
EU bit,
id_EU_zeme int foreign key references tmp.EU_zeme (id_EU_zeme),
id_nonEU_zeme int foreign key references tmp.nonEU_zeme (id_nonEU_zeme)
)

create table tmp.cesta
(
predchazejici_cesta varchar (200),
pocet_dni varchar (50),
id_cestovne int,
id_zeme int,
foreign key(id_cestovne) references tmp.cestovne (id_cestovne),
foreign key(id_zeme) references tmp.zeme (id_zeme),
constraint pk_cesta primary key (id_cestovne, id_zeme)
)

create table tmp.univerzita
(
id_univerzity int identity(1,1) primary key,
nazev_univerzity varchar (100),
id_zeme int,
foreign key(id_zeme) references tmp.zeme (id_zeme)
)

create table tmp.fakulta
(
id_fakulty int identity(1,1) primary key,
nazev_fakulty varchar (100),
id_univerzity int,
foreign key(id_univerzity) references tmp.univerzita (id_univerzity)
)

------------------------- po sem funkcni -------------------------------------

create table tmp.resitel
(
id_resitele int identity(1,1) primary key,
jemno varchar (50),
prijmeni varchar (50),
id_fakulty int foreign key references tmp.fakulta (id_fakulty)
)

create table tmp.projekt
(
id_projektu int identity(1,1),
nazev_projektu varchar(50),
cil varchar(100),
popis varchar(200),
datum date,
primary key (id_projektu)
)

create table tmp.prac_skupina
(
id_prac_skupiny int identity(1,1),
nazev varchar(100),
primary key (id_prac_skupiny),
id_resitele int foreign key references tmp.resitel (id_resitele)
)

----------------- od tut si nejsem jist -----------
create table tmp.osoba
(
id_osoba int identity(1,1) primary key,
jmeno varchar (50),
prijmeni varchar (50),
id_fakulty int foreign key references tmp.fakulta (id_fakulty)
)

create table tmp.osoba_na_aktivite
(
id_osoba int, -- id_osoba int foreign key references tmp.osoba (id_osoba),
id_zdroj int, -- id_zdroj int foreign key references tmp.zdroj (id_zdroj),
id_sazba int, -- id_sazba int foreign key references tmp.sazba (id_sazba)
kapacita int,
foreign key(id_osoba) references tmp.osoba (id_osoba),
foreign key(id_zdroj) references tmp.zdroj (id_zdroj),
foreign key(id_sazba) references tmp.sazba (id_sazba),
constraint pk_osoba_na_aktivite primary key (id_osoba, id_zdroj, id_sazba)
)

create table tmp.osoba_na_prac_skupinu
(
id_prac_skupiny int,
id_osoba int ,
foreign key(id_prac_skupiny) references tmp.prac_skupina (id_prac_skupiny), --id_prac_skupiny int foreign key references tmp.prac_skupina (id_prac_skupiny),
foreign key(id_osoba) references tmp.osoba (id_osoba),
constraint pk_osoba_na_prac_skupinu primary key (id_prac_skupiny, id_osoba) --id_osoba int foreign key references tmp.osoba (id_osoba),
)

create table wp_na_projektu
(
id_projektu int,
id_wp int,
foreign key(id_projektu) references tmp.projekt (id_projektu), --id_projektu int foreign key references tmp.projekt (id_projektu),
foreign key(id_wp) references tmp.WP (id_wp),
constraint pk_wp_na_projektu primary key (id_projektu, id_wp) --id_wp int foreign key references tmp.WP (id_wp),
)

------------------------------------ naplnění daty ----------------------------------------------

insert into tmp.sazba (id_sazba, nazeve_pozice, vyse_sazby)
values (, 'manager', '150')