1、數(shù)據庫原理 復習,設計題一 有1個B2C電子購物網站，其業(yè)務規(guī)則如下： 購物網站公司負責維護商品分類信息（category），管理商品（goods）目錄,每個商品都需要歸屬到某個商品分類下；網民可以注冊成為網站買家（buyer）,買家可以在購物網站通過商品名、商品類名搜索自己需要的商品，把選中的一定數(shù)量商品放入自己的購物車（trolley）中，選購完成后下訂單（orders），訂單需要在明細（detail）中記錄商品數(shù)量、交易價格，下單時需要填寫詳細的收貨人信息，然后通過第三方網銀支付系統(tǒng)付款，付款成功后網銀支付系統(tǒng)會在訂單中標注付款成功標志,此標志的值分別為0：等待付款；1：等待發(fā)貨；2：等

2、待收貨；3：關閉；公司看到買家付款成功后負責物流快遞商品，買家收到商品后確定收貨；暫不考慮退、換貨業(yè)務。 商品分類實體的屬性包括分類號(cno)、類名（cname）。 商品實體的屬性包括商品號(gno)、商品名(gname)、單位(unit)、商品詳情（detail）、價格(price)。 買家實體的屬性包括用戶代碼（bno）、姓名（bname）、電話（bphone）、身份證號(id_card)。 訂單實體的屬性包括訂單號(ono)、時間（odate）、狀態(tài)（status）、收貨人(consignee)、總金額(money)。,1、概念模型設計 - ER圖,1、進行數(shù)據庫概念模型設計，補充完善

3、圖1所示的ER模型，要求同時標出聯(lián)系的連通詞。（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,c

4、onsignee,money,bno) BC范式 Detail(dno,gno,ono,counts,price) BC范式,3、DDL,數(shù)據定義語言： 3、利用SQL語句完成數(shù)據庫模型中其它表格的創(chuàng)建，要求創(chuàng)建相應的索引;另外需要修改buyer表結構, 為此表添加1個email字段，數(shù)據類型為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 tab

5、le 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, bn

6、ame 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

7、; create index idx_orders_bno on orders(bno); go;,3、DDL,數(shù)據定義語言： 3、利用SQL語句完成數(shù)據庫模型中其它表格的創(chuàng)建，要求創(chuàng)建相應的索引;另外需要修改buyer表結構, 為此表添加1個email字段，數(shù)據類型為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

8、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,數(shù)據定義語言： 3、利用SQL語句完成數(shù)據庫模型中其它表格的創(chuàng)建，要求創(chuàng)建相應的索引;另外需要修改buyer表結構, 為此表添加1個email字段，數(shù)據類型為varchar(32)。（15分） create table detail ( dno int ide

9、ntity 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

10、、請用SQL語句完成以下數(shù)據更新操作。（20分） （1）往商品分類表中插入記錄：(c001,食品)。 （2）往商品表中插入記錄：添加1個商品信息,商品類別：食品、編號：g001、商品名：巧克力、單位：塊、詳情：榛子+牛奶味、價格：20元。 （3）進行商品調價操作，將所有食品類商品價格打9折。 （4）清空買家用戶代碼為考生學號的購物車,4、DML,4、請用SQL語句完成以下數(shù)據更新操作。（20分） （1）往商品分類表中插入記錄：(c001,食品)。 insert category (cno, cname) values (c001,食品); （2）往商品表中插入記錄：添加1個商品信息,商品類別：

11、食品、編號：g001、商品名：巧克力、單位：塊、詳情：榛子+牛奶味、價格：20元。 insert into goods (gno, gname,unit,detail,price,cno) values (g001,巧克力,塊,榛子+牛奶味,20,c001);,4、DML,4、請用SQL語句完成以下數(shù)據更新操作。（20分） （3）進行商品調價操作，將所有食品類商品價格打9折。 update goods set price=price*0.9 where cno=c001; （4）清空買家用戶代碼為考生學號的購物車 delete trolley where bno=004062;,5、DML和關

12、系代數(shù)表達式,5、完成下列查詢要求，寫出對應的SQL語句及關系代數(shù)表達式。（20分） （1）、查找商品名為“巧克力”的商品信息，包括商品名、單位、價格。 （2）、查找類別為“食品”的所有商品清單，包括商品名、單位、價格。 （3）、查找用戶代碼為“Berry”的買家所有已購商品清單，包括商品名、價格、數(shù)量。 （4）、查找購買過商品名為“手機”或“筆記本電腦”的買家信息，包括買家號、姓名、購買數(shù)量。（本小題不用寫關系代數(shù)）,5、DML和關系代數(shù)表達式,5、完成下列查詢要求，寫出對應的SQL語句及關系代數(shù)表達式。（20分） （1）、查找商品名為“巧克力”的商品信息，包括商品名、單位、價格。 sele

13、ct gname,unit,price from goods where gname=巧克力; gname,unit,price（ gname=巧克力（goods）,5、DML和關系代數(shù)表達式,5、完成下列查詢要求，寫出對應的SQL語句及關系代數(shù)表達式。（20分） （2）、查找類別為“食品”的所有商品清單，包括商品名、單位、價格。 select gname,unit,price from goods g,category c where o=o and ame=食品; gname,unit,price(o=o cname=食品（goodscategory） ),5、DML和關系代數(shù)表達式,5、

14、完成下列查詢要求，寫出對應的SQL語句及關系代數(shù)表達式。（20分） （3）、查找用戶代碼為“Berry”的買家所有已購商品清單，包括商品名、價格、數(shù)量。 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 （goods

15、buyerordersdetail）,5、DML和關系代數(shù)表達式,5、完成下列查詢要求，寫出對應的SQL語句及關系代數(shù)表達式。（20分） （4）、查找購買過商品名為“手機”或“筆記本電腦”的買家信息，包括買家號、姓名、購買數(shù)量。（本小題不用寫關系代數(shù)） 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、編

16、寫1個視圖，按商品類別分類統(tǒng)計出所有商品2014年之前的銷售數(shù)量、銷售額，視圖列集包括類別名、銷售數(shù)量、銷售金額。（10分）,6、view,按商品類別分類統(tǒng)計出所有商品2014年之前的銷售數(shù)量、銷售額，視圖列集包括類別名、銷售數(shù)量、銷售金額。 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 a

17、nd detail.ono=orders.ono and year(orders.odate) =2014 group by o,ame,7、procedure,7、編寫1個存貯過程，完成往指定訂單中添加商品的操作業(yè)務，輸入參數(shù)包括訂單編號、商品編號、購買數(shù)量，需要檢查訂單編號、商品編號是否存在。（10分）,7、procedure,7、編寫1個存貯過程，完成往指定訂單中添加商品的操作業(yè)務，輸入參數(shù)包括訂單編號、商品編號、購買數(shù)量，需要檢查訂單編號、商品編號是否存在。（10分） create procedure prg_ins_detail ono varchar(16), gno char(1

18、0), 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個觸發(fā)器，完成訂單表中總金額的自動累計功能，即通過訂單明細中商品的價格與數(shù)量自動統(tǒng)計訂單中的總金額。（10分）,8、trigger,8、編寫1個觸發(fā)器，完成訂單