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

1、实用标准云南大学软件学院实验报告课程：数据库原理与实用技术实验学期:任课教师：专业：学号：姓名：成绩：期末大作业：ElectronicVentor数据库设计、实验目的(1)掌握数据库设计的基本方法(2)掌握各种数据库对象的设计方法(3)熟练掌握DBA必须具备的技能、实验内容1、根据项目的应用和项目的需求说明文档，进行详细的需求分析，给出需求分析的结果。(1)客户可以在网站上注册，注册的客户要提供客户的姓名、电话、地址，以方便售后和联系，姓名即作为用户名，和密码一起用于注册和登录，客户编号可唯一识别用户，卡号可网上支付。其中地址、电话以方便联系和寄货；(2)网站管理员可以登记各种商品，供客户查询

2、，订购。登记商品时要提供商品的名称、价格，商店中现有商品量，商品编号可唯一识别商品；(3)类别表示商品所属类别，类别编号可唯一识别类别，其中包含了，商品类别名称和制造厂商，可以对商品进行分类售卖；(4)客户可以在网上下订单，也可以到实体店购物，其在订单上所选择的支付方式不同(信用卡、借记卡、现金，现金代表实体店购物)，网站管理员可以查看订单，并及时将订单的处理情况更新(比如货物已寄出的信息，订单状态：0:未处理，1:已处理,2:已发货)；订单编号可唯一识别订单，订单中包含订单产生时间，订单状态，支付方式和支付总额；(5)实体商店有自己的店名，卖多种商品，每个商店都有固定的地址，顾客可以到店中买

3、商品，(注：在实体店中购买商品的顾客一律将顾客名默认为佚名)，当商店中的库存量小于10时会有提醒到仓库中拿货；(6)配送单中包含查询号可唯一识别配送单，配送人，联系方式；(7)仓库中仓库编号可唯一识别仓库，其中每个仓库都有区号，代表其地址。(8)各实体向关系1) 一个客户可以购买多种商品，一种商品可以被多个客户购买；2) 一个商品属于且仅属于一种类别，一种类别的商品可以包含多个商品或没有；3) 一种商品放在多个商店中销售，一个商店至少销售一种或销售多种商品；4) 一个订单对应一个客户，一个客户对应多个订单；5) 一个订单对应至少有一件商品或多件，一个商品对应多个订单；6) 一个订单可以有一个商

4、品配送单7) 一个仓库可以存放多种商品，一种商品可以存放在一个仓库；2、数据库逻辑设计：用文字简要描述实体之间的联系，画出 E-R图（标出各联系中实体的基数）Ihruble dunctex (5).,±± iatLi tn=a±tt±: (.15)(11)V*x i al 11(ZQ)5玛话让号 空笆里地卡醴副黄亘翼号TunaKlE才曰二七型山咬氏更称Varxatleehiraiteri制遗硬Vuriablechuactefs务五庭号<ri> 必(10)U0)三吉三 J ；= i =： =二三=31二二二h 二 厘 <JLW至星X二正1

5、=二二工二5J是匕'ajri4hl= -Im- 2CTi三烹h.w 善时方龈 单ftf+jL幅 H 即支1T.&>O订住客户-商品：n-n;商品-类别：n-1;商品-商店：n-n;客户-订单：1-n;订单-商品：1-n;订单-配送当：1-1;仓库-商品：1-n文案大全3、数据库物理设计：将E-R图转换为数据表。需要给出详细的转换规则，对应生成的表，属性（主属性、描述性属性，多值属性，符合属性等），主键，外键，约束（取值是否NULL等），索引（三种类型的索引至少每种出现一次）等。置盍言千二7ir:Ur(K'nnkar T73Z7 zkar '. 1 . mw

6、cri g (11J v*r ;uz ' H ', ULU 2Zj“-二七三七三电摩专vurhir(10)甄吉比古与,工出工：二,童工：堂古名；_gg叮近与 YBJfdtMf营三编号 YareJm ：5J虻量id t eer金北£ort7=12二上二" =1址七vu±ku.2jj*.户询购12地盘工事IWC支灯总yk>va-shari5)<£kl>shjjr(lOJ<£k2>tix-astiUif"3±*£ir(3)nrXf-z-i二ilvit?5)vvrchar

