学
校
食
堂
用
餐
卡
系
统
关
系
数
据
库
设
计
一、 需求分析
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 个关系模式:
1 用餐卡(用餐卡号,持卡人编号,办卡日期,余额)
2 持卡人(姓名,性别,照片,编号,身份证号,部门,人员类型)
3 操作员(操作员编号,姓名,性别,照片,身份证号,工作时间,密
码)
2. 联系集转换为关系模型
根据联系转换原则,对于“拥有”联系,因为是一对多联系,可以把“一”
那头的主码放入“多”的那一头;对于“操作”联系,因为是多对多联系,应建
立一个新的关系模式,新关系模式中应该加入两端的码的联系本身的属性。因此,
图 3 所示的两个联系集转换为如下所示的 2 个关系模式:
1 用餐卡(用餐卡号,持卡人编号,办卡日期,余额)
其中,“持卡人编号”为外码,参照“持卡人”关系中的“编号”。
②操作(用餐卡号,操作员编号,存/取,操作时间,发生余额,挂失信
息)
其中,“用餐卡号”为外码,参照“用餐卡”关系中的“用餐卡号”;“操
作员编号”也为外码,参照“操作员”关系中的“操作员编号”。
3. 综合关系模型
综合以上所有关系模型,本系统最终生成如下四个关系模式:
1
2
3
持卡人(姓名,性别,照片,编号,身份证号,部门,人员类型)
用餐卡(用餐卡号,持卡人编号,办卡日期,余额)
其中,“持卡人编号”为外码,参照“持卡人”关系中的“编号”。
操作(用餐卡号,操作员编号,存/取,操作时间,发生金额,挂
失信息)
其中,“用餐卡号”为外码,参照“用餐卡”关系中的“用餐卡号”;“操
作员编号”也为外码,参照“操作员”关系中的“操作员编号”。
④操作员(操作员编号,姓名,性别,照片,身份证号,工作时间,密
码)
四、 关系范式检验
1“持卡人”关系模式检验
①“持卡人”关系中各列属性均不可再分,故属于第一范式;
②“持卡人”关系中非主属性均完全函数依赖于主码整体,故属于第二
范式;
③“持卡人”关系中非主属性:身份证号和姓名、性别之间存在函数依
赖关系,故不属于第三范式。
“持卡人”关系应进一步分解为:
持卡人(编号,照片,身份证号,部门,人员类型)
持卡人身份(身份证号,姓名,性别)
分解后的关系均符合三大范式。
2“用餐卡”关系模式检验
①“用餐卡”关系中各列属性均不可再分,故属于第一范式;
②“用餐卡”关系中非主属性均完全函数依赖于主码整体,故属于第二
③“用餐卡”关系中非主属性之间不存在函数依赖关系,故属于第三范
范式;
式。
3“操作”关系模式检验
①“操作”关系中各列属性均不可再分,故属于第一范式;
②“操作”关系中非主属性均完全函数依赖于主码整体,故属于第二范
式;
③“操作”关系中非主属性之间不存在函数依赖关系,故属于第三范式。
4“操作员”关系模式检验
①“操作员”关系中各列属性均不可再分,故属于第一范式;
②“操作员”关系中非主属性均完全函数依赖于主码整体,故属于第二
范式;
③“操作员”关系中非主属性:身份证号和姓名、性别之间存在函数依
赖关系,故不属于第三范式。
操作员关系进一步分解为:
操作员(操作员编号,照片,身份证号,工作时间,密码)
操作员(身份证号,姓名,性别)
分解后的关系均符合三大范式。
五、 建立关系数据库
4-05')
insert into 用餐卡 values ('0007','10007','70','2005-10-07')
insert into 用餐卡 values ('0008','10008','60','2005-11-08')
insert into 用餐卡 values ('0009','10009','50','2006 根据本系统建立的四个
关系模式,利用 SQL SERVER 2000 建立四张数据表,并通过 INSERT 语句给每
张表各插入 20 条以上的记录。
表 1:
插入数据:
create table 用餐卡
(持卡人编号 int,
用餐卡号 int primary key,
余额 float check (余额>0
办卡日期 datetime
foreign key (持卡人编号)references 持卡人(编号))
and 余额<=500),
insert into 用餐卡 values ('0001','10001','50','2000-01-01')
insert into 用餐卡 values ('0002','10002','40','2001-03-02')
insert into 用餐卡 values ('0003','10003','80','2002-05-06')
insert into 用餐卡 values ('0004','10004','90','2003-07-01')
insert into 用餐卡 values ('0005','10005','30','2003-08-06')
insert into 用餐卡 values ('0006','10006','20','2004-0-02-09')
insert into 用餐卡 values ('0010','10010','260','2006-08-10')
insert into 用餐卡 values ('0011','10011','70','2006-09-01')
insert into 用餐卡 values ('0012','10012','180','2006-11-12')
insert into 用餐卡 values ('0013','10013','90','2007-01-13')
insert into 用餐卡 values ('0014','10014','100','2007-04-08')
insert into 用餐卡 values ('0015','10015','10','2007-04-09')
insert into 用餐卡 values ('0016','10016','15','2008-08-08')
insert into 用餐卡 values ('0017','10017','20','2008-08-09')
insert into 用餐卡 values ('0018','10018','140','2009-01-18')
insert into 用餐卡 values ('0019','10019','50','2009-5-20')
insert into 用餐卡 values ('0020','10020','70','2010-8-20')
insert into 用餐卡 values ('0021','10021','90','2010-10-02')
表 2:
create table 操作员
(操作员编号 int primary key,
姓名 varchar(20) not null,
性别 Char(1) ,
身份证号 char(18) unique,
照片 Varchar(60),
工作时间 datetime,
密码 char(8))
into 操 作 员 values ('20001',' 李 毅 ','M','340101199108022721','D:\','2008-01-01
插入数据:
insert
09:30:11:30','19740')
insert
09:30:11:30','24500')
insert
09:30:11:30','54201')
insert
09:30:11:30','76190')
insert
09:30:11:30','54890')
insert
09:30:11:30','226600')
insert
09:30:11:30','07891')
insert
06:30:9:30','01234')
into 操 作 员 values
into 操 作 员 values
('20002',' 蒲 萍 ','F','230101199008022721','C:\','2008-01-02
('20003',' 李 好 ','F','480101198908022721','C:\','2008-01-03
into 操 作 员 values ('20004',' 蒲 苇 ','M','760101199108022722','D:\','2008-01-04
into 操 作 员 values ('20005',' 李 勇 ','M','520101198808022721','E:\','2008-01-05
into 操 作 员 values ('20006',' 王 图 ','M','170101199108022723','E:\','2008-01-06
into 操 作 员 values ('20007',' 王 涛 ','M','500101199008022724','E:\','2008-01-07
into 操 作 员 values ('20008',' 范 冲 ','M','300101198908022725','D:\','2008-01-08
into 操 作 员 values ('20009',' 王 倩 ','F','310101199108022726','D:\','2008-01-09
into 操 作 员 values ('20010',' 王 忠 ','M','370101198808022727','C:\','2008-01-10
into 操 作 员 values
('20011',' 张 瑞 ','F','540101199108022728','D:\','2008-01-11
into 操 作 员 values ('20012',' 易 云 霞 ','F','780101199008022729','D:\','2008-01-12
into 操 作 员 values ('20013',' 张 鹏 ','M','700101198908022731','D:\','2008-01-13
into 操 作 员 values ('20014',' 黄 平 ','M','560101199108022732','D:\','2008-01-14
into 操 作 员 values ('20015',' 张 扬 ','M','980101198808022731','C:\','2008-01-15
into 操 作 员 values
into 操 作 员 values
('20016',' 余 晏 ','F','120101199108022821','C:\','2008-01-16
('20017',' 余 婷 ','F','230101199008022821','C:\','2008-01-17
into 操 作 员 values ('20018',' 于 浩 ','M','320101198908022821','E:\','2008-01-18
into 操 作 员 values ('20019',' 羽 涛 ','M','500101199108022822','D:\','2008-01-19
into 操 作 员 values ('20020',' 黄 奕 ','M','190101198808022821','E:\','2008-01-20
into 操 作 员 values ('20021',' 邝 悠 悠 ','F','500101198808022821','D:\','2008-01-20
insert
09:30:11:30','02234')
insert
09:30:11:30','00456')
insert
09:30:11:30','0789')
insert
09:30:11:30','00321')
insert
09:30:11:30','00689')
insert
09:30:11:30','12309')
insert
09:30:11:30','98651')
insert
09:30:11:30','13478')
insert
09:30:11:30','23490')
insert
09:30:11:30','24567')
insert
09:30:11:30','56098')
insert
09:30:11:30','98421')
insert
09:30:11:30','56109')
表 3:
create table 持卡人
(姓名 Varchar(20)not null,
性别 Char(1) default'M' ,
编号 int primary key,
照片 char(20),
部门 char(20),
身份证号 char(18) unique,
人员类别 char(20) )
插入数据:
insert into 持卡人 values ('李毅','M','0001','D;\','团务部','500101199108022721','
学生')
insert into 持卡人 values ('张薇','F','0002','D;\','文艺部','320101199008022721','礼
仪人员')
insert into 持卡人 values ('王丽','F','0003','D;\','团务部','500101198908022721','学
生')
insert into 持卡人 values ('李永','M','0004','C;\','团务部','240101199108022722','部
长')