-- prvi kolokvijum - MUSTRA - 06-11-2014

create database Trgovački_centar;
go

use Trgovački_centar;
go

create table RADNIK (
   ID_RADNIKA           int                  not null	primary key,
   JMBG                 numeric(13)          not null,
   IME                  char(15)             not null,
   PREZIME              char(15)             not null,
   DATUM_RO_ENJA        datetime             not null,
   OTKAD_JE_NAS_RADNIK  datetime             not null,
   ADRESA_GRAD          char(15)             null,
   ADRESA_ULICA_BROJ    char(15)             null
)
go

create table ROBA (
   SIFRA_ROBE           int                  not null	primary key,
   NAZIV_ROBE           char(25)             not null,
   OPIS_ROBE            varchar(500)         null
)
go

create table RACUN (
   BROJ_RACUNA          int                  not null	primary key,
   ID_RADNIKA           int                  not null	foreign key references RADNIK (ID_RADNIKA),
   DATUM_RACUNA         datetime             not null,
   IZNOS_RACUNA         money                not null,
   ROK_ZA_PLACANJE      datetime             not null,
   LICE_ZA_VEZU         char(30)             null,
   TELEFON              char(20)             null
)
go

create table STAVKA_RACUNA (
   BROJ_RACUNA          int                  not null	foreign key references RACUN (BROJ_RACUNA),
   BROJ_STAVKE_RAC      int                  not null,
   SIFRA_ROBE           int                  not null	foreign key references ROBA (SIFRA_ROBE),
   KOLICINA             decimal(12,2)        not null,
   JEDINICA_MERE        char(15)             not null,
   CENA                 decimal(12,2)        not null,
   constraint PK_STAVKA_RACUNA primary key nonclustered (BROJ_RACUNA, BROJ_STAVKE_RAC)
)
go

alter table roba
	add Upotrebljiv_do date default getdate() check (Upotrebljiv_do>getdate());
	go
	
alter table racun
	alter column lice_za_vezu varchar(25) not null;
go

alter table stavka_racuna
add constraint uc_cena_kontrola
	check (cena>=10);
go

insert RADNIK
	values (1,1411955820030,'Imre', 'Petkovics','1955.11.14', '1993.09.01', 'Subotica', 'Rumska 17');
go
select * from RADNIK;
go

insert ROBA
	values (1,'Gyömbér','Erősítőszer','2016.02.15');
go
select * from ROBA;
go

insert RACUN
	values(1,1,GETDATE(),237.54,'2014.11.09','Petkovics Györgyi','+381656557498');
go
select * from RACUN;
go

insert STAVKA_RACUNA
	values(1,1,1,1,'Kg',890.00);
go
select * from STAVKA_RACUNA;
go

alter table stavka_racuna
	nocheck constraint uc_cena_kontrola;
go

update STAVKA_RACUNA
	set CENA=5;
go

alter table stavka_racuna
	check constraint uc_cena_kontrola;
go

update STAVKA_RACUNA
	set CENA=3;
go

delete STAVKA_RACUNA
	where CENA<10;
go

select * from STAVKA_RACUNA;
go

alter table stavka_racuna
drop constraint uc_cena_kontrola;
go

create view Radnik_pogled as
	(select prezime, ime, jmbg, id_radnika, adresa_grad
		from radnik);
go

select * from RADNIK_pogled;
go