【正文】 ON [dbo].[OrderDetail] TO [訂單管理員]GOGRANT SELECT ON [dbo].[OrderDetail] TO [會(huì)員]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 [會(huì)員]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 [會(huì)員]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] ) 為目標(biāo)數(shù)據(jù)庫(kù)管理系統(tǒng)設(shè)計(jì)業(yè)務(wù)規(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。超級(jí)管理員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_：當(dāng)刪除訂購(gòu)單BusinessOrder中的一條記錄時(shí),就刪除該訂購(gòu)單所對(duì)應(yīng)的所有詳細(xì)訂單的信息 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：當(dāng)刪除一種服裝時(shí)，說(shuō)明該系統(tǒng)不再出售該種服裝，那么該服裝所對(duì)應(yīng)的訂購(gòu)單也要?jiǎng)h除 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： 刪除服裝類型表中的一條記錄時(shí),就刪除該類型所對(duì)應(yīng)的服裝品種的信息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：當(dāng)刪除會(huì)員表中的一條記錄時(shí),就刪除該會(huì)員所對(duì)應(yīng)的訂購(gòu)單信息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ì)物理表示法： 分析事務(wù)該數(shù)據(jù)庫(kù)系統(tǒng)主要為一服裝網(wǎng)的網(wǎng)上交易及會(huì)員間的交流提供后臺(tái)支持，主要事務(wù)封裝在30多個(gè)存儲(chǔ)過(guò)中：,其中TypeFid為父類型號(hào)，如果無(wú)父類型，則填0,TypeHaveChild的值為y/n（其中TypeName屬性有唯一約束，所以存儲(chǔ)過(guò)程中 不再對(duì)插入是否重復(fù)進(jìn)行處理） 存儲(chǔ)過(guò)程名：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權(quán)限：GRANT EXECUTE ON [dbo].[AddCostumeType] TO [服裝管理員]GO測(cè)試數(shù)據(jù)：Execute AddCostumeType T恤，0，y 添加服裝（品種），Costume的每項(xiàng)記錄為一種服裝的信息 存儲(chǔ)過(guò)程名：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