USE SQL2008SBS
GO

CREATE SCHEMA HumanResources AUTHORIZATION dbo
GO

CREATE TABLE Customers.Customer
(CustomerID     INT         IDENTITY(1,1),
CompanyName     VARCHAR(50) NULL,
FirstName       VARCHAR(50) NULL,
LastName        VARCHAR(50) NULL,
ModifiedDate    DATE        NOT NULL CONSTRAINT df_modifieddate DEFAULT GETDATE(),
CONSTRAINT pk_customer PRIMARY KEY (CustomerID))
GO

CREATE TABLE Customers.CustomerAddress
(AddressID      INT         IDENTITY(1,1),
CustomerID      INT         NOT NULL,
AddressTypeID   INT         NOT NULL,
AddressLine1    VARCHAR(50) NOT NULL,
AddressLine2    VARCHAR(50) SPARSE NULL,
AddressLine3    VARCHAR(50) SPARSE NULL,
City            VARCHAR(50) NOT NULL,
StateProvinceID INT         NULL,
CountryID       INT         NULL,
CONSTRAINT pk_customeraddress PRIMARY KEY (AddressID))
GO

CREATE TABLE Orders.OrderHeader
(OrderID        INT         IDENTITY(1,1),
CustomerID      INT         NOT NULL,
OrderDate       DATE        NOT NULL CONSTRAINT df_orderdate DEFAULT GETDATE(),
SubTotal        MONEY       NOT NULL CONSTRAINT ck_subtotal CHECK (SubTotal > 0),
TaxAmount       MONEY       NOT NULL CONSTRAINT ck_taxamount CHECK (TaxAmount >= 0),
ShippingAmount  MONEY       NOT NULL CONSTRAINT ck_shippingamount CHECK (ShippingAmount >= 0),
GrandTotal      AS (SubTotal + TaxAmount + ShippingAmount),
FinalShipDate   DATE        NULL,
CONSTRAINT pk_orderheader PRIMARY KEY (OrderID))
GO

CREATE TABLE Orders.OrderDetail
(OrderDetailID  INT         IDENTITY(1,1),
OrderID         INT         NOT NULL,
SKU             CHAR(10)    NOT NULL,
Quantity        INT         NOT NULL CHECK (Quantity > 0),
UnitPrice       MONEY       NOT NULL CHECK (UnitPrice > 0),
ShipDate        DATE        NULL,
CONSTRAINT pk_orderdetail PRIMARY KEY (OrderDetailID))
GO

CREATE TABLE Products.Product
(ProductID          INT             IDENTITY(1,1),
SubcategoryID       INT             NOT NULL,
ProductName         VARCHAR(50)     NOT NULL,
ProductCost         MONEY           NOT NULL CHECK (ProductCost > 0),
ListPrice           MONEY           NOT NULL CHECK (ListPrice > 0),
ProductMargin       AS (ListPrice - ProductCost),
ProductDescription  XML             NULL,
CONSTRAINT pk_product PRIMARY KEY (ProductID))
GO

CREATE TABLE HumanResources.Employee
(EmployeeID     INT         IDENTITY(1,1),
FirstName       VARCHAR(50) NOT NULL,
LastName        VARCHAR(50) NOT NULL,
JobTitle        VARCHAR(50) NOT NULL,
BirthDate       DATE        NOT NULL,
HireDate        DATE        NOT NULL,
CONSTRAINT pk_employee PRIMARY KEY (EmployeeID))
GO

CREATE TABLE HumanResources.EmployeeAddress
(AddressID      INT         IDENTITY(1,1),
EmployeeID      INT         NOT NULL,
AddressTypeID   INT         NOT NULL,
AddressLine1    VARCHAR(50) NOT NULL,
AddressLine2    VARCHAR(50) SPARSE NULL,
AddressLine3    VARCHAR(50) SPARSE NULL,
City            VARCHAR(50) NOT NULL,
StateProvinceID INT         NULL,
CountryID       INT         NULL,
CONSTRAINT pk_employeeaddress PRIMARY KEY (AddressID))
GO

