电子商务-简单建模（练习篇）
练习篇：
电子商务简单UML模型
个人喜欢UML建模后再建数据库，特此复习下，练习代码如下：
-- IT'S MUST TO CREATE THE DIRECTORY 'F:\Database\DatabaseOptimization\' BEFORE RUNNING THE CODE USE master -- NOCOUNT SET NOCOUNT ON; IF EXISTS (SELECT * FROM dbo.sysdatabases WHERE NAME='DatabaseOptimization') BEGIN DROP DATABASE DatabaseOptimization END CREATE DATABASE DatabaseOptimization ON ( NAME = 'DatabaseOptimization1_DATA', FILENAME = 'F:\Database\DatabaseOptimization\DatabaseOptimization.mdf', SIZE = 10, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) LOG ON( NAME = 'DatabaseOptimization1_LOG', FILENAME = 'F:\Database\DatabaseOptimization\DatabaseOptimization.ldf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) GO USE DatabaseOptimization GO CREATE VIEW [dbo].[VIEW_RANDOM] AS SELECT RAND() AS RANDOM GO CREATE FUNCTION [dbo].[PadLeft] ( @Source varchar(256), @PaddingChar char(1), @TotalLength tinyint ) RETURNS varchar(512) AS BEGIN SET @Source = RTRIM(LTRIM(@Source)); IF(@TotalLength<1) BEGIN SET @TotalLength=1; END DECLARE @Length int; SET @Length = LEN(@Source); IF(@TotalLength <= @Length) BEGIN RETURN @Source; END DECLARE @Result varchar(512); SET @Result = REPLICATE(@PaddingChar, @TotalLength - @Length) + @Source RETURN @Result END GO CREATE FUNCTION [dbo].[Random] ( @Length tinyint ) RETURNS int AS BEGIN IF(@Length<1) BEGIN SET @Length=1; END DECLARE @Index int; DECLARE @Result decimal(38,0); SET @Index=0; SET @Result =1 WHILE(@Index<@Length) BEGIN SET @Result = @Result*10; SET @Index = @Index+1; END DECLARE @Return int; SELECT @Return = CAST(@Result * RANDOM AS int) FROM VIEW_RANDOM; RETURN @Return; END GO CREATE FUNCTION [dbo].[RandomValue] ( ) RETURNS varchar(30) AS BEGIN DECLARE @Result varchar(30); SET @Result = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(23),GETDATE(),121),'-',''),'.',''),' ',''),':','') +'-'+ dbo.padleft( dbo.Random(8) ,'0',8) RETURN @Result END GO DECLARE @CustomerCount int; SET @CustomerCount = 10000; DECLARE @OrderCount int; SET @OrderCount = 10000; DECLARE @ProductTypeLevel1Count int; SET @ProductTypeLevel1Count = 100; DECLARE @ProductTypeLevel2Count int; SET @ProductTypeLevel2Count = 100; DECLARE @ProductCount int; SET @ProductCount = 1000; DECLARE @ProductRandomMax int; SET @ProductRandomMax = 1000; -------------------------------------------------------------------------------------------------------- -- CREATE [Customer] TABLE AND INSERT BASE INFOMATION IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type = 'U') BEGIN DROP TABLE [dbo].[Customer]; END CREATE TABLE [dbo].[Customer]( [ID] bigint NOT NULL PRIMARY KEY IDENTITY, [Name] [varchar](20) NOT NULL, [Email] [varchar](40) NOT NULL, [Password] [varchar](40) NOT NULL, [FirstName] [nvarchar](20) NULL, [LastName] [nvarchar](20) NULL, [Address] [nvarchar](100) NULL, [Phone] [varchar](11) NULL, [Zip] [varchar](6) NULL, [CreateTime] [datetime] NULL DEFAULT (GETDATE()), [IsActive] [bit] NULL DEFAULT (0) ); CREATE UNIQUE INDEX IX_CUSTOMER_NAME ON [dbo].[Customer](Name); DECLARE @Index int; SET @Index = 1; WHILE @Index < @CustomerCount BEGIN INSERT INTO [dbo].[Customer]([Name],[Email],[Password]) VALUES ('JASENKIN'+ dbo.PadLeft(CAST(@Index AS varchar),'0',5),'jasen.kin'+dbo.PadLeft(CAST(@Index AS varchar),'0',5)+'@gmail.com','123456'); SET @Index = @Index+1; END -------------------------------------------------------------------------------------------------------- -- CREATE [Order] TABLE AND INSERT BASE INFOMATION IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type = 'U') BEGIN DROP TABLE [dbo].[Order]; END CREATE TABLE [dbo].[Order]( [ID] [varchar](30) NOT NULL, [CustomerID] bigint NOT NULL, [PostingWay] [nvarchar](50) NULL, [Receiver] [nvarchar](20) NULL, [Address] [nvarchar](100) NULL, [Phone] [varchar](11) NULL, [Zip] [varchar](6) NULL, [Desc] [nvarchar](500) NULL, [OrderDate] [datetime] NULL DEFAULT (GETDATE()), [IsPaid] [bit] NULL DEFAULT (0), [IsDealed] [bit] NULL DEFAULT (0), [DealedTime] [datetime] NULL ); ALTER TABLE [dbo].[Order] ADD CONSTRAINT PK_Order_OrderID PRIMARY KEY ([ID]) ; ALTER TABLE [dbo].[Order] ADD CONSTRAINT FK_Order_OrderID FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[Customer]([ID]) ON DELETE CASCADE; DECLARE CursorCustomer cursor FOR SELECT TOP 10 [ID] FROM [dbo].[Customer] ORDER BY NEWID(); DECLARE @RandomIndex int; DECLARE @RandomCount int; DECLARE @RandomOrderID varchar(30); DECLARE @CursorID bigint; OPEN CursorCustomer; FETCH NEXT FROM CursorCustomer INTO @CursorID; WHILE @@FETCH_STATUS = 0 BEGIN SET @RandomCount = CEILING(RAND()*@OrderCount) ; PRINT @RandomCount; SET @RandomIndex =1; WHILE @RandomIndex < @RandomCount BEGIN SET @RandomOrderID = dbo.RandomValue(); PRINT @RandomOrderID; INSERT INTO [dbo].[Order]([ID],CustomerID,PostingWay,Receiver,Address,Phone,[Desc]) VALUES (@RandomOrderID, @CursorID, 'PostingWay-'+ CAST(@CursorID as varchar), 'jasen.kin', 'sz', '18617119581', 'Desc-'+CAST(@CursorID as varchar)+'-'+@RandomOrderID); SET @RandomIndex = @RandomIndex + 1; END PRINT @CursorID; FETCH NEXT FROM CursorCustomer INTO @CursorID; END CLOSE CursorCustomer; DEALLOCATE CursorCustomer; -------------------------------------------------------------------------------------------------------- -- CREATE [ProductType] TABLE AND INSERT BASE INFOMATION IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductType]') AND type = 'U') BEGIN DROP TABLE [dbo].[ProductType]; END -- THIS TABLE'S DATA IS NOT BIG,SO THERE'S NOT NECESSARY TO CREATE RELATED INDEXES. CREATE TABLE [dbo].[ProductType]( [ID] int NOT NULL PRIMARY KEY IDENTITY, [Title] [nvarchar](100) NOT NULL, [EnglishTile] [nvarchar](100) NULL, [ParentID] int NULL, [Level] int NULL, [OrderID] int NULL, [IsVisible] [bit] NULL DEFAULT(1), [Desc] [nvarchar](500) NULL, [CreateDate] [datetime] NULL DEFAULT (GETDATE()) ); DECLARE @ProductTypeIndex int; SET @ProductTypeIndex = 1; DECLARE @Identity int; WHILE @ProductTypeIndex< @ProductTypeLevel1Count BEGIN INSERT INTO [dbo].[ProductType]([ParentID],[Title],[Level])VALUES(NULL,'',1); SELECT @Identity = @@IDENTITY; UPDATE [dbo].[ProductType] SET [Title] = 'Title'+ CAST(@Identity AS varchar),[EnglishTile]='EnglishTile'+ CAST(@Identity AS varchar), [OrderID] = @Identity * 100 WHERE [ID] = @Identity ; SET @ProductTypeIndex = @ProductTypeIndex + 1 ; END -- CREATE SECOND LEVEL DATA DECLARE CursorProductType cursor FOR SELECT TOP 30 [ID] FROM [dbo].[ProductType] ORDER BY NEWID(); DECLARE @TypeRandomIndex int; DECLARE @TypeRandomCount int; DECLARE @TypeCursorID int; DECLARE @SecondIdentity int; OPEN CursorProductType; FETCH NEXT FROM CursorProductType INTO @TypeCursorID; SET NOCOUNT ON; WHILE @@FETCH_STATUS = 0 BEGIN SET @TypeRandomCount = CEILING(RAND()*@ProductTypeLevel2Count) ; PRINT @TypeRandomCount; SET @TypeRandomIndex =1; WHILE @TypeRandomIndex < @TypeRandomCount BEGIN INSERT INTO [dbo].[ProductType]([ParentID],[Title],[Level])VALUES(@TypeCursorID,'',2); SELECT @SecondIdentity = @@IDENTITY; UPDATE [dbo].[ProductType] SET [Title] = 'Title'+ CAST(@SecondIdentity AS varchar),[EnglishTile]='EnglishTile'+ CAST(@SecondIdentity AS varchar), [OrderID] = @SecondIdentity * 100 WHERE [ID] = @SecondIdentity ; SET @TypeRandomIndex =@TypeRandomIndex+1; END PRINT @TypeCursorID; FETCH NEXT FROM CursorProductType INTO @TypeCursorID; END CLOSE CursorProductType; DEALLOCATE CursorProductType; -------------------------------------------------------------------------------------------------------- -- CREATE [Product] TABLE AND INSERT BASE INFOMATION IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type = 'U') BEGIN DROP TABLE [dbo].[Product]; END CREATE TABLE [dbo].[Product]( [ID] [bigint] PRIMARY KEY IDENTITY NOT NULL, [Name] [nvarchar](100) NOT NULL, [Desc] [nvarchar](Max) NULL, [IsRecommended] [bit] NULL, [CostPrice] [decimal](38, 2) NULL, [SalePrice] [decimal](38, 2) NULL, [ImageUrl] [nvarchar](200) NULL, [RemainedQuantity] [int] NULL, [ProductTypeID] [int] NULL, [Hits] [int] NULL DEFAULT (0), [DiscountRate] [decimal](8, 1) NULL DEFAULT (1), [CreateTime] [datetime] NULL DEFAULT (GETDATE()), [UpdateTime] [datetime] NULL DEFAULT (GETDATE()), [IsValid] [bit] NOT NULL DEFAULT (1) ) ALTER TABLE [dbo].[Product] ADD CONSTRAINT FK_Product_ProductTypeID_ProductType_ID FOREIGN KEY ([ProductTypeID]) REFERENCES [dbo].[ProductType]([ID]) ON DELETE CASCADE; -- CREATE SECOND LEVEL DATA DECLARE CursorProductTypeWithLevel CURSOR FOR SELECT TOP 3000 [ID] FROM [dbo].[ProductType] WHERE [Level] = 2 ORDER BY NEWID(); DECLARE @ProductTypeRandomIndex int; DECLARE @ProductTypeRandomCount int; DECLARE @ProductTypeCursorID int; DECLARE @ProductIdentity int; OPEN CursorProductTypeWithLevel; FETCH NEXT FROM CursorProductTypeWithLevel INTO @ProductTypeCursorID; SET NOCOUNT ON; WHILE @@FETCH_STATUS = 0 BEGIN SET @ProductTypeRandomCount = CEILING(RAND()* @ProductRandomMax) ; PRINT @ProductTypeRandomCount; SET @ProductTypeRandomIndex =1; WHILE @ProductTypeRandomIndex < @ProductTypeRandomCount BEGIN INSERT INTO [dbo].[Product]([ProductTypeID],[Name],[RemainedQuantity],[CostPrice],[SalePrice])VALUES( @ProductTypeCursorID,'ProductName',@ProductTypeRandomCount,@ProductTypeRandomCount,@ProductTypeRandomCount*10/9); SELECT @ProductIdentity = @@IDENTITY; UPDATE [dbo].[Product] SET [Name] = [Name] + CAST(@ProductIdentity AS varchar),[Desc]='description'+ CAST(@ProductIdentity AS varchar), [Hits] = @ProductIdentity * 100 WHERE [ID] = @ProductIdentity ; SET @ProductTypeRandomIndex = @ProductTypeRandomIndex + 1 ; END FETCH NEXT FROM CursorProductTypeWithLevel INTO @ProductTypeCursorID; END CLOSE CursorProductTypeWithLevel; DEALLOCATE CursorProductTypeWithLevel; -------------------------------------------------------------------------------------------------------- -- CREATE [OrderDetail] TABLE AND INSERT BASE INFOMATION IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderDetail]') AND type = 'U') BEGIN DROP TABLE [dbo].[OrderDetail]; END CREATE TABLE [dbo].[OrderDetail]( [ID] [bigint] PRIMARY KEY IDENTITY NOT NULL, [OrderID] [varchar](30) NOT NULL, [ProductID] [bigint] NOT NULL, [Price] [decimal](38, 2) NULL, [Quantity] [int] NULL ) ALTER TABLE [dbo].[OrderDetail] ADD CONSTRAINT [FK_OrderDetail_Order_OrderID] FOREIGN KEY([OrderID]) REFERENCES [dbo].[Order] ([ID]) ON DELETE CASCADE; ALTER TABLE [dbo].[OrderDetail] ADD CONSTRAINT [FK_OrderDetail_Product_ProductID] FOREIGN KEY([ProductID]) REFERENCES [dbo].[Product] ([ID]) ON DELETE CASCADE; DECLARE CursorOrder CURSOR FOR SELECT TOP 300 [ID] FROM [dbo].[Order] ORDER BY NEWID(); DECLARE @DetailQuantity int; DECLARE @OrderCursorID varchar(30); DECLARE @ProductCursorDiscountRate decimal(38,2); DECLARE @ProductCursorSalePrice decimal(8,1); DECLARE @ProductCursorID bigint; OPEN CursorOrder; FETCH NEXT FROM CursorOrder INTO @OrderCursorID; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CursorProduct CURSOR FOR SELECT TOP 10000 [ID],DiscountRate,SalePrice FROM [dbo].[Product] ORDER BY NEWID(); OPEN CursorProduct; FETCH NEXT FROM CursorProduct INTO @ProductCursorID,@ProductCursorDiscountRate,@ProductCursorSalePrice; WHILE @@FETCH_STATUS = 0 BEGIN SET @DetailQuantity = CEILING(RAND()*50) ; INSERT INTO [dbo].[OrderDetail] ([OrderID] ,[ProductID] ,[Price] ,[Quantity]) VALUES ( @OrderCursorID ,@ProductCursorID ,@ProductCursorDiscountRate * @ProductCursorSalePrice ,@DetailQuantity) FETCH NEXT FROM CursorProduct INTO @ProductCursorID,@ProductCursorDiscountRate,@ProductCursorSalePrice; END CLOSE CursorProduct; DEALLOCATE CursorProduct; FETCH NEXT FROM CursorOrder INTO @OrderCursorID; END CLOSE CursorOrder; DEALLOCATE CursorOrder;
关于是否建立外键，取决于系统的数据是否需要支持完整性。以上需要的外键全部建立了，索引方面的调优的时候再创建。
