/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     11/4/2017 8:08:23 PM  

Rešenje prvog kolokvijuma -10-11-2017
                       */
/*==============================================================*/


/*
1.	Realizovati bazu podataka RezultatiStudenata sa gornjim
    tabelama na levoj strani modela. Baza podataka ne postoji. 
    Zabraniti bisanje ispitnog roka ako za njega postoji 
    evidentiran rezultat, a izmena vrednosti ključa ispitnog
    roka treba da se propagira u njegovoj tabeli-dete. 
    Brisanje studenta treba da izazove automatsko brisanje 
    njegovih rezultata, a izmena BrIndeksa treba da se dozvoli 
    samo u slučaju ako za njega nije povezana ni jedna ocena. 
    DatUpisaOcene ima predefinisanu/podrazumevanu vrednost 
    aktuelnog datuma, a Ocena ne sme imati vrednost veću od 10.
    DatZavrsIspRoka mora imati veću vrednost od vrednosti polja
    DatPocIspRoka. (4 poena)
*/

use master
drop database RezultatiStudenata;

create database RezultatiStudenata;
go

use RezultatiStudenata;
go

/*==============================================================*/
/* Table: STUDENT                                               */
/*==============================================================*/
create table STUDENT (
   BRINDEKSA            char(6)              not null,
   IMESTUD              varchar(15)          not null,
   PREZIMESTUD          varchar(15)          not null,
   DATRODJSTUD          date             not null,
   constraint SifStud primary key nonclustered (BRINDEKSA)
)
go

-- drop table dbo.ISPITNIROK;

/*==============================================================*/
/* Table: ISPITNIROK                                            */
/*==============================================================*/
create table ISPITNIROK (
   SKOLSKAGOD           char(5)              not null,
   ISPROK               smallint             not null,
   DATPOCISPROKA        datetime             not null,
   DATZAVRSISPROKA      datetime             not null,
   constraint SifIspRoka primary key nonclustered (SKOLSKAGOD, ISPROK),
   constraint uc_KontrPocKrajIspRoka 
	check (datzavrsisproka>datpocisproka)
);
go


/*==============================================================*/
/* Table: STUDIJSKIPROGRAM                                      */
/*==============================================================*/
create table STUDIJSKIPROGRAM (
   SIFSTUDPROGRAMA      smallint             not null,
   NAZIVSTUDPROGRAMA    varchar(50)          not null,
   GODUVODJSTUDPROGR    numeric(4,0)         not null,
   GODVAZENJASTUDPROG   decimal(4,0)         not null,
   constraint SifStPrg primary key nonclustered (SIFSTUDPROGRAMA)
)
go


/*==============================================================*/
/* Table: PREDMET                                               */
/*==============================================================*/
create table PREDMET (
   SIFSTUDPROGRAMA      smallint             not null,
   SIFPREDMETA          int                  not null,
   NAZIVPREDMETA        varchar(30)          not null,
   SEMESTAR             smallint             not null,
   BRCASNASTNEDELJNO    smallint             not null,
   BRCASLABNEDELJNO     smallint             null,
   constraint SifPredm primary key 
		nonclustered (SIFSTUDPROGRAMA, SIFPREDMETA),
   constraint Sadrzi foreign key (SIFSTUDPROGRAMA)
      references STUDIJSKIPROGRAM (SIFSTUDPROGRAMA)
)
go


/*==============================================================*/
/* Table: REZULTAT                                              */
/*==============================================================*/
create table REZULTAT (
   BRINDEKSA            char(6)              not null,
   SKOLSKAGOD           char(5)              not null,
   ISPROK               smallint             not null,
   SIFSTUDPROGRAMA      smallint             not null,
   SIFPREDMETA          int                  not null,
   OCENA                numeric(2,0)         not null
		constraint uc_OgrMaksVrednOcene check (ocena<=10),
   DATUPISAOCENE        datetime             not null 
		default getdate(),
   constraint SifRezultata primary 
		key nonclustered (BRINDEKSA, SKOLSKAGOD, ISPROK, SIFSTUDPROGRAMA, SIFPREDMETA),
   constraint Polaze foreign key (BRINDEKSA)
      references STUDENT (BRINDEKSA)
      on delete cascade on update no action,
   constraint OcenaUIspRoku foreign key (SKOLSKAGOD, ISPROK)
      references ISPITNIROK (SKOLSKAGOD, ISPROK)
      on delete no action on update cascade,
   constraint OcenaZaPredmet foreign key (SIFSTUDPROGRAMA, SIFPREDMETA)
      references PREDMET (SIFSTUDPROGRAMA, SIFPREDMETA)
)
go

/*
2./Ubacite novu alfanumeričku kolonu (polje) dužine 1 sa nazivom 
	Pol u tabelu Student, za koju je podrazumevana vrednost ’M’. 
	Kolona treba da prima sledeće vrednosti: ’M’, ’m’, ’Z’ i ’z’. 
	(1 poen).
*/

--Resenje br.1
alter table dbo.STUDENT
	add Pol char(1) default 'F',
	constraint ck_VrednostiZaPol check (Pol in ('M','m','Z','z'));
go


