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
)

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

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

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

go
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)
)

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

go
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)
)

go
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)
)

go
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)
)

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

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

go
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)
)

go
create table tmp.cesta
(
predchazejici_cesta varchar (200),
pocet_dni int,
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)
)

go
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)
)

go
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)
)

go
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)
)

go
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)
)

go
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)
)

go
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)
)

go
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)
)

go
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),
)

go
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 ----------------------------------------------


go
insert into tmp.sazba ( nazev_pozice, vyse_sazby)
values ( 'manager', '150')
insert into tmp.sazba ( nazev_pozice, vyse_sazby)
values ( 'technik', '130')
insert into tmp.sazba ( nazev_pozice, vyse_sazby)
values ( 'administrace', '75')

go
insert into tmp.majetek ( nazev, cena)
values ( 'stůl', '80')
insert into tmp.majetek ( nazev, cena)
values ( 'židel', '50')
insert into tmp.majetek ( nazev, cena)
values ( 'koberec', '200')
insert into tmp.majetek ( nazev, cena)
values ( 'lampička', '40')

go
insert into tmp.riziko ( nazev, popis)
values ( 'nevčastné dokončení wp', 'projekt bude ukončen')
insert into tmp.riziko ( nazev, popis)
values ( 'dokončení ale špatné odevzdání', 'není možné projekt vyhodnotit')

go
insert into tmp.WP ( popis, doba_realizace)
values ( 'květinové děti', '2 měsíce')
insert into tmp.WP ( popis, doba_realizace)
values ( 'šikaná ve škole', 'týden')
insert into tmp.WP ( popis, doba_realizace)
values ( 'rasismus v Evropě', '3 měsíce')


go
insert into tmp.cestovne ( pocet_osob, naklady)
values ( '2', '300')
insert into tmp.cestovne ( pocet_osob, naklady)
values ( '4', '500')
insert into tmp.cestovne ( pocet_osob, naklady)
values ( '1', '100')
insert into tmp.cestovne ( pocet_osob, naklady)
values ( '50', '2000')

go
insert into tmp.zdroj ( id_cestovne, id_majetek)
values ( 2, 3)


go
insert into tmp.aktivita ( popis, id_zdroj)
values ('kreslení krtečka a veverky', 1)
insert into tmp.aktivita ( popis, id_zdroj)
values ('tvorba ručního papíru', 1)
insert into tmp.aktivita ( popis, id_zdroj)
values ('dělaní masek k dyvadelní hře', 1)

go
insert into tmp.zeme ( nazev_zeme, skratka_zeme, EU) --, id_EU_zeme, id_nonEU_zeme)
values ('Česká Republika', 'ČR', '1')--, 1 , 0)
insert into tmp.zeme ( nazev_zeme, skratka_zeme, EU) --, id_EU_zeme, id_nonEU_zeme)
values ('Německo', 'D', '1')--, 1 , 0)
insert into tmp.zeme ( nazev_zeme, skratka_zeme, EU) --, id_EU_zeme, id_nonEU_zeme)
values ('Švícarsko', 'SW', '0')--,  1 , 0)
insert into tmp.zeme ( nazev_zeme, skratka_zeme, EU) --, id_EU_zeme, id_nonEU_zeme)
values ('Amerika', 'USA', '0')--, 0 , 1)

go
insert into tmp.cesta ( predchazejici_cesta, pocet_dni, id_cestovne, id_zeme)
values ('Do Německa', 5, 1, 7)
insert into tmp.cesta ( predchazejici_cesta, pocet_dni, id_cestovne, id_zeme)
values ('Do Švícarsko', 10, 2, 8)
insert into tmp.cesta ( predchazejici_cesta, pocet_dni, id_cestovne, id_zeme)
values ('Do České Republiky', 50, 3, 6)
insert into tmp.cesta ( predchazejici_cesta, pocet_dni, id_cestovne, id_zeme)
values ('Do USA', 20 , 4, 9)

go
insert into tmp.univerzita ( nazev_univerzity, id_zeme)
values ('Vysoké učení technické', 6)
insert into tmp.univerzita ( nazev_univerzity, id_zeme)
values ('Masarikova univerzita', 10)
insert into tmp.univerzita ( nazev_univerzity, id_zeme)
values ('Univerzita Tomáše Bati', 14)
insert into tmp.univerzita ( nazev_univerzity, id_zeme)
values ('Hochevan university', 7)

go
insert into tmp.fakulta ( nazev_fakulty, id_univerzity)
values ('Teleinformatika', 53)
insert into tmp.fakulta ( nazev_fakulty, id_univerzity)
values ('Tělovýchova', 55)
insert into tmp.fakulta ( nazev_fakulty, id_univerzity)
values ('Bezpečnostní systémy', 56)
insert into tmp.fakulta ( nazev_fakulty, id_univerzity)
values ('Consuldecor', 57)

go
insert into tmp.resitel ( jemno, prijmeni, id_fakulty)
values ('Pepa', 'Hájek', 53)
insert into tmp.resitel ( jemno, prijmeni, id_fakulty)
values ('Matěj', 'Hladký', 54)
insert into tmp.resitel ( jemno, prijmeni, id_fakulty)
values ('Kuba', 'Rodny', 55)
insert into tmp.resitel ( jemno, prijmeni, id_fakulty)
values ('Švarc', 'Hagen', 56)

go
insert into tmp.projekt ( nazev_projektu, cil, popis, datum)
values ('hogofogo', 'zjistit jak na sebe vzájemně púsobí částice hogo fogo', 'nemluvte', '11.3.2011')
insert into tmp.projekt ( nazev_projektu, cil, popis, datum)
values ('kockopec', 'ziskejte novy druh','pomoci mutace', '7.3.2000')
insert into tmp.projekt ( nazev_projektu, cil, popis, datum)
values ('příliv, odliv', 'ověřit působení měsice na moře', 'sledujte a zapisujte působení měsíce na moře', '8.7.2005')

