/*============================================================================
  File:         instawdwdb.sql

  Summary:      Creates the AdventureWorks 2008R2 data warehouse sample database.

  Date:         June 14, 2008
  Updated:      March 28, 2012
============================================================================*/

-->> WARNING: THIS SCRIPT MUST BE RUN IN SQLCMD MODE INSIDE SQL SERVER MANAGEMENT STUDIO. <<--
:on error exit

/*
 * In order to run this script manually, either set the environment variables,
 * or uncomment the setvar statements and provide the necessary values if
 * the defaults are not correct for your installation.
 */


:setvar SqlSamplesDatabasePath   "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar SqlSamplesSourceDataPath "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorksData Warehouse\KAMIL\"

IF '$(SqlSamplesSourceDataPath)' IS NULL OR '$(SqlSamplesSourceDataPath)' = ''
BEGIN
    RAISERROR(N'The variable SqlSamplesSourceDataPath must be defined.', 16, 127) WITH NOWAIT;
    RETURN;
END;

IF '$(SqlSamplesDatabasePath)' IS NULL OR '$(SqlSamplesDatabasePath)' = ''
BEGIN
    RAISERROR(N'The variable SqlSamplesDatabasePath must be defined.', 16, 127) WITH NOWAIT;
    RETURN;
END;

SET NOCOUNT OFF;
GO

PRINT CONVERT(VARCHAR(1000), @@VERSION);
GO

USE [master];
GO

SET QUOTED_IDENTIFIER ON;

-- ****************************************
-- Drop Database
-- ****************************************
PRINT '';
PRINT '*** Dropping Database';
GO

IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'AdventureKamil')
    DROP DATABASE [AdventureKamil];
GO

-- ****************************************
-- Create Database
-- ****************************************
PRINT '';
PRINT '*** Creating Database';
GO

CREATE DATABASE [AdventureKamil] ON (NAME = 'AdventureKamil_Data',
    FILENAME = N'$(SqlSamplesDatabasePath)AdventureKamil_Data.mdf', SIZE = 64, FILEGROWTH = 4) LOG ON (NAME = 'AdventureKamil_Log',
    FILENAME = N'$(SqlSamplesDatabasePath)AdventureKamil_Log.LDF' , SIZE = 2, FILEGROWTH = 8);
GO

ALTER DATABASE AdventureKamil
SET RECOVERY SIMPLE,
    ANSI_NULLS ON,
    ANSI_PADDING ON,
    ANSI_WARNINGS ON,
    ARITHABORT ON,
    CONCAT_NULL_YIELDS_NULL ON,
    QUOTED_IDENTIFIER ON,
    NUMERIC_ROUNDABORT OFF,
    PAGE_VERIFY CHECKSUM,
    ALLOW_SNAPSHOT_ISOLATION ON;
GO

USE [AdventureKamil];
GO


-- ******************************************************
-- Create tables
-- ******************************************************
PRINT '';
PRINT '*** Creating Tables';
GO

