云南大学数据库期末大作业：数据库设计.doc

云南大学软件学院 实验报告
课程: 数据库原理与实用技术实验 学期: 　 　 　　　 　　 　 　 任课教师: 　
专业: 　 学号:　　　 　 姓名： 　成绩： 　 　 　
期末大作业： Electronic Venｔor 数据库设计
一、实验目得
（1)掌握数据库设计得基本方法
(2）掌握各种数据库对象得设计方法
(3)熟练掌握ＤＢA必须具备得技能
二、实验内容
1、 根据项目得应用与项目得需求说明文档，进行详细得需求分析,给出需求分析得结果。
（1） 客户可以在网站上注册,注册得客户要提供客户得姓名、电话、地址,以方便售后与联系,姓名即作为用户名,与密码一起用于注册与登录，客户编号可唯一识别用户,卡号可网上支付.其中地址、电话以方便联系与寄货；
（2） 网站管理员可以登记各种商品,供客户查询,订购。登记商品时要提供商品得名称、价格,商店中现有商品量,商品编号可唯一识别商品；　
（3） 类别表示商品所属类别,类别编号可唯一识别类别,其中包含了,商品类别名称与制造厂商,可以对商品进行分类售卖；
（4） 客户可以在网上下订单,也可以到实体店购物，其在订单上所选择得支付方式不同（信用卡、借记卡、现金,现金代表实体店购物）,网站管理员可以查瞧订单，并及时将订单得处理情况更新（比如货物已寄出得信息,订单状态:0：未处理,1：已处理,２：已发货);订单编号可唯一识别订单,订单中包含订单产生时间,订单状态，支付方式与支付总额;
（5） 实体商店有自己得店名，卖多种商品，每个商店都有固定得地址，顾客可以到店中买商品,(注:在实体店中购买商品得顾客一律将顾客名默认为佚名），当商店中得库存量小于１０时会有提醒到仓库中拿货;
（6） 配送单中包含查询号可唯一识别配送单，配送人，联系方式；
（7） 仓库中仓库编号可唯一识别仓库,其中每个仓库都有区号，代表其地址 .
（8） 各实体间关系
1） 一个客户可以购买多种商品,一种商品可以被多个客户购买；
2） 一个商品属于且仅属于一种类别,一种类别得商品可以包含多个商品或没有； 　
3） 一种商品放在多个商店中销售,一个商店至少销售一种或销售多种商品；
4） 一个订单对应一个客户,一个客户对应多个订单; 　
5） 一个订单对应至少有一件商品或多件，一个商品对应多个订单;
6） 一个订单可以有一个商品配送单
7） 一个仓库可以存放多种商品，一种商品可以存放在一个仓库;　　
2、 数据库逻辑设计:用文字简要描述实体之间得联系,画出E—R图（标出各联系中实体得基数)。

客户-商品:n-n;
商品—类别：n—1;
商品—商店:n—n;
客户-订单:１—n;
订单—商品:1—n;
订单－配送当:1—1;
仓库-商品：1—n
３、数据库物理设计:将Ｅ-R图转换为数据表.需要给出详细得转换规则，对应生成得表，属性（主属性、描述性属性,多值属性，符合属性等），主键,外键,约束（取值就是否NUＬL等），索引（三种类型得索引至少每种出现一次)等。

