Automart Data Warehouse SQL Script

Use Master
if exists 
 (Select name from sys.DataBases where name = 'AutomartDW')
 Begin
 Drop Database AutomartDW
 end
Go
Create database AutomartDW

--possible dimension tables
--Employee
--Customer
--Vehicle
--Services
--Location
Go
use AutomartDW
Go
Create table DimEmployee
(
  EmployeeDWKey int identity(1,1) primary key,
  EmployeeKey int,
  HireDate Date,
  EmployeeFirstname nvarchar(255),
  EmployeeLastname nvarchar(255),
  SupervisorID int,
  LocationID int

)
Go
Create table DimCustomer
(
      CustomerDwKey int identity (1,1) primary key,
   Email nvarchar(255),
   VehicleID int,
   CustomerLastName nvarchar(255),
   CustomerFirstName nvarchar(255)
)
Go
Create Table DimVehicle
(
 VehicleDWKey int identity (1,1) Primary key,
 LicenseNumber nvarchar(10),
 VehicleMake nvarchar(255),
 VehicleYear nchar(4)
)
go
Create table DimLocation
(
 LocationDWKey int identity(1,1) Primary Key,
 LocationName nvarchar(255),
 LocationAddress nvarchar(255),
 LocationCity nvarchar(255),
 locationState nchar(2),
 LocationZip nchar(10),
 LocationPhone nchar(13)
)
go
Create Table DimService
(
 ServiceDWKey int identity(1,1) primary key,
 ServiceName nvarchar(255)

)

Go

Create Table DimTime
(
 DimTimeKey int identity(1,1) primary key,
 ServiceDate Date,
 ServiceTime Time(7),
 ServiceMonth int,
 ServiceYear int
)
 Go
 Create table ServiceFact
 (
 EmployeeDWkey int not null,
 CustomerDWkey int not null,
 LocationDWKey int not null,
 VehicleDWKey int not null,
 ServiceDWKey int not null,
 DimTimeKey int not null,
 AutoServiceID int,
 VSReferenceID int,
 ServicePrice Money,
 DiscountPercent decimal(3,2),
 TaxPercent decimal(3,2)
 )
 go
 alter table ServiceFact
 Add constraint PK_ServiceFact Primary Key (EmployeeDWkey,
 CustomerDWkey,
 LocationDWKey,
 VehicleDWKey,
 ServiceDWKey,
 DimTimeKey)

 alter table ServiceFact
 Add Constraint Fk_EmployeeDim Foreign Key(EmployeeDwKey)
  References DimEmployee(EmployeeDwKey)

 alter table ServiceFact
 Add Constraint Fk_CustomerDim Foreign Key(CustomerDwKey)
  References DimCustomer(CustomerDwKey)

 alter table ServiceFact
 Add Constraint Fk_LocationDim Foreign Key(LocationDwKey)
  References DimLocation(LocationDwKey)

 alter table ServiceFact
 Add Constraint Fk_VehicleDim Foreign Key(VehicleDwKey)
  References DimVehicle(VehicleDwKey)

 alter table ServiceFact
 Add Constraint Fk_ServiceDim Foreign Key(ServiceDwKey)
  References DimService(ServiceDwKey)

 alter table ServiceFact
 Add Constraint Fk_timeDim Foreign Key(DimTimeKey)
  References Dimtime(DimTimeKey)