CREATE TABLE [dbo].[DimCustomer] (
    [CustomerKey] [int] IDENTITY(1, 1) NOT NULL,
    [GeographyKey] [int] NULL,
    [CustomerAlternateKey] [nvarchar] (15) NOT NULL,
    [Title] [nvarchar] (8) NULL,
    [FirstName] [nvarchar] (50) NULL,
    [MiddleName] [nvarchar] (50) NULL,
    [LastName] [nvarchar] (50) NULL,
    [NameStyle] [bit] NULL,
    [BirthDate] [date] NULL,
    [MaritalStatus] [nchar] (1) NULL,
    [Suffix] [nvarchar] (10) NULL,
    [Gender] [nvarchar] (1) NULL,
    [EmailAddress] [nvarchar] (50) NULL,
    [YearlyIncome] [money] NULL,
    [TotalChildren] [tinyint] NULL,
    [NumberChildrenAtHome] [tinyint] NULL,
    [EnglishEducation] [nvarchar] (40) NULL,
    [SpanishEducation] [nvarchar] (40) NULL,
    [FrenchEducation] [nvarchar] (40) NULL,
    [EnglishOccupation] [nvarchar] (100) NULL,
    [SpanishOccupation] [nvarchar] (100) NULL,
    [FrenchOccupation] [nvarchar] (100) NULL,
    [HouseOwnerFlag] [nchar] (1) NULL,
    [NumberCarsOwned] [tinyint] NULL,
    [AddressLine1] [nvarchar] (120) NULL,
    [AddressLine2] [nvarchar] (120) NULL,
    [Phone] [nvarchar] (20) NULL,
    [DateFirstPurchase] [date] NULL,
    [CommuteDistance] [nvarchar] (15) NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[DimDate] (
    [DateKey] int NOT NULL,
    [FullDateAlternateKey] [date] NOT NULL,
    [DayNumberOfWeek] [tinyint] NOT NULL,
    [EnglishDayNameOfWeek] [nvarchar] (10) NOT NULL,
    [SpanishDayNameOfWeek] [nvarchar] (10) NOT NULL,
    [FrenchDayNameOfWeek] [nvarchar] (10) NOT NULL,
    [DayNumberOfMonth] [tinyint] NOT NULL,
    [DayNumberOfYear] [smallint] NOT NULL,
    [WeekNumberOfYear] [tinyint] NOT NULL,
    [EnglishMonthName] [nvarchar] (10) NOT NULL,
    [SpanishMonthName] [nvarchar] (10) NOT NULL,
    [FrenchMonthName] [nvarchar] (10) NOT NULL,
    [MonthNumberOfYear] [tinyint] NOT NULL,
    [CalendarQuarter] [tinyint] NOT NULL,
    [CalendarYear] [smallint] NOT NULL,
    [CalendarSemester] [tinyint] NOT NULL,
    [FiscalQuarter] [tinyint] NOT NULL,
    [FiscalYear] [smallint] NOT NULL,
    [FiscalSemester] [tinyint] NOT NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[DimSalesTerritory] (
    [SalesTerritoryKey] [int] IDENTITY(1, 1) NOT NULL,
    [SalesTerritoryAlternateKey] [int] NULL,
    [SalesTerritoryRegion] [nvarchar] (50) NOT NULL,
    [SalesTerritoryCountry] [nvarchar] (50) NOT NULL,
    [SalesTerritoryGroup] [nvarchar] (50) NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[FactRent] (
    [RentKey] [int] IDENTITY(1, 1) NOT NULL,
    [RoomKey] [int] NOT NULL,
    [CustomerKey] [int] NOT NULL,
    [OrderDateKey] [int] NOT NULL,
    [RentStartKey] [int] NOT NULL,
    [Length] [int] NOT NULL,
    [Price] [money] NOT NULL,
) ON [PRIMARY];

CREATE TABLE [dbo].[DimGeography] (
    [GeographyKey] [int] IDENTITY(1, 1) NOT NULL,
    [City] [nvarchar] (30) NULL,
    [StateProvinceCode] [nvarchar] (3) NULL,
    [StateProvinceName] [nvarchar] (50) NULL,
    [CountryRegionCode] [nvarchar] (3) NULL,
    [EnglishCountryRegionName] [nvarchar] (50) NULL,
    [SpanishCountryRegionName] [nvarchar] (50) NULL,
    [FrenchCountryRegionName] [nvarchar] (50) NULL,
    [PostalCode] [nvarchar] (15) NULL,
    [SalesTerritoryKey] [int] NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[DimRoom] (
    [RoomKey] [int] IDENTITY(1, 1) NOT NULL,
    [GeographyKey] [int] NULL,
    [HaveTv] [bit] NOT NULL,
    [HaveNet] [bit] NOT NULL,
    [HaveWasher] [bit] NOT NULL,
    [HaveCoffeeMachine] [bit] NOT NULL
) ON [PRIMARY];

GO

-- ******************************************************
-- Load data
-- ******************************************************
PRINT '';
PRINT '*** Loading Data';
GO

PRINT 'Loading [AdventureKamil].[dbo].[DimCustomer]';

BULK INSERT [AdventureKamil].[dbo].[DimCustomer] FROM N'$(SqlSamplesSourceDataPath)DimCustomer.csv'
WITH (
   CODEPAGE='ACP',
   DATAFILETYPE = 'char',
   FIELDTERMINATOR= '\t',
   ROWTERMINATOR = '\n' ,
   KEEPIDENTITY,
   TABLOCK
)

PRINT 'Loading [AdventureKamil].[dbo].[DimData]';

BULK INSERT [AdventureKamil].[dbo].[DimDate] FROM N'$(SqlSamplesSourceDataPath)DimDate.csv'
WITH (
   CODEPAGE='ACP',
   DATAFILETYPE = 'char',
   FIELDTERMINATOR= '\t',
   ROWTERMINATOR = '\n' ,
   KEEPIDENTITY,
   TABLOCK
)

PRINT 'Loading [AdventureKamil].[dbo].[DimGeography]';

BULK INSERT [AdventureKamil].[dbo].[DimGeography] FROM N'$(SqlSamplesSourceDataPath)DimGeography.csv'
WITH (
   CODEPAGE='ACP',
   DATAFILETYPE = 'char',
   FIELDTERMINATOR= '\t',
   ROWTERMINATOR = '\n' ,
   KEEPIDENTITY,
   TABLOCK
)



PRINT 'Loading [AdventureKamil].[dbo].[FactRent]';

BULK INSERT [AdventureKamil].[dbo].[FactRent] FROM N'$(SqlSamplesSourceDataPath)FactRent.csv'
WITH (
   CODEPAGE='ACP',
   DATAFILETYPE = 'char',
   FIELDTERMINATOR= '\t',
   ROWTERMINATOR = '\n' ,
   KEEPIDENTITY,
   TABLOCK
)

PRINT 'Loading [AdventureKamil].[dbo].[DimRoom]';

BULK INSERT [AdventureKamil].[dbo].[DimRoom] FROM N'$(SqlSamplesSourceDataPath)DimRoom.csv'
WITH (
   CODEPAGE='ACP',
   DATAFILETYPE = 'char',
   FIELDTERMINATOR= '\t',
   ROWTERMINATOR = '\n' ,
   KEEPIDENTITY,
   TABLOCK
)

PRINT 'Loading [AdventureKamil].[dbo].[DimSalesTerritory]';

BULK INSERT [AdventureKamil].[dbo].[DimSalesTerritory] FROM N'$(SqlSamplesSourceDataPath)DimSalesTerritory.csv'
WITH (
   CODEPAGE='ACP',
   DATAFILETYPE = 'char',
   FIELDTERMINATOR= '\t',
   ROWTERMINATOR = '\n' ,
   KEEPIDENTITY,
   TABLOCK
)

-- GO

-- ******************************************************
-- Add Primary Keys
-- ******************************************************
PRINT '';
PRINT '*** Adding Primary Keys';
GO

ALTER TABLE [dbo].[DimCustomer] WITH CHECK ADD
    CONSTRAINT [PK_DimCustomer_CustomerKey] PRIMARY KEY CLUSTERED
    (
        [CustomerKey]
    )  ON [PRIMARY];

ALTER TABLE [dbo].[FactRent] WITH CHECK ADD
    CONSTRAINT [PK_FactRent_RentKey] PRIMARY KEY CLUSTERED
    (
        [RentKey]
    )  ON [PRIMARY];

ALTER TABLE [dbo].[DimRoom] WITH CHECK ADD
    CONSTRAINT [PK_DimRoom_RoomKey] PRIMARY KEY CLUSTERED
    (
        [RoomKey]
    )  ON [PRIMARY];

ALTER TABLE [dbo].[DimDate] WITH CHECK ADD
    CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED
    (
        [DateKey]
    )  ON [PRIMARY];

ALTER TABLE [dbo].[DimGeography] WITH CHECK ADD
    CONSTRAINT [PK_DimGeography_GeographyKey] PRIMARY KEY CLUSTERED
    (
        [GeographyKey]
    )  ON [PRIMARY];


ALTER TABLE [dbo].[DimSalesTerritory] WITH CHECK ADD
    CONSTRAINT [PK_DimSalesTerritory_SalesTerritoryKey] PRIMARY KEY CLUSTERED
    (
        [SalesTerritoryKey]
    )  ON [PRIMARY];

-- ****************************************
-- Create Foreign key constraints
-- ****************************************
PRINT '';
PRINT '*** Creating Foreign Key Constraints';
GO

ALTER TABLE [dbo].[DimCustomer] ADD
    CONSTRAINT [FK_DimCustomer_DimGeography] FOREIGN KEY
    (
        [GeographyKey]
    ) REFERENCES [dbo].[DimGeography] (
        [GeographyKey]
    );

ALTER TABLE [dbo].[FactRent] ADD
    CONSTRAINT [FK_FactRent_DimRoom] FOREIGN KEY
    (
        [RoomKey]
    ) REFERENCES [dbo].[DimRoom] (
        [RoomKey]
    ),
    CONSTRAINT [FK_FactRent_DimDate2] FOREIGN KEY
    (
        [RentStartKey]
    ) REFERENCES [dbo].[DimDate] (
        [DateKey]
    );

ALTER TABLE [dbo].[DimGeography] ADD
    CONSTRAINT [FK_DimGeography_DimSalesTerritory] FOREIGN KEY
    (
        [SalesTerritoryKey]
    ) REFERENCES [dbo].[DimSalesTerritory] (
        [SalesTerritoryKey]
    );

ALTER TABLE [dbo].[DimRoom] ADD
    CONSTRAINT [FK_DimRoom_DimGeography] FOREIGN KEY
    (
        [GeographyKey]
    ) REFERENCES [dbo].[DimGeography] (
        [GeographyKey]
    );

/*
-- Output database object creation messages
SELECT [PostTime], [DatabaseUser], [Event], [Schema], [Object], [TSQL], [XmlEvent]
FROM [AdventureKamil].[dbo].[DatabaseLog];
*/
GO


-- ****************************************
-- Change File Growth Values for Database
-- ****************************************
PRINT '';
PRINT '*** Changing File Growth Values for Database';
GO

ALTER DATABASE [AdventureKamil]
MODIFY FILE (NAME = 'AdventureKamil_Data', FILEGROWTH = 16);
ALTER DATABASE [AdventureKamil]
MODIFY FILE (NAME = 'AdventureKamil_Log', FILEGROWTH = 16);
GO


-- ****************************************
-- Shrink Database
-- ****************************************
PRINT '';
PRINT '*** Shrinking Database';
GO

DBCC SHRINKDATABASE ([AdventureKamil]);
GO

USE [master]
GO