电话计费管理系统数据库设计.doc

课 程 设 计 课程名称_数据库课程设计_ 题目名称_电话计费管理系统_学生学院_计算机学院_专业班级_ _学 号_学生姓名_ _指导教师_左亚尧_2013年 1月 日目录1、 需求分析31.1 总体描述31.2 功能需求31.2.1 查询模块31.2.2 计费模块41.2.3 基本信息更新模块41.3 数据流图41.4 数据字典72、 概念设计82.1 概述82.2 系统E-R图82.3 创建各表123、 逻辑设计143.1 概述143.2 E-R图向关系模型的转换143.2.1 实体及其属性143.2.2 联系的属性153.2.3 根据转换规则将实体转换为关系模式153.2.4 电话计费系统的E-R图，转换为以下6个关系模式：154、 物理设计164.1 概述164.2 实现语句165、 数据库实施185.1 概述.185.2 应用系统的开发.186、 程序代码设计与分析196.1 创建表及数据库196.2 建立存储过程向表中插入、修改、删除数据206.2.1 插入数据的存储过程206.2.2 修改数据的存储过程236.2.3 删除数据的存储过程246.3 建立存储过程实现查询246.3.1 建立存储过程实现单表查询（5个）246.3.2 建立存储过程实现连接查询（5个）276.3.3 建立存储过程实现嵌套查询（2个）306.4 视图建立317、 心得体会328、 参考文献32电话计费管理系统1、 需求分析1.1背景随着电信运营领域垄断因素的逐步消除，以及中国加入WTO后所面临的开放的电信市场，我国电信领域的竞争日益激烈。电信市场的竞争逐步从简单的价格战转向高层次的服务竞争，运营商把提高服务能力作为核心竞争力。计费系统作为业务运营支撑系统的基础，其准确性和有效性至关重要，计费系统的错误将直接影响结算、账务及客户管理系统的处理结果。由于我国电信用户的基数很大，计费系统任何微小的偏差所造成的损失都是巨大的。该系统信息来源主要有管理员添加，方便网站管理员的查询和管理。该系统的任务是方便，灵活的管理用户的各项信息。1.2 总体描述开发一套能够适用于电话计费管理系统，能够使电话的计费管理达到方便，易于管理，其管理人员可以对用户和电话费等信息内容进行查阅，修改等管理； 本系统实现的是一个独立的系统，在数据库的基础上，实现对电话计费的管理。对电信部门电话计费业务进行调查，设计的系统要求：l 能用关系数据库理论建立几个数据库文件，来存储用户信息、收费员信息和收费信息等资料。l 能够记录通话信息，如来电号码、去电号码、通话时长、通话费用，查询费用账单等信息，具有对各种数据文件装入和修改数据的功能。l 能在用户交费同时打印发票。l 有多种查询和统计功能。l 能通过统计制定未来邮局服务计划方案。1.3 功能需求组织结构对组织结构的分析有助于分析业务范围与业务流程。邮局电话计费管理系统的组织结构如图1所示。图1 邮局电话计费管理系统组织结构简图其中，管理者是管理客户受理表、收费信息和收费人；客户受理表是用户提建议的表单；收费信息是收费人收取用户是产生的记录；收费人收取用户的费用；用户可以查看自己的收费信息，也可以提建议1.3.1 查询模块u 月花费查询：客户可对每月的话费进行查询（每项记录包括通话费、 新业务费、费用合计、实缴费用合计等信息）。u 账户余额查询：客户可查询话费单上的余额。u 用户资料查询：客户可以查阅个人资料。u 电信业务查询：客户可以实时了解电信部门的各项活动。1.3.2 计费模块u 缴费信息：管理员可根据用户所缴的话费进行计费，并反馈给用户，用户在交费的同时可打印发票。1.3.3 基本信息更新模块u 月话费管理：管理员可对每月的话费记录进行逐条添加、更新和删除。u 客户受理结果：管理员可对客户受理情况的记录进行逐条添加、更新和删除。1.4 数据流图客 户客 户 登入申请 结果通知 系 统 收 费 员收 费 员 结果通知 登入申请 第0层的数据流图客 户客 户电 话 计 费 系 统 查询申请 1 话 费 查询结果 结果通知 查 询 子 系 统收 费 员 收 费 员 2 登入申请 话 费 更新结果 管 理 子 系 统 第1层的数据流图 1.1 生 成客户查询登入缴费 缴费信息单 信息 手机号码 1.2密 码 套餐 生 成 套餐信息单信息 1.3 生 成 账户余额 余 额 单 查询 1.4 生 成 用户资料 资料报告单 查询 第2层的数据流图 客 户 记录年份 1.1.1 生成 记录月份 计 费 缴费信息单 付 款 第3层的数据流图客 户 1.2.1 查询套餐 显示套餐 生成 套餐信息表 信息 第3层的数据流图客 户 1.3.1 查询余额 显示余额 生成 余额信息单信息 第3层的数据流图客 户 1.4.1 查询资料 显示资料 生成 资料信息单信息 第3层的数据流图收 费 员收费人员登入 2.1 收费员ID 客户受理 显示结果 情况 收 费 员密 码 2.2 更新结果 客户费用 信息 第2层的数据流图1.5 数据字典 客户资料表1 手机号码 数字 112 客户姓名汉字|字母103 联系电话数字114 联系地址汉字205 套餐名称汉字206 身份证号码 数字18 收费员资料表1 收费员编号数字|字母82 收费员姓名 汉字|字母103 年龄数字24 密码数字|字母 10 应收款额表1 手机号码数字 112 姓名汉字|字母103 账户编号汉字|字母104 应收费用数字 105 余额 数字 10 话费信息表1 手机号码 数字 112 姓名汉字|字母103 通话费数字 84 业务费 数字 85 套餐名称汉字206 费用合计数字8 通话信息1 来电号码 数字 112 去电号码 数字113 通话时间 数字 84 单位费用 数字 85 通话费用 数字86 手机号码 数字 11 客户受理表1 手机号码 数字 112 姓名汉字|字母103 受理时间 数字 84 受理内容 汉字 20（1）数据项客户资料表列 名数据类型长度允许空 主键kehunameChar 8phoneno Char 11city Char 20packagename Char 20phonepasswd Char 20收费员资料表列 名 数据类型长 度允许空 主键 cashierno Char 11cashiername Char 8age Char 8passwd Char 20客户受理表列 名 数据类型长 度允许空 主键kehuname Varhar 15phoneno Char 15admissibilitycontent Varchar 50admissibilitytime Date time 8通话信息列 名数据类型长 度允许空calleridChar 11 outgoingid Char 11 talktime INT 8 unitcost INT 8 数据结构编号数据结构名属性1客户信息姓名、手机号码、帐户余额、套餐名称、身份证号2收银员信息姓名、工作证号、年龄、密码3通话信息来电号码、去电号码、通话时间长、单位费用、通话日期时刻、通话费用4收费信息收费员工作号、手机号码、客户姓名、受理时间数据流编号数据流明输入输出1登录申请客户用户信息2登录申请收银员收银员信息3查询资料客户资料信息4查询套餐客户套餐信息5客户受理情况收银员受理信息数据存储数据存储名输入数据流输出数据流客户信息客户基本信息客户信息收银员信息收银员基本信息收银员基本信息客户费用信息客户各项费用信息客户费用信息客户缴费信息客户缴费消息客户缴费信息处理过程处理过程名输入数据流输出数据流说明部分登录查看终端客户信息登录查看终端收银员信息客户受理客户要求客户受理情况客户缴费终端客户显示余额信息客户信息客户余额信息显示套餐信息客户信息客户套餐信息2、 概念设计2.1 概述根据需求将分析阶段形成的分析说明书，把用户的信息需求抽象为信息结构即概念模型的过程就是概念设计。设计概念结构通常有自顶向下、自底向上、逐步扩张、混合策略4类方法。其中最常用的策略是自底向上方法。下面就用E-R图来描述显示世界的概念模型。2.2 系统E-R图基本实体和联系首先确定实体类别以及它们各自的属性构成，指出实体标识符，并尽量规范属性名，避免同名异义或异名同义。确定实体后，就可以分析实体之间的联系。可以很容易确定，用户、收费员、收费信息、管理者、客户受理是不同的实体。用户的属性：用户编号、姓名、电话号码、身份证号、申请日期，帐户余额；收费员的属性：工号、姓名、性别、生日；用户与收费员发生收费联系。这里一个用户能在多个收费员出缴费，一个收费员可以收取多个用户费用，它们是m：n联系。当联系发生时，产生收费信息属性。收费信息属性：收费流水号，收费员编号，用户编号，收费日期、实收费用；管理者属性：管理者名称、对应密码和权限编号；任务概述开发一套能够适用于电话计费管理系统，能够使电话的计费管理达到方便，易于管理，其管理人员可以对用户和电话费等信息内容进行查阅，修改等管理； 本系统实现的是一个独立的系统，在数据库的基础上，实现对电话计费的管理。功能定义能用关系数据库理论建立几个数据库文件存储用户信息、收费员信息和收费信息等资料用户；具有对各种数据文件装入和修改数据的功能；能在用户交费同时打印发票；能通过统计制定未来邮局服务计划方案；有多种查询和统计功能。系统维护：如数据安全管理（含备份与恢复）、操作员管理、权限设置等；u 实体及其属性图客 户账户余额姓名身份证号手机号码账户余额套餐名称 图2-1：客户实体图收费员工作证号姓名登陆密码性别所在单位 图2-2：收费员体图通话记录去电号码通话时间长单位费用通话日期时刻通话费用来电号 来电号码 图2-3：通话信息实体图收费信息手机号码收费员工作号客户姓名收费日期实收费用套餐名称 图2-4：收费信息实体图客户受理信息手机号码收费员工作号客户姓名受理时间受理内容是否成功 图2-5：客户受理实体图u 实体及其联系图客户收费员查询支付处理话费信息计费收费应收款项客户受理 图2-6：系统实体联系图u 完整的实体联系图客户收费员查询支付处理话费信息计费收费应收款项客户受理姓名手机号码身份证号手机号码性别姓名工作证号手机号码受理内容受理时间应收费用手机号码话费总额1N11NNMMN 图2-7：完整系统实体联系图2.3 创建各表l 创建客户资料表客户资料表记录了客户的基本信息，设计表如下页图：列名字段名数据类型长度允许空客户姓名kNameVarChar10联系电话kPhoneNumberVarChar11联系地址kAddressVarChar20套餐名称PackageNameVarChar20手机号码kMobileNumberChar11手机密码kCipheVarChar10l 创建收费员资料表收费员资料表记录了收费员的基本信息，设计表如下图：列名字段名数据类型长度允许空收费员编号snoChar8姓 名snameVarChar10年 龄sageInt2密 码sCipheVarChar10l 创建应收款额表应收款额表记录了客户的缴费及余额信息，设计表如下图：列名字段名数据类型长度允许空手机号码kMobileNumberChar11客户姓名kNameVarChar10应收费用FeeVarChar10余 额OverageVarChar10l 创建话费信息表话费信息库表记录了客户的基本话费信息，设计表如下页图：列名字段名数据类型长度允许空手机号码kMobileNumberChar11客户姓名kNameVarChar10通话费TFeeVarChar10新业务费NewcostVarChar10费用合计FeetotalVarChar10套餐名称PackageNameVarChar20l 创建通话信息表通话信息表记录了客户通话记录的信息，设计表如下图：列名字段名数据类型长度允许空手机号码kMobileNumberChar11来电号码CallerIDVarChar11去电号码OutgoingcallVarChar11通话时长CalltimeVarChar8通话时间TalktimeVarChar8单位费用unitcostVarChar3总 费 用TotalcostVarChar10l 创建客户受理表客户受理表记录了客户受理结果的基本信息，设计表如下图：列名字段名数据类型长度允许空客户姓名kNameVarChar10收费员编号snoChar8手机号码kMobileNumberVarChar11受理内容ContentVarchar100受理时间TimeVarchar103、 逻辑设计3.1 概述逻辑设计的任务就是把概念结构设计阶段的设计好的基本E-R图转换为选用的DBMS产品所支持的数据模型项符合的逻辑结构设计。设计逻辑结构时一般要分为两步进行如下图所示：概 念 结 构基本E-R图 关系数据模型优化的数据模型转换 规则优化 方法 图3-1：逻辑结构设计步骤l 将概念结构转换为关系模型。l 对数据模型进行优化。3.2 E-R图向关系模型的转换3.2.1 实体及其属性 客户：客户姓名，联系电话，联系地址，套餐名称，手机号码，手机密码收费员：收费员编号，姓名，年龄，密码 应收款额：手机号码，客户姓名，应收费用，余额话费信息库：手机号码，客户姓名，通话费，新业务费，费用合计， 套餐名称 通话信息：手机号码，来电号码，去电号码，单位费用，通话时长， 总费用客户受理：手机号码，客户姓名，受理内容，受理时间3.2.2 联系的属性 使用：手机密码；计费：密码3.2.3 根据转换规则将实体转换为关系模式l 本实例中没有1：1关系l 对于1：n联系，将它们与n端的关系模式合并。该关系模式中应加 入1端的码和联系本身的属性。 客户：客户姓名，联系地址，套餐名称，手机号码，手机密码 收费员：收费员编号，姓名，年龄，手机号码，密码l 对于m：n的联系，必须单独转换为一个关系模式，属性由各实体的 码和联系本身的属性组成，关系的码为组成联系各实体的码的组合。 支付：客户姓名，联系电话，收费员编号 处理：手机号码，收费员编号3.2.4 电话计费系统的E-R图，转换为以下6个关系模式：客户：手机号码，联系地址，套餐名称，客户姓名，手机密码收费员：收费员编号，姓名，年龄，手机号码，密码 应收款额：手机号码，应收费用，余额 话费信息：手机号码，客户姓名，通话费，费用合计，套餐名称 客户受理：手机号码，收费员编号，客户姓名，受理内容，受理时间 通话信息：手机号码，来电号码，去电号码，单位费用，通话时长， 总费用对以上关系数据模型进行优化，发现它们都属于第三范式。3.2.5 关系模式的规范化关系模式属于第三范式，每个非主属性都不传递函数依赖于主关系键。在关系模式中，对于客户模式，手机号码为主属性，其余的为非主属性，非主属性不传递依赖于手机号码，属于第三范式。对于收费员，收费员编号为主属性，姓名，年龄，手机号码，编号，密码为非主属性，它们不传递依赖于主关系键收费编号，属于第三范式。对于客户受理，手机号码和收费员编号为主属性，客户姓名，受理内容，受理时间为非主属性，手机号码、收费员编号，他们不依赖于手机号码、收费员编号，属于第三范式。对于应收款额、话费信息、通话信息，他们的非主属性同样不传递依赖于主属性手机号码，都属于第三范式。4、 物理设计4.1 概述 通常关系数据库无力设计的内容主要包括：l 为关系选择存取方法，建立存取路径；l 确定数据库存储结构，即确定关系、索引、聚日志、备份等数据的 存储安排和存储机构；l 确定系统配置；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、 数据库实施5.1概述 数据库实施阶段包括两项重要的工作，一是数据集的载入，另一项就是应用程序的编码和调式。5.2应用系统的开发系统的主界面设计如下： 6、 程序代码设计与分析6.1 创建表及数据库Create database PhoneSysterm gouse PhoneSystermCreate table KH(kMobileNumber Char(11) primary key, kName varchar(10) not null,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( 王大力,上海 20125231115, 125648)Insert into KH Values 李明, 北京20125231116, 859765）Insert into KH Values 王二 ,杭州201252311167, 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 Values200010,王大力,取消业务,12:18)Insert into KHSL Values200011,李明,开通业务,11:25)Insert into KHSL Values200012,王二,修改密码,9:45)6.2.2 修改数据的存储过程Update SFY SET Sage = Sage + 66.2.3 删除数据的存储过程Delete from SFYWhere sno=200010select *from SFY6.3 建立存储过程实现查询6.3.1 建立存储过程实现单表查询（5个）1、查询收费员资料表中所有成员的资料Select *From SFY 2、查询收费员编号为“200010”的信息，列出收费员的姓名、年龄、密码Select sname, sage, sCipheFrom SFY Where sno=2000103、查询收费员资料表中年龄在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=2000102、查询电话号码为的客户信息，列出客户的姓名、手机号码和联系电话。Select KH.Kname, KH.kMobileNumber, kPhoneNumberFrom KH, KHSLWhere KH.kMobileNumber=KHSL.KmobileNumber and KH.kMobileNumber3、查询收费员编号是“200012”客户的话费信息（手机号码、客户姓名、通话费、新业务费、费用合计、套餐名称）。Select *From HFinformation,KHSLWhere KHSL.kMobileNumber=HFinformation.kMobileNumber AND sno=2000124、查询客户姓名是“王大力”的通话信息（包括来电号码、去电号码、总费用）。Select CallerID, Outgoingcall, TotalcostFrom KH, THinf