員之家建通訊站新聞存檔系統(tǒng)設(shè)計(jì)

1、.黨員之家黨建通訊站新聞存檔系統(tǒng)設(shè)計(jì) 課程名稱： 數(shù)據(jù)庫(kù)原理上機(jī) 學(xué) 院： 信息與電氣工程學(xué)院 專業(yè)班級(jí)： 信息1201班 姓 名： 馬棟捷 學(xué) 號(hào)： 31202162 時(shí) 間： 2014年6月 .;新聞存檔系統(tǒng)設(shè)計(jì)一、系統(tǒng)概述黨員之家黨建通訊站新聞存檔系統(tǒng)是基于黨員之家大量新聞稿的存檔問(wèn)題而特別設(shè)計(jì)的一款軟件，該軟件通過(guò)mysql軟件搭建存檔新聞的底層數(shù)據(jù)庫(kù)，通過(guò)Microsoft Visual Studio 2008軟件并使用C#語(yǔ)言搭建了用戶界面，使新聞存檔變得更加清晰。通過(guò)該系統(tǒng)，可以有效的實(shí)現(xiàn)以下三方面功能：1、該系統(tǒng)搭建了清晰的管理平臺(tái)，高效的完成對(duì)大量新聞的存檔工作。2、在該系統(tǒng)

2、中，將存檔以及對(duì)通訊員、拍攝員、編輯員、責(zé)任編輯的考核工作集成為一體，可以在存檔的同時(shí)完成考核工作，高效且方便。3、一人一帳號(hào)，通訊員可以通過(guò)進(jìn)入系統(tǒng)查看自己新聞提交的新聞稿狀態(tài)以及考核情況，編輯可以通過(guò)評(píng)分對(duì)新聞稿質(zhì)量進(jìn)行反饋。二、系統(tǒng)功能、結(jié)構(gòu)設(shè)計(jì)1、黨員之家黨建通訊站新聞存檔系統(tǒng)功能圖2、黨員之家黨建通訊站新聞存檔系統(tǒng)ER圖三、系統(tǒng)底層數(shù)據(jù)庫(kù)設(shè)計(jì)1、關(guān)系模型通訊員(通訊員id，通訊員姓名，所屬部門，班級(jí)，QQ號(hào)碼，短號(hào)，長(zhǎng)號(hào))，即info_corr(corrId，corrName，corrDepar，corrClass，corrQq, corrShortphone, corrLongph

3、one)責(zé)任編輯（責(zé)任編輯ID，責(zé)任編輯姓名，職位），即info_admin(adminId，adminName，adminPost)新聞稿（新聞稿編號(hào)，新聞標(biāo)題，新聞發(fā)生時(shí)間），即info_news(newsNumber，newsName，newsHappen)寫（通訊員ID，新聞稿編號(hào)，編輯員姓名，任務(wù)類型，上交時(shí)間），即inter_write(corrId，newsNumber，writeSubname, writeType, writeSubtime)分?jǐn)?shù)（新聞稿編號(hào), 責(zé)任編輯ID, 字?jǐn)?shù)得分，用詞得分，構(gòu)架得分，主旨得分，病句得分，格式得分，上交得分，照片構(gòu)圖得分，照片清晰度得分，

4、事件反映度得分，人物表情得分 ），即inter_score(newsNumber, adminId, scoreWordnum, scoreWordcorrect, scoreNewsstru, scoreGist, scoreSentence, scoreForm, scoreSubtime, scorePhotostu, scoreDefin, scoreReflect, scoreFace)密碼（用戶名，密碼，身份），即Usert（username， pad，sta）用戶名編輯表（用戶名，責(zé)任編輯ID）inter_usad(username,adminID) 用戶名通訊員表（用戶名，通訊

5、員ID）inter_usco(username,corrID) 2、數(shù)據(jù)庫(kù)創(chuàng)建及約束、索引的設(shè)置DROP DATABASE if exists NewsManage;CREATE DATABASE NewsManage default charset utf8 COLLATE utf8_general_ci;USE NewsManage;#-#表名: info_corr#作者：馬棟捷#日期：2014/3/17 V1.3#描述：通訊員名單#具體內(nèi)容：#1(主鍵) (檢查約束)(非空)corrId： char(8) 通訊員ID#2(非空)corrName： varchar(20) 通訊員姓名#3(