go
insert into tmp.prac_skupina ( nazev, id_resitele)
values ('Hvězdy', 22)
insert into tmp.prac_skupina ( nazev, id_resitele)
values ('Kapři', 23)
insert into tmp.prac_skupina ( nazev, id_resitele)
values ('Azmahu', 24)

go
insert into tmp.osoba ( jmeno, prijmeni, id_fakulty)
values ('Anička', 'Kuželová', 53 )
insert into tmp.osoba ( jmeno, prijmeni, id_fakulty)
values ('Pepa', 'Vojtel', 54)
insert into tmp.osoba ( jmeno, prijmeni, id_fakulty)
values ('Antonín', 'Kučera', 55)
insert into tmp.osoba ( jmeno, prijmeni, id_fakulty)
values ('Lukáš', 'Poláček', 56)
insert into tmp.osoba ( jmeno, prijmeni, id_fakulty)
values ('Lenka', 'Holá', 53)
insert into tmp.osoba ( jmeno, prijmeni, id_fakulty)
values ('Anetka', 'Rychlá', 60)


go
insert into tmp.osoba_na_aktivite ( kapacita, id_osoba, id_zdroj, id_sazba)
values (5, 80, 1, 8)
insert into tmp.osoba_na_aktivite ( kapacita, id_osoba, id_zdroj, id_sazba)
values (2, 77, 2, 2)
insert into tmp.osoba_na_aktivite ( kapacita, id_osoba, id_zdroj, id_sazba)
values (3, 75, 3, 4)
insert into tmp.osoba_na_aktivite ( kapacita, id_osoba, id_zdroj, id_sazba)
values (4, 76, 4, 3)


go -------------- POHLED -----------------
CREATE VIEW Přehled_osob AS
select * from tmp.osoba
go

select * from Přehled_osob


go
CREATE VIEW osoby2 AS
select distinct o.jmeno,o.prijmeni,f.nazev_fakulty from tmp.osoba o, tmp.fakulta f
where o.id_fakulty = f.id_fakulty
go

select * from osoby2


go
CREATE VIEW prehled AS
select distinct o.jmeno,o.prijmeni,a.kapacita  from tmp.osoba o, tmp.osoba_na_aktivite a
where a.id_osoba = o.id_osoba
go

select * from prehled


go
CREATE VIEW prehled4 AS
select distinct o.jmeno,o.prijmeni,f.nazev_fakulty,u.nazev_univerzity from tmp.osoba o, tmp.fakulta f, tmp.univerzita u
where o.id_fakulty = f.id_fakulty or u.id_univerzity = f.id_univerzity
go

select * from prehled4


go --------------- KURZOR ----------------- spouští se celý
declare Kurzor cursor for
 select wp.popis, wp.doba_realizace as doba  from tmp.WP wp, tmp.projekt pr
  where pr.id_projektu = 1 and pr.id_projektu = wp.id_wp

  declare
  @popis varchar (50),
  @doba_realizace varchar (10)

  open kurzor
  fetch Kurzor into @popis, @doba_realizace
   while @@FETCH_STATUS = 0
    begin
     print 'Popis procesu: '+@popis
     print 'Doba realizace: '+@doba_realizace
     print ''
     fetch Kurzor into @popis,@doba_realizace
    end
   close kurzor
  deallocate Kurzor


go ------------ TRIGGER ---------------
create table tmp.archiv_osoba
(
id_archiv_osoba int identity(1,1) primary key,
jmeno varchar (50),
prijmeni varchar (50),
id_fakulty int foreign key references tmp.fakulta (id_fakulty),
zmena int
)

go
CREATE TRIGGER zmena_jmena on tmp.osoba
for update
as
begin
 declare
  @puvodni int,
  @novy int,
  @zmena int,
  @jmeno int,
  @prijmeni int

 select @puvodni = jmeno from deleted
 select @novy = jmeno from inserted
 select @jmeno = jmeno from inserted

 set @zmena = @novy - @puvodni

 insert into tmp.archiv_osoba  (jmeno, zmena)
 values (@jmeno, @zmena)
end



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

go
CREATE TRIGGER smaz_projekt on tmp.projekt
for delete

as
begin
 declare @nazev_projektu varchar (50)
 set @nazev_projektu = (select id_projektu from deleted)
 delete from tmp.projekt where id_projektu = @nazev_projektu
end

begin tran smaz_projekt5
delete from tmp.projekt where id_projektu = 1
select * from tmp.projekt
rollback tran smaz_projekt
go


go ---------------- PROCEDURA ------------- spouští se EXEC
CREATE PROC add_osoba
@jmeno varchar (50),
@prijmeni varchar (50)
as
begin
 insert into tmp.osoba(jmeno, prijmeni)
 values (@jmeno, @prijmeni)
END

EXEC add_osoba 'Martin', 'Hustý'

select * from tmp.osoba


go
CREATE PROC add_zeme
@nazev_zeme varchar (100),
@skratka_zeme varchar (80),
@EU bit
as
begin
 insert into tmp.zeme(nazev_zeme, skratka_zeme, EU)
 values (@nazev_zeme, @skratka_zeme, @EU)
END

EXEC add_zeme 'Rusko', 'RU', '1'

select * from tmp.zeme


go
CREATE PROC  add_cestovne
@pocet_osob int,
@naklady int
as
begin
 insert into tmp.cestovne(pocet_osob, naklady)
 values (@pocet_osob, @naklady)
END

EXEC add_cestovne 100, 2500

select * from tmp.cestovne