Creating the Database Schema

The Information Retrieval Visio XML application uses the services_info database to retrieve information about services, companies, and locations.

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

Listing 7-1: Creating the services_info Database

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [CompanyService]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CompanyService] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [Location]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Location] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [NearByLocations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[NearByLocations] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]. [Services]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Services] GO CREATE TABLE [dbo].[CompanyService] (    [SId] [int] NOT NULL , [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [LId] [int] NOT NULL , [Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Location] ( [LId] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL , [LocationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[NearByLocations] (    [LId] [int] NOT NULL ,    [NbLId] [int] NOT NULL ,    [Distance] [float] NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Services] ( [SId] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL , [ServiceCategory] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO 

 

Download this Listing .

The above listing creates the services_info database for the Information Retrieval Visio XML application. The above code creates the following tables in the database:

• Services : Stores information on service categories. This table stores the service id and service category name .

• Location : Stores information on locations of the services. This table stores the location id and location name.

• NearByLocations : Stores information on other locations that are close to the locations in the Location table. This table stores two location ids; one for the location present in the Location table and the other for the location that is close to a location present in the Location table. The table also stores the distance of the nearby location from a particular location.

• CompanyService : Stores information on services provided by various companies. This table stores the service id, company name, address, location id, and phone number of the company.