6、索引)(非空)corrDepar： varchar(20) 所屬部門#4corrClass： char(20) 班級(jí)#5corrQq： varchar(15) QQ號(hào)碼#6corrShortphone: char(12) 短號(hào)#7corrLongphone: char(12) 長(zhǎng)號(hào)#-CREATE TABLE info_corr(corrId char(8),corrName varchar(20) not null,corrDepar varchar(20) not null,corrClass char(20),corrQq varchar(15),corrShortphone char(

7、6),corrLongphone char(11),CONSTRAINT pk_info_corr PRIMARYKEY(corrId),CONSTRAINT ck_corrId CHECK (corrId like '0-90-90-90-90-90-90-90-9');CREATE INDEX idx_corrDepar ON info_corr (corrDepar);#-#表名：info_admin#作者：馬棟捷#日期：2014/3/17 V1.3#描述：責(zé)任編輯名單#具體內(nèi)容：#1(主鍵) (檢查約束)(非空)adminId： char(8) 責(zé)任編輯ID#2(非空)

8、adminName： varchar(20) 責(zé)任編輯姓名#3(索引)(非空)adminPost： varchar(20) 職位#-CREATE TABLE info_admin(adminId char(8),adminName varchar(20) not null,adminPost varchar(20) not null,CONSTRAINT pk_info_admin PRIMARY KEY (adminId),CONSTRAINT ck_adminId CHECK (adminId like '0-90-90-90-90-90-90-90-9');CREATE

9、INDEX idx_adminPost ON info_admin (adminPost);#-#表名：info_news#作者：馬棟捷#日期：2014/3/21 V1.3#描述：新聞稿統(tǒng)計(jì)#具體內(nèi)容：#1(主鍵) (檢查約束)(非空)newsNumber： char(8) 新聞稿編號(hào)#2(唯一約束)(非空)newsName： varchar(50) 新聞稿標(biāo)題#3(索引)(非空)newsHappen： date 新聞發(fā)生時(shí)間#-CREATE TABLE info_news(newsNumber char(8),newsName varchar(50) not null,newsHappen

10、date not null,CONSTRAINT pk_info_news PRIMARY KEY (newsNumber),CONSTRAINT un_info_newsName UNIQUE (newsName),CONSTRAINT ck_newsNumber CHECK (newsNumber like '0-90-90-90-90-90-90-90-9');CREATE INDEX idx_newsHappen ON info_news (newsHappen);#-#表名：inter_write#作者：馬棟捷#日期：2014/3/21 V1.3#描述：寫#具體內(nèi)容：

11、#1(外鍵)(非空)corrId: char(8) 通訊員ID#2(外鍵)(非空)newsNumber: char(8) 新聞稿編號(hào)#3(索引)(非空)writeSubname: varchar(20) 編輯員姓名#4(默認(rèn)約束)(非空)writeType: char(20) 任務(wù)類型#5(索引)(非空)writeSubtime: date 上交時(shí)間#-CREATE TABLE inter_write(corrId char(8) not null,newsNumber char(8) not null,writeSubname varchar(20) not null,writeType c

12、har(20) not null DEFAULT "文字",writeSubtime date not null,CONSTRAINT fk_write_corrId FOREIGN KEY (corrId) REFERENCES info_corr(corrId),CONSTRAINT fk_write_newsNumber FOREIGN KEY (newsNumber) REFERENCES info_news(newsNumber);CREATE INDEX idx_writeSubname ON inter_write (writeSubname);CREATE

13、INDEX idx_writeSubtime ON inter_write (writeSubtime);#-#表名：inter_score#作者：馬棟捷#日期：2014/3/21 V1.3#描述：分?jǐn)?shù)#具體內(nèi)容：#1(外鍵)(非空)newsNumberchar(8)新聞稿編號(hào)#2(外鍵)(非空)adminIdchar(8)責(zé)任編輯ID#3scoreWordnumdecimal(4,1)字?jǐn)?shù)得分#4scoreWordcorrectdecimal(4,1)用詞得分#5socreNewsstrudecimal(4,1)構(gòu)架得分#6scoreGistdecimal(4,1)主旨得分#7scoreSe

