/*
SCRIPT FILE : 1 : Script Generated To Build Databases, Migration Upsizing Tool
copyright asc associates 2007, All rights Reserved
This is the first script to be run on the SQL Server and creates the basic structures.
*/


-- ENSURE YOU ARE IN THE CORRECT DATABASE TO EXECUTE THE SCRIPT
USE [Northwind]
GO



CREATE TABLE [dbo].[Categories] (
	[CategoryID] INT NOT NULL  IDENTITY(1,1) ,
	[CategoryName] VARCHAR(15) NOT NULL ,
	[Description] VARCHAR(MAX) NULL,
	[Picture] IMAGE NULL,
	[TSCategories] TIMESTAMP NULL,
	[A_CategoriesCreatedBy] VARCHAR(30) NULL CONSTRAINT [defCategoriesA_CategoriesCreatedBy] DEFAULT SUSER_SNAME(),
	[A_CategoriesCreatedOn] DATETIME NULL CONSTRAINT [defCategoriesA_CategoriesCreatedOn] DEFAULT GetDate(),
	[A_CategoriesLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defCategoriesA_CategoriesLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_CategoriesLastUpdatedOn] DATETIME NULL CONSTRAINT [defCategoriesA_CategoriesLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkCategories] PRIMARY KEY CLUSTERED ( [CategoryID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Number automatically assigned to a new category.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Categories', @level2type=N'COLUMN', @level2name=N'CategoryID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Name of food category.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Categories', @level2type=N'COLUMN', @level2name=N'CategoryName'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'A picture representing the food category.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Categories', @level2type=N'COLUMN', @level2name=N'Picture'
GO
CREATE TABLE [dbo].[Customers] (
	[CustomerID] VARCHAR(5) NOT NULL ,
	[CompanyName] VARCHAR(40) NOT NULL ,
	[ContactName] VARCHAR(30) NULL,
	[ContactTitle] VARCHAR(30) NULL,
	[Address] VARCHAR(60) NULL,
	[City] VARCHAR(15) NULL,
	[Region] VARCHAR(15) NULL,
	[PostalCode] VARCHAR(10) NULL,
	[Country] VARCHAR(15) NULL,
	[Phone] VARCHAR(24) NULL,
	[Fax] VARCHAR(24) NULL,
	[TSCustomers] TIMESTAMP NULL,
	[A_CustomersCreatedBy] VARCHAR(30) NULL CONSTRAINT [defCustomersA_CustomersCreatedBy] DEFAULT SUSER_SNAME(),
	[A_CustomersCreatedOn] DATETIME NULL CONSTRAINT [defCustomersA_CustomersCreatedOn] DEFAULT GetDate(),
	[A_CustomersLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defCustomersA_CustomersLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_CustomersLastUpdatedOn] DATETIME NULL CONSTRAINT [defCustomersA_CustomersLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkCustomers] PRIMARY KEY CLUSTERED ( [CustomerID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Unique five-character code based on customer name.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Customers', @level2type=N'COLUMN', @level2name=N'CustomerID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Street or post-office box.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Customers', @level2type=N'COLUMN', @level2name=N'Address'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'State or province.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Customers', @level2type=N'COLUMN', @level2name=N'Region'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Phone number includes country code or area code.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Customers', @level2type=N'COLUMN', @level2name=N'Phone'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Phone number includes country code or area code.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Customers', @level2type=N'COLUMN', @level2name=N'Fax'
GO
CREATE TABLE [dbo].[Employees] (
	[EmployeeID] INT NOT NULL  IDENTITY(1,1) ,
	[LastName] VARCHAR(20) NOT NULL ,
	[FirstName] VARCHAR(10) NOT NULL ,
	[Title] VARCHAR(30) NULL,
	[TitleOfCourtesy] VARCHAR(25) NULL,
	[BirthDate] DATETIME NULL,
	[HireDate] DATETIME NULL,
	[Address] VARCHAR(60) NULL,
	[City] VARCHAR(15) NULL,
	[Region] VARCHAR(15) NULL,
	[PostalCode] VARCHAR(10) NULL,
	[Country] VARCHAR(15) NULL,
	[HomePhone] VARCHAR(24) NULL,
	[Extension] VARCHAR(4) NULL,
	[Photo] VARCHAR(255) NULL,
	[Notes] VARCHAR(MAX) NULL,
	[ReportsTo] INT NULL,
	[TSEmployees] TIMESTAMP NULL,
	[A_EmployeesCreatedBy] VARCHAR(30) NULL CONSTRAINT [defEmployeesA_EmployeesCreatedBy] DEFAULT SUSER_SNAME(),
	[A_EmployeesCreatedOn] DATETIME NULL CONSTRAINT [defEmployeesA_EmployeesCreatedOn] DEFAULT GetDate(),
	[A_EmployeesLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defEmployeesA_EmployeesLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_EmployeesLastUpdatedOn] DATETIME NULL CONSTRAINT [defEmployeesA_EmployeesLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkEmployees] PRIMARY KEY CLUSTERED ( [EmployeeID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Number automatically assigned to new employee.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'EmployeeID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Employee''s title.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'Title'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Title used in salutations.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'TitleOfCourtesy'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Street or post-office box.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'Address'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'State or province.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'Region'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Phone number includes country code or area code.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'HomePhone'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Internal telephone extension number.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'Extension'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Picture of employee.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'Photo'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'General information about employee''s background.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'Notes'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Employee''s supervisor.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Employees', @level2type=N'COLUMN', @level2name=N'ReportsTo'
GO
CREATE TABLE [dbo].[Order Details] (
	[OrderID] INT NOT NULL ,
	[ProductID] INT NOT NULL ,
	[UnitPrice] MONEY NOT NULL  CONSTRAINT [defOrderDetailsUnitPrice] DEFAULT 0,
	[Quantity] SMALLINT NOT NULL  CONSTRAINT [defOrderDetailsQuantity] DEFAULT 1,
	[Discount] REAL NOT NULL  CONSTRAINT [defOrderDetailsDiscount] DEFAULT 0,
	[TSOrderDetails] TIMESTAMP NULL,
	[A_OrderDetailsCreatedBy] VARCHAR(30) NULL CONSTRAINT [defOrderDetailsA_OrderDetailsCreatedBy] DEFAULT SUSER_SNAME(),
	[A_OrderDetailsCreatedOn] DATETIME NULL CONSTRAINT [defOrderDetailsA_OrderDetailsCreatedOn] DEFAULT GetDate(),
	[A_OrderDetailsLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defOrderDetailsA_OrderDetailsLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_OrderDetailsLastUpdatedOn] DATETIME NULL CONSTRAINT [defOrderDetailsA_OrderDetailsLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkOrderDetails] PRIMARY KEY CLUSTERED ( [OrderID],[ProductID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Same as Order ID in Orders table.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Order Details', @level2type=N'COLUMN', @level2name=N'OrderID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Same as Product ID in Products table.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Order Details', @level2type=N'COLUMN', @level2name=N'ProductID'
GO
CREATE TABLE [dbo].[Orders] (
	[OrderID] INT NOT NULL  IDENTITY(1,1) ,
	[CustomerID] VARCHAR(5) NULL,
	[EmployeeID] INT NULL,
	[OrderDate] DATETIME NULL,
	[RequiredDate] DATETIME NULL,
	[ShippedDate] DATETIME NULL,
	[ShipVia] INT NULL,
	[Freight] MONEY NULL CONSTRAINT [defOrdersFreight] DEFAULT 0,
	[ShipName] VARCHAR(40) NULL,
	[ShipAddress] VARCHAR(60) NULL,
	[ShipCity] VARCHAR(15) NULL,
	[ShipRegion] VARCHAR(15) NULL,
	[ShipPostalCode] VARCHAR(10) NULL,
	[ShipCountry] VARCHAR(15) NULL,
	[TSOrders] TIMESTAMP NULL,
	[A_OrdersCreatedBy] VARCHAR(30) NULL CONSTRAINT [defOrdersA_OrdersCreatedBy] DEFAULT SUSER_SNAME(),
	[A_OrdersCreatedOn] DATETIME NULL CONSTRAINT [defOrdersA_OrdersCreatedOn] DEFAULT GetDate(),
	[A_OrdersLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defOrdersA_OrdersLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_OrdersLastUpdatedOn] DATETIME NULL CONSTRAINT [defOrdersA_OrdersLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkOrders] PRIMARY KEY CLUSTERED ( [OrderID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Unique order number.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Orders', @level2type=N'COLUMN', @level2name=N'OrderID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Same entry as in Customers table.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Orders', @level2type=N'COLUMN', @level2name=N'CustomerID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Same entry as in Employees table.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Orders', @level2type=N'COLUMN', @level2name=N'EmployeeID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Same as Shipper ID in Shippers table.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Orders', @level2type=N'COLUMN', @level2name=N'ShipVia'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Name of person or company to receive the shipment.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Orders', @level2type=N'COLUMN', @level2name=N'ShipName'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Street address only -- no post-office box allowed.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Orders', @level2type=N'COLUMN', @level2name=N'ShipAddress'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'State or province.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Orders', @level2type=N'COLUMN', @level2name=N'ShipRegion'
GO
CREATE TABLE [dbo].[Products] (
	[ProductID] INT NOT NULL  IDENTITY(1,1) ,
	[ProductName] VARCHAR(40) NOT NULL ,
	[SupplierID] INT NULL,
	[CategoryID] INT NULL,
	[QuantityPerUnit] VARCHAR(20) NULL,
	[UnitPrice] MONEY NULL CONSTRAINT [defProductsUnitPrice] DEFAULT 0,
	[UnitsInStock] SMALLINT NULL CONSTRAINT [defProductsUnitsInStock] DEFAULT 0,
	[UnitsOnOrder] SMALLINT NULL CONSTRAINT [defProductsUnitsOnOrder] DEFAULT 0,
	[ReorderLevel] SMALLINT NULL CONSTRAINT [defProductsReorderLevel] DEFAULT 0,
	[Discontinued] BIT NULL CONSTRAINT [defProductsDiscontinued] DEFAULT 'N',
	[TSProducts] TIMESTAMP NULL,
	[A_ProductsCreatedBy] VARCHAR(30) NULL CONSTRAINT [defProductsA_ProductsCreatedBy] DEFAULT SUSER_SNAME(),
	[A_ProductsCreatedOn] DATETIME NULL CONSTRAINT [defProductsA_ProductsCreatedOn] DEFAULT GetDate(),
	[A_ProductsLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defProductsA_ProductsLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_ProductsLastUpdatedOn] DATETIME NULL CONSTRAINT [defProductsA_ProductsLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkProducts] PRIMARY KEY CLUSTERED ( [ProductID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Number automatically assigned to new product.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Products', @level2type=N'COLUMN', @level2name=N'ProductID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Same entry as in Suppliers table.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Products', @level2type=N'COLUMN', @level2name=N'SupplierID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Same entry as in Categories table.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Products', @level2type=N'COLUMN', @level2name=N'CategoryID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'(e.g., 24-count case, 1-liter bottle).',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Products', @level2type=N'COLUMN', @level2name=N'QuantityPerUnit'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Minimum units to maintain in stock.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Products', @level2type=N'COLUMN', @level2name=N'ReorderLevel'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Yes means item is no longer available.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Products', @level2type=N'COLUMN', @level2name=N'Discontinued'
GO
CREATE TABLE [dbo].[Shippers] (
	[ShipperID] INT NOT NULL  IDENTITY(1,1) ,
	[CompanyName] VARCHAR(40) NOT NULL ,
	[Phone] VARCHAR(24) NULL,
	[TSShippers] TIMESTAMP NULL,
	[A_ShippersCreatedBy] VARCHAR(30) NULL CONSTRAINT [defShippersA_ShippersCreatedBy] DEFAULT SUSER_SNAME(),
	[A_ShippersCreatedOn] DATETIME NULL CONSTRAINT [defShippersA_ShippersCreatedOn] DEFAULT GetDate(),
	[A_ShippersLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defShippersA_ShippersLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_ShippersLastUpdatedOn] DATETIME NULL CONSTRAINT [defShippersA_ShippersLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkShippers] PRIMARY KEY CLUSTERED ( [ShipperID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Number automatically assigned to new shipper.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Shippers', @level2type=N'COLUMN', @level2name=N'ShipperID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Name of shipping company.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Shippers', @level2type=N'COLUMN', @level2name=N'CompanyName'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Phone number includes country code or area code.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Shippers', @level2type=N'COLUMN', @level2name=N'Phone'
GO
CREATE TABLE [dbo].[Suppliers] (
	[SupplierID] INT NOT NULL  IDENTITY(1,1) ,
	[CompanyName] VARCHAR(40) NOT NULL ,
	[ContactName] VARCHAR(30) NULL,
	[ContactTitle] VARCHAR(30) NULL,
	[Address] VARCHAR(60) NULL,
	[City] VARCHAR(15) NULL,
	[Region] VARCHAR(15) NULL,
	[PostalCode] VARCHAR(10) NULL,
	[Country] VARCHAR(15) NULL,
	[Phone] VARCHAR(24) NULL,
	[Fax] VARCHAR(24) NULL,
	[HomePage] VARCHAR(MAX) NULL,
	[TSSuppliers] TIMESTAMP NULL,
	[A_SuppliersCreatedBy] VARCHAR(30) NULL CONSTRAINT [defSuppliersA_SuppliersCreatedBy] DEFAULT SUSER_SNAME(),
	[A_SuppliersCreatedOn] DATETIME NULL CONSTRAINT [defSuppliersA_SuppliersCreatedOn] DEFAULT GetDate(),
	[A_SuppliersLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defSuppliersA_SuppliersLastUpdatedBy] DEFAULT SUSER_SNAME(),
	[A_SuppliersLastUpdatedOn] DATETIME NULL CONSTRAINT [defSuppliersA_SuppliersLastUpdatedOn] DEFAULT GetDate() ,
	 CONSTRAINT [pkSuppliers] PRIMARY KEY CLUSTERED ( [SupplierID]) 
)
	
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Number automatically assigned to new supplier.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Suppliers', @level2type=N'COLUMN', @level2name=N'SupplierID'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Street or post-office box.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Suppliers', @level2type=N'COLUMN', @level2name=N'Address'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'State or province.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Suppliers', @level2type=N'COLUMN', @level2name=N'Region'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Phone number includes country code or area code.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Suppliers', @level2type=N'COLUMN', @level2name=N'Phone'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Phone number includes country code or area code.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Suppliers', @level2type=N'COLUMN', @level2name=N'Fax'
GO
sp_addextendedproperty @name=N'MS_Description', @value=N'Supplier''s home page on World Wide Web.',@level0type=N'USER',@level0name=N'dbo',@level1type=N'TABLE', @level1name=N'Suppliers', @level2type=N'COLUMN', @level2name=N'HomePage'
GO