学校通讯录系统数据库设计

4.2 数据库设计

在本实例中使用的是SQL Server 2000数据库管理系统。数据库服务器安装的位置为本机，数据库名称为communicationserver 。下面将介绍数据库分析和设计、数据表的创建、数据表关系图和创建存储过程的内容。

4.2.1 数据库分析和设计

在本节中，将根据上一节系统功能的设计分析创建各数据表的实体E-R 关系图，它是数据库设计非常重要的一步，下面用E-R 图的形式表示出各实体的属性。

1. 用户E-R 实体图

用户的实体具有用户ID 、密码、姓名、性别、电话、E-mail 、用户权限７个属性，ER 图如图4-5所示。

图4-5 用户E-R 实体图

2. 地区E-R 实体图

地区的实体具有地区编号、地区名称２个属性，E-R 图如图4-6所示。

图4-6 地区E-R 实体图

3. 职务E-R 实体图

职务的实体具有职务编号、职务名称２个属性，E-R 图如图4-7所示。

图4-7 职务E-R 实体图

4. 学校E-R实体图

学校的实体具有学校名称、学校所在地区、在校学生人数３个属性，可用E-R图表示，如图4-8所示。

图4-8 学校E-R实体图

5. 通讯录E-R实体图

通讯录的实体具有联系人姓名、地址、联系电话、所属职务、所在学校、所在单位、所属地区７个属性，E-R图如图4-9所示。

图4-9 通讯录E-R实体图

4.2.2 数据表的创建

在上面一节中讲解了实体的E-R图，本节中将根据E-R图的内容，创建企业信息管理系统中的各数据表，在此系统中共涉及到5个数据表的应用，分别为如下。

1、字段采用camel 风格定义

2、用不同颜色区分字段

●主键：蓝色

●外键：白色

●既是主键又是外键：淡蓝色

●普通字段：青绿

1. 用户信息表

用户信息表(Users)用于存放校园通讯录信息管理系统中所有系统管理用户的信息，用户

管理是每个管理系统中必不可少的，也是比较重要的一部分，本系统中进行了较详细的设计，

表中各个字段的数据类型、大小以及简短描述如表4-1所示。

表4-1 用户信息表TB_Users

字段名称字段类型字段描述

sUserID varchar(10) 用户ID [PK]

sUserName varchar (20) 用户姓名

sUserPassword varchar(10) 记录用户登入本系统时的用户密码sUserSex char(2) 用户性别

sUserEmail varchar(20) 用户E-mail

sUserPhone varchar(20) 用户的联系电话

sUserPurview char(1) 用户权限

2．地区信息表

地区信息表(Client)用于存放校园通讯录信息管理系统中所有地区的信息，地区信息的

管理是系统中比较重要的内容，地区的名称是其中的关键信息，表中各个字段的数据类型、

大小以及简短描述如表4-2所示。

表4-2 地区信息表TB_Area

字段名称字段类型字段描述

sAreaID varchar(20) 地区编号[PK]

sAreaName varchar(50) 地区名称

3. 职务信息表

职务信息表(Compact)用于存放校园通讯录信息管理系统中所有职务的信息，职务信息

的管理是系统中比较重要的内容，职务的名称是其中的关键信息，表中各个字段的数据类型、

大小以及简短描述如表4-3所示。

表4-3 职务信息表TB_Duty

字段名称字段类型字段描述

sDutyID varchar(20) 职务编号[PK]

sDutyName varchar(50) 职务名称

4. 学校信息表

学校信息表(Com_detail)用于存放校园通讯录信息管理系统中所有学校的信息，学校信息的管理是系统中比较重要的内容，学校名称是其中的关键信息，表中各个字段的数据类型、大小以及简短描述如表4-4所示。

表4-4 学校信息表TB_School

字段名称字段类型字段描述

sSchoolID varchar(20) 学校编号[PK]

sSchoolName varchar(50) 学校名称

iStudentNum int 学校人数

sAreaID_FK varchar(20) 地区编号[FK]

5. 通讯录信息表

