罗斯文商贸数据库分析

第16章数据库实例分析

初学者面对一个应用课题，常不知如何着手。有的人将开发数据库视为编程，其结果常常事倍功半，数据库质量也难以保证。现实的数据库应用系统的开发是复杂的，必须按照一定的规范有步骤地进行。

本章将整合以前各章介绍的思想与方法，通过microsoft office自带的“罗斯文商贸数据库”来说明Access应用系统的整个开发过程，达到进一步学习Access的目的。

16.1罗斯文商贸数据库简介

罗斯文商贸数据库是微软公司为用户提供的一个实例，安装office 同时用户在“自定义”安装时选择。如果当初没有选择该项，任何时候都可以将OFFICE 2003光盘插入到光驱中，然后通过windows系统的“控制面板”?“添加或删除程序” ?“添加新程序”?选择从CD-ROM安装。

安装好的数据库文件存放在系统盘的“program file /Microsoft

office/office/sample”目录下。该数据库文件的英文名称为

“northwind.mdb”。打开Microsoft Office Access时，在已有数据库列表中选择“罗斯文商贸数据库”或者northwind.mdb即可。本光盘中复制了

northwind.mdb，读者可以直接将其复制到您的系统硬盘中，用鼠标双击该文件名，启动它运行。

本文中有时把该数据库名简称为“罗斯文”。

“罗斯文”示例数据库提供了用户可以试用的数据以及数据库对象(表、查询、窗体、报表、数据访问页、宏及模块)，这些数据库对象展示了可以在用户自己的数据库中运用的 Microsoft Access 功能。通过使用“罗斯文”数据库，用户

能熟悉如何构造关系数据库，以及如何将数据库对象组合在一起来帮助输入、存储、处理、分发与打印数据。

数据库开发步骤:

16.1.1需求分析

系统需求包括数据分析和功能分析两个方面。数据分析的任务是归纳出系统所包含的数据，功能分析的任务在于为应用程序设计提供依据，以实现应用要求的功能。

数据需求分析:在一个商业过程中包括供货商，订单，产品，雇员，客户和运货商多个环节，对于每一个环节我们都需要掌握详细的信息，这些信息将成为本数据库重要的信息源。

功能需求分析:本数据库的功能需求可以概括为以下几个方面:

(1) 数据录入数据录入功能要求及时更新或修改各种数据信息，包括供货商信息，产品信息，订单信息，雇员信息以及客户信息。

(2) 查询能够查询订单信息，产品信息，雇员和客户信息，发货单信息。

(3) 汇总并显示各类销售额信息，包括年度汇总销售额，季度汇总销售额，金额汇总销售额等。

(4) 打印各种报表包括产品报表，雇员报表，各类销售额报表，发货单等。

16.1.2.数据库逻辑设计

确定数据表:

根据前面的分析，我们分别归纳出八个表:

