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

1、数据库原理,复习,设计题一,有,1,个,B2C,电子购物网站，其,业务规则,如下,购物网站公司负责维护商品分类信息,category,，管理商品,goods,目录,每个商品都需要归属到某个商品分类下；网民可以注册成为网站买,家,buyer,买家可以在购物网站通过商品名、商品类名搜索自己需要,的商品，把选中的一定数量商品放入自己的购物车,trolley,中，选购,完成后下订单,orders,，订单需要在明细,detail,中记录商品数量,交易价格，下单时需要填写详细的收货人信息，然后通过第三方网银支,付系统付款，付款成功后网银支付系统会在订单中标注付款成功标志,此,标志的值分别为,0,等待付款,

2、1,等待发货,2,等待收货,3,关闭,公司看到买家付款成功后负责物流快递商品，买家收到商品后确定收货,暂不考虑退、换货业务,商品分类实体的属性包括分类号,cno,类名,cname,商品实体的属性包括商品号,gno,商品名,gname,单位,unit,商品详情,detail,价格,price,买家实体的属性包括用户代码,bno,姓名,bname,电话,bphone,身,份证号,id_card,订单实体的属性包括订单号,ono,时间,odate,状态,status,收货人,consignee,总金额,money,1,概念模型设计,ER,图,g,n,a,u,n,i,t,d,a,e,t,i,l,1,进

3、行数,据库概念,模型设计,补充完善,图,1,所示的,ER,模型,要求同时,标出联系,的连通词,10,分,cno,cname,g,n,o,Category,1,分类,n,m,e,p,r,i,c,e,b,n,a,m,o,n,o,b,n,o,g,o,e,r,b,u,y,o,d,s,e,n,e,b,o,p,h,I,d,_,c,a,r,d,1,订,购,n,o,r,d,e,r,s,consignee,s,t,a,t,m,o,n,e,y,u,s,o,d,a,t,e,cno,cname,g,n,o,g,n,a,m,e,u,n,i,t,Category,1,分类,n,d,e,i,t,a,l,e,c,p,r,i,

4、b,n,a,m,o,n,o,b,n,o,s,o,d,g,o,r,y,e,b,u,e,m,trolley,n,b,e,n,o,p,h,n,I,d,_,c,a,r,d,1,counts,counts,detail,订,购,m,n,p,r,i,c,e,r,o,r,d,e,s,consignee,s,t,a,t,u,s,m,o,n,e,y,o,d,a,t,e,2,将概念模型转化成关系模型,逻辑设计,2,将概念模型转化成关系模型，标志出主,外键，判断每个关系模式的范式并说明理,由。,5,分,2,将概念模型转化成关系模型,逻辑设计,Category,cno,cname,BC,范式,Buyer,bno,bn

5、ame,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,利用,SQL,语句完成数据库模型中其它表格的创建，要求创建相应的索引,另外,需要修改,buyer,表结构,为此表添加,1,个,email,字段，数据类型

6、为,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 refe

7、rences 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

8、,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,分,cre

9、ate 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,DD

10、L,数据定义语言,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,

11、default 1,price numeric(8,1,go,create index idx_detail_ono,on detail(ono,create index idx_detail_gno,on detail (gno,go,3,DDL,4,DML,4,请用,SQL,语句完成以下数据更新操作。,20,分,1,往商品分类表中插入记录,c001,食品,2,往商品表中插入记录：添加,1,个商品信息,商,品类别：食品、编号,g001,商品名：巧克力,单位：块、详情：榛子,牛奶味、价格,20,元,3,进行商品调价操作，将所有食品类商品价格,打,9,折,4,清空买家用户代码为考生学号的购物车,

12、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,进行商品调价

13、操作，将所有食品类商品价格,打,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,查找购买过商品名为“手机”或“笔记本,电

14、脑”的买家信息，包括买家号、姓名、购买数,量。（本小题不用写关系代数,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 gna

15、me,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.on

16、o=d.ono,and d.gno=g.gno,and b.bname=Berry,gname, counts, price,b.bno=o.bno,goods,buyer,orders,detail,o.ono=d.ono,d.gno=g.gno,b.bname=Berry,5,DML,和关系代数表达式,5,完成下列查询要求，写出对应的,SQL,语句及关,系代数表达式,20,分,4,查找购买过商品名为“手机”或“笔记本,电脑”的买家信息，包括买家号、姓名、购买数,量。（本小题不用写关系代数,select b.bno,b.bname,from buyer b where b.bno,in,se

17、lect 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,S

18、elect 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=o