【正文】 ON [dbo].[OrderDetail] TO [訂單管理員]GOGRANT SELECT ON [dbo].[OrderDetail] TO [會員]GO ：CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED ( [OrderDetailID] ) ON [PRIMARY] ,CONSTRAINT [CK_OrderDetail_CostumeNum] CHECK ([CostumeNum] 0)CONSTRAINT [FK_OrderDetail_Costume] FOREIGN KEY ( [CostumeID] ) REFERENCES [dbo].[Costume] ( [CostumeID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_OrderDetail_OrderInfo] FOREIGN KEY ( [OrderID] ) REFERENCES [dbo].[BusinessOrder] ( [OrderID] ) ON DELETE CASCADE ON UPDATE CASCADE216。 Store：Store :dbo：CREATE TABLE [dbo].[Store] ( [CostumeID] [int] NOT NULL , [Store] [int] NOT NULL , [Lowest] [int] NOT NULL ) ON [PRIMARY]GO：GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Store] TO [服裝管理員]GO：CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED ( [CostumeID] ) ON [PRIMARY] , CONSTRAINT [CK_Store_Lowest] CHECK ([Lowest] = 0)CONSTRAINT [FK_Store_Costume] FOREIGN KEY ( [CostumeID] ) REFERENCES [dbo].[Costume] ( [CostumeID] ) ON DELETE CASCADE ON UPDATE CASCADE216。 newpost：newpost :dbo：CREATE TABLE [dbo].[newpost] ( [postid] [int] IDENTITY (1000, 1) NOT NULL , [subject] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL , [posttime] [datetime] NOT NULL , [userid] [int] NOT NULL , [replies] [int] NOT NULL , [postviews] [int] NOT NULL , [message] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO：GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[newpost] TO [會員]GOGRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[newpost] TO [論壇管理員]GO：CONSTRAINT [PK_newpost] PRIMARY KEY CLUSTERED ( [postid] ) ON [PRIMARY]CONSTRAINT [FK_newpost_Member] FOREIGN KEY ( [userid] ) REFERENCES [dbo].[Member] ( [MemberID] ) ON DELETE CASCADE ON UPDATE CASCADE 216。 reply：reply :dbo：CREATE TABLE [dbo].[reply] ( [replyid] [int] IDENTITY (1000, 1) NOT NULL , [userid] [int] NOT NULL , [message] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL , [postid] [int] NOT NULL , [replytime] [datetime] NOT NULL ) ON [PRIMARY]GO：GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[reply] TO [會員]GOGRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[reply] TO [論壇管理員]GO：CONSTRAINT [PK_reply] PRIMARY KEY CLUSTERED ( [replyid] ) ON [PRIMARY]CONSTRAINT [FK_reply_Member] FOREIGN KEY ( [userid] ) REFERENCES [dbo].[Member] ( [MemberID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_reply_newpost] FOREIGN KEY ( [postid] ) REFERENCES [dbo].[newpost] ( [postid] ) 為目標數(shù)據(jù)庫管理系統(tǒng)設計業(yè)務規(guī)則： 規(guī)則：216。 rule[Sex]create rule [Sex] as @ in (39。男39。,39。女39。)GO216。 rule[TypeHaveChild]create rule [TypeHaveChild] as @ in (39。y39。,39。n39。)GO216。 rule[IsSpecial]create rule [IsSpecial] as @ in (39。y39。,39。n39。)GO216。 rule[OrderIsConfirm]create rule [OrderIsConfirm] as @ in (39。y39。,39。n39。)GO216。 rule[Actor]create rule [Actor] as @ in(39。超級管理員39。,39。用戶管理員39。,39。論壇管理員39。,39。服裝管理員39。,39。訂單管理員39。,39。公告管理員39。)GO216。 rule[OrderSendStatus]create rule [OrderSendStatus] as @ in(39。039。,39。139。,39。239。)GO 觸發(fā)器：216。 BusinessOrder_Delete 1. 觸發(fā)器名：BusinessOrder_Delete ：dbo3．所屬表：BusinessOrder_：當刪除訂購單BusinessOrder中的一條記錄時,就刪除該訂購單所對應的所有詳細訂單的信息 5．源代碼： CREATE TRIGGER BusinessOrder_Delete ON BusinessOrderFOR DELETE AS begin declare @t int select @t=OrderID from deleted delete from OrderDetail where OrderID=@tendGO216。 CostumeDelete1. 觸發(fā)器名：CostumeDelete：dbo3．所屬表：Costume：當刪除一種服裝時，說明該系統(tǒng)不再出售該種服裝，那么該服裝所對應的訂購單也要刪除 5．源代碼：CREATE TRIGGER CostumeDelete ON Costume FOR DELETEAS begin declare @t int select @t=CostumeID from deleted delete from OrderDetail where CostumeID=@tendGO216。 CostumeType_Delete1. 觸發(fā)器名：CostumeType_Delete ：dbo3．所屬表：CostumeType： 刪除服裝類型表中的一條記錄時,就刪除該類型所對應的服裝品種的信息5．源代碼：CREATE TRIGGER CostumeType_Delete ON CostumeTypeFOR DELETE AS begin declare @t int select @t=TypeID from deleted delete from Costume where TypeID=@t end Go216。 Member_Delete1. 觸發(fā)器名：Member _Delete ：dbo3．所屬表：Member：當刪除會員表中的一條記錄時,就刪除該會員所對應的訂購單信息5．源代碼：CREATE TRIGGER Member_Delete ON MemberFOR DELETE AS begin declare @t int select @t=MemberID from deleted delete from BusinessOrder where OrderMemberID=@tendGO 設計物理表示法： 分析事務該數(shù)據(jù)庫系統(tǒng)主要為一服裝網(wǎng)的網(wǎng)上交易及會員間的交流提供后臺支持，主要事務封裝在30多個存儲過中：,其中TypeFid為父類型號，如果無父類型，則填0,TypeHaveChild的值為y/n（其中TypeName屬性有唯一約束，所以存儲過程中 不再對插入是否重復進行處理） 存儲過程名：AddCostumeType 所有者：dbo 源代碼：CREATE proc AddCostumeType @TypeName varchar(50), @TypeFid int, @TypeHaveChild char(2)AS begin insert into CostumeType(TypeName,TypeFid,TypeHaveChild,TypeAddDate) values(@TypeName,@TypeFid,@TypeHaveChild,getdate()) print39。成功添加該服裝類型39。 endGO權限：GRANT EXECUTE ON [dbo].[AddCostumeType] TO [服裝管理員]GO測試數(shù)據(jù)：Execute AddCostumeType T恤，0，y 添加服裝（品種），Costume的每項記錄為一種服裝的信息 存儲過程名：AddCostume 所有者：dbo 源代碼：CREATE proc AddCostume @strName varchar(50), @strType int, @strColor varchar(20), @strBrand varchar(20), @strTexture varchar(20), @strSize varchar(20), @strStyle varchar(20), @url varchar(200), @strIntroduce varchar(4000), @strOrderDesc varchar(100), @strPrice money as begin if exists(select * from Costume where CostumeName= @strName and CostumeColor= @strColor and CostumeBrand= @strBrand and CostumeTexture=@strTexture and CostumeStyle=@strStyle and CostumeSize= @strSize ) print39。該服裝品中已存在39。 else begin Insert into Costume(CostumeName,TypeID,CostumeColor,CostumeBrand,CostumeTexture,CostumeStyle,CostumeSize,AddDate,Costu