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

,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,数据库原,理,复习,设计题一,有,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,分）,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;,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,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,4,、,DML,4,、请用,SQL,语句完成以下数据更新操作。（,20,分）,（,1,）往商品分类表中插入记录：,(c001,食品,),。,（,2,）往商品表中插入记录：添加,1,个商品信息,商品类别：食品、编号：,g001,、商品名：巧克力、单位：块、详情：榛子,+,牛奶味、价格：,20,元。,（,3,）进行商品调价操作，将所有食品类商品价格打,9,折。,（,4,）清空买家用户代码为考生学号的购物车,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);,4,、,DML,4,、请用,SQL,语句完成以下数据更新操作。（,20,分）,（,3,）进行商品调价操作，将所有食品类商品价格打,9,折。,update goods set price=,price,*0.9 where,cno,=c001;,（,4,）清空买家用户代码为考生学号的购物车,delete trolley where,bno,=004062;,5,、,DML,和关系代数表达式,5,、完成下列查询要求，写出对应的,SQL,语句及关系代数表达式,。,（,20,分）,（,1,）、查找商品名为“巧克力”的商品信息，包括商品名、单位、价格。,（,2,）、查找类别为“食品”的所有商品清单，包括商品名、单位、价格。,（,3,）、查找用户代码为“,Berry”,的买家所有已购商品清单，包括商品名、价格、数量。,（,4,）、查找购买过商品名为“手机”或“笔记本电脑”的买家信息，包括买家号、姓名、购买数量。,（本小题不用写关系代数）,5,、,DML,和关系代数表达式,5,、完成下列查询要求，写出对应的,SQL,语句及关系代数表达式,。,（,20,分）,（,1,）、查找商品名为“巧克力”的商品信息，包括商品名、单位、价格。,select,gname,unit,price,from goods where,gname,=,巧克力,;,gname,unit,price,（,gname,=,巧克力,（,goods,）,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,）,),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,）,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,=,笔记本电脑,),6,、,view,6,、编写,1,个视图，按商品类别分类统计出所有商品,2014,年之前的销售数量、销售额，视图列集包括类别名、销售数量、销售金额。（,10,分）,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,7,、,procedure,7,、编写,1,个存贮过程，完成往指定订单中添加商品的操作业务，输入参数包括订单编号、商品编号、购买数量，需要检查订单编号、商品编号是否存在。（,10,分）,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;,8,、,trigger,8,、编写,1,个触发器，完成订单表中总金额的自动累计功能，即通过订单明细中商品的价格与数量自动统计订单中的总金额。（,10,分）,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,g0,13,5;,