Northwind
database
Northwind database summary
Microsoft sample database
Database server:
server1
Database size:
4.25 MB
- 14 tables
- 16 views
- 7 stored procedures
- 0 user defined functions
- 1 user defined data types
- 0 XML schemas
- 0 assemblies
Table dependencies matrix
User defined data types
- TestType ( varchar
(20)
, not null )
,
Users
| dbo | sa | 06 Ago 2000 | db_owner | |
| guest | | 06 Ago 2000 | | |
Roles
| db_accessadmin | | | |
| db_backupoperator | | | |
| db_datareader | | | |
| db_datawriter | | | |
| db_ddladmin | | | |
| db_denydatareader | | | |
| db_denydatawriter | | | |
| db_owner | | dbo | |
| db_securityadmin | | | |
| public | | | |
Table:
dbo.Categories
List of categories
Columns
(4)
| PK |
| IX | CategoryID | int | | | | Categories of Northwind products. |
|
|
| IX | 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. |
Indexes
| CategoryName | CategoryName | ASC | | NONCLUSTERED |
| PK_Categories | CategoryID | ASC | Yes | CLUSTERED |
Object permissions
| public | grant | grant | grant | grant | | grant |
Extended properties
| John Smith |
| Sample value to test extended properties |
Objects that depend on dbo.Categories
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
Test description
Columns
(2)
| PK | FK | IX | CustomerID | nchar |
(5)
| | | 234 |
| PK | FK | IX | CustomerTypeID | nchar |
(10)
| | | 234 |
Indexes
| PK_CustomerCustomerDemo | CustomerID | ASC | Yes | NONCLUSTERED |
| CustomerTypeID | ASC | Yes | NONCLUSTERED |
Foreign keys dependencies:
FK_CustomerCustomerDemo_Customers:
CustomerID relies upon remote
PK_Customers
(dbo.Customers)
FK_CustomerCustomerDemo:
CustomerTypeID relies upon remote
PK_CustomerDemographics
(dbo.CustomerDemographics)
Object permissions
| public | grant | grant | grant | grant | | grant |
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 [dbo].[CustomerDemographics] (
[CustomerTypeID]
),
CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
)
) ON [PRIMARY]
GO
Table:
dbo.CustomerDemographics
Sample annotation for table CustomerDemographics
Columns
(2)
| PK |
| IX | CustomerTypeID | nchar |
(10)
| | | 123465 |
|
|
|
| CustomerDesc | ntext | | Yes | | sdfg |
Indexes
| PK_CustomerDemographics | CustomerTypeID | ASC | Yes | NONCLUSTERED |
Object permissions
| public | grant | grant | grant | grant | | grant |
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 (demo version, please register)
Customers' names and related data
Columns
(11)
| PK |
| IX | CustomerID | nchar |
(5)
| | | Unique five-character code based on customer name. |
|
|
| IX | CompanyName | nvarchar |
(40)
| | | sdfg |
|
|
|
| ContactName | nvarchar |
(30)
| Yes | | sdg |
|
|
|
| ContactTitle | nvarchar |
(30)
| Yes | | sdfg |
|
|
|
| Address | nvarchar |
(60)
| Yes | | Street or post-office box. |
|
|
| IX | City | nvarchar |
(15)
| Yes | | |
|
|
| IX | Region | nvarchar |
(15)
| Yes | | State or province. |
|
|
| IX | 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. |
Indexes
| 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
| public | grant | grant | grant | grant | | grant |
Extended properties
| John Smith |
| Sample value to test extended properties |
Objects that depend on dbo.Customers (demo version, please register)
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 (demo version, please register)
Employees' names, titles, and personal information.
Columns
(18)
| PK |
| IX | EmployeeID | int | | | | Number automatically assigned to new employee. |
|
|
| IX | 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. |
|
|
| IX | 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. |
|
| FK |
| ReportsTo | int | | Yes | | Employee's supervisor. |
|
|
|
| PhotoPath | nvarchar |
(255)
| Yes | | |
Indexes
| LastName | LastName | ASC | | NONCLUSTERED |
| PK_Employees | EmployeeID | ASC | Yes | CLUSTERED |
| PostalCode | ASC | | NONCLUSTERED |
Foreign keys dependencies:
FK_Employees_Employees:
ReportsTo relies upon remote
PK_Employees
(dbo.Employees)
Check constraints:
| CK_Birthdate | BirthDate | ([BirthDate] < getdate()) |
Object permissions
| public | grant | grant | grant | grant | | grant |
Objects that depend on dbo.Employees (demo version, please register)
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 [dbo].[Employees] (
[EmployeeID]
),
CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Table:
dbo.EmployeeTerritories (demo version, please register)
Columns
(2)
| PK | FK | IX | EmployeeID | int | | | | |
| PK | FK | IX | TerritoryID | nvarchar |
(20)
| | | |
Indexes
| PK_EmployeeTerritories | EmployeeID | ASC | Yes | NONCLUSTERED |
| TerritoryID | ASC | Yes | NONCLUSTERED |
Foreign keys dependencies:
FK_EmployeeTerritories_Employees:
EmployeeID relies upon remote
PK_Employees
(dbo.Employees)
FK_EmployeeTerritories_Territories:
TerritoryID relies upon remote
PK_Territories
(dbo.Territories)
Object permissions
| public | grant | grant | grant | grant | | grant |
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 [dbo].[Employees] (
[EmployeeID]
),
CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
(
[TerritoryID]
) REFERENCES [dbo].[Territories] (
[TerritoryID]
)
) ON [PRIMARY]
GO
Table:
dbo.Order Details (demo version, please register)
Details on products, quantities, and prices for each order in the Orders table.
Columns
(5)
| PK | FK | IX | OrderID | int | | | | Same as Order ID in Orders table. |
| PK | FK | IX | ProductID | int | | | | Same as Product ID in Products table. |
|
|
|
| UnitPrice | money | | | (0) | |
|
|
|
| Quantity | smallint | | | (1) | |
|
|
|
| Discount | real | | | (0) | |
Indexes
| 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 |
Foreign keys dependencies:
FK_Order_Details_Orders:
OrderID relies upon remote
PK_Orders
(dbo.Orders)
FK_Order_Details_Products:
ProductID relies upon remote
PK_Products
(dbo.Products)
Check constraints:
| CK_Discount | Discount | ([Discount] >= 0 and [Discount] <= 1) |
| CK_Quantity | Quantity | ([Quantity] > 0) |
| CK_UnitPrice | UnitPrice | ([UnitPrice] >= 0) |
Object permissions
| public | grant | grant | grant | grant | | grant |
Objects that depend on dbo.Order Details (demo version, please register)
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 [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)
) ON [PRIMARY]
GO
Table:
dbo.Orders (demo version, please register)
Customer name, order date, and freight charge for each order.
Columns
(14)
| PK |
| IX | OrderID | int | | | | |
|
| FK | IX | CustomerID | nchar |
(5)
| Yes | | |
|
| FK | IX | EmployeeID | int | | Yes | | Same entry as in Employees table. |
|
|
| IX | OrderDate | datetime | | Yes | | |
|
|
|
| RequiredDate | datetime | | Yes | | |
|
|
| IX | ShippedDate | datetime | | Yes | | |
|
| FK | IX | 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. |
|
|
| IX | ShipPostalCode | nvarchar |
(10)
| Yes | | |
|
|
|
| ShipCountry | nvarchar |
(15)
| Yes | | |
Indexes
| 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 |
Foreign keys dependencies:
FK_Orders_Customers:
CustomerID relies upon remote
PK_Customers
(dbo.Customers)
FK_Orders_Employees:
EmployeeID relies upon remote
PK_Employees
(dbo.Employees)
FK_Orders_Shippers:
ShipVia relies upon remote
PK_Shippers
(dbo.Shippers)
Object permissions
| public | grant | grant | grant | grant | | grant |
Objects that depend on dbo.Orders (demo version, please register)
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 [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]
)
) ON [PRIMARY]
GO
Table:
dbo.Products (demo version, please register)
Product names, suppliers, prices, and units in stock.
Columns
(10)
| PK |
| IX | ProductID | int | | | | Number automatically assigned to new product. |
|
|
| IX | ProductName | nvarchar |
(40)
| | | |
|
| FK | IX | SupplierID | int | | Yes | | Same entry as in Suppliers table. |
|
| FK | IX | 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. |
Indexes
| 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 |
Foreign keys dependencies:
FK_Products_Suppliers:
SupplierID relies upon remote
PK_Suppliers
(dbo.Suppliers)
FK_Products_Categories:
CategoryID relies upon remote
PK_Categories
(dbo.Categories)
Check constraints:
| CK_Products_UnitPrice | UnitPrice | ([UnitPrice] >= 0) |
| CK_ReorderLevel | ReorderLevel | ([ReorderLevel] >= 0) |
| CK_UnitsInStock | UnitsInStock | ([UnitsInStock] >= 0) |
| CK_UnitsOnOrder | UnitsOnOrder | ([UnitsOnOrder] >= 0) |
Object permissions
| public | grant | grant | grant | grant | | grant |
Objects that depend on dbo.Products (demo version, please register)
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 [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)
) ON [PRIMARY]
GO
Table:
dbo.Region (demo version, please register)
Sample comment add to test dbdesc
This is the second line
Columns
(2)
| PK |
| IX | RegionID | int | | | | |
|
|
|
| RegionDescription | nchar |
(50)
| | | |
Indexes
| PK_Region | RegionID | ASC | Yes | NONCLUSTERED |
Object permissions
| public | grant | grant | grant | grant | | grant |
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 (demo version, please register)
Shippers' names and phone numbers.
Columns
(3)
| PK |
| IX | 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. |
Indexes
| PK_Shippers | ShipperID | ASC | Yes | CLUSTERED |
Objects that depend on dbo.Shippers (demo version, please register)
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 (demo version, please register)
Suppliers' names, addresses, phone numbers, and hyperlinks to home pages.
Columns
(12)
| PK |
| IX | SupplierID | int | | | | Number automatically assigned to new supplier. |
|
|
| IX | 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. |
|
|
| IX | 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. |
Indexes
| CompanyName | ASC | | NONCLUSTERED |
| PK_Suppliers | SupplierID | ASC | Yes | CLUSTERED |
| PostalCode | ASC | | NONCLUSTERED |
Object permissions
| public | grant | grant | grant | grant | | grant |
Objects that depend on dbo.Suppliers (demo version, please register)
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 (demo version, please register)
sdfsf
Columns
(3)
| PK |
| IX | TerritoryID | nvarchar |
(20)
| | | sdfg |
|
|
|
| TerritoryDescription | nchar |
(50)
| | | sdfg |
|
| FK |
| RegionID | int | | | | sdfg |
Indexes
| PK_Territories | TerritoryID | ASC | Yes | NONCLUSTERED |
Foreign keys dependencies:
FK_Territories_Region:
RegionID relies upon remote
PK_Region
(dbo.Region)
Object permissions
| public | grant | grant | grant | grant | | grant |
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 [dbo].[Region] (
[RegionID]
)
) ON [PRIMARY]
GO
Table:
dbo.TestTable (demo version, please register)
This is a test table 2
Columns
(1)
|
|
|
| TestCustomField | TestType |
(20)
| Yes | | sdcs |
Table definition
CREATE TABLE [dbo].[TestTable] (
[TestCustomField] [TestType] NULL
) ON [PRIMARY]
GO
View:
dbo.Alphabetical list of products
( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Fields
View definition
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
| public | grant | grant | grant | grant | | |
Related objects
dbo.Products
dbo.Categories
View:
dbo.Category Sales for 1997
( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Totals product sales by category based on values returned by the Product Sales for 1997 query.
Fields
View definition
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
| public | grant | grant | grant | grant | | |
Related objects
dbo.Product Sales for 1997
View:
dbo.Current Product List
( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Filters records in Products table; query returns only products that are not discontinued.
Fields
View definition
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
| public | grant | grant | grant | grant | | |
Related objects
dbo.Products
View:
dbo.Customer and Suppliers by City
( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Fields
View definition
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
| public | grant | grant | grant | grant | | |
Related objects
dbo.Suppliers
dbo.Customers
View:
dbo.Invoices
( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
(Criteria) Record source for Invoice report. Based on six tables. Includes expressions that concatenate first and last employee name and that use the CCur function to calculate extended price.
Fields