科研管理信息系統(tǒng)數(shù)據(jù)庫設(shè)計

1、1. 問題描述1.1背景理論背景：某高等學(xué)校擬建立一套科研管理信息系統(tǒng)，實現(xiàn)對科研工作的信息化管理，要求系統(tǒng)對數(shù)據(jù)的存儲完整，查詢迅速，操作方便。實際背景：建立一套完整的數(shù)據(jù)庫系統(tǒng)，使管理員和用戶能夠?qū)Σ煌瑱?quán)限的數(shù)據(jù)進(jìn)行查詢，修改等操作，加強(qiáng)學(xué)校對科研工作的研究，通過這一數(shù)據(jù)庫管理系統(tǒng)促進(jìn)教師的工作水平。1.2數(shù)據(jù)需求通過分析，本系統(tǒng)需要輸入的數(shù)據(jù)如下：（1）學(xué)院：包括學(xué)院編號，學(xué)院名稱（2）行政機(jī)關(guān)：單位名稱，單位地址（3）系：系名稱，學(xué)院編號，聯(lián)系電話（4）教職員工：職工編號，姓名，系名稱，單位名稱，性別，出生年月，政治面貌，籍貫，畢業(yè)院校，聯(lián)系電話（5）科研活動的信息：承擔(dān)課題，發(fā)明專利

2、，撰寫論文，創(chuàng)作著作（6）科研項目的信息：課題（立項，鑒定，獲獎），專利，論文，著作（7）課題：課題編號，課題名稱，承擔(dān)單位，合作單位（8）專利：專利號，專利名稱，授予時間，主持人，人員，專利類型，總分（9）論文：論文名稱，主編，副主編，出版刊物名稱，刊號，刊物級別，總分（10）著作：書名，出版社，出版時間，職工編號，姓名，著作類別，總分（11）課題立項：立項編號，課題編號，課題來源，得分，參與人員，職工編號（12）課題鑒定：鑒定編號，課題編號，成果等級，參與人員，得分，職工編號（13）課題獲獎：獲獎編號，課題編號，獲獎級別，姓名，職工編號，獲獎總得分1.3事務(wù)需求1、 要求能錄入、修改和刪除

3、個人科研信息詳情。2、 要求能按系，學(xué)院、部門或科研種類統(tǒng)計所有人員各項科研項目情況。3、 要求可以按時間（某年或某時間段）查詢及統(tǒng)計個人、各單位的科研項目詳細(xì)情況。4、 計算科研得分情況。按評分規(guī)則將計算的得分存儲，以便將來統(tǒng)計查詢。5、 要求能夠修改或設(shè)定評分規(guī)則。6、 要求可能以從各個口徑統(tǒng)計得分，如個人各項得分，各單位各項得分，加上年份控制。7、 要求能使用餅圖、柱狀圖、折線圖、趨勢圖等顯示相關(guān)信息。8、 輸出要求能打印正規(guī)報表，能導(dǎo)出到excel或word。9、 要求有權(quán)限管理、分級審核功能。除各單位管理員外，個人只可以維護(hù)自己的科研信息。個人科研信息提交后，由所在單位管理員審核，審

4、核后，個人不能修改和刪除已審核的科研信息。所在單位管理員審核無誤后提交學(xué)?？蒲泄芾韱T審核，提交后所在單位管理員不能對該記錄進(jìn)行修改和刪除。2. 解決方案(或數(shù)據(jù)庫系統(tǒng)設(shè)計)2.1 E-R模型設(shè)計1）選擇fileànew modelàConceptual Data Model，建立CDM模型（即概念數(shù)據(jù)模型）。2）創(chuàng)建實體3）雙擊創(chuàng)建的實體符號，在此窗口“General”標(biāo)簽中可以輸入實體的名稱、代碼、描述等信息。4）添加實體屬性：上述窗口的“Attribute”選項標(biāo)簽上可以添加屬性。5）建立聯(lián)系：在圖形窗口中創(chuàng)建兩個實體后，單擊Relationship工具，單擊一個實體，

5、在按下鼠標(biāo)左鍵的同時把光標(biāo)拖至別一個實體上并釋放鼠標(biāo)左鍵，這樣就在兩個實體之間創(chuàng)建了聯(lián)系，右鍵單擊圖形窗口，釋放工具。2.2 邏輯模型toolsàGenerate Physical Data Model。從DBMS下拉列表框選擇 Microsoft SQL Server 2005選擇 Share 單選按鈕，單擊OK即可。2.3數(shù)據(jù)庫實現(xiàn)1） 選擇Database> Generate Database。設(shè)置directory和filename，單擊確定。2）首先啟動SQL Server2005建立數(shù)據(jù)庫，執(zhí)行腳本，生成數(shù)據(jù)庫對象。執(zhí)行腳本的代碼如下：（3）分析腳本，運(yùn)行腳本，在S