14、ntencedecimal(4,1)病句得分#8scoreFormdecimal(4,1)格式得分#9scoreSubtimedecimal(4,1)上交得分#10scorePhotostudecimal(4,1)照片構(gòu)圖得分#11scoreDefindecimal(4,1)照片清晰度得分#12scoreReflectdecimal(4,1)事件反映度得分#13scoreFacedecimal(4,1)人物表情得分#-CREATE TABLE inter_score(newsNumber char(8) not null,adminId char(8) not null,writeType c

15、har(20) not null DEFAULT "文字",scoreWordnum decimal(4,1),scoreWordcorrect decimal(4,1),scoreNewsstru decimal(4,1),scoreGist decimal(4,1),scoreSentence decimal(4,1),scoreForm decimal(4,1),scoreSubtime decimal(4,1),scorePhotostu decimal(4,1),scoreDefin decimal(4,1),scoreReflect decimal(4,1),s

16、coreFace decimal(4,1),CONSTRAINT fk_score_newsNumber FOREIGN KEY (newsNumber) REFERENCES info_news(newsNumber),CONSTRAINT fk_score_adminId FOREIGN KEY (adminId) REFERENCES info_admin(adminId);#-#表名：usert#作者：馬棟捷#日期：2014/3/21 V1.3#描述：用戶密碼表#具體內(nèi)容：#1(非空)username: char(10) 用戶名#2(非空)pad: char(10) 密碼#3(非空)s

17、ta:char(10)身份#-CREATE TABLE usert(username char(10) not null,psd char(10) not null,sta char(10) not null);#-#表名：inter_usad#作者：馬棟捷#日期：2014/3/21 V1.3#描述：用戶名、編輯對(duì)應(yīng)關(guān)系#具體內(nèi)容：#1(非空)username: char(10) 用戶名#2adminId ：char(8) 責(zé)任編輯ID#-CREATE TABLE inter_usad(username char(10) not null,adminId char(8);#-#表名：inter

18、_usco#作者：馬棟捷#日期：2014/3/21 V1.3#描述：用戶名、通訊員對(duì)應(yīng)關(guān)系#具體內(nèi)容：#1(非空)username:char(10)用戶名#2corrId：char(8)通訊員ID#-CREATE TABLE inter_usco(username char(10) not null,corrId char(8);四、數(shù)據(jù)庫(kù)記錄的插入U(xiǎn)SE newsmanage;#INSERT INTO info_corr VALUE ('31302036','lly','秘書處','電科','01234567',

19、'011234','#39;);INSERT INTO info_corr VALUE ('31302037','xzx','秘書處','通信','02234567','021234','#39;);INSERT INTO info_corr VALUE ('31302038','xjj','宣拓部','電信','03234567','

20、;031234','#39;);INSERT INTO info_admin VALUE ('31302039','wyt','副站長(zhǎng)');INSERT INTO info_admin VALUE ('31302040','wxl','副站長(zhǎng)');INSERT INTO info_admin VALUE ('31202162','mdj','站長(zhǎng)');INSERT INTO info_news VALUE (&#

21、39;001','副書記會(huì)議','2014-3-10');INSERT INTO info_news VALUE ('002','管委會(huì)例會(huì)','2014-3-21');INSERT INTO info_news VALUE ('003','在杭高校交流','2014-4-1');INSERT INTO info_news VALUE ('004','在杭','2014-4-5');#INSERT INTO in

22、ter_write VALUE ('31302036','001','TSY','write','2014-3-15');#INSERT INTO inter_write VALUE ('31302036','002','TSY','write','2014-3-24');INSERT INTO inter_write VALUE ('31302037','003','YCY','

23、write','2014-4-3');INSERT INTO inter_write VALUE ('31302038','003','LYZ','photo','2014-4-3');INSERT INTO inter_write value ('31302036','004','mmm','write','2014/2/3');INSERT INTO inter_score VALUE ('001&#

24、39;,'31302039','write',100,100,90,80,100,100,100,0,0,0,0);INSERT INTO inter_score VALUE ('002','31302040','write',100,90,90,80,100,90,100,0,0,0,0);INSERT INTO inter_score VALUE ('003','31302039','write',90,90,80,100,100,100,100,0,0,0,0)

