2表设计.docx

2表设计.docx

《2表设计.docx》由会员分享，可在线阅读，更多相关《2表设计.docx（10页珍藏版）》请在冰豆网上搜索。

2表设计

第二节表设计

应用背景

数据库的数据存储是以表为存储单位，就像我们日常生活中的很多数据都是以表格的形式出现的一样，例如机动车驾驶报名申请表，入学申请表，工资表，销售单据，药品单据等等。

那么如何把这些表格组织在一起形成一个数据库就是我们设计数据库的目的。

通常数据库设计跟我们的应用场景有关系，设计的基本要求为使用简便，设计合理，避免数据冗余，保证数据完整性等等。

例如针对某公司的销售单据，我们可能的数据库设计如下：

客户数据表：

存储客户数据，属性有：

客户代码（主键），客户名称，电话，手机，地址，邮编等。

产品数据表：

存储销售的产品数据，属性有：

产品代码（主键），名称，型号,品牌，计量单位，参考单价等。

销售单据表：

销售单编号（主键），客户代码（客户数据表），销售日期，销售代表，折扣系数，货款金额，收款金额等

销售明细表：

流水号（主键），销售单编号（销售单据表），产品代码（产品数据），销售单价，销售数量，销售价格。

共4个实体构成我们的数据库，数据库的实体关系图如下：

图2.1销售数据库的实体关系图

知识要点

（1）表名

表是关系数据库的最基本对象，数据库中的数据皆存储在表中，以2维表格的形式存储。

每个表有一个名字，一个数据库中的表名不可重复。

表名以字母开头，其余的可以是数字或字母，例如company,tablename，Tbl001等等都是合法的表名。

（2）列名

列名又可称为字段名，在创建表的时候，为表的每个列指定一个名字，这样对表的访问，就可通过字段名来指定所要访问的列，例如要访问表Tbl001的字段F001,F002,F003,可以写为Tbl001.F001,Tbl001.F002,Tbl001.F003，表名和字段名之间采用“.”相连。

一个表中的字段名不能重复，且必需以字母开头，后边可以使数字、字符或下划线等字符。

表中列的数量有一定限制，不同的数据库有不同的限制数。

表中的列最好少于255列，过多的列直接影响数据库的设计质量。

每个列还有数据类型、数据的默认值、索引约束、主键、外键等等限制。

（3）记录

表中的一行数据称为一条记录，每个表中的记录几乎没有限制，加上每个数据库中可以有多张没有数据记录限制的表，充分体现了数据库的无限的数据存储能力。

图2.2示意表的列名、表名，记录的概念。

表名：

Tbl001

字段1

F001（序号）

字段2

F002（姓）

字段3

F003（年龄）

字段4

F004（性别）

字段5

F005（电话）

（记录）1

Wang

40

F

67602861

（记录）2

Zhang

32

T

65679990

（记录）3

li

23

T

54300211

（记录）4

liu

35

T

556666

……

……

……

……

……

……

……

……

……

……

N

图2.2表名、列名、记录概念示意图

（4）数据类型

数据表中每列都有统一的数据类型，它不仅影响数据的存储空间，而且还影响对列数据的操作，例如日期类型的列数据进行比较，与数字类型的列数据进行比较，会有很大不同，字符串类型的列排序与日期类型的列排序也会有所不同。

整数类型：

存储整数数据，以INT表示，分为1字节，2字节，4字节整数，支持统计操作。

浮点数类型：

存储浮点数据类型，以Float表示，通常把精确的小数数字的类型，定义为Decimal或Numeric。

字符串类型：

用于存储字母、数字、特殊字符等组成的字符串，在使用时必须加上单引号，一般以Char（n）表示，n代表字符串的设计长度，对于没有固定长度的字符串，使用VarChar（n）表示，n代表未知的字符串长度。

时间类型：

用于存储时间量，有日期型Date,时间Time,以及日期时间DateTime类型3种格式，对于含有日期的数据记录非常重要，经常会用到设计时间计算的问题。

（5）创建表

在MSSQLSERVER2000中，创建表可以采用图形化操作步骤，下面以创建销售数据库为例，给出创建客户表的步骤：

a.打开企业管理器，选中数据库对象，点击鼠标右键，选择新建数据库，进入新建数据库窗体，如图2.3创建数据库www。

图2.3创建数据库www

b.选择数据库www,选中表对象，点击鼠标右键，选择新建表，如图2.4新建表窗口。

新建表名为customer,包含字段为：

