科研管理信息系统数据库设计

1、1. 问题描述1.1背景理论背景：某高等学校拟建立一套科研管理信息系统，实现对科研工作的信息化管理，要求系统对数据的存储完整，查询迅速，操作方便。实际背景：建立一套完整的数据库系统，使管理员和用户能够对不同权限的数据进行查询，修改等操作，加强学校对科研工作的研究，通过这一数据库管理系统促进教师的工作水平。1.2数据需求通过分析，本系统需要输入的数据如下：（1）学院：包括学院编号，学院名称（2）行政机关：单位名称，单位地址（3）系：系名称，学院编号，联系电话（4）教职员工：职工编号，姓名，系名称，单位名称，性别，出生年月，政治面貌，籍贯，毕业院校，联系电话（5）科研活动的信息：承担课题，发明专利

2、，撰写论文，创作著作（6）科研项目的信息：课题（立项，鉴定，获奖），专利，论文，著作（7）课题：课题编号，课题名称，承担单位，合作单位（8）专利：专利号，专利名称，授予时间，主持人，人员，专利类型，总分（9）论文：论文名称，主编，副主编，出版刊物名称，刊号，刊物级别，总分（10）著作：书名，出版社，出版时间，职工编号，姓名，著作类别，总分（11）课题立项：立项编号，课题编号，课题来源，得分，参与人员，职工编号（12）课题鉴定：鉴定编号，课题编号，成果等级，参与人员，得分，职工编号（13）课题获奖：获奖编号，课题编号，获奖级别，姓名，职工编号，获奖总得分1.3事务需求1、 要求能录入、修改和删除

3、个人科研信息详情。2、 要求能按系，学院、部门或科研种类统计所有人员各项科研项目情况。3、 要求可以按时间（某年或某时间段）查询及统计个人、各单位的科研项目详细情况。4、 计算科研得分情况。按评分规则将计算的得分存储，以便将来统计查询。5、 要求能够修改或设定评分规则。6、 要求可能以从各个口径统计得分，如个人各项得分，各单位各项得分，加上年份控制。7、 要求能使用饼图、柱状图、折线图、趋势图等显示相关信息。8、 输出要求能打印正规报表，能导出到excel或word。9、 要求有权限管理、分级审核功能。除各单位管理员外，个人只可以维护自己的科研信息。个人科研信息提交后，由所在单位管理员审核，审

4、核后，个人不能修改和删除已审核的科研信息。所在单位管理员审核无误后提交学校科研管理员审核，提交后所在单位管理员不能对该记录进行修改和删除。2. 解决方案(或数据库系统设计)2.1 E-R模型设计1）选择fileànew modelàConceptual Data Model，建立CDM模型（即概念数据模型）。2）创建实体3）双击创建的实体符号，在此窗口“General”标签中可以输入实体的名称、代码、描述等信息。4）添加实体属性：上述窗口的“Attribute”选项标签上可以添加属性。5）建立联系：在图形窗口中创建两个实体后，单击Relationship工具，单击一个实体，

5、在按下鼠标左键的同时把光标拖至别一个实体上并释放鼠标左键，这样就在两个实体之间创建了联系，右键单击图形窗口，释放工具。2.2 逻辑模型toolsàGenerate Physical Data Model。从DBMS下拉列表框选择 Microsoft SQL Server 2005选择 Share 单选按钮，单击OK即可。2.3数据库实现1） 选择Database> Generate Database。设置directory和filename，单击确定。2）首先启动SQL Server2005建立数据库，执行脚本，生成数据库对象。执行脚本的代码如下：（3）分析脚本，运行脚本，在S

6、QL server2005中生成数据库，表的结构如下：2.4处理流程图（1）触发器：lunwenzongfen功能：在向论文表填写刊物级别时，自动填写总分列，判断论文总分。论文编号刊物级别核心期刊10分一般期刊8分报纸杂志6分会议论文4分（2）触发器：zhuzuozongdefen 功能：在向著作表填写著作类别时，自动填写总分列，判断著作总分。书名著作类别专著10分编著9教材8译著7（3）触发器：defen 功能：在向专利表填写专利类型时，自动填写总分列，判断专利总分。专利号专利类型发明10分实用新型8分外观设计6分（4）存储过程：1. 存储过程：lunwen_scoreconvert22.

