云南大学大数据库期末大作业大数据库设计.docx
《云南大学大数据库期末大作业大数据库设计.docx》由会员分享，可在线阅读，更多相关《云南大学大数据库期末大作业大数据库设计.docx（22页珍藏版）》请在冰豆网上搜索。
云南大学大数据库期末大作业大数据库设计
云南大学软件学院实验报告
课程：
数据库原理与实用技术实验学期：
任课教师：
专业：
学号：
姓名：
成绩：
期末大作业：
ElectronicVentor数据库设计
一、实验目的
（1）掌握数据库设计的基本方法
（2）掌握各种数据库对象的设计方法
（3）熟练掌握DBA必须具备的技能
二、实验内容
1、根据项目的应用和项目的需求说明文档，进行详细的需求分析，给出需求分析的结果。
（1）客户可以在网站上注册，注册的客户要提供客户的姓名、电话、地址，以方便售后和联系，姓名即作为用户名，和密码一起用于注册和登录，客户编号可唯一识别用户，卡号可网上支付。
其中地址、电话以方便联系和寄货；
（2）网站管理员可以登记各种商品，供客户查询，订购。
登记商品时要提供商品的名称、价格，商店中现有商品量，商品编号可唯一识别商品；
（3）类别表示商品所属类别，类别编号可唯一识别类别，其中包含了，商品类别名称和制造厂商，可以对商品进行分类售卖；
（4）客户可以在网上下订单，也可以到实体店购物，其在订单上所选择的支付方式不同（信用卡、借记卡、现金，现金代表实体店购物），网站管理员可以查看订单，并及时将订单的处理情况更新（比如货物已寄出的信息，订单状态：
0：
未处理，1：
已处理，2：
已发货）；订单编号可唯一识别订单，订单中包含订单产生时间，订单状态，支付方式和支付总额；
（5）实体商店有自己的店名，卖多种商品，每个商店都有固定的地址，顾客可以到店中买商品，（注：
在实体店中购买商品的顾客一律将顾客名默认为佚名），当商店中的库存量小于10时会有提醒到仓库中拿货；
（6）配送单中包含查询号可唯一识别配送单，配送人，联系方式；
（7）仓库中仓库编号可唯一识别仓库，其中每个仓库都有区号，代表其地址。
（8）各实体间关系
1）一个客户可以购买多种商品，一种商品可以被多个客户购买；
2）一个商品属于且仅属于一种类别，一种类别的商品可以包含多个商品或没有；
3）一种商品放在多个商店中销售，一个商店至少销售一种或销售多种商品；
4）一个订单对应一个客户，一个客户对应多个订单；
5）一个订单对应至少有一件商品或多件，一个商品对应多个订单；
6）一个订单可以有一个商品配送单
7）一个仓库可以存放多种商品，一种商品可以存放在一个仓库；
2、数据库逻辑设计：
用文字简要描述实体之间的联系，画出E-R图（标出各联系中实体的基数）。
客户-商品：
n-n;
商品-类别：
n-1；
商品-商店：
n-n;
客户-订单：
1-n;
订单-商品：
1-n;
订单-配送当：
1-1;
仓库-商品：
1-n
3、数据库物理设计：
将E-R图转换为数据表。
需要给出详细的转换规则，对应生成的表，属性（主属性、描述性属性，多值属性，符合属性等），主键，外键，约束（取值是否NULL等），索引（三种类型的索引至少每种出现一次）等。
在客户，配送单，类别中建立索引，语句在4题中
如3中，有2个n-n关系，分生成订购表和销售表
4、SQL中创建语句的使用：
根据第二步中的结果，将相应的表，属性，主键，外键，约束等使用标准的SQLCREATE语句实现。
（要求给出创建的标准语句，以及创建之后在SQL数据库中的脚本和截图）。
createtable客户
（
客户编号char（5）notnullunique,
姓名varchar（10）,
密码varchar（15）,
电话numeric（11）,
地址varchar（20）,
卡号char（19）,
constraintPK_客户primarykey（客户编号）
）;
createtable类别
（
类别编号char（5）notnullunique,
类别名称varchar（10）,
制造商varchar（10）,
constraintPK_类别primarykey（类别编号）
）;
createuniqueindex类别_PKon类别（
类别编号ASC
）;
createtable仓库
（
仓库编号char（5）notnullunique,
仓库区号varchar（5）,
constraintPK_仓库primarykey（仓库编号）
）;
createtable商品
（
商品编号char（5）notnullunique,
仓库编号char（5）notnull,
类别编号char（5）notnull,
商品名称varchar（50）,
价格float,
constraintPK_商品primarykey（商品编号）,
constraintfk_仓库编号foreignkey（仓库编号）references仓库,
constraintfk_类别编号foreignkey（类别编号）references类别
）;
createuniqueclusteredindex客户_PKon客户（
客户编号ASC
）;
createtable订单
（
订单编号char（5）notnull,
客户编号char（5）,
订购时间varchar（10）,
支付方式varchar（10）,
订单状态numeric,
总额float,
constraintPK_订单primarykey（订单编号）,
constraintfk_客户编号foreignkey（客户编号）references客户
）;
createtable配送单
（
查询号char（10）notnullunique,
订单编号char（5）notnull,
配送人varchar（10）,
联系电话numeric（11）,
constraintPK_配送单primarykey（查询号）,
constraintfk_订单编号foreignkey（订单编号）references订单
）;
createclusteredindex配送_FKon配送单（
订单编号ASC
）;
createuniqueindex配送单_PKon配送单（
查询号ASC
）;
createtable商店
（
商店号char（5）notnullunique,
商店名char（10）,
地址varchar（20）,
constraintPK_商店primarykey（商店号）
）;
createtable销售
（
商店号char（5）notnull,
商品编号char（5）notnull,
库存量int,
constraintPK_销售primarykey（商店号,商品编号）
）;
createtable订购
（
订单编号char（5）notnull,
商品编号char（5）notnull,
数量int,
金额float,
constraintPK_订购primarykey（订单编号,商品编号）
）;
5、存储过程、触发器和视图：
根据需要给数据库添加至少六个实用的存储过程、触发器和视图，并说明它们各自的功能。
（需要给出语句执行的结果示意图）
（1）创建视图，查找商品名为'苹果'的商品
createviewfind_goods
as
select商品编号,商品名称,价格
from商品
where商品名称like'苹果%'
（2）创建视图，查找价格在3000-6000的商品
createviewprice
as
select商品编号,商品名称
from商品
where价格>3000and价格<6000
（3）创建触发器，提醒店铺中库存量小于10的商品
createtriggertrigger_alarm
on销售
afterupdate
as
if（select库存量from销售where库存量<10）<10
print'triggerout:
'
select商品编号,库存量from销售where库存量<10
return
（4）创建触发器，当删除配送单中的数据时显示出所删信息
createtriggertrigger_del
on配送单
afterdelete
as
print'delete'
（5）创建存储过程，根据商品编号，查询该商品的订购量
createprocsp_find_quantity
@商品编号char（5）,
@sumqintoutput
as
select商品编号,sum（数量）
from订购
groupby商品编号
having商品编号=@商品编号
declare@sumqint
execsp_find_quantity
@商品编号='s0001',
@sumq=@sumqoutput
print'thesumquantityis:
'+
convert（varchar（5）,@sumq
（6）创建存储过程，通过商品名称寻找商品信息
createprocsp_find_price
@namechar（10）
as
select*
from商品
where商品名称like@name+'%'
execsp_find_price
@name='戴尔
6、分析常见的业务流程，列举出至少五种SQLSELECT语句。
实现的语句要满足如下要求：
a）在五种语句中的查询能反映正常的业务需求；
b）分析中至少要分别出现一次ORDERBY、GROUPBY…HAVING子句；
c）分析中至少使用一次聚集函数；
d）分析中至少使用一次嵌套查询；
e）分析中至少使用一次UNION或INTERSECT运算；
给出每一种语句执行的结果。
（1）计算并查找订购单中相同订单订购超过5000的总额
select订单编号,sum（金额）as总额
from订购
groupby订单编号
havingsum（金额）>5000
（2）查找即购买了商品编号为s0001又买了s0002的客户
select姓名
from客户b,订单o,订购s
wheres.商品编号='s0001'andb.客户编号=o.客户编号ando.订单编号=s.订单编号
intersect
select姓名
from客户b,订单o,订购s
wheres.商品编号='s0002'andb.客户编号=o.客户编号ando.订单编号=s.订单编号
（3）按照顾客c0002消费的总额大小顺序排订单编号
select订单编号,总额
from订单
where客户编号='c0001'
orderby总额desc
（4）查询订单编号为00003的配送信息
selecto.订单编号,配送人,联系电话
from订单o,配送单s
whereo.订单编号=s.订单编号ands.订单编号='00003'
（5）查找同样买了商品编号为s0002的客户信息
select客户编号,姓名,电话
from客户
where客户编号
in
（select客户编号
from订单，订购
where订单.订单编号=订购.订单编号and订购.商品编号='s0002'）
（6）查询当日销售总额
select订购时间,sum（总额）as营业额
from订单
groupby订购时间
7、（选做）完成数据库的设计之后，根据自己所熟悉的编程语言（C、C++、JAVA），实现一个简单的程序，能够在程序中组装SELECT语句，链接创建的数据库，进行查询，并显示查询结果。
若完成，请提供详细的代码清单（代码作为附件，附在本报告的最后）。
用JSP页面编写
实现如下：
8、实验小结。
在本次实验中一共建立了9个表，完成了6条Select语句，6条关于视图、触发器和存储过程，在语句实现过程中，因为涉及内容比较广，差不多把数据库语句全部复习了一遍才开始做，所花的时间较长，但也收益颇丰，对数据库的设计和实现也有了总体了解。
最后在做选做时，选择了通过网页的方式实现，因为在做专业实训时也用了JavaEE这项技术，所以实现起来颇为得心应手。
附件
packagecom.ynu.myBusiness.db;
importjava.sql.Connection;
importjava.sql.SQLException;
importjava.sql.ResultSet;
importjava.sql.Statement;
importcom.devdaily.opensource.database.DDConnectionBroker;
publicclassConnectionBrokerimplementsBuildConnection{
privateStringdriver=null;
privateStringurl=null;
privateStringusername=null;
privateStringpassword=null;
privateintminConnections=0;
privateintmaxConnections=0;
privatelongtimeout=0;
privatelongleaseTime=0；
privateStringlogFile=null;
privateDDConnectionBrokerbroker=null;
voidsetUp（）{
//驱动包
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
//连接到数据库business
url="jdbc:
sqlserver:
//127.0.0.1:
1433;DatabaseName=business";
username="sa";
password="yym";
minConnections=3;
maxConnections=6;
timeout=100;
leaseTime=60000;
//日志文件存储位置
logFile="C:
/D/DDConnectionBroker.log";
broker=null;
}
publicConnectiongetConnection（）throwsSQLException{
try{
//construct（建立）thebroker（中间人）
broker=newDDConnectionBroker（driver,url,username,password,
minConnections,maxConnections,timeout,leaseTime,logFile）;
}
catch（SQLExceptionse）{
//couldnotgetabroker;notmuchreasontogoon
System.out.println（se.getMessage（））;
System.out.println（"Couldnotconstructabroker,quitting."）;
}
//建立broker成功
returnbroker.getConnection（）;
}
publicvoidfreeConnection（Connectionconn）throwsSQLException{
try{
broker.freeConnection（conn）;
}
catch（Exceptione）{
System.out.println（"ThrewanexceptiontryingtofreemyConnection:
"+e.getMessage（））;
}
}
publicintgetNumberConnections（）throwsSQLException{
if（broker!
=null）
returnbroker.getNumberConnections（）;
else
return-1;
}
publicConnectionBroker（）{
super（）;
setUp（）;
}
}
<%@pagelanguage="java"contentType="text/html;charset=GB2312"
pageEncoding="GB2312"%>
<%@pageimport="com.ynu.myBusiness.db.*,java.sql.Connection,java.sql.PreparedStatement,java.sql.ResultSet,java.sql.Statement"%>
DOCTYPEhtmlPUBLIC"-//W3C//DTDHTML4.01Transitional//EN""http:
//www.w3.org/TR/html4/loose.dtd">
Inserttitlehere数据库实验大作业
搜索配送单中的数据
<%
//从工厂中得到连接
ConnectionFactoryfactory=ConnectionFactory.getInstatnce（）;
Connectionconnection=factory.getConnection（）;
//执行语句
PreparedStatementpstatement=null;
//结果集
ResultSetrs=null;
pstatement=connection.prepareStatement（"select*from配送单"）;
//执行
rs=pstatement.executeQuery（）;
%>
| 查询号 | 订单编号 | 配送人 |
|---|
<%
while（rs.next（））{
out.println（"
"）;out.println（"
"+rs.getString （1）+" | "）;out.println（"
"+rs.getString （2）+" | "）;out.println（"
"+rs.getString（3）+" | "）;out.println（"
"）;
}
%>
<%//显示完毕，释放资源
try{
if（rs!
=null）
rs.close（）;
if（pstatement!
=null）
pstatement.close（）;
if（factory!
=null）
factory.freeConnection（connection）;
}catch（Exceptione）{
//TODOAuto-generatedcatchblock
e.printStackTrace（）;
}
%>
升级会员 