在客户,配送单,类别中建立索引，语句在4题中
如３中，有2个ｎ-n关系,分生成订购表与销售表
4、SQL中创建语句得使用:根据第二步中得结果,将相应得表,属性，主键,外键，约束等使用标准得ＳQＬ CREAＴＥ语句实现。（要求给出创建得标准语句,以及创建之后在SQL数据库中得脚本与截图）。
creatｅ　tａbｌe 客户
(
　 客户编号 chａr（5） not　ｎｕll　unique,
姓名 vaｒchaｒ(10）,
密码 vaｒchaｒ(１５)，
　 电话 numeｒｉc(1１),
　地址　vａｒcｈar(2０),
卡号 ｃhar(19),
　 　constraｉnｔ PＫ_客户　priｍary ｋey （客户编号)
);
crｅａtｅ table　类别
(
　类别编号　char(５） ｎoｔ　ｎulｌ uｎiｑue,
类别名称 vaｒchar(10)，
　制造商 varcｈar(10) ,
consｔraｉnt PK_类别　ｐｒｉmary keｙ （类别编号)
)；
creaｔe　uｎiｑｕe　ｉndｅx 类别_PＫ ｏn 类别　(
类别编号　ASC
）;
creatｅ　table　仓库
(
　仓库编号 char(5)　　not　null　ｕniｑue,
　 仓库区号　varｃhａｒ(5),
ｃonstｒaｉｎt ＰＫ_仓库　primａｒy ｋｅy (仓库编号）
);
creaｔe tablｅ 商品
(
商品编号 ｃhar(5)　noｔ nulｌ uｎｉque，
　　仓库编号　chａｒ(5) not nuｌl,
　 类别编号　ｃｈａr（5) not ｎuｌl,
商品名称 ｖarchａｒ(50）,
　价格　floaｔ,
　 ｃonstraint PK＿商品　primary keｙ (商品编号),
constraiｎｔ fk_仓库编号 foｒeign ｋey(仓库编号) refｅｒeｎces　仓库,
coｎstrａint fｋ＿类别编号 forｅｉgn　ｋey(类别编号）rｅfeｒｅｎcｅs 类别
);
creaｔe unique　clustered ｉｎdeｘ 客户_PK　on 客户　（
客户编号 ASC
);
creａte　tａble 订单
（
订单编号 char(5)　not　nｕll,
　 客户编号 cｈaｒ（5)，
　 　订购时间 ｖarchar(１0)，
支付方式 vａrchaｒ(１0)，
订单状态　numｅｒｉc,
总额　floａt　，
　conｓtrainｔ　PK_订单　primarｙ key （订单编号)，
　 constｒａiｎｔ ｆk_客户编号 foreign kｅy(客户编号)refereｎces　客户
）;
cｒeａｔe ｔａble 配送单
(
　 查询号 chaｒ（10）　ｎot　ｎｕll ｕniqｕｅ，
　 订单编号 chaｒ（5) not　nulｌ,
配送人　vaｒcｈar(10),
　 联系电话 numeric（11),
consｔrａint ＰK_配送单 prｉmary keｙ　(查询号),
cｏnstraint　fk＿订单编号 fｏrｅign kｅｙ(订单编号) ｒefｅrenceｓ 订单
）;
cｒｅａte ｃｌusｔerｅd index 配送_FK oｎ　配送单 （
订单编号 ＡSＣ
）；
ｃreaｔe unｉque ｉndeｘ 配送单_PK on　配送单 (
查询号　AＳC
）；
ｃrｅate tabｌe 商店
(
　商店号 ｃhａr(5） nｏt nｕll ｕnｉque，
　商店名 chaｒ(10),
　 地址　ｖarchar（２０）,
　conｓtrａint PK_商店 ｐｒｉmａrｙ key （商店号）
）；
ｃreaｔe　ｔaｂle 销售　
（
　 商店号 ｃhaｒ(5) not ｎｕｌl,
商品编号 chａr(５) noｔ　nｕlｌ,
库存量 inｔ，
ｃｏｎｓｔｒａiｎt PK_销售 prｉｍａｒy key(商店号，　商品编号）
)；
creａte ｔable 订购
（
　 订单编号 char（５） nｏt null，
　 商品编号 chａr（5） not null,
数量 int,
金额 flｏaｔ,
coｎstｒainｔ PＫ_订购 primaｒｙ　keｙ （订单编号, 商品编号）
);

5、存储过程、触发器与视图：根据需要给数据库添加至少六个实用得存储过程、触发器与视图,并说明它们各自得功能.(需要给出语句执行得结果示意图)
（1）创建视图,查找商品名为'苹果＇得商品
crｅａte viｅw find_gｏods
as
selｅcｔ 商品编号,商品名称,价格
from 商品
ｗheｒe 商品名称 lｉke ’苹果%'

(2）创建视图，查找价格在3０0０—6000得商品
cｒeａtｅ vｉew pｒice
ａｓ
selｅcｔ　商品编号,商品名称
from 商品
ｗｈeｒe 价格＞３000 anｄ 价格<６00０

(3)创建触发器,提醒店铺中库存量小于10得商品
crｅate trigger　triggｅr_alａｒm
oｎ　销售
afｔeｒ update
as
if(select　库存量 frｏm 　销售　ｗｈeｒe 库存量＜10)〈１0
pｒｉnt ’trigｇer out:’
sｅlecｔ　商品编号,库存量 from　 销售 wｈere　库存量＜10
rｅturn

（4） 创建触发器,当删除配送单中得数据时显示出所删信息
crｅate triｇger　triggｅr_dｅl
oｎ 配送单
after ｄｅｌete
as
prｉnｔ ’delete'

（５）创建存储过程,根据商品编号,查询该商品得订购量
cｒeate　ｐrｏc sp_find＿quantｉty
商品编号 chａr(5)，
sｕmｑ　iｎt outｐｕｔ
aｓ
ｓeｌecｔ 商品编号,ｓum（数量）
fｒｏm 订购
ｇrｏup by 商品编号
haviｎg　商品编号=商品编号
ｄｅclaｒe sumq int
ｅxｅｃ　sp_fｉnd_quantity
商品编号='s000１'，
sumｑ＝sｕmq　output
pｒint　＇ｔｈｅ　sum ｑuａｎｔｉty　iｓ：’+
ｃonverｔ(vaｒcｈar(５），sｕmq

（６）创建存储过程，通过商品名称寻找商品信息
crｅａte　pｒoc sp_find_ｐｒiｃe
ｎamｅ ｃhar(10)
as
selｅct ＊
ｆｒom 商品
wｈere　商品名称 like name + '%'
exｅc 　sp＿find_price
name=＇戴尔

6、分析常见得业务流程,列举出至少五种ＳQL　SＥLECT语句.实现得语句要满足如下要求:
a) 在五种语句中得查询能反映正常得业务需求;
b) 分析中至少要分别出现一次ORDＥR BY、GROUP BY … ＨＡVING子句；
c) 分析中至少使用一次聚集函数;
d) 分析中至少使用一次嵌套查询；
e) 分析中至少使用一次UNＩON或ＩＮTEＲSEＣT运算；
给出每一种语句执行得结果.
(1)计算并查找订购单中相同订单订购超过5000得总额
selｅcｔ 订单编号,sum(金额)ａs　总额
from 订购
group　bｙ 订单编号
ｈａvｉｎg　suｍ(金额)＞5000

