/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/

USE master
GO
if exists (select * from sysdatabases where name='Northgale')
      drop database Northgale
go

DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

EXECUTE (N'CREATE DATABASE Northgale
  ON PRIMARY (NAME = N''Northgale'', FILENAME = N''' + @device_directory + N'Northgale.mdf'', SIZE = 500 MB)
  LOG ON (NAME = N''Northgale_log'',  FILENAME = N''' + @device_directory + N'Northgale.ldf'', SIZE = 20 MB)')

DECLARE @logsize char(1)
SELECT @logsize = CASE WHEN convert(varchar, Serverproperty('ProductVersion')) LIKE '8%'
                       THEN '2'
                       ELSE '4'
                  END
EXECUTE(N'ALTER DATABASE Northgale
    ADD LOG FILE (NAME = N''Northgale_log2'',
                  FILENAME = N''' + @device_directory + N'Northgalelog2.ldf'', SIZE = ' + @logsize + ' GB)')


exec sp_dboption 'Northgale','trunc. log on chkpt.','true'
GO
set quoted_identifier on
GO
use tempdb  -- Precaution, in case database creation failes.
go
use Northgale
go
CREATE TABLE "Employees" (
   "EmployeeID" "int" NOT NULL ,
   "LastName" nvarchar (20) NOT NULL ,
   "FirstName" nvarchar (10) NOT NULL ,
   "Title" nvarchar (30) NULL ,
   "TitleOfCourtesy" nvarchar (25) NULL ,
   "BirthDate" "datetime" NULL ,
   "HireDate" "datetime" NULL ,
   "Address" nvarchar (60) NULL ,
   "City" nvarchar (15) NULL ,
   "Region" nvarchar (15) NULL ,
   "PostalCode" nvarchar (10) NULL ,
   "Country" nvarchar (15) NULL ,
   "HomePhone" nvarchar (24) NULL ,
   "Extension" nvarchar (4) NULL ,
   "Photo" "image" NULL ,
   "Notes" "ntext" NULL ,
   "ReportsTo" "int" NULL ,
   "PhotoPath" nvarchar (255) NULL ,
   CONSTRAINT "PK_Employees" PRIMARY KEY  CLUSTERED
   (
      "EmployeeID"
   ),
   CONSTRAINT "FK_Employees_Employees" FOREIGN KEY
   (
      "ReportsTo"
   ) REFERENCES "dbo"."Employees" (
      "EmployeeID"
   ),
   CONSTRAINT "CK_Birthdate" CHECK (BirthDate < getdate())
)
GO
 CREATE  INDEX "LastName" ON "dbo"."Employees"("LastName")
GO
 CREATE  INDEX "PostalCode" ON "dbo"."Employees"("PostalCode")
GO

CREATE TABLE "Categories" (
   "CategoryID" "int" NOT NULL ,
   "CategoryName" nvarchar (15) NOT NULL ,
   "Description" "ntext" NULL ,
   "Picture" "image" NULL ,
   CONSTRAINT "PK_Categories" PRIMARY KEY  CLUSTERED
   (
      "CategoryID"
   )
)
GO
 CREATE  INDEX "CategoryName" ON "dbo"."Categories"("CategoryName")
GO

CREATE TABLE "Customers" (
   "CustomerID" nchar (5) NOT NULL ,
   "CompanyName" nvarchar (40) NOT NULL ,
   "ContactName" nvarchar (30) NULL ,
   "ContactTitle" nvarchar (30) NULL ,
   "Address" nvarchar (60) NULL ,
   "City" nvarchar (15) NULL ,
   "Region" nvarchar (15) NULL ,
   "PostalCode" nvarchar (10) NULL ,
   "Country" nvarchar (15) NULL ,
   "Phone" nvarchar (24) NULL ,
   "Fax" nvarchar (24) NULL ,
   CONSTRAINT "PK_Customers" PRIMARY KEY  CLUSTERED
   (
      "CustomerID"
   )
)
GO
 CREATE  INDEX "City" ON "dbo"."Customers"("City")
GO
 CREATE  INDEX "CompanyName" ON "dbo"."Customers"("CompanyName")
GO
 CREATE  INDEX "PostalCode" ON "dbo"."Customers"("PostalCode")
GO
 CREATE  INDEX "Region" ON "dbo"."Customers"("Region")
GO

CREATE TABLE "Shippers" (
   "ShipperID" "int" NOT NULL ,
   "CompanyName" nvarchar (40) NOT NULL ,
   "Phone" nvarchar (24) NULL ,
   CONSTRAINT "PK_Shippers" PRIMARY KEY  CLUSTERED
   (
      "ShipperID"
   )
)
GO
CREATE TABLE "Suppliers" (
   "SupplierID" "int" NOT NULL ,
   "CompanyName" nvarchar (40) NOT NULL ,
   "ContactName" nvarchar (30) NULL ,
   "ContactTitle" nvarchar (30) NULL ,
   "Address" nvarchar (60) NULL ,
   "City" nvarchar (15) NULL ,
   "Region" nvarchar (15) NULL ,
   "PostalCode" nvarchar (10) NULL ,
   "Country" nvarchar (15) NULL ,
   "Phone" nvarchar (24) NULL ,
   "Fax" nvarchar (24) NULL ,
   "HomePage" "ntext" NULL ,
   CONSTRAINT "PK_Suppliers" PRIMARY KEY  CLUSTERED
   (
      "SupplierID"
   )
)
GO
 CREATE  INDEX "CompanyName" ON "dbo"."Suppliers"("CompanyName")
GO
 CREATE  INDEX "PostalCode" ON "dbo"."Suppliers"("PostalCode")
GO

CREATE TABLE "Orders" (
   "OrderID" "int" NOT NULL ,
   "CustomerID" nchar (5) NULL ,
   "EmployeeID" "int" NULL ,
   "OrderDate" "datetime" NULL ,
   "RequiredDate" "datetime" NULL ,
   "ShippedDate" "datetime" NULL ,
   "ShipVia" "int" NULL ,
   "Freight" "money" NULL CONSTRAINT "DF_Orders_Freight" DEFAULT (0),
   "ShipName" nvarchar (40) NULL ,
   "ShipAddress" nvarchar (60) NULL ,
   "ShipCity" nvarchar (15) NULL ,
   "ShipRegion" nvarchar (15) NULL ,
   "ShipPostalCode" nvarchar (10) NULL ,
   "ShipCountry" nvarchar (15) NULL ,
   CONSTRAINT "PK_Orders" PRIMARY KEY  CLUSTERED
   (
      "OrderID"
   ),
   CONSTRAINT "FK_Orders_Customers" FOREIGN KEY
   (
      "CustomerID"
   ) REFERENCES "dbo"."Customers" (
      "CustomerID"
   ),
   CONSTRAINT "FK_Orders_Employees" FOREIGN KEY
   (
      "EmployeeID"
   ) REFERENCES "dbo"."Employees" (
      "EmployeeID"
   ),
   CONSTRAINT "FK_Orders_Shippers" FOREIGN KEY
   (
      "ShipVia"
   ) REFERENCES "dbo"."Shippers" (
      "ShipperID"
   )
)
GO
 CREATE  INDEX "CustomerID" ON "dbo"."Orders"("CustomerID")
GO
 CREATE  INDEX "CustomersOrders" ON "dbo"."Orders"("CustomerID")
GO
 CREATE  INDEX "EmployeeID" ON "dbo"."Orders"("EmployeeID")
GO
 CREATE  INDEX "EmployeesOrders" ON "dbo"."Orders"("EmployeeID")
GO
 CREATE  INDEX "OrderDate" ON "dbo"."Orders"("OrderDate")
GO
 CREATE  INDEX "ShippedDate" ON "dbo"."Orders"("ShippedDate")
GO
 CREATE  INDEX "ShippersOrders" ON "dbo"."Orders"("ShipVia")
GO
 CREATE  INDEX "ShipPostalCode" ON "dbo"."Orders"("ShipPostalCode")
GO

CREATE TABLE "Products" (
   "ProductID" "int" NOT NULL ,
   "ProductName" nvarchar (40) NOT NULL ,
   "SupplierID" "int" NULL ,
   "CategoryID" "int" NULL ,
   "QuantityPerUnit" nvarchar (20) NULL ,
   "UnitPrice" "money" NULL CONSTRAINT "DF_Products_UnitPrice" DEFAULT (0),
   "UnitsInStock" "smallint" NULL CONSTRAINT "DF_Products_UnitsInStock" DEFAULT (0),
   "UnitsOnOrder" "smallint" NULL CONSTRAINT "DF_Products_UnitsOnOrder" DEFAULT (0),
   "ReorderLevel" "smallint" NULL CONSTRAINT "DF_Products_ReorderLevel" DEFAULT (0),
   "Discontinued" "bit" NOT NULL CONSTRAINT "DF_Products_Discontinued" DEFAULT (0),
   CONSTRAINT "PK_Products" PRIMARY KEY  CLUSTERED
   (
      "ProductID"
   ),
   CONSTRAINT "FK_Products_Categories" FOREIGN KEY
   (
      "CategoryID"
   ) REFERENCES "dbo"."Categories" (
      "CategoryID"
   ),
   CONSTRAINT "FK_Products_Suppliers" FOREIGN KEY
   (
      "SupplierID"
   ) REFERENCES "dbo"."Suppliers" (
      "SupplierID"
   ),
   CONSTRAINT "CK_Products_UnitPrice" CHECK (UnitPrice >= 0),
   CONSTRAINT "CK_ReorderLevel" CHECK (ReorderLevel >= 0),
   CONSTRAINT "CK_UnitsInStock" CHECK (UnitsInStock >= 0),
   CONSTRAINT "CK_UnitsOnOrder" CHECK (UnitsOnOrder >= 0)
)
GO
 CREATE  INDEX "CategoriesProducts" ON "dbo"."Products"("CategoryID")
GO
 CREATE  INDEX "CategoryID" ON "dbo"."Products"("CategoryID")
GO
 CREATE  INDEX "ProductName" ON "dbo"."Products"("ProductName")
GO
 CREATE  INDEX "SupplierID" ON "dbo"."Products"("SupplierID")
GO
 CREATE  INDEX "SuppliersProducts" ON "dbo"."Products"("SupplierID")
GO

CREATE TABLE "Order Details" (
   "OrderID" "int" NOT NULL ,
   "ProductID" "int" NOT NULL ,
   "UnitPrice" "money" NOT NULL CONSTRAINT "DF_Order_Details_UnitPrice" DEFAULT (0),
   "Quantity" "smallint" NOT NULL CONSTRAINT "DF_Order_Details_Quantity" DEFAULT (1),
   "Discount" "real" NOT NULL CONSTRAINT "DF_Order_Details_Discount" DEFAULT (0),
   CONSTRAINT "PK_Order_Details" PRIMARY KEY  CLUSTERED
   (
      "OrderID",
      "ProductID"
   ),
   CONSTRAINT "FK_Order_Details_Orders" FOREIGN KEY
   (
      "OrderID"
   ) REFERENCES "dbo"."Orders" (
      "OrderID"
   ),
   CONSTRAINT "FK_Order_Details_Products" FOREIGN KEY
   (
      "ProductID"
   ) REFERENCES "dbo"."Products" (
      "ProductID"
   ),
   CONSTRAINT "CK_Discount" CHECK (Discount >= 0 and (Discount <= 1)),
   CONSTRAINT "CK_Quantity" CHECK (Quantity > 0),
   CONSTRAINT "CK_UnitPrice" CHECK (UnitPrice >= 0)
)
GO
 CREATE  INDEX "OrderID" ON "dbo"."Order Details"("OrderID")
GO
 CREATE  INDEX "OrdersOrder_Details" ON "dbo"."Order Details"("OrderID")
GO
 CREATE  INDEX "ProductID" ON "dbo"."Order Details"("ProductID")
GO
 CREATE  INDEX "ProductsOrder_Details" ON "dbo"."Order Details"("ProductID")
GO


/* The follwing adds tables to the Northgale database */


CREATE TABLE [dbo].[CustomerCustomerDemo]
   ([CustomerID] nchar (5) NOT NULL,
   [CustomerTypeID] [nchar] (10) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CustomerDemographics]
   ([CustomerTypeID] [nchar] (10) NOT NULL ,
   [CustomerDesc] [ntext] NULL
)  ON [PRIMARY]
GO

CREATE TABLE [dbo].[Region]
   ( [RegionID] [int] NOT NULL ,
   [RegionDescription] [nchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Territories]
   ([TerritoryID] [nvarchar] (20) NOT NULL ,
   [TerritoryDescription] [nchar] (50) NOT NULL ,
        [RegionID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmployeeTerritories]
   ([EmployeeID] [int] NOT NULL,
   [TerritoryID] [nvarchar] (20) NOT NULL
) ON [PRIMARY]




--  The following adds constraints to the Northgale database

ALTER TABLE CustomerCustomerDemo
   ADD CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED
   (
      [CustomerID],
      [CustomerTypeID]
   ) ON [PRIMARY]
GO

ALTER TABLE CustomerDemographics
   ADD CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED
   (
      [CustomerTypeID]
   ) ON [PRIMARY]
GO

ALTER TABLE CustomerCustomerDemo
   ADD CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY
   (
      [CustomerTypeID]
   ) REFERENCES [dbo].[CustomerDemographics] (
      [CustomerTypeID]
   )
GO

ALTER TABLE CustomerCustomerDemo
   ADD CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
   (
      [CustomerID]
   ) REFERENCES [dbo].[Customers] (
      [CustomerID]
   )
GO

ALTER TABLE Region
   ADD CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED
   (
      [RegionID]
   )  ON [PRIMARY]
GO

ALTER TABLE Territories
   ADD CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED
   (
      [TerritoryID]
   )  ON [PRIMARY]
GO

ALTER TABLE Territories
   ADD CONSTRAINT [FK_Territories_Region] FOREIGN KEY
   (
      [RegionID]
   ) REFERENCES [dbo].[Region] (
      [RegionID]
   )
GO

ALTER TABLE EmployeeTerritories
   ADD CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED
   (
      [EmployeeID],
      [TerritoryID]
   ) ON [PRIMARY]
GO

ALTER TABLE EmployeeTerritories
   ADD CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY
   (
      [EmployeeID]
   ) REFERENCES [dbo].[Employees] (
      [EmployeeID]
   )
GO


ALTER TABLE EmployeeTerritories
   ADD CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
   (
      [TerritoryID]
   ) REFERENCES [dbo].[Territories] (
      [TerritoryID]
   )
GO
INSERT Categories (CategoryID, CategoryName)
   SELECT DISTINCT ((a.CategoryID - 1) * 100) + b.CategoryID, a.CategoryName
   FROM   Northwind..Categories a
   CROSS  JOIN Northwind..Categories b
go
INSERT Customers(CustomerID,
                 CompanyName,
                 ContactName, ContactTitle, Address, City, Region,
                 PostalCode, Country, Phone, Fax)
   SELECT newcustid,
          substring( substring(a.CompanyName, 1, charindex(' ', a.CompanyName)) +
                     substring(b.CompanyName, charindex(' ', b.CompanyName), len(b.CompanyName)),
                    1, 40),
          a.ContactName, b.ContactTitle, a.Address, b.City, a.Region,
          b.PostalCode, a.Country, a.Phone, b.Fax
   FROM   (SELECT newcustid = substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2),
                  acustid = MIN(a.CustomerID), bcustid = MIN(b.CustomerID)
           FROM   Northwind..Customers a
           CROSS  JOIN Northwind..Customers b
           GROUP  BY substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2)) AS x
   JOIN    Northwind..Customers a ON a.CustomerID = x.acustid
   JOIN    Northwind..Customers b ON b.CustomerID = x.bcustid
go
INSERT Employees (EmployeeID, LastName, FirstName, Title,
                  TitleOfCourtesy, BirthDate, HireDate, Address, City,
                  Region, PostalCode, Country, HomePhone, Extension, ReportsTo)
   SELECT DISTINCT ((a.EmployeeID - 1) * 100) + b.EmployeeID, a.LastName, b.FirstName, a.Title,
          b.TitleOfCourtesy, a.BirthDate, b.HireDate, a.Address, b.City,
          a.Region, b.PostalCode, a.Country, b.HomePhone, a.Extension,
          ((a.ReportsTo - 1) * 100) + b.ReportsTo
   FROM   Northwind..Employees a
   CROSS  JOIN Northwind..Employees b
go
INSERT Suppliers (SupplierID,
                  CompanyName,
                  ContactName, ContactTitle, Address, City, Region,
                  PostalCode, Country, Phone, Fax)
   SELECT DISTINCT ((a.SupplierID - 1) * 1000) + b.SupplierID,
          substring( substring(a.CompanyName, 1, charindex(' ', a.CompanyName)) +
                     substring(b.CompanyName, charindex(' ', b.CompanyName), len(b.CompanyName)),
                    1, 40),
           b.ContactName, a.ContactTitle, b.Address, a.City, b.Region,
           a.PostalCode, b.Country, a.Phone, b.Fax
   FROM    Northwind..Suppliers a
   CROSS   JOIN Northwind..Suppliers b
go
INSERT Shippers(ShipperID, CompanyName, Phone)
   SELECT DISTINCT ((a.ShipperID - 1) * 100) + b.ShipperID,
          substring( substring(a.CompanyName, 1, charindex(' ', a.CompanyName)) +
                     substring(b.CompanyName, charindex(' ', b.CompanyName), len(b.CompanyName)),
                    1, 40), b.Phone
   FROM   Northwind..Shippers a
   CROSS  JOIN Northwind..Shippers b
go
INSERT Products (ProductID,
                 ProductName,
                 SupplierID,
                 CategoryID,
                 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                 ReorderLevel, Discontinued)
   SELECT DISTINCT ((a.ProductID - 1) * 100) + b.ProductID,
          substring( substring(a.ProductName, 1, charindex(' ', a.ProductName)) +
                     substring(b.ProductName, charindex(' ', b.ProductName), len(b.ProductName)),
                    1, 40),
          ((a.SupplierID - 1) * 1000) + b.SupplierID,
          ((a.CategoryID - 1) * 100) + b.CategoryID,
          a.QuantityPerUnit, b.UnitPrice, a.UnitsInStock, b.UnitsOnOrder,
          a.ReorderLevel, b.Discontinued
    FROM  Northwind..Products a
    CROSS JOIN  Northwind..Products b
go
INSERT Orders (OrderID,
               CustomerID,
               EmployeeID,
               OrderDate, RequiredDate, ShippedDate,
               ShipVia, Freight, ShipName,
               ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
   SELECT (a.OrderID - (SELECT MIN(OrderID) FROM Northwind..Orders)) * 10000 + b.OrderID,
          substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2),
          ((a.EmployeeID - 1) * 100) + b.EmployeeID,
          a.OrderDate, b.RequiredDate, a.ShippedDate,
          (a.ShipVia - 1) * 100 + b.ShipVia, a.Freight, b.ShipName,
          a.ShipAddress, b.ShipCity, a.ShipRegion, b.ShipPostalCode, a.ShipCountry
   FROM   Northwind..Orders a
   CROSS  JOIN Northwind..Orders b
   WHERE  a.OrderID < b.OrderID
go
INSERT [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
   SELECT (a.OrderID - (SELECT MIN(OrderID) FROM Northwind..Orders)) * 10000 + b.OrderID,
          ((a.ProductID - 1) * 100) + b.ProductID,
          a.UnitPrice, b.Quantity, a.Discount
   FROM   Northwind..[Order Details] a
   CROSS  JOIN Northwind..[Order Details] b
   WHERE  a.OrderID < b.OrderID
go
DBCC DBREINDEX(Categories)
DBCC DBREINDEX(Suppliers)
DBCC DBREINDEX(Employees)
DBCC DBREINDEX(Shippers)
DBCC DBREINDEX(Products)
DBCC DBREINDEX(Customers)
DBCC DBREINDEX(Orders)
DBCC DBREINDEX([Order Details])
go
DBCC SHRINKFILE (Northgale_log2, EMPTYFILE )
ALTER DATABASE Northgale REMOVE FILE Northgale_log2