客户代码（主键,数据不可为空）-cidchar（10）notnull，客户名称—cnamechar（30）notnull，电话—ctelvarchar（50），手机—cmobilevarchar（50），地址—caddrvarchar（50），邮编—czipchar（6）等。

每个字段都要输入一个字段名，字段数据类型，允许为Null的要求。

完毕后，点击保存按钮，保存数据表名为customer,参见图2.5保存新建数据表。

图2.4新建表窗口

图2.5保存新建数据表

（6）创建表的SQL语句

创建表可以使用标准SQL语言的CreateTable语句。

选中新建表Customer后，点击鼠标右键，出现菜单选择所有任务-〉生成SQL语句脚本-〉预览，即可得到建立数据表的脚本，创建客户信息表的标准SQL语句为：

CREATETABLE[dbo].[customer]（

[cid][char]（10）NOTNULL,

[cname][char]（30）NOTNULL,

[ctel][varchar]（50）NULL,

[cmobile][varchar]（50）NULL,

[caddr][varchar]（50）NULL,

[czip][char]（6）NULL

）

CreateTable为关键字，其后新建的表名，表的内容采用括号（）扩起来，列名、数据类型采用中括号[]，数据长度采用（），然后为该列允许为空Null，不许为空notNull的要求。

每行代表一个列的属性值，使用逗号把列分割开来。

这样依据上面新建表步骤，建立针对产品数据表，销售单据表，销售明细表的3个新表，选中每个新建表后，点击鼠标右键，出现菜单选择所有任务-〉生成SQL语句脚本-〉预览，即可得到建立数据库表的脚本，SQL语句如下：

CREATETABLE[dbo].[product]（

[pcode][char]（10）NOTNULL,

[pname][varchar]（50）NOTNULL,

[ptype][varchar]（50）NULL,

[pbrand][varchar]（30）NULL,

[punit][char]（10）NULL,

[pprice][float]NULL

）

CREATETABLE[dbo].[detail]（

[id][int]IDENTITY（1,1）NOTNULL,

[rid][int]NOTNULL,

[pcode][char]（10）NOTNULL,

[dprice][float]NOTNULL,

[dnumber][float]NOTNULL,

[dmoney][float]NULL

）

CREATETABLE[dbo].[retail]（

[rid][int]NOTNULL,

[cid][char]（10）NOTNULL,

[cdate][datetime]NULL,

[remployee][char]（10）NULL,

[rrate][float]NULL,

[rprice][float]NOTNULL,

[money][float]NOTNULL

）

这样在数据库www中创建了4个表，表名分别为customer,product,retail,detail，参见图2.6为所建表后的企业管理器。

图2.6在数据库www中所创建的4个表

（7）列约束问题

给表中的列加上约束是保证数据完整性的重要方法。

一旦给表的列字段添加约束后，给表添加数据或者更改数据，都必须满足列的约束条件，不符合约束条件的操作，将会产生错误。

主要约束有：

唯一性约束，主键约束，外键约束，非空约束，默认值约束等。

唯一性约束：

候选键约束将保证该列数据在记录中没有重复。

例如客户信息中的客户代码不能重复，销售数据中的销售单据号不能重复，这样就以他们为候选键，保证数据表中的数据不重复。

关键字Unique用来指定唯一性列，一个表中可以有很多个唯一性列。

主键约束：

一个表只能有一个主键，使用Primarykey定义，例如客户信息中客户代码为主键，当然也是唯一性列，所有记录在该列不能重复。

选中新建的表customer,点击鼠标右键，选择设计表后，参看图2.7。

图2.7主键约束设定及管理索引/键快捷键

选择管理索引/键后，可以选择新建索引，并给索引添加列及列约束，如新建IX_customer，创建Unique约束，参见图2.8画面。

图2.8表索引/键管理

外键约束：

外部键约束增加了关系数据库之间的关系，能够较好地保证数据库表间的完整性。

对应的主键所在的表和与其对应的具有外部键的表，一般称为父/子表关系，主键所在的表成为主表，对应外部键表称为子表。

例如，销售单据明细表中的销售单据号与销售单据表中的销售单据号对应，这样就可以在销售单据明细表中把销售单据号字段设为外键，且在销售单据表中以销售单据号为主键，从而构成主表/子表关系。

在企业管理器中，选择设计表-〉管理关系，见图2.9用于建立表间关系。

图2.9表关系管理

选择新建关系，并确定主表/子表，确定连接字段，保存表后，即建立了表间的主/子表关系。

