《数据库原理考试习题及答案.ppt》由会员分享，可在线阅读，更多相关《数据库原理考试习题及答案.ppt（23页珍藏版）》请在三一办公上搜索。
1、数据库原理复习,设计题一有1个B2C电子购物网站，其业务规则如下：购物网站公司负责维护商品分类信息（category），管理商品（goods）目录,每个商品都需要归属到某个商品分类下；网民可以注册成为网站买家（buyer）,买家可以在购物网站通过商品名、商品类名搜索自己需要的商品，把选中的一定数量商品放入自己的购物车（trolley）中，选购完成后下订单（orders），订单需要在明细（detail）中记录商品数量、交易价格，下单时需要填写详细的收货人信息，然后通过第三方网银支付系统付款，付款成功后网银支付系统会在订单中标注付款成功标志,此标志的值分别为0：等待付款；1：等待发货；2：等待收货
2、；3：关闭；公司看到买家付款成功后负责物流快递商品，买家收到商品后确定收货；暂不考虑退、换货业务。商品分类实体的属性包括分类号(cno)、类名（cname）。商品实体的属性包括商品号(gno)、商品名(gname)、单位(unit)、商品详情（detail）、价格(price)。买家实体的属性包括用户代码（bno）、姓名（bname）、电话（bphone）、身份证号(id_card)。订单实体的属性包括订单号(ono)、时间（odate）、状态（status）、收货人(consignee)、总金额(money)。,1、概念模型设计-ER图,1、进行数据库概念模型设计，补充完善图1所示的ER模型
3、，要求同时标出联系的连通词。（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)
4、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
5、,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(
6、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、利
7、用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);gocreate index
8、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
9、char(10)foreign key references goods(gno)on update cascade,counts int default 1,price numeric(8,1)gocreate 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、商品名：巧克力、单位：块、详情：榛
10、子+牛奶味、价格：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,巧克力,块,榛子+牛奶味
11、,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）、查找用户代码为“Ber
12、ry”的买家所有已购商品清单，包括商品名、价格、数量。（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分
13、）（2）、查找类别为“食品”的所有商品清单，包括商品名、单位、价格。select gname,unit,price from goods g,category c where o=o and ame=食品;gname,unit,price(o=o cname=食品（goodscategory）),5、DML和关系代数表达式,5、完成下列查询要求，写出对应的SQL语句及关系代数表达式。（20分）（3）、查找用户代码为“Berry”的买家所有已购商品清单，包括商品名、价格、数量。select g.gname,d.counts,d.price from goods g,buyer b,orders
14、o,detail dwhere 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 fr
15、om buyer b where b.bno in(select o.bno from orders o,detail d,goods gwhere 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,c
16、ount_c,sum_c)asSelect ame,count(o),sum(detail.counts*detail.price)From category,goods,detail,ordersWhere 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
17、,7、编写1个存贮过程，完成往指定订单中添加商品的操作业务，输入参数包括订单编号、商品编号、购买数量，需要检查订单编号、商品编号是否存在。（10分）create procedure prg_ins_detail ono varchar(16),gno char(10),counts intasdeclare 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;s
18、elect price=price from goods where gno=gno;insert into detail(ono,gno,counts,price)values(ono,gno,counts,price);go,8、trigger,8、编写1个触发器，完成订单表中总金额的自动累计功能，即通过订单明细中商品的价格与数量自动统计订单中的总金额。（10分）,8、trigger,8、编写1个触发器，完成订单表中总金额的自动累计功能，即通过订单明细中商品的价格与数量自动统计订单中的总金额。（10分）create trigger tri_detail_ins on detail for insert asdeclare 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 13,5;,