6、QL server2005中生成數(shù)據(jù)庫，表的結(jié)構(gòu)如下：2.4處理流程圖（1）觸發(fā)器：lunwenzongfen功能：在向論文表填寫刊物級別時，自動填寫總分列，判斷論文總分。論文編號刊物級別核心期刊10分一般期刊8分報紙雜志6分會議論文4分（2）觸發(fā)器：zhuzuozongdefen 功能：在向著作表填寫著作類別時，自動填寫總分列，判斷著作總分。書名著作類別專著10分編著9教材8譯著7（3）觸發(fā)器：defen 功能：在向?qū)＠硖顚憣＠愋蜁r，自動填寫總分列，判斷專利總分。專利號專利類型發(fā)明10分實用新型8分外觀設(shè)計6分（4）存儲過程：1. 存儲過程：lunwen_scoreconvert22.

7、功能：通過職工編號查詢個人單項得分，如論文。論文編號刊物級別核心期刊10分一般期刊8分報紙雜志6分會議論文4分職工編號分工主編 60%副主編 40%得分（5）存儲過程：zhuzuo_scoreconvert1 功能：通過職工編號獲得個人著作得分。書名著作類別專著10分編著9分譯著8分教材7分職工編號分工作者 80%助手 20%最終得分（6）存儲過程：zhuanli_scoreconvert3功能：通過職工編號獲得個人得分。專利號專利類型發(fā)明10分實用新型8分外觀設(shè)計6分職工編號分工主持人 60%人員 40%最終得分3. 系統(tǒng)實現(xiàn)3.1開發(fā)環(huán)境本系統(tǒng)適用于微型機(jī)，內(nèi)存容量為512M，外部設(shè)備有主

8、機(jī)，顯示器，鍵盤，鼠標(biāo)。采用的結(jié)構(gòu)化查詢語言（SQL）,運(yùn)用到的軟件有：powerdesigner（運(yùn)行環(huán)境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) /編寫評分規(guī)則endendendbegin select result= '職工號：'+str(F_職工編號)+'職工名：'+F_姓名+'論文得分為：'+str(score2,5,1) /設(shè)置顯示方法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,創(chuàng)作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 /編寫評分規(guī)則endendbeginselect result= '職工號：'+str(F_職工編號)+'職工名：'+F_姓名+'著作得分為：'+str(score1,5,1) /設(shè)置顯示方法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,發(fā)明b where b.職工編號=F_職工編號and a.專利號=b.專利號)set O_分工=(select 分工from 專利a,發(fā)明b where b.職工編號=F_職工編號and a.專利號=b.專利號)set num3=(select count(專利號) from 發(fā)明a where 專利號=(select 專利號from 發(fā)明b where 職工編號=F_職工編號)set F_姓名=(select a.姓名from 教職員工a,專利b,發(fā)明c where a.職工編號=c.職工編號and b.專利號=c.專利號and c.

15、職工編號=F_職工編號)begin if O_專利類型='發(fā)明'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) /編寫評分規(guī)則endendselect result= '職工號：'+str(F_職工編號)+'職工名：'+F_姓名+'專利得分為：&

16、#39;+str(score3) /設(shè)置顯示方法end 1） 論文觸發(fā)器：/根據(jù)刊物級別來判定得分 ALTER TRIGGER dbo.lunwenzongfen /定義觸發(fā)器名稱 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 /定義給分規(guī)則2） 著作觸發(fā)器：/著作數(shù)據(jù)發(fā)生變更時相關(guān)表內(nèi)容相應(yīng)發(fā)生變化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） 專利觸發(fā)器：ALTER TRIGGER dbo.defen ON dbo.專利 FOR INSERTAS DECL

19、ARE 專利號char(10)DECLARE 專利類型char(10)SET 專利類型=(SELECT 專利類型FROM INSERTED)IF 專利類型='發(fā)明'BEGINUPDATE 專利SET 總分='10'ENDELSEIF 專利類型='實用新型'BEGINUPDATE 專利SET 總分='8'ENDELSEIF 專利類型='外觀設(shè)計'BEGINUPDATE 專利SET 總分='6'END3.3數(shù)據(jù)庫系統(tǒng)調(diào)試情況遇到的問題：（1）生成數(shù)據(jù)庫以后，填寫數(shù)據(jù)時很多表都發(fā)生沖突，不能正常填入數(shù)據(jù)，

20、原因是用powerdesigner設(shè)計概念結(jié)構(gòu)模型是一些數(shù)據(jù)類型和長度沒有按實際情況來設(shè)置。（2）給表創(chuàng)建約束時發(fā)生沖突。（3）創(chuàng)建觸發(fā)器時，由于一些數(shù)據(jù)是事先填入的，導(dǎo)致觸發(fā)器不能正常觸發(fā)，總是出現(xiàn)錯誤的結(jié)果。（4）創(chuàng)建存儲過程計算個人總得分的時候，總是出現(xiàn)語法錯誤，有一些細(xì)節(jié)沒有注意到。（5）按照老師所給的實驗要求中的評分規(guī)則編寫代碼時，計算多人的分?jǐn)?shù)分配時不能正常運(yùn)行，出現(xiàn)的分?jǐn)?shù)結(jié)果都是一樣的。（6）在把存儲過程的計算結(jié)果導(dǎo)入指定的表，指定的列時，運(yùn)用執(zhí)行存儲過程的語句，未能成功，總是提示返回的是空值。（7）為了實現(xiàn)從各口徑獲得得分，方便查詢的功能時創(chuàng)建了視圖，其中幾個視圖出現(xiàn)了冗余，信