（8）创建索引

数据库中查询速度是用户非常关心的问题，查询速度的快慢与我们建立的索引有很大关系，创建索引后，查询记录是通过索引来查找数据，而不比扫描每个记录，因此索引好坏直接影响查询速度。

但即使索引建立的非常有利于记录查询，但也带来更新记录，添加记录以及删除记录后，需要重新建立索引而带来的性能消费，而导致数据库的效率降低。

选中新建的表detail后，点击鼠标右键，选择设计表菜单，进入设计表画面，选择管理索引/键功能，选择新建索引IX_detail，并确定索引的列名为pcode作为索引列，升序排列，参见图2.9创建索引。

图2.9创建索引

应用举例：

例1：

以某药品公司的药品为例，设计药品数据表，并设定表的主键，外键，索引，表间关系等。

1．建立药店的药品数据库，首先是药品分类实体，再次是药品供货商实体，然后是药品数据实体。

2．药品采购数据库3个实体之间的关系为：

每个药品分类对应多个药品数据实体数据，每个药品供货商实体对应多个药品数据实体数据。

3.建立每个实体的基本属性如下：

药品分类class：

药品分类号（主键，唯一性）cid，药品类别名称cname，类别描述cnote。

药品供货商suply：

供货商代码（主键，唯一性）sid，名称sname，联系人slink，电话stel，手机smobile，地址saddr，邮编szip。

药品数据drug：

药品代码（主键，唯一性）did，名称dname，药品类别（药品分类实体,索引字段）cid，供货商代码（供货商实体,索引字段）sid，品牌dbrand，采购单价dprice，药品描述dnote等。

4．药品分类为主表，药品数据为子表，外键为药品分类号cid，关联字段为药品分类号cid；供货商为主表，药品数据为子表,外键为供货商代码sid，关联字段为供货商代码sid。

例2：

以天然气井口生产数据为例，设计生产数据表，并设定表的主键，外键及索引。

1．建立天然气井口的生产数据数据库，首先要有天然气井口设备实体，然后是每个井口的生产数据实体，由设备采集生产数据。

2．天然气井口的生产数据包含2个实体，他们之间的关系为：

天然气井设备实体对应多条生产数据实体，对应表分别为device,datamap。

3．天然气井口的生产数据实体属性如下：

天然气井口设备表--device：

井口数据采集设备编号（主键，唯一性）didint，井口名称dnamechar（20），隶属生产区域dzonevarchar（50）。

生产数据实体—datamap：

序号（主键，唯一性）idint,数据采集时间dtimeDateTime，井口数据采集设备编号didint（外键），生产数据1（油压）a1float，生产数据2（套压）a2float，生产数据3（出站压力）a3float，生产数据1（温度）a4float，生产数据1（流量）a5float，生产数据1（硫化氢含量）a6float等。

4．设备表为主表，设备编号为主键，生产数据表datamap为子表，主键为id,外部键为did即设备编号。

知识扩展：

（1）数据完整性

数据库中的数据是以表的形式存储的,由于数据表之间存在的关系，导致数据的完整性指对数据内容具有一定的限制，称为完整性。

数据库的完整性，包括数据的准确性，一致性。

完整性可以在以下方面体现：

字段约束：

字段是存储数据的基本单位，有特定的取值范围。

例如客户信息表中的年龄，基本应该在10-100岁之间，不可能有其他取值范围，小于10岁或大于100岁再现实生活中都是不可思议的事情。

表完整性约束：

对表中记录数据之间的约束，体现了记录之间关系完整性。

例如表中的主键，不能接收空值，而且不能够重复，这些都是创建表时通过设置主键来维护的。

关联完整性约束：

关联完整性指数据表之间的关联有明确的数据意义，例如父表与子表之间的关联，子表中的记录涉及外键列的数据在主表中必须有相关记录，否则视为非法，这种关联性能够保证数据的完整性。

培训练习：

（1）请回答数据库，表，列，记录之间的关系。

（2）请回答数据表中列的约束条件：

主键，唯一性，外键，缺省值，非空约束的意义。

（3）请建立关系客户信息表与销售单据表之间的父/子表关系，客户信息表与销售单据表之间的父/子表关系，销售单据表与销售明细表之间的父/子表关系。

（4）请给客户信息表，产品信息表，销售单据表，销售明细表建立主键、索引、外键，并指明关联字段都是那些？

（5）请在MSSELSERVER2000上建立按照例题1，例题2的步骤建立数据库，并生成SQL脚本。