Database Reference
In-Depth Information
3.
If you are typing the SQL code, highlight and execute the code by clicking the
“! Execute” button. If you choose to use the table designer or the diagramming tool,
make sure that you use the Save button to create the tables in the database.
adding Foreign Key constraints
Now that the tables are created, we want to add foreign key constraints between the tables.
1.
using either SQL code or the diagramming tool, create foreign key constraints as
described by the code in Listing 5-9.
Listing 5-9.
Creating the DWPubsSales Foreign Key Constraints
/****** Add Foreign Keys ******/
ALTER TABLE [dbo].[DimTitles] WITH CHECK ADD CONSTRAINT [FK_DimTitles_DimPublishers]
FOREIGN KEY([PublisherKey]) REFERENCES [dbo].[DimPublishers] ([PublisherKey])
GO
ALTER TABLE [dbo].[FactTitlesAuthors] WITH CHECK ADD CONSTRAINT
[FK_FactTitlesAuthors_DimAuthors]
FOREIGN KEY([AuthorKey]) REFERENCES [dbo].[DimAuthors] ([AuthorKey])
GO
ALTER TABLE [dbo].[FactTitlesAuthors] WITH CHECK ADD CONSTRAINT
[FK_FactTitlesAuthors_DimTitles]
FOREIGN KEY([TitleKey]) REFERENCES [dbo].[DimTitles] ([TitleKey])
GO
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimStores]
FOREIGN KEY([StoreKey]) REFERENCES [dbo].[DimStores] ([Storekey])
GO
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimTitles]
FOREIGN KEY([TitleKey]) REFERENCES [dbo].[DimTitles] ([TitleKey])
GO
2.
If you are typing the SQL code, highlight and execute the code by clicking the “!
Execute” button. If you choose to use the table designer or the diagramming tool,
make sure you use the Save button to create the tables in the database.
Verifying Your tables
once the tables are made and the foreign keys created, it is best to check your work by comparing it to the
design document, the Publication Industries BI solution worksheet you reviewed in Chapter 3.
1.
open the file
C:\_BISolutions\PublicationsIndustries\
BISolutionWorksheets.xlsx
in Microsoft Excel, and verify that you have created
your data warehouse accurately (Figure
5-28
).
In this exercise, you created the data warehouse tables and foreign key constraints. Currently these tables
are empty. In Chapters 6 through 8 we create an ETL process to fill them.
