DBS-Projekt

create database projekt
go

use projekt
go

create table stat (
zkratka_stat varchar(10) primary key,
nazev varchar(70),
eu bit,
)

create table univerzita (
zkratka_un varchar(10) primary key,
nazev varchar(70),
zkratka_stat varchar(10) foreign key references stat (zkratka_stat)
)

create table fakulta (
zkratka_fak varchar(10) primary key,
zkratka_un varchar(10) foreign key references univerzita (zkratka_un),
nazev varchar(70),
)

create table pozice(
nazev varchar(30) primary key not null
)

create table zamestnanec(
id_zam int identity(1,1) primary key,
jmeno varchar(30),
prijmeni varchar (50),
zkratka_fak varchar(10) foreign key references fakulta (zkratka_fak),
pozice varchar(30) foreign key references pozice (nazev)
)

create table skupina (
id_skupiny int identity (1,1) primary key,
id_resitele int foreign key references zamestnanec (id_zam) unique
)


create table projekt (
id_projektu int identity(1,1) primary key,
cil varchar(100),
popis varchar(200),
nazev varchar(50),
id_skupiny int foreign key references skupina (id_skupiny) unique
)


create table skupina_zam (
id_skupiny int,
id_zam int unique,
constraint PK_skupina_zam primary key (id_skupiny, id_zam),
constraint FK_skupinazam_skupina foreign key (id_skupiny) references skupina (id_skupiny),
constraint FK_skupinazam_zam foreign key (id_zam) references zamestnanec (id_zam)
)

create table neprime_naklady (
id_neprime int identity(1,1) primary key,
nazev varchar(50),
pocet_ks int not null default 1,
cena int not null default 0,
cena_celkem as (pocet_ks * cena)
)


create table prime_naklady (
id_prime int identity(1,1) primary key,
cena_celkem int
)

create table majetek (
id_majetek int identity(1,1) primary key,
id_prime int foreign key references prime_naklady (id_prime),
nazev varchar(50),
pocet_ks int default 1,
cena int default 0,
celkem_majetek as (pocet_ks*cena)
)

create table mzdy (
id_mzdy int identity(1,1) primary key,
id_prime int foreign key references prime_naklady (id_prime),
zkratka_un varchar(10) foreign key references univerzita (zkratka_un),
pocet_osob int not null,
pocet_dni int not null,
sazba int not null,
celkem_mzdy as (pocet_osob*pocet_dni*sazba*8)
)


create table cestovne (
id_cesty int identity(1,1) primary key,
id_prime int foreign key references prime_naklady (id_prime),
zkratka_un varchar(10) foreign key references univerzita (zkratka_un),
pocet_osob int not null,
cil_cesty varchar(10) foreign key references stat (zkratka_stat),
cena int default 0
)


create table zdroje (
id_zdroje int identity(1,1) primary key,
id_prime int foreign key references prime_naklady (id_prime)
)

create table rizika (
id_rizika int identity (1,1) primary key,
popis varchar(200)
)

create table aktivity (
id_aktivity int identity(1,1) primary key,
zkratka_un varchar(10) foreign key references univerzita (zkratka_un),
popis varchar(200),
id_zdroje int foreign key references zdroje (id_zdroje),
cena_celkem int
)

create table osoby_na_aktivite (
id_aktivity int,
id_zam int,
constraint PK_osobynaaktivite primary key (id_aktivity,id_zam),
constraint FK_osobynaaktivite_aktivita foreign key (id_aktivity) references aktivity (id_aktivity),
constraint FK_osobynaaktivite_zamestnanec foreign key (id_zam) references zamestnanec (id_zam)
)

create table univerzity_na_projektu (
id_projektu int,
zkratka_un varchar(10),
constraint PK_univerzitynaprojektu primary key (id_projektu, zkratka_un),
constraint FK_univerzitynaprojektu_projekt foreign key (id_projektu) references projekt (id_projektu),
constraint FK_univerzitynaprojektu_univerzity foreign key (zkratka_un) references univerzita (zkratka_un)
)

create table proces (
id_procesu int identity(1,1) primary key,
id_projektu int foreign key references projekt (id_projektu),
nazev varchar(40),
popis varchar(200),
doba_v_mes varchar(10) default '1-6',
)

create table rizika_u_procesu (
id_rizika int,
id_procesu int,
constraint PK_rizikauprocesu primary key (id_rizika,id_procesu),
constraint FK_rizikauprocesu_rizika foreign key (id_rizika) references rizika (id_rizika),
constraint FK_rizikauprocesu_proces foreign key (id_procesu) references proces (id_procesu)
)

