1、软件开发这点儿事软件开发工具手册 邵志东著程序员指南丛书ASP.NET高级编程 但正刚著ASP.NET网络应用系统开发与实例 吴明晖主编 吴丹红编著2外部设计2.1支持软件本数据库主要采用微软公司的成熟数据库系统SQL Server2000,提供了较为灵活操作简单的功能,可以为数据库管理员和系统开发人员提供很好的支持。3结构设计3.1概念结构设计本数据库将反映的现实世界中的实体、属性和它们之间的关系等的原始数据形式,包括各数据项、记录、系、文卷的标识符、定义、类型、度量单位和值域,商城首页:热门商品:购物清单:3.2 逻辑结构设计:3.3关系图3.3物理结构设计 Customers用户表:列名
2、说明类型(长度)备注ID顾客idInt不允许空,主键Name顾客名称Varchar(50)不允许空Password登录密码Phone家庭电话允许空Fax传真号码MobilePhone移动电话E-mail邮件地址Address详细地址Postcode邮政编码Char(6)QQQQ号MSNMSN号 Categories商品分类表:类型商品类型idInt(4.)商品类型名称Memo商品类型描述Varchar(255) Products商品表:ProductID商品ID不允许为空CategoryID分类IDModelNumber商品编号允许为空ModelName商品名称ProductImage商品图片
3、文件名UnitCost单价MoneyDiscount折扣Description描述Varchar(2000)DateTimed上架时间SmalldatetimeClicks点击量Sales销售量Commend是否推荐Bit Orders订单表订单IDOrderDate下订单时间CustomerID用户IDShipDate发货时间HasSend是否送货HasReceive顾客是否收到Payway付款方式Carryway送货方式ReceiveAddress收货地址 OrderDetails订单详情表:Int(4)OrderIDQuantity商品数量商品单价 Shoppingcart购物车表:Ca
4、rtID购物车ID购买时间Managers管理员表:管理员主键管理员姓名管理员密码4.存储过程脚本:(通过商品名称查询商品图片,单价,商品描述,购买时间)if exists(select * from sysobjects where name=Products_list and type=p)drop procedure Products_listgocreate PROCEDURE dbo.Products_listspname char(50),sptupian char(50) output,danjia money output,spmiaoshu char(250) output,
5、spshijian char(50) outputas select sptupian=ProductImage,danjia=UnitCost,spmiaoshu=Description,spshijian=Datetimed from Productswhere ModelName=spnamedeclare spname char(50),declare sptupian char(50),declare danjia money ,spmiaoshu char(250),spshijian char(50) set spname=蓝宝石exec Products_list spname
6、,sptupian output,danjia output,spmiaoshu output,spshijian outputselect spname as 商品名称,sptupian as 图片,danjia as 单价,spmiaoshu as 商品描述,spshijian as 购买时间(通过商品ID查询商品名称,商品数量,商品单价,商品描述,购物时间,购物车ID)Shoppingcart_listdrop procedure Shoppingcart_listcreate PROCEDURE dbo.Shoppingcart_listProductID char(50),spnam
7、e char(50) output,spshuliang char(50) output,danjia money output,spmiaoshu char(250) output,spshijian char(50) output,gouwuchengID char(50) outputas select spname=ModelName, spshuliang=Quantity,danjia=UnitCost,spmiaoshu=Description,spshijian=ShoppingCart.Datetimed,gouwuchengID=CartID from Products,S
8、hoppingCartwhere ShoppingCart.ProductID=Products.ProductID and Products.ProductID=ProductIDdeclare ProductID char(50),spname char(50),spshuliang char(50) ,danjia money ,spmiaoshu char(250),spshijian char(50),gouwuchengID char(50)set ProductID=64exec Shoppingcart_list ProductID,spname output,spshulia
9、ng output,danjia output,spmiaoshu output,spshijian output,gouwuchengID outputselect ProductID as 商品ID,spname as 商品名称,spshuliang as 商品数量,danjia as 商品单价,spmiaoshu as 商品描述,spshijian as 购买时间,gouwuchengID 购物车IDGo5.触发器脚本:修改表Products中的ProductID,则表ShoppingCart中的ProductID随之改变if exists (select * from sysobjec
10、ts where name =Product_IDtrdrop trigger Product_IDcreate trigger Product_ID on Products for update asif update(ProductID)begindeclare productID_new char(50),productID_old char(50)select productID_new=ProductID from insertedselect ProductID_old=ProductID from deletedupdate ShoppingCart set ProductID=
11、productID_newwhere ProductID=productID_oldendupdate Products set ProductID=99 where ProductID=63select * from Productsselect * from ShoppingCart(当插入或删除一个商品ID时,如果ID在1100操作成功,否则提示商品ID超出所存商品ID范围,无法找到您需要的商品!请核对后重新输入。Productdrop trigger Productcreate trigger Product on Products for update,insertdeclare p
12、roductID char(50)select productID=ProductID from insertedif productID0 and productID=100 begin print操作成功完成! return endprintrollback Transactioninsert into Products (ProductID,CategoryID,ModelNumber,ModelName,Datetimed,Clicks,Sales,Commend)values(78,123564N微波炉2002-05-13 11:29:00473000118811888N空气加湿器2
13、04506.视图脚本:SELECT dbo.Orders.ID, dbo.OrdersDetails.ID AS Expr1, dbo.OrdersDetails.OrderID, dbo.OrdersDetails.ProductID, dbo.OrdersDetails.QuantityFROM dbo.Orders INNER JOIN dbo.OrdersDetails ON dbo.Orders.ID = dbo.OrdersDetails.IDSELECT dbo.ShoppingCart.ID, dbo.Products.ProductID, dbo.Products.Categ
14、oryID, dbo.Products.ModelNumber, dbo.Products.ModelNameFROM dbo.ShoppingCart INNER JOIN dbo.Products ON dbo.ShoppingCart.ProductID = dbo.Products.ProductID7:数据库恢复与备份:数据库的完全备份backup database OnlineShop to disk =f:备份数据库OnlineShop with initgo 数据库的恢复restore database OnlineShop from disk = with recovery数据库差异备份 with norecovery with file=2