Northwind project 

 

Database Design

 Northwind 

 

 

 

 

 

 

 

 

 

02.19.2007


Index

Database: Northwind _ 3

Tables _ 3

Categories _ 3

CustomerCustomerDemo _ 3

CustomerDemographics _ 3

Customers _ 3

Employees _ 3

EmployeeTerritories _ 3

Order Details _ 3

Orders _ 3

Products _ 3

Region _ 3

Shippers _ 3

Suppliers _ 3

Territories _ 3

Views _ 3

Alphabetical list of products _ 3

Category Sales for 1997 _ 3

Current Product List _ 3

Customer and Suppliers by City _ 3

Invoices _ 3

Order Details Extended _ 3

Order Subtotals _ 3

Orders Qry _ 3

Product Sales for 1997 _ 3

Products Above Average Price _ 3

Products by Category _ 3

Quarterly Orders _ 3

Sales by Category _ 3

Sales Totals by Amount _ 3

Summary of Sales by Quarter _ 3

Summary of Sales by Year _ 3

Stored Procedures _ 3

CustOrderHist _ 3

CustOrdersDetail _ 3

CustOrdersOrders _ 3

Employee Sales by Country _ 3

Sales by Year _ 3

SalesByCategory _ 3

Ten Most Expensive Products _ 3

Users _ 3

dbo _ 3

guest _ 3

Roles _ 3

public _ 3

db_owner _ 3

Server Info _ 3

Logins _ 3

sa _ 3

BUILTIN\Administrators _ 3

About _ 3


Database: Northwind

Properties

Name: Northwind 
Owner: sa 
Create for Attach: 08.06.2000 
Compatibility Level: 80 
Status: Database is available for query 
Full Text Search Enabled:  
Primary File: D:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf 
Version: 539 
Database size, MB: 4.25 MB 
Allocated Space, KB: 2504 KB 
Data Space in Use and Reserved for Use, KB: 976 KB 
Index Space Usage, KB: 1256 KB 
Allocated and Unused Space, KB: 272 KB 
Description:  

Data Files

File Name Location File Group Allocated Space, MB Autogrows Grows Maximum File Size, MB
Northwind   D:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf   PRIMARY  3    10 %  Unrestricted 
Total: 1 data file(s)

Log Files

File Name Location Allocated Space, MB Autogrows Grows Maximum File Size, MB
Northwind_log   D:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf   1    10 %  Unrestricted 
Total: 1 log file(s)

Tables

Name Owner Data Size KB Index Size KB Creation Date Cols Rows Description
  Categories  dbo  32  112  08.06.2000  4  8   
  CustomerCustomerDemo  dbo  0  0  08.06.2000  2  0   
  CustomerDemographics  dbo  0  0  08.06.2000  2  0   
  Customers  dbo  80  24  08.06.2000  11  91   
  Employees  dbo  48  232  08.06.2000  18  9   
  EmployeeTerritories  dbo  24  8  08.06.2000  2  49   
  Order Details  dbo  208  72  08.06.2000  5  2155   
  Orders  dbo  312  160  08.06.2000  14  830   
  Products  dbo  96  8  08.06.2000  10  77   
  Region  dbo  24  8  08.06.2000  2  4   
  Shippers  dbo  16  8  08.06.2000  3  3   
  Suppliers  dbo  48  24  08.06.2000  12  29   
  Territories  dbo  24  8  08.06.2000  3  53   
Total: 13 table(s)

Views

Name Owner Schema bound Encrypted Creation Date Description
  Alphabetical list of products  dbo      08.06.2000   
  Category Sales for 1997  dbo      08.06.2000   
  Current Product List  dbo      08.06.2000   
  Customer and Suppliers by City  dbo      08.06.2000   
  Invoices  dbo      08.06.2000   
  Order Details Extended  dbo      08.06.2000   
  Order Subtotals  dbo      08.06.2000   
  Orders Qry  dbo      08.06.2000   
  Product Sales for 1997  dbo      08.06.2000   
  Products Above Average Price  dbo      08.06.2000   
  Products by Category  dbo      08.06.2000   
  Quarterly Orders  dbo      08.06.2000   
  Sales by Category  dbo      08.06.2000   
  Sales Totals by Amount  dbo      08.06.2000   
  Summary of Sales by Quarter  dbo      08.06.2000   
  Summary of Sales by Year  dbo      08.06.2000   
Total: 16 view(s)

Stored procedures

Name Owner Description
  CustOrderHist  dbo   
  CustOrdersDetail  dbo   
  CustOrdersOrders  dbo   
  Employee Sales by Country  dbo   
  Sales by Year  dbo   
  SalesByCategory  dbo   
  Ten Most Expensive Products  dbo   
Total: 7 stored procedure(s)

Database Options

Settings: Auto Close:  
Settings: Auto Shrink:  
Settings: Auto Create Statistics:  
Settings: Auto Update Statistics:  
Settings: Use Quoted Identifiers:  
Settings: Torn Page Detection:  
Settings: Recursive Triggers:  
Settings: ANSI NULL default:  
Access: Read Only:  
Access: Single User:  
Access: Members of db_owner, dbcreator or sysadmin:  
Truncate Log On Checkpoint:  
Select Into/Bulkcopy:  
Cursor Close On Commit:  
Cursors in a Batch are with Local Scope:  

See Also

Server overview

Tables


Table: Categories

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 2041058307 
Located On: PRIMARY 
Rows: 8 
Data Size KB: 112 
Index Size KB: 32 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      CategoryID  int  4         
      CategoryName  nvarchar  30         
      Description  ntext  16         
      Picture  image  16         
Total: 4 column(s)

Indexes

Index Primary Unique
  PK_Categories     
  CategoryName     
Total: 2 indexes(s)

Identity column

Name Seed Increment Not for replication
  CategoryID  1  1   

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Products  FK_Products_Categories  PK_Categories 

Objects that depend on [dbo].[Categories]

Object Name Owner Object Type Dep Level
  Products  dbo  Table  1 
  Alphabetical list of products  dbo  View  2 
  Current Product List  dbo  View  2 
  Products Above Average Price  dbo  View  2 
  Products by Category  dbo  View  2 
  Order Details  dbo  Table  2 
  Ten Most Expensive Products  dbo  Procedure  2 
  Invoices  dbo  View  3 
  Order Details Extended  dbo  View  3 
  Order Subtotals  dbo  View  3 
  Product Sales for 1997  dbo  View  3 
  CustOrderHist  dbo  Procedure  3 
  CustOrdersDetail  dbo  Procedure  3 
  SalesByCategory  dbo  Procedure  3 
  Category Sales for 1997  dbo  View  4 
  Sales by Category  dbo  View  4 
  Sales Totals by Amount  dbo  View  4 
  Summary of Sales by Quarter  dbo  View  4 
  Summary of Sales by Year  dbo  View  4 
  Employee Sales by Country  dbo  Procedure  4 
  Sales by Year  dbo  Procedure  4 
Total: 21 objects

SQL

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


See Also

List of tables

Indexes


Index: PK_Categories on table dbo.Categories

Properties