create table aktivity_u_procesu (
id_procesu int,
id_aktivity int,
constraint PK_aktivityuprocesu primary key (id_procesu,id_aktivity),
constraint FK_aktivityuprocesu_proces foreign key (id_procesu) references proces (id_procesu),
constraint FK_aktivityuprocesu_aktivity foreign key (id_aktivity) references aktivity (id_aktivity)
)

create table neprime_u_zdroje (
id_zdroje int,
id_neprime int,
constraint PK_neprimeuzdroje primary key (id_zdroje,id_neprime),
constraint FK_neprimeuzdroje_zdroj foreign key (id_zdroje) references zdroje (id_zdroje),
constraint FK_neprimeuzdroje_neprimenaklady foreign key (id_neprime) references neprime_naklady (id_neprime)
)

--------------------naplneni daty-------------------------
use projekt
go


--tabulka stat
insert into stat (zkratka_stat,nazev,eu)
values ('CZ','Èeská republika',1)
insert into stat (zkratka_stat,nazev,eu)
values ('GE','Nìmecko',1),('SK','Slovensko',1),('CHE','Švýcarsko',0),('UKR','Ukrajina',0),('BLR','Bìlorusko',0),('NOR','Norsko',0)

--tabulka univerzita
insert into univerzita (zkratka_un,nazev,zkratka_stat)
values ('VUT','Vysoké uèení technické v Brnì','CZ'),('NÌM','Nìmecké škola','GE'),('SLOV','Slovenská škola','SK'),('Švýc','Švýcarská škola','CHE'),('Ukra','Ukrajinská škola','UKR'),('Bìl','Bìloruská škola','BLR'),('Nor','Norská škola','NOR')

--tabulka fakulta
insert into fakulta (zkratka_fak,zkratka_un,nazev)
values ('FP','VUT','Fakulta podnikatelská')
insert into fakulta (zkratka_fak,zkratka_un,nazev)
values ('FANÌM','NÌM','Fakulta nìmecká')
insert into fakulta (zkratka_fak,zkratka_un,nazev)
values ('FASLOV','SLOV','Fakulta slovenská')
insert into fakulta (zkratka_fak,zkratka_un,nazev)
values ('Fašvýc','Švýc','Fakulta švýcarská')
insert into fakulta (zkratka_fak,zkratka_un,nazev)
values ('Faukra','Ukra','Fakulta ukrajinská')
insert into fakulta (zkratka_fak,zkratka_un,nazev)
values ('Fabìl','Bìl','Fakulta bìloruská')
insert into fakulta (zkratka_fak,zkratka_un,nazev)
values ('Fanor','Nor','Fakulta norská')

--tabulka pozice
insert into pozice (nazev)
values ('manager'),('technik1'),('administrace'),('technik2')

--tabulka zamestnanec
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Uè','Uèiteloviè','administrace','FP')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Krum','Páè','technik1','FP')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Dežo','Dorant','manager','faNìm')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Zdislav','Uzdichcal','manager','FANìm')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Chcaladorini','Kapaloto','technik1','FASlov')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Tonejá','Tomojeshauma','technik2','FASlov')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Jemito','Šumafuk','administrace','FAŠvýc')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Naštván','Kulemaházi','technik2','FAŠvýc')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Chcaljak','Stochlapov','technik1','FAUkra')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Vypiee','Pivson','manager','FAUkra')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Nasraláh','Ovna','administrace','FABìl')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Stojan','Jakotiè','manager','FABìl')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Maho','Jakobiè','technik2','FANor')
insert into zamestnanec (jmeno,prijmeni,pozice,zkratka_fak)
values ('Milorad','Mrdak','technik1','FANor')

--tabulka skupina
insert into skupina (id_resitele)
values (1),(3)

--tabulka projekt
insert into projekt (cil,popis,nazev,id_skupiny)
values ('Cilem projektu je projekt dokoncit','Budeme popisovat popis, až to popíšem','Projekt jedna',1),('Cilem druheho je druhy cil','Popis popíšem','Projekt dva',2)

--tabulka zamìstnanci ve skupinì (skupina_zam)
insert into skupina_zam (id_skupiny,id_zam)
values (1,1),(1,2),(2,3),(2,4)

--neprime naklady
insert into neprime_naklady (nazev,pocet_ks,cena)
values ('Hromada papiru',1,1150),('Toner',2,620)

--prime naklady
insert into prime_naklady (cena_celkem)
values (0),(0),(0),(0),(0),(0),(0)

--majetek
insert into majetek (id_prime,nazev,pocet_ks,cena)
values (1,'stùl',2,1050),(1,'PC',5,11200)

--mzdy
insert into mzdy (id_prime,zkratka_un,pocet_osob,pocet_dni,sazba)
values (1,'VUT',3,2,150),(1,'NÌM',2,5,130)