通讯录信息表(product)用于存放校园通讯录信息管理系统中所有通讯录的信息，通讯录信息的管理是系统中比较重要的内容，联系电话是其中的关键信息，表中各个字段的数据类型、大小以及简短描述如表4-5所示。

表4-5 通讯录信息表TB_AddrList

字段名称字段类型字段描述

iAddrID int 通讯录编号(自动编号)[PK] sLinkName varchar(50) 联系人姓名

sAddress varchar(20) 地址

sLinkPhone varchar(20) 联系电话

sUnitName varchar(50) 工作单位

sSchoolID_FK varchar(20) 学校编号[FK]

sDutyID_FK varchar(20) 职务编号[FK]

sAreaID_FK varchar(20) 地区编号[PK]

4.2.3 创建视图

学校信息视图表V_SchoolInfo

字段名称字段类型字段描述

sSchoolName varchar(50) 学校名称

sAreaName varchar(50) 地区名称

sLinkName varchar(20) 联系人姓名

sDutyName varchar(20) 职务名称

sLinkPhone varchar(20) 联系电话

create view V_SchoolInfo

as

select sSchoolName,sAreaName,sLinkName,sDutyName,sLinkPhone

from TB_AddrList,TB_School,TB_Duty,TB_Area

where TB_AddrList.sSchoolID_FK=TB_School.sSchoolID and TB_AddrList.sDutyID_FK=TB_Duty.sDutyID and TB_AddrList.sAreaID_FK=TB_Area.sAreaID

4.2.4 数据表关系图

在学校通讯录信息管理系统中各数据表的关系图，如图4-10所示。在TB_School数据表中，TB_Area数据表的sDutyID_FK字段作为外键。在TB_AddrList数据表中，TB_School 数据表的sSchoolID_FK字段、TB_Area数据表中的sAreaID_FK字段和TB_Duty数据表中的sDutyID_FK字段作为外键。

图4-10 数据表关系图

4.2.5 创建存储过程

因为使用存储过程可以给系统带来优化，所以系统将很大程度地使用存储过程，在系统中共应用了10个存储过程，以Add开头的存储过程为系统中信息添加的存储过程，以Update 开头的存储过程为系统中信息修改的存储过程，这些存储过程将会在系统程序文件的后台应用中调用，下面将对系统中应用的存储过程进行解释。

1. AddTB _Users存储过程

AddTB _Users存储过程用于向用户信息表中添加新的用户信息。以下代码表示了这一存储过程。

CREATE PROCEDURE AddTB_Users @sUserID varchar(10),

@sUserName varchar(20),

@sUserPassword varchar(10),

@sUserSex char(2),

@sUserEmail varchar(20),

@sUserPhone varchar(20),

@sUserPurview char(1)

AS

INSERT INTO TB_Users

(

sUserID,

sUserName,

sUserPassword,

sUserSex,

sUserEmail,

sUserPhone,

sUserPurview

)

VALUES

(

@sUserID,

@sUserName,

@sUserPassword,

@sUserSex,

@sUserEmail,

@sUserPhone,

@sUserPurview

)

GO

2. Add 存储过程

Add TB_Area存储过程用于往地区信息表中添加新的地区信息。以下代码表示了这一存储过程。

CREATE PROCEDURE AddTB_Area

@sAreaID varchar(20),

@sAreaName varchar(50)

AS

INSERT INTO TB_Area

(

sAreaID,

sAreaName

)

VALUES

(

@sAreaID,

@sAreaName

)

GO

3. AddTB_Duty存储过程

AddTB_Duty存储过程用于往职务信息表中添加新的职务信息。以下代码表示了这一存储过程。

@sDutyID varchar(20),

@sDutyName varchar(50)

AS

INSERT INTO TB_Duty

(

sDutyID,

sDutyName

)

VALUES

(

@sDutyID,

@sDutyName

)

GO

4．AddTB_School存储过程

AddTB_School 该存储过程用于往学校信息表中添加新的学校信息。以下代码表示了这一存储过程。

