Creating the Database Schema | 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:

CustSupportExecutives: Stores information about the executives, such as the executive id, name, contact details, and the password assigned to the executive.
Departments: Stores information about the departments in the organization, such as the dept ID and department name.
Queries: Stores information about customer queries, such as the query ID, title, description of the query, date of the query, status of the query, and date on which the customer support executives attended the query.
LoginAdministrator: Stores the user name and password for the administrator.
ExecDept: Stores information about executives and their departments.
ExecQuery: Stores the mapping of queries to executives.