25、;INSERT INTO inter_score VALUE ('003','31202162','photo',0,0,0,0,0,0,0,100,100,90,90);INSERT INTO inter_score VALUE ('004','31202162','photo',0,0,0,0,0,0,0,100,100,90,90);INSERT INTO usert VALUE ('jerry','1234',"corr");INSERT

26、INTO usert VALUE ('xd','clc','corr');INSERT INTO inter_usad VALUE ('jerry','31302036');INSERT INTO inter_usco VALUE ('xd','31202162')五、軟件功能設(shè)計(jì)1、注冊(cè)界面 （管理員注冊(cè)界面） （通訊員注冊(cè)界面）（1）注冊(cè)關(guān)鍵代碼：（以通訊員注冊(cè)為例） string Standardm = "0" /初始化一個(gè)變量 Standardm = th

27、is.corrzhuce_num.Text.Trim();/文本框內(nèi)容賦值給變量 Regex digitregex = new Regex("0-9*$");/初始化正則表達(dá)式 0-9d*.?d*$ if (digitregex.IsMatch(Standardm) = false) /判斷文本框內(nèi)容是否符合正則表達(dá)式 MessageBox.Show("學(xué)號(hào)必須為數(shù)字", "錯(cuò)誤"); this.corrzhuce_num.Clear(); this.corrzhuce_num.Focus(); else if (corrzhuce_

28、num.Text.Trim().Length != 8) /判斷學(xué)號(hào)長(zhǎng)度是否為8位且忽略空格 MessageBox.Show("學(xué)號(hào)位數(shù)需要八位", "錯(cuò)誤"); /若不是8位，則彈出對(duì)話框 this.corrzhuce_num.Clear(); this.corrzhuce_num.Focus(); else if (corrzhuce_yhm.Text.Trim().Length > 10) MessageBox.Show("用戶名最大長(zhǎng)度為10！", "錯(cuò)誤"); this.corrzhuce_yhm.