--Resenje br.2
alter table dbo.STUDENT
	add Pol char(1) default 'F',
	constraint ck_VrednostiZaPol 
	check (Pol='M' or pol='m' or pOl='Z' or poL='z');
go

-- Ne moze se definisati novi def. constraint na kolonu
-- koja vec ima def.vrednost
alter table dbo.STUDENT
	add constraint DefZaPol
	default 'z' for pol;
go 

/*
3./Modifikujte kolonu DatRodjStud u tabeli student da bude 
	tipa datum-vreme bez obaveze popunjavanja. (1 poen).
*/

alter table dbo.STUDENT
	alter column DATRODJSTUD datetime null;
go


alter table dbo.STUDENT
	alter column DATRODJSTUD date null,
	alter column imestud varchar(16;
go



/*
4./Dodajte naknadno ograničenje za kolonu Ocena sa nazivom 
	uc_donjagranicaocene: najmanja vrednost kolone Ocena je 5. 
	(1 poen).
*/

alter table dbo.REZULTAT
	add constraint uc_donjagranicaocene
	check (OCENA>=5);
go


/*
5./Modifikujte ograničenje za najveću vrednost kolone Ocena 
	sa 10 na 9. (1 poen).
*/

alter table dbo.REZULTAT
	drop constraint uc_OgrMaksVrednOcene;
go

alter table dbo.REZULTAT
	add constraint uc_OgrMaksVrednOcene check (OCENA <= 9);
go

-- Proba dodavanje novog ogranicenja za istu kolonu tabele
alter table dbo.REZULTAT
	add constraint uc_MaksOcena check (OCENA <= 10);
go


/*
6./Popunite generisane tabele (one na levoj strani modela) 
	sa vrednostima (po jedan red za svaku tabelu, pri čemu 
	uzeti SifStudPrograma=1 i SifPredmeta=1). 
	Referencijalni integritet ne sme biti oštećen! 
	Pripazite i na to da su tabele možda izmenjene od trenutka 
	njihovog generisanja! (2 poena).
*/

insert dbo.STUDENT
	values ('E-495','Marko','Marković','1994.aug.08','M');
go
delete dbo.STUDENT
select * from dbo.STUDENT


insert dbo.ISPITNIROK
	values ('17/18',1,'2017-10-25','2017-11-03');
go

select * from dbo.ISPITNIROK	

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-pocetak*/
insert dbo.STUDIJSKIPROGRAM
	values (1,'Studijski program-1',2014,2021);
go

insert dbo.PREDMET
	values (1,1,'Baze podataka',3,2,2);
go

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-pocetak*/

insert dbo.REZULTAT
	values ('E-495','17/18',1,1,1,9,default);
go

select * from dbo.REZULTAT

delete dbo.REZULTAT

insert dbo.REZULTAT (BRINDEKSA,SKOLSKAGOD,ISPROK,SIFSTUDPROGRAMA,
			SIFPREDMETA,OCENA)
	values ('E-495','17/18',1,1,1,9);
go

/*
7./Upišite novi red u tabelu Rezultat sa ocenom 10, i to 
	bez brisanja i/ili modifikacije postojećih ograničenja! 
	Šta je uslov za upis novog reda u tabelu Rezultat? (2 poena)
*/

/* 
	Za upis novog reda u tabelu Rezultat zahteva 
	a) upis novog reda u tabelu Student
	ILI/I
	b) upis novog reda u tabeli Ispitnirok
	ILI/I
	c) upis novog reda u tabelu Predmet
*/

insert dbo.STUDENT
values ('E-494','Milovan','Ilic Minimaks','1938-11-05','M');
go
alter table dbo.REZULTAT
	nocheck constraint uc_OgrMaksVrednOcene;
insert dbo.REZULTAT
	values ('E-494','17/18',1,1,1,10,default);
go	
alter table dbo.REZULTAT
	check constraint uc_OgrMaksVrednOcene;
go

select * from dbo.REZULTAT	

/*
8./Izbrišite redove iz tabele Rezultat koji sadrže vrednost 
	ocene 10! (1 poen)
*/

delete dbo.REZULTAT
	where OCENA=10;
go

/*
9./Izmenite vrednost datuma na aktuelni datum, a ocenu na 10 
	u redovima tabele Rezultat u kojima je vrednost ocene 9! 
	(1 poen)
*/

alter table dbo.REZULTAT
	nocheck constraint uc_OgrMaksVrednOcene;
go

update dbo.REZULTAT
	set DATUPISAOCENE=GETDATE(),
		OCENA=10
	where OCENA=9;
go

alter table dbo.REZULTAT
	check constraint uc_OgrMaksVrednOcene;
	
/*
10./Generišite pogled sa nazivom Pogled_Student sa sledećim 
	poljima (redosled kolona je takođe bitan): Prezime Studenta, 
	Ime Studenta, Broj Indeksa, Datum Rođenja Studenta. (1 poen)
*/

create view Pogled_Student("Prezime Studenta","Ime Studenta",
				"Broj indeksa","Datum Rođenja Studenta") as
	select PREZIMESTUD,IMESTUD,BRINDEKSA,DATRODJSTUD 
		from  dbo.STUDENT;
go

select * from Pogled_Student;