现有一个关于网络玩具销售系统的项目，要求开发数据库部分。系统所能达到的功能包括 以下几个方面。
(1 )客户注册功能。客户在购物之前必须先注册，所以要有客户表来存放客户信息。如客 户编号、姓名、性别、年龄、电话、通信地址等。
(2 )顾客可以浏览到库存玩具信息，所以要有一个库存玩具信息表，用来存放玩具编号、 名称、类型、价格、所剩数量等信息。
(3) 顾客可以订购自己喜欢的玩具，并可以在未付款之前修改自己的选购信息。商家可以 根据顾客是否付款，通过顾客提供的通信地址给顾客邮寄其所订购的玩具。这样就需要有订单 表，用来存放订单号、用户号、玩具号、所买个数等信息。
操作内容及要求如下。
•根据案例分析过程提取实体集和它们之间的联系，画出相应的E-R图。
•把E-R图转换为关系模式。
•将转换后的关系模式规范化为第三范式。
cBrandld 玩具品牌 FK 续表 字段名 imPhoto siToyQoh siLowerAge siUpperAge siToyWeight vToylmgPath 表4.48 字段名 cBrandld cBrandName 说 明 铤 值 备 注 图片 库存数量 年龄下限 年龄上限 玩具重量 图片存放地址 ToyBrand （玩具品牌） 说 明 品牌编号 键 值 PK 备 注 品牌名称 Category （玩具类别） 说 明 类别编号 表4.49 字段名 cCategoryld cCategory 键 值 备 注 PK 类别名称 类别描述 Country （国家） 说 明 国家编号 vDescription 表 4.50 字段名 cCountryld 键 值 备 注 PK cCountry 表 4.51 字段名 cToyld siMonth iYear iTotalSold 表 4.52
字段名 cOrderNo vFirstName vLastName v Address 国家名称 PickOfMonth （月销售量） 说 明 玩具编号 键 值 备 注 PK 月份 年份 销售总量 Recipient （接受者） 说 明 订单编号 键 值 备 注 PK/FK 接受者姓 接受者名 地址 续表 字段名 cCity estate cCountryld cZipCode 说 明 键 值 备 注 城市 州 国家 邮编 cPhone 表 4.53 字段名 cOrderNo dShipmentDate cDeliveryStatus dActualDeliveryDa 电话 Shipment （运货） 说 明 订单编号 键 值 备 注 PK/FK 运货日期 运货状态 d:已送达s:未送 达 表 4.54 te 实际交付日期 ShippingMode （运货方式） 说 明 运货方式代码 字段名 cModeld cMode 键 值 备 注 PK 运货方式 最长运货时间 ShippingRate （运价表） 说 明 国家编号 iMaxDelDays 表 4.55 字段名 cCountrylD cModeld 键 值 备 注 PK 运货方式 运价比 Shopper （顾客） 说 明 顾客编号 mRatePerPound 表 4.56 字段名 cShopperld ePassword vFirstName vLastName vEmailld vAddress cCity estate cCountryld 每磅运价比率 键 值 备 注 PK 密码 姓 名 E-mail 地址 城市 州 国家编号 续表 字段名 说 明 键 值 备 注 字段名 cZipCode cPhone cCreditCardNo vCreditCardType 说 明 键 值 备 注 邮编 电话 信用卡号 信用卡类型 dExpiryDate 表 4.57 字段名 cCartld cToyld 有效期限 ShoppingCart （购物车） 说 明 购物车编号 键 值 备 注 PK 玩具编号 玩具数量 Wrapper （包装） 说 明 包装编号 si Qty 表 4.58 字段名 cWrapperld vDescription mWrapperRate imPhoto vWrapperlmgPat h 键 值 备 注 PK 描述 包装费用 图片 图片存放地址 操作内容及要求如下。 1.索引和视图
(1) 查询显示购物者的名字及其所订购的玩具的总价。 Select vFirstName.mTotalCost From shopper join Orders
On shopper.cShopperld=Orders.cShopperld
上述查询的执行要花费很长的时间。创建相应的索引来优化上述查询。
(2 )表Toys经常用作查询，查询一般基于属性cToyld，用户必须优化查询的执行。同时， 确保属性cToyld没有重复。
(3 )表Category经常用于查询，查询基于表中的属性cCategory。属性cCategoryld被 定义为主关键字，在表上创建相应的索引，加快查询的执行。同时确保属性cCategory没有重 复。
(4 )完成下面的查询。
① 显示购物者的名字和他们所订购的玩具的名字。
select shopper.vFirstName , cToyName from shopper join orders on shopper.cShopperld^Orders.cShopperld join orderDetail on Orders.cOrderNo=OrderDetail.cOrderNo join Toys on OrderDetail.cToyId=Toys.cToyld (2) 显示购物者的名字和他们订购的玩具的名字和订购的数量。
Select shopper.vFirstName,cToyName,siQty From shopper join orders On shopper.cshopperld=0rders.cShopperld Join OrderDetail On Orders.cOrderNo=OrderDetail.cOrderNo Join Toys On OrderDetail.cToyId=Toys.cToyld ③ 显示购物者的名字和他们所订购的玩具的名字和玩具价格。
select shopper.vFirstName,cToyName,mToyCost from shopper join orders on shopper.cShopperId=orders.cShopperld join orderDetailon orders.cOrderNo-orderDetail.cOrderNo join Toys on OrderDetail.cToyId=Toys.cToyld 简化这些查询。
(5 )视图定义如下。
create view vwOrderWrapper as select cOrderNO , cToyld , siQty , vDescription , mWrapperRate from OrderDetail join Wrapper on OrderDetail.cWrapperId=Wrapper.cWrapperld 当使用下列更新命令更新siQty和mWrapperRate时，该命令给出一个错误。
update vv/OrderWrapper set siQty=2zmWrapperRate= mWrapperRate from vwOrderWrapper where cOrderNo= '000001' 修改更新命令，在基表中更新所需的值。
① 需要获得订货代码为“000003”的货物的船运状况，如果该批订货已经投递，则应该显
示消息\"the order has been delivered\"。否贝\"，显示消息\"the order has been shipped but not delivered\"。
如果该批订货已经船运但未投递，则属性cDeliveryStatus将包含“s”，如 果该批订货已经投递，则属性cDeliverystatus包含“d”。
@将每件玩具的价格增加¥0.5 ,直到玩具的平均价格达到约¥22.5o
③将每件玩具的价格增加¥0.5 ,直到玩具的平均价格达到约¥24.5o此外，任何一件玩 具的价格最高不得超过¥53。
2.存储过程和触发器
(1 )频繁地需要一份包含所有玩具的名称、说明、价格的报表。在数据库中创建一个对象， 消除获得报表时因网络阻塞造成的延时。
(2)对报表的查询如下。
select vFirstName , vLastName , vEmailld
from shopper ;
为上述查询创建存储过程。
(3) 创建存储过程，接收一个玩具代码，显示该玩具的名称和价格。 (4 )创建一个存储过程，将下列数据添加到表ToyBrand中，见表5.11。
表 5.11 cBrandld (品牌代码) 009 ToyBrand
cBrandName (品牌名称) Fun World (5地•］建一个叫prcAddCategory的存储过程，将下列数据添加到表Category中 况表5.12。
表 5.12 cCategory Id (种类代码) 018 Category
cCategory (种类名称) Electronic Games vDescription (说明) 这些游戏中包含了一个和孩子们交互的屏幕 (6 )删除过程 prcAddCategoryo
(7 )创建一个叫preCharges的触发器，按照给定的订货代码返回船运费和包装费。 (8 )创建一个叫prcHandlingChanges的触发器，接收一个订货代码并显示处理费。触发 器prcHandlingCharges中应该用到触发器preCharges ,以取得船运费和包装费。
处理费=船运费+包装费。 (1 )当完成了订购之后，订购信息被存放在表OrderDetail中，系统应当将玩具的现有数
量减少，减少数量为购物者订购的数量。
(2 )存储过程prcGenOrder生成数据库中现有的订货数量。
Create procedure prcGenOrder(in OrderNo char(6)) BEGIN Declare OrderNo char(6); select max(cOrderNo) into OrderNo from orders; select @OrderNo=case when @OrderNo>=0 and @OrderNo<9 then '00000'+convert(char,@OrderNO+l) when @OrderNo>=9 and @OrderNo<99 then '0000'+convert(char,@OrderNO+l) when @OrderNo>=99 and @OrderNo<999 then '000* +convert(char,@OrderNO+l)when @OrderNo>=999 and @OrderNo<9999 then '00'+convert(char,@OrderNO+l) when @OrderNo>=9999and @OrderNo<99999 then '0'+convert(char,@OrderNO+l) when @OrderNo>=99999 then convert(charz @OrderNO+l) end; 当购物者确认一次订购时，依次执行下列步骤。
① 通过上述过程生成订货代码。
② 将订货代码、当前日期、车辆代码、购物者代码添加到表Order中。 ③ 将订货代码、玩具代码、数量添加到表OrderDetail中。 ④ OrderDetail表中的玩具价格应该更新。
玩具价格=数量X玩具单价。上述步骤应当具有原子性。 将上述事务转换成存储过程，该过程接收车辆代码和购物者代码作为参数。
(3)当购物者为某个特定的玩具选择礼品包装时，依次执行下列步骤。
① 属性cGiftWrap中应当存放“丫”，属性cWrapperld应根据选择的包装代码进行更新。
② 礼品包装费用应当更新。
上述步骤应当具有原子性。
将上述事务转换成存储过程，该过程接收订货代码、玩具代码和包装代码作为参数。
(4 )如果购物者改变了订货数量，则玩具价格将自动修改。
玩具价格=数量x玩具单价。
操作内容及要求如下。 1. 数据库的备份和还原
首先在指定位置建立备份文件的存储文件夹，如D:\\GlobalToysbak。 (1 )利用Navicat图形工具实现数据的备份与恢复。
① 对GlobalToys数据库进行备份，备份文件名为GlobalToysbako
② 备份GlobalToys数据库中的Toys表。备份文件存储在D:\\ GlobalToysIbak ,文件名 称为 Toysbak.txto
③ 将原有的GlobalToys数据库删除 然后将备份文件GlobalToysbak恢复为GlobalToys。
④ 将GlobalToys数据库中的Toys表删除，然后将备份文件Toysbak.txt恢复到数据库 中。
(2)使用命令进行数据的备份和恢复。
① 使用mysqldump命令备份GlobalToys数据库，生成的gbak.sql文件存储在D:\\ GlobalToysbako
② 使用mysqldump命令备份GlobalToys数据库中的Toys表和Orders表 住成的bak.sql 文件存储在 D:\\GlobalToysbako
③ 将GlobalToys数据库删除，分别使用mysql命令和source命令将GlobalToys数据 库的备份文件gbak.sql恢复到数据库中。
④ 将GlobalToys数据库中的Toys表和Orders表删除，分别使用mysql命令和source 命令将备份文件bak.sql恢复到GlobalToys数据库中。
2.
日志的综合管理
首先在指定位置建立日志文件的存储文件夹，如D:\\GlobalToys\\log。 (1 )二进制日志。
。启动二进制日志功能，并且将二进制日志存储到D:\\ GlobalToysMog中。
② 启动服务后，查看二进制日志。
③ 然后向GlobalToys数据库中的Toys表中插入两条记录。
④ 暂停二进制日志功能，然后，再次删除Toys表中的所有记录(注意做好备份工作)。 ⑤ 重新开启二进制日志功能。 ⑥ 使用二进制日志来恢MToys表。 (2)错误日志、通用查询日志和慢查询日志。
。将错误日志的位置设置为D:\\ GlobalToysMog目录中。
② 开启通用查询日志，并设置该日志存储在D:\\ GlobalToysMog目录中。 ③ 开启慢查询日志，并设置该日志存储在D:\\ GlobalToysMog目录中。 ④ 查看错误日志、通用查询日志和慢查询日志。
操作内容及要求如下。
在2.8节课外拓展中数据库设计的基础上，在服务器D:\\MYSQL\\DATA文件夹中建立一个
数据库 GlobalToyso
操作内容及要求如下。 1. 管理表
在上一模块的课外拓展中，已建立好数据库GlobalToys ,现在实现表的创建与维护。 考虑下面的表结构，见表4.28~表4.30o 表4.28 属性名 cToyld cToyName vToyDescription cCategoryld mToyRate cBrandld imPhoto siToyQoh siLowerAge siUpperAge siToy Weight Toys （玩具）表结构 数据类型 char(6) varchar(20) varchar(250) char ⑶ decimal(10,2) char(3) blob smallint smallint smallint smallint varchar(50) Category （玩具类别）表结构 属性名 cCategoryld cCategory 数据类型 char(3) char(20) varchar(100) ToyBrand （玩具品牌）表结构 属性名 cBrandld 数据类型 char(3) char(20) 表4.29 vToylmgpath 表4.30 vDescription cBrandName （1 ）创建表Categoryo创建表时，实施下面的数据完整性规则。
。主关键字应该是种类代码。
② 属性cCategory应该是唯一的，但不是主关键字。
③ 种类描述属性允许NULL值。
（2 ）创建表ToyBrando创建表时，实施下列数据完整性规则。
。主关键字应该是品牌代码。
②品牌名应该是唯一的，但不是主关键字。
(3)创建表Toyso该表须满足下列的数据完整性。 ① 主关键字应该是玩具代码。
② 玩具的现存数量(siToyQoh )应该是在0到200之间。
③ 属性imPhotOs vToylmgpath允许存放NULL值。
④ 属性 cToyName、vToyDescription 不应该允许为 NULL。
⑤ 玩具年龄下限的默认值是1o
⑥ 属性cCategoryld的值应该是在表Category中。
(4 )修改表Toys ,实施下列数据完整性。
① 输入到属性cBrandld中的值应当在表ToyBrand中存在。
② 玩具年龄上限的默认值应该是1。
(5 )修改已经创建的表Toys ,实施下列数据完整性规则。 ① 玩具的价格应该大于0。
② 玩具重量的默认值应为1o
(6 )在数据表中存入下列品牌，见表4.310 表 4.31 cBrandld 001 002 003 ToyBrand 表 cBrandName Bobby Frances_Price The Bernie Kids Largo 004 (7 )将下列种类的玩具存储在数据库中，见表4.320 表 4.32
Category 表
cCateg cCategory vDescription
oryld 001 002 003 Activity 兴趣 Dolls Arts And Crafts 各种各样先进品牌的洋娃娃 鼓励孩子们用这些令人难以置信的手工工具创造出杰作 创造性玩具鼓励孩子的社交技能，并激发他们对周围世界的 (8 )将下列信息存入数据库，见表4.33o 表 4.33 属性名 cToyld cToyName vToy Description cCategoryld mToyRate cBrandld imPhoto siToyQoh siLowerAge siUpperAge siToy Weight 上 001 8.99 001 NULL 50 3 9 1 NULL 000001 Robby the Whale 一条带两个重型把手的巨大蓝鲸，使得孩子可以骑在它的背 Toys 表 数 据 vToylmgpath (9 )将玩具代码为“000001”的玩具的mToyRate增加¥ 1。 (10 )在数据库中删除品牌\"Largo”。
(11 )将种类\"Activity”的信息复制到一张新表中，此表叫PreferredCategoryo (12 )将种类\"Dolls”的信息从表 Category 复制到表 PreferredCategoryo 2. 表的查询(1 )
(1 )显示在玩具名称中包含“Racer”的所有玩具的所有信息。 (2 )显示所有名字以“s”开头的购物者。
(3)显示接受者所属的所有州，州名不应该有重复。
(5 )显示所有玩具的订货代码、玩具代码、包装说明。格式见表4.34o 表 4.34 OrderNumber 格式1 Toyld WrapperDescription (6)显示所有玩具的名称、品牌和类别。格式见表4.350 表 4.35 ToyName 格式2 Rrand Category (7 )显示购物者和接受者的名字和地址。格式见表4.36o 表4.36
ShopperName 格式3
ShopperAddress Recipient Name RecipientAddress (8 )显示所有玩具的名称和购物车代码，见表4.370如果玩具不在购物车上，则应显示 NULLo
表4.37 ToyName Robby the Whale Water Channel System 格式4 Cartld 000005 NULL 提 示 使用左外连接。 (9 )将所有价格高于¥ 20的玩具的所有信息复制到一个叫PremiumToys的新表中。 (10 )显示购物者和接受者的名字、姓、地址和城市。格式见表4.38o 表4.38
FirstName 格式5
Last Name Address City (11 )显示价钱最贵的玩具名称。
(12 )查询订单(Orders )表中，运货方式代码(cShippingModeld )是“OF的运货费用 (mShippingCharges 卜用 GROUP BY 和 HAVING 实现。
(13 )查询订单(Orders )表中，总费用(mTotalCost)最高的前3个订货单代码。
(1 )显示价格范围在¥10到¥20的所有玩具的列表。
(2 )显示属于California或Illinois州的购物者的名字、姓和E-mail地址。 (3 )显示发生在2001-05-20的，总值超过¥ 75的订货，格式见表4.39。
表 4.39 OrderNumber 格式6
OrderDate Shopperld TotalCost (4) 显示属于“Dolls”类，且价格小于¥20的玩具的名字。
Dolls 的类别代码(cCategoryld )为 “002”。 (5) 显示没有任何附加信息的订货的全部信息。 (6 )显示不住在Texas州的购物者的所有信息。
(7 )显示所有玩具的名字和价格，见表4.40o确保价格最高的玩具显示在列表顶部。
表 4.40 ToyName 格式7
Toy Rate (8 )升序显示价格小于¥20的玩具的名字。
(9)显示订货代码、购物者代码和订货总值，按总值的升序显示。 (10 )显示本公司卖出的玩具的种数。
(11 )显示玩具价格的最大值、最小值和平均值。
(13 )在一次订货中，可以订购多个玩具。显示包含订货代码和每次订货的玩具总价的报 表，见表4.41 o
表 4.41 OrderNumber 格式8
T otalCostofT oysforanOrder (14 )在一次订货中，可以订购多个玩具。显示包含订货代码和每次订货的玩具总价的报 表。(条件：该次订货的玩具总价超过¥50o )
(15 )根据2000年的售出数量，显示头5个'Pick of the Month\"玩具的玩具代码。 (16 )显示一张包含所有订货的订货代码、玩具代码和所有订货的玩具价格的报表。该报 表应该既显示每次订货的危计又显示所有订货的总计。
(17 )显示所有玩具的玩具名、说明、价格。但是，只显示说明的前40个字母。 (18 )显示所有运货的报表，格式见表4.42o 表 4.42 OrderNumber 格式9
ShipmentDate ActualDeliveryDa te DaysinTransit
运送天数(DaysinTransit)=实际交付日期(dActualDeliveryDate)-运货日 期(dShipmentDate) (19 )显示订货代码为“000009”的订货的报表，格式见表4.43o
表 4.43
OrderNumber 格式10
DaysinTransit (20 )显示所有的订货，格式见表4.440 表 4.44
格式11
OrderNumber Shopperld DayofOrder Weekday
GlobalToys数据库说明，见表4.45 ~表4.580 表 4.45 字段名 cOrderNo dOrderDate cCartld cShopperld cShippingMode Id mShippingChar ges Orders （订单） 说 明 订单编号 键 值 备 注 PK 订单日期 购物车编号 顾客编号 FK FK 运货方式代码 运货费用 mGiftWrapChar ges cOrderProcess ed mTotalCost 包装费用 订单是否处理 订单总价 商品总价+运货费用+包装费 用 dExpDelDate 表 4.46 字段名 cOrderNo cToyld siQty cGiftWrap cWrapperld vMessage 期望送货时间 OrderDetail （订单细目） 说 明 订单编号 键 值 PK 备 注 玩具编号 玩具数量 是否包装 是：丫，否：N FK 包装ID 信息 玩具总价 mToyCost 表 4.47 字段名 cToyld cToyName vToyDescription cCategoryld mToyRate 玩具单价X数量 Toys （玩具） 说 明 玩具编号 键 值 备 注 PK 玩具名称 玩具描述 玩具类别 FK 玩具价格
因篇幅问题不能全部显示，请点此查看更多更全内容