Northwind

Microsoft sample database14/07/2008
4.25 MB

Summary

• Tables (14)
  • dbo.Categories
  • dbo.CustomerCustomerDemo
  • dbo.CustomerDemographics
  • dbo.Customers (go to dbdesc.com to register)
  • dbo.Employees (go to dbdesc.com to register)
  • dbo.EmployeeTerritories (go to dbdesc.com to register)
  • dbo.Order Details (go to dbdesc.com to register)
  • dbo.Orders (go to dbdesc.com to register)
  • dbo.Products (go to dbdesc.com to register)
  • dbo.Region (go to dbdesc.com to register)
  • dbo.Shippers (go to dbdesc.com to register)
  • dbo.Suppliers (go to dbdesc.com to register)
  • dbo.Territories (go to dbdesc.com to register)
  • dbo.TestTable (go to dbdesc.com to register)
• Views (16)
  • dbo.Alphabetical list of products
  • dbo.Category Sales for 1997
  • dbo.Current Product List
  • dbo.Customer and Suppliers by City
  • dbo.Invoices
  • dbo.Order Details Extended
  • dbo.Order Subtotals
  • dbo.Orders Qry
  • dbo.Product Sales for 1997
  • dbo.Products Above Average Price
  • dbo.Products by Category
  • dbo.Quarterly Orders
  • dbo.Sales by Category
  • dbo.Sales Totals by Amount
  • dbo.Summary of Sales by Quarter
  • dbo.Summary of Sales by Year
• Stored procedures (7)
  • dbo.CustOrderHist
  • dbo.CustOrdersDetail
  • dbo.CustOrdersOrders
  • dbo.Employee Sales by Country
  • dbo.Sales by Year
  • dbo.SalesByCategory
  • dbo.Ten Most Expensive Products
• User-defined types (1)
  • TestType
• Users (2)
  • dbo
  • guest
• Roles (10)
  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin
  • public

Tables

Table: dbo.Categories

Fields

	Field name	Data type	Nullable	Default value	Field description
	CategoryID	int			Categories of Northwind products.
	CategoryName	nvarchar (15)			Name of food category.
	Description	ntext	Yes		Full description of the category. Provide samples.
	Picture	image	Yes		A picture representing the food category.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.Products	FK_Products_Categories	PK_Categories

Indices

Index name	Column name	Sort direction	Unique	Index type
CategoryName	CategoryName	ASC		NONCLUSTERED
PK_Categories	CategoryID	ASC	Yes	CLUSTERED

Field extended properties:

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Extended properties

Author	John Smith
CustomProperty1	Sample value to test extended properties

Related objects

dbo.Alphabetical list of products
dbo.Product Sales for 1997
dbo.Products by Category
dbo.Sales by Category
dbo.SalesByCategory

Table definition