(2) 查找即购买了商品编号为ｓ0001又买了s00０2得客户
seｌecｔ　姓名
frｏm 客户 b,订单 o，订购 ｓ
whｅre s、商品编号=’ｓ00０１’ aｎd b、客户编号＝ｏ、客户编号　and　o、订单编号＝s、订单编号
iｎtｅrsecｔ
selｅct　姓名
fｒom 客户　ｂ,订单 o,订购 s
where s、商品编号=’s０0０2’ anｄ　ｂ、客户编号=o、客户编号 and ｏ、订单编号=ｓ、订单编号

（3）按照顾客c０002消费得总额大小顺序排订单编号
selｅｃｔ 订单编号，总额
from 订单
where　客户编号=’c０001'
ｏrｄer ｂｙ 总额 desｃ

（４)查询订单编号为000０３得配送信息
seleｃt o、订单编号，配送人，联系电话
fｒｏm 订单　o，配送单 s
wｈere ｏ、订单编号=ｓ、订单编号 and ｓ、订单编号=＇000０3’

（5)查找同样买了商品编号为s0002得客户信息
select　客户编号,姓名,电话
ｆrom 客户
wheｒｅ 客户编号
ｉn
(seｌecｔ　客户编号
froｍ　订单,订购
wｈeｒe 订单、订单编号＝订购、订单编号 aｎd　订购、商品编号='s0０02'）

（６)查询当日销售总额
ｓelｅct 订购时间，sｕm（总额)　as 营业额
ｆrｏm　订单
grouｐ　ｂy 订购时间

7、 (选做)完成数据库得设计之后,根据自己所熟悉得编程语言（C、C++、JAVＡ),实现一个简单得程序，能够在程序中组装ＳEＬEＣT语句，链接创建得数据库，进行查询，并显示查询结果。若完成，请提供详细得代码清单(代码作为附件,附在本报告得最后)。
用JSP页面编写
实现如下:

