/*
To je MSSQLServer SQL script za 
	1. kreiranje baze podataka na konkretno mesto (u konkretan, umesto default foldera),
	2. generisanje tabela sa potrebnim primarnim i spoljnim ključevima
		ZA PRETHODNO GENERISANU MS SQLServer bazu podataka sa ciljem smeštaja korisničkih podataka za analizu
		za case study Osiguravanje
	3. unošenje podataka u pojedine tabele iz pripremljenih .txt flat fileova

 Izvršavanje svih SQL naredbi se može pokrenuti pritiskom na f5

 Pre pokretanja NEOPHONO JE unositi/ispraviti naziv baze podataka
 koja se kreirati i u koju se žele generisati tabele, a na kraju unositi
 pripremljeni podaci!!!


*/


/*	1. Kreiranje baze podataka na konkretno mesto umesto default foldera

Default folder je:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

*/


create database DW_Osiguranje							--naziv BP
	on ( name = DW_Osiguranje,							--logički naziv BP
		filename = 'd:\Osiguranje\DW_Osiguranje.mdf');	--path za fizičko smeštanje
	go




/*	2. Generisanje tabela sa potrebnim primarnim i spoljnim ključevima
		ZA PRETHODNO GENERISANU MS SQLServer bazu podataka

	Upisati naziv prethodno kreirane, tj.POSTOJEĆE MS SQLServer baze podataka za case study Osiguranje
*/



USE [DW_Osiguranje]
GO
/****** Object:  Table [dbo].[dim_klijent]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_klijent](
	[ID_klijenta] [smallint] NOT NULL,
	[naziv_vrste_klijen] [char](15) NULL,
	[JMBG] [char](13) NULL,
	[ime] [char](12) NULL,
	[prezime] [char](20) NULL,
	[datum_rodjenja] [datetime] NULL,
	[pol] [char](1) NULL,
	[strucna_sprema] [char](3) NULL,
	[zanimanje] [char](25) NULL,
	[adresa_klijenta] [varchar](30) NULL,
	[PIB] [char](12) NULL,
	[naziv_preduzeca] [char](20) NULL,
	[sediste_preduzeca] [char](25) NULL,
	[sifra_delatnosti] [smallint] NULL,
	[naziv_delatnosti] [char](30) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID_klijenta] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dim_organizaciona_jedinica]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_organizaciona_jedinica](
	[ID_ekspoziture] [smallint] NOT NULL,
	[naziv_ekspoziture] [char](25) NULL,
	[sifra_glavne_filij] [char](3) NULL,
	[naziv_glavne_filij] [char](25) NULL,
	[sifra_filijale] [char](3) NULL,
	[naziv_filijale] [char](25) NULL,
	[mesto] [char](25) NULL,
	[opstina] [char](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID_ekspoziture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dim_radnik]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_radnik](
	[ID_radnika] [smallint] NOT NULL,
	[sifra_radnika] [char](6) NULL,
	[JMBG] [char](13) NULL,
	[ime] [char](15) NULL,
	[prezime] [char](20) NULL,
	[datum_rodjenja] [datetime] NULL,
	[pol] [char](1) NULL,
	[strucna_sprema] [char](3) NULL,
	[zanimanje] [char](15) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID_radnika] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dim_vozilo]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_vozilo](
	[ID_vozila] [smallint] NOT NULL,
	[broj_sasije] [char](18) NULL,
	[registarski_broj] [char](9) NULL,
	[tip_vozila] [char](20) NULL,
	[marka_vozila] [char](20) NULL,
	[model_vozila] [char](20) NULL,
	[zapremina_motora] [char](4) NULL,
	[snaga_motora] [char](3) NULL,
	[boja_vozila] [char](20) NULL,
	[vrsta_boje] [char](10) NULL,
	[vrsta_goriva] [char](10) NULL,
	[godina_proizvodnje] [smallint] NULL,
PRIMARY KEY CLUSTERED 
(
	[ID_vozila] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dim_vreme]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_vreme](
	[datum] [datetime] NOT NULL,
	[naziv_dana] [char](10) NULL,
	[dan_u_nedelji] [smallint] NULL,
	[godina_nedelja] [char](10) NULL,
	[nedelja_u_godini] [smallint] NULL,
	[sifra_godina_mesec] [smallint] NULL,
	[naziv_meseca] [char](10) NULL,
	[godina_mesec] [char](10) NULL,
	[sifra_godina_kvartal] [char](2) NULL,
	[naziv_kvartala] [char](10) NULL,
	[godina_kvartal] [char](10) NULL,
	[godina] [smallint] NULL,
	[sifra_naziv_meseca] [int] NULL,
	[sifra_naziv_kvartala] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[datum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[dim_vrsta_polise]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_vrsta_polise](
	[ID_vrste_polise] [smallint] NOT NULL,
	[naziv_vrste_polise] [char](15) NULL,
	[sifra_grupe_polise] [char](2) NULL,
	[naziv_grupe_polise] [char](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID_vrste_polise] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[fact_isplata_po_po]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[fact_isplata_po_po](
	[ID_isplate] [char](18) NOT NULL,
	[ID_ugovora] [smallint] NULL,
	[ID_radnika] [smallint] NULL,
	[procenjena_vrednos] [decimal](18, 2) NULL,
	[isplacen_iznos] [decimal](18, 2) NULL,
	[datum_nastanka_ste] [datetime] NULL,
	[datum_prijave_stet] [datetime] NULL,
	[datum_isplate_stet] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[ID_isplate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[fact_polisa_osigur]    Script Date: 2/27/2017 6:13:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[fact_polisa_osigur](
	[ID_ugovora] [smallint] NOT NULL,
	[ID_vrste_polise] [smallint] NULL,
	[ID_vozila] [smallint] NULL,
	[ID_klijenta] [smallint] NOT NULL,
	[ID_radnika] [smallint] NOT NULL,
	[ID_ekspoziture] [smallint] NULL,
	[datum_sklapanja_po] [datetime] NOT NULL,
	[datum_isteka_polis] [datetime] NOT NULL,
	[iznos_premije] [decimal](18, 2) NULL,
	[nacin_placanja] [char](10) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID_ugovora] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[fact_isplata_po_po]  WITH CHECK ADD FOREIGN KEY([datum_isplate_stet])
REFERENCES [dbo].[dim_vreme] ([datum])
GO
ALTER TABLE [dbo].[fact_isplata_po_po]  WITH CHECK ADD FOREIGN KEY([datum_prijave_stet])
REFERENCES [dbo].[dim_vreme] ([datum])
GO
ALTER TABLE [dbo].[fact_isplata_po_po]  WITH CHECK ADD FOREIGN KEY([datum_nastanka_ste])
REFERENCES [dbo].[dim_vreme] ([datum])
GO
ALTER TABLE [dbo].[fact_isplata_po_po]  WITH CHECK ADD FOREIGN KEY([ID_radnika])
REFERENCES [dbo].[dim_radnik] ([ID_radnika])
GO
ALTER TABLE [dbo].[fact_isplata_po_po]  WITH CHECK ADD FOREIGN KEY([ID_ugovora])
REFERENCES [dbo].[fact_polisa_osigur] ([ID_ugovora])
GO
ALTER TABLE [dbo].[fact_polisa_osigur]  WITH CHECK ADD FOREIGN KEY([datum_isteka_polis])
REFERENCES [dbo].[dim_vreme] ([datum])
GO
ALTER TABLE [dbo].[fact_polisa_osigur]  WITH CHECK ADD FOREIGN KEY([datum_sklapanja_po])
REFERENCES [dbo].[dim_vreme] ([datum])
GO
ALTER TABLE [dbo].[fact_polisa_osigur]  WITH CHECK ADD FOREIGN KEY([ID_ekspoziture])
REFERENCES [dbo].[dim_organizaciona_jedinica] ([ID_ekspoziture])
GO
ALTER TABLE [dbo].[fact_polisa_osigur]  WITH CHECK ADD FOREIGN KEY([ID_klijenta])
REFERENCES [dbo].[dim_klijent] ([ID_klijenta])
GO
ALTER TABLE [dbo].[fact_polisa_osigur]  WITH CHECK ADD FOREIGN KEY([ID_radnika])
REFERENCES [dbo].[dim_radnik] ([ID_radnika])
GO
ALTER TABLE [dbo].[fact_polisa_osigur]  WITH CHECK ADD FOREIGN KEY([ID_vozila])
REFERENCES [dbo].[dim_vozilo] ([ID_vozila])
GO
ALTER TABLE [dbo].[fact_polisa_osigur]  WITH CHECK ADD FOREIGN KEY([ID_vrste_polise])
REFERENCES [dbo].[dim_vrsta_polise] ([ID_vrste_polise])
GO
USE [master]
GO
ALTER DATABASE [DW_Osiguranje] SET  READ_WRITE 
GO





/*	3. Unošenje podataka u pojedine tabele iz pripremljenih .txt flat fileova
		za prethodno kreiranu MSSQLServer bazu podataka

	Upisati naziv prethodno kreirane, tj.POSTOJEĆE MS SQLServer baze podataka za case study Osiguranje
*/