21、息重復(fù)次數(shù)很多，占用大量空間。3.4 問題的解決（1）在填寫數(shù)據(jù)庫信息的過程中，不斷修改表中的數(shù)據(jù)類型和數(shù)據(jù)長度。（2）創(chuàng)建了檢查性約束，如要求中提到的專利類型（發(fā)明、實用新型、外觀設(shè)計）（3）為了解決觸發(fā)器不能觸發(fā)的難題，我向表中重新填入了數(shù)據(jù)，將以前的數(shù)據(jù)刪除。（4）存儲過程按照老師給的實例來編寫，雖然避免的出錯，但是卻將過程簡化，實現(xiàn)的功能也就不完整了。（5）導(dǎo)出存儲過程的執(zhí)行結(jié)果和表的冗余等問題還沒有更好的解決方案。3.5 結(jié)論（1） 系統(tǒng)能夠?qū)崿F(xiàn)一些基本的功能，結(jié)構(gòu)比較簡單。（2） 能夠統(tǒng)計個人單項得分，各項總得分。（3） 能夠?qū)崿F(xiàn)數(shù)據(jù)的查詢，增刪改等一些操作。（4） 很多功能未能實

22、現(xiàn)，比如各單位各項的得分，年份控制等。（5） 數(shù)據(jù)庫中有一些表中存在冗余。（6） 改進(jìn)意見：明確各模塊的功能，避免重復(fù)。避免冗余。4. 結(jié)束語通過本次課程設(shè)計，我收獲良多。首先，我認(rèn)識到了老師在課堂上所講的基礎(chǔ)知識的重要性，此次課程設(shè)計的很多內(nèi)容看似復(fù)雜，其實運(yùn)用到的確實最簡單的知識。這對我來說既是教訓(xùn)又是鼓勵，以后一定要加強(qiáng)對知識的積累，熟練掌握，靈活運(yùn)用。其次，我對自己所學(xué)的專業(yè)有了更深層次的了解，也改正了我對本專業(yè)一直以來存有的錯誤認(rèn)識。比如這次課程設(shè)計所涉及的高等院?？蒲泄芾硇畔⑾到y(tǒng)，就與現(xiàn)實有很密切的聯(lián)系，在將來的工作當(dāng)中，我們很有可能遇到類似的問題，這對我們將來的就業(yè)、擇業(yè)也產(chǎn)生了

23、很深遠(yuǎn)、很有意義的影響。第三，此次課程設(shè)計鍛煉了我的實際操作能力，很多理論上的東西看起來簡單，但是真的要實踐起來還是需要付出很多努力的，比如編寫存儲過程，看老師給的實例代碼感覺很容易，也明白代碼的含義，但是與實際問題聯(lián)系起來需要自己編寫時，總會出現(xiàn)這樣那樣的錯誤，只有通過不斷地檢查和修改才能解決這些問題。第四，本次實驗讓我對開發(fā)數(shù)據(jù)庫管理系統(tǒng)所運(yùn)用到的軟件如（powerdesigner和 sql server 2005）更加熟悉，也掌握了很多操作的要領(lǐng)，比如利用powerdesigner創(chuàng)建E-R圖直到最終生成物理結(jié)構(gòu)模型，在sql server 2005生成數(shù)據(jù)庫，開始的時候我發(fā)現(xiàn)數(shù)據(jù)庫中的

24、表有錯誤時，還要去更改概念結(jié)構(gòu)模型然后再重新生成，這樣做費(fèi)時費(fèi)力，后來通過老師的講解才知道powerdesigner只是輔助工具，數(shù)據(jù)庫的修改完全可以在sql server 2005中實現(xiàn)。最后，通過本次課程設(shè)計我深刻體會到了團(tuán)隊合作和創(chuàng)新能力的重要性。遇到困難的時候要多嘗試、多思考，有可能會發(fā)現(xiàn)解決困難的新途徑新方法。總之，這次課程設(shè)計讓我認(rèn)識到了自身的不足，明確了奮斗的目標(biāo)。課程設(shè)計雖然是告一段落，但是我們探索的腳步卻不能停歇。附錄：源代碼清單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,創(chuàng)作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,發(fā)明b where b.職工編號=F_職工編號and a.專利號=b.專利號)set O_分工=(select 分工from 專利a,發(fā)明b where b.職工編號=F_職工編號and a.專利號=b.專利號)set num3=(select count(專利號) from 發(fā)明a where 專利號=(select 專利號from 發(fā)明b where 職工編號=F_職工編號)set F_姓名=(select a.姓名from 教職員工a,專利b,發(fā)明c whe

31、re a.職工編號=c.職工編號and b.專利號=c.專利號and c.職工編號=F_職工編號)begin if O_專利類型='發(fā)明'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） 論文觸發(fā)器： ALTER TRIGGER dbo.lunwenzongfen ON db