资源描述
单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,数据库原理,复习,设计题一,有,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,c
展开阅读全文