Data Warehouse SQL Script

Use master
go
if exists (Select name from sys.Databases 
where name='MetroAltDw')
Begin
Drop Database MetroAltDW
End
Go
Create Database MetroAltDW
Go
Use MetroAltDW
Go
Create table DimEmployee
(
   DimEmployeeKey int identity(1,1),
   EmployeeKey int unique,
   EmployeelastName nvarchar(255),
   EmployeeFirstName nvarchar(255),
   EmployeeEmail nvarchar(255),
   EmployeeCity nvarchar(255),
   EmployeeZipCode nchar(5),
   EmployeeHireDate date,
   PositionName nvarchar(255),
   EmployeeHourlyPayRate decimal(5,2)

)
go
Create table DimBus
(
    DimBusKey int identity(1,1),
 BusKey int unique,
 BusPurchaseDate Date,
 BusTypeDescription nvarchar(255),
 BusTypeCapacity int,
 BusTypePurchaseprice decimal(12,2)
)
Go
Create table DimRoute
(
 DimRouteKey int identity(1,1),
 BusrouteKey int unique,
 BusRouteZone nvarchar(255)
)
go
Create table DimBusScheduleAssignment
(
   DimBusScheduleAssignmentKey int identity(1,1),
   BusScheduleAssignmentKey int unique,
   ShiftName nvarchar(255)
   
)
go
Create table DimDate
(
 DimDateKey int not null,
 BusScheduleAssignmentDate Date unique,
 BusScheduleAssignmentYear int,
 BusScheduleAssignmentMonth int,
 BusScheduleAssignmentDay int,
 BusScheduleAssignmentDayOfWeek nvarchar(30)

)

go

Create table FactBusSchedule
(
 DimEmployeeKey int not null,
 DimBusKey int not null,
 DimRouteKey int not null,
 DimBusScheduleAssignmentKey int not null,
 DimDateKey int not null,
 Riders int,
 FareAmount money,
 FareImplementationDateYear int
)
Go
alter table DimEmployee
Add Constraint PK_DimEmployee 
primary key(DimEmployeeKey)

alter table DimBus
Add Constraint PK_DimBus 
primary key(DimBusKey)

alter table DimRoute
Add Constraint PK_DimRoute 
primary key(DimRouteKey)

alter table DimBusScheduleAssignment
Add Constraint PK_DimBusScheduleAssignment 
primary key(DimBusScheduleAssignmentKey)

alter table DimDate
Add Constraint PK_DimDate 
primary key(DimDateKey)

alter table FactBusSchedule
Add Constraint PK_FactBusSchedule 
primary key(DimEmployeeKey, DimRouteKey,
DimBusKey, DimBusScheduleAssignmentKey,
DimDateKey)

Alter table FactBusSchedule
Add Constraint FK_DimEmployee
Foreign Key (DimEmployeeKey) 
References DimEmployee(DimEmployeeKey)

Alter table FactBusSchedule
Add Constraint FK_DimBus
Foreign Key (DimBusKey) 
References DimBus(DimBusKey)

Alter table FactBusSchedule
Add Constraint FK_DimRoute
Foreign Key (DimRouteKey) 
References DimRoute(DimRouteKey)

Alter table FactBusSchedule
Add Constraint FK_DimBusScheduleAssignment
Foreign Key (DimBusScheduleAssignmentKey) 
References DimBusScheduleAssignment(DimBusScheduleAssignmentKey)

Alter table FactBusSchedule
Add Constraint FK_DimDate
Foreign Key (DimDateKey) 
References DimDate(DimDateKey)