Creating the Database Schema

The Cover Designer application uses the CoverDesigner database to store information about the images that end users can select and drop on the Visio drawing control to design the cover for products.

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

Listing 6-1: Creating the CoverDesigner Database

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImageCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Image Category] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Images]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Images] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Stencils]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Stencils] GO CREATE TABLE [dbo].[ImageCategory] (    [imageCategoryId] [int] NOT NULL ,    [imageCategory] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [imagePath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [stencilId] [int] NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Images] (    [imageID] [int] NOT NULL ,    [imageCategoryId] [int] NOT NULL ,    [imageFileName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [imageDescription] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [imageSource] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Stencils] (    [stencilId] [int] NOT NULL ,    [stencilName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [stencilPath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO 

 

Download this Listing .

The code in the above listing creates the CoverDesigner database for the Cover Designer application. The above code creates the following tables in the database:

• ImageCategory: Stores information about the image categories provided by the Cover Designer application. It stores the category id, name of the category, path on the hard disk and stencil id.

• Images: Stores information about the images provided by the Cover Designer application. It stores the image Id, category Id of the image, the name of the image file, the image description, and source of the image file.

• Stencils: Stores information about the stencils provided by the Cover Designer application. It stores the stencil id, stencil name, and its path on the hard disk.