Size KB: 32 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CategoryID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Categories] ON [dbo].[Categories]([CategoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CategoryName on table dbo.Categories

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CategoryName  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 25 
Density: 0 
Rows in the table: 8 
Rows sampled for statistics data: 8 
Distribution steps: 8 

SQL

CREATE  INDEX [CategoryName] ON [dbo].[Categories]([CategoryName]) ON [PRIMARY]
GO

See Also

List of indexes



Table: CustomerCustomerDemo

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 853578079 
Located On: PRIMARY 
Rows: 0 
Data Size KB: 0 
Index Size KB: 0 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      CustomerID  nchar  10         
      CustomerTypeID  nchar  20         
Total: 2 column(s)

Indexes

Index Primary Unique
  PK_CustomerCustomerDemo     
Total: 1 indexes(s)

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  CustomerDemographics  FK_CustomerCustomerDemo  PK_CustomerDemographics 
  Customers  FK_CustomerCustomerDemo_Customers  PK_Customers 

Objects that [dbo].[CustomerCustomerDemo] depends on

Object Name Owner Object Type Dep Level
  CustomerDemographics  dbo  Table  1 
  Customers  dbo  Table  1 
Total: 2 objects

SQL

CREATE TABLE [CustomerCustomerDemo] (
    [CustomerID] [nchar] (5) COLLATE Cyrillic_General_CI_AS NOT NULL ,
    [CustomerTypeID] [nchar] (10) COLLATE Cyrillic_General_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


See Also

List of tables

Indexes


Index: PK_CustomerCustomerDemo on table dbo.CustomerCustomerDemo

Properties

Size KB: 0 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CustomerID  0 
  CustomerTypeID  0 
Total: 2 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  INDEX [PK_CustomerCustomerDemo] ON [dbo].[CustomerCustomerDemo]([CustomerID], [CustomerTypeID]) ON [PRIMARY]
GO

See Also

List of indexes


Relationships


Relationship: [FK_CustomerCustomerDemo] on [CustomerCustomerDemo]

Properties

Primary Table Owner: dbo 
Primary Table: CustomerDemographics 
Primary Key or Unique Constraint: PK_CustomerDemographics 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  CustomerTypeID  CustomerTypeID  nchar 
Total: 1 trigger(s)

SQL

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

See Also

Table [CustomerDemographics]
Table [CustomerCustomerDemo]


Relationship: [FK_CustomerCustomerDemo_Customers] on [CustomerCustomerDemo]

Properties

Primary Table Owner: dbo 
Primary Table: Customers 
Primary Key or Unique Constraint: PK_Customers 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  CustomerID  CustomerID  nchar 
Total: 1 trigger(s)

SQL

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

See Also

Table [Customers]
Table [CustomerCustomerDemo]



Table: CustomerDemographics

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 869578136 
Located On: PRIMARY 
Rows: 0 
Data Size KB: 0 
Index Size KB: 0 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      CustomerTypeID  nchar  20         
      CustomerDesc  ntext  16         
Total: 2 column(s)

Indexes

Index Primary Unique
  PK_CustomerDemographics     
Total: 1 indexes(s)

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  CustomerCustomerDemo  FK_CustomerCustomerDemo  PK_CustomerDemographics 

Objects that depend on [dbo].[CustomerDemographics]

Object Name Owner Object Type Dep Level
  CustomerCustomerDemo  dbo  Table  1 
Total: 1 objects

SQL

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


See Also

List of tables

Indexes


Index: PK_CustomerDemographics on table dbo.CustomerDemographics

Properties

Size KB: 0 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CustomerTypeID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  INDEX [PK_CustomerDemographics] ON [dbo].[CustomerDemographics]([CustomerTypeID]) ON [PRIMARY]
GO

See Also

List of indexes



Table: Customers

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 2073058421 
Located On: PRIMARY 
Rows: 91 
Data Size KB: 24 
Index Size KB: 80 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      CustomerID  nchar  10         
      CompanyName  nvarchar  80         
      ContactName  nvarchar  60         
      ContactTitle  nvarchar  60         
      Address  nvarchar  120         
      City  nvarchar  30         
      Region  nvarchar  30         
      PostalCode  nvarchar  20         
      Country  nvarchar  30         
      Phone  nvarchar  48         
      Fax  nvarchar  48         
Total: 11 column(s)

Indexes

Index Primary Unique
  PK_Customers     
  City     
  CompanyName     
  PostalCode     
  Region     
Total: 5 indexes(s)

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Orders  FK_Orders_Customers  PK_Customers 
  CustomerCustomerDemo  FK_CustomerCustomerDemo_Customers  PK_Customers 

Objects that depend on [dbo].[Customers]

Object Name Owner Object Type Dep Level
  Customer and Suppliers by City  dbo  View  1 
  CustomerCustomerDemo  dbo  Table  1 
  Orders  dbo  Table  1 
  Orders Qry  dbo  View  2 
  Quarterly Orders  dbo  View  2 
  Order Details  dbo  Table  2 
  CustOrdersOrders  dbo  Procedure  2 
  Invoices  dbo  View  3 
  Order Details Extended  dbo  View  3 
  Order Subtotals  dbo  View  3 
  Product Sales for 1997  dbo  View  3 
  CustOrderHist  dbo  Procedure  3 
  CustOrdersDetail  dbo  Procedure  3 
  SalesByCategory  dbo  Procedure  3 
  Category Sales for 1997  dbo  View  4 
  Sales by Category  dbo  View  4 
  Sales Totals by Amount  dbo  View  4 
  Summary of Sales by Quarter  dbo  View  4 
  Summary of Sales by Year  dbo  View  4 
  Employee Sales by Country  dbo  Procedure  4 
  Sales by Year  dbo  Procedure  4 
Total: 21 objects

SQL

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


See Also

List of tables

Indexes


Index: PK_Customers on table dbo.Customers

Properties

Size KB: 80 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CustomerID  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 10 
Density: 1,098901E-02 
Rows in the table: 91 
Rows sampled for statistics data: 91 
Distribution steps: 91 

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Customers] ON [dbo].[Customers]([CustomerID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: City on table dbo.Customers

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  City  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 26,02198 
Density: 0 
Rows in the table: 91 
Rows sampled for statistics data: 91 
Distribution steps: 69 

SQL

CREATE  INDEX [City] ON [dbo].[Customers]([City]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CompanyName on table dbo.Customers

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CompanyName  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 47,8022 
Density: 0 
Rows in the table: 91 
Rows sampled for statistics data: 91 
Distribution steps: 91 

SQL

CREATE  INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: PostalCode on table dbo.Customers

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  PostalCode  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 21,01099 
Density: 0 
Rows in the table: 91 
Rows sampled for statistics data: 91 
Distribution steps: 86 

SQL

CREATE  INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]) ON [PRIMARY]
GO

See Also

List of indexes


Index: Region on table dbo.Customers

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  Region  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 12,21978 
Density: 0 
Rows in the table: 91 
Rows sampled for statistics data: 91 
Distribution steps: 18 

SQL

CREATE  INDEX [Region] ON [dbo].[Customers]([Region]) ON [PRIMARY]
GO

See Also

List of indexes



Table: Employees

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 1977058079 
Located On: PRIMARY 
Rows: 9 
Data Size KB: 232 
Index Size KB: 48 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      EmployeeID  int  4         
      LastName  nvarchar  40         
      FirstName  nvarchar  20         
      Title  nvarchar  60         
      TitleOfCourtesy  nvarchar  50         
      BirthDate  datetime  8    ([BirthDate] < getdate())     
      HireDate  datetime  8         
      Address  nvarchar  120         
      City  nvarchar  30         
      Region  nvarchar  30         
      PostalCode  nvarchar  20         
      Country  nvarchar  30         
      HomePhone  nvarchar  48         
      Extension  nvarchar  8         
      Photo  image  16         
      Notes  ntext  16         
      ReportsTo  int  4         
      PhotoPath  nvarchar  510         
Total: 18 column(s)

Indexes

Index Primary Unique
  PK_Employees     
  LastName     
  PostalCode     
Total: 3 indexes(s)

Check Constraints

Name Expression
  CK_Birthdate  ([BirthDate] < getdate()) 
Total: 1 constraints

Identity column

Name Seed Increment Not for replication
  EmployeeID  1  1   

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Orders  FK_Orders_Employees  PK_Employees 
  EmployeeTerritories  FK_EmployeeTerritories_Employees  PK_Employees 
  Employees  FK_Employees_Employees  PK_Employees 

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  Employees  FK_Employees_Employees  PK_Employees 

Objects that depend on [dbo].[Employees]

Object Name Owner Object Type Dep Level
  EmployeeTerritories  dbo  Table  1 
  Orders  dbo  Table  1 
  Orders Qry  dbo  View  2 
  Quarterly Orders  dbo  View  2 
  Order Details  dbo  Table  2 
  CustOrdersOrders  dbo  Procedure  2 
  Invoices  dbo  View  3 
  Order Details Extended  dbo  View  3 
  Order Subtotals  dbo  View  3 
  Product Sales for 1997  dbo  View  3 
  CustOrderHist  dbo  Procedure  3 
  CustOrdersDetail  dbo  Procedure  3 
  SalesByCategory  dbo  Procedure  3 
  Category Sales for 1997  dbo  View  4 
  Sales by Category  dbo  View  4 
  Sales Totals by Amount  dbo  View  4 
  Summary of Sales by Quarter  dbo  View  4 
  Summary of Sales by Year  dbo  View  4 
  Employee Sales by Country  dbo  Procedure  4 
  Sales by Year  dbo  Procedure  4 
Total: 20 objects

SQL

CREATE TABLE [Employees] (
    [EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
    [LastName] [nvarchar] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,
    [FirstName] [nvarchar] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ,
    [Title] [nvarchar] (30) COLLATE Cyrillic_General_CI_AS NULL ,
    [TitleOfCourtesy] [nvarchar] (25) COLLATE Cyrillic_General_CI_AS NULL ,
    [BirthDate] [datetime] NULL ,
    [HireDate] [datetime] NULL ,
    [Address] [nvarchar] (60) COLLATE Cyrillic_General_CI_AS NULL ,
    [City] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ,
    [Region] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ,
    [PostalCode] [nvarchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,
    [Country] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ,
    [HomePhone] [nvarchar] (24) COLLATE Cyrillic_General_CI_AS NULL ,
    [Extension] [nvarchar] (4) COLLATE Cyrillic_General_CI_AS NULL ,
    [Photo] [image] NULL ,
    [Notes] [ntext] COLLATE Cyrillic_General_CI_AS NULL ,
    [ReportsTo] [int] NULL ,
    [PhotoPath] [nvarchar] (255) COLLATE Cyrillic_General_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


See Also

List of tables

Indexes


Index: PK_Employees on table dbo.Employees

Properties

Size KB: 48 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  EmployeeID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Employees] ON [dbo].[Employees]([EmployeeID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: LastName on table dbo.Employees

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  LastName  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 18,22222 
Density: 0 
Rows in the table: 9 
Rows sampled for statistics data: 9 
Distribution steps: 9 

SQL

CREATE  INDEX [LastName] ON [dbo].[Employees]([LastName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: PostalCode on table dbo.Employees

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  PostalCode  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 15,77778 
Density: 0 
Rows in the table: 9 
Rows sampled for statistics data: 9 
Distribution steps: 9 

SQL

CREATE  INDEX [PostalCode] ON [dbo].[Employees]([PostalCode]) ON [PRIMARY]
GO

See Also

List of indexes


Relationships


Relationship: [FK_Employees_Employees] on [Employees]

Properties

Primary Table Owner: dbo 
Primary Table: Employees 
Primary Key or Unique Constraint: PK_Employees 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  ReportsTo  EmployeeID  int 
Total: 1 trigger(s)

SQL

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

See Also

Table [Employees]
Table [Employees]



Table: EmployeeTerritories

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 917578307 
Located On: PRIMARY 
Rows: 49 
Data Size KB: 8 
Index Size KB: 24 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      EmployeeID  int  4         
      TerritoryID  nvarchar  40         
Total: 2 column(s)

Indexes

Index Primary Unique
  PK_EmployeeTerritories     
  _WA_Sys_TerritoryID_36B12243     
Total: 2 indexes(s)

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  Employees  FK_EmployeeTerritories_Employees  PK_Employees 
  Territories  FK_EmployeeTerritories_Territories  PK_Territories 

Objects that [dbo].[EmployeeTerritories] depends on

Object Name Owner Object Type Dep Level
  Employees  dbo  Table  1 
  Region  dbo  Table  1 
  Territories  dbo  Table  2 
Total: 3 objects

SQL

CREATE TABLE [EmployeeTerritories] (
    [EmployeeID] [int] NOT NULL ,
    [TerritoryID] [nvarchar] (20) COLLATE Cyrillic_General_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


See Also

List of tables

Indexes


Index: PK_EmployeeTerritories on table dbo.EmployeeTerritories

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  EmployeeID  0 
  TerritoryID  0 
Total: 2 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 14 
Density: 0 
Rows in the table: 49 
Rows sampled for statistics data: 49 
Distribution steps: 9 

SQL

CREATE  UNIQUE  INDEX [PK_EmployeeTerritories] ON [dbo].[EmployeeTerritories]([EmployeeID], [TerritoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: _WA_Sys_TerritoryID_36B12243 on table dbo.EmployeeTerritories

Properties

Size KB: 0 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  TerritoryID  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 10 
Density: 0 
Rows in the table: 49 
Rows sampled for statistics data: 49 
Distribution steps: 49 

SQL

See Also

List of indexes


Relationships


Relationship: [FK_EmployeeTerritories_Employees] on [EmployeeTerritories]

Properties

Primary Table Owner: dbo 
Primary Table: Employees 
Primary Key or Unique Constraint: PK_Employees 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  EmployeeID  EmployeeID  int 
Total: 1 trigger(s)

SQL

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

See Also

Table [Employees]
Table [EmployeeTerritories]


Relationship: [FK_EmployeeTerritories_Territories] on [EmployeeTerritories]

Properties

Primary Table Owner: dbo 
Primary Table: Territories 
Primary Key or Unique Constraint: PK_Territories 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  TerritoryID  TerritoryID  nvarchar 
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[EmployeeTerritories] ADD CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
    (
        [TerritoryID]
    ) REFERENCES [Territories] (
        [TerritoryID]
    )
GO

See Also

Table [Territories]
Table [EmployeeTerritories]



Table: Order Details

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 325576198 
Located On: PRIMARY 
Rows: 2155 
Data Size KB: 72 
Index Size KB: 208 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      OrderID  int  4         
      ProductID  int  4         
      UnitPrice  money  8    ([UnitPrice] >= 0)     
      Quantity  smallint  2    ([Quantity] > 0)     
      Discount  real  4    ([Discount] >= 0 and [Discount] <= 1)     
Total: 5 column(s)

Indexes

Index Primary Unique
  PK_Order_Details     
  OrderID     
  OrdersOrder_Details     
  ProductID     
  ProductsOrder_Details     
Total: 5 indexes(s)

Check Constraints

Name Expression
  CK_Discount  ([Discount] >= 0 and [Discount] <= 1) 
  CK_Quantity  ([Quantity] > 0) 
  CK_UnitPrice  ([UnitPrice] >= 0) 
Total: 3 constraints

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  Orders  FK_Order_Details_Orders  PK_Orders 
  Products  FK_Order_Details_Products  PK_Products 

Objects that [dbo].[Order Details] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
Total: 7 objects

Objects that depend on [dbo].[Order Details]

Object Name Owner Object Type Dep Level
  Invoices  dbo  View  1 
  Order Details Extended  dbo  View  1 
  Order Subtotals  dbo  View  1 
  Product Sales for 1997  dbo  View  1 
  CustOrderHist  dbo  Procedure  1 
  CustOrdersDetail  dbo  Procedure  1 
  SalesByCategory  dbo  Procedure  1 
  Category Sales for 1997  dbo  View  2 
  Sales by Category  dbo  View  2 
  Sales Totals by Amount  dbo  View  2 
  Summary of Sales by Quarter  dbo  View  2 
  Summary of Sales by Year  dbo  View  2 
  Employee Sales by Country  dbo  Procedure  2 
  Sales by Year  dbo  Procedure  2 
Total: 14 objects

SQL

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]
    )  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


See Also

List of tables

Indexes


Index: PK_Order_Details on table dbo.Order Details

Properties

Size KB: 208 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  OrderID  0 
  ProductID  0 
Total: 2 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Order_Details] ON [dbo].[Order Details]([OrderID], [ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: OrderID on table dbo.Order Details

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  OrderID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: OrdersOrder_Details on table dbo.Order Details

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  OrderID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ProductID on table dbo.Order Details

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ProductID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ProductsOrder_Details on table dbo.Order Details

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ProductID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


Relationships


Relationship: [FK_Order_Details_Orders] on [Order Details]

Properties

Primary Table Owner: dbo 
Primary Table: Orders 
Primary Key or Unique Constraint: PK_Orders 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  OrderID  OrderID  int 
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Order Details] ADD CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
    (
        [OrderID]
    ) REFERENCES [Orders] (
        [OrderID]
    )
GO

See Also

Table [Orders]
Table [Order Details]


Relationship: [FK_Order_Details_Products] on [Order Details]

Properties

Primary Table Owner: dbo 
Primary Table: Products 
Primary Key or Unique Constraint: PK_Products 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  ProductID  ProductID  int 
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Order Details] ADD CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
    (
        [ProductID]
    ) REFERENCES [Products] (
        [ProductID]
    )
GO

See Also

Table [Products]
Table [Order Details]



Table: Orders

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 21575115 
Located On: PRIMARY 
Rows: 830 
Data Size KB: 160 
Index Size KB: 312 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      OrderID  int  4         
      CustomerID  nchar  10         
      EmployeeID  int  4         
      OrderDate  datetime  8         
      RequiredDate  datetime  8         
      ShippedDate  datetime  8         
      ShipVia  int  4         
      Freight  money  8         
      ShipName  nvarchar  80         
      ShipAddress  nvarchar  120         
      ShipCity  nvarchar  30         
      ShipRegion  nvarchar  30         
      ShipPostalCode  nvarchar  20         
      ShipCountry  nvarchar  30         
Total: 14 column(s)

Indexes

Index Primary Unique
  PK_Orders     
  CustomerID     
  CustomersOrders     
  EmployeeID     
  EmployeesOrders     
  OrderDate     
  ShippedDate     
  ShippersOrders     
  ShipPostalCode     
Total: 9 indexes(s)

Identity column

Name Seed Increment Not for replication
  OrderID  1  1   

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Order Details  FK_Order_Details_Orders  PK_Orders 

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  Customers  FK_Orders_Customers  PK_Customers 
  Employees  FK_Orders_Employees  PK_Employees 
  Shippers  FK_Orders_Shippers  PK_Shippers 

Objects that [dbo].[Orders] depends on

Object Name Owner Object Type Dep Level
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
Total: 3 objects

Objects that depend on [dbo].[Orders]

Object Name Owner Object Type Dep Level
  Orders Qry  dbo  View  1 
  Quarterly Orders  dbo  View  1 
  Order Details  dbo  Table  1 
  CustOrdersOrders  dbo  Procedure  1 
  Invoices  dbo  View  2 
  Order Details Extended  dbo  View  2 
  Order Subtotals  dbo  View  2 
  Product Sales for 1997  dbo  View  2 
  CustOrderHist  dbo  Procedure  2 
  CustOrdersDetail  dbo  Procedure  2 
  SalesByCategory  dbo  Procedure  2 
  Category Sales for 1997  dbo  View  3 
  Sales by Category  dbo  View  3 
  Sales Totals by Amount  dbo  View  3 
  Summary of Sales by Quarter  dbo  View  3 
  Summary of Sales by Year  dbo  View  3 
  Employee Sales by Country  dbo  Procedure  3 
  Sales by Year  dbo  Procedure  3 
Total: 18 objects

SQL

CREATE TABLE [Orders] (
    [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
    [CustomerID] [nchar] (5) COLLATE Cyrillic_General_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 Cyrillic_General_CI_AS NULL ,
    [ShipAddress] [nvarchar] (60) COLLATE Cyrillic_General_CI_AS NULL ,
    [ShipCity] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ,
    [ShipRegion] [nvarchar] (15) COLLATE Cyrillic_General_CI_AS NULL ,
    [ShipPostalCode] [nvarchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,
    [ShipCountry] [nvarchar] (15) COLLATE Cyrillic_General_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


See Also

List of tables

Indexes


Index: PK_Orders on table dbo.Orders

Properties

Size KB: 312 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  OrderID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Orders] ON [dbo].[Orders]([OrderID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CustomerID on table dbo.Orders

Properties

Size KB: 40 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CustomerID  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 14 
Density: 0 
Rows in the table: 830 
Rows sampled for statistics data: 830 
Distribution steps: 89 

SQL

CREATE  INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CustomersOrders on table dbo.Orders

Properties

Size KB: 40 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CustomerID  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 14 
Density: 0 
Rows in the table: 830 
Rows sampled for statistics data: 830 
Distribution steps: 89 

SQL

CREATE  INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: EmployeeID on table dbo.Orders

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  EmployeeID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: EmployeesOrders on table dbo.Orders

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  EmployeeID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: OrderDate on table dbo.Orders

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  OrderDate  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ShippedDate on table dbo.Orders

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ShippedDate  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ShippersOrders on table dbo.Orders

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ShipVia  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ShipPostalCode on table dbo.Orders

Properties

Size KB: 40 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ShipPostalCode  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 14,97108 
Density: 0 
Rows in the table: 830 
Rows sampled for statistics data: 830 
Distribution steps: 84 

SQL

CREATE  INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
GO

See Also

List of indexes


Relationships


Relationship: [FK_Orders_Customers] on [Orders]

Properties

Primary Table Owner: dbo 
Primary Table: Customers 
Primary Key or Unique Constraint: PK_Customers 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  CustomerID  CustomerID  nchar 
Total: 1 trigger(s)

SQL

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

See Also

Table [Customers]
Table [Orders]


Relationship: [FK_Orders_Employees] on [Orders]

Properties

Primary Table Owner: dbo 
Primary Table: Employees 
Primary Key or Unique Constraint: PK_Employees 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  EmployeeID  EmployeeID  int 
Total: 1 trigger(s)

SQL

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

See Also

Table [Employees]
Table [Orders]


Relationship: [FK_Orders_Shippers] on [Orders]

Properties

Primary Table Owner: dbo 
Primary Table: Shippers 
Primary Key or Unique Constraint: PK_Shippers 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  ShipVia  ShipperID  int 
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
    (
        [ShipVia]
    ) REFERENCES [Shippers] (
        [ShipperID]
    )
GO

See Also

Table [Shippers]
Table [Orders]



Table: Products

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 117575457 
Located On: PRIMARY 
Rows: 77 
Data Size KB: 8 
Index Size KB: 96 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      ProductID  int  4         
      ProductName  nvarchar  80         
      SupplierID  int  4         
      CategoryID  int  4         
      QuantityPerUnit  nvarchar  40         
      UnitPrice  money  8    ([UnitPrice] >= 0)     
      UnitsInStock  smallint  2    ([UnitsInStock] >= 0)     
      UnitsOnOrder  smallint  2    ([UnitsOnOrder] >= 0)     
      ReorderLevel  smallint  2    ([ReorderLevel] >= 0)     
      Discontinued  bit  1         
Total: 10 column(s)

Indexes

Index Primary Unique
  PK_Products     
  CategoriesProducts     
  CategoryID     
  ProductName     
  SupplierID     
  SuppliersProducts     
Total: 6 indexes(s)

Check Constraints

Name Expression
  CK_Products_UnitPrice  ([UnitPrice] >= 0) 
  CK_ReorderLevel  ([ReorderLevel] >= 0) 
  CK_UnitsInStock  ([UnitsInStock] >= 0) 
  CK_UnitsOnOrder  ([UnitsOnOrder] >= 0) 
Total: 4 constraints

Identity column

Name Seed Increment Not for replication
  ProductID  1  1   

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Order Details  FK_Order_Details_Products  PK_Products 

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  Categories  FK_Products_Categories  PK_Categories 
  Suppliers  FK_Products_Suppliers  PK_Suppliers 

Objects that [dbo].[Products] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Suppliers  dbo  Table  1 
Total: 2 objects

Objects that depend on [dbo].[Products]

Object Name Owner Object Type Dep Level
  Alphabetical list of products  dbo  View  1 
  Current Product List  dbo  View  1 
  Products Above Average Price  dbo  View  1 
  Products by Category  dbo  View  1 
  Order Details  dbo  Table  1 
  Ten Most Expensive Products  dbo  Procedure  1 
  Invoices  dbo  View  2 
  Order Details Extended  dbo  View  2 
  Order Subtotals  dbo  View  2 
  Product Sales for 1997  dbo  View  2 
  CustOrderHist  dbo  Procedure  2 
  CustOrdersDetail  dbo  Procedure  2 
  SalesByCategory  dbo  Procedure  2 
  Category Sales for 1997  dbo  View  3 
  Sales by Category  dbo  View  3 
  Sales Totals by Amount  dbo  View  3 
  Summary of Sales by Quarter  dbo  View  3 
  Summary of Sales by Year  dbo  View  3 
  Employee Sales by Country  dbo  Procedure  3 
  Sales by Year  dbo  Procedure  3 
Total: 20 objects

SQL

CREATE TABLE [Products] (
    [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
    [ProductName] [nvarchar] (40) COLLATE Cyrillic_General_CI_AS NOT NULL ,
    [SupplierID] [int] NULL ,
    [CategoryID] [int] NULL ,
    [QuantityPerUnit] [nvarchar] (20) COLLATE Cyrillic_General_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


See Also

List of tables

Indexes


Index: PK_Products on table dbo.Products

Properties

Size KB: 96 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ProductID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Products] ON [dbo].[Products]([ProductID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CategoriesProducts on table dbo.Products

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CategoryID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [CategoriesProducts] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CategoryID on table dbo.Products

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CategoryID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [CategoryID] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: ProductName on table dbo.Products

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ProductName  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 36,75325 
Density: 0 
Rows in the table: 77 
Rows sampled for statistics data: 77 
Distribution steps: 77 

SQL

CREATE  INDEX [ProductName] ON [dbo].[Products]([ProductName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: SupplierID on table dbo.Products

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  SupplierID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [SupplierID] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: SuppliersProducts on table dbo.Products

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  SupplierID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  INDEX [SuppliersProducts] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
GO

See Also

List of indexes


Relationships


Relationship: [FK_Products_Categories] on [Products]

Properties

Primary Table Owner: dbo 
Primary Table: Categories 
Primary Key or Unique Constraint: PK_Categories 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  CategoryID  CategoryID  int 
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Products] ADD CONSTRAINT [FK_Products_Categories] FOREIGN KEY
    (
        [CategoryID]
    ) REFERENCES [Categories] (
        [CategoryID]
    )
GO

See Also

Table [Categories]
Table [Products]


Relationship: [FK_Products_Suppliers] on [Products]

Properties

Primary Table Owner: dbo 
Primary Table: Suppliers 
Primary Key or Unique Constraint: PK_Suppliers 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  SupplierID  SupplierID  int 
Total: 1 trigger(s)

SQL

ALTER TABLE [dbo].[Products] ADD CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY
    (
        [SupplierID]
    ) REFERENCES [Suppliers] (
        [SupplierID]
    )
GO

See Also

Table [Suppliers]
Table [Products]



Table: Region

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 885578193 
Located On: PRIMARY 
Rows: 4 
Data Size KB: 8 
Index Size KB: 24 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      RegionID  int  4         
      RegionDescription  nchar  100         
Total: 2 column(s)

Indexes

Index Primary Unique
  PK_Region     
Total: 1 indexes(s)

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Territories  FK_Territories_Region  PK_Region 

Objects that depend on [dbo].[Region]

Object Name Owner Object Type Dep Level
  Territories  dbo  Table  1 
  EmployeeTerritories  dbo  Table  2 
Total: 2 objects

SQL

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


See Also

List of tables

Indexes


Index: PK_Region on table dbo.Region

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  RegionID  0 
Total: 1 column(s)

Statistics

Updated: Aug 6 2000 1:34AM  
Average key length: 4 
Density: 0,25 
Rows in the table: 4 
Rows sampled for statistics data: 4 
Distribution steps: 3 

SQL

CREATE  UNIQUE  INDEX [PK_Region] ON [dbo].[Region]([RegionID]) ON [PRIMARY]
GO

See Also

List of indexes



Table: Shippers

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 2105058535 
Located On: PRIMARY 
Rows: 3 
Data Size KB: 8 
Index Size KB: 16 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      ShipperID  int  4         
      CompanyName  nvarchar  80         
      Phone  nvarchar  48         
Total: 3 column(s)

Indexes

Index Primary Unique
  PK_Shippers     
Total: 1 indexes(s)

Identity column

Name Seed Increment Not for replication
  ShipperID  1  1   

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Orders  FK_Orders_Shippers  PK_Shippers 

Objects that depend on [dbo].[Shippers]

Object Name Owner Object Type Dep Level
  Orders  dbo  Table  1 
  Orders Qry  dbo  View  2 
  Quarterly Orders  dbo  View  2 
  Order Details  dbo  Table  2 
  CustOrdersOrders  dbo  Procedure  2 
  Invoices  dbo  View  3 
  Order Details Extended  dbo  View  3 
  Order Subtotals  dbo  View  3 
  Product Sales for 1997  dbo  View  3 
  CustOrderHist  dbo  Procedure  3 
  CustOrdersDetail  dbo  Procedure  3 
  SalesByCategory  dbo  Procedure  3 
  Category Sales for 1997  dbo  View  4 
  Sales by Category  dbo  View  4 
  Sales Totals by Amount  dbo  View  4 
  Summary of Sales by Quarter  dbo  View  4 
  Summary of Sales by Year  dbo  View  4 
  Employee Sales by Country  dbo  Procedure  4 
  Sales by Year  dbo  Procedure  4 
Total: 19 objects

SQL

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


See Also

List of tables

Indexes


Index: PK_Shippers on table dbo.Shippers

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  ShipperID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Shippers] ON [dbo].[Shippers]([ShipperID]) ON [PRIMARY]
GO

See Also

List of indexes



Table: Suppliers

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 2137058649 
Located On: PRIMARY 
Rows: 29 
Data Size KB: 24 
Index Size KB: 48 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      SupplierID  int  4         
      CompanyName  nvarchar  80         
      ContactName  nvarchar  60         
      ContactTitle  nvarchar  60         
      Address  nvarchar  120         
      City  nvarchar  30         
      Region  nvarchar  30         
      PostalCode  nvarchar  20         
      Country  nvarchar  30         
      Phone  nvarchar  48         
      Fax  nvarchar  48         
      HomePage  ntext  16         
Total: 12 column(s)

Indexes

Index Primary Unique
  PK_Suppliers     
  CompanyName     
  PostalCode     
Total: 3 indexes(s)

Identity column

Name Seed Increment Not for replication
  SupplierID  1  1   

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  Products  FK_Products_Suppliers  PK_Suppliers 

Objects that depend on [dbo].[Suppliers]

Object Name Owner Object Type Dep Level
  Customer and Suppliers by City  dbo  View  1 
  Products  dbo  Table  1 
  Alphabetical list of products  dbo  View  2 
  Current Product List  dbo  View  2 
  Products Above Average Price  dbo  View  2 
  Products by Category  dbo  View  2 
  Order Details  dbo  Table  2 
  Ten Most Expensive Products  dbo  Procedure  2 
  Invoices  dbo  View  3 
  Order Details Extended  dbo  View  3 
  Order Subtotals  dbo  View  3 
  Product Sales for 1997  dbo  View  3 
  CustOrderHist  dbo  Procedure  3 
  CustOrdersDetail  dbo  Procedure  3 
  SalesByCategory  dbo  Procedure  3 
  Category Sales for 1997  dbo  View  4 
  Sales by Category  dbo  View  4 
  Sales Totals by Amount  dbo  View  4 
  Summary of Sales by Quarter  dbo  View  4 
  Summary of Sales by Year  dbo  View  4 
  Employee Sales by Country  dbo  Procedure  4 
  Sales by Year  dbo  Procedure  4 
Total: 22 objects

SQL

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


See Also

List of tables

Indexes


Index: PK_Suppliers on table dbo.Suppliers

Properties

Size KB: 48 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  SupplierID  0 
Total: 1 column(s)

Statistics

Updated:  
Average key length:  
Density:  
Rows in the table:  
Rows sampled for statistics data:  
Distribution steps:  

SQL

CREATE  UNIQUE  CLUSTERED  INDEX [PK_Suppliers] ON [dbo].[Suppliers]([SupplierID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: CompanyName on table dbo.Suppliers

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  CompanyName  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 42,41379 
Density: 0 
Rows in the table: 29 
Rows sampled for statistics data: 29 
Distribution steps: 29 

SQL

CREATE  INDEX [CompanyName] ON [dbo].[Suppliers]([CompanyName]) ON [PRIMARY]
GO

See Also

List of indexes


Index: PostalCode on table dbo.Suppliers

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  PostalCode  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 14,41379 
Density: 0 
Rows in the table: 29 
Rows sampled for statistics data: 29 
Distribution steps: 29 

SQL

CREATE  INDEX [PostalCode] ON [dbo].[Suppliers]([PostalCode]) ON [PRIMARY]
GO

See Also

List of indexes



Table: Territories

Properties

Owner: dbo 
Creation Date: 08.06.2000 
ID: 901578250 
Located On: PRIMARY 
Rows: 53 
Data Size KB: 8 
Index Size KB: 24 
Description:  

Columns

Name Data Type Length NULL Default IsGUID Description
      TerritoryID  nvarchar  40         
      TerritoryDescription  nchar  100         
      RegionID  int  4         
Total: 3 column(s)

Indexes

Index Primary Unique
  PK_Territories     
  _WA_Sys_RegionID_35BCFE0A     
Total: 2 indexes(s)

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  EmployeeTerritories  FK_EmployeeTerritories_Territories  PK_Territories 

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  Region  FK_Territories_Region  PK_Region 

Objects that [dbo].[Territories] depends on

Object Name Owner Object Type Dep Level
  Region  dbo  Table  1 
Total: 1 objects

Objects that depend on [dbo].[Territories]

Object Name Owner Object Type Dep Level
  EmployeeTerritories  dbo  Table  1 
Total: 1 objects

SQL

CREATE TABLE [Territories] (
    [TerritoryID] [nvarchar] (20) COLLATE Cyrillic_General_CI_AS NOT NULL ,
    [TerritoryDescription] [nchar] (50) COLLATE Cyrillic_General_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


See Also

List of tables

Indexes


Index: PK_Territories on table dbo.Territories

Properties

Size KB: 16 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  TerritoryID  0 
Total: 1 column(s)

Statistics

Updated: Oct 21 2006 5:45PM  
Average key length: 10 
Density: 1,886792E-02 
Rows in the table: 53 
Rows sampled for statistics data: 53 
Distribution steps: 53 

SQL

CREATE  UNIQUE  INDEX [PK_Territories] ON [dbo].[Territories]([TerritoryID]) ON [PRIMARY]
GO

See Also

List of indexes


Index: _WA_Sys_RegionID_35BCFE0A on table dbo.Territories

Properties

Size KB: 0 
Fill Factor: 0 
Don't Recompute Statistics:  
Pad Index:  
Unique Index:  
Primary Key Index:  
Clustered Index:  
Is computed column in index:  
Ignore Duplicates:  
Located On: PRIMARY 
Disabled:  

Index Columns

Name Descending
  RegionID  0 
Total: 1 column(s)

Statistics

Updated: Aug 6 2000 1:34AM  
Average key length: 4 
Density: 0 
Rows in the table: 53 
Rows sampled for statistics data: 53 
Distribution steps: 4 

SQL

See Also

List of indexes


Relationships


Relationship: [FK_Territories_Region] on [Territories]

Properties

Primary Table Owner: dbo 
Primary Table: Region 
Primary Key or Unique Constraint: PK_Region 
Delete Cascade:  
Update Cascade:  

Relationship Columns

Column Reference Column Type
  RegionID  RegionID  int 
Total: 1 trigger(s)

SQL

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

See Also

Table [Region]
Table [Territories]



Views


View: Alphabetical list of products

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  ProductID  int  4     
  ProductName  nvarchar  80     
  SupplierID  int  4     
  CategoryID  int  4     
  QuantityPerUnit  nvarchar  40     
  UnitPrice  money  8     
  UnitsInStock  smallint  2     
  UnitsOnOrder  smallint  2     
  ReorderLevel  smallint  2     
  Discontinued  bit  1     
  CategoryName  nvarchar  30     
Total: 11 column(s)

Objects that [dbo].[Alphabetical list of products] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Products  dbo  Table  2 
Total: 3 objects

SQL

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))

See Also

List of views


View: Category Sales for 1997

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  CategoryName  nvarchar  30     
  CategorySales  money  8     
Total: 2 column(s)

Objects that [dbo].[Category Sales for 1997] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
  Product Sales for 1997  dbo  View  4 
Total: 9 objects

SQL

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

See Also

List of views


View: Current Product List

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  ProductID  int  4     
  ProductName  nvarchar  80     
Total: 2 column(s)

Objects that [dbo].[Current Product List] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Products  dbo  Table  2 
Total: 3 objects

SQL

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

See Also

List of views


View: Customer and Suppliers by City

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  City  nvarchar  30     
  CompanyName  nvarchar  80     
  ContactName  nvarchar  60     
  Relationship  varchar  9     
Total: 4 column(s)

Objects that [dbo].[Customer and Suppliers by City] depends on

Object Name Owner Object Type Dep Level
  Customers  dbo  Table  1 
  Suppliers  dbo  Table  1 
Total: 2 objects

SQL

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

See Also

List of views


View: Invoices

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  ShipName  nvarchar  80     
  ShipAddress  nvarchar  120     
  ShipCity  nvarchar  30     
  ShipRegion  nvarchar  30     
  ShipPostalCode  nvarchar  20     
  ShipCountry  nvarchar  30     
  CustomerID  nchar  10     
  CustomerName  nvarchar  80     
  Address  nvarchar  120     
  City  nvarchar  30     
  Region  nvarchar  30     
  PostalCode  nvarchar  20     
  Country  nvarchar  30     
  Salesperson  nvarchar  62     
  OrderID  int  4     
  OrderDate  datetime  8     
  RequiredDate  datetime  8     
  ShippedDate  datetime  8     
  ShipperName  nvarchar  80     
  ProductID  int  4     
  ProductName  nvarchar  80     
  UnitPrice  money  8     
  Quantity  smallint  2     
  Discount  real  4     
  ExtendedPrice  money  8     
  Freight  money  8     
Total: 26 column(s)

Objects that [dbo].[Invoices] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
Total: 8 objects

SQL

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

See Also

List of views


View: Order Details Extended

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  OrderID  int  4     
  ProductID  int  4     
  ProductName  nvarchar  80     
  UnitPrice  money  8     
  Quantity  smallint  2     
  Discount  real  4     
  ExtendedPrice  money  8     
Total: 7 column(s)

Objects that [dbo].[Order Details Extended] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
Total: 8 objects

Objects that depend on [dbo].[Order Details Extended]

Object Name Owner Object Type Dep Level
  Sales by Category  dbo  View  1 
Total: 1 objects

SQL

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

See Also

List of views


View: Order Subtotals

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  OrderID  int  4     
  Subtotal  money  8     
Total: 2 column(s)

Objects that [dbo].[Order Subtotals] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
Total: 8 objects

Objects that depend on [dbo].[Order Subtotals]

Object Name Owner Object Type Dep Level
  Sales Totals by Amount  dbo  View  1 
  Summary of Sales by Quarter  dbo  View  1 
  Summary of Sales by Year  dbo  View  1 
  Employee Sales by Country  dbo  Procedure  1 
  Sales by Year  dbo  Procedure  1 
Total: 5 objects

SQL

create view "Order Subtotals" AS
SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
FROM "Order Details"
GROUP BY "Order Details".OrderID

See Also

List of views


View: Orders Qry

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  OrderID  int  4     
  CustomerID  nchar  10     
  EmployeeID  int  4     
  OrderDate  datetime  8     
  RequiredDate  datetime  8     
  ShippedDate  datetime  8     
  ShipVia  int  4     
  Freight  money  8     
  ShipName  nvarchar  80     
  ShipAddress  nvarchar  120     
  ShipCity  nvarchar  30     
  ShipRegion  nvarchar  30     
  ShipPostalCode  nvarchar  20     
  ShipCountry  nvarchar  30     
  CompanyName  nvarchar  80     
  Address  nvarchar  120     
  City  nvarchar  30     
  Region  nvarchar  30     
  PostalCode  nvarchar  20     
  Country  nvarchar  30     
Total: 20 column(s)

Objects that [dbo].[Orders Qry] depends on

Object Name Owner Object Type Dep Level
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Orders  dbo  Table  2 
Total: 4 objects

SQL

create view "Orders Qry" AS
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,
    Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
    Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,
    Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

See Also

List of views


View: Product Sales for 1997

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  CategoryName  nvarchar  30     
  ProductName  nvarchar  80     
  ProductSales  money  8     
Total: 3 column(s)

Objects that [dbo].[Product Sales for 1997] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
Total: 8 objects

Objects that depend on [dbo].[Product Sales for 1997]

Object Name Owner Object Type Dep Level
  Category Sales for 1997  dbo  View  1 
Total: 1 objects

SQL

create view "Product Sales for 1997" AS
SELECT Categories.CategoryName, Products.ProductName,
Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
    INNER JOIN (Orders
        INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
    ON Products.ProductID = "Order Details".ProductID
WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))
GROUP BY Categories.CategoryName, Products.ProductName

See Also

List of views


View: Products Above Average Price

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  ProductName  nvarchar  80     
  UnitPrice  money  8     
Total: 2 column(s)

Objects that [dbo].[Products Above Average Price] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Products  dbo  Table  2 
Total: 3 objects

SQL

create view "Products Above Average Price" AS
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)
--ORDER BY Products.UnitPrice DESC

See Also

List of views


View: Products by Category

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  CategoryName  nvarchar  30     
  ProductName  nvarchar  80     
  QuantityPerUnit  nvarchar  40     
  UnitsInStock  smallint  2     
  Discontinued  bit  1     
Total: 5 column(s)

Objects that [dbo].[Products by Category] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Products  dbo  Table  2 
Total: 3 objects

SQL

create view "Products by Category" AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName

See Also

List of views


View: Quarterly Orders

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  CustomerID  nchar  10     
  CompanyName  nvarchar  80     
  City  nvarchar  30     
  Country  nvarchar  30     
Total: 4 column(s)

Objects that [dbo].[Quarterly Orders] depends on

Object Name Owner Object Type Dep Level
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Orders  dbo  Table  2 
Total: 4 objects

SQL

create view "Quarterly Orders" AS
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'

See Also

List of views


View: Sales by Category

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  CategoryID  int  4     
  CategoryName  nvarchar  30     
  ProductName  nvarchar  80     
  ProductSales  money  8     
Total: 4 column(s)

Objects that [dbo].[Sales by Category] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
  Order Details Extended  dbo  View  4 
Total: 9 objects

SQL

create view "Sales by Category" AS
SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName,
    Sum("Order Details Extended".ExtendedPrice) AS ProductSales
FROM     Categories INNER JOIN
        (Products INNER JOIN
            (Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID)
        ON Products.ProductID = "Order Details Extended".ProductID)
    ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
--ORDER BY Products.ProductName

See Also

List of views


View: Sales Totals by Amount

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  SaleAmount  money  8     
  OrderID  int  4     
  CompanyName  nvarchar  80     
  ShippedDate  datetime  8     
Total: 4 column(s)

Objects that [dbo].[Sales Totals by Amount] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
  Order Subtotals  dbo  View  4 
Total: 9 objects

SQL

create view "Sales Totals by Amount" AS
SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM     Customers INNER JOIN
        (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
    ON Customers.CustomerID = Orders.CustomerID
WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')

See Also

List of views


View: Summary of Sales by Quarter

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  ShippedDate  datetime  8     
  OrderID  int  4     
  Subtotal  money  8     
Total: 3 column(s)

Objects that [dbo].[Summary of Sales by Quarter] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
  Order Subtotals  dbo  View  4 
Total: 9 objects

SQL

create view "Summary of Sales by Quarter" AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate

See Also

List of views


View: Summary of Sales by Year

Properties

Owner: dbo 
Schema bound:  
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Columns

Name Data Type Length NULL IsGUID
  ShippedDate  datetime  8     
  OrderID  int  4     
  Subtotal  money  8     
Total: 3 column(s)

Objects that [dbo].[Summary of Sales by Year] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
  Order Subtotals  dbo  View  4 
Total: 9 objects

SQL

create view "Summary of Sales by Year" AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate

See Also

List of views


Stored Procedures


Stored Procedure: CustOrderHist

Properties

Owner: dbo 
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length
  @CustomerID  INPUT  nchar  10 
Total: 1 parameter(s)

Objects that [dbo].[CustOrderHist] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
Total: 8 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: CustOrdersDetail

Properties

Owner: dbo 
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length
  @OrderID  INPUT  int  4 
Total: 1 parameter(s)

Objects that [dbo].[CustOrdersDetail] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
Total: 8 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE CustOrdersDetail @OrderID int
AS
SELECT ProductName,
    UnitPrice=ROUND(Od.UnitPrice, 2),
    Quantity,
    Discount=CONVERT(int, Discount * 100),
    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: CustOrdersOrders

Properties

Owner: dbo 
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length
  @CustomerID  INPUT  nchar  10 
Total: 1 parameter(s)

Objects that [dbo].[CustOrdersOrders] depends on

Object Name Owner Object Type Dep Level
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Orders  dbo  Table  2 
Total: 4 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
    OrderDate,
    RequiredDate,
    ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: Employee Sales by Country

Properties

Owner: dbo 
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length
  @Beginning_Date  INPUT  datetime  8 
  @Ending_Date  INPUT  datetime  8 
Total: 2 parameter(s)

Objects that [dbo].[Employee Sales by Country] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
  Order Subtotals  dbo  View  4 
Total: 9 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure "Employee Sales by Country"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
    (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: Sales by Year

Properties

Owner: dbo 
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length
  @Beginning_Date  INPUT  datetime  8 
  @Ending_Date  INPUT  datetime  8 
Total: 2 parameter(s)

Objects that [dbo].[Sales by Year] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
  Order Subtotals  dbo  View  4 
Total: 9 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure "Sales by Year"
    @Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: SalesByCategory

Properties

Owner: dbo 
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length
  @CategoryName  INPUT  nvarchar  30 
  @OrdYear  INPUT  nvarchar  8 
Total: 2 parameter(s)

Objects that [dbo].[SalesByCategory] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Customers  dbo  Table  1 
  Employees  dbo  Table  1 
  Shippers  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Orders  dbo  Table  2 
  Products  dbo  Table  2 
  Order Details  dbo  Table  3 
Total: 8 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SalesByCategory
    @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
    SELECT @OrdYear = '1998'
END

SELECT ProductName,
    TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
    AND OD.ProductID = P.ProductID
    AND P.CategoryID = C.CategoryID
    AND C.CategoryName = @CategoryName
    AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Stored Procedure: Ten Most Expensive Products

Properties

Owner: dbo 
Encrypted:  
Creation Date: 08.06.2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Objects that [dbo].[Ten Most Expensive Products] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table  1 
  Suppliers  dbo  Table  1 
  Products  dbo  Table  2 
Total: 3 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure "Ten Most Expensive Products" AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures


Users


User: dbo

User Properties

Name: dbo 

SQL

if not exists (select * from dbo.sysusers where name = N'dbo' and uid < 16382)
    EXEC sp_grantdbaccess N'sa', N'dbo'
GO



User: guest

User Properties

Name: guest 

SQL

if not exists (select * from dbo.sysusers where name = N'guest' and uid < 16382 and hasdbaccess = 1)
    EXEC sp_grantdbaccess N'guest'
GO



Roles


Role: public

Properties

Fixed:  

SQL

if not exists (select * from dbo.sysusers where name = N'public' and uid > 16399)
    EXEC sp_addrole N'public'
GO



Role: db_owner

Properties

Fixed:  

Users

Name
  dbo 
Total: 1 user(s)

SQL




Server Info

Properties

Network Name: PDCN 
Language: Cyrillic_General_CI_AS 

Version

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
    May  3 2005 23:18:38
    Copyright (c) 1988-2003 Microsoft Corporation
    Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Databases

Database Owner System
  Northwind  sa   
Total: 1 database(s)

Server Configuration

Name Value
Maximum recovery interval in minutes  0 
Allow updates to system tables  0 
Number of user connections allowed  0 
Number of locks for all users  0 
Number of open database objects  0 
Default fill factor percentage  0 
Allow triggers to be invoked within triggers  1 
Allow remote access  1 
default language  0 
Cross DB Ownership Chaining  0 
Maximum worker threads  255 
Network packet size  4096 
show advanced options  1 
Create DTC transaction for remote procedures  0 
c2 audit mode  0 
default full-text language  1033 
two digit year cutoff  2049 
Memory for index create sorts (kBytes)  0 
Priority boost  0 
remote login timeout  20 
remote query timeout  600 
cursor threshold  -1 
set working set size  0 
user options  0 
affinity mask  0 
Maximum size of a text field in replication.  65536 
Tape retention period in days  0 
cost threshold for parallelism  5 
maximum degree of parallelism  0 
minimum memory per query (kBytes)  1024 
maximum time to wait for query memory (s)  -1 
Minimum size of server memory (MB)  0 
Maximum size of server memory (MB)  2147483647 
Maximum estimated cost allowed by query governor  0 
User mode scheduler uses lightweight pooling  0 
scan for startup stored procedures  0 
AWE enabled in the server  0 
affinity64 mask  0 

Logins


Login: sa

Properties

Name: sa 
Friendly Name: English 
System:  
Default Database: master 
Deny Windows Security Authenticated Connections:  
Type: SQL Server standard 
Permissions to Access the Server:  

Users with this login

Database User
  master  dbo 
  tempdb  dbo 
  model  dbo 
  msdb  dbo 
  pubs  dbo 
  Northwind  dbo 
  aanf  dbo 
  almirante  dbo 
  antinucciIT  dbo 
  aspnetstore  dbo 
  B_INDA  dbo 
  BIC  dbo 
  cast  dbo 
  ciak  dbo 
  CompulsionAsset  dbo 
  distmodel  dbo 
  farmacia  dbo 
  immobiliare  dbo 
  isvema  dbo 
  ivd  dbo 
  juli  dbo 
  lportal  dbo 
  mb_pg  dbo 
  metamedica  dbo 
  mGiove  dbo 
  MTC  dbo 
  ofbiz_ap  dbo 
  ofbiz  dbo 
  ofbiz_mnet  dbo 
  ofbiz2  dbo 
  OLAP_REPOSOTORY_SPAIN  dbo 
  privacy  dbo 
  QM  dbo 
  regina  dbo 
  RIO_WEB  dbo 
  romesweethome  dbo 
  SICVE_TEST  dbo 
  student  dbo 
  sun_marte  dbo 
  sun_terra  dbo 
  sun_terra_door  dbo 
  sun_terra_terminal  dbo 
  sun_venus  dbo 
  sun2_marte  dbo 
  sun2_marte2  dbo 
  sun2_terra  dbo 
  SUSDB  dbo 
  Terra  dbo 
  Terra_copy  dbo 
  TERRA_DOOR  dbo 
  terra_v2  dbo 
  Test  dbo 
  UIPState  dbo 
  UIPstore  dbo 
  uVersion  dbo 
  WH_TEST  dbo 

Roles with this Login

Role
  sysadmin 

SQL

if not exists (select * from master.dbo.syslogins where loginname = N'sa')
BEGIN
    declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
    if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
        select @logindb = N'master'
    if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
        select @loginlang = @@language
    exec sp_addlogin N'sa', null, @logindb, @loginlang
END
GO

See Also

Server overview
Logins list


Login: BUILTIN\Administrators

Properties

Name: BUILTIN\Administrators 
Friendly Name: English 
System:  
Default Database: master 
Deny Windows Security Authenticated Connections:  
Type: Windows security group 
Permissions to Access the Server:  

Roles with this Login

Role
  sysadmin 

SQL

if not exists (select * from master.dbo.syslogins where loginname = N'BUILTIN\Administrators')
    exec sp_grantlogin N'BUILTIN\Administrators'
    exec sp_defaultdb N'BUILTIN\Administrators', N'master'
    exec sp_defaultlanguage N'BUILTIN\Administrators', N'us_english'
GO

See Also

Server overview
Logins list



About

Documentation details

Customer:  
Project: Northwind project 
Created: 02.19.2007