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

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

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

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

4、应多个订单；6) 一个订单可以有一个商品配送单7) 一个仓库可以存放多种商品，一种商品可以存放在一个仓库；皂运istz. 姓府一 SM客户弱号8C.5疣空号 5一 心式二七人LEhtnE0工心茗苫茬；：6 /=二nu工: E，餐子运纪VttTL±bl ”hkKi*+7 ,.10）商苫三仁心x>萍松°2、数据库逻辑设计：用文字简要描述实体之间的联系，画出 E-R图（标出各联系中实体的基数）害户zzs-atl e zjz.arazxEZ,S I上J iLUz-A£1&i>£. (15)aber (11)Vnriablt EuraEttrs

5、. (ZO) CbK-BCt&TE t 刈客户-商品：n-n;商品-类别：n-1;商品-商店：n-n;客户-订单：1-n;订单-商品：1-n;订单-配送当：1-1;仓库-商品：1-n3、数据库物理设计：将E-R图转换为数据表。需要给出详细的转换规则，对应生成的表，属性（主属性、描述性属性，多值属性，符合属性等）,主键，外键，约束（取值是否NULL等），索引（三种类型的索引至少每种出现一次）等。4A二工工'RicCl 1)vu ；hu '' 改二-吟三;_* *!西电地中我帛老可7ir:k*r(lC "洋壹 vir=larilO：.套.吉支古号B二皿；袁

6、启魔苫W Vi zhiy ：选七 vaifikur .2 Z'）去£亏rv- 堆.产寓购付华惠 i-F宣S灯总(10Ja±ihir (3)匚2MH 1 =fl«t配翅盆美任三_:丁k，订工编写vir '<£< J忑运人vir:h*jf'：）立屁/送二过在客户，配送单，类别中建立索引，语句在 4题中如3中，有2个n-n关系，分生成订购表和销售表4、 SQL 中创建语句的使用：根据第二步中的结果，将相应的表，属性，主键，外键，约束等使用标准的 SQL CREATE 语句实现。(要求给出创建的标准语句，以及创建之后在SQL

7、数据库中的脚本和截图)。create table 客户(客户编号char(5) not null unique,姓名varchar(10),密码varchar(15),电话numeric(11),地址varchar(20),卡号char(19),constraint PK_ 客户 primary key (客户编号);create table 类别(类别编号char(5) not null unique,类别名称varchar(10),制造商varchar(10) ,constraint PK_ 类别 primary key (类别编号);create unique index 类别 _PK

8、on 类别 (类别编号ASC);create table 仓库(仓库编号char(5) not null unique,仓库区号varchar(5),constraint PK_ 仓库 primary key (仓库编号);create table 商品商品编号char(5) not null unique,仓库编号char(5) not null,类别编号char(5) not null,商品名称varchar(50),价格 float,constraint PK_ 商品 primary key (商品编号),constraint fk_ 仓库编号 foreign key(仓库编号 )ref

9、erences 仓库,constraint fk_ 类别编号 foreign key(类别编号'references 类别);create unique clustered index 客户 _PK on 客户 (客户编号ASC);create table 订单(订单编号char(5) not null,客户编号char(5),订购时间varchar(10),支付方式varchar(10),订单状态numeric,总额 float ,constraint PK_ 订单 primary key (订单编号 ),constraint fk_ 客户编号 foreign key(客户编号,re

10、ferences 客户);create table 配送单(查询号 char(10) not null unique,订单编号char(5) not null,配送人 varchar(10),联系电话numeric(11),constraint PK_ 配送单 primary key (查询号), constraint fk_ 订单编号 foreign key(订单编号 )references 订单);create clustered index 配送 _FK on 配送单 (订单编号ASC);create unique index 配送单 _PK on 配送单 (查询号 ASC);creat

11、e 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,constraint PK_订购 pr

12、imary key (订单编号,商品编号)；国口率毓表±) n dba仓库E二I dba订单±)二dbd订购t J3 dbo.每户S J dba类别3 3 dba配送单±.二 db。,商店S n db。,商品 ± dbd镇警 1.h5、存储过程、触发器和视图：根据需要给数据库添加至少六个实用的存储过程、触发器和视图,并说明它们各自的功能。(需要给出语句执行的结果示意图)创建视图，查找商品名为苹果的商品create view find_goodsasselect商品编号，商品名称，价格from商品where商品名称like '苹果' * &

13、#187; . p * 一«-» )!» l j1 r3 create view find_gcQd3 iB3el品亡七 商品编号,商品名称,价格fmm商品Lwhere商品名称工工让.苹果3消息命令已成功完成。+1 n题题图A 区 dbo.findgoods 日口列S)商品聚号(charCS), not null) 国商品名称(varchjr(50), null) 国价格(float, null)(2)创建视图，查找价格在3000-6000的商品create view price as select商品编号，商品名称 from商品 where 价格 >300

14、0 and 价格 <6000|日 create view priceasselect商品编号,商品名称from商品Lwhere 价格>3。0。 and 价格<6QQO1)消息命令已成功完成。dboprice直日口列国 商品遍号(char(5), not null)H 商品名称(varchair(5O), null)(3)创建触发器，提醒店铺中库存量小于10的商品create trigger trigger_alarmon销售after updateasif(select 库存量 from 销售 where 库存量 <10)<10print 'trigger

