Обработка баз данных на Visual Basic®.NET

drop table [dbo].[tblEmployee]
GO
/****** Object: Table [dbo].[tblInventory] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N' [dbo].[tblInventory]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblInventory]
GO
/****** Object: Table [dbo].[tblOrder] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblOrder]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblOrder]
GO
/****** object: Table [dbo].[tblOrderItem] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblOrderItem]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblOrderltem]
GO
/****** object: Table [dbo].[tblRegion] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegion]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegion]
GO

Теперь можно приступать к созданию новых объектов базы данных. Начнем с создания таблиц; сценарий этих операций приведен в листинге 3.4.

Листинг 3.4. Сценарий создания таблиц базы данных Novelty

/****** Object: Table [dbo].[tblCustomer] Script Date: 10-Jul-02 12:41:10 PM ******/
CREATE TABLE [dbo].[tblCustomer] (
 [ID] [int] IDENTITY (1, 1) NOT NULL,
 [FirstName] [varchar] (20) COLLATE Latin1_General_CI_AI NULL,
 [LastName] [varchar] (30) COLLATE Latinl_General_CI_AI NULL,
 [Company] [varchar] (50) COLLATE Latin1_General_CI_AI NULL,
 [Address] [varchar] (50) COLLATE Latin1_General_CI_AI NULL,
 [City] [varchar] (30) COLLATE Latin1_General_CI_AI_NULL,
 [State] [char] (2) COLLATE Latin1_General_CI_AI_NULL,
 [PostalCode] [varchar] (9) COLLATE Latin1_General_CI_AI NULL,
 [Phone] [varchar] (15) COLLATE Latinl_General_CI_AI NULL,
 [Fax] [varchar] (15) COLLATE Latinl_General_CI_AI NULL,
 [Email] [varchar] (100) COLLATE Latinl_General_CI_AI NULL,
 [LastNameSoundex] [varchar] (4) COLLATE Latinl_General_CI_AI NULL
) ON [PRIMARY]
GO
/****** object: Table [dbo].[tblDepartment] Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblDepartment] (
 [ID] [int] IDENTITY (1, 1) NOT NULL,
 [DepartmentName] [varchar] (75) COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblEmployee] Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblEmployee] (
 [ID] [int] IDENTITY (1, 1) NOT NULL,
 [FirstName] [varchar] (50) COLLATE Latin1_General_CI_AI NOT NULL,
 [LastName] [varchar] (70) COLLATE Latin1_General_CI_AI NOT NULL,
 [DepartmentID] [int] NULL,
 [Salary] [money] NULL
) ON [PRIMARY]
GO
/****** object: Table [dbo]. [tblInventory] Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblInventory] (
 [ID] [int] IDENTITY (1, 1) NOT NULL,
 [ProductName] [varchar] (75) COLLATE Latin1_General_CI_AI NOT NULL
 [WholesalePrice] [money] NULL,
 [RetailPrice] [money] NULL,
 [Description] [ntext] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** object: Table [dbo].[tblOrder] Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE [dbo].[tblOrder] (
 [ID] [int] IDENTITY (1, 1) NOT NULL,
 [CustomerlD] [int] NULL,
 [OrderDate] [datetime] NULL,
 [Amount] [money] NULL
) ON [PRIMARY]
GO
/****** object: Table [dbo].[tblOrderItem] Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE] (
 [ID] [int] IDENTITY (1, 1) NOT NULL, [OrderID] [int] NOT NULL,
 [int] NOT NULL, [Quantity] [int] NULL,
 [Cost] [money] NULL
) ON [PRIMARY]
GO
/******* object: Table [dbo].[tblRegion] Script Date: 10-Jul -02 12:41:12 PM *******/
CREATE TABLE [dbo]. [tblRegion] (
 [ID] [int] IDENTITY (1, 1) NOT NULL,
 [State] [char] (2) COLLATE Latin1_General_CI_AI NOT NULL,
 [RegionName] [varchar] (25) COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY]
GO

Далее следует создать ограничения, как показано в листинге 3.5.

Листинг 3.5. Сценарий создания ограничений для базы данных Novelty

ALTER TABLE [dbo].[tblCustomer] WITH NOCHECK ADD
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED (
 [ID]
 ) ON [PRIMARY]