1. Write a SQL script to alter the structure of the DimLocation Table to include

Question

1. Write a SQL script to alter the structure of the DimLocation Table to include a new field named StreetID. Therefore, DimLocation table will now include LocationKey, CityKey, StreetID and Street (which contains the street name). The data source for StreetID values is the Street table in the DBService OLTP database. StreetID is DimLocation is the same as Street_Code in the Street table. Data type for StreetID is the same as the data type of Street_Code. Please remember that DimLocation is already populated with data and may also have a PK-FK relationship specified. Therefore, take appropriate steps to empty out the table, make the change, and then repopulate the table with data. Save the entire script to accomplish the above as AlterDimLocation.SQL in the Scripts folder on your USB drive. Ensure that DWService.SQL and DWServiceETL.SQL are also modified to reflect the new structure of the DimLocation table.

Here is the code I have - I am not sure how to do what changes I need to make to satisfy this

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]
   )
   )

This is the code I have to create the DWService:

USE [DWService]
GO

--Create DimDriver
CREATE TABLE [dbo].[DimDriver] (
    [DriverKey] int Not null Identity,
   [DriverID] [nchar](8) ,
   [DriverName] [nvarchar](100)
   CONSTRAINT [PK_DimDriver] PRIMARY KEY CLUSTERED
([DriverKey] ASC)
   )
GO

-- Create Dimension table for dates
CREATE TABLE [dbo].[DimDates] (
[DateKey] int NOT NULL Identity
, [Date] datetime NOT NULL
, [DateName] nVarchar(50)
, [Month] int NOT NULL
, [MonthName] nVarchar(50) NOT NULL
, [Year] int NOT NULL
, [YearName] nVarchar(50) NOT NULL
CONSTRAINT [PK_DimDates] PRIMARY KEY CLUSTERED
([DateKey] ASC))
GO

--Create DimCity
CREATE TABLE [dbo].[DimCity](
   [CityKey] [int] NOT NULL Identity,
   [CityID] nchar(10),
   [CityName] nchar(50) ,
   [State] nVarChar(50) ,
   CONSTRAINT [PK_DimCity] PRIMARY KEY CLUSTERED
([CityKey] ASC))
GO

--[CityID] [Nchar](10) NOT NULL,
--[CityName] [NVarChar](50) NOT NULL,

CREATE TABLE [dbo].[DimLocation] (
[LocationKey] [int] NOT NULL IDENTITY,
[CityKey] [int] ,
[Street] [NVARCHAR](50) NOT NULL
   CONSTRAINT [PK_DimLocation] PRIMARY KEY CLUSTERED
([LocationKey] ASC))
GO

CREATE TABLE [dbo].[FactTrips] (
[TripNumber] [NVarchar](50) NOT NULL,
[DateKey] [int] NOT NULL,
[LocationKey] [int] NOT NULL,
[DriverKey] [int] NOT NULL,
[TripMilage] [decimal](18,4) NOT NULL,
[TripChange] [decimal](18,4) NOT NULL,

CONSTRAINT [PK_FactTrips] PRIMARY KEY CLUSTERED
([TripNumber] ASC, [DateKey] ASC, [LocationKey] ASC, [DriverKey] ASC )
)
GO

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

This is the code to create serviceDB:

USE [serviceDB]
GO

/****** Create the trip Table ******/
CREATE TABLE [dbo].[Trip](
    [number] char(10) NOT NULL,
   [Date] [datetime] NOT NULL,
   [charge] [decimal](18,2),
   [milage] [decimal](18,2),
   [payment_no] int,
   [Street_Code] [nchar](10),
   [Driver_Id] [nchar](5),
CONSTRAINT [PK_Trip] PRIMARY KEY CLUSTERED
(
   [number] ASC
))

/****** Create the Street Table ******/
CREATE TABLE [dbo].[Street](
   [Street_Code] nchar(10) NOT NULL,
   [StreetName] nchar(50),
   [Zipcode] char(5),
   [City_Code] nchar(5),
CONSTRAINT [PK_Street] PRIMARY KEY CLUSTERED
(
   [Street_Code] ASC
))

/****** Create the City Table ******/
CREATE TABLE [dbo].[City](
   [City_Code] nchar(5) NOT NULL,
   [CountryName] nchar(50),
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
   [City_Code] ASC
))

/****** Create the Driver Table ******/
CREATE TABLE [dbo].[Driver](
   [Driver_Id] nchar(5) ,
   [LastName] char(10),
   [FirstName] char(10),
   DateOfBirth datetime
CONSTRAINT [PK_Driver] PRIMARY KEY CLUSTERED
(
   [Driver_Id] ASC
))

-- Creating the weatherDB foreign key constraints

ALTER TABLE [dbo].[Trip] WITH CHECK ADD CONSTRAINT [FK_Trip_Street] FOREIGN KEY([Street_Code])
REFERENCES [dbo].[Street] ([Street_Code])
GO

ALTER TABLE [dbo].[Trip] WITH CHECK ADD CONSTRAINT [FK_Trip_Driver] FOREIGN KEY([Driver_Id])
REFERENCES [dbo].[Driver] ([Driver_Id])
GO

ALTER TABLE [dbo].[Street] WITH CHECK ADD CONSTRAINT [FK_Street_City] FOREIGN KEY([City_Code])
REFERENCES [dbo].[City] ([City_Code])
GO

Explanation / Answer

1.To add Column named StreetID

alter table DimLocation
add StreetID nchar(10) not null

2.Datasource for StreetID

select StreetID=(select Stree_Code from Street)
from DimLocation

3.Same datatype for StreetID as of Street_code

Yes,StreetID column is added to DimLocation table
with same datatype of Street_code in Street table

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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