Джеффри Мак-Манус - Обработка баз данных на Visual Basic®.NET

and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[EmployeeDepartment_view]

GO

/****** Object: View dbo.qryEmployee_view Script Date: 10-Jul-02 12:41:09 PM ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryEmployee_view]')

and OBJECTPROPERTY(id,N'IsView') = 1)

drop view [dbo].[qryEmployee_view]

GO

/****** Object: Table [dbo].[tblCustomer] Script Date:10-Jul-0212:41:09 PM ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCustomer]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tblCustomer]

GO

/****** Object: Table [dbo].[tblDepartment] Script Date: 10-Jul-02 12:41:09 PM ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDepartment]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tblDepartment]

GO

/****** Object: Table [dbo].[tblEmployee] Script Date: 10-Jul-02 12:41:09 PM ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEmployee]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

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]

GO

ALTER TABLE [dbo].[tblDepartment] WITH NOCHECK ADD

 CONSTRAINT [tblDepartment_IDPK] PRIMARY KEY CLUSTERED (

  [ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo]. [tblEmployee] WITH NOCHECK ADD

 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED (

  [ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblInventory] WITH NOCHECK ADD

 CONSTRAINT [PK_tblInventory] PRIMARY KEY CLUSTERED (

  [ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblOrder] WITH NOCHECK ADD

 CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED (

  [ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblOrderltem] WITH NOCHECK ADD

 CONSTRAINT [PK_tblOrderItem] PRIMARY KEY CLUSTERED (

  [ID]

 ) ON [PRIMARY]

GO

ALTER TABLE [dbo]. [tblRegion] WITH NOCHECK ADD

 CONSTRAINT [PK_tblRegion] PRIMARY KEY CLUSTERED (

  [ID]

) ON [PRIMARY]

GO

CREATE UNIQUE INDEX [IX_tblRegion] ON [dbo]. [tblRegion] ([State]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblCustomer] ADD

 CONSTRAINT [FK_tblCustomer_tblRegion] FOREIGN KEY ([State])

 references [dbo].[tblRegion] (

  [State]

 ) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[tblEmployee] ADD

 CONSTRAINT [FK_tblEmployee_tblDepartment] FOREIGN KEY ([DepartmentID])

 REFERENCES [dbo].[tblDepartment] (

  [ID]

 ) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo]. [tblOrder] ADD

 CONSTRAINT [FK_tblOrder_tblCustomer] FOREIGN KEY ( [CustomerID])

 REFERENCES [dbo].[tblCustomer] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[tblOrderItem] ADD

 CONSTRAINT [FK_tblOrderItem_tblInventory] FOREIGN KEY ([ItemID])

 REFERENCES [dbo].[tblInventory] ([ID])

 ON DELETE CASCADE ON UPDATE CASCADE,

 constraint [FK_tblOrderItem_tblOrder] foreign key ([OrderID])

 REFERENCES [dbo].[tblOrder] ([ID])

 ON DELETE CASCADE ON UPDATE CASCADE

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Наконец, для создания представлений, хранимых процедур и триггеров следует выполнить сценарий из листинга 3.6.

Листинг 3.6. Сценарий создания представлений, хранимых процедур и триггеров

/****** object: View dbo.EmployeeDepartment_view Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE view EmployeeDepartment_view as

 select e.ID, FirstName, LastName, DepartmentName

 from tblEmployee e, tblDepartment t

 where e.DepartmentID = t.ID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: View dbo.qryEmployee_view Script Date: 10-Jul-02 12:41:13 PM ******/

create view qryEmployee_view as

 SELECT ID, FirstName, LastName, DepartmentID from tblEmployee

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.DeleteEmployee Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE PROCEDURE dbo.DeleteEmployee (@Original_ID int)

AS

 SET NOCOUNT OFF;

 DELETE FROM tblEmployee WHERE (ID = @Original_ID)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.GetCustomerFromID Script Date: 10-Jul-02 12:41:13 PM ******/

create procedure GetCustomerFromID @custID int

as

 select * from tblCustomer where ID = @custID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

/****** Object: Stored Procedure Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE PROCEDURE dbo.InsertEmployee (

 @FirstName varchar(50),

 @LastName varchar(70),

 @DepartmentID int,

 @Salary money)

AS

 SET NOCOUNT OFF;

 if (@Salary = 0 or @Salary is null) begin

  -– Do complicated salary calculations

  set @Salary = @DepartmentID * 10000

 end

 INSERT INTO tblEmployee(FirstName, LastName, Salary) VALUES

  (@FirstName, @LastName, @DepartmentID, @Salary)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON