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

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

3、計(jì)，補(bǔ)充完善圖1所示的ER模型，要求同時(shí)標(biāo)出聯(lián)系的連通詞。（10分）,.,.,2、將概念模型轉(zhuǎn)化成關(guān)系模型,邏輯設(shè)計(jì)： 2、將概念模型轉(zhuǎn)化成關(guān)系模型，標(biāo)志出主、外鍵，判斷每個(gè)關(guān)系模式的范式并說明理由。（5分）,.,2、將概念模型轉(zhuǎn)化成關(guān)系模型,邏輯設(shè)計(jì)： Category(cno,cname） BC范式 Buyer(bno,bname,bphone,id_card) 2范式，因?yàn)閕d_card的問題而存在傳遞依賴。 Goods(gno,gname,unit,detail,price,cno) BC范式 Trolley(tno,bno,gno,counts) BC范式 Orders(ono,od

4、ate,status,consignee,money,bno) BC范式 Detail(dno,gno,ono,counts,price) BC范式,.,3、DDL,數(shù)據(jù)定義語言： 3、利用SQL語句完成數(shù)據(jù)庫模型中其它表格的創(chuàng)建，要求創(chuàng)建相應(yīng)的索引;另外需要修改buyer表結(jié)構(gòu), 為此表添加1個(gè)email字段，數(shù)據(jù)類型為varchar(32)。（15分） create table category (cno char(4) primary key, cname varchar(80); go; create index idx_category_name on category(cno);

5、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) p

6、rimary 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 updat

7、e cascade); go; create index idx_orders_bno on orders(bno); go;,.,3、DDL,數(shù)據(jù)定義語言： 3、利用SQL語句完成數(shù)據(jù)庫模型中其它表格的創(chuàng)建，要求創(chuàng)建相應(yīng)的索引;另外需要修改buyer表結(jié)構(gòu), 為此表添加1個(gè)email字段，數(shù)據(jù)類型為varchar(32)。（15分） create table trolley ( tno int identity primary key, bno varchar(16) foreign key references buyer(bno) on update cascade, gno char(

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

9、tail ( 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

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

11、品表中插入記錄：添加1個(gè)商品信息,商品類別：食品、編號：g001、商品名：巧克力、單位：塊、詳情：榛子+牛奶味、價(jià)格：20元。 insert into goods (gno, gname,unit,detail,price,cno) values (g001,巧克力,塊,榛子+牛奶味,20,c001);,.,4、DML,4、請用SQL語句完成以下數(shù)據(jù)更新操作。（20分） （3）進(jìn)行商品調(diào)價(jià)操作，將所有食品類商品價(jià)格打9折。 update goods set price=price*0.9 where cno=c001; （4）清空買家用戶代碼為考生學(xué)號的購物車 delete trolley w

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

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

14、dscategory） ),.,5、DML和關(guān)系代數(shù)表達(dá)式,5、完成下列查詢要求，寫出對應(yīng)的SQL語句及關(guān)系代數(shù)表達(dá)式。（20分） （3）、查找用戶代碼為“Berry”的買家所有已購商品清單，包括商品名、價(jià)格、數(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

15、d.gno=g.gno b.bname=Berry （goodsbuyerordersdetail）,.,5、DML和關(guān)系代數(shù)表達(dá)式,5、完成下列查詢要求，寫出對應(yīng)的SQL語句及關(guān)系代數(shù)表達(dá)式。（20分） （4）、查找購買過商品名為“手機(jī)”或“筆記本電腦”的買家信息，包括買家號、姓名、購買數(shù)量。（本小題不用寫關(guān)系代數(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.gnam

16、e=巧克力 or g.gname=筆記本電腦),.,6、view,6、編寫1個(gè)視圖，按商品類別分類統(tǒng)計(jì)出所有商品2014年之前的銷售數(shù)量、銷售額，視圖列集包括類別名、銷售數(shù)量、銷售金額。（10分）,.,6、view,按商品類別分類統(tǒng)計(jì)出所有商品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,order

17、s 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個(gè)存貯過程，完成往指定訂單中添加商品的操作業(yè)務(wù)，輸入?yún)?shù)包括訂單編號、商品編號、購買數(shù)量，需要檢查訂單編號、商品編號是否存在。（10分）,.,7、procedure,7、編寫1個(gè)存貯過程，完成往指定訂單中添加商品的操作業(yè)務(wù)，輸入?yún)?shù)包括訂單編號、商品編號、購買數(shù)量，需要檢查訂單編號、商品編號是否存在。（10分） create procedure

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