数据库原理考试习题及答案ppt课件

.,数据库原理复习,.,设计题一有1个B2C电子购物网站，其业务规则如下：购物网站公司负责维护商品分类信息（category），管理商品（goods）目录,每个商品都需要归属到某个商品分类下；网民可以注册成为网站买家（buyer）,买家可以在购物网站通过商品名、商品类名搜索自己需要的商品，把选中的一定数量商品放入自己的购物车（trolley）中，选购完成后下订单（orders），订单需要在明细（detail）中记录商品数量、交易价格，下单时需要填写详细的收货人信息，然后通过第三方网银支付系统付款，付款成功后网银支付系统会在订单中标注付款成功标志,此标志的值分别为0：等待付款；1：等待发货；2：等待收货；3：关闭；公司看到买家付款成功后负责物流快递商品，买家收到商品后确定收货；暂不考虑退、换货业务。商品分类实体的属性包括分类号(cno)、类名（cname）。商品实体的属性包括商品号(gno)、商品名(gname)、单位(unit)、商品详情（detail）、价格(price)。买家实体的属性包括用户代码（bno）、姓名（bname）、电话（bphone）、身份证号(id_card)。订单实体的属性包括订单号(ono)、时间（odate）、状态（status）、收货人(consignee)、总金额(money)。,.,1、概念模型设计-ER图,1、进行数据库概念模型设计，补充完善图1所示的ER模型，要求同时标出联系的连通词。（10分）,.,.,2、将概念模型转化成关系模型,逻辑设计：2、将概念模型转化成关系模型，标志出主、外键，判断每个关系模式的范式并说明理由。（5分）,.,2、将概念模型转化成关系模型,逻辑设计：Category(cno,cname）BC范式Buyer(bno,bname,bphone,id_card)2范式，因为id_card的问题而存在传递依赖。Goods(gno,gname,unit,detail,price,cno)BC范式Trolley(tno,bno,gno,counts)BC范式Orders(ono,odate,status,consignee,money,bno)BC范式Detail(dno,gno,ono,counts,price)BC范式,.,3、DDL,数据定义语言：3、利用SQL语句完成数据库模型中其它表格的创建，要求创建相应的索引;另外需要修改buyer表结构,为此表添加1个email字段，数据类型为varchar(32)。（15分）createtablecategory(cnochar(4)primarykey,cnamevarchar(80);go;createindexidx_category_nameoncategory(cno);go;createtablegoods(gnochar(10)primarykey,gnamevarchar(80),unitvarchar(32),detailvarchar(1024),pricenumeric(8,1)default0,cnochar(4)foreignkeyreferencescategory(cno)onupdatecascade);go;createindexidx_goods_nameongoods(gname);go;createtablebuyer(bnovarchar(16)primarykey,bnamevarchar(32),bphonevarchar(16),id_cardvarchar(18);go;createtableorders(onovarchar(16)primarykey,odatesmalldatetimedefaultgetdate(),consigneevarchar(256),statuschar(1)default0,moneynumeric(8,1)default0,bnovarchar(16)foreignkeyreferencesbuyer(bno)onupdatecascade);go;createindexidx_orders_bnoonorders(bno);go;,.,3、DDL,数据定义语言：3、利用SQL语句完成数据库模型中其它表格的创建，要求创建相应的索引;另外需要修改buyer表结构,为此表添加1个email字段，数据类型为varchar(32)。（15分）createtabletrolley(tnointidentityprimarykey,bnovarchar(16)foreignkeyreferencesbuyer(bno)onupdatecascade,gnochar(10)foreignkeyreferencesgoods(gno)onupdatecascade,countsintdefault1);gocreateindexidx_trolley_bnoontrolley(bno);createindexidx_trolley_gnoontrolley(gno);go,.,3、DDL,数据定义语言：3、利用SQL语句完成数据库模型中其它表格的创建，要求创建相应的索引;另外需要修改buyer表结构,为此表添加1个email字段，数据类型为varchar(32)。（15分）createtabledetail(dnointidentityprimarykey,onovarchar(16)foreignkeyreferencesorders(ono)onupdatecascade,gnochar(10)foreignkeyreferencesgoods(gno)onupdatecascade,countsintdefault1,pricenumeric(8,1)gocreateindexidx_detail_onoondetail(ono);createindexidx_detail_gnoondetail(gno);go,.,4、DML,4、请用SQL语句完成以下数据更新操作。（20分）（1）往商品分类表中插入记录：(c001,食品)。（2）往商品表中插入记录：添加1个商品信息,商品类别：食品、编号：g001、商品名：巧克力、单位：块、详情：榛子+牛奶味、价格：20元。（3）进行商品调价操作，将所有食品类商品价格打9折。（4）清空买家用户代码为考生学号的购物车,.,4、DML,4、请用SQL语句完成以下数据更新操作。（20分）（1）往商品分类表中插入记录：(c001,食品)。insertcategory(cno,cname)values(c001,食品);（2）往商品表中插入记录：添加1个商品信息,商品类别：食品、编号：g001、商品名：巧克力、单位：块、详情：榛子+牛奶味、价格：20元。insertintogoods(gno,gname,unit,detail,price,cno)values(g001,巧克力,块,榛子+牛奶味,20,c001);,.,4、DML,4、请用SQL语句完成以下数据更新操作。（20分）（3）进行商品调价操作，将所有食品类商品价格打9折。updategoodssetprice=price*0.9wherecno=c001;（4）清空买家用户代码为考生学号的购物车deletetrolleywherebno=004062;,.,5、DML和关系代数表达式,5、完成下列查询要求，写出对应的SQL语句及关系代数表达式。（20分）（1）、查找商品名为“巧克力”的商品信息，包括商品名、单位、价格。（2）、查找类别为“食品”的所有商品清单，包括商品名、单位、价格。（3）、查找用户代码为“Berry”的买家所有已购商品清单，包括商品名、价格、数量。（4）、查找购买过商品名为“手机”或“笔记本电脑”的买家信息，包括买家号、姓名、购买数量。（本小题不用写关系代数）,.,5、DML和关系代数表达式,5、完成下列查询要求，写出对应的SQL语句及关系代数表达式。（20分）（1）、查找商品名为“巧克力”的商品信息，包括商品名、单位、价格。selectgname,unit,pricefromgoodswheregname=巧克力;gname,unit,price（gname=巧克力（goods）,.,5、DML和关系代数表达式,5、完成下列查询要求，写出对应的SQL语句及关系代数表达式。（20分）（2）、查找类别为“食品”的所有商品清单，包括商品名、单位、价格。selectgname,unit,pricefromgoodsg,o=ame=食品;gname,unit,price(o=ocname=食品（goodscategory）),.,5、DML和关系代数表达式,5、完成下列查询要求，写出对应的SQL语句及关系代数表达式。（20分）（3）、查找用户代码为“Berry”的买家所有已购商品清单，包括商品名、价格、数量。selectg.gname,d.counts,d.pricefromgoodsg,buyerb,orderso,detaildwhereb.bno=o.bnoando.ono=d.onoandd.gno=g.gnoandb.bname=Berry;gname,counts,price（b.bno=o.bnoo.ono=d.onod.gno=g.gnob.bname=Berry（goodsbuyerordersdetail）,.,5、DML和关系代数表达式,5、完成下列查询要求，写出对应的SQL语句及关系代数表达式。（20分）（4）、查找购买过商品名为“手机”或“笔记本电脑”的买家信息，包括买家号、姓名、购买数量。（本小题不用写关系代数）selectb.bno,b.bnamefrombuyerbwhereb.bnoin(selecto.bnofromorderso,detaild,goodsgwhereo.ono=d.onoandg.gno=d.gnoand(g.gname=巧克力org.gname=笔记本电脑),.,6、view,6、编写1个视图，按商品类别分类统计出所有商品2014年之前的销售数量、销售额，视图列集包括类别名、销售数量、销售金额。（10分）,.,6、view,按商品类别分类统计出所有商品2014年之前的销售数量、销售额，视图列集包括类别名、销售数量、销售金额。createviewv_sales_sum2(cname,count_c,sum_c)asSame,count(o),sum(detail.counts*detail.price)Fromcategory,goods,detail,ordersWo=oandgoods.gno=detail.gnoanddetail.ono=orders.onoandyear(orders.odate)=2014o,ame,.,7、procedure,7、编写1个存贮过程，完成往指定订单中添加商品的操作业务，输入参数包括订单编号、商品编号、购买数量，需要检查订单编号、商品编号是否存在。（10分）,.,7、procedure,7、编写1个存贮过程，完成往指定订单中添加商品的操作业务，输入参数包括订单编号、商品编号、购买数量，需要检查订单编号、商品编号是否存在。（10分）createprocedureprg_ins_detailonovarchar(16),gnochar(10),countsintasdeclarepricenumeric(6,1);ifnotexists(selectonofromorderswhereono=ono)return6001;ifnotexists(selectgnofromgoodswheregno=gno)return6002;selectprice=pricefromgoodswheregno=gno;insertintodetail(ono,gno,counts,price)values(ono,gno,counts,price);goexecprg_ins_detail20141215194302,g024,5;,.,8、trigger,8、编写1个触发器，完成订单表中总金额的自动累计功能，即通过订单明细中商品的价格与数量自动统计订单中的总金额。（10分）,.,8、trigger,8、编写1个触发