/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     10/12/2015 2:11:05 PM                        */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('NARUDZBA') and o.name = 'FK_NARUDZBA_NARUCUJE_KUPAC')
alter table NARUDZBA
   drop constraint FK_NARUDZBA_NARUCUJE_KUPAC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('STAVKA_NARUDZBE') and o.name = 'FK_STAVKA_N_NARUDZBI__NARUDZBA')
alter table STAVKA_NARUDZBE
   drop constraint FK_STAVKA_N_NARUDZBI__NARUDZBA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('STAVKA_NARUDZBE') and o.name = 'FK_STAVKA_N_ROBA_UCES_ROBA_CEN')
alter table STAVKA_NARUDZBE
   drop constraint FK_STAVKA_N_ROBA_UCES_ROBA_CEN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('KUPAC')
            and   type = 'U')
   drop table KUPAC
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('NARUDZBA')
            and   name  = 'NARUCUJE_FK'
            and   indid > 0
            and   indid < 255)
   drop index NARUDZBA.NARUCUJE_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('NARUDZBA')
            and   type = 'U')
   drop table NARUDZBA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ROBA_CENOVNIK')
            and   type = 'U')
   drop table ROBA_CENOVNIK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('STAVKA_NARUDZBE')
            and   name  = 'ROBA_UCESTVUJE_U_STAVKI_NAR_FK'
            and   indid > 0
            and   indid < 255)
   drop index STAVKA_NARUDZBE.ROBA_UCESTVUJE_U_STAVKI_NAR_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('STAVKA_NARUDZBE')
            and   name  = 'NARUDZBI_PRIPADA_STAVKA_FK'
            and   indid > 0
            and   indid < 255)
   drop index STAVKA_NARUDZBE.NARUDZBI_PRIPADA_STAVKA_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('STAVKA_NARUDZBE')
            and   type = 'U')
   drop table STAVKA_NARUDZBE
go

/*==============================================================*/
/* Table: KUPAC                                                 */
/*==============================================================*/
create table KUPAC (
   SIF_KUPCA            smallint             not null,
   NAZ_KUPCA            varchar(30)          null,
   POST_BROJ            numeric(5)           null,
   ADRESA               varchar(20)          null,
   constraint PK_KUPAC primary key nonclustered (SIF_KUPCA)
)
go

/*==============================================================*/
/* Table: NARUDZBA                                              */
/*==============================================================*/
create table NARUDZBA (
   BR_NARUDZBE          smallint             not null,
   SIF_KUPCA            smallint             not null,
   DATUM_NARUDZBE       datetime             null,
   constraint PK_NARUDZBA primary key nonclustered (BR_NARUDZBE)
)
go

/*==============================================================*/
/* Index: NARUCUJE_FK                                           */
/*==============================================================*/
create index NARUCUJE_FK on NARUDZBA (
SIF_KUPCA ASC
)
go

/*==============================================================*/
/* Table: ROBA_CENOVNIK                                         */
/*==============================================================*/
create table ROBA_CENOVNIK (
   SIF_ROBE             smallint             not null,
   JED_MERE             char(10)             not null,
   NAZIV_ROBE           varchar(20)          null,
   JED_CENA             numeric(15,2)        null,
   constraint PK_ROBA_CENOVNIK primary key nonclustered (SIF_ROBE, JED_MERE)
)
go

/*==============================================================*/
/* Table: STAVKA_NARUDZBE                                       */
/*==============================================================*/
create table STAVKA_NARUDZBE (
   SIF_ROBE             smallint             not null,
   JED_MERE             char(10)             not null,
   BR_NARUDZBE          smallint             not null,
   NARUCENA_KOL         numeric(12,2)        null,
   constraint PK_STAVKA_NARUDZBE primary key nonclustered (SIF_ROBE, JED_MERE, BR_NARUDZBE)
)
go

/*==============================================================*/
/* Index: NARUDZBI_PRIPADA_STAVKA_FK                            */
/*==============================================================*/
create index NARUDZBI_PRIPADA_STAVKA_FK on STAVKA_NARUDZBE (
BR_NARUDZBE ASC
)
go

/*==============================================================*/
/* Index: ROBA_UCESTVUJE_U_STAVKI_NAR_FK                        */
/*==============================================================*/
create index ROBA_UCESTVUJE_U_STAVKI_NAR_FK on STAVKA_NARUDZBE (
SIF_ROBE ASC,
JED_MERE ASC
)
go

alter table NARUDZBA
   add constraint FK_NARUDZBA_NARUCUJE_KUPAC foreign key (SIF_KUPCA)
      references KUPAC (SIF_KUPCA)
go

alter table STAVKA_NARUDZBE
   add constraint FK_STAVKA_N_NARUDZBI__NARUDZBA foreign key (BR_NARUDZBE)
      references NARUDZBA (BR_NARUDZBE)
go

alter table STAVKA_NARUDZBE
   add constraint FK_STAVKA_N_ROBA_UCES_ROBA_CEN foreign key (SIF_ROBE, JED_MERE)
      references ROBA_CENOVNIK (SIF_ROBE, JED_MERE)
go