7、9;10)float在客户，配送单，类别中建立索引，语句在4题中如3中，有2个n-n关系，分生成订购表和销售表4、SQL中创建语句的使用：根据第二步中的结果，将相应的表，属性，主键，外键，约束等使用标准的SQLCREAT皤句实现。(要求给出创建的标准语句，以及创建之后在SQL数据库中的脚本和截图)。createtable客户(客户编号char(5)notnullunique,姓名varchar(10),密码varchar(15),电话numeric(11),地址varchar(20),卡号char(19),constraintPK_客户primarykey(客户编号)；createtable类

8、别(类别编号char(5)notnullunique,类别名称varchar(10),制造商varchar(10),constraintPK_类别primarykey(类别编号);createuniqueindex类别_PKon类另1J(类别编号ASC);createtable仓库(仓库编号char(5)notnullunique,仓库区号varchar(5),constraintPK_仓库primarykey(仓库编号);createtable商品(商品编号char(5)notnullunique,仓库编号char(5)notnull,类别编号char(5)notnull,商品名称varch

9、ar(50),价格float,constraintPK_商品primarykey(商口口编）,constraintfk_仓库foreignkey(仓库)references仓库constraintfk_类另1J编号foreignkey(类别编号）references类别)；createuniqueclusteredindex客户_PKon客户(客户编号ASC)；createtable订单(订单编号char(5)notnull,客户编号char(5),订购时间varchar(10),支付方式varchar(10),订单状态numeric,总额float,constraintPK_订单primar

10、ykey(订单编号),constraintfk_客户编号foreignkey(客户编号)references客户);createtable配送单(查询号char(10)notnullunique,订单编号char(5)notnull,配送人varchar(10),联系电话numeric(11),constraintPK_配送单primarykey(查询号),constraint fk订单编号 foreign key(订单编号 )references订单）；create clustered index配送_FK on配送单（订单编号ASC）；create unique index配送单_PK o

11、n配送单（查询号ASC）；create table 商店商店号 char(5) not null unique,商店名char(10),地址 varchar(20),constraint PK商店 primary key ( 商店号);create table 销售商店号 char(5) not null,商品编号 char(5) not null,库存量int,constraint PK_ 销售primary key（ 商店号，商品编号）);create table订购订单编号char(5) not null,商品编号char(5) not null,数量int,金额float,constr

12、aintPK_订购primarykey(订单编号,商品编号)；1=19U国二条就表.±)dba仓库E口dba订单±)二dbd订题tndbo.客户SJdba类别Sndba配送单±二db。,商店S口db。商品±dbd镇警1Ih5、存储过程、触发器和视图：根据需要给数据库添加至少六个实用的存储过程、触发器和视图,并说明它们各自的功能。(需要给出语句执行的结果示意图)(1)创建视图，查找商品名为苹果的商品createviewfind_goodsasselect商品编号，商品名称，价格from商品where商品名称like'苹果'日createvi

13、ewfindgocdsselect商品编号,商品名称,价格fmm商品LtttEC商品名称1上侬.苹果可工消息命令已成功完成。E视图+12的题图A国dbo-findgoods日口列国商品聚号(charCS),notnull)国商品名称(varchjr(50),null)圉价格(float,null)(2)创建视图，查找价格在3000-6000的商品createviewpriceasselect商品编号，商品名称from商品where价格>3000and价格<6000日createviewpriceasselect商品鼎号,商品名称fsm商品Lwhere价格>3000and价格4

14、5口00Ld消息命令已成功竞成7sE®日口列国商品兼号(char(5),notnull)H商品名称(varchair(50),null)(3)创建触发器，提醒店铺中库存量小于10的商品createtriggertrigger_alarmon销售afterupdateasif(select库存量from销售where库存量<10)<10print'triggerout:'select商品编号，库存量from销售where库存量<10returnSQLQueryS.sql-YA.s(yanyanpc(58)*BMS*触发器sql-耒SQLQuIj.fBe

15、lect库存量frcrr,销售wn=re库存量二二，二二-print1triggerout:1select商品编号,库存量from销售where库存量口return命令已成功完成。日update销售set库存量xLwhes商品编号-®结果消息商品编号库存里UlUI-ll-：l_IMMIUlUI-IUUIb(4)创建触发器，当删除配送单中的数据时显示出所删信息createtriggertrigger_delon配送单afterdeleteasprint'delete'£QLQuery4$ql-YAtm(yanyanpc(54)*SCcreatetriggert

16、riggexdelgn配送单afterdeleteprint1delete,select-froirJ消息命令已成功完成立结果，消息查询号订单编号配送人联系电话臼dElE.配送单Lwhere查询号二工001口，IfOOlOj00011张gt;iuiibibI(5)创建存储过程，根据商品编号，查询该商品的订购量createprocsp_find_quantity颔品编号char(5),sumqintoutputasselect商品编号，sum(数量)from订购groupby商品编号having商品编号=M品编号declaresumqintexecsp_find_qua

17、ntity颔品编号='s0001',sumq=sumqoutputprint'thesumquantityis:'+convert(varchar(5),sumqVAN¥AN.bu5ine55dbd商品SQLQuery4.sql臼declareamsqint商品编号=七。工-0suirrq=0suir.qoutputAprint*the5uicqrianrity工耳Lconvertvarchar5,g月umq)国结果Was商副扁号阮列匐1sbdoi4(6)创建存储过程，通过商品名称寻找商品信息createprocsp_find_pricenamecha

18、r(10)asselect*from商品where商品名称likename+'%'execsp_find_price五种SQL SELEC9句。实现的语句要满足如下要求:name姿尔a)在五种语句中的查询能反映正常的业务需求;1:s(XK)3iaW03I00C3戴尔(DELL)kis14L-2523B3400250009a0003I0003戴尔(DELL)hs150Rl443S5990330010a0003I0003戴尔(DELL)lns14UM52SRR4100口结果上消息商品编号仓库编号类别编号商品名称价格6、分析常见的业务流程，列举出至少b)分析中至少要分别出现一次ORD

19、ERBYGROUPBY-HAVING子句;c)分析中至少使用一次聚集函数;d)分析中至少使用一次嵌套查询;e)分析中至少使用一次UNIONSINTERSECT!算;给出每一种语句执行的结果。(1)计算并查找订购单中相同订单订购超过5000的总额select订单编号，sum(金额)as总额from订购groupby订单编号havingsum(金额)>5000SQLQueryLsql-YA_s(yanyanpc(54)*elecc订单编号”uml金额且白豆凝mm订购roupby订单编号Lavingsw金颔>>5。|口结果 J消息 订单编号总额piBii-irsinriieiHro

20、ieii-iiBin1 j 0DM1 | 21&00 J i-s r-1 s-i r, r r r_1 mm l i2000021220030000422SD040000685M5 ODDOS7sM6 0001021700(2)查找即购买了商品编号为S0001又买了S0002的客户select姓名from客户b,订单o,订购swheres.商品编号='s0001'andb.客户编号=o.客户编号ando.订单编号=s.订单编号intersectselect姓名from客户b,订单o,订购swhere s.商品编号='s0002' and b.客户编号=o

21、.客户编号ando.订单编号=s.订单编号SQLQueryl.Eql-VA-s(yanyanpc(54)*select姓名Emm客户上订单露订购swherem.商品编自haOOOl'and“客户编号y.客户编号and0.订单编号F.订单编号interseccselect.姓名from客户0订单d订购sLwheru曰商品编号30002Rand*客户编号=。客户编号andOx订单编号7.订单编号1|"I日结果4消息姓名1:房灵j(3)按照顾客c0002消费的总额大小顺序排订单编号select订单编号，总额from订单where客户编号='c0001'orderby

22、总额descYANYAN.busIness-dbm订睾SQLQuerylselect订单编号，总额from订单where客户编号="00。广orderby，苣额desc(4)查询订单编号为00003的配送信息口结果3消息selecto.订单编号，配送人，联系电话from订单o,配送单swhereo.订单编号=s.订单编号ands.订单编号='00003'YANYAEbusiness-dbo,订单订单编号配M联系电话 OTOOa j 光信 1128373832查找同样买了商品编号为s0002的客户信息select客户编号，姓名，电话from客户where客户编号in(s

23、elect 客户编号from订单，订购订购时间营北额 BIUIBIIMIBIMM £（ MMIBIBill1 j 201477/11 | 37100 hainraiHinramMiBiBiHinMiHH'22014/7/127363032014/71334007、（选做）完成数据库的设计之后，根据自己所熟悉的编程语言（C、C+卡JAVA）,实现一个简单的程序，能够在程序中组装SELECT语句，链接创建的数据库，进行查询，并显示查询结果。若完成，请提供详细的代码清单（代码作为附件，附在本报告的最后）。用JSP页面编写 实现如下:SQLQueryLsql-YAs(yanyanpc

24、(54)*"luud打箪襄与鲍送人，联系电话from订单d配送单3Where口.订单编号=s.订单编号d巾订单编号S结果应消息where订单.订单编号=订购.订单编号and订购.商品编号='s0002'）日巴史工曰5客户编号,姓名,电话from客户where客户编号select客户编号from订单，仃购-wnere订单.订单编号,丁购.订单编号and订购.商品编号=匕。21口结果J消息客户编号姓名电话1 fcCWI房灵1533需&91832 %旃ijft18293a32332（6）查询当日销售总额select订购时间，sum（总额）as营业额from订单gro

25、upby订购时间h*rI134曰3订购时间1师（总额Ias营业额from订单by订购时间|S结果消息Zbusiness&JAX-WSWebService?IjDeploymentDescriptor：busj陋JavaResourcesj四srcJ舟coim.ynj.myBusiness.(7 BuildConnectionJ;|j-CcnnecticnBroker|jjConnecticnFactor)咄Libraries迷JavaScriptResources>&build三JBWebContenti>6META-1NF0&WEB-INF-.ibusin

26、es5.jspJ聚business&JAX-WSWebServicesijDeploymentDescriptor：bus一画JavaResources上国src/出com.ynu.myBusiness.(jBuildConnecticn.j5jCcnnectionBroker|jConnecticnFactor)赧Libraries或JaviaScriptResources>&buildj©WebContenti>BMETA-INF0&WEB-INFbj5ines5.jsp8、实验小结。在本次实验中一共建立了9个表，完成了6条Select语句，6

27、条关于视图、触发器和存储过程,在语句实现过程中，因为涉及内容比较广，差不多把数据库语句全部复习了一遍才开始做，所花的时间较长，但也收益颇丰，对数据库的设计和实现也有了总体了解。最后在做选做时，选择了通过网页的方式实现，因为在做专业实训时也用了JavaEE这项技术，所以实现起来颇为得心应手。附件packagecom.ynu.myBusiness.db;importjava.sql.Connection;importjava.sql.SQLException;importjava.sql.ResultSet;importjava.sql.Statement;importcom.devdaily.o

28、pensource.database.DDConnectionBroker;publicclassConnectionBrokerimplementsBuildConnectionprivateStringdriver=null;privateStringurl=null;privateStringusername=null;privateStringpassword=null;privateintminConnections=0;privateintmaxConnections=0;privatelongtimeout=0;privatelongleaseTime=0；privateStri

29、nglogFile=null;privateDDConnectionBrokerbroker=null;voidsetUp()/驱动包driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"/连接到数据库businessurl="jdbc:sqlserver::1433;DatabaseName=business"username="sa"password="yym"minConnections=3;maxConnections=6;timeout

30、=100;leaseTime=60000;/日志文件存储位置logFile="C:/D/DDConnectionBroker.log"broker=null;publicConnectiongetConnection()throwsSQLExceptiontry/construct(建立)thebroker(中间人)broker=newDDConnectionBroker(driver,url,username,password,minConnections,maxConnections,timeout,leaseTime,logFile);catch(SQLExcepti

31、onse)/couldnotgetabroker;notmuchreasontogoonSystem.out.println(se.getMessage();System.out.println("Couldnotconstructabroker,quitting.");/建立broker成功returnbroker.getConnection();publicvoidfreeConnection(Connectionconn)throwsSQLExceptiontrybroker.freeConnection(conn);catch(Exceptione)System.o

32、ut.println("ThrewanexceptiontryingtofreemyConnection:"+e.getMessage();publicintgetNumberConnections()throwsSQLExceptionif(broker!=null)returnbroker.getNumberConnections();elsereturn-1;publicConnectionBroker()super();setUp();<%pagelanguage="java"contentType="text/html;char

33、set=GB2312"pageEncoding="GB2312"%><%pageimport="com.ynu.myBusiness.db.*,java.sql.Connection,java.sql.PreparedStatement,java.sql.ResultSet,java.sql.Statement"%><!DOCTYPEhtmlPUBLIC"-/W3C/DTDHTML4.01TransitionalEN”"/TR/html4/loose.dtd"><html><head><metahttp-equiv="Content-Type"content=&