--cestovne
insert into cestovne (id_prime,zkratka_un,pocet_osob,cil_cesty)
values (1,'VUT',3,'GE')  --u EU do EU
insert into cestovne (id_prime,zkratka_un,pocet_osob,cil_cesty)
values (1,'VUT',2,'CHE') -- z EU do nonEU
insert into cestovne (id_prime,zkratka_un,pocet_osob,cil_cesty)
values (1,'Ukra',5,'BLR') -- nonEU do nonEU

--zdroje
insert into zdroje (id_prime)
values (1)

--rizika
insert into rizika (popis)
values ('Atomova valka'),('Konec sveta 2012'),('Apokalypsa'),('UFO')

-- aktivity
insert into aktivity (zkratka_un,popis,id_zdroje,cena_celkem)
values ('VUT','Aktivní aktivita',1,NULL),('VUT','Druhá aktivita',1,NULL)

-- osoby na aktivitì
insert into osoby_na_aktivite (id_aktivity,id_zam)
values (1,1),(1,2)

--univerzity na projektu
insert into univerzity_na_projektu (id_projektu,zkratka_un)
values (1,'VUT'),(1,'NÌM')
insert into univerzity_na_projektu (id_projektu,zkratka_un)
values (2,'Švýc'),(2,'Ukra')


--tabulka proces
insert into proces (id_projektu,nazev,popis,doba_v_mes)
values (1,'Proces cislo 1','Popis u procesu 1','1-6'),(2,'Proces cislo 2','Popis u procesu 2','7-12')
insert into proces (id_projektu,nazev,popis,doba_v_mes)
values (1,'Dalsi proces u projektu 1','Popis u procesu 3','1-6'),(2,'Dalsi proces u projektu 2','Popis u procesu 4','7-12')

-- rizika u procesu
insert into rizika_u_procesu (id_procesu,id_rizika)
values (1,2)
insert into rizika_u_procesu (id_procesu,id_rizika)
values (1,1),(1,3),(1,4)


--aktivity u procesu
insert into aktivity_u_procesu (id_procesu,id_aktivity)
values (1,1)

--neprime_u_zdroje
insert into neprime_u_zdroje (id_zdroje,id_neprime)
values (1,1)

-----------------------skripty a dotazy-------------------
use projekt
go

----------------------------------CESTOVNE----------------------------------------------------
begin tran ukol_jedna --zacatek transakce ukol jedna
create procedure cesty
@zkratka_un varchar(10),
@cil_cesty varchar(10),
@sazba_cesty int output
as
begin
declare @start bit
declare @cil bit
select @start=s.eu from stat s,cestovne c, univerzita u
  where @zkratka_un=c.zkratka_un and @cil_cesty=c.cil_cesty and c.zkratka_un=u.zkratka_un and u.zkratka_stat=s.zkratka_stat
select @cil=s.eu from stat s,cestovne c
  where @cil_cesty=c.cil_cesty and c.cil_cesty=s.zkratka_stat

 if (@start=1 and @cil=1)
   begin
   set @sazba_cesty=120
   print @sazba_cesty
   end
 else if (@start=1 and @cil=0)
   begin
   set @sazba_cesty=210
   print @sazba_cesty
   end
 else if (@start=0 and @cil=1)
   begin
   set @sazba_cesty=210
   print @sazba_cesty
   end
 else if (@start=0 and @cil=0)
   begin
   set @sazba_cesty=80
   print @sazba_cesty
   end
end

create trigger cestak on cestovne
for insert
as
begin
declare @id_prime int
declare @zkratka_un varchar(10)
declare @cil_cesty varchar(10)
declare @cestak int
declare @pocet_osob int
declare @sazba_cesty int

select @id_prime=id_prime from inserted
select @zkratka_un= zkratka_un from inserted
select @pocet_osob= pocet_osob from inserted
select @cil_cesty= cil_cesty from inserted

execute cesty @zkratka_un,@cil_cesty,@sazba_cesty output
set @cestak= @pocet_osob*@sazba_cesty
print @cestak

update cestovne set cena=@cestak where id_prime=@id_prime and zkratka_un=@zkratka_un and pocet_osob=@pocet_osob and cil_cesty=@cil_cesty
end




insert into cestovne (id_prime,zkratka_un,pocet_osob,cil_cesty)
values (1,'Ukra',2,'CHE')
select * from cestovne


rollback tran ukol_jedna --konec transakce k ukolu jedna
------------------------------------------------------------------------------------------------


---------------------------------ZDROJE U AKTIVITY-----------------------------------------------
-- vypoèítá pøímé náklady
begin tran ukol_jedna_dva --zacatek transakce
create procedure celk_nakl_vnitr
@id_prime int,
@celkem_nepr int
as
begin
declare @cestovne int
declare @majetek int
declare @mzdy int
declare @celkem_pr int
declare @celkem_zdroje int

