电话计费管理系统数据库设计说明

数据库原理课程设计题目计费管理系统学院商学院专业信息管理与信息系统班级信息101 学号 9学生金军地指导教师宣军英编写日期 2012年6月29日目录1、需求分析（园） (3)1.1 总体描述 (3)1.2 功能需求 (3)1.2.1 查询模块 (3)1.2.2 计费模块 (4)1.2.3 基本信息更新模块 (4)1.3 数据流图 (4)1.4 数据字典 (7)2、概念设计（泽阳） (8)2.1 概述 (8)2.2 系统E-R图 (8)2.3 创建各表 (12)3、逻辑设计（吴彬彬） (14)3.1 概述 (14)3.2 E-R图向关系模型的转换 (14)3.2.1 实体及其属性 (14)3.2.2 联系的属性 (15)3.2.3 根据转换规则将实体转换为关系模式 (15)3.2.4 计费系统的E-R图，转换为以下6个关系模式： (15)4、物理设计（金军地） (16)4.1 概述 (16)4.2 实现语句 (16)5、用户界面设计 (18)6、程序代码设计与分析 (19)6.1 创建表及数据库 (19)6.2 建立存储过程向表中插入、修改、删除数据 (20)6.2.1 插入数据的存储过程 (20)6.2.2 修改数据的存储过程 (23)6.2.3 删除数据的存储过程 (24)6.3 建立存储过程实现查询 (24)6.3.1 建立存储过程实现单表查询（5个） (24)6.3.2 建立存储过程实现连接查询（5个） (27)6.3.3 建立存储过程实现嵌套查询（2个） (30)6.4 视图建立 (31)7、心得体会 (32)8、参考文献 (32)计费管理系统1、需求分析（园）1.1 总体描述对邮电局计费业务进行调查，设计的系统要求：●能用关系数据库理论建立几个数据库文件，来存储用户信息、收费员信息和收费信息等资料。

●能够记录通话信息，如来电、去电、通话时长、通话费用，查询费用账单等信息，具有对各种数据文件装入和修改数据的功能。

●能在用户交费同时打印发票。

●有多种查询和统计功能。

●能通过统计制定未来邮局服务计划方案。

1.2 功能需求1.2.1 查询模块◆月花费查询：客户可对每月的话费进行查询（每项记录包括通话费、新业务费、费用合计、实缴费用合计等信息）。

◆账户余额查询：客户可查询话费单上的余额。

◆用户资料查询：客户可以查阅个人资料。

◆电信业务查询：客户可以实时了解电信部门的各项活动。

1.2.2 计费模块◆缴费信息：管理员可根据用户所缴的话费进行计费，并反馈给用户，用户在交费的同时可打印发票。

1.2.3 基本信息更新模块◆月话费管理：管理员可对每月的话费记录进行逐条添加、更新和删除。

◆客户受理结果：管理员可对客户受理情况的记录进行逐条添加、更新和删除。

