Write a SQL script to alter the structure of the DimLocations table to include a

Question

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?.

Explanation / Answer

One time script to alter table is:

Step 1: Drop foreign keys

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

Step 2: Alter the table

ALTER TABLE dbo.DimLocation

ADD StreetID NCHAR(10) NULL

Step 3: Truncate the table

TRUNCATE TABLE [dbo].[DimLocation]

Step 4: ADD Foreign keys

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimCity]

FOREIGN KEY ([CityKey]) References [dbo].[DimCity] ([CityKey])
GO

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimStreet]

FOREIGN KEY ([StreetID]) References [dbo].[DimStreet] ([Street_Code])
GO

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

Step 5: Updated population logic

--The dimLocation Table, which would repopulate the table

INSERT INTO dbo.DimLocation
( CityKey, Street , StreetID)
(
   SELECT
       [CityKey] = DimCity.CityKey,
       [Street] = CAST ( isNull([StreetName], 'Unknown') AS NVARCHAR(50)),

[StreetID] = [Street_Code]
   FROM ([ServiceDB].[Dbo].[Street]
   INNER JOIN [DWService].[dbo].[DimCity]
   ON [serviceDB].[dbo].[Street].[City_Code] = [DWService].[dbo].[DimCity].[CityID]
   )
   )

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.