createdbobjects.sql

电子商务入门套件是一个电子商务网络店面

USE [~~##CMRCStarterKitDB##~~]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMRC_Products] DROP CONSTRAINT FK_Products_Categories
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMRC_Orders] DROP CONSTRAINT FK_Orders_Customers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_OrderDetails_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMRC_OrderDetails] DROP CONSTRAINT FK_OrderDetails_Orders
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Reviews_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMRC_Reviews] DROP CONSTRAINT FK_Reviews_Products
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ShoppingCart_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMRC_ShoppingCart] DROP CONSTRAINT FK_ShoppingCart_Products
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_CustomerAdd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_CustomerAdd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_CustomerAlsoBought]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_CustomerAlsoBought]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_CustomerDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_CustomerDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_CustomerLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_CustomerLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_OrdersAdd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_OrdersAdd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_OrdersDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_OrdersDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_OrdersList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_OrdersList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ProductCategoryList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ProductCategoryList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ProductDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ProductDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ProductsByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ProductsByCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ProductsMostPopular]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ProductsMostPopular]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ProductSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ProductSearch]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ReviewsAdd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ReviewsAdd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ReviewsList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ReviewsList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartAddItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartAddItem]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartEmpty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartEmpty]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartItemCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartItemCount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartMigrate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartMigrate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartRemoveAbandoned]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartRemoveAbandoned]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartRemoveItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartRemoveItem]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartTotal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartTotal]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCartUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CMRC_ShoppingCartUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMRC_Categories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMRC_Customers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_OrderDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMRC_OrderDetails]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMRC_Orders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMRC_Products]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_Reviews]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMRC_Reviews]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMRC_ShoppingCart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMRC_ShoppingCart]
GO

