ÿþWEB LISTING 1: Script to Create the Northwind_Star Dimensional Database



/*

This script creates a new database called Northwind_Star and creates tables and 

foriegn key constraints in the new database.  This database will be created

in the default (c:\mssql7\log) folder, if SQL Server is installed in a different

directory, then the CREATE DATABASE statement below should be changed accordingly.

*/



USE Master

GO



CREATE DATABASE [Northwind_Star]

ON PRIMARY (NAME = N'Northwind_Star_Data', FILENAME = N'C:\MSSQL7\data\Northwind_Star_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) 

LOG ON (NAME = N'Northwind_Star_Log', FILENAME = N'C:\MSSQL7\data\Northwind_Star_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)

GO



USE [Northwind_Star]

GO



if exists (select * from sysobjects where id = object_id(N'[dbo].[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Customer]

GO



if exists (select * from sysobjects where id = object_id(N'[dbo].[Customer_XRef]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Customer_XRef]

GO



if exists (select * from sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Employee]

GO



if exists (select * from sysobjects where id = object_id(N'[dbo].[Orders_Fact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Orders_Fact]

GO



if exists (select * from sysobjects where id = object_id(N'[dbo].[Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Product]

GO



if exists (select * from sysobjects where id = object_id(N'[dbo].[Supplier]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Supplier]

GO



if exists (select * from sysobjects where id = object_id(N'[dbo].[Time]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Time]

GO



CREATE TABLE [dbo].[Customer] (

	[CustomerID] [int] NOT NULL ,

	[CompanyName] [nvarchar] (40) NOT NULL ,

	[City] [nvarchar] (15) NOT NULL ,

	[Country] [nvarchar] (15) NOT NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Customer_XRef] (

	[SourceCustomerId] [nchar] (5) NOT NULL ,

	[DestCustomerId] [int] IDENTITY (1, 1) NOT NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Employee] (

	[EmployeeID] [int] NOT NULL ,

	[EmpLastName] [nvarchar] (20) NOT NULL ,

	[MgrLastName] [nvarchar] (20) NULL,

	[ReportsTo] [int] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Orders_Fact] (

	[ProductID] [int] NOT NULL ,

	[CustomerID] [int] NOT NULL ,

	[EmployeeID] [int] NOT NULL ,

	[TimeID] [int] NOT NULL ,

	[SupplierID] [int] NOT NULL ,

	[Price] [money] NOT NULL ,

	[Quantity] [smallint] NOT NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Product] (

	[ProductID] [int] NOT NULL ,

	[ProductName] [nvarchar] (40) NOT NULL ,

	[ProductCategory] [nvarchar] (15) NOT NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Supplier] (

	[SupplierID] [int] NOT NULL ,

	[CompanyName] [nvarchar] (40) NOT NULL ,

	[Country] [nvarchar] (15) NOT NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Time] (

	[TimeID] [int] IDENTITY (1, 1) NOT NULL ,

	[Date] [smalldatetime] NOT NULL ,

	[Year] [smallint] NOT NULL ,

	[Quarter] [nchar](2) NOT NULL ,

	[Month] [nvarchar] (9) NOT NULL ,

	[Day] [tinyint] NOT NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[Orders_Fact] WITH NOCHECK ADD 

	CONSTRAINT [PK_Orders_Fact] PRIMARY KEY  CLUSTERED 

	(

		TimeID, CustomerID, ProductID, EmployeeID, SupplierID

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD 

	CONSTRAINT [PK_Customer] PRIMARY KEY  NONCLUSTERED 

	(

		[CustomerID]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Customer_XRef] WITH NOCHECK ADD 

	CONSTRAINT [PK_Customer_XRef] PRIMARY KEY  NONCLUSTERED 

	(

		[DestCustomerId]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD 

	CONSTRAINT [PK_Employee] PRIMARY KEY  NONCLUSTERED 

	(

		[EmployeeID]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Product] WITH NOCHECK ADD 

	CONSTRAINT [PK_Product] PRIMARY KEY  NONCLUSTERED 

	(

		[ProductID]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Supplier] WITH NOCHECK ADD 

	CONSTRAINT [PK_Supplier] PRIMARY KEY  NONCLUSTERED 

	(

		[SupplierID]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Time] WITH NOCHECK ADD 

	CONSTRAINT [PK_Time_TimeID] PRIMARY KEY  NONCLUSTERED 

	(

		[TimeID]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Orders_Fact] ADD 

	CONSTRAINT [fk_of_customer] FOREIGN KEY 

	(

		[CustomerID]

	) REFERENCES [dbo].[Customer] (

		[CustomerID]

	),

	CONSTRAINT [fk_of_employee] FOREIGN KEY 

	(

		[EmployeeID]

	) REFERENCES [dbo].[Employee] (

		[EmployeeID]

	),

	CONSTRAINT [fk_of_product] FOREIGN KEY 

	(

		[ProductID]

	) REFERENCES [dbo].[Product] (

		[ProductID]

	),

	CONSTRAINT [fk_of_supplier] FOREIGN KEY 

	(

		[SupplierID]

	) REFERENCES [dbo].[Supplier] (

		[SupplierID]

	),

	CONSTRAINT [fk_of_time] FOREIGN KEY 

	(

		[TimeID]

	) REFERENCES [dbo].[Time] (

		[TimeID]

	)

GO



create nonclustered index nc_productid on orders_fact(productid)

create nonclustered index nc_customerid on orders_fact(customerid)

create nonclustered index nc_supplierid on orders_fact(supplierid)

create nonclustered index nc_timeid on orders_fact(timeid)

GO