校园卡系统关系数据库设计完整版.doc

学校食堂用餐卡系统关系数据库..设计一、 需求分析1. 系统目标为了方便管理学生用餐卡的发放、挂失、补办和管理人员信息、学生信息、存储金额及相关数据信息等，现对学校食堂用餐卡系统进行关系数据库设计2. 数据分析1） 用餐卡信息：用餐卡编号，持卡人编号，办卡日期，余额；2） 持卡人信息：姓名，性别，照片，编号，身份证号，部门，人员类别；3） 操作员信息：职工编号，姓名，性别，照片，身份证号码，参加工作时间，密码等；4） 账户操作详细信息：流水号，用餐卡编号，存/取，操作员编号，操作时间，发放金额等3. 事物需求 1）办新卡，修卡信息；2）存取卡中金额；3）维护人员信息；4）列出数据库中的每餐使用金额的清单及余额信息；5）列出所有持卡人信息；6）列出每个月的累计消费信息；7）列出用餐卡是否为挂失卡信息二、 建立 E-R 模型1. 实体集设计本系统有三个实体集：用餐卡、持卡人、操作员，标识如下：1） 实体集“用餐卡” ，属性包括：用餐卡号、持卡人编号、办卡日期及余额，其中， “用餐卡号”为主码，见图 1A）所示；2） 实体集“持卡人” ，属性包括：姓名、性别、照片、编号、身份证号、部门及人员类别，其中“编号”为主码。

见图 1B）所示；3） 实体集“操作员” ，属性包括：操作员编号、姓名、性别、照片、身份证号、工作时间及密码，其中“操作员编号”为主码，见图 1C）所示持卡人编号部门照片性别人员类别 身份证号姓名用餐卡持卡人编号余额用餐卡号 办卡日期操作员性别姓名密码照片身份证号工作时间操作员编号图 1 各实体集的 E-R 图2. 联系集设计..本系统有两个联系，标识如下：1） “拥有”联系：标识持卡人拥有用餐卡（“用餐卡”与“持卡人”之间的一对联系） ；2） “操作”联系：标识操作员处理用餐卡的账户信息（“操作员”与“用餐卡”之间的多对多联系） ，其本身还具有属性：存/取、操作发生的时间、发生金额和挂失信息联系集的 E-R 模型见图 2 所示持卡人 拥有 用餐卡 操作 操作员图 2 联系集 E-R 图3. 综合 E-R 图综合 1 和 2 两步，画出综合 E-R,见图 3 所示操作员编号工作时间 身份证号人员类别 身份证号拥有用餐卡操作操作员持卡人编号办卡日期用餐卡号余额发生时间存/取发生金额挂失信息照片性别密码姓名持卡人姓名照片 编号性别 部门图 3 综合 E-R 图三、 建立关系模型 1. 实体集转换为关系模型根据实体转换原则，每个实体转换为一个关系模式，实体的属性转换为关系模式的属性，实体的码转换为关系模式的码。

因此，将图 3 所示的 3 个实体集转换为如下 3 个关系模式：① 用餐卡（用餐卡号，持卡人编号，办卡日期，余额）..② 持卡人（姓名，性别，照片，编号，身份证号，部门，人员类型）③ 操作员（操作员编号，姓名，性别，照片，身份证号，工作时间，密码）2. 联系集转换为关系模型根据联系转换原则，对于“拥有”联系，因为是一对多联系，可以把“一”那头的主码放入“多”的那一头；对于“操作”联系，因为是多对多联系，应建立一个新的关系模式，新关系模式中应该加入两端的码的联系本身的属性因此，图 3 所示的两个联系集转换为如下所示的 2 个关系模式：① 用餐卡（用餐卡号，持卡人编号，办卡日期，余额）其中， “持卡人编号”为外码，参照“持卡人”关系中的“编号” ②操作（用餐卡号，操作员编号，存/取，操作时间，发生余额，挂失信息）其中， “用餐卡号”为外码，参照“用餐卡”关系中的“用餐卡号” ；“操作员编号”也为外码，参照“操作员”关系中的“操作员编号” 3. 综合关系模型综合以上所有关系模型，本系统最终生成如下四个关系模式：① 持卡人（姓名，性别，照片，编号，身份证号，部门，人员类型）① 用餐卡（用餐卡号，持卡人编号，办卡日期，余额）其中， “持卡人编号”为外码，参照“持卡人”关系中的“编号” 。

