Categories




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[Categories] (
	[CategoryID] [int] IDENTITY (1 , 1 ) NOT NULL ,
	[CategoryName] [nvarchar] (15 ) NOT NULL ,
	[Description] [ntext] NULL ,
	[Picture] [image] NULL 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

CategoryID
CategoryID
[int]
NOT NULL
 


CategoryName
CategoryName
[nvarchar](15)
NOT NULL
 


Description
Description
[ntext]
NULL
 


Picture
Picture
[image]
NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Categories
PRIMARY
[CategoryID]


[CategoryName]
INDEX
[CategoryName]





Foreign Keys




Name
Field
Parent Table
Parent Field




Storage




STORAGE OPTION
STORAGE VALUE


 
 






CustomerDemographics




Definition
 


Note
 


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






Columns




ColumnName
Domain
Datatype
NULL
Definition

CustomerTypeID
CustomerTypeID
[nchar](10)
NOT NULL
 


CustomerDesc
CustomerDesc
[ntext]
NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_CustomerDemographics
PRIMARY
[CustomerTypeID]





Foreign Keys




Name
Field
Parent Table
Parent Field




Storage




STORAGE OPTION
STORAGE VALUE


 
 






Customers




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[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 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

CustomerID
CustomerID
[nchar](5)
NOT NULL
 


CompanyName
CompanyName
[nvarchar](40)
NOT NULL
 


ContactName
ContactName
[nvarchar](30)
NULL
 


ContactTitle
ContactTitle
[nvarchar](30)
NULL
 


Address
Address
[nvarchar](60)
NULL
 


City
City
[nvarchar](15)
NULL
 


Region
Region
[nvarchar](15)
NULL
 


PostalCode
PostalCode
[nvarchar](10)
NULL
 


Country
Country
[nvarchar](15)
NULL
 


Phone
Phone
[nvarchar](24)
NULL
 


Fax
Fax
[nvarchar](24)
NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Customers
PRIMARY
[CustomerID]


[City]
INDEX
[City]


[CompanyName]
INDEX
[CompanyName]


[PostalCode]
INDEX
[PostalCode]


[Region]
INDEX
[Region]





Foreign Keys




Name
Field
Parent Table
Parent Field




Storage




STORAGE OPTION
STORAGE VALUE


 
 






Employees




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[Employees] (
	[EmployeeID] [int] IDENTITY (1 , 1 ) 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 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

EmployeeID
EmployeeID
[int]
NOT NULL
 


LastName
LastName
[nvarchar](20)
NOT NULL
 


FirstName
FirstName
[nvarchar](10)
NOT NULL
 


Title
Title
[nvarchar](30)
NULL
 


TitleOfCourtesy
TitleOfCourtesy
[nvarchar](25)
NULL
 


BirthDate
BirthDate
[datetime]
NULL
 


HireDate
HireDate
[datetime]
NULL
 


Address
Address
[nvarchar](60)
NULL
 


City
City
[nvarchar](15)
NULL
 


Region
Region
[nvarchar](15)
NULL
 


PostalCode
PostalCode
[nvarchar](10)
NULL
 


Country
Country
[nvarchar](15)
NULL
 


HomePhone
HomePhone
[nvarchar](24)
NULL
 


Extension
Extension
[nvarchar](4)
NULL
 


Photo
Photo
[image]
NULL
 


Notes
Notes
[ntext]
NULL
 


ReportsTo
ReportsTo
[int]
NULL
 


PhotoPath
PhotoPath
[nvarchar](255)
NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Employees
PRIMARY
[EmployeeID]


[LastName]
INDEX
[LastName]


[PostalCode]
INDEX
[PostalCode]





Foreign Keys




Name
Field
Parent Table
Parent Field

FK_Employees_Employees
[ReportsTo]
[dbo].[Employees]
[ReportsTo]





Storage




STORAGE OPTION
STORAGE VALUE


 
 






Region




Definition
 


Note
 


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






Columns




ColumnName
Domain
Datatype
NULL
Definition

RegionID
RegionID
[int]
NOT NULL
 


RegionDescription
RegionDescription
[nchar](50)
NOT NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Region
PRIMARY
[RegionID]





Foreign Keys




Name
Field
Parent Table
Parent Field




Storage




STORAGE OPTION
STORAGE VALUE


 
 






Shippers




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[Shippers] (
	[ShipperID] [int] IDENTITY (1 , 1 ) NOT NULL ,
	[CompanyName] [nvarchar] (40 ) NOT NULL ,
	[Phone] [nvarchar] (24 ) NULL 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

ShipperID
ShipperID
[int]
NOT NULL
 


CompanyName
CompanyName
[nvarchar](40)
NOT NULL
 


Phone
Phone
[nvarchar](24)
NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Shippers
PRIMARY
[ShipperID]





Foreign Keys




Name
Field
Parent Table
Parent Field




Storage




STORAGE OPTION
STORAGE VALUE


 
 






Suppliers




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[Suppliers] (
	[SupplierID] [int] IDENTITY (1 , 1 ) 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 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

SupplierID
SupplierID
[int]
NOT NULL
 


CompanyName
CompanyName
[nvarchar](40)
NOT NULL
 


ContactName
ContactName
[nvarchar](30)
NULL
 


ContactTitle
ContactTitle
[nvarchar](30)
NULL
 


Address
Address
[nvarchar](60)
NULL
 


City
City
[nvarchar](15)
NULL
 


Region
Region
[nvarchar](15)
NULL
 


PostalCode
PostalCode
[nvarchar](10)
NULL
 


Country
Country
[nvarchar](15)
NULL
 


Phone
Phone
[nvarchar](24)
NULL
 


Fax
Fax
[nvarchar](24)
NULL
 


HomePage
HomePage
[ntext]
NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Suppliers
PRIMARY
[SupplierID]


[CompanyName]
INDEX
[CompanyName]


[PostalCode]
INDEX
[PostalCode]





Foreign Keys




Name
Field
Parent Table
Parent Field




Storage




STORAGE OPTION
STORAGE VALUE


 
 






CustomerCustomerDemo




Definition
 


Note
 


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






Columns




ColumnName
Domain
Datatype
NULL
Definition

CustomerID
CustomerID
[nchar](5)
NOT NULL
 


CustomerTypeID
CustomerTypeID
[nchar](10)
NOT NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_CustomerCustomerDemo
PRIMARY
[CustomerID],  [CustomerTypeID]





Foreign Keys




Name
Field
Parent Table
Parent Field

FK_CustomerCustomerDemo
[CustomerTypeID]
[dbo].[CustomerDemographics]
[CustomerTypeID]


FK_CustomerCustomerDemo_Customers
[CustomerID]
[dbo].[Customers]
[CustomerID]





Storage




STORAGE OPTION
STORAGE VALUE


 
 






Orders




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[Orders] (
	[OrderID] [int] IDENTITY (1 , 1 ) NOT NULL ,
	[CustomerID] [nchar] (5 ) NULL ,
	[EmployeeID] [int] NULL ,
	[OrderDate] [datetime] NULL ,
	[RequiredDate] [datetime] NULL ,
	[ShippedDate] [datetime] NULL ,
	[ShipVia] [int] NULL ,
	[Freight] [money] NULL ,
	[ShipName] [nvarchar] (40 ) NULL ,
	[ShipAddress] [nvarchar] (60 ) NULL ,
	[ShipCity] [nvarchar] (15 ) NULL ,
	[ShipRegion] [nvarchar] (15 ) NULL ,
	[ShipPostalCode] [nvarchar] (10 ) NULL ,
	[ShipCountry] [nvarchar] (15 ) NULL 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

OrderID
OrderID
[int]
NOT NULL
 


CustomerID
CustomerID
[nchar](5)
NULL
 


EmployeeID
EmployeeID
[int]
NULL
 


OrderDate
OrderDate
[datetime]
NULL
 


RequiredDate
RequiredDate
[datetime]
NULL
 


ShippedDate
ShippedDate
[datetime]
NULL
 


ShipVia
ShipVia
[int]
NULL
 


Freight
Freight
[money]
NULL
 


ShipName
ShipName
[nvarchar](40)
NULL
 


ShipAddress
ShipAddress
[nvarchar](60)
NULL
 


ShipCity
ShipCity
[nvarchar](15)
NULL
 


ShipRegion
ShipRegion
[nvarchar](15)
NULL
 


ShipPostalCode
ShipPostalCode
[nvarchar](10)
NULL
 


ShipCountry
ShipCountry
[nvarchar](15)
NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Orders
PRIMARY
[OrderID]


[CustomerID]
INDEX
[CustomerID]


[CustomersOrders]
INDEX
[CustomerID]


[EmployeeID]
INDEX
[EmployeeID]


[EmployeesOrders]
INDEX
[EmployeeID]


[OrderDate]
INDEX
[OrderDate]


[ShippedDate]
INDEX
[ShippedDate]


[ShippersOrders]
INDEX
[ShipVia]


[ShipPostalCode]
INDEX
[ShipPostalCode]





Foreign Keys




Name
Field
Parent Table
Parent Field

FK_Orders_Customers
[CustomerID]
[dbo].[Customers]
[CustomerID]


FK_Orders_Employees
[EmployeeID]
[dbo].[Employees]
[EmployeeID]


FK_Orders_Shippers
[ShipVia]
[dbo].[Shippers]
[ShipVia]





Storage




STORAGE OPTION
STORAGE VALUE


 
 






Products




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[Products] (
	[ProductID] [int] IDENTITY (1 , 1 ) NOT NULL ,
	[ProductName] [nvarchar] (40 ) NOT NULL ,
	[SupplierID] [int] NULL ,
	[CategoryID] [int] NULL ,
	[QuantityPerUnit] [nvarchar] (20 ) NULL ,
	[UnitPrice] [money] NULL ,
	[UnitsInStock] [smallint] NULL ,
	[UnitsOnOrder] [smallint] NULL ,
	[ReorderLevel] [smallint] NULL ,
	[Discontinued] [bit] NOT NULL 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

ProductID
ProductID
[int]
NOT NULL
 


ProductName
ProductName
[nvarchar](40)
NOT NULL
 


SupplierID
SupplierID
[int]
NULL
 


CategoryID
CategoryID
[int]
NULL
 


QuantityPerUnit
QuantityPerUnit
[nvarchar](20)
NULL
 


UnitPrice
UnitPrice
[money]
NULL
 


UnitsInStock
UnitsInStock
[smallint]
NULL
 


UnitsOnOrder
UnitsOnOrder
[smallint]
NULL
 


ReorderLevel
ReorderLevel
[smallint]
NULL
 


Discontinued
Discontinued
[bit]
NOT NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Products
PRIMARY
[ProductID]


[CategoriesProducts]
INDEX
[CategoryID]


[CategoryID]
INDEX
[CategoryID]


[ProductName]
INDEX
[ProductName]


[SupplierID]
INDEX
[SupplierID]


[SuppliersProducts]
INDEX
[SupplierID]





Foreign Keys




Name
Field
Parent Table
Parent Field

FK_Products_Categories
[CategoryID]
[dbo].[Categories]
[CategoryID]


FK_Products_Suppliers
[SupplierID]
[dbo].[Suppliers]
[SupplierID]





Storage




STORAGE OPTION
STORAGE VALUE


 
 






Territories




Definition
 


Note
 


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






Columns




ColumnName
Domain
Datatype
NULL
Definition

TerritoryID
TerritoryID
[nvarchar](20)
NOT NULL
 


TerritoryDescription
TerritoryDescription
[nchar](50)
NOT NULL
 


RegionID
RegionID
[int]
NOT NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Territories
PRIMARY
[TerritoryID]





Foreign Keys




Name
Field
Parent Table
Parent Field

FK_Territories_Region
[RegionID]
[dbo].[Region]
[RegionID]





Storage




STORAGE OPTION
STORAGE VALUE


 
 






EmployeeTerritories




Definition
 


Note
 


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






Columns




ColumnName
Domain
Datatype
NULL
Definition

EmployeeID
EmployeeID
[int]
NOT NULL
 


TerritoryID
TerritoryID
[nvarchar](20)
NOT NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_EmployeeTerritories
PRIMARY
[EmployeeID],  [TerritoryID]





Foreign Keys




Name
Field
Parent Table
Parent Field

FK_EmployeeTerritories_Employees
[EmployeeID]
[dbo].[Employees]
[EmployeeID]


FK_EmployeeTerritories_Territories
[TerritoryID]
[dbo].[Territories]
[TerritoryID]





Storage




STORAGE OPTION
STORAGE VALUE


 
 






Order Details




Definition
 


Note
 


DDL Code
CREATE TABLE [dbo].[Order Details] (
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[UnitPrice] [money] NOT NULL ,
	[Quantity] [smallint] NOT NULL ,
	[Discount] [real] NOT NULL 
)






Columns




ColumnName
Domain
Datatype
NULL
Definition

OrderID
OrderID
[int]
NOT NULL
 


ProductID
ProductID
[int]
NOT NULL
 


UnitPrice
UnitPrice
[money]
NOT NULL
 


Quantity
Quantity
[smallint]
NOT NULL
 


Discount
Discount
[real]
NOT NULL
 





Primary and Unique Keys




Key Name
Key Type
Keys


PK_Order_Details
PRIMARY
[OrderID],  [ProductID]


[OrderID]
INDEX
[OrderID]


[OrdersOrder_Details]
INDEX
[OrderID]


[ProductID]
INDEX
[ProductID]


[ProductsOrder_Details]
INDEX
[ProductID]





Foreign Keys




Name
Field
Parent Table
Parent Field

FK_Order_Details_Orders
[OrderID]
[dbo].[Orders]
[OrderID]


FK_Order_Details_Products
[ProductID]
[dbo].[Products]
[ProductID]





Storage




STORAGE OPTION
STORAGE VALUE