Creating the Database Schema

The Order Processing application uses the OrderProcessing database to store information about the customers, orders, products, and invoices.

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

Listing 2-1: Creating the OrderProcessing Database

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]')  and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Company] 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].[InvoiceCreditCardInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[InvoiceCreditCardInfo] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoices]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Invoices] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrderInformation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[OrderInformation] 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 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]')  and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Products] GO CREATE TABLE [dbo].[Company] ( [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [City] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Country] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ZipCode] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Customers] ( [customerId] [int] IDENTITY (1, 1) NOT NULL , [username] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [password] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [firstName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [city] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [country] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [zipCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[InvoiceCreditCardInfo] ( [invoiceID] [int] NOT NULL , [nameOnCard] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [creditCardNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Invoices] ( [invoiceId] [int] IDENTITY (1, 1) NOT NULL , [invoiceDate] [datetime] NOT NULL , [orderId] [int] NOT NULL , [customerId] [int] NOT NULL , [subTotal] [float] NOT NULL , [shipping_handlingCost] [float] NOT NULL , [totalAmount] [float] NOT NULL , [paymentMode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrderInformation] ( [orderId] [int] NOT NULL , [productId] [int] NOT NULL , [qty] [int] NOT NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Orders] ( [orderId] [int] IDENTITY (1, 1) NOT NULL , [orderDate] [datetime] NOT NULL , [amount] [float] NOT NULL , [customerId] [int] NOT NULL , [status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [completionDate] [datetime] NULL  ) ON [PRIMARY] GO CREATE TABLE [dbo].[Products] ( [productId] [int] IDENTITY (1, 1) NOT NULL , [product] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [unitPrice] [float] NOT NULL , [qtyInHand] [int] NOT NULL  ) ON [PRIMARY] GO 

 

Download this Listing .

The above listing creates the OrderProcessing database for the Order Processing application. The above code creates the following tables in the database:

• Company: Stores information about the company that uses the Order Processing application. It stores the company name, address, city, country, zip code, and phone.

• Customers: Stores information about the customers. It stores the customer id, user name, password, first name, last name, address1, address2, city, country, and zip code.

• InvoiceCreditCardInfo: Stores information about the credit card that a customer uses to pay for an order when an invoice is generated. It stores the invoice id, name on the credit card, and credit card number.

• Invoices: Stores information about the invoices that the administrator generates when an order is completed. It stores the invoice id, order id, customer id, invoice date, subtotal, shipping and handling cost, total amount, and payment mode.

• OrderInformation: Stores information about the products in the order that a customer places. It stores the order id, product id, and quantity of product.

• Orders: Stores information about the orders. It stores the order id, order date, customer id, amount, completion date, and order status.

• Products: Stores information about the products, such as product id, product name, unit price, and quantity available.