1( 产品(产品ID，产品名称，供应商，类别，单位数量，单价，库存量，订购量，再订购量，

中止)

2( 订单(订单ID，客户，雇员，订购日期，到货日期，发货日期，运货商，运货费，货主名称，

货主地址，货主城市，货主地区，货主邮政编码，货主国家)

3( 订单细则(订单ID，产品，单价，数量，折扣)

4( 供应商(供应商ID，公司名称，联系人姓名，联系人头衔，地址，城市，地区，邮政编码，

国家，电话，传真，主页)

5( 雇员(雇员ID，姓氏，名字，头衔，尊称，出生年月，雇用日期，地址，城市，地区，邮政

编码，国家，家庭电话，分机，照片)

6( 客户(客户ID，公司名称，联系人姓名，联系人头衔，地址，城市，地区，邮政编码，国家，

电话，传真)

7( 类别(类别ID，类别名称，说明，图片)

8( 运货商(运货商ID，公司名称，电话)

表之间的关系设计:

在Access中为每个主题都设置了不同的表后，必须告诉 Access 如何再将这些信息合并在一起。第一个步骤是定义表间的关系，然后创建查询、窗体及报表来从多个表中立刻显示信息。

罗斯文数据库中包含六个一对多关系，其中“产品”表与“订单”表之间的多对多关系是通过“订单明细”表中两个一对多关系来创建。详见图16.1。

注意:在多对多关系中， A 表中的记录能与 B 表中的许多记录匹配，并且在B 表中的记录也能与 A 表中的许多记录匹配。此关系的类型仅能通过定义第三个表(称作联结表)来达成，它的主键包含二个字段，即来源于 A 和 B 两个表的外部

键。多对多关系实际上是使用第三个表的两个一对多关系。“订单”表和“产品”表有多对多的关系，它是通过“订单明细”表中两个一对多关系来创建，详见图16.2。

图16.1

我们用表16.1解释表间关系

需求一对多的关系主键外键

一个供应商可以提供多种“供应商”表和供应商.供产品.供应商商品“产品” 表应商ID ID

一种类别包含很多种产品“类别”表和“产类别.类别产品.类别ID

品”表 ID

“产品”表中的主键“产“产品”表和“订单产品.产品订单细则.产品ID”，可以在“订单细则”中细则”表 ID 品ID 找到多个一样的产品ID，构成一对多的关系

“产品细则”中存在多个“订单细则”表和订单细则.订单.订单ID 相同的订单ID，在“订单”表的“订单”表订单ID，产品

主键为订单ID，所以构成多对一

的关系

一个雇员可以拿到多份订“雇员”表和“订雇员.雇员订单.雇员ID 单单”表ID

一份顾客可能订了多个订“订单”表和“顾订单.订单顾客.订单ID 单客”表ID

表16.1

图16.2

16.1.3数据库物理设计

物理设计部分包括定义表的字段结构，建立表间关系。首先我们讲述如何为表定义字段。

(1) 表的字段结构

产品:

字段名数据类型字段属性主键说明产品ID 自动编号字段大小:长整型是自动赋予新产品的编号。产品名称文本字段大小:40

供应商ID 数字字段大小:长整型与供应商表中的项相同。

小数位数:自动

类别ID 类别ID 字段大小:长整型与类别表中的项相同

小数位数:自动

单位数量文本字段大小:20 (例如， 24 装箱、一公升瓶)。单价货币格式:货币

小数位数:自动

库存量数字字段大小:整型

格式:常规数字

小数位数:自动

订购量数字字段大小:整型

格式:常规数字

小数位数:自动

再订购量数字字段大小:整型为保持库存所需的最小单元数。

格式:常规数字

小数位数:自动

中止是/否格式:是/否 "是"表示条目不可用。

订单:

字段名数据类型字段属性主键说明

订单ID 自动编号字段大小:长整型是唯一订单编号。客户ID 文本字段大小:5 与客户表中的项相同。雇员ID 数字字段大小:长整型与雇员表中的项相同。

小数位数:自动

订购日期日期/时间格式:yyyy\-mm\-dd

到货日期日期/时间格式:yyyy\-mm\-dd

发货日期日期/时间格式:yyyy\-mm\-dd

运货商数字字段大小:长整型与运货商表中的运货商ID相同。

小数位数:自动

运货费货币格式:货币

小数位数:自动

货主名称文本字段大小:40 接收货物的公司或人的名称。货主地址文本字段大小:60 仅为街道地址 -- 不允许为邮政

信箱。货主城市文本字段大小:15

货主地区文本字段大小:15 州或省。

货主邮政编码文本字段大小:10

货主国家文本字段大小:15

订单细则:

字段名数据类型字段属性主键说明

订单ID 数字字段大小:长整型是与订单表中的订单ID相同。

小数位数:自动

产品ID 数字字段大小:长整型是与产品表中的产品ID相同。

小数位数:自动

单价货币格式:

,#,##0.00;($#,##0.00)

小数位数:自动

数量数字字段大小:整型

格式:常规数字

小数位数:自动

折扣数字字段大小:单精度型

格式:百分比

小数位数:0

供应商:

字段名数据类型字段属性主键说明供应商ID 自动编号字段大小:长整型自动赋予新供应商的编号。公司名称文本字段大小:40

联系人姓名文本字段大小:30

联系人头衔文本字段大小:30

地址文本字段大小:60

城市文本字段大小:15

地区文本字段大小:15

邮政编码文本字段大小:10

国家文本字段大小:15

电话文本字段大小:24 电话号码包括国家代号或区号。传真文本字段大小:24 电话号码包括国家代号或区号。主页超级链接供应商在 World Wide Web 上

的主页。

雇员:

字段名数据类型字段属性主键说明雇员ID 自动编号字段大小:长整型

是自动赋予新雇员的编号。姓氏文本字段大小:20 名字文本字段大小:10 头衔文本字段大小:30 雇员的头衔。尊称文本字段大小:25 礼貌的称呼。出生日期日期/时间格式:

yyyy-mm-dd

雇用日期日期/时间格式:

yyyy-mm-dd

地址文本字段大小:60 街道或邮政信箱。城市文本字段大小:15

地区文本字段大小:15 州或省。邮政编码文本字段大小:10

国家文本字段大小:15

家庭电话文本字段大小:24 电话号码包括国

家代号或区号。分机文本字段大小:4 内部电话分机号码。照片 OLE对象雇员照片。备注备注有关雇员背景的一般信

息。

上级数字字段大小:长整型雇员的上级。

小数位数:自动

客户:

字段名数据类型字段属性主键说明客户ID 文本字段大小:5 是基于客户名称的5 字符唯一代码。公司名称文本字段大小:40

联系人姓名文本字段大小:30

联系人头衔文本字段大小:30

地址文本字段大小:60 街道或邮政信箱。城市文本字段大小:15

地区文本字段大小:15 州或县

邮政编码文本字段大小:10

国家文本字段大小:15

电话文本字段大小:24 电话号码包括国家代号或区号。传真文本字段大小:24 电话号码包括国家代号或区号。

类别:

字段名数据类型字段属性主键说明

类别ID 自动编号字段大小:长整型是自动赋予新类别的编号。类别名称文本字段大小:15 食品类别名称。说明备注

图片 OLE对象描绘食品类别的图片。

运货商:

字段名数据类型字段属性主键说明

运货商ID 自动编号字段大小:长整型是自动赋予新运货商的编号。

公司名称文本字段大小:40 运货公司名称。

电话文本字段大小:24 电话号码包括国家代号或区号。.

(2) 表的建立:

第八章中介绍了三种建立表的方法:利用向导创建表，通过输入数据创建表，使用设计视图创建表，下面我们利用设计视图建立“产品”表。

1(在数据库窗口，选定对象条中的表对象，然后点击窗口右边“使用设计器创建表”，窗口上会出现表的设计视图。表的设计窗口包括各个字段的字段名称，数据类型，说明等信息。参见图16.3和图16.4。

图16.3

2.首先我们将产品(产品ID，产品名称，供应商，类别，单位数量，单价，库存量，订购量，再订购量，中止)中的各字段名称分别输入表设计视图中的字段名称列。

3.在数据类型列，单击下箭头选择各字段所对应的数据类型，其中包括文本，备注，数字，日期/时间，货币，自动编号，是/否，OLE对象，超级链接，阅读向导。

4. 当在数据类型中选择一种字段类型后，设计视图下面会出现所对应的字段属性设置框，在“常规”选项卡中设定字段的属性，其中“字段大小”设定该字段所占的字节数或数字的类型，“格式”设定该字段字符串的显示格式，“输入法模式”设定当焦点移到该字段时是否打开输入法，“输入掩码”设定该字段数据输人的模式，“默认值”表示当增加新记录时该字段所使用的预设值。这些字段属性都要根据具体字段而具体设定。

5(如果需要添加说明文字，可以在说明栏中添加。当以后在窗体中选择该字段时

说明文字会显示在状态栏中。

6(在Access中，每个表都要指定关键字，在表中定义主关键字除了可以保证每条记录都被惟一识别外，更重要的作用在于多个表间的连接。如果用户没有制定一个惟一的主关键字，Access会自动建立一个。用户也可以自己制定一个主关键字，方法是:选定一个或多个字段后单击工具栏中的“主键”按钮，则相应字段左侧会出现主关镶字标志。这里定义“学号”字段为主关键字。

7(保存“产品”表

通过上面步骤我们建立了“产品”表，我们要先保存表，然后在数据视图中输入记录，见图16.5,图16.6。

图16.4

图16.5

图16.6 (3) 表间关系的建立

要定义关系，首先在“关系”窗口中添加要定义关系的表(见图16.7)，然后从表中拖动键字段，并将它拖动到其他表中的键字段上(见图16.8),这时会出现编辑关系对话框(见图16.9)。我们以“雇员”表和“订单” 表为例，字段雇员ID将两表连接在一起形成一对多的关系，在编辑关系对话框中我们选择实施参照完整性这一项，并点击联系类型按键，在联系属性中选择第一项只包含来自两个表的连接字段相等处的行(见图16.10)。这样“雇员”和“订单”两表之间一对多的关系就

建立起来了。整个数据库中六个一对多的关系都可以通过这种方式建立起来，最终形成完整的数据库关系(见图16.11)。

图16. 7

图16. 8

图16. 9

图16. 10

图16. 11

16.1.4应用程序设计

数据库设计完成后，接下来要进行应用程序的设计。首先我们要从整体上把握，作出系统层次图并设计出用户界面。

(1).系统层次图

系统的总体结构可以用层次图来表示，第一层是系统层，对应主程序;第二层是子系统层，一般起分类控制作用;第三层是功能层。本数据库有两个子系统:查看产品和订单信息子系统和打印销售额报表子系统。查看产品和订单信息子系统又可以分为按类别查看，按供应商查看，按产品查看和按订单查看四种。打印销售报表子系统可以分为各国雇员销售额，按金额销售额分和各类销售额，如图16.12。

(2).用户界面设计

根据系统层次图我们可以设计出主切换面板。这样不但可以使设计者思路清晰，而且也可以令用户对数据库的功能一目了然。本数据库主切换面板如图

16.13。为了使功能更完善，主切换面板上还添加了退出Access和显示数据窗口两个按键。

图16. 12

图16. 13

(3).建立查询

所谓查询就是根据给定的条件从数据库的表中选出符合条件的记录，构成一个数据的集合。当建立了一个查询后，就可以把它看作是一个简化的数据表，它可以作为窗体或报表的数据来源，也可以以它为基础建立其他查询。在罗斯文商贸数据库中需要建立按不同条件进行的销售额查询，产品信息查询，订单信息和发货单信息。对于这四大类查询又有多种查询方法，比如选择查询，参数查询和交叉表查询。

选择查询

选择查询是最常见的查询类型，它从一个或多个表中检索数据，并且在可以更新记录(有一些限制条件)的数据表中显示结果。也可以使用选择查询来对记录进行分组，并且对记录作总计、计数、平均值以及其他类型的总和计算。下面我们以各类产品为例具体讲解如何建立和使用选择查询。

1. 在“数据库”窗口中，单击“对象”下的“查询”，再单击“数据库”窗口工具栏上的“新建”。

2. 在“新建查询”对话框中，单击“设计视图”，然后单击“确定”。见图16.14

3. 在“显示表”对话框中，单击列出了所需的表或查询数据的选项卡。见图16.15

4. 双击要添加到查询的“产品”和“类别”表，然后单击“关闭”。

5. 在设计网格中将字段添加到“字段”行，指定条件与排序次序，添加到设计网格中的字段、排序次序和条件决定了可以在查询结果中看到的内容。见图1

6.16

6. 若要查看查询结果，请单击工具栏上的“视图”。

图16. 14

图16. 15

图

16. 16

交叉表查询

使用交叉表查询计算和重构数据，可以简化数据分析。交叉表查询不仅能进行“总计”计算，还能重构数据和分析数据。这种查询以表或查询为数据源，分别按行和列对数据分组，并且既可按行进行“总计”,又可按行与列“总计”,结果产生

一个数据表。“罗斯文”示例数据库中的“各种产品的季度订单”查询，就是一个交叉表查询的示例。本查询要实现的功能是将各类产品在1997年各季度的订购额进行统计，用到的表包括“产品”，“订单”和“订单细则”，需要的字段名包括产品名称，客户ID，订购年份，季度和订购额。其中，产品名称，客户ID可以分别从“产品”表和“订单”表直接调用，而订购年份，季度和订购额则不能从这几个表中直接找到，我们需要使用函数来完成。下面我们来具体讲解设计步骤:

1. 在“数据库”窗口中，单击“对象”下的“查询”，再单击“数据库”窗口工具栏上的“新建”。

2. 在“新建查询”对话框中，单击“设计视图”，然后单击“确定”。

3. 在“显示表”对话框中，单击列出了所需的表或查询数据的选项卡。

4. 双击要添加到查询的“订单”，“订单细则”和“产品”表，然后单击“关闭”。

5. 将字段产品名称，客户ID添加到“字段”行，在“表”行指定各字段的来源，由于这两个字段均作为行标题，故在“交叉表”行指定为行标题。

6. 定义“季度”字段，调用datepart函数根据订购日期返回季度值，在字段行键入:季度:"第" & DatePart("q",[订购日期],1,0) & "季度" In ("第1季度","第2季度","第3季度","第4季度")。在“交叉表”行指定为列标题。注意，列标题只能有一个。

7. 定义“订购年份”字段，调用Year函数根据订购日期返回订购年份值，在字段行键入:订购年份: Year([订购日期])。在“交叉表”行指定为行标题。

8. 定义“产品金额”字段，调用调用sum和ccur函数，在字段行键入:产品金额: Sum(CCur([订单明细].[单价]*[数量]*(1-[折扣])/100)*100)。“总计”行选择表达式，“交叉表”行选择为值

9. 由于本查询是在1997年基础上进行的季度统计，所以要指定查询条件，故将“订单”表中的“订购日期”字段键入字段行，在“总计”行中选择“条件”，并在“条件”行中写入查询条件，即Between #1997-1-1# And #1997-12-31#。见图16.17

10. 若要查看查询结果，请单击工具栏上的“视图”。见图16.18

注意:以上操作中，凡“总计”行单元格未进行行选择的均要保持默认的“分组”。

图16. 17

图16. 18

参数查询:

参数查询是这样一种查询，它在执行时显示自己的对话框以提示用户输入信息，例如条件，检索要插入到字段中的记录或值。可以设计此类查询来提示更多的