@sSchoolID varchar(20),

@sSchoolName varchar(50),

@iStudentNum int,

@sAreaID_FK varchar(20)

AS

INSERT INTO TB_School

(

sSchoolID,

sSchoolName,

iStudentNum,

sAreaID_FK

)

VALUES

(

@sSchoolID,

@sSchoolName,

@iStudentNum,

@sAreaID_FK

)

GO

5. AddTB_AddrList存储过程

AddTB_AddrList存储过程用于往通讯录信息表中添加新的通讯录信息。以下代码表示了这一存储过程。

@sLinkName varchar(20),

@sAddress varchar(50),

@sLinkPhone varchar(20),

@sUnitName varchar(20),

@sSchoolID_FK varchar(20),

@sDutyID_FK varchar(20)

AS

INSERT INTO TB_AddrList

(

sLinkName,

sAddress,

sLinkPhone,

sUnitName,

sSchoolID_FK,

sDutyID_FK

)

VALUES

(

@sLinkName,

@sAddress,

@sLinkPhone,

@sUnitName,

@sSchoolID_FK,

@sDutyID_FK

)

GO

6. UpdateTB _Users存储过程

UpdateTB _Users存储过程用于修改用户信息表中，符合条件的用户信息，其中密码信息不可以更新。以下代码表示了这一存储过程。

@sUserID varchar(10),

@sUserName varchar(20),

@sUserSex char(2),

@sUserEmail varchar(20),

@sUserPhone varchar(20),

@sUserPurview char(1)

AS

UPDATE TB_Users

SET

sUserID =@sUserID,

sUserName =@sUserName,

sUserSex=@sUserSex,

sUserEmail=@sUserEmail,

sUserPhone=@sUserPhone,

sUserPurview=@sUserPurview

WHERE

sUserID =@sUserID

GO

7. UpdateTB_Area存储过程

UpdateTB_Area存储过程用于修改地区信息表中的地区信息。以下代码表示了这一存储过程。

@sAreaID varchar(20),

@sAreaName varchar(50)

AS

UPDATE TB_Area

SET

sAreaID =@sAreaID,

sAreaName =@sAreaName

WHERE

sAreaID =@sAreaID

GO

8. UpdateTB_Duty存储过程

UpdateTB_Duty存储过程用于修改职务信息表中的职务信息。以下代码表示了这一存储过程。

CREATE PROCEDURE UpdateTB_Duty

@sDutyID varchar(20),

@sDutyName varchar(50)

AS

UPDATE TB_Duty

SET

sDutyID =@sDutyID,

sDutyName =@sDutyName

WHERE

sDutyID =@sDutyID

GO

9. UpdateTB_School存储过程

UpdateTB_School存储过程用于修改学校信息表中的学校信息。以下代码表示了这一存储过程。

@sSchoolID varchar(20),

@sSchoolName varchar(50),

@iStudentNum int,

@sAreaID_FK varchar(20)

AS

UPDATE TB_School

SET

sSchoolID =@sSchoolID,

sSchoolName =@sSchoolName,

iStudentNum =@iStudentNum,

sAreaID_FK=@sAreaID_FK

WHERE

sSchoolID =@sSchoolID

GO

10. UpdateTB_AddrList存储过程

UpdateTB_AddrList存储过程用于修改通讯录信息表中的通讯录信息。以下代码表示了这一存储过程。

CREATE PROCEDURE UpdateTB_AddrList

@iAddrID int,

@sLinkName varchar(20),

@sAddress varchar(50),

@sLinkPhone varchar(20),

@sUnitName varchar(20),

@sSchoolID_FK varchar(20),

@sDutyID_FK varchar(20)

AS

UPDATE TB_AddrList

SET

sLinkName =@sLinkName,

sAddress =@sAddress,

sLinkPhone =@sLinkPhone,

sUnitName=@sUnitName,

sSchoolID_FK=@sSchoolID_FK,

sDutyID_FK=@sDutyID_FK

WHERE

iAddrID =@iAddrID GO