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