CREATE TABLE [dbo].[CMRC_Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CMRC_Customers] (
	[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
	[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CMRC_OrderDetails] (
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[Quantity] [int] NOT NULL ,
	[UnitCost] [money] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CMRC_Orders] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[OrderDate] [datetime] NOT NULL ,
	[ShipDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CMRC_Products] (
	[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryID] [int] NOT NULL ,
	[ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UnitCost] [money] NOT NULL ,
	[Description] [nvarchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CMRC_Reviews] (
	[ReviewID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustomerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Rating] [int] NOT NULL ,
	[Comments] [nvarchar] (3850) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CMRC_ShoppingCart] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[CartID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Quantity] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[DateCreated] [datetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CMRC_Categories] ADD 
	CONSTRAINT [PK_CMRC_Categories] PRIMARY KEY  NONCLUSTERED 
	(
		[CategoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CMRC_Customers] ADD 
	CONSTRAINT [PK_CMRC_Customers] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Customers] UNIQUE  NONCLUSTERED 
	(
		[EmailAddress]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CMRC_OrderDetails] ADD 
	CONSTRAINT [PK_CMRC_OrderDetails] PRIMARY KEY  NONCLUSTERED 
	(
		[OrderID],
		[ProductID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CMRC_Orders] ADD 
	CONSTRAINT [DF_Orders_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
	CONSTRAINT [DF_Orders_ShipDate] DEFAULT (getdate()) FOR [ShipDate],
	CONSTRAINT [PK_CMRC_Orders] PRIMARY KEY  NONCLUSTERED 
	(
		[OrderID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CMRC_Products] ADD 
	CONSTRAINT [PK_CMRC_Products] PRIMARY KEY  NONCLUSTERED 
	(
		[ProductID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CMRC_ShoppingCart] ADD 
	CONSTRAINT [DF_ShoppingCart_Quantity] DEFAULT (1) FOR [Quantity],
	CONSTRAINT [DF_ShoppingCart_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_CMRC_ShoppingCart] PRIMARY KEY  NONCLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_ShoppingCart] ON [dbo].[CMRC_ShoppingCart]([CartID], [ProductID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CMRC_OrderDetails] ADD 
	CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [dbo].[CMRC_Orders] (
		[OrderID]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[CMRC_Orders] ADD 
	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[CMRC_Customers] (
		[CustomerID]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[CMRC_Products] ADD 
	CONSTRAINT [FK_Products_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [dbo].[CMRC_Categories] (
		[CategoryID]
	)
GO

ALTER TABLE [dbo].[CMRC_Reviews] ADD 
	CONSTRAINT [FK_Reviews_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[CMRC_Products] (
		[ProductID]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[CMRC_ShoppingCart] ADD 
	CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[CMRC_Products] (
		[ProductID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE Procedure CMRC_CustomerAdd
(
    @FullName   nvarchar(50),
    @Email      nvarchar(50),
    @Password   nvarchar(50),
    @CustomerID int OUTPUT
)
AS

INSERT INTO CMRC_Customers
(
    FullName,
    EmailAddress,
    Password
)

VALUES
(
    @FullName,
    @Email,
    @Password
)

SELECT
    @CustomerID = @@Identity


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_CustomerAlsoBought
(
    @ProductID int
)
As

/* We want to take the top 5 products contained in
    the orders where someone has purchased the given Product */
SELECT  TOP 5 
    CMRC_OrderDetails.ProductID,
    CMRC_Products.ModelName,
    SUM(CMRC_OrderDetails.Quantity) as TotalNum

FROM    
    CMRC_OrderDetails
  INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID = CMRC_Products.ProductID

WHERE   OrderID IN 
(
    /* This inner query should retrieve all orders that have contained the productID */
    SELECT DISTINCT OrderID 
    FROM CMRC_OrderDetails
    WHERE ProductID = @ProductID
)
AND CMRC_OrderDetails.ProductID != @ProductID 

GROUP BY CMRC_OrderDetails.ProductID, CMRC_Products.ModelName 

ORDER BY TotalNum DESC


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_CustomerDetail
(
    @CustomerID int,
    @FullName   nvarchar(50) OUTPUT,
    @Email      nvarchar(50) OUTPUT,
    @Password   nvarchar(50) OUTPUT
)
AS

SELECT 
    @FullName = FullName, 
    @Email    = EmailAddress, 
    @Password = Password

FROM 
    CMRC_Customers

WHERE 
    CustomerID = @CustomerID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_CustomerLogin
(
    @Email      nvarchar(50),
    @Password   nvarchar(50),
    @CustomerID int OUTPUT
)
AS

SELECT 
    @CustomerID = CustomerID
    
FROM 
    CMRC_Customers
    
WHERE 
    EmailAddress = @Email
  AND 
    Password = @Password

IF @@Rowcount < 1 
SELECT 
    @CustomerID = 0

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartEmpty
(
    @CartID nvarchar(50)
)
AS

DELETE FROM CMRC_ShoppingCart

WHERE 
    CartID = @CartID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE Procedure CMRC_OrdersAdd
(
    @CustomerID int,
    @CartID     nvarchar(50),
    @OrderDate  datetime,        
    @ShipDate   datetime,
    @OrderID    int OUTPUT
)
AS

BEGIN TRAN AddOrder

/* Create the Order header */
INSERT INTO CMRC_Orders
(
    CustomerID, 
    OrderDate, 
    ShipDate
)
VALUES
(   
    @CustomerID, 
    @OrderDate, 
    @ShipDate
)

SELECT
    @OrderID = @@Identity    

/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO CMRC_OrderDetails
(
    OrderID, 
    ProductID, 
    Quantity, 
    UnitCost
)

SELECT 
    @OrderID, 
    CMRC_ShoppingCart.ProductID, 
    Quantity, 
    CMRC_Products.UnitCost

FROM 
    CMRC_ShoppingCart 
  INNER JOIN CMRC_Products ON CMRC_ShoppingCart.ProductID = CMRC_Products.ProductID
  
WHERE 
    CartID = @CartID

/* Removal of  items from user's shopping cart will happen on the business layer*/
EXEC CMRC_ShoppingCartEmpty @CartID

COMMIT TRAN AddOrder


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_OrdersDetail
(
    @OrderID    int,
    @CustomerID int,
    @OrderDate  datetime OUTPUT,
    @ShipDate   datetime OUTPUT,
    @OrderTotal money OUTPUT
)
AS

/* Return the order dates from the Orders
    Also verifies the order exists for this customer. */
SELECT 
    @OrderDate = OrderDate,
    @ShipDate = ShipDate
    
FROM    
    CMRC_Orders
    
WHERE   
    OrderID = @OrderID
    AND
    CustomerID = @CustomerID

IF @@Rowcount = 1
BEGIN