① 操作（用餐卡号，操作员编号，存/取，操作时间，发生金额，挂失信息）其中， “用餐卡号”为外码，参照“用餐卡”关系中的“用餐卡号” ；“操作员编号”也为外码，参照“操作员”关系中的“操作员编号” ④操作员（操作员编号，姓名，性别，照片，身份证号，工作时间，密码） 四、 关系范式检验1“持卡人”关系模式检验①“持卡人”关系中各列属性均不可再分，故属于第一范式；②“持卡人”关系中非主属性均完全函数依赖于主码整体，故属于第二范式；③“持卡人”关系中非主属性：身份证号和姓名、性别之间存在函数依赖关系，故不属于第三范式持卡人”关系应进一步分解为：持卡人（编号，照片，身份证号，部门，人员类型）持卡人身份（身份证号，姓名，性别）..分解后的关系均符合三大范式2“用餐卡”关系模式检验①“用餐卡”关系中各列属性均不可再分，故属于第一范式；②“用餐卡”关系中非主属性均完全函数依赖于主码整体，故属于第二范式；③“用餐卡”关系中非主属性之间不存在函数依赖关系，故属于第三范式3“操作”关系模式检验①“操作”关系中各列属性均不可再分，故属于第一范式；②“操作”关系中非主属性均完全函数依赖于主码整体，故属于第二范式；③“操作”关系中非主属性之间不存在函数依赖关系，故属于第三范式。

