手机话费查询系统

手机话费查询系统 姓名:陈君文 班级:12计科(2)班 学号:201210510213 手机话费查询系统 1、需求 1.1 编写目的 使用该数据库使用户和通信工作这更好的管理和查询手机话费 1.2 背景 项目名称:手机话费查询系统 项目提出者:中国移动，中国联通，中国电信总公司 项目开发者:项目开发人员 项目鉴定者:各通信总公司 项目开始时间:××××-××-××。 1.3 定义 系统数据图的一些定义:数据流图是结构化分析方法中使用的工具,它以图形的方式描绘数据在系统中流动和处理的过程，由于它只反映系统必须完成的逻辑功能，所以它是一种功能模型。 基本符号 :圆框，示加工;学生表示数据的源点; :方框，表示数据的源点或数据的终点; :箭头，表示被加工数据的路径和流向，即数据流; The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this :双杠，表示数据存储，可以是一个纪录或一个数据文件，可用名词或名 1.4 目标 当用户使用手机时所产生的费用需由手机营业厅和用户知道并且缴纳相应的手机话费费用，需要有相应的话费打印凭证。 对手机话费查询系统可以分为三类: 用户:用户需要对自己所消费的信息由明确的细节了解并且可以随时的查询自己的消费记 录。 营业员:工作人员可以借助手机话费查询系统自动查寻到用户的消费记录，可以自动的管理并计算出用户的消费金额，帮助营业员管理用户信息等。 管理人员:管理人员需要了解该手机话费查询系统的缺点和不足，应具备一定的数据库知识，不过只要了解就行，这些人员负责对数据库中重要数据及基本数据的更新，如对静态表的重新定义，普通用户的权限分配。 1.5 功能分析 ?数据源点和数据终点 数据源点和数据终点用方框表示，它是系统之外的实体，可以是人、事、物、部门或其他系统，如工作人员和用户。 ?加工(数据处理变换) 加工用圆框表示，是对数据进行处理的逻辑单元，它接受若干输入数据流，通过加工内部产生的输出数据流。如查询手机用户基本信息。 ?数据流 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 数据流用带数据流标识的箭头表示，表示系统处理的数据对象和数据流动的方向。数据流的方向可以是:从一个加工流向另一个加工、从加工流向数据存储或数据存储流向加工、从源点流向加工或从加工流向终点，如箭头所表示就是流向。 1.6需求分析 在手机话费查询系统中，主要有手机用户和工作人员两个操作实体，对于普通的手机用户来说，该系统仅允许他们作一个操作:即可以使用该系统通过手机号与密码发出查询请求, 查询该手机所剩余的话费。工作人员必须通过账号与相对应的密码登陆系统才能有权限完成以下的各种功能。工作人员的登陆可以统计并查看所有的手机用户信息。 1.6.1通过对手机话费查询的各个步骤、所需的各项信息等的分析，我们规定: 手机话费管理系统中实体的联系如下: ,一个用户可以注册多个手机号码，一个手机号码只能被一个用户注册。用户和号码之间是一对多的联系。 ,一个用户可以拥有多张银行卡，一张银行卡只能属于一个用户，用户和银行卡之间是一对多的联系。 ,一张银行卡可以为多个号码缴费，一个号码可以由多张银行卡缴费，银行卡和号码之间是多对多联系。 ?一个号码可以办理多个业务，一个业务可以被多个号码办理，号码和业务之间是多对多的联系。 ?一个帐目包含多种业务，一种业务只能产生一种业务，帐目和业务之间是一对多的联系。 1.6.2数据字典 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ?数据项 ?用户信息 ?数据项名:身份证号码 含义说明:用户的唯一标识 别名:ID_no 类型:字符型 长度:18 ?数据项名:用户姓名 含义说明:手机用户的姓名 别名:user_name 类型:字符型 长度:8 ?数据项名:用户密码 含义说明:用户查询信息时的密码 别名:user_password 类型:字符型 长度:10 ?数据项名:用户性别 含义说明:手机用户的性别 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 别名:user_sex 类型:字符型 长度:2 取值范围:男\女 ?数据项名:用户地址 含义说明:用户的家庭住址 别名:user_addr 类型:字符型 长度:20 ?数据项名:用户状态 含义说明:手机号码的开/停状态 别名:user_state 类型:字符型 长度:2 取值范围:开/停 ?工作人员信息 ?数据项名名:工作人员编号 含义说明:工作人员唯一标识 别名:staff_id The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 类型:字符型 长度:10 ?数据项名:工作人员姓名 含义说明:工作人员的姓名 别名:staff_name 类型:字符型 长度:10 ?数据项名:工作人员口令 含义说明:操作记录工作人员的登录密码 别名:staff_password 类型:字符型 长度:10 ?数据项名:工作人员性别 含义说明:工作人员的性别 别名:staff_sex 类型:字符型 长度:2 取值范围:男\女 ?银行卡信息 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ?数据项名:银行卡号 含义说明:银行卡的唯一标识 别名:bank_card 类型:字符型 长度:18 ?数据项名:余额 含义说明:银行卡的余额 别名:bank_balance 类型:money型 长度:8 ?手机信息 ?数据项名:手机号码 含义说明:用户注册的手机号码 别名:phone_no 类型:字符型 长度:12 ?业务信息 ?数据项名:业务名称 含义说明:手机所能办理业务的名称 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 别名:YEWU_name 类型:字符型 长度:8 ?数据项名:业务金额 含义说明:业务对于的金额 别名:YEWU_sum 类型:money型 长度:8 ?数据项:业务编号 含义说明:业务的唯一标示 别名:YEWU_no 类型:字符型 长度:6 ?账目信息 ?数据项:消费月份 含义说明:手机消费的具体月份 别名:consume_month 类型:字符型 长度:4 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ?数据项名:账目编号 含义说明:账目的唯一标示 别名:account_no 类型:字符型 长度:10 ?数据项名:业务费 含义说明:用户注册手机号码的时间 别名:YEWU_fee 类型:money型 长度:8 ?话费信息 ?数据项名:话费编号 含义说明:话费的唯一标示 别名:cost_no 类型:字符型 长度:10 ?数据项:话费余额 含义说明:手机当前剩余的话费 别名:balance The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 类型:money型 长度:8 ?数据项名:手机号码 含义说明:用户注册的手机的号码 别名:phone_no 类型:字符型 长度:12 ?注册信息 ?数据项名:注册时间 含义说明:用户注册手机号码的时间 别名:register_time 类型:日期型 ?数据项名:手机号码 含义说明:用户注册的手机的号码 别名:phone_no 类型:字符型 长度:12 ?办理信息 ?数据项名:办理时间 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 含义说明:手机号码办理业务的时间 别名:BANLI_time 类型:日期型 ?缴费信息 ?数据项名:缴费金额 含义说明:银行卡缴费时的金额 别名:pay_sum 类型:money型 长度:8 ?数据项名:缴费时间 含义说明:卡缴费的时间 别名:pay_time 类型:日期型 2、数据结构 数据结构反映了数据之间的组合关系。 一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成。对数据结构的描述，数据结构描述,,数据结构名，含义说明，组成:,数据项或数据结构,,。 ?工作人员信息结构={工作人员，此结构描述工作人员的具体信息，组成:{工作人员编号，工作人员姓名，工作人员口令，工作人员性别}} ?用户银行卡结构={银行卡，此结构描述充值时银行卡的具体信息，组成:The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this {银行卡号，余额}} ?话费结构={话费，此结构描述当前剩余话费的具体信息，组成:{手机号码，话费余额}} ?手机用户个人信息结构={用户，此结构描述用户的具体信息，组成:{身份证号码，用户姓名，用户性别，用户地址，用户密码，用户年龄}} ?业务结构{业务，此结果描述用户所能办理业务的具体信息，组成:{业务编号，业务名称，业务金额}} ?帐目结构{帐目，此结果描述用户办理的业务消费的具体信息，组成:{帐目编号，消费月份，业务费}} 3、数据流 ?用户注册号码={注册号码，用户到工作人员处注册手机号码，此数据来源于用户，流向用户个人信息存储，组成:{用户，工作人员}} ?工作人员注册用户={注册用户，用户注册号码时工作人员添加新用户的注册信息，此数据来源于工作人员，流向用户个人信息存储，组成:{工作人员，用户}} ?产生个人信息={个人信息，用户注册经工作人员确认的号码后产生的用户个人信息，此数据来源于用户，流向用户信息存储，组成:{工作人员，用户}} ?用户信息{提供用户信息，工作人员修改用户信息时的信息来源，此信息来源于用户个人信息存储，流向工作人员，组成:{工作人员}} ?工作人员修改信息={修改后的信息，工作人员修改后的个人信息，此信息来源于工作人员，流向用户信息存储，组成:{工作人员}} ?查询剩余话费={提供话费信息，用户查询剩余话费时的信息来源，此数据来源于话费，流向用户，组成:{用户，话费}} ?提供个人信息={提供个人信息，充值时用户信息和银行卡信息进行综合，The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 此数据来源于用户个人信息存储，流向话费信息存储，组成:{用户}} ?产生银行卡余额={银行卡余额，综合用户信息与银行卡信息后产生银行卡余额进行缴费，此数据来源于用户和银行卡，流向话费信息存储，组成:{用户，银行卡}} ?产生缴费金额={缴费金额，银行卡剩余金额中支出部分金额缴纳话费，此数据来源于用户和银行卡，流向话费信息存储，组成:{用户，银行卡}} ?用户办理业务={业务办理，用户办理手机业务，此数据来源于用户，流向个人业务信息存储，组成{用户，业务}} ?具体业务={具体业务，用户可办理的所有业务，此数据来源于业务，流向个人业务信息存储，组成{业务}} ?办理的业务={办理的业务，用户确认办理的业务，此数据来源于业务和用户，流向个人业务信息存储，组成:{业务，用户}} ?产生的帐目={帐目，用户办理的所有业务的消费帐目，此数据来源于业务信息存储，流向帐目信息存储，组成:{业务，用户}} ?产生帐目信息={帐目信息，用户办理的所有业务产生的帐目信息，此数据来源于帐目信息存储，流向帐目，组成{帐目}} ?用户查询剩余话费={提供账目信息，用户查询账目信息时提供账目信息，此数据来源于话费信息存储，流向用户，组成:{用户, 话费}} ?工作人员查询业务={提供业务信息，工作人员查询所有业务时提供业务信息，此数据来源于业务，流向工作人员，组成:{业务，工作人员}} ?工作人员查询帐目={提供帐目信息，工作人员查询所有用户的帐目信息时提供帐目信息，此数据来源于帐目，流向工作人员，组成:{帐目，工作人员}} ?工作人员查询个人业务={提供个人业务信息，工作人员查询用户个人业务时提供个人业务信息，此数据来源于个人信息存储，流向工作人员，组成:{工作人员}} The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 4、数据存储 ?用户个人的信息={用户个人信息存储，用于存储手机用户的个人及话费信息等，信息来源工作人员，去向用户，组成:{用户，工作人员}} ?手机的话费信息={话费信息存储，用于存储手机的话费记录，信息来源用户和银行卡，去向话费，组成:{用户，银行卡，话费}} ?业务信息={个人业务信息存储，用于存储提供的所有业务，信息来源业务，去向帐目，组成:{用户，业务}} ?帐目信息={帐目信息存储，用于存储用户所办业务的具体帐目信息，信息来源业务信息存储，去向帐目，组成:{业务，用户}} 5、处理过程 ?用户对号码注册={注册，用户对手机号码进行注册，输入:{注册号码，注册用户}，输出:{个人信息}，处理:{用户进行号码注册，工作人员确认注册后，产生用户个人信息}} ?用户缴纳话费={缴费，用户使用银行卡为手机缴费。输入:{提供个人信息，银行卡余额，缴费金额}，输出:{当前话费信息}，处理:{，首先综合用户与银行卡信息，然后从银行卡中扣除缴费金额，更新话费信息}} ?帐目产生{生成帐目，用户办理的所有业务具体消费帐目，输入:{办理的业务}，输出:{帐目信息}，处理:{统计用户办理业务后，产生具体的帐目信息}} 软硬件平台设计 (1) 软件平台: windows操作系统、Sql-Server2000数据库 (2) 硬件平台:普通微机 6、概念设计 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 6.1概念结构设计思想 概念结构设计是将需求分析阶段所得到的用户需求抽象为信息结构，能够真实、充分的反映现实世界，包括事物与事物之间的联系，能满足用户对数据的处理需求，易于理解，易于更改，易于向关系、网状、层次等各种数据模型转换。概念结构是各种数据模型的共同基础，它比数据模型更独立于机器、更抽象，是用来描述某个特定组所关心的信息结构，从而更加稳定。 6.2概念结构设计方法 概念桔构设计通常有四种方法，自顶向下、自底向上、逐步扩张、混合策略等四种方法。通常采用自底向上的方法，自顶向下地进行需求分析，然后再自底向上地设计概念结构。自底向上的设计概念结构的方法通常分为两步:第一步是抽象数据并设计局部视图，第二部是集成局部视图，得到全局的概念结构。 基于这些，本次课程设计采用自底向上的方法进行概念结构设计。根据需求分析阶段的需求分析设计思想、数据字典以及数据流图所提供的信息，采取分类、聚集的抽象方法，对需求分析的结果进行细化、补充和修改。形成实体、实体的属性，标示实体的码，确定实体之间的联系类型(1:1，1:n，m:n)，设计分E—R图，然后将所有的分E—R图综合成一个系统的总E—R图。最后根据总的E—R图，利用利用PowerDesigner设计出概念模型。 根据需求分析阶段所提供的系统信息，确定系统中的实体、属性以及实体之间的联系。确定实体和属性的两条准则:1.作为“属性”不能再具有需要描述的性质。“属性”必须是不可分割的数据项，不能包含其他属性。2.“属性”不能与其他实体具有联系，即E—R图中所有表示的联系是实体之间的联系。 ?设计局部E—R图 ?用户实体信息，如图2.2所示。 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 用户姓名 身份号码用户地址 用户 用户性别用户密码 用户状态 图2.2用户实体信息 用户实体定义。用户:{身份证号码，用户姓名，用户密码，用户性别，用户地址，用户状态} ?工作人员实体信息，如图2.3所示。 工作人员编号工作人员性别 工作人员 工作人员姓名工作人员口令 图2.3工作人员实体信息 工作人员实体定义。工作人员:{工作人员编号，工作人员姓名，工作人员性别，工作人员口令} ?银行卡实体信息，如图2.4所示。 卡号余额银行卡 图2.4银行卡实体信息 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 银行卡实体定义。银行卡:{银行卡号，余额} ?话费实体信息，如图2.5所示。 话费余额手机号码话费 话费编号 图2.5话费实体信息 话费实体定义。话费{话费编号，手机号码，话费余额} ?业务实体信息，如图2.6所示。 业务编号 业务名称业务金额业务 图2.6业务实体信息 业务实体定义。业务{业务编号，业务名称，业务金额} ?账目实体信息，如图2.7所示。 账目编号 消费月份业务费账目 图2.7账目实体信息 账目实体定义。账目{账目编号，消费月份，业务费} The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ?手机信息实体，如图2.8所示。 手机号码 手机 图2.8手机实体信息 手机实体定义。手机{手机号码} ?银行卡实体和话费实体之间的联系，如图2.9所示。 缴费金额 缴费银行卡话费nm 缴费时间 图2.9银行卡实体和话费实体之间的缴费联系 用户的一张银行卡可以为自己的多部手机缴费，一部手机的缴费可以通过用户的多张不同的银行卡进行。银行卡实体和话费实体之间通过缴费构成多对多的联系。 ?手机实体和业务实体之间的联系，如图2.10所示。 办理时间 办理业务手机 图2.10手机实体和业务实体之间的办理联系 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 一个手机号码可以办理多个业务，一个业务可以被多个手机号码办理。手机实体和业务实体通过办理构成多对多联系。 ?工作人员实体和用户实体之间的联系，如图2.11所示。 注册时间 注册工作人员用户 手机号码 图2.11工作人员实体和用户实体之间的注册联系 一个工作人员可以为用户注册多个手机号码，一个用户的手机号码可以由多个工作人注册。工作人员实体和用户实体之间通过注册构成多对多的联系。 ?集成局部E—R图生成全局E—R图 根据局部E—R图，实体的属性，实体的码，实体之间的联系，设计出的手机话费管理系统E-R图，如下图2.12所示。 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 余额缴费金额缴费时间 p 银行卡缴费卡号 话费编号qs 手机号码话费 使用 身份号码话费余额 手机号码用户地址1用户姓名 s1用户属于手机用户状态 用户密码mm用户性别 注册时间注册手机号码 n办理时间办理 工作人员编号工作人员口令 工作人员业务编号 n 业务名称业务 业务金额工作人员姓名工作人员性别r 1消费账目编号账目 消费月份 业务费 7、逻辑结构设计 7.1 逻辑结构设计 概念结构是独立于任何一种数据模型的信息结构。 逻辑结构设计的任务就是 把概念The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 结构设计阶段设计好的基本 E-R 图转化为与选用 DBMS 产品所支持数据 模型相符合的逻辑结构。 7.2 逻辑结构设计的思想 针对手机话费管理系统 ， 逻辑结构设计采用关系模型转换概念结构， E-R 将 图依照规则转换为关系模型， 为了进一步提高数据库应用系统的性能，再将转换 后的关系模型进行优化， 确定是否要对某些模式进行合并或分解，为物理设计提 供最优的处理。 7.3 E-R 图向关系模型的转换 关系模型的逻辑结构是一组关系模式的集合。E-R 图则是由实体，实体的属 性和实体间的联系三个要素组成。所以将 E-R 图转换为关系模型实际上就是要 将实体，实体的属性和实体间的联系转换为关系模式。转换原则如下: ?实体类型的转换:将每个实体类型转换成一个关系模式，实体的属性及为 关系模式的属 性，实体标识符及为关系模式的键。 ?联系类型的转换，根据不同的情况做不同的处理。 ? 若实体间的联系是 1:1 的，可以在两个实体类型转换成的两个关系模式中的任意一个关系模式的属性中加入另一各关系模式的键和联系类型的属性。 若实体间的联系 1:N 的，则在 N 端实体类型转换成的关系模式中加入 1 端实体类型转换成的关系模式的键和联系类型的属性。 ? 若实体间的联系是 M:N 的，则将联系类型也转换成关系模式，其属性为 两端实体类型的键加上关系类型的属性，而键为两端实体间的组合。 3 个或 3 个以上实体间的一个多元联系可以转换为一个关系模式。与该多 元联系相连的各实体的码以及联系本身的属性均转换为关系的属性， 各实体的码 组成关系的码或关系码的一部分。 ?具有相同码的关系模式可合并。 ? 依照该规则将企业人事管理系统的 E-R 图转换为关系模型如下 ? 将每一个实体转换成一个关系(关系就是给出关系名，写出属性，并标 明该关系的主键) The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ? 工作人员的基本信息(工作人员编号，工作人员姓名，工作人员性别， 工作人员口令) 此为工作人员实体对应的关系模式，主码为工作人员的编号。 ? 用户的基本信息(身份证号码，用户姓名，用户密码，用户性别，用户 地址，用户状态) 此为用户实体对应的关系模式，主码为用户的身份证号码。 ? 银行卡信息(银行卡号，余额) 此为银行卡实体对应的关系模式，主码为银行卡号。 ? 话费信息(手机号码，话费余额) 此为话费实体对应的关系模式，主码为手机号码。 ? 业务信息(业务编号，账目编号，业务名称，业务金额) 此为业务实体对应的关系模式， 主码为业务编号， 业务消费产生账目， n: 为 1 的“消费”联系，所以账目编号为业务信息的外码。 ?账目信息(账目编号，消费月份，业务费) 此为账目实体对应的关系模式，账目编号为主码。 ?缴费(缴费编号，手机号码，缴费金额，缴费时间) 此为联系“缴费”所对应的关系模式，银行卡缴费话费的 n:m“缴费”联系，所 以银行卡号和手机号码联合做“缴费”的主码。 ?办理(业务编号，手机号码，办理时间) 此为联系“办理”所对应的关系模式，手机办理业务的 n:m“办理”联系，所以 手机号码和业务编号联合做“办理”的主码。 ?注册(工作人员编号，身份证号码，注册时间，手机号码) 此为联系“注册”所对应的关系模式， 工作人员为用户注册的 n:m“注册”联系， 所以工作人员的编号和用户的身份证号码联合做“注册”的主码。 ?手机信息(手机号码，身份证号码) 此为手机实体的关系模式，手机号码为主码，手机号The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 码为用户的 n:1 的“属于”关系，所以用户的身份证号码传递过去做手机的外码。 由 E-R 图可知，手机话费管理系统有七个实体和三带属性个联系。可转换 为 10 个关系模型。 由关系依赖知，在工作人员的基本信息、用户的基本的信息、银行卡信息、 话费信息、账目信息、业务信息、手机信息、缴费、办理、注册中，这十个关系 模式中其属性都是不能再分的， 为一阶范式。并且都是由主码唯一决定关系中的 其他属性，不存在部分函数依赖和传递函数依赖，所以这是十个关系都是 3NF。 皆为最优，关系模型的优化结束。 本次设计的数据库在SQL Server 2005上实现，将概念结构设计中的E-R图转换成SQL Server 2005支持的关系数据模型后，结合上述分析得到如下数据表: ID_no CHAR(18) not null primary Key 身份证号码 user_name CHAR(8) 用户姓名 user_password CHAR(10) 用户密码 user_sex CHAR(2) 用户性别 user_addr CHAR(20) 用户地址 user_state CHAR(2) 用户状态 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this staff_id CHAR(10) not null primary key 工作人员编号 staff_name CHAR(8) 工作人员姓名 staff_passworCHAR(08) 工作人员口令 d staff_sex CHAR(2) 工作人员性别 card_no CHAR(18) not_null primary key 银行卡号 ID_no CHAR(18) not_null 身份证号码 card_balance NUMERIC(8) 余额 fee_no CHAR(10) not_null primary key 话费编号 phone_no CHAR(12) 手机号码 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this fee_balance NUMERIC(8) 话费余额 business_no CHAR(6) not_null primary key 业务编号 account_no CHAR(10) not_null 账目编号 business_name CHAR(8) 业务名称 business_sum NUMERIC(8) 业务金额 account_no CHAR(10) not_null primary key 账目编号 consume_month CHAR(4) 消费月份 business_fee NUMERIC(8) 业务费 mobilephone CHAR(10) not_null primary key 手机 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ID_no CHAR(10) not_null 身份证号码 8、物理设计 为了加速表的查询，根据所设计的表的特点，我们咋业务表中“业务编号”“手机”， 银行卡中“身份证号”，缴费表中“卡号”“缴费号”，注册表中“注册号”“身份证号”，手 机表中“身份证号”，办理表中“办理号”分别建立索引。 Create index handle_FK on handle (business_no ASC) Create index handle2_FK on handle (mobilephone ASC) Create index “use_FK” on “bank_card” (“ID_no” ASC) Create index “pay_FK” on “pay” (“card_no” ASC) Create index “pay2_FK” on “pay” (“fee_no” ASC) Create index “register_FK” on “register” (“staff_id” ASC) Create index “register2_FK” on “register” (“ID_no” ASC) Create index “belong_FK” on mobile (“ID_no” ASC) Create index “consume_FK” on account (“account_no” ASC) 9、存储过程 (1)建立存储过程P1，可以使用该系统通过手机号与密码发出查询请求，查询该手机所 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this 剩余的话费。 if exists (select * from sysobjects where nama=”P1”) drop procedure dbo.P1 GO crrate procedure P1 as select fee_balance from mobilephone user where user_name = “......”and user_password = “......” go (2) 建立存储过程P2，统计并查看所有的手机用户信息，该信息主要包括两个方面，一方面是仅关于本手机的各种信息;另一方面是登记该手机的用户信息。 if exists (select * from sysobjects where name=”P2”) drop procedure dbo.P2 Go create procedure P2 as select mobilephone.account_no,user.user_name.ID_no,card_balance from mobilephone,user The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this where mobilephone.phone_no=user.phone_no go (3)建立存储过程P3，利用存储过程查找地址的工作人员的具体信息 if exists (select * from sysobjects where name=”P3”) drop procedure dbo.P3 Go create procedure P3 as select * from sraff_id where staff_name =”......” go 10、触发器 SQL脚本 Database name: ConceptualDataModel_5 DBMS name: ORACLE Version 9i Created on: 2008-11-29 21:32:38 alter table BANLI The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this drop constraint FK_BANLI_BANLI_业务 alter table BANLI drop constraint FK_BANLI_BANLI2_手机 alter table "bank_card" drop constraint FK_BANK_CAR_USE_USER alter table "pay" drop constraint FK_PAY_PAY_BANK_CAR alter table "pay" drop constraint FK_PAY_PAY2_PHONE_CO alter table "register" drop constraint FK_REGISTER_REGISTER_STAFF alter table "register" drop constraint FK_REGISTER_REGISTER2_USER alter table 手机 drop constraint FK_手机_BELONG_USER alter table 业务 drop constraint FK_业务_CONSUME_ACCOUNT The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this drop index BANLI2_FK drop index BANLI_FK drop index "use_FK" drop index "pay2_FK" drop index "pay_FK" drop index "register2_FK" drop index "register_FK" drop index "belong_FK" drop index "consume_FK" drop table BANLI cascade constraints drop table "account" cascade constraints drop table "bank_card" cascade constraints drop table "pay" cascade constraints drop table "phone_cost" cascade constraints drop table "register" cascade constraints drop table "staff" cascade constraints drop table "user" cascade constraints The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this drop table 手机 cascade constraints drop table 业务 cascade constraints Table: BANLI create table BANLI ( "YEWU_no" CHAR(6) not null, 手机号码 CHAR(10) not null, "BANLI_time" DATE, constraint PK_BANLI primary key ("YEWU_no", 手机号码) ) ndex: BANLI_FK create index BANLI_FK on BANLI ( "YEWU_no" ASC ) Index: BANLI2_FK create index BANLI2_FK on BANLI ( 手机号码 ASC The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ) Table: "account" create table "account" ( "account_no" CHAR(10) not null, "consume_month" CHAR(4), "YEWU_fee" NUMBER(8), constraint PK_ACCOUNT primary key ("account_no") ) Table: "bank_card" create table "bank_card" ( "card_no" CHAR(18) not null, "ID_no" CHAR(18) not null, "card_balance" NUMBER(8), constraint PK_BANK_CARD primary key ("card_no") ) Index: "use_FK" create index "use_FK" on "bank_card" ( The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this "ID_no" ASC ) Table: "pay" create table "pay" ( "card_no" CHAR(18) not null, "fee_no" CHAR(10) not null, "pay_sum" NUMBER(8) not null, "pay_time" DATE not null, constraint PK_PAY primary key ("card_no", "fee_no") ) Index: "pay_FK" create index "pay_FK" on "pay" ( "card_no" ASC ) Index: "pay2_FK" create index "pay2_FK" on "pay" ( "fee_no" ASC The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this ) Table: "phone_cost" create table "phone_cost" ( "fee_no" CHAR(10) not null, "phone_no" CHAR(12), "fee_balance" NUMBER(8), constraint PK_PHONE_COST primary key ("fee_no") ) Table: "register" create table "register" ( "staff_id" CHAR(10) not null, "ID_no" CHAR(18) not null, "register_time" DATE, "phone_no" CHAR(12), constraint PK_REGISTER primary key ("staff_id", "ID_no") ) Index: "register_FK" The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this create index "register_FK" on "register" ( "staff_id" ASC ) Index: "register2_FK" create index "register2_FK" on "register" ( "ID_no" ASC ) Table: "staff" create table "staff" ( "staff_id" CHAR(10) not null, "staff_name" CHAR(8), "staff_password" CHAR(10), "staff_sex" CHAR(2), constraint PK_STAFF primary key ("staff_id") ) Table: "user" create table "user" ( The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this "ID_no" CHAR(18) not null, "user_name" CHAR(8), "user_password" CHAR(10), "user_sex" CHAR(2), "user_addr" CHAR(20), "user_state" CHAR(2), constraint PK_USER primary key ("ID_no") ) Table: 手机 create table 手机 ( 手机号码 CHAR(10) not null, "ID_no" CHAR(18) not null, constraint PK_手机 primary key (手机号码) ) Index: "belong_FK" create index "belong_FK" on 手机 ( The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this "ID_no" ASC ) Table: 业务 create table 业务 ( "YEWU_no" CHAR(6) not null, "account_no" CHAR(10) not null, "YEWU_name" CHAR(8), "YEWU_sum" NUMBER(8), constraint PK_业务 primary key ("YEWU_no") Index: "consume_FK" create index "consume_FK" on 业务 ( "account_no" ASC) alter table BANLI add constraint FK_BANLI_BANLI_业务 foreign key ("YEWU_no") references 业务 ("YEWU_no") alter table BANLI add constraint FK_BANLI_BANLI2_手机 foreign key (手机号码) The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this references 手机 (手机号码) alter table "bank_card" add constraint FK_BANK_CAR_USE_USER foreign key ("ID_no") references "user" ("ID_no") alter table "pay" add constraint FK_PAY_PAY_BANK_CAR foreign key ("card_no") references "bank_card" ("card_no") alter table "pay" add constraint FK_PAY_PAY2_PHONE_CO foreign key ("fee_no") references "phone_cost" ("fee_no") alter table "register" add constraint FK_REGISTER_REGISTER_STAFF foreign key ("staff_id") references "staff" ("staff_id") alter table "register" add constraint FK_REGISTER_REGISTER2_USER foreign key ("ID_no") references "user" ("ID_no") alter table 手机 The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this add constraint FK_手机_BELONG_USER foreign key ("ID_no") references "user" ("ID_no") alter table 业务 add constraint FK_业务_CONSUME_ACCOUNT foreign key ("account_no") references "account" ("account_no") The fifth is to with all kinds of voluntary service resources combined. Active with the Youth League Committee, civilization office and other relevant departments to strengthen ties and strive for the support. At the same time, and other social volunteer organizations to maintain close contact, timely carried out jointly by the volunteer activities, to further extend the volunteer work contact angle. Three, strengthen the organization and leadership, strengthen measures to implement, to build Juxian public security volunteers brand Year-end appraisal of common responsibility of Pingan volunteer work both volunteer service object, category, or play a role, involving all kinds of police, Department of business scope, the Council at the beginning of the year has been carried out decomposition project, and into the performance assessment, and several scheduling work progress. This work although it is Propaganda Department to take the lead, but it does not mean that it is a responsible department and the police department, is a global engineering. From now on, left in less than three months time, time is tight, the task is heavy, ask each unit to get a clear understanding of the situation, make clear the task, further accelerate and improve efficiency, and effectively strengthen the work sense of responsibility and urgency. One is to catch condominium together, to further expand the peace volunteers. The relevant departments must clear responsibilities, division of responsibility. To put this