7、功能：通过职工编号查询个人单项得分，如论文。论文编号刊物级别核心期刊10分一般期刊8分报纸杂志6分会议论文4分职工编号分工主编 60%副主编 40%得分（5）存储过程：zhuzuo_scoreconvert1 功能：通过职工编号获得个人著作得分。书名著作类别专著10分编著9分译著8分教材7分职工编号分工作者 80%助手 20%最终得分（6）存储过程：zhuanli_scoreconvert3功能：通过职工编号获得个人得分。专利号专利类型发明10分实用新型8分外观设计6分职工编号分工主持人 60%人员 40%最终得分3. 系统实现3.1开发环境本系统适用于微型机，内存容量为512M，外部设备有主

8、机，显示器，键盘，鼠标。采用的结构化查询语言（SQL）,运用到的软件有：powerdesigner（运行环境Win2003,WinXP,Win2000,Win9X）sql server 2005。3.2 处理模块编码1）论文存储过程：ALTER procedure dbo.lunwen_scoreconvert2 F_职工编号int/定义存储过程名称asbegindeclare score2 intdeclare num2 intdeclare S_刊物级别char(50)declare S_分工char(50)declare F_姓名char(50)declare S_论文名称int /定义

9、变量名称set S_刊物级别= (select top 1 刊物级别from 论文a,撰写b where b.职工编号= F_职工编号and a.论文名称= b.论文名称)set S_分工= (select top 1 分工from 撰写a where a.职工编号=F_职工编号)set num2 = (select top 1 count(论文名称) from 撰写where 论文名称in (select 论文名称from 撰写where 职工编号=F_职工编号)set F_姓名= (select top 1 a.姓名from 教职员工a,撰写b where a.职工编号=F_职工编号and

10、 a.职工编号= (b.职工编号) /获取表中的码beginif S_刊物级别='核心期刊' set score2=100 else if S_刊物级别='一般期刊' set score2=90else if S_刊物级别='报纸杂志' set score2=80else if S_刊物级别='会议论文' set score2=70begin if S_分工='主编' set score2=score2+score2*0.6else if s_分工='副主编' set score2=score2+(

11、score2*0.4)/(num2-1) /编写评分规则endendendbegin select result= '职工号：'+str(F_职工编号)+'职工名：'+F_姓名+'论文得分为：'+str(score2,5,1) /设置显示方法end2）著作存储过程：set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER procedure dbo.zhuzuo_scoreconvert1 F_职工编号int/定义存储过程名asbegindeclare score1 intdeclare W_著作类别var

12、char(10)declare F_姓名varchar(50) /定义变量名称set W_著作类别=(select 著作类别from 著作a,创作b where a.职工编号=b.职工编号and b.职工编号=F_职工编号)set F_姓名=(select 姓名from 教职员工a where a.职工编号=F_职工编号)beginif W_著作类别='专著'set score1=100else if W_著作类别='编著'set score1=90else if W_著作类别='教材'set score1=80else set score1=7

13、0 /编写评分规则endendbeginselect result= '职工号：'+str(F_职工编号)+'职工名：'+F_姓名+'著作得分为：'+str(score1,5,1) /设置显示方法end3）专利存储过程：ALTER procedure dbo.zhuanli_scoreconvert3 F_职工编号int/定义存储过程名asbegindeclare score3 int declare O_专利类型char(50)declare O_分工char(50)declare num3 intdeclare F_姓名char(50)set

14、 O_专利类型=(select 专利类型from 专利a,发明b where b.职工编号=F_职工编号and a.专利号=b.专利号)set O_分工=(select 分工from 专利a,发明b where b.职工编号=F_职工编号and a.专利号=b.专利号)set num3=(select count(专利号) from 发明a where 专利号=(select 专利号from 发明b where 职工编号=F_职工编号)set F_姓名=(select a.姓名from 教职员工a,专利b,发明c where a.职工编号=c.职工编号and b.专利号=c.专利号and c.

15、职工编号=F_职工编号)begin if O_专利类型='发明'set score3=100else if O_专利类型='实用新型'set score3=90else set score3=80begin if O_分工='主持人' set score3=score3+score3*0.6else set score3=score3+score3*0.4/(num3-1) /编写评分规则endendselect result= '职工号：'+str(F_职工编号)+'职工名：'+F_姓名+'专利得分为：&

16、#39;+str(score3) /设置显示方法end 1） 论文触发器：/根据刊物级别来判定得分 ALTER TRIGGER dbo.lunwenzongfen /定义触发器名称 ON dbo.论文 FOR INSERTAS DECLARE 论文编号char(10)DECLARE 刊物级别char(10)SET 刊物级别=(SELECT 刊物级别FROM INSERTED)IF 刊物级别='核心期刊'BEGINUPDATE 论文SET 总分='10'/所要操作的列ENDELSEIF 刊物级别='一般期刊'BEGINUPDATE 论文SET 总分

17、='8'ENDELSEIF 刊物级别='报纸杂志'BEGINUPDATE 论文SET 总分='6'ENDELSEIF 刊物级别='会议论文'BEGINUPDATE 论文SET 总分='4'END /定义给分规则2） 著作触发器：/著作数据发生变更时相关表内容相应发生变化ALTER TRIGGER dbo.zhuzuozongdefen ON dbo.著作 FOR INSERTAS DECLARE 著作编号char(10)DECLARE 著作类别char(10)SET 著作类别=(SELECT 著作类别FROM IN

18、SERTED)IF 著作类别='专著'BEGINUPDATE 著作SET 总分='10'ENDELSEIF 著作类别='编著'BEGINUPDATE 著作SET 总分='9'ENDELSEIF 著作类别='教材'BEGINUPDATE 著作SET 总分='8'ENDELSEIF 著作类别='译著'BEGINUPDATE 著作SET 总分='7'END3） 专利触发器：ALTER TRIGGER dbo.defen ON dbo.专利 FOR INSERTAS DECL

19、ARE 专利号char(10)DECLARE 专利类型char(10)SET 专利类型=(SELECT 专利类型FROM INSERTED)IF 专利类型='发明'BEGINUPDATE 专利SET 总分='10'ENDELSEIF 专利类型='实用新型'BEGINUPDATE 专利SET 总分='8'ENDELSEIF 专利类型='外观设计'BEGINUPDATE 专利SET 总分='6'END3.3数据库系统调试情况遇到的问题：（1）生成数据库以后，填写数据时很多表都发生冲突，不能正常填入数据，

20、原因是用powerdesigner设计概念结构模型是一些数据类型和长度没有按实际情况来设置。（2）给表创建约束时发生冲突。（3）创建触发器时，由于一些数据是事先填入的，导致触发器不能正常触发，总是出现错误的结果。（4）创建存储过程计算个人总得分的时候，总是出现语法错误，有一些细节没有注意到。（5）按照老师所给的实验要求中的评分规则编写代码时，计算多人的分数分配时不能正常运行，出现的分数结果都是一样的。（6）在把存储过程的计算结果导入指定的表，指定的列时，运用执行存储过程的语句，未能成功，总是提示返回的是空值。（7）为了实现从各口径获得得分，方便查询的功能时创建了视图，其中几个视图出现了冗余，信

21、息重复次数很多，占用大量空间。3.4 问题的解决（1）在填写数据库信息的过程中，不断修改表中的数据类型和数据长度。（2）创建了检查性约束，如要求中提到的专利类型（发明、实用新型、外观设计）（3）为了解决触发器不能触发的难题，我向表中重新填入了数据，将以前的数据删除。（4）存储过程按照老师给的实例来编写，虽然避免的出错，但是却将过程简化，实现的功能也就不完整了。（5）导出存储过程的执行结果和表的冗余等问题还没有更好的解决方案。3.5 结论（1） 系统能够实现一些基本的功能，结构比较简单。（2） 能够统计个人单项得分，各项总得分。（3） 能够实现数据的查询，增删改等一些操作。（4） 很多功能未能实

22、现，比如各单位各项的得分，年份控制等。（5） 数据库中有一些表中存在冗余。（6） 改进意见：明确各模块的功能，避免重复。避免冗余。4. 结束语通过本次课程设计，我收获良多。首先，我认识到了老师在课堂上所讲的基础知识的重要性，此次课程设计的很多内容看似复杂，其实运用到的确实最简单的知识。这对我来说既是教训又是鼓励，以后一定要加强对知识的积累，熟练掌握，灵活运用。其次，我对自己所学的专业有了更深层次的了解，也改正了我对本专业一直以来存有的错误认识。比如这次课程设计所涉及的高等院校科研管理信息系统，就与现实有很密切的联系，在将来的工作当中，我们很有可能遇到类似的问题，这对我们将来的就业、择业也产生了

23、很深远、很有意义的影响。第三，此次课程设计锻炼了我的实际操作能力，很多理论上的东西看起来简单，但是真的要实践起来还是需要付出很多努力的，比如编写存储过程，看老师给的实例代码感觉很容易，也明白代码的含义，但是与实际问题联系起来需要自己编写时，总会出现这样那样的错误，只有通过不断地检查和修改才能解决这些问题。第四，本次实验让我对开发数据库管理系统所运用到的软件如（powerdesigner和 sql server 2005）更加熟悉，也掌握了很多操作的要领，比如利用powerdesigner创建E-R图直到最终生成物理结构模型，在sql server 2005生成数据库，开始的时候我发现数据库中的

24、表有错误时，还要去更改概念结构模型然后再重新生成，这样做费时费力，后来通过老师的讲解才知道powerdesigner只是辅助工具，数据库的修改完全可以在sql server 2005中实现。最后，通过本次课程设计我深刻体会到了团队合作和创新能力的重要性。遇到困难的时候要多尝试、多思考，有可能会发现解决困难的新途径新方法。总之，这次课程设计让我认识到了自身的不足，明确了奋斗的目标。课程设计虽然是告一段落，但是我们探索的脚步却不能停歇。附录：源代码清单1）论文存储过程：ALTER procedure dbo.lunwen_scoreconvert2 F_职工编号intasbegindeclare

25、score2 intdeclare num2 intdeclare S_刊物级别char(50)declare S_分工char(50)declare F_姓名char(50)declare S_论文名称intset S_刊物级别= (select top 1 刊物级别from 论文a,撰写b where b.职工编号= F_职工编号and a.论文名称= b.论文名称)set S_分工= (select top 1 分工from 撰写a where a.职工编号=F_职工编号)set num2 = (select top 1 count(论文名称) from 撰写where 论文名称in (

26、select 论文名称from 撰写where 职工编号=F_职工编号)set F_姓名= (select top 1 a.姓名from 教职员工a,撰写b where a.职工编号=F_职工编号and a.职工编号= (b.职工编号)beginif S_刊物级别='核心期刊' set score2=100 else if S_刊物级别='一般期刊' set score2=90else if S_刊物级别='报纸杂志' set score2=80else if S_刊物级别='会议论文' set score2=70begin if

27、 S_分工='主编' set score2=score2+score2*0.6else if s_分工='副主编' set score2=score2+(score2*0.4)/(num2-1)endendendbegin select result= '职工号：'+str(F_职工编号)+'职工名：'+F_姓名+'论文得分为：'+str(score2,5,1)end2）著作存储过程：set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER procedure dbo.zhu

28、zuo_scoreconvert1 F_职工编号intasbegindeclare score1 intdeclare W_著作类别varchar(10)declare F_姓名varchar(50)set W_著作类别=(select 著作类别from 著作a,创作b where a.职工编号=b.职工编号and b.职工编号=F_职工编号)set F_姓名=(select 姓名from 教职员工a where a.职工编号=F_职工编号)beginif W_著作类别='专著'set score1=100else if W_著作类别='编著'set score

29、1=90else if W_著作类别='教材'set score1=80else set score1=70endendbeginselect result= '职工号：'+str(F_职工编号)+'职工名：'+F_姓名+'著作得分为：'+str(score1,5,1)end3）专利存储过程：ALTER procedure dbo.zhuanli_scoreconvert3 F_职工编号intasbegindeclare score3 int declare O_专利类型char(50)declare O_分工char(50)de

30、clare num3 intdeclare F_姓名char(50)set O_专利类型=(select 专利类型from 专利a,发明b where b.职工编号=F_职工编号and a.专利号=b.专利号)set O_分工=(select 分工from 专利a,发明b where b.职工编号=F_职工编号and a.专利号=b.专利号)set num3=(select count(专利号) from 发明a where 专利号=(select 专利号from 发明b where 职工编号=F_职工编号)set F_姓名=(select a.姓名from 教职员工a,专利b,发明c whe

31、re a.职工编号=c.职工编号and b.专利号=c.专利号and c.职工编号=F_职工编号)begin if O_专利类型='发明'set score3=100else if O_专利类型='实用新型'set score3=90else set score3=80begin if O_分工='主持人' set score3=score3+score3*0.6else set score3=score3+score3*0.4/(num3-1)endendselect result= '职工号：'+str(F_职工编号)+'职工名：'+F_姓名+'专利得分为：'+str(score3)end 4） 论文触发器： ALTER TRIGGER dbo.lunwenzongfen ON db