4“操作员”关系模式检验①“操作员”关系中各列属性均不可再分，故属于第一范式；②“操作员”关系中非主属性均完全函数依赖于主码整体，故属于第二范式；③“操作员”关系中非主属性：身份证号和姓名、性别之间存在函数依赖关系，故不属于第三范式操作员关系进一步分解为：操作员（操作员编号，照片，身份证号，工作时间，密码）操作员（身份证号，姓名，性别）分解后的关系均符合三大范式五、 建立关系数据库根据本系统建立的四个关系模式，利用 SQL SERVER 2000 建立四张数据表，并通过 INSERT 语句给每张表各插入 20 条以上的记录表 1：create table 用餐卡(持卡人编号 int,用餐卡号 int primary key,余额 float check (余额>0 and 余额0 and 发生金额502 查询部门为“人事处”的所有用餐卡的挂失信息 （多表子查询）例子：Select * from 操作 where 用餐卡号 =(select 用餐卡号 from 用餐卡 where 持卡人编号 =(select 编号 from 持卡人 where 部门='人事处' ))1）聚集查询①列出人员类别为学生的男生。

查询语句：Select * from 持卡人Where 人员类别='学生' and 性别='M'②检索出操作中的信息，并且按照发生金额降序排列查询语句：Select * from 操作Order by 发生金额 desc③检索出用餐卡的最大余额和最小余额查询语句：select max(余额)as 最大余额 , min(余额)as 最小余额 from 用餐卡④检索出姓王的操作员，结果要求输出姓名,性别,身份证号和照片查询语句：select 姓名,性别 ,身份证号,照片 from 操作员where 姓名 like '王%'2）多表聚集查询①检索出各个部门总额，结果输出余额的总和和部门查询语句：select 部门,sum( 余额)as 总额 from 持卡人,用餐卡where 持卡人 .编号=用餐卡.持卡人编号 group by 部门②列出余额在 80 以上的那些部门及对应的余额和人员类别查询语句：select 部门,人员类别 ,avg(余额)as 平均余额from 持卡人 ,用餐卡where 持卡人 .编号=用餐卡.持卡人编号 group by 部门,人员类别having avg (余额 )>80order by 部门,人员类别③计算出每个部门的余额的最大值，最小值和平均值。

查询语句：select 人员类别,max (余额)as 最大余额,min(余额)as 最小金额,avg(余额)as 平均余额from 持卡人 ,用餐卡where 持卡人 .编号=用餐卡.持卡人编号 group by 人员类别④按部门汇总各部门的总余额查询语句：Select 部门, sum(余额)as 总余额 From 持卡人,用餐卡Where 持卡人.编号=用餐卡.持卡人编号 Group by 部门..3）连接查询①列出用餐卡余额少于 50 的持卡人信息查询语句：Select 持卡人.* from 持卡人,用餐卡Where 持卡人.编号= 用餐卡.持卡人编号 and 余额 余额④检索出有挂失信息的人的持卡人表中的所有信息查询语句：select 持卡人 .* from 持卡人,用餐卡,操作where 持卡人.编号=用餐卡 .持卡人编号 and 用餐卡 .用餐卡号=操作.用餐卡号 and 挂失信息 ='是'⑤检索出部门为人事部的持卡人的用餐卡信息查询语句：select 用餐卡 .* from 持卡人 ,用餐卡where 用餐卡 .持卡人编号=持卡人.编号 and 部门 ='人事部'⑥列出至少已经有一个余额大于 70 的持卡人信息查询语句：select distinct 持卡人.*from 持卡人 ,用餐卡where 持卡人 .编号=用餐卡.持卡人编号 and 余额 >70⑦检索出操作员中姓张的操作信息并输出姓名。

查询语句：select 姓名,操作 .* from 操作员,操作where 操作员 .操作员编号=操作.操作员编号 and 姓名 like '张%'4）子查询①检索出与用餐卡号为’’10003”人员类别相同的持卡人信息查询语句：Select * from 持卡人where 人员类别=(select 人员类别 from 持卡人 where 编号=(select 持卡人编号 from 用餐卡 where 用餐卡号='10003'))②列出部门为’团务部’ 的用餐卡信息查询语句：select * from 用餐卡 where 持卡人编号 in (select 编号 from 持卡人 where 部门=' 团务部')③检索出没有挂失信息的人的在持卡人表中的所有信息查询语句：Select * from 持卡人where 编号 in (select 持卡人编号 from 用餐卡 where 用餐卡号 in (select 用餐卡号 from 操作 where 挂失信息=' 否'))..④列出用餐卡余额大于 80 的持卡人信息查询语句：Select * from 持卡人where 编号 in (select 持卡人编号 from 用餐卡 where 余额>80)⑤查询部门人数在平均部门人数以上的部门以及人数。

查询语句：select 部门 , count(编号) as 部门人数from 持卡人group by 部门 having count( 编号 )>(select count (编号 )/count (distinct 部门) from 持卡人)⑥查询持卡人编号为‘0005’的操作员信息查询语句：select * from 操作员where 操作员编号 in (select 操作员编号 from 操作 where 用餐卡号=(select 用餐卡号 from 用餐卡 where 持卡人编号='0005'))5）更新命令①将名字为‘李毅’的持卡人的部门修改为‘人事处’ ，人员类别改为‘学生’ 查询语句：Update 持卡人 set 部门='人事处',人员类别='学生'Where 姓名='李毅'②将发生金额少于 50 元的统一归零，并输出持卡人所有信息查询语句：Update 用餐卡 set 余额 = '50' from 用餐卡,持卡人,操作Where 用餐卡.持卡人编号= 持卡人.编号 and 用餐卡.用餐卡号=操作.用餐卡号 and 余额=20)BeginPrint'不能把余额。