Creating the Database Schema

The Online Book Store application uses the BookStore database to store information about the customers, orders, products, and invoices.

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

Listing 3-1: Creating the BookStore Database

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Books] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Books Category] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreditCardType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CreditCardType] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Customers] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrderDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[OrderDetails] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Orders] GO CREATE TABLE [dbo].[Books] (  [BookId] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [CategoryId] [int] NOT NULL , [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Publisher] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [Price] [float] NOT NULL , [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PicturePath] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Books Category] (  [CategoryId] [int] NOT NULL , [Category] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[CreditCardType] ( [Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Customers] (  [CustomerId] [int] NOT NULL , [FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Address1] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Address2] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Email] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Username] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Password] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrderDetails] (  [OrderId] [int] NOT NULL ,  [BookId] [int] NOT NULL ,  [Qty] [int] NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Orders] (  [OrderId] [int] NOT NULL ,  [OrderDate] [datetime] NOT NULL ,  [CustomerId] [int] NOT NULL ,  [Amount] [float] NOT NULL , [CreditCardNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CreditCardType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [CompletionDate] [datetime] NULL  ) ON [PRIMARY] GO 

 

Download this Listing .

The above listing creates the BookStore database for the Online Book Store application. The above code creates the following tables in the database:

• Books : Stores information about the books in the bookstore. This table stores the book id, name, category id, author, publisher, price, description, and physical path of the file that stores the cover picture of the book.

• Books Category : Stores information about the categories into which the books are sorted, such as the category id and category name.

• CreditCardType : Stores information about credit card types, such as Master Card and Visa Card, which the end user will use to pay when placing an order.

• Customers : Stores information about the customers. This table stores the user name, password, first name, last name, address1, address2, city, country, zip code, phone, e-mail, and customer id.

• OrderDetails : Stores information about the books for which the end user has placed an order. This table stores the order id, book id, and quantity of the book ordered.

• Orders : Stores information about the orders placed by an end user, such as the order id, order date, customer id, amount, completion date, credit card number, credit card type, and order status.