NET InstantCode. UML with Visio and Visual Studio .NET

The Query Tracker application uses the QueryTracker database to store information about the executives and the queries they handle.

Listing 4-1 shows the SQL script to generate the database on the SQL Server:

Listing 4-1: Creating the QueryTracker Database

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. _ [CustSupportExecutives]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CustSupportExecutives] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Departments]') _ and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Departments] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExecDept]') _ and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[ExecDept] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExecQuery]') _ and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[ExecQuery] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoginAdministrator]') _ and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[LoginAdministrator] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Queries]') _ and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Queries] GO CREATE TABLE [dbo].[CustSupportExecutives] ( [EId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Pwd] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Departments] ( [DId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ExecDept] ( [EId] [int] NOT NULL , [DId] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[ExecQuery] ( [EId] [int] NOT NULL , [QueryId] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[LoginAdministrator] ( [AdminName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [AdminPwd] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Queries] ( [QueryId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [QueryTitle] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [QueryDesc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [QueryDate] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Customer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CompDate] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO

 

Download this Listing .

The above listing creates the QueryTracker database for the Query Tracker application. The above code creates the following tables in the database:

Категории