use DM$\_$Order

go

CREATE table DimCustomer$2($

CustomerKey INT IDENTITY$(1,1)$ NOT NULL PRIMARY KEY NONCLUSTERED,

CustomerCode NVARCHAR$(50)$ NOT NULL,

CompanyName VARCHAR$(80)$ NOT NULL,

ContactName VARCHAR$(50)$ NOT NULL,

ContactTitle VARCHAR$(80),$

Address VARCHAR$(100),$

City VARCHAR$(50),$

Region VARCHAR$(80),$

PostalCode VARCHAR$(15),$

Country VARCHAR$(15),$

Phone VARCHAR$(10),$

Fax VARCHAR$(12),$

StartDate DATE,

EndDate DATE,

IsActive BIT

$)$;

CREATE TABLE DimDate$2($

DateKey INT PRIMARY KEY NOT NULL,

FullDate DATE,

DayNumberOfWeek INT,

DayNumberOfMonth INT,

DayNumberOfYear INT,

WeekNumberOfYear INT,

MonthName VARCHAR$(50),$

MonthNumberOfYear INT,

Quarter INT,

Year INT

$)$;

CREATE TABLE DimEmployee$2($

EmployeeKey INT PRIMARY KEY NOT NULL,

EmployeeId INT,

Title VARCHAR$(50),$

FirstName VARCHAR$(50),$

MiddleName VARCHAR$(50),$

LastName VARCHAR$(50),$

EmailAddress VARCHAR$(100),$

AddressLine$1$ VARCHAR$(100),$

City VARCHAR$(50),$

PostalCode VARCHAR$(15),$

StartDate DATE,

EndDate DATE,

IsActive BIT

$)$;

CREATE TABLE DimProduct$2($

ProductKey INT PRIMARY KEY NOT NULL,

ProductID INT,

ProductName VARCHAR$(100),$

EnglishName VARCHAR$(100),$

QuantityPerUnit VARCHAR$(50),$

Discontinued BIT,

LastRevisionDate DATE

$)$;

CREATE TABLE SalesFact$2($

ProductKey INT FOREIGN KEY $($ProductKey$)$ REFERENCES DimProduct$2($ProductKey$),$

EmployeeKey INT FOREIGN KEY $($EmployeeKey$)$ REFERENCES DimEmployee$2($EmployeeKey$),$

CustomerKey INT FOREIGN KEY $($CustomerKey$)$ REFERENCES DimCustomer$($CustomerKey$),$

OrderDateKey INT FOREIGN KEY $($OrderDateKey$)$ REFERENCES DimDate$2($DateKey$),$

RequiredDateKey INT FOREIGN KEY $($RequiredDateKey$)$ REFERENCES DimDate$2($DateKey$),$

ShippedDateKey INT FOREIGN KEY $($ShippedDateKey$)$ REFERENCES DimDate$2($DateKey$),$

SalesOrderNumber varchar$(50)$ Primary Key not null,

OrderQuantity INT,

UnitPrice DECIMAL$(18,2),$

UnitPriceDiscount DECIMAL$(18,2),$

DiscountAmount DECIMAL$(18,2),$

SalesAmount DECIMAL$(18,2)$

$)$;