29、Focus(); else if (corrzhuce_psd.Text.Trim() != corrzhuce_psdr.Text.Trim()/若是8位，則執(zhí)行原有功能,判斷兩次密碼是否相同,當(dāng)不同時(shí)： MessageBox.Show("兩次輸入的密碼不同！", "錯(cuò)誤"); this.corrzhuce_psd.Clear(); this.corrzhuce_psdr.Clear(); this.corrzhuce_psd.Focus(); else/執(zhí)行正常程序 MySQLConnection conn = new MySQLConnection

30、(new MySQLConnectionString("localhost", "newsmanage", "root", "5536309").AsString); conn.Open(); int i, j; string sql_1 = null; string sql_name = null; string sql_num = null; string sql_depar = null; string sql_class = null; string sql_qq = null; string sql_sh

31、ort = null; string sql_long = null; sql_num = corrzhuce_num.Text.Trim(); sql_name = corrzhuce_name.Text.Trim(); sql_depar = corrzhuce_depar.Text.Trim(); sql_class = corrzhuce_class.Text.Trim(); sql_qq = corrzhuce_qq.Text.Trim(); sql_short = corrzhuce_short.Text.Trim(); sql_long = corrzhuce_long.Text

32、.Trim(); string chaxun_num = "select * from info_corr where corrId=" + sql_num; /查詢學(xué)號(hào)是否被注冊(cè)過(guò) MySQLCommand cmd_cx_num = new MySQLCommand(chaxun_num, conn); DbDataReader dr = cmd_cx_num.ExecuteReader(); if (!dr.Read() /若未被注冊(cè)過(guò) sql_1 = "INSERT INTO info_corr VALUE ('" + sql_num +

33、"','" + sql_name + "','" + sql_depar + "','" + sql_class + "','" + sql_qq + "','" + sql_short + "','" + sql_long + "');" MySQLCommand cmd_1 = new MySQLCommand(sql_1, conn); strin

34、g sql_yhm; string sql_psd; string sql_2; sql_yhm = corrzhuce_yhm.Text.Trim(); sql_psd = corrzhuce_psd.Text.Trim(); string chaxun_yhm = "select * from Usert where username='" + sql_yhm + "'" MySQLCommand cmd_cx_yhm = new MySQLCommand(chaxun_yhm, conn); DbDataReader dr2 = c

35、md_cx_yhm.ExecuteReader(); /查詢用戶名是否被使用過(guò) if (!dr2.Read() sql_2 = "INSERT INTO usert VALUE ('" + sql_yhm + "','" + sql_psd + "','corr');" MySQLCommand cmd_2 = new MySQLCommand(sql_2, conn); j = cmd_2.ExecuteNonQuery(); /執(zhí)行插入語(yǔ)句 i = cmd_1.ExecuteNonQ

36、uery(); /執(zhí)行插入語(yǔ)句 if (i != -1 && j != -1) /判斷插入語(yǔ)句是否成功 MessageBox.Show("注冊(cè)成功，黨建通訊站歡迎您的加入！", "成功"); string sql_usad = "INSERT INTO inter_usco VALUE ('" + sql_yhm + "','" + sql_num + "');" MySQLCommand cmd_3 = new MySQLCommand(sql_

37、usad, conn); cmd_3.ExecuteNonQuery(); this.Hide(); else MessageBox.Show("注冊(cè)失敗，請(qǐng)與管理員聯(lián)系！", "失敗"); else MessageBox.Show("用戶名已被使用", "失敗"); this.corrzhuce_yhm.Clear(); this.corrzhuce_yhm.Focus(); else MessageBox.Show("該學(xué)號(hào)已經(jīng)注冊(cè)過(guò)，若有疑問(wèn)請(qǐng)聯(lián)系管理員", "失敗")

38、; this.corrzhuce_num.Clear(); this.corrzhuce_num.Focus(); （2）在注冊(cè)界面內(nèi)，還有查詢用戶名是否重復(fù)功能，關(guān)鍵代碼如下：MySQLConnection conn = new MySQLConnection(new MySQLConnectionString("localhost", "newsmanage", "root", "5536309").AsString); conn.Open(); string sql_yhm; string sql_psd;

39、sql_yhm = corrzhuce_yhm.Text.Trim(); sql_psd = corrzhuce_psd.Text.Trim(); string chaxun_yhm = "select * from Usert where username='" + sql_yhm + "'" MySQLCommand cmd_cx_yhm = new MySQLCommand(chaxun_yhm, conn); DbDataReader dr2 = cmd_cx_yhm.ExecuteReader(); /查詢用戶名是否被使用過(guò) i

40、f (!dr2.Read() tishi_corryhm.Text = "" else tishi_corryhm.Text = "已重復(fù)！" this.corrzhuce_yhm.Focus(); （3）此外，還具有判斷兩次輸入密碼是否相同的功能，關(guān)鍵代碼如下：if (corrzhuce_psd.Text.Trim() != corrzhuce_psdr.Text.Trim() tishi_corr.Text = "兩次輸入的密碼不同！" this.corrzhuce_psd.Clear(); this.corrzhuce_psdr

41、.Clear(); this.corrzhuce_psd.Focus(); else tishi_corr.Text = "" 2、登陸界面（1）登陸界面根據(jù)用戶名和選擇的身份，共同判斷進(jìn)入通訊員界面還是編輯員界面，同時(shí)將用戶名傳遞給對(duì)應(yīng)的界面，以幫助進(jìn)行后面的操作。登陸功能的關(guān)鍵代碼如下： MySQLConnection conn = new MySQLConnection(new MySQLConnectionString("localhost", "newsmanage", "root", "553

42、6309").AsString); conn.Open(); string sql = null; if(radioadmin.Checked) sql = "select sta from Usert where username='" + TxtName.Text.Trim().Replace("'", "'") + "' and psd ='" + TxtPassword.Text.Trim().Replace("'", "

43、;'") + "' and sta ='admin'" else sql = "select sta from Usert where username='" + TxtName.Text.Trim().Replace("'", "'") + "' and psd ='" + TxtPassword.Text.Trim().Replace("'", "'")

44、+ "' and sta ='corr'" MySQLCommand cmd = new MySQLCommand(sql, conn); try DbDataReader dr = cmd.ExecuteReader(); if (dr.Read() string sql_sql=cmd.ExecuteScalar().ToString().Trim(); if (sql_sql = "admin") adminmain adminmain_begin = new adminmain(); string cx_name = "select adminName from inter_usad , info_admin where inter_usad.adminId=info_admin.