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

,*,单击此处编辑母版标题样式,-,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,数据库原理,复习,1,-,设计题一,有,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),。,2,-,1、概念模型设计-ER图,1,、进行数据库概念模型设计，补充完善图,1,所示的,ER,模型，要求同时标出联系的连通词。（,10,分）,3,-,4,-,2、将概念模型转化成关系模型,逻辑设计：,2,、将概念模型转化成关系模型，标志出主、外键，判断每个关系模式的范式并说明理由。（,5,分）,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,范式,6,-,3、DDL,数据定义语言：,3,、利用,SQL,语句完成数据库模型中其它表格的创建，要求创建相应的索引,;,另外需要修改,buyer,表结构,为此表添加,1,个,email,字段，数据类型为,varchar(32),。（,15,分）,create table category,(cno char(4)primary key,cname varchar(80);go;,create index idx_category_name on category(cno);go;,create table goods,(gno char(10)primary key,gname varchar(80),unit varchar(32),detail varchar(1024),price numeric(8,1)default 0,cno char(4)foreign key references category(cno)on update cascade,);go;,create index idx_goods_name on goods(gname);go;,create table buyer,(bno varchar(16)primary key,bname varchar(32),bphone varchar(16),id_card varchar(18);go;,create table orders,(ono varchar(16)primary key,odate smalldatetime default getdate(),consignee varchar(256),status char(1)default 0,money numeric(8,1)default 0,bno varchar(16)foreign key references buyer(bno)on update cascade);go;,create index idx_orders_bno on orders(bno);go;,7,-,3、DDL,数据定义语言：,3,、利用,SQL,语句完成数据库模型中其它表格的创建，要求创建相应的索引,;,另外需要修改,buyer,表结构,为此表添加,1,个,email,字段，数据类型为,varchar(32),。（,15,分）,create table trolley,(,tno int identity primary key,bno varchar(16)foreign key references buyer(bno)on update cascade,gno char(10)foreign key references goods(gno)on update cascade,counts int default 1,);,go,create index idx_trolley_bno on trolley(bno);,create index idx_trolley_gno on trolley(gno);,go,8,-,3、DDL,数据定义语言：,3,、利用,SQL,语句完成数据库模型中其它表格的创建，要求创建相应的索引,;,另外需要修改,buyer,表结构,为此表添加,1,个,email,字段，数据类型为,varchar(32),。（,15,分）,create table detail,(,dno int identity primary key,ono varchar(16)foreign key references orders(ono)on update cascade,gno char(10)foreign key references goods(gno)on update cascade,counts int default 1,price numeric(8,1),),go,create index idx_detail_ono on detail(ono);,create index idx_detail_gno on detail(gno);,go,9,-,4、DML,4,、请用,SQL,语句完成以下数据更新操作。（,20,分）,（,1,）往商品分类表中插入记录：,(c001,食品,),。,（,2,）往商品表中插入记录：添加,1,个商品信息,商品类别：食品、编号：,g001,、商品名：巧克力、单位：块、详情：榛子,+,牛奶味、价格：,20,元。,（,3,）进行商品调价操作，将所有食品类商品价格打,9,折。,（,4,）清空买家用户代码为考生学号的购物车,10,-,4、DML,4,、请用,SQL,语句完成以下数据更新操作。（,20,分）,（,1,）往商品分类表中插入记录：,(c001,食品,),。,insert category(cno,cname)values(c001,食品,);,（,2,）往商品表中插入记录：添加,1,个商品信息,商品类别：食品、编号：,g001,、商品名：巧克力、单位：块、详情：榛子,+,牛奶味、价格：,20,元。,insert into goods(gno,gname,unit,detail,price,cno)values(g001,巧克力,块,榛子,+,牛奶味,20,c001);,11,-,4、DML,4,、请用,SQL,语句完成以下数据更新操作。（,20,分）,（,3,）进行商品调价操作，将所有食品类商品价格打,9,折。,update goods set price=price*0.9 where cno=c001;,（,4,）清空买家用户代码为考生学号的购物车,delete trolley where bno=004062;,12,-,5、DML和关系代数表达式,5,、完成下列查询要求，写出对应的,SQL,语句及关系代数表达式,。,（,20,分）,（,1,）、查找商品名为“巧克力”的商品信息，包括商品名、单位、价格。,（,2,）、查找类别为“食品”的所有商品清单，包括商品名、单位、价格。,（,3,）、查找用户代码为“,Berry”,的买家所有已购商品清单，包括商品名、价格、数量。,（,4,）、查找购买过商品名为“手机”或“笔记本电脑”的买家信息，包括买家号、姓名、购买数量。,（本小题不用写关系代数）,13,-,5、DML和关系代数表达式,5,、完成下列查询要求，写出对应的,SQL,语句及关系代数表达式,。,（,20,分）,（,1,）、查找商品名为“巧克力”的商品信息，包括商品名、单位、价格。,select gname,unit,price from goods where gname=,巧克力,;,gname,unit,price,（,gname=,巧克力,（,goods,）,14,-,5、DML和关系代数表达式,5,、完成下列查询要求，写出对应的,SQL,语句及关系代数表达式,。,（,20,分）,（,2,）、查找类别为“食品”的所有商品清单，包括商品名、单位、价格。,select gname,unit,price from goods g,category c where o=o and ame=,食品,;,gname,unit,price(,o=o,cname=,食品,（,goods,category）,),15,-,5、DML和关系代数表达式,5,、完成下列查询要求，写出对应的,SQL,语句及关系代数表达式,。,（,20,分）,（,3,）、查找用户代码为“,Berry”,的买家所有已购商品清单，包括商品名、价格、数量。,select g.gname,d.counts,d.price,from goods g,buyer b,orders o,detail d,where b.bno=o.bno and o.ono=d.ono and d.gno=g.gno and b.bname=Berry;,gname,counts,price,（,b.bno=o.bno o.ono=d.ono d.gno=g.gno b.bname=Berry,（goodsbuyerordersdetail）,16,-,5、DML和关系代数表达式,5,、完成下列查询要求，写出对应的,SQL,语句及关系代数表达式,。,（,20,分）,（,4,）、查找购买过商品名为“手机”或“笔记本电脑”的买家信息，包括买家号、姓名、购买数量。,（本小题不用写关系代数）,select b.bno,b.bname from buyer b where b.bno in,(select o.bno from orders o,detail d,goods g,where o.ono=d.ono and g.gno=d.gno and,(g.gname=,巧克力,or g.gname=,笔记本电脑,),17,-,6、view,6,、编写,1,个视图，按商品类别分类统计出所有商品,2014,年之前的销售数量、销售额，视图列集包括类别名、销售数量、销售金额。（,10,分）,18,-,6、view,按商品类别分类统计出所有商品,2014,年之前的销售数量、销售额，视图列集包括类别名、销售数量、销售金额。,create view v_sales_sum2(cname,count_c,sum_c)as,Select ame,count(o),sum(detail.counts*detail.price),From category,goods,detail,orders,Where o=o and,goods.gno=detail.gno and detail.ono=orders.ono and year(orders.odate)=2014,group by,o,ame,19,-,7、procedure,7,、编写,1,个存贮过程，完成往指定订单中添加商品的操作业务，输入参数包括订单编号、商品编号、购买数量，需要检查订单编号、商品编号是否存在。（,10,分）,20,-,7、procedure,7,、编写,1,个存贮过程，完成往指定订单中添加商品的操作业务，输入参数包括订单编号、商品编号、购买数量，需要检查订单编号、商品编号是否存在。（,10,分）,create procedure prg_ins_detail,ono varchar(16),gno char(10),counts int,as,declare price numeric(6,1);,if not exists(select ono from orders where ono=ono),return 6001;,if not exists(select gno from goods where gno=gno),return 6002;,select price=price from goods where gno=gno;,insert into detail(ono,gno,counts,price)values(ono,gno,counts,price);,go,exec prg_ins_detail 20141215194302,g024,5;,21,-,8、trigger,8,、编写,1,个触发器，完成订单表中总金额的自动累计功能，即通过订单明细中商品的价格与数量自动统计订单中的总金额。（,10,分）,22,-,8、trigger,8,、编写,1,个触发器，完成订单表中总金额的自动累计功能，即通过订单明细中商品的价格与数量自动统计订单中的总金额。（,10,分）,create trigger tri_detail_ins on detail for insert as,declare ono varchar(16),money numeric(6,1),select ono=ono,money=counts*price from inserted;,update orders set money=money+money where ono=ono;,go,exec prg_ins_detail 20141215194302,g013,5;,23,-,