云南大学数据库期末大作业数据库设计[共20页]

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）一个仓库可以存放多种商品，一种商品可以存放在一个仓库；2、 数据库 逻辑设计 ：用文字简要描述实体之间的联系，画出 E-R 图（标出各联系中实体的基数） 。客户-商品： n-n;商品-类别： n-1；商品-商店： n-n;客户-订单： 1-n;订单-商品： 1-n;订单-配送当： 1-1;仓库-商品： 1-n3、数据库 物理设计 ：将 E-R 图转换为数据表。 需要给出详细的转换规则， 对应生成的表， 属性（主属性、描述性属性，多值属性，符合属性等） ，主键，外键，约束（取值是否 NULL 等），索引（三种类型的索引至少每种出现一次）等。在客户，配送

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

6、编号 );create table 类别(类别编号 char(5) not null unique,类别名称 varchar(10),制造商 varchar(10) ,constraint PK_ 类别 primary key ( 类别编号 );create unique index 类别_PK on 类别 (类别编号 ASC);create table 仓库(仓库编号 char(5) not null unique,仓库区号 varchar(5),constraint PK_ 仓库 primary key ( 仓库编号 );create table 商品(商品编号 char(5) not n

7、ull unique,仓库编号 char(5) not null,类别编号 char(5) not null,商品名称 varchar(50),价格 float,constraint PK_ 商品 primary key ( 商品编号 ),constraint fk_ 仓库编号 foreign key( 仓库编号 ) references 仓库,constraint fk_ 类别编号 foreign key( 类别编号 )references 类别);create unique clustered index 客户_PK on 客户 (客户编号 ASC);create table 订单(订单编

8、号 char(5) not null,客户编号 char(5),订购时间 varchar(10),支付方式 varchar(10),订单状态 numeric,总额 float ,constraint PK_ 订单 primary key ( 订单编号 ),constraint fk_ 客户编号 foreign key( 客户编号 )references 客户);create table 配送单(查询号 char(10) not null unique,订单编号 char(5) not null,配送人 varchar(10),联系电话 numeric(11),constraint PK_ 配送

9、单 primary key ( 查询号 ),constraint fk_ 订单编号 foreign key( 订单编号 ) references 订单);create clustered index 配送_FK on 配送单 (订单编号 ASC);create unique index 配送单 _PK on 配送单 (查询号 ASC);create table 商店(商店号 char(5) not null unique,商店名 char(10),地址 varchar(20),constraint PK_ 商店 primary key ( 商店号 );create table 销售(商店号 c

10、har(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_ 订购 primary key ( 订单编号 , 商品编号 );5、存储过程、触发器和视图：根据需要给数据库添加 至少六个 实用的存储过程、触发器和视图，并说明它们各自的功能。 （需要给出语句执行的结果示意图）(1)创建视图，查找商品名为

11、苹果 的商品create view find_goodsasselect 商品编号 ,商品名称 ,价格from 商品where 商品名称 like 苹果%（2）创建视图，查找价格在 3000-6000 的商品create view priceasselect 商品编号 ,商品名称from 商品where 价格3000 and 价格6000(3)创建触发器，提醒店铺中库存量小于 10 的商品create trigger trigger_alarmon 销售after updateasif(select 库存量 from 销售 where 库存量 10)10print trigger out:se

12、lect 商品编号 ,库存量 from 销售 where 库存量 5000(2)查找即购买了商品编号为 s0001 又买了 s0002 的客户select 姓名from 客户 b,订单 o,订购 swhere s.商品编号 =s0001 and b.客户编号 =o.客户编号 and o.订单编号 =s.订单编号intersectselect 姓名from 客户 b,订单 o,订购 swhere s.商品编号 =s0002 and b.客户编号 =o.客户编号 and o.订单编号 =s.订单编号(3)按照顾客 c0002 消费的总额大小顺序排订单编号select 订单编号 ,总额from 订单

13、where 客户编号 =c0001order by 总额 desc(4)查询订单编号为 00003 的配送信息select o.订单编号 ,配送人 ,联系电话from 订单 o,配送单 swhere o.订单编号 =s.订单编号 and s.订单编号 =00003(5)查找同样买了商品编号为 s0002 的客户信息select 客户编号 ,姓名,电话from 客户where 客户编号in(select 客户编号from 订单，订购where 订单.订单编号 =订购.订单编号 and 订购.商品编号 =s0002)(6)查询当日销售总额select 订购时间 ,sum(总额) as 营业额fro

14、m 订单group by 订购时间7、（ 选做 ）完成数据库的设计之后，根据自己所熟悉的编程语言（ C、C+、JAVA ），实现一个简单的程序，能够在程序中组装 SELECT 语句，链接创建的数据库，进行查询，并显示查询结果。若完成，请提供详细的代码清单（代码作为附件，附在本报告的最后） 。用 JSP页面编写实现如下：8、实验小结。在本次实验中一共建立了 9 个表， 完成了 6 条 Select 语句，6 条关于视图、 触发器和存储过程，在语句实现过程中，因为涉及内容比较广，差不多把数据库语句全部复习了一遍才开始做，所花的时间较长，但也收益颇丰，对数据库的设计和实现也有了总体了解。最后在做选做

15、时，选择了通过网页的方式实现，因为在做专业实训时也用了 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 BuildC

16、onnection 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

17、roker = null;void setUp() / 驱动包driver = com.microsoft.sqlserver.jdbc.SQLServerDriver ;/ 连接到数据库 businessurl = jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=business;username = sa;password = yym;minConnections = 3;maxConnections = 6;timeout = 100;leaseTime = 60000;/ 日志文件存储位置logFile = C:/D/DDConnectionBr

18、oker.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 (SQLException 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.);