/****** Object:  Database OC_DW  Script Date: 3/27/2007  ******/
/*
Michael Redmond
Generate a example database for working with Integration Services (a target)

Uncomment the next lines if you want to drop and create the database
*/


/*
USE OC_DW
DROP TABLE FactRental_DW;
DROP TABLE DimRenter_DW;
DROP TABLE DimDateWeeks_DW; 
DROP TABLE DimRentingAgent_DW;
DROP TABLE DimHouse_DW;
DROP DATABASE OC_DW
GO
*/

CREATE DATABASE OC_DW
GO

USE OC_DW
GO

/***********************************************************************************************
 ***  Weeks
 ***********************************************************************************************/

CREATE TABLE DimDateWeeks_DW
 ( 
   WeekKey           int IDENTITY  NOT NULL,
   weekid            numeric(7),
   WeekNumYr         integer,
   Year              integer,
   Month             varchar(9),
   YearMonth         varchar(14),
   startdate         smalldatetime,
   enddate           smalldatetime,
   season            varchar(12),
   special           varchar(15),
      CONSTRAINT DimDateWeeks_DW_pk PRIMARY KEY (WeekKey)
   );
GO
 

/***********************************************************************************************
 ***  Agent
 ***********************************************************************************************/


CREATE TABLE DimRentingAgent_DW
 ( 
   AgentKey           int IDENTITY  NOT NULL,
   Agentid           numeric(7),
   AgentName           varchar(44),  
   FirstName             varchar(20),
   MidInit            char(2),
   LastName              varchar(20), 
   Position          varchar(25),
   RentingAgentCompany           varchar(50),
   RentingAgentOfficename        varchar(50), 
   RentingAgentCity              varchar(50),
   RentingAgentState             char(2),
   RentingAgentZip               varchar(9),
      CONSTRAINT DimRentingAgent_DW_pk PRIMARY KEY (AgentKey),
   );


/***********************************************************************************************
 ***  Renter
 ***********************************************************************************************/

CREATE TABLE DimRenter_DW
 ( 
   RenterKey         int IDENTITY  NOT NULL,
   renterid          numeric(7),
   RenterName             varchar(44),
   RenterFirstName             varchar(20),
   RenterMiddle            char(2),
   RenterLastName              varchar(20),
   RenterGender            varchar(6), 
   RenterCity              varchar(50),
   RenterCounty            varchar(20),
   RenterState             char(2),
   RenterZip               varchar(9), 
   RenterAgeBracket        varchar(8),
   RowIsCurrent            char(1)   NULL ,
   RowStartDate            datetime   NULL , 
   RowEndDate              datetime  DEFAULT '12/31/9999' NULL , 
   RowChangeReason         varchar(200)   NULL ,

      CONSTRAINT renterid_pk PRIMARY KEY (RenterKey),
    );
 

/***********************************************************************************************
 ***  House
 ***********************************************************************************************/

CREATE TABLE DimHouse_DW
 ( 
   HouseKey          int IDENTITY  NOT NULL,
   houseid           numeric(7),
   address           varchar(50),
   city              varchar(50),
   state             char(2),
   zip               varchar(9),
   neighborhood      varchar(12),
   bedrooms          integer,
   bathrooms         integer,
   totalrooms        integer,
   cots              integer,
   sleeps            integer,
   floor             varchar(10),
   type              varchar(10),
   blocks            integer,
   facebeach         varchar(3),
   oceanview         varchar(3),
   oceanfront        varchar(3),
   garage            varchar(3),
   parking           integer,
   tvs               integer,
   cable             varchar(3),
   ac                varchar(10),
   heat              varchar(3),
   washer            varchar(3),
   dryer             varchar(3),
   frontporch        varchar(3),
   backporch         varchar(3),
   outsideshower     varchar(3),
   pool              varchar(3),
   hottub            varchar(3),
   tennis            varchar(3),
   beachtags         varchar(3),
   smoking           varchar(7),
   pets              varchar(7),
   condition         varchar(10),
   basepriceRange         varchar(10),
   OwnerName             varchar(45),
   OwnerFirst             varchar(20),
   OwnerLast              varchar(20),
   OwnerGender            varchar(6), 
   OwnerCity              varchar(50),
   OwnerCounty            varchar(20),
   OwnerState             char(2),
   OwnerZip               varchar(9), 
   ListingAgentName           varchar(44),  
   ListingAgentFirstName             varchar(20),
   ListingAgentLastName              varchar(20), 
   ListingAgentPosition          varchar(25),
   ListingAgentCompany           varchar(50),
   ListingAgentOfficename        varchar(50), 
   ListingAgentCity              varchar(50),
   ListingAgentState             char(2),
   ListingAgentZip               varchar(9),

      CONSTRAINT house_pk PRIMARY KEY (HouseKey),
  );
 



/***********************************************************************************************
 ***  Rentals
 ***********************************************************************************************/


CREATE TABLE FactRental_DW 
 (  
   houseKey           integer,
   renterKey          integer,
   weekKey           integer,
   agentKey           integer,
   askprice          integer,
   discount          integer,
   pricepaid         integer,  
      CONSTRAINT houserent_fk FOREIGN KEY (houseKey) REFERENCES DimHouse_DW(houseKey),
      CONSTRAINT renter_fk FOREIGN KEY (renterKey) REFERENCES DimRenter_DW(renterKey),
      CONSTRAINT weekrent_fk FOREIGN KEY (weekKey) REFERENCES DimDateWeeks_DW(weekKey),
      CONSTRAINT agentrent_fk FOREIGN KEY (agentKey) REFERENCES DimRentingAgent_DW(agentKey)
  );

   


/***********************************************************************************************
 ***  Audit
 ***********************************************************************************************/


/* Create table DimAudit */
CREATE TABLE DimAudit (
   AuditKey  int IDENTITY  NOT NULL
,  TableProcessKey  int   NOT NULL
,  BranchName  varchar(50)   NOT NULL
,  BranchRowCnt  int   NULL
,  ProcessingSummaryGroup  varchar(25)   NOT NULL
, CONSTRAINT PK_DimAudit PRIMARY KEY CLUSTERED 
( AuditKey )
) ON [PRIMARY]
GO



/* Create table AuditPkgExecution */
CREATE TABLE [AuditPkgExecution] (
   [PkgExecKey]  int IDENTITY  NOT NULL
,  [PkgName]  varchar(50)   NULL
,  [PkgGUID]  uniqueidentifier   NULL
,  [PkgVersionGUID]  uniqueidentifier   NULL
,  [PkgVersionMajor]  smallint   NULL
,  [PkgVersionMinor]  smallint   NULL
,  [ExecStartDT]  datetime   NULL
,  [ExecStopDT]  datetime   NULL
,  [SuccessfulProcessingInd]  char(1)   NULL
,  [ParentPkgExecKey]  int   NULL
, CONSTRAINT [PK_AuditPkgExecution] PRIMARY KEY CLUSTERED 
( [PkgExecKey] )
) ON [PRIMARY]
GO