CREATE TABLE LookupTables.AddressType
(AddressTypeID      INT         IDENTITY(1,1),
AddressType         VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_addresstype PRIMARY KEY(AddressTypeID))
GO

CREATE TABLE LookupTables.Country
(CountryID      INT         IDENTITY(1,1),
CountryName     VARCHAR(70) NOT NULL UNIQUE,
CONSTRAINT pk_country PRIMARY KEY(CountryID))
GO

CREATE TABLE LookupTables.StateProvince
(StateProvinceID    INT         IDENTITY(1,1),
CountryID           INT         NOT NULL,
StateProvince       VARCHAR(50) NOT NULL UNIQUE,
StateProvinceAbbrev CHAR(2)     NOT NULL,
CONSTRAINT pk_stateprovince PRIMARY KEY(StateProvinceID))
GO

CREATE TABLE LookupTables.ProductCategory
(CategoryID     INT         IDENTITY(1,1),
Category        VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productcategory PRIMARY KEY(CategoryID))
GO

CREATE TABLE LookupTables.ProductSubCategory
(SubcategoryID      INT         IDENTITY(1,1),
CategoryID          INT         NOT NULL,
SubcategoryName     VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productsubcategory PRIMARY KEY(SubcategoryID))
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customertocustomeraddress FOREIGN KEY (CustomerID)
    REFERENCES Customers.Customer (CustomerID)    
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customeraddresstoaddresstype FOREIGN KEY (AddressTypeID)
    REFERENCES LookupTables.AddressType (AddressTypeID)    
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customeraddresstostateprovince FOREIGN KEY (StateProvinceID)
    REFERENCES LookupTables.StateProvince (StateProvinceID)    
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customeraddresstocountry FOREIGN KEY (CountryID)
    REFERENCES LookupTables.Country (CountryID)    
GO

ALTER TABLE Orders.OrderHeader
    ADD CONSTRAINT fk_customertoorderheader FOREIGN KEY (CustomerID)
    REFERENCES Customers.Customer (CustomerID)    
GO

ALTER TABLE Orders.OrderDetail
    ADD CONSTRAINT fk_orderheadertoorderdeatils FOREIGN KEY (OrderID)
    REFERENCES Orders.OrderHeader (OrderID)    
GO

ALTER TABLE Products.Product
    ADD CONSTRAINT fk_producttosubcategory FOREIGN KEY (SubcategoryID)
    REFERENCES LookupTables.ProductSubCategory (SubcategoryID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeetoemployeeaddress FOREIGN KEY (EmployeeID)
    REFERENCES HumanResources.Employee (EmployeeID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeeaddresstoaddresstype FOREIGN KEY (AddressTypeID)
    REFERENCES LookupTables.AddressType (AddressTypeID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeeaddresstostateprovince FOREIGN KEY (StateProvinceID)
    REFERENCES LookupTables.StateProvince (StateProvinceID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeeaddresstocountry FOREIGN KEY (CountryID)
    REFERENCES LookupTables.Country (CountryID)    
GO

ALTER TABLE LookupTables.StateProvince
    ADD CONSTRAINT fk_countrytostateprovince FOREIGN KEY (CountryID)
    REFERENCES LookupTables.Country(CountryID)    
GO

ALTER TABLE LookupTables.ProductSubCategory
    ADD CONSTRAINT fk_productcategorytosubcategory FOREIGN KEY (CategoryID)
    REFERENCES LookupTables.ProductCategory(CategoryID)
GO

USE SQL2008SBSFS
GO

CREATE TABLE Products.ProductDocument
(DocumentID     UNIQUEIDENTIFIER    ROWGUIDCOL UNIQUE,
ProductID       INT                 NOT NULL,
DocumentType    VARCHAR(20)         NOT NULL,
Document        VARBINARY(MAX)      FILESTREAM NULL,
CONSTRAINT pk_productdocument PRIMARY KEY(DocumentID))
GO