Листинг 1

CREATE DATABASE Northwind_Mart ON PRIMARY  
 (  
  NAME=Northwind_Mart_Data,   
  FILENAME='d:\Program Files\Microsoft SQL Server\mssql\data\Northwind_Mart_Data.MDF',   
  SIZE=5MB,  
  FILEGROWTH=10%  
  )  
LOG ON   
  (  
  NAME=Northwind_Mart_Log,   
  FILENAME='d:\Program Files\Microsoft SQL Server\mssql\data\Northwind_Mart_Log.LDF',   
  SIZE=2MB,  
  FILEGROWTH=10%  
  )  
GO  
   
USE Northwind_Mart  
GO  
   
CREATE TABLE [dbo].[Customer_Dim] (  
      [CustomerKey] [int] IDENTITY (1, 1) NOT NULL ,  
      [CustomerID] [nchar] (5) NOT NULL ,  
      [CompanyName] [nvarchar] (40) NOT NULL ,  
      [ContactName] [nvarchar] (30) NOT NULL ,  
      [ContactTitle] [nvarchar] (30) NOT NULL ,  
      [Address] [nvarchar] (60) NOT NULL ,  
      [City] [nvarchar] (15) NOT NULL ,  
      [Region] [nvarchar] (15) NOT NULL ,  
      [PostalCode] [nvarchar] (10) NULL ,  
      [Country] [nvarchar] (15) NOT NULL ,  
      [Phone] [nvarchar] (24) NOT NULL ,  
      [Fax] [nvarchar] (24) NULL   
) ON [PRIMARY]  
GO  
   
CREATE TABLE [dbo].[Employee_Dim] (  
      [EmployeeKey] [int] IDENTITY (1, 1) NOT NULL ,  
      [EmployeeID] [int] NOT NULL ,  
      [EmployeeName] [nvarchar] (30) NOT NULL ,  
      [HireDate] [datetime] NULL   
) ON [PRIMARY]  
GO  
   
CREATE TABLE [dbo].[Product_Dim] (  
      [ProductKey] [int] IDENTITY (1, 1) NOT NULL ,  
      [ProductID] [int] NOT NULL ,  
      [ProductName] [nvarchar] (40) NOT NULL ,  
      [SupplierName] [nvarchar] (40) NOT NULL ,  
      [CategoryName] [nvarchar] (15) NOT NULL ,  
      [ListUnitPrice] [money] NOT NULL   
) ON [PRIMARY]  
GO  
   
CREATE TABLE [dbo].[Sales_Fact] (  
      [TimeKey] [int] NOT NULL ,  
      [CustomerKey] [int] NOT NULL ,  
      [ShipperKey] [int] NOT NULL ,  
      [ProductKey] [int] NOT NULL ,  
      [EmployeeKey] [int] NOT NULL ,  
      [RequiredDate] [datetime] NOT NULL ,  
      [LineItemFreight] [money] NOT NULL ,  
      [LineItemTotal] [money] NOT NULL ,  
      [LineItemQuantity] [smallint] NOT NULL ,  
      [LineItemDiscount] [money] NOT NULL   
) ON [PRIMARY]  
GO  
   
CREATE TABLE [dbo].[Shipper_Dim] (  
      [ShipperKey] [int] IDENTITY (1, 1) NOT NULL ,  
      [ShipperID] [int] NOT NULL ,  
      [ShipperName] [nvarchar] (40) NOT NULL   
) ON [PRIMARY]  
GO  
   
CREATE TABLE [dbo].[Time_Dim] (  
      [TimeKey] [int] IDENTITY (1, 1) NOT NULL ,  
      [TheDate] [datetime] NOT NULL ,  
      [DayOfWeek] [nvarchar] (20) NOT NULL ,  
      [Month] [int] NOT NULL ,  
      [Year] [int] NOT NULL ,  
      [Quarter] [int] NOT NULL ,  
      [DayOfYear] [int] NOT NULL ,  
      [Holiday] [nvarchar] (1) NOT NULL ,  
      [Weekend] [nvarchar] (1) NOT NULL ,  
      [YearMonth] [nvarchar] (10) NOT NULL ,  
      [WeekOfYear] [int] NOT NULL   
) ON [PRIMARY]  
GO  
   
ALTER TABLE [dbo].[Customer_Dim] WITH NOCHECK ADD   
      CONSTRAINT [PK_Customer_Dim] PRIMARY KEY  NONCLUSTERED   
      ([CustomerKey])  ON [PRIMARY]   
GO  
   
ALTER TABLE [dbo].[Employee_Dim] WITH NOCHECK ADD   
      CONSTRAINT [PK_Employee_Dim] PRIMARY KEY  NONCLUSTERED   
      ([EmployeeKey])  ON [PRIMARY]   
GO  
   
ALTER TABLE [dbo].[Product_Dim] WITH NOCHECK ADD   
      CONSTRAINT [PK_Product_Dim] PRIMARY KEY  NONCLUSTERED   
      ([ProductKey])  ON [PRIMARY]   
GO  
   
ALTER TABLE [dbo].[Sales_Fact] WITH NOCHECK ADD   
      CONSTRAINT [PK_Sales_Fact] PRIMARY KEY  NONCLUSTERED   
      (      [TimeKey],  
            [CustomerKey],  
            [ShipperKey],  
            [ProductKey],  
            [EmployeeKey]  
      )  ON [PRIMARY]   
GO  
   
ALTER TABLE [dbo].[Shipper_Dim] WITH NOCHECK ADD   
      CONSTRAINT [PK_Shipper_Dim] PRIMARY KEY  NONCLUSTERED   
      ([ShipperKey])  ON [PRIMARY]   
GO  
   
ALTER TABLE [dbo].[Time_Dim] WITH NOCHECK ADD   
      CONSTRAINT [PK_Time_Dim] PRIMARY KEY  NONCLUSTERED   
      ([TimeKey])  ON [PRIMARY]   
GO  
   
ALTER TABLE [dbo].[Sales_Fact] ADD   
      CONSTRAINT [FK_Sales_Fact_Customer_Dim] FOREIGN KEY   
      ([CustomerKey]) REFERENCES [dbo].[Customer_Dim] ([CustomerKey]),  
      CONSTRAINT [FK_Sales_Fact_Employee_Dim] FOREIGN KEY   
      ([EmployeeKey]) REFERENCES [dbo].[Employee_Dim] ([EmployeeKey]),  
      CONSTRAINT [FK_Sales_Fact_Product_Dim] FOREIGN KEY   
      ([ProductKey]) REFERENCES [dbo].[Product_Dim] ([ProductKey]),  
      CONSTRAINT [FK_Sales_Fact_Shipper_Dim] FOREIGN KEY   
      ([ShipperKey]) REFERENCES [dbo].[Shipper_Dim] ([ShipperKey]),  
      CONSTRAINT [FK_Sales_Fact_Time_Dim] FOREIGN KEY   
      ([TimeKey]) REFERENCES [dbo].[Time_Dim] ([TimeKey])  
GO