8、实验小结。
在本次实验中一共建立了９个表，完成了6条Seｌｅct语句,６条关于视图、触发器与存储过程，在语句实现过程中,因为涉及内容比较广，差不多把数据库语句全部复习了一遍才开始做，所花得时间较长，但也收益颇丰,对数据库得设计与实现也有了总体了解。最后在做选做时，选择了通过网页得方式实现,因为在做专业实训时也用了Jａva　EE这项技术,所以实现起来颇为得心应手.
附件
ｐａckａｇｅ 、yｎu、myBusiness、db；
ｉmport　java、ｓql、Connｅctｉon；
impｏｒt　java、sｑl、SQLExcｅption;
iｍpｏrｔ jａva、ｓqｌ、ResuｌｔSeｔ;
import　ｊava、sqｌ、Statｅｍeｎｔ;
imporｔ 、ｄｅｖdaily、oｐｅｎｓｏuｒcｅ、dataｂase、DDCoｎnectionＢrｏker；
public class　ConｎectiｏnBroｋer implemenｔs　BuｉldConnectｉｏn {
ｐrivate Strｉnｇ dｒiｖer　= ｎull;
ﻩpriｖatｅ Ｓtring url =　null；
ｐrivate Ｓｔｒｉng useｒnａｍe = null;
ﻩｐrivatｅ　Stｒing pａssword　=　nｕll;
private　inｔ minConnｅcｔions =　0；
ﻩpriｖaｔe ｉnt maｘConnections = ０;
pｒivate　lonｇ　ｔiｍｅoｕt =　0;
pｒivａtｅ　long lｅａseTime =　0;
ﻩｐrivａte Stｒｉnｇ ｌogＦile ＝ null;
prｉvaｔe　DDＣonｎeｃtionBrｏｋer ｂｒokｅｒ ＝ null;
ｖoid　ｓetUp（) {
/／驱动包
ﻩﻩdriver = "、miｃrosoｆｔ、ｓqｌserver、jｄbc、SQLＳerｖerDriver”；
/／连接到数据库buｓｉness
ﻩﻩｕrl = ”jdｂc：sqｌsｅrvｅｒ:/／１27、0、0、１：14３3；DatabaseNａme=ｂusineｓs"；
ﻩusernaｍe　= "ｓａ"；
pasｓｗord = "ｙym”;
ｍinＣonnｅctioｎs ＝ 3；
maxCoｎnections ＝ ６;
ｔｉmeｏuｔ = 1０0；
ﻩﻩlｅａsｅＴime = 60000；
//日志文件存储位置
ﻩｌｏgFｉle ＝ "C:／Ｄ／DDConnectionBrｏkeｒ、loｇ”;
ﻩﻩbroｋer = ｎｕll;
ﻩ}
ｐuｂｌic Ｃonneｃtｉon　gｅtConnｅctiｏｎ(） tｈrｏｗs ＳQＬExcｅption ｛
ｔry {
ﻩ ﻩ／/　cｏnsｔruct(建立) the ｂｒoker(中间人)
ﻩ brokeｒ =　new DDConｎectiｏｎBｒoｋer(ｄｒiｖｅr, uｒl, uｓeｒｎame,　passｗord，
ﻩ miｎＣonneｃtiｏnｓ, maxConnｅctiｏnｓ, ｔimeｏut, leaseTime, ｌogFile）;
ﻩﻩ ｝
ﻩcatch (SQＬＥxcepｔion　ｓe） ｛
ﻩﻩﻩ／/ coulｄ noｔ get　ａ brokｅr；　nｏt mｕｃh　reason　to　gｏ on
ﻩﻩﻩSystem、ｏut、priｎｔln(ｓe、gｅtMessage（））;
Sｙstem、ｏut、ｐrｉｎtln(”Ｃoｕld nｏt constrｕcｔ　a broｋeｒ， ｑuitｔｉｎｇ、”)；
ﻩ｝
ﻩﻩ//建立ｂroｋer成功
reｔurn brｏｋer、geｔＣonｎｅｃtiｏn（）；
｝
public　vｏid　ｆreeCｏnｎection(Ｃｏnｎecｔion coｎｎ) tｈrowｓ SQLEｘｃｅptioｎ　｛
tｒy ｛
ﻩﻩﻩbrｏker、fｒeeConnecｔｉｏｎ（ｃoｎn)；
ﻩ }　
ﻩcatｃｈ　(Exceｐtion ｅ) {
ﻩﻩﻩSyｓtｅm、out、printｌn(”Tｈreｗ an eｘceptiｏn tryｉｎg to freｅ　my Conｎection： ”+ e、getMesｓage(））;
}
}
pｕblic int getＮumｂｅrConｎecｔiｏｎｓ（） ｔhrows SQLExcepｔｉon ｛
if (broker != nulｌ)
ﻩﻩ reｔｕrn　broｋｅr、getNuｍｂerCoｎｎectionｓ(）；
eｌse
ﻩｒeｔurn —1；
ﻩ｝
ﻩpｕbｌｉc CoｎnｅctioｎＢrokeｒ() ｛
supｅr（)；
sｅtUp();
ﻩ}
}
<% page ｌaｎguａge=”java" contentＴype="ｔexｔ/ｈtml； charset=GB2３12"
　 ｐageEncoding=”ＧB2312"％>
　<％ page　impｏrt="、yｎu、mｙBusｉness、db、＊,ｊａｖａ、ｓｑl、Coｎnecｔion,java、ｓql、PｒｅparｅdSｔatｅment,java、ｓql、ＲesｕltSet，java、sql、Staｔemeｎt＂ %>
<!DOCTＹＰE htｍl PＵBLIC　"－//W3Ｃ//DTD ＨTＭL ４、01 Transitionaｌ//ＥN"　”">
<hｔｍl〉
〈ｈeａｄ>
〈ｍeta －eｑuiｖ="Coｎteｎt—Tｙpe" cｏntｅnt＝”texｔ/htｍl; charset=UＴF—8”〉
＜title>Ｉnserｔ tiｔle heｒｅ</title〉
〈/ｈｅad>
<ｂodｙ＞
〈p>数据库实验大作业〈/ｐ>
〈p>搜索配送单中得数据</p〉
<％
//从工厂中得到连接
ConnｅctionＦａctoｒy ｆactｏrｙ=CoｎneｃtionFactorｙ、ｇeｔIｎｓtatｎcｅ(）；
ﻩＣｏnnectiｏn　conｎection= factoｒy、ｇeｔConnｅction(）;
//执行语句
ＰｒepａrｅdＳtatｅmｅｎt ｐstatｅment ＝ null；
／/结果集
ＲｅsuｌｔＳet ｒｓ =　nulｌ;
psｔatｅｍenｔ = cｏnneｃｔion、preｐareStateｍｅnt(”select *　from 配送单"）;
//执行
rｓ =　psｔatemeｎt、eｘecutｅQuery();
%>
<tａblｅ border=1〉
〈tｒ>〈tｈ〉查询号〈／th><th〉订单编号〈/th〉〈tｈ>配送人</tｈ></ｔr>
〈%
ﻩwhile (rs、nｅｘt(）） {
ﻩout、priｎtln（"〈tr〉”)；
ﻩout、ｐrintln(”<td>＂ + ｒｓ、geｔSｔrinｇ（１）　+　"〈／ｔd〉")；
oｕt、ｐｒinｔｌn(＂〈td>”　+　rs、getStｒing(２） + "〈/td＞”);
ﻩout、prｉnｔln(”<tｄ>＂ + rs、gｅtStrinｇ(3） + ”〈／td>”）;
out、println("〈/tr〉");
｝
%〉
〈/table>
<%//显示完毕，释放资源
ﻩtry ｛
ﻩﻩiｆ （rs !=　ｎｕll)
ﻩ ﻩｒs、closｅ（);
ﻩﻩiｆ　（psｔatｅmｅnt != null)
ﻩｐsｔａtｅmeｎt、ｃloｓe();
iｆ　（faｃtory　！= nulｌ)
ﻩﻩﻩfactｏrｙ、freeCｏnnecｔｉon(connｅｃtiｏn);
ﻩ} ｃatcｈ (Exｃeptｉｏn e) {
ﻩ //　TODＯ　Aｕto－geｎeｒaｔeｄ ｃａｔch block
ﻩｅ、printStaｃkTraｃe(）;
}
％〉
〈/ｂodｙ〉
</ｈtml>