1.3 数据流图第0层的数据流图第1层的数据流图第2层的数据流图缴费信息单 第3层的数据流图1.2.1查询套餐 显示套餐生成 套餐信息表 信息第3层的数据流图生成 余额信息单第3层的数据流图生成 资料信息单第3层的数据流图1.4 数据字典✓ 客户资料表[1] 手机 {数字} 11[2] 客户｛汉字|字母｝10[3] 联系｛数字｝11[4] 联系地址｛汉字｝20[5] 套餐名称｛汉字｝20[6] {数字}18✓收费员资料表[1] 收费员编号｛数字|字母｝8[2] 收费员｛汉字|字母｝10[3] 年龄｛数字｝2[4] 密码{数字|字母} 10✓应收款额表[1]手机{数字} 11[2]｛汉字|字母｝10[3]账户编号｛汉字|字母｝10[4]应收费用{数字} 10[5]余额 {数字} 10✓话费信息表[1] 手机 {数字} 11[2] ｛汉字|字母｝10[3] 通话费{数字} 8[4] 业务费 {数字} 8[5] 套餐名称{汉字｝20[6] 费用合计{数字}8✓通话信息[1] 来电 {数字} 11[2] 去电 {数字｝11[3] 通话时间 {数字} 8[4] 单位费用 {数字} 8[5] 通话费用 {数字｝8[6] 手机 {数字} 11✓客户受理表[1] 手机 {数字} 11[2] ｛汉字|字母｝10[3] 受理时间 {数字} 8[4] 受理容 {汉字} 202、概念设计（泽阳）2.1 概述根据需求将分析阶段形成的分析说明书，把用户的信息需求抽象为信息结构即概念模型的过程就是概念设计。

设计概念结构通常有自顶向下、自底向上、逐步扩、混合策略4类方法。

其中最常用的策略是自底向上方法。

下面就用E-R图来描述显示世界的概念模型。

2.2 系统E-R图◆实体及其属性图图2-1：客户实体图图2-2：收费员体图图2-3：通话信息实体图图2-4：收费信息实体图图2-5：客户受理实体图◆实体及其联系图图2-6：系统实体联系图◆完整的实体联系图图2-7：完整系统实体联系图2.3 创建各表●创建客户资料表客户资料表记录了客户的基本信息，设计表如下页图：●创建收费员资料表收费员资料表记录了收费员的基本信息，设计表如下图：●创建应收款额表应收款额表记录了客户的缴费及余额信息，设计表如下图：●创建话费信息表话费信息库表记录了客户的基本话费信息，设计表如下页图：●创建通话信息表通话信息表记录了客户通话记录的信息，设计表如下图：创建客户受理表客户受理表记录了客户受理结果的基本信息，设计表如下图：3、逻辑设计（吴彬彬）3.1 概述逻辑设计的任务就是把概念结构设计阶段的设计好的基本E-R图转换为选用的DBMS产品所支持的数据模型项符合的逻辑结构设计。

设计逻辑结构时一般要分为两步进行如下图所示：图3-1：逻辑结构设计步骤●将概念结构转换为关系模型。

●对数据模型进行优化。

3.2 E-R图向关系模型的转换3.2.1 实体及其属性客户：｛客户，联系，联系地址，套餐名称，手机，手码｝收费员：｛收费员编号，，年龄，密码}应收款额：｛手机，客户，应收费用，余额｝话费信息库：｛手机，客户，通话费，新业务费，费用合计，套餐名称｝通话信息：{手机，来电，去电，单位费用，通话时长，总费用}客户受理：｛手机，客户，受理容，受理时间｝3.2.2 联系的属性使用：｛手码｝；计费：｛密码｝3.2.3根据转换规则将实体转换为关系模式●本实例中没有1：1关系●对于1：n联系，将它们与n端的关系模式合并。

该关系模式中应加入1端的码和联系本身的属性。

客户：｛客户，联系地址，套餐名称，手机，手码｝收费员：｛收费员编号，，年龄，手机，密码｝●对于m：n的联系，必须单独转换为一个关系模式，属性由各实体的码和联系本身的属性组成，关系的码为组成联系各实体的码的组合。

支付：｛客户，联系，收费员编号｝处理：｛手机，收费员编号｝3.2.4 计费系统的E-R图，转换为以下6个关系模式：客户：｛手机，联系地址，套餐名称，客户，手码｝收费员：｛收费员编号，，年龄，手机，密码｝应收款额：｛手机，应收费用，余额｝话费信息：｛手机，客户，通话费，费用合计，套餐名称｝客户受理：｛手机，收费员编号，客户，受理容，受理时间｝通话信息：{手机，来电，去电，单位费用，通话时长，总费用}对以上关系数据模型进行优化，发现它们都属于第三式。

4、物理设计（金军地）4.1 概述通常关系数据库无力设计的容主要包括：●为关系选择存取方法，建立存取路径；●确定数据库存储结构，即确定关系、索引、聚日志、备份等数据的存储安排和存储机构；●确定系统配置；4.2 实现语句create database PhoneSysterm /*创建数据库*/create table 客户/*创建客户表*/( 手机 CHAR(11) PRIMARY KEY,客户 VARCHAR(10)，NOT NULL联系 VARCHAR(11), NOT NULL联系地址 VARCHAR(20),套餐名称 VARCHAR(20),手码 VARCHAR(10), NOT NULL);create table 收费员/*创建收银员表*/( 收费员编号 CHAR(8) PRIMARY KEY,VARCHAR(10), NOT NULL年龄 CHAR(2),密码 VARCHAR(10), NOT NULL);create table 应收款额/*创建收款表*/ ( 手机 CHAR(11) PRIMARY KEY,客户 VARCHAR(10),应收费用 varchar,余额 FLOAT(10));create table 话费信息/*创建话费信息表*/ ( 手机 CHAR(11) PRIMARY KEY,客户 VARCHAR(10), NOT NULL通话费 VARCHAR(10)，新业务费 VARCHAR(10)，套餐名称 VARCHAR(20),费用合计 varchar);create table 通话信息/*创建通话信息表*/ ( 手机 CHAR(11) PRIMARY KEY,来电 VARCHAR(11),去电 VARCHAR(11),通话时长 varchar(8),通话时间 DateTime (8)，单位费用 FLOAT(3),总费用 FLOAT(10));create table 客户受理/*创建客户受理表*/ ( 手机 CHAR(11) NOT NULL,收费员编号 CHAR(8) NOT NULL客户 VARCHAR(10)，受理容 VARCHAR(100),受理时间 DateTime(8)PRIMARY KEY(手机，收费员编号));5、用户界面设计系统的主界面设计如下：6、程序代码设计与分析6.1 创建表及数据库Create database PhoneSystermgouse PhoneSystermCreate table KH(kMobileNumber Char(11) primary key, kName varchar(10) notnull,kPhoneNumber varchar(11) not null, kAddress varchar(20), PackageName varchar(20),kCiphe varchar(10) not null)Create table SFY(sno Char(8) primary key,sname varchar(10) not null,sage int,sCiphe varchar(10) not null)Create table YSK(kMobileNumber Char(11) primary key,kName varchar(10),Fee varchar(10),overage Varchar(10))Create table HFinformation(kMobileNumber Char(11) primary key,kName Varchar(10) not null,TFee varchar(10),Newcost varchar(10),PackageName varchar(20),Feetotal Varchar(10))Create table THinformation(kMobileNumber char(11) primary key,CallerID Varchar(11),Outgoingcall varchar(11),Calltime varchar(8),Talktime Datetime,unitcost varchar(3),Totalcost varchar(10))Create table KHSL(kMobileNumber char(11) not null,sno char(8) not null, kName varchar(10),Content varchar(100),Time Datetime,primary key(kMobileNumber,sno)) Go6.2 建立存储过程向表中插入、修改、删除数据6.2.1 插入数据的存储过程Insert into KH Values( '', '王大力','','', '', 125648)Insert into KH Values('', '明', '',’','', 859765）Insert into KH Values('', '王二' ,'','','7', 156358）Insert into SFY Values（'200010', '阳', 26, 512）Insert into SFY Values（'200011', '敏', 23, 513）Insert into SFY Values（'200012', '石猛', 28, 515）Insert into YSK Values（'', '王大力', 82, 43.8)Insert into YSK Values（'', '明', 49, 59.8)Insert into YSK Values（'', '王二',36,35.6)Insert into HFinformation Values('', '王大力', 38, 13 '套餐一',51) Insert into HFinformation Values（'', '明',41,19, '套餐二',60）Insert into HFinformation Values（'', '王二',45,20,’套餐三’,65）Insert into THinformation Values （'', '', '','48 minutes', '11:23',0.8,39）Insert into THinformation Values（ '', '', '','53 minutes', '18:45',0.8,49）Insert into THinformation Values ('', '', '','55 minutes', '11:56',0.8,69)Insert into KHSL Values('','200010',’王大力',取消业务,'12:18')Insert into KHSL Values(‘’,’200011’,’明’,’开通业务,’11:25’)Insert into KHSL Values(‘’,’200012’,’王二’,’修改密码’,’9:45’)6.2.2 修改数据的存储过程Update SFYSET Sage = Sage + 66.2.3 删除数据的存储过程Delete from SFYWhere sno=’200010’select *from SFY6.3 建立存储过程实现查询6.3.1 建立存储过程实现单表查询（5个）1、查询收费员资料表中所有成员的资料Select *From SFY2、查询收费员编号为“200010”的信息，列出收费员的、年龄、密码Select sname, sage, sCipheFrom SFYWhere sno=’200010’3、查询收费员资料表中年龄在23岁和26岁的收费员Select snameFrom SFYWhere sage in(23,26)4、查询收费员的平均年龄Select avg(sage)From SFY5、在客户表中查询为王大力的密码Select kCipheFrom KHWhere Kname=’王大力’6.3.2 建立存储过程实现连接查询（5个）1、查询收费员为“200010”的客户信息，列出客户的、手机Select KHSL.Kname, KHSL.kMobileNumberFrom KH, KHSLWhere KH.kMobileNumber=KHSL. kMobileNumber and sno='200010'2、查询为“”的客户信息，列出客户的、手机和联系。