Write a SQL script to alter the structure of the DimLocations table to include a new field named StreetID. Therefore, DimLocation Table will now include LocationKey, CityKey, StreetID and Street(Which contains street name). The data source for StreetID values the StreetTable in the OLTP database. STreetID in DimLocation is the same as Street_Code in the street table. Data Type for StreetID is the same as the data type Street_Code. (Which is Nchar(10)) Remember that the DimLocations table is already populated with data and may also have a PK-FK relationship. Therefore take the appropriate steps to empty out the table, make the change, and then repopulate the table with data.

Here is the ETL start:

-- First drop all foreign key constraints

ALTER TABLE dbo.DimLocation DROP CONSTRAINT [FK_DimLocation_DimCity] ALTER TABLE dbo.FactTrips DROP CONSTRAINT [FK_FactTrips_DimDates] ALTER TABLE dbo.FactTrips DROP CONSTRAINT [FK_FactTrips_DimLocation] ALTER TABLE dbo.FactTrips DROP CONSTRAINT [FK_FactTrips_DimDriver] Go

--Truncate the tables to 'Select all' the data to flush. This clears the tables TRUNCATE TABLE [dbo].[DimCity] TRUNCATE TABLE [dbo].[DimDates] TRUNCATE TABLE [dbo].[DimLocation] TRUNCATE TABLE [dbo].[DimDriver] TRUNCATE TABLE [dbo].[FactTrips]

--Add the foreign keys back in

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimCity] FOREIGN KEY ([CityKey]) References [dbo].[DimCity] ([CityKey]) GO

ALTER TABLE [dbo].[FactTrips] WITH CHECK ADD CONSTRAINT [FK_FactTrips_DimLocation] FOREIGN KEY ([LocationKey]) REFERENCES [dbo].[DimLocation] ([LocationKey]) GO

ALTER TABLE [dbo].[FactTrips] WITH CHECK ADD CONSTRAINT [FK_FactTrips_DimDates] FOREIGN KEY ([DateKey]) References [dbo].[DimDates] ([DateKey]) GO

ALTER TABLE [dbo].[FactTrips] WITH CHECK ADD CONSTRAINT [FK_FactTrips_DimDriver] FOREIGN KEY ([DriverKey]) References [dbo].[DimDriver] ([DriverKey]) GO --The dimLocation Table, which would repopulate the table

INSERT INTO dbo.DimLocation ( CityKey, Street ) ( SELECT [CityKey] = DimCity.CityKey, [Street] = CAST ( isNull([StreetName], 'Unknown') AS NVARCHAR(50)) FROM ([ServiceDB].[Dbo].[Street] INNER JOIN [DWService].[dbo].[DimCity] ON [serviceDB].[dbo].[Street].[City_Code] = [DWService].[dbo].[DimCity].[CityID] ) )

DimDrivel xiver Datel Tri??- Data_ stueat code1 Location Diveh TAP 2 Locationhe iu Stueat omeit wame TBL Sheet got steet Stieet code CN OLT -code Tip emp Street.code-->%rutetzp stuut-lede