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

云南大学软件学院实验报告

课程：数据库原理与实用技术实验学期：任课教师：

专业：学号：姓名：成绩：

期末大作业：Electronic Ventor 数据库设计

一、实验目的

（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中创建语句的使用：根据第二步中的结果，将相应的表，属性，主键，外键，约束等使用标准的SQL CREATE语句实现。（要求给出创建的标准语句，以及创建之后在SQL数据库中的脚本和截图）。

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 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(仓库编号) references 仓库, 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(客户编号)references 客户);

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

);

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,

constraint PK_订购primary key (订单编号, 商品编号)

);

5、存储过程、触发器和视图：根据需要给数据库添加至少六个实用的存储过程、触发器和视图，并说明它们各自的功能。（需要给出语句执行的结果示意图）

(1)创建视图，查找商品名为'苹果'的商品

create view find_goods

as

select 商品编号,商品名称,价格

from 商品

where 商品名称like '苹果%'

（2）创建视图，查找价格在3000-6000的商品create view price

as

select 商品编号,商品名称

from 商品

where 价格>3000 and 价格<6000

(3)创建触发器，提醒店铺中库存量小于10的商品create trigger trigger_alarm

on 销售

after update

as

if(select 库存量from 销售where 库存量<10)<10 print 'trigger out:'

select 商品编号,库存量from 销售where 库存量<10 return

（4）创建触发器，当删除配送单中的数据时显示出所删信息create trigger trigger_del

on 配送单

after delete

as

print 'delete'

(5)创建存储过程，根据商品编号，查询该商品的订购量create proc sp_find_quantity

@商品编号char(5),

@sumq int output

as

select 商品编号,sum(数量)

from 订购

group by 商品编号

having 商品编号=@商品编号

declare @sumq int

exec sp_find_quantity

@商品编号='s0001',

@sumq=@sumq output

print 'the sum quantity is:'+

convert(varchar(5),@sumq

（6）创建存储过程，通过商品名称寻找商品信息

create proc sp_find_price

@name char(10)

as

select *

from 商品

where 商品名称like @name + '%'

exec sp_find_price

@name='戴尔

6、分析常见的业务流程，列举出至少五种SQL SELECT语句。实现的语句要满足如下要求：

a)在五种语句中的查询能反映正常的业务需求；

b)分析中至少要分别出现一次ORDER BY、GROUP BY … HAVING子句；

c)分析中至少使用一次聚集函数；

d)分析中至少使用一次嵌套查询；

e)分析中至少使用一次UNION或INTERSECT运算；

给出每一种语句执行的结果。

(1)计算并查找订购单中相同订单订购超过5000的总额

select 订单编号,sum(金额)as 总额

from 订购

group by 订单编号

having sum(金额)>5000

(2)查找即购买了商品编号为s0001又买了s0002的客户select 姓名

from 客户b,订单o,订购s

intersect

select 姓名

from 客户b,订单o,订购s

(3)按照顾客c0002消费的总额大小顺序排订单编号select 订单编号,总额

from 订单

where 客户编号='c0001'

order by 总额desc

(4)查询订单编号为00003的配送信息

select o.订单编号,配送人,联系电话

from 订单o,配送单s

where o.订单编号=s.订单编号and s.订单编号='00003'

(5)查找同样买了商品编号为s0002的客户信息select 客户编号,姓名,电话

from 客户

where 客户编号

in

(select 客户编号

from 订单，订购

where 订单.订单编号=订购.订单编号and 订购.商品编号='s0002')

(6)查询当日销售总额

select 订购时间,sum(总额) as 营业额

from 订单

group by 订购时间

7、（选做）完成数据库的设计之后，根据自己所熟悉的编程语言（C、C++、JA V A），实现一个简单的程序，能够在程序中组装SELECT语句，链接创建的数据库，进行查询，并显示查询结果。若完成，请提供详细的代码清单（代码作为附件，附在本报告的最后）。

用JSP页面编写

实现如下：

8、实验小结。

在本次实验中一共建立了9个表，完成了6条Select语句，6条关于视图、触发器和存储过程，在语句实现过程中，因为涉及内容比较广，差不多把数据库语句全部复习了一遍才开始做，所花的时间较长，但也收益颇丰，对数据库的设计和实现也有了总体了解。最后在做选做时，选择了通过网页的方式实现，因为在做专业实训时也用了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 BuildConnection { 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 broker = null;

void setUp() {

//驱动包

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;

}

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 on

System.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.freeConnection(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();

else

return -1;

}

public ConnectionBroker() {

super();

setUp();

}

}

<%@ page language="java" contentType="text/html; charset=GB2312"

pageEncoding="GB2312"%>

<%@ page import="com.ynu.myBusiness.db.*,java.sql.Connection,java.sql.PreparedStatement,java.s ql.ResultSet,java.sql.Statement" %>

数据库实验大作业

搜索配送单中的数据

<%

//从工厂中得到连接

ConnectionFactory factory=ConnectionFactory.getInstatnce();

Connection connection= factory.getConnection();

//执行语句

PreparedStatement pstatement = null;

//结果集

ResultSet rs = null;

pstatement = connection.prepareStatement("select * from 配送单");

//执行

rs = pstatement.executeQuery();

%>

<%

while (rs.next()) {

out.println("

");

out.println("

");

out.println("

");

out.println("

");

out.println("

");

}

%>

查询号	订单编号	配送人
" + rs.getString(1) + "	" + rs.getString(2) + "	" + rs.getString(3) + "

<%//显示完毕，释放资源

try {

if (rs != null)

rs.close();

if (pstatement != null)

pstatement.close();

if (factory != null)

factory.freeConnection(connection);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

%>