CREATE TABLE [dbo.Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[Description] [ntext] COLLATE Modern_Spanish_CI_AS NULL ,
	[Picture] [image] NULL ,
	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 
	(
		[CategoryID]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Table: dbo.CustomerCustomerDemo

Fields

	Field name	Data type	Nullable	Default value	Field description
	CustomerID	nchar (5)			234
	CustomerTypeID	nchar (10)			234

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_CustomerCustomerDemo_Customers	CustomerID	PK_Customers (dbo.Customers)
FK_CustomerCustomerDemo	CustomerTypeID	PK_CustomerDemographics (dbo.CustomerDemographics)

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_CustomerCustomerDemo	CustomerID	ASC	Yes	NONCLUSTERED
	CustomerTypeID	ASC	Yes	NONCLUSTERED

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Table definition

CREATE TABLE [dbo.CustomerCustomerDemo] (
	[CustomerID] [nchar] (5) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[CustomerTypeID] [nchar] (10) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerID],
		[CustomerTypeID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY 
	(
		[CustomerTypeID]
	) REFERENCES [CustomerDemographics] (
		[CustomerTypeID]
	),
	CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [Customers] (
		[CustomerID]
	)
) ON [PRIMARY]
GO

Table: dbo.CustomerDemographics

Fields

	Field name	Data type	Nullable	Default value	Field description
	CustomerTypeID	nchar (10)			123465
	CustomerDesc	ntext	Yes		sdfg

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.CustomerCustomerDemo	FK_CustomerCustomerDemo	PK_CustomerDemographics

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_CustomerDemographics	CustomerTypeID	ASC	Yes	NONCLUSTERED

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Table definition

CREATE TABLE [dbo.CustomerDemographics] (
	[CustomerTypeID] [nchar] (10) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[CustomerDesc] [ntext] COLLATE Modern_Spanish_CI_AS NULL ,
	CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerTypeID]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Table: dbo.Customers (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	CustomerID	nchar (5)			Unique five-character code based on customer name.
	CompanyName	nvarchar (40)			sdfg
	ContactName	nvarchar (30)	Yes		sdg
	ContactTitle	nvarchar (30)	Yes		sdfg
	Address	nvarchar (60)	Yes		Street or post-office box.
	City	nvarchar (15)	Yes
	Region	nvarchar (15)	Yes		State or province.
	PostalCode	nvarchar (10)	Yes
	Country	nvarchar (15)	Yes
	Phone	nvarchar (24)	Yes		Phone number includes country code or area code.
	Fax	nvarchar (24)	Yes		Phone number includes country code or area code.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.CustomerCustomerDemo	FK_CustomerCustomerDemo_Customers	PK_Customers
dbo.Orders	FK_Orders_Customers	PK_Customers

Indices

Index name	Column name	Sort direction	Unique	Index type
City	City	ASC		NONCLUSTERED
CompanyName	CompanyName	ASC		NONCLUSTERED
IX_Customers	CompanyName	ASC		NONCLUSTERED
PK_Customers	CustomerID	ASC	Yes	CLUSTERED
PostalCode	PostalCode	ASC		NONCLUSTERED
Region	Region	ASC		NONCLUSTERED

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Extended properties

Author	John Smith
CustomProperty1	Sample value to test extended properties

Related objects

dbo.Customer and Suppliers by City
dbo.CustOrderHist
dbo.Invoices
dbo.Orders Qry
dbo.Quarterly Orders
dbo.Sales Totals by Amount

Table definition

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

Table: dbo.Employees (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	EmployeeID	int			Number automatically assigned to new employee.
	LastName	nvarchar (20)
	FirstName	nvarchar (10)
	Title	nvarchar (30)	Yes		Employee's title.
	TitleOfCourtesy	nvarchar (25)	Yes
	BirthDate	datetime	Yes
	HireDate	datetime	Yes
	Address	nvarchar (60)	Yes		Street or post-office box.
	City	nvarchar (15)	Yes
	Region	nvarchar (15)	Yes		Street or post-office box.
	PostalCode	nvarchar (10)	Yes
	Country	nvarchar (15)	Yes
	HomePhone	nvarchar (24)	Yes		Phone number includes country code or area code.
	Extension	nvarchar (4)	Yes		Internal telephone extension number.
	Photo	image	Yes		Picture of employee.
	Notes	ntext	Yes		General information about employee's background.
	ReportsTo	int	Yes		Employee's supervisor.
	PhotoPath	nvarchar (255)	Yes

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Employees_Employees	ReportsTo	PK_Employees (dbo.Employees)

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.Employees	FK_Employees_Employees	PK_Employees
dbo.EmployeeTerritories	FK_EmployeeTerritories_Employees	PK_Employees
dbo.Orders	FK_Orders_Employees	PK_Employees

Indices

Index name	Column name	Sort direction	Unique	Index type
LastName	LastName	ASC		NONCLUSTERED
PK_Employees	EmployeeID	ASC	Yes	CLUSTERED
	PostalCode	ASC		NONCLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Birthdate	BirthDate	([BirthDate] < getdate())

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Employee Sales by Country
dbo.Invoices

Table definition

CREATE TABLE [dbo.Employees] (
	[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
	[LastName] [nvarchar] (20) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[FirstName] [nvarchar] (10) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[Title] [nvarchar] (30) COLLATE Modern_Spanish_CI_AS NULL ,
	[TitleOfCourtesy] [nvarchar] (25) COLLATE Modern_Spanish_CI_AS NULL ,
	[BirthDate] [datetime] NULL ,
	[HireDate] [datetime] NULL ,
	[Address] [nvarchar] (60) COLLATE Modern_Spanish_CI_AS NULL ,
	[City] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[Region] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[PostalCode] [nvarchar] (10) COLLATE Modern_Spanish_CI_AS NULL ,
	[Country] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[HomePhone] [nvarchar] (24) COLLATE Modern_Spanish_CI_AS NULL ,
	[Extension] [nvarchar] (4) COLLATE Modern_Spanish_CI_AS NULL ,
	[Photo] [image] NULL ,
	[Notes] [ntext] COLLATE Modern_Spanish_CI_AS NULL ,
	[ReportsTo] [int] NULL ,
	[PhotoPath] [nvarchar] (255) COLLATE Modern_Spanish_CI_AS NULL ,
	CONSTRAINT [PK_Employees] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Employees_Employees] FOREIGN KEY 
	(
		[ReportsTo]
	) REFERENCES [Employees] (
		[EmployeeID]
	),
	CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Table: dbo.EmployeeTerritories (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	EmployeeID	int
	TerritoryID	nvarchar (20)

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_EmployeeTerritories_Employees	EmployeeID	PK_Employees (dbo.Employees)
FK_EmployeeTerritories_Territories	TerritoryID	PK_Territories (dbo.Territories)

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_EmployeeTerritories	EmployeeID	ASC	Yes	NONCLUSTERED
	TerritoryID	ASC	Yes	NONCLUSTERED

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Table definition

CREATE TABLE [dbo.EmployeeTerritories] (
	[EmployeeID] [int] NOT NULL ,
	[TerritoryID] [nvarchar] (20) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED 
	(
		[EmployeeID],
		[TerritoryID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [Employees] (
		[EmployeeID]
	),
	CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY 
	(
		[TerritoryID]
	) REFERENCES [Territories] (
		[TerritoryID]
	)
) ON [PRIMARY]
GO

Table: dbo.Order Details (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	OrderID	int			Same as Order ID in Orders table.
	ProductID	int			Same as Product ID in Products table.
	UnitPrice	money		(0)
	Quantity	smallint		(1)
	Discount	real		(0)

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Order_Details_Orders	OrderID	PK_Orders (dbo.Orders)
FK_Order_Details_Products	ProductID	PK_Products (dbo.Products)

Indices

Index name	Column name	Sort direction	Unique	Index type
OrderID	OrderID	ASC		NONCLUSTERED
OrdersOrder_Details	OrderID	ASC		NONCLUSTERED
PK_Order_Details	OrderID	ASC	Yes	CLUSTERED
	ProductID	ASC	Yes	CLUSTERED
ProductID	ProductID	ASC		NONCLUSTERED
ProductsOrder_Details	ProductID	ASC		NONCLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Discount	Discount	([Discount] >= 0 and [Discount] <= 1)
CK_Quantity	Quantity	([Quantity] > 0)
CK_UnitPrice	UnitPrice	([UnitPrice] >= 0)

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.CustOrderHist
dbo.CustOrdersDetail
dbo.Invoices
dbo.Order Details Extended
dbo.Order Subtotals
dbo.Product Sales for 1997
dbo.SalesByCategory

Table definition

CREATE TABLE [dbo.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]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [Orders] (
		[OrderID]
	),
	CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [Products] (
		[ProductID]
	),
	CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
	CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
	CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
) ON [PRIMARY]
GO

Table: dbo.Orders (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	OrderID	int
	CustomerID	nchar (5)	Yes
	EmployeeID	int	Yes		Same entry as in Employees table.
	OrderDate	datetime	Yes
	RequiredDate	datetime	Yes
	ShippedDate	datetime	Yes
	ShipVia	int	Yes		Same as Shipper ID in Shippers table.
	Freight	money	Yes	(0)
	ShipName	nvarchar (40)	Yes		Name of person or company to receive the shipment.
	ShipAddress	nvarchar (60)	Yes		Street address only -- no post-office box allowed.
	ShipCity	nvarchar (15)	Yes
	ShipRegion	nvarchar (15)	Yes		State or province.
	ShipPostalCode	nvarchar (10)	Yes
	ShipCountry	nvarchar (15)	Yes

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Orders_Customers	CustomerID	PK_Customers (dbo.Customers)
FK_Orders_Employees	EmployeeID	PK_Employees (dbo.Employees)
FK_Orders_Shippers	ShipVia	PK_Shippers (dbo.Shippers)

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.Order Details	FK_Order_Details_Orders	PK_Orders

Indices

Index name	Column name	Sort direction	Unique	Index type
CustomerID	CustomerID	ASC		NONCLUSTERED
CustomersOrders	CustomerID	ASC		NONCLUSTERED
EmployeeID	EmployeeID	ASC		NONCLUSTERED
EmployeesOrders	EmployeeID	ASC		NONCLUSTERED
OrderDate	OrderDate	ASC		NONCLUSTERED
PK_Orders	OrderID	ASC	Yes	CLUSTERED
ShippedDate	ShippedDate	ASC		NONCLUSTERED
ShippersOrders	ShipVia	ASC		NONCLUSTERED
ShipPostalCode	ShipPostalCode	ASC		NONCLUSTERED

Field extended properties:

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.CustOrderHist
dbo.CustOrdersOrders
dbo.Employee Sales by Country
dbo.Invoices
dbo.Orders Qry
dbo.Product Sales for 1997
dbo.Quarterly Orders
dbo.Sales by Category
dbo.Sales by Year
dbo.Sales Totals by Amount
dbo.SalesByCategory
dbo.Summary of Sales by Quarter
dbo.Summary of Sales by Year

Table definition

CREATE TABLE [dbo.Orders] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [nchar] (5) COLLATE Modern_Spanish_CI_AS 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) COLLATE Modern_Spanish_CI_AS NULL ,
	[ShipAddress] [nvarchar] (60) COLLATE Modern_Spanish_CI_AS NULL ,
	[ShipCity] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[ShipRegion] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[ShipPostalCode] [nvarchar] (10) COLLATE Modern_Spanish_CI_AS NULL ,
	[ShipCountry] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [Customers] (
		[CustomerID]
	),
	CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [Employees] (
		[EmployeeID]
	),
	CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 
	(
		[ShipVia]
	) REFERENCES [Shippers] (
		[ShipperID]
	)
) ON [PRIMARY]
GO

Table: dbo.Products (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductID	int			Number automatically assigned to new product.
	ProductName	nvarchar (40)
	SupplierID	int	Yes		Same entry as in Suppliers table.
	CategoryID	int	Yes		Same entry as in Categories table.
	QuantityPerUnit	nvarchar (20)	Yes		(e.g., 24-count case, 1-liter bottle).
	UnitPrice	money	Yes	(0)
	UnitsInStock	smallint	Yes	(0)
	UnitsOnOrder	smallint	Yes	(0)
	ReorderLevel	smallint	Yes	(0)	Minimum units to maintain in stock.
	Discontinued	bit		(0)	Yes means item is no longer available.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Products_Suppliers	SupplierID	PK_Suppliers (dbo.Suppliers)
FK_Products_Categories	CategoryID	PK_Categories (dbo.Categories)

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.Order Details	FK_Order_Details_Products	PK_Products

Indices

Index name	Column name	Sort direction	Unique	Index type
CategoriesProducts	CategoryID	ASC		NONCLUSTERED
CategoryID	CategoryID	ASC		NONCLUSTERED
PK_Products	ProductID	ASC	Yes	CLUSTERED
ProductName	ProductName	ASC		NONCLUSTERED
SupplierID	SupplierID	ASC		NONCLUSTERED
SuppliersProducts	SupplierID	ASC		NONCLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Products_UnitPrice	UnitPrice	([UnitPrice] >= 0)
CK_ReorderLevel	ReorderLevel	([ReorderLevel] >= 0)
CK_UnitsInStock	UnitsInStock	([UnitsInStock] >= 0)
CK_UnitsOnOrder	UnitsOnOrder	([UnitsOnOrder] >= 0)

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Alphabetical list of products
dbo.Current Product List
dbo.CustOrderHist
dbo.CustOrdersDetail
dbo.Invoices
dbo.Order Details Extended
dbo.Product Sales for 1997
dbo.Products Above Average Price
dbo.Products by Category
dbo.Sales by Category
dbo.SalesByCategory
dbo.Ten Most Expensive Products

Table definition

CREATE TABLE [dbo.Products] (
	[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductName] [nvarchar] (40) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[SupplierID] [int] NULL ,
	[CategoryID] [int] NULL ,
	[QuantityPerUnit] [nvarchar] (20) COLLATE Modern_Spanish_CI_AS 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]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Products_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [Categories] (
		[CategoryID]
	),
	CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY 
	(
		[SupplierID]
	) REFERENCES [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)
) ON [PRIMARY]
GO

Table: dbo.Region (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	RegionID	int
	RegionDescription	nchar (50)

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.Territories	FK_Territories_Region	PK_Region

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_Region	RegionID	ASC	Yes	NONCLUSTERED

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Table definition

CREATE TABLE [dbo.Region] (
	[RegionID] [int] NOT NULL ,
	[RegionDescription] [nchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED 
	(
		[RegionID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: dbo.Shippers (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	ShipperID	int			Number automatically assigned to new shipper.
	CompanyName	nvarchar (40)			Name of shipping company.
	Phone	nvarchar (24)	Yes		Phone number includes country code or area code.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.Orders	FK_Orders_Shippers	PK_Shippers

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_Shippers	ShipperID	ASC	Yes	CLUSTERED

Related objects

dbo.Invoices

Table definition

CREATE TABLE [dbo.Shippers] (
	[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] [nvarchar] (40) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[Phone] [nvarchar] (24) COLLATE Modern_Spanish_CI_AS NULL ,
	CONSTRAINT [PK_Shippers] PRIMARY KEY  CLUSTERED 
	(
		[ShipperID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: dbo.Suppliers (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	SupplierID	int			Number automatically assigned to new supplier.
	CompanyName	nvarchar (40)
	ContactName	nvarchar (30)	Yes
	ContactTitle	nvarchar (30)	Yes
	Address	nvarchar (60)	Yes		Street or post-office box.
	City	nvarchar (15)	Yes
	Region	nvarchar (15)	Yes		State or province.
	PostalCode	nvarchar (10)	Yes
	Country	nvarchar (15)	Yes
	Phone	nvarchar (24)	Yes		Phone number includes country code or area code.
	Fax	nvarchar (24)	Yes		Phone number includes country code or area code.
	HomePage	ntext	Yes		Supplier's home page on World Wide Web.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.Products	FK_Products_Suppliers	PK_Suppliers

Indices

Index name	Column name	Sort direction	Unique	Index type
	CompanyName	ASC		NONCLUSTERED
PK_Suppliers	SupplierID	ASC	Yes	CLUSTERED
	PostalCode	ASC		NONCLUSTERED

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Customer and Suppliers by City

Table definition

CREATE TABLE [dbo.Suppliers] (
	[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] [nvarchar] (40) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[ContactName] [nvarchar] (30) COLLATE Modern_Spanish_CI_AS NULL ,
	[ContactTitle] [nvarchar] (30) COLLATE Modern_Spanish_CI_AS NULL ,
	[Address] [nvarchar] (60) COLLATE Modern_Spanish_CI_AS NULL ,
	[City] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[Region] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[PostalCode] [nvarchar] (10) COLLATE Modern_Spanish_CI_AS NULL ,
	[Country] [nvarchar] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[Phone] [nvarchar] (24) COLLATE Modern_Spanish_CI_AS NULL ,
	[Fax] [nvarchar] (24) COLLATE Modern_Spanish_CI_AS NULL ,
	[HomePage] [ntext] COLLATE Modern_Spanish_CI_AS NULL ,
	CONSTRAINT [PK_Suppliers] PRIMARY KEY  CLUSTERED 
	(
		[SupplierID]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Table: dbo.Territories (go to dbdesc.com to register)

Fields

	Field name	Data type	Nullable	Default value	Field description
	TerritoryID	nvarchar (20)			sdfg
	TerritoryDescription	nchar (50)			sdfg
	RegionID	int			sdfg

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Territories_Region	RegionID	PK_Region (dbo.Region)

Referencing tables

Table name	Foreign key	Primary key or unique constraint
dbo.EmployeeTerritories	FK_EmployeeTerritories_Territories	PK_Territories

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_Territories	TerritoryID	ASC	Yes	NONCLUSTERED

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Table definition

CREATE TABLE [dbo.Territories] (
	[TerritoryID] [nvarchar] (20) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[TerritoryDescription] [nchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
	[RegionID] [int] NOT NULL ,
	CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED 
	(
		[TerritoryID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Territories_Region] FOREIGN KEY 
	(
		[RegionID]
	) REFERENCES [Region] (
		[RegionID]
	)
) ON [PRIMARY]
GO

Table: dbo.TestTable (go to dbdesc.com to register)

Fields

Table definition

CREATE TABLE [dbo.TestTable] (
	[TestCustomField] [TestType] NULL 
) ON [PRIMARY]
GO

Views

View: dbo.Alphabetical list of products

Fields

Source	Field name	Data type	Nullable	Field description
dbo.Products	ProductID	int
dbo.Products	ProductName	nvarchar (40)
dbo.Products	SupplierID	int	Yes
dbo.Products	CategoryID	int	Yes
dbo.Products	QuantityPerUnit	nvarchar (20)	Yes
dbo.Products	UnitPrice	money	Yes
dbo.Products	UnitsInStock	smallint	Yes
dbo.Products	UnitsOnOrder	smallint	Yes
dbo.Products	ReorderLevel	smallint	Yes
dbo.Products	Discontinued	bit
dbo.Categories	CategoryName	nvarchar (15)

create view "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Products
dbo.Categories

View: dbo.Category Sales for 1997

Fields

Source	Field name	Data type	Nullable	Field description
dbo.Product Sales for 1997	CategoryName	nvarchar (15)		CatName
	CategorySales	money	Yes	CatSales

create view "Category Sales for 1997" AS
SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales
FROM "Product Sales for 1997"
GROUP BY "Product Sales for 1997".CategoryName

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Product Sales for 1997

View: dbo.Current Product List

Fields

Source	Field name	Data type	Nullable	Field description
dbo.Products	ProductID	int
dbo.Products	ProductName	nvarchar (40)

create view "Current Product List" AS
SELECT Product_List.ProductID, Product_List.ProductName
FROM Products AS Product_List
WHERE (((Product_List.Discontinued)=0))
--ORDER BY Product_List.ProductName

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Products

View: dbo.Customer and Suppliers by City

Fields

Source	Field name	Data type	Nullable	Field description
dbo.Customers	City	nvarchar (15)	Yes
dbo.Customers	CompanyName	nvarchar (40)
dbo.Customers	ContactName	nvarchar (30)	Yes
	Relationship	varchar (9)

create view "Customer and Suppliers by City" AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship 
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Suppliers
dbo.Customers

View: dbo.Invoices

Fields

Source	Field name	Data type	Nullable	Field description
dbo.Orders	ShipName	nvarchar (40)	Yes	Name of the shipper
dbo.Orders	ShipAddress	nvarchar (60)	Yes
dbo.Orders	ShipCity	nvarchar (15)	Yes
dbo.Orders	ShipRegion	nvarchar (15)	Yes
dbo.Orders	ShipPostalCode	nvarchar (10)	Yes
dbo.Orders	ShipCountry	nvarchar (15)	Yes
dbo.Orders	CustomerID	nchar (5)	Yes
	CustomerName	nvarchar (40)
dbo.Customers	Address	nvarchar (60)	Yes
dbo.Customers	City	nvarchar (15)	Yes
dbo.Customers	Region	nvarchar (15)	Yes
dbo.Customers	PostalCode	nvarchar (10)	Yes
dbo.Customers	Country	nvarchar (15)	Yes
	Salesperson	nvarchar (31)
dbo.Orders	OrderID	int
dbo.Orders	OrderDate	datetime	Yes
dbo.Orders	RequiredDate	datetime	Yes
dbo.Orders	ShippedDate	datetime	Yes
	ShipperName	nvarchar (40)
dbo.Products	ProductID	int
dbo.Products	ProductName	nvarchar (40)
dbo.Order Details	UnitPrice	money
dbo.Order Details	Quantity	smallint
dbo.Order Details	Discount	real
	ExtendedPrice	money	Yes
dbo.Orders	Freight	money	Yes

create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName + ' ' + LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, 
	"Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN 
		(Products INNER JOIN 
			(
				(Employees INNER JOIN 
					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
		ON Products.ProductID = "Order Details".ProductID) 
	ON Shippers.ShipperID = Orders.ShipVia

Object permissions

	User / Role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI
	public

Related objects

dbo.Customers
dbo.Shippers
dbo.Employees
dbo.Order Details
dbo.Products
dbo.Orders

View: dbo.Order Details Extended

Fields

Source	Field name	Data type	Nullable	Field description
dbo.Order Details	OrderID	int
dbo.Products	ProductID	int
dbo.Products	ProductName	nvarchar (40)
dbo.Order Details	UnitPrice	money
dbo.Order Details	Quantity	smallint
dbo.Order Details	Discount	real
	ExtendedPrice	money	Yes

create view "Order Details Extended" AS
SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName, 
	"Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)100))*100) AS ExtendedPrice
FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID
--ORDER BY "Order Details".OrderID

Object permissions