USE [DW_Osiguranje]
GO

/****** Object-1:  Table [dbo].[dim_klijent]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT dim_klijent														--naziv tabele BP
FROM 'd:\Osiguranje\Podaci\TxtExp_dim_klijent.txt'		--path i naziv flat fajla sa podacima za punjenje
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')


/****** Object-2:  Table [dbo].[dim_organizaciona_jedinica]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT dim_organizaciona_jedinica
FROM 'd:\Osiguranje\Podaci\TxtExp_dim_organizaciona_jedinica.txt'
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')


/****** Object-3:  Table [dbo].[dim_radnik]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT dim_radnik
FROM 'd:\Osiguranje\Podaci\TxtExp_dim_radnik.txt'
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')


/****** Object-4:  Table [dbo].[dim_vozilo]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT dim_vozilo
FROM 'd:\Osiguranje\Podaci\TxtExp_dim_vozilo.txt'
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')


/****** Object-5:  Table [dbo].[dim_vreme]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT dim_vreme
FROM 'd:\Osiguranje\Podaci\TxtExp_dim_vreme.txt'
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')


/****** Object-6:  Table [dbo].[dim_vrsta_polise]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT dim_vrsta_polise
FROM 'd:\Osiguranje\Podaci\TxtExp_dim_vrsta_polise.txt'
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')


/****** Object-7:  Table [dbo].[fact_polisa_osigur]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT fact_polisa_osigur
FROM 'd:\Osiguranje\Podaci\TxtExp_fact_polisa_osigur.txt'
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')

/****** Object-8:  Table [dbo].[fact_isplata_po_po]    Script Date: 2/27/2017 6:13:59 PM ******/

BULK INSERT fact_isplata_po_po
FROM 'd:\Osiguranje\Podaci\TxtExp_fact_isplata_po_po.txt'
WITH	(
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n')


use master;
go