set @cestovne= (select SUM(cena) from cestovne c, prime_naklady pn where pn.id_prime=c.id_prime)
set @majetek= (select SUM(celkem_majetek) from majetek m, prime_naklady pn where pn.id_prime=m.id_prime)
set @mzdy= (select SUM(celkem_mzdy) from mzdy mz, prime_naklady pn where pn.id_prime=mz.id_prime)

set @celkem_pr=@cestovne+@majetek+@mzdy
print 'Celkové pøímé výdaje jsou: '+convert(varchar(7),@celkem_pr)+', z toho '+convert(varchar(6),@cestovne)+' cestovne a '+convert(varchar(6),@majetek)+' majetek a '+convert(varchar(6),@mzdy)+' mzdy'

set @celkem_zdroje=@celkem_nepr+@celkem_pr
print 'Celkové náklady na aktivitu jsou: '+convert(varchar(7),@celkem_zdroje)+' pìnìz, z toho '+convert(varchar(6),@celkem_nepr)+' pìnìz za nepøímé náklady a '+convert(varchar(6),@celkem_pr)+' penìz za pøímé náklady'
end

--vypoèítá celkové náklady aktivity
create procedure celk_naklady
@id_zdroje int,
@celkem_nepr int output,
@id_prime int output
as
begin
set @celkem_nepr= (select SUM(cena_celkem) from neprime_naklady nn, neprime_u_zdroje nuz, zdroje z
                     where z.id_zdroje=nuz.id_zdroje and nuz.id_neprime=nn.id_neprime)
set @id_prime= (select id_prime from zdroje where id_zdroje=@id_zdroje)

print 'Celkové nepøímé výdaje jsou: '+convert(varchar(7),@celkem_nepr)+' penìz.'
execute celk_nakl_vnitr @id_prime, @celkem_nepr

end


declare @celkem_nepr int
declare @id_prime int
execute celk_naklady 1,@celkem_nepr,@id_prime
rollback tran ukol_jedna_dva

------------------------------------------------------------------------------------------------


----------------------------------smazání rizika a dat v navázané tabulce-----------------------
create trigger smazat_riziko on rizika_u_procesu
for delete
as
begin
declare @riziko int
set @riziko= (select id_rizika from deleted)
delete from rizika where id_rizika=@riziko
end


begin tran smazani_rizika
delete from rizika_u_procesu where id_rizika=1
select * from rizika
select * from rizika_u_procesu
rollback tran smazani_rizika

-------------------------------------------------------------------------------------------------

--------------------------------kurzor na procesy------------------------------------------------
declare Kurzor cursor for
 select p.nazev,p.popis,p.doba_v_mes as Doba from proces p, projekt pr
  where pr.id_projektu=1 and pr.id_projektu=p.id_projektu

 declare
 @nazev varchar(50),
 @popis varchar(200),
 @doba_v_mes varchar(10)

 open kurzor
 fetch Kurzor into @nazev,@popis,@doba_v_mes
  while @@FETCH_STATUS=0
   begin
   print 'Název procesu: '+@nazev
   print 'Popis procesu: '+@popis
   print 'Doba trvání: '+@doba_v_mes+ ' mìsícù'
   print ''
   fetch Kurzor into @nazev,@popis,@doba_v_mes
   end
 close kurzor
 deallocate Kurzor
 -------------------------------------------------------------------------------------------------

 -----------------------------Pohledy-------------------------------------------------------------

-- seznam zamìstnancù ve skupinì
 create view Zamestnanci_ve_skupine as
 select sz.id_skupiny as 'Èíslo skupiny', z.jmeno+' '+z.prijmeni as 'Jméno zamìstnance',z.zkratka_fak as 'Fakulta',z.pozice as 'Pozice'  from zamestnanec z, skupina_zam sz
  where sz.id_zam=z.id_zam
 go
select * from Zamestnanci_ve_skupine

-- seznam univerzit
create view Univerzity as
select u.nazev as 'Jméno univerzity',u.zkratka_un as 'Zkratka univerzity',s.nazev as 'Stát' from univerzita u, stat s
 where u.zkratka_stat=s.zkratka_stat
go
select * from univerzity

--univerzity na projektu
create view prehled_univerzit_na_projektu as
select p.nazev as 'Název projektu', u.nazev as 'Název univerzity', u.zkratka_un as 'Zkratka univerzity' from univerzita u, projekt p, univerzity_na_projektu up
where p.id_projektu=up.id_projektu and up.zkratka_un=u.zkratka_un
go
select * from prehled_univerzit_na_projektu