15、 out:'select商品编号，库存量from 销售 where库存量<10 returnSQLQueryS.sql - YA.s (yanyanpc (53)*触发器sql =未由9 SQLQuEiftselect 库存量f工皿 销售库存量COYl。 -print 1 trigger out:1select商品褊号,库存量from销售where库存量10L return绪果命令已成功完成。日update稍售 set库存量XLwhes商品编号-国结果|。丽商品编骂库存里UlUI-ll-：l_IMMIUlUI-IUUIb1 I sOM1 | 2 %IBIialBlfallB AI

16、BI iui bikimI(4)创建触发器，当删除配送单中的数据时显示出所删信息create trigger trigger_delon配送单after deleteasprint 'delete'SQLQuery4.sql - YA.s (yanyanpc (54)* 5( create trigger triqger_d.elcm配送单after deleteASprint 1 delete'-select f rcio deleteiJ消息命令已成功完成。日delE七日 配送单Lwhere查徇号=，工810，盅结果育福查询号订单编号配送人联系电话1 IfOoid

17、1 00011 泰信ibikiibiuibiuibiuibI(5)创建存储过程，根据商品编号，查询该商品的订购量create proc sp_find_quantity商品编号char(5),sumq int outputasselect商品编号，sum(数量)from订购group by商品编号having商品编号=商品编号 declare sumq intexec sp_find_quantity 商品编号='s0001',sumq=sumq output print 'the sum quantity is:'+ convert(

18、varchar(5),sumqVAN¥AN.bu5ine55 dbd商品 SQLQuery4.sql 臼 declare 口 int©商品编号=T。工-03umq=suir.q outputA print * the 5uic quantity 工耳L convertvarchar 5 ,g月umq)二I结果消息商品编号优列名)1 r'sbobi 4(6)创建存储过程，通过商品名称寻找商品信息create proc sp_find_pricename char(10)asselect *from商品where 商品名称 like name + '%'

19、exec sp_find_pricename='戴尔n结果出消息1商品编号仓库编号类别编号商品名称价格:s(XK)3a(K)C3I0003戴尔(DELL)kis14L-2523B3400250009a0003I0003戴尔(DELL)hsl50Rl44SS5990330010a0003I0003戴尔(DELL)lns14UR 152SRR41006、分析常见的业务流程，列举出至少五种SQL SELECT语句。实现的语句要满足如下要求:a) 在五种语句中的查询能反映正常的业务需求；b) 分析中至少要分别出现一次ORDER BY、GROUP BYHAVING 子句；c) 分析中至少使用一次

20、聚集函数；d) 分析中至少使用一次嵌套查询；e) 分析中至少使用一次 UNION或INTERSECT运算；给出每一种语句执行的结果。(1)计算并查找订购单中相同订单订购超过5000的总额select订单编号，sum(金额)as总额from订购group by订单编号 having sum(金额)>5000SQLQueryl.sql - YA.S (yanyanpc (54)* elecc订单编号”uml金额且白豆凝mm订购roup by 订单编号L aving sw 金颔> >5。|nr口结果：消息 订单编号总额piBii-irainriiBii-troieii-iiBin1

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

22、编号 and。.订单编号=s.订单编号SQLQueryl.Eql - VA-s (yanyanpc (54)* select 姓名Emm客户b,订单露订购swhere m.商品编自h aOOOl' and “客户编号y.客户编号 and 0.订单编号F.订单编号 intersecc select. 姓名from客户0订单d订购sLwheru曰商品编号30002 R and 七客户编号=。客户编号 and Ox订单编号7 .订单编号T L"1I日结果4消息姓名1 :房灵j(3)按照顾客c0002消费的总额大小顺序排订单编号select订单编号，总额 from订单where 客户

23、编号='c0001'order by 总额 desc_ YANYAN.business - dba订单 SQLQUfiryl Fs selec?fem订单where客巧编号口口“-order by，总、额 desc0结果心消息订单编号总额jisimemiirm mriim mi nn1 j 00004 j 及 3002000021220030000878004000113400(4)查询订单编号为 00003的配送信息select。.订单编号，配送人，联系电话 from 订单 o,配送单 s where。.订单编号=s.订单编号 and s订单编号='00003'

24、;LI一 'YANYAN.business - dboS丁单 SQLQueryLsql - YA_s (yanyanpc (54)T select 口.外革辑与配送人联系电话from订单d配送单aI-where O .订单编号订单编号d4订单编号。口。3r*in目结果7感痴订单编号配缸联系电话1 rS»D3 5光信 1128373832查找同样买了商品编号为 s0002的客户信息select客户编号，姓名，电话from客户where客户编号in(select客户编号 from 订单，订购where订单.订单编号=订购.订单编号 and订购.商品编号='s0002

25、9;）日巴史工曰5客户编号,姓名,电话from客户where客户编号select客户编号from订单，仃购-wnere 订单.订单编号,丁购.订单编号and订购.商品编号=匕。21客户编号姓名电话1 f cCWI房灵 1533需&91832 %旃 ijft 18293a32332（6）查询当日销售总额select订购时间，sum（总额）as营业额from订单group by订购时间 h * rI134曰3订购时间1师（总额I as营业额 from订单by订购时间|S结果消息订购时间营北额 £（ B4M1 j 201477/11 | 37100 hainraiHinramMiB

26、iBiHinMiHH'2 2014/7/12 736303 2014/71334007、（选做）完成数据库的设计之后，根据自己所熟悉的编程语言（ C、C+、JAVA）,实现一个简单的程序，能够在程序中组装SELECT语句，链接创建的数据库，进行查询，并显示查询结果。若完成，请提供详细的代码清单（代码作为附件，附在本报告的最后）。用JSP页面编写 实现如下:Z business& JAX-WS Web Service?la Deployment Descriptor： busj 陋 Java Resourcesj 四 src一田 co im.yn j. my Business.(

27、|J BuildConnectionJ;|j- CcnnecticnBroker|jj ConnecticnFactor)咄 Libraries就 JavaScript Resources|> & build三J B WebContenti> 6 META-1NF0 & WEB-INF busines5.jspJ 聚 business充 JAX-WS Web Servicesil Deployment Descriptor： bus j / Java Resources/0 src& 出 com.ynu.my Busin ess.( |J| BuildCon

28、necticn.j5 j CcnnectionBroker |j ConnecticnFactor) W LibrariesBl JaviaScript Resources> & buildJ © WebContenti> B META-INF0 & WEB-INF . bj5ines5.jsp8、实验小结。在本次实验中一共建立了 9个表，完成了 6条Select语句，6条关于视图、触发器和存储过程, 在语句实现过程中，因为涉及内容比较广，差不多把数据库语句全部复习了一遍才开始做，所花的 时间较长，但也收益颇丰，对数据库的设计和实现也有了总体了解。最后在做

29、选做时，选择了通过 网页的方式实现，因为在做专业实训时也用了Java EE这项技术，所以实现起来颇为得心应手。附件package com.ynu.myBusiness.db;import java.sql.Connection;import java.sql.SQLException;import java.sql.ResultSet;import java.sql.Statement;import com.devdaily.opensource.database.DDConnectionBroker;public class ConnectionBroker implements Build

30、Connection private String driver = null;private String url = null;private String username = null;private String password = null;private int minConnections = 0;private int maxConnections = 0;private long timeout = 0;private long leaseTime = 0；private String logFile = null;private DDConnectionBroker b

31、roker = null;void setUp() / 驱动包driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"/ 连接到数据库businessurl = "jdbc:sqlserver:/:1433;DatabaseName=business"username = "sa"password = "yym"minConnections = 3;maxConnections = 6;timeout = 100;leaseTime = 600

32、00;logFile = "C:/D/DDConnectionBroker.log"broker = null;/ 日志文件存储位置public Connection getConnection() throws SQLException try / construct (建立)the broker (中间人)broker = new DDConnectionBroker(driver, url, username, password, minConnections, maxConnections, timeout, leaseTime, logFile);catch (S

33、QLException se) / could not get a broker; not much reason to go onSystem.out.println(se.getMessage();System.out.println("Could not construct a broker, quitting.");/ 建立 broker 成功return broker.getConnection();public void freeConnection(Connection conn) throws SQLException try broker.freeConn

34、ection(conn);catch (Exception e) System.out.println("Threw an exception trying to free my Connection: "+ e.getMessage();public int getNumberConnections() throws SQLException if (broker != null)return broker.getNumberConnections();elsereturn -1;public ConnectionBroker() super();setUp();<

35、% page language="java" contentType="text/html; charset=GB2312" pageEncoding="GB2312"%><%pageimport="com.ynu.myBusiness.db.*,java.sql.Connection,java.sql.PreparedStatement,java.s ql.ResultSet,java.sql.Statement" %><!DOCTYPE html PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN""/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content=&qu