在線考試系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)

1、西南財(cái)經(jīng)大學(xué)Southwestern University of Finance andJrEconomics A課程實(shí)驗(yàn)報(bào)告fT;課程名稱：數(shù)據(jù)庫學(xué)生姓名：周小銖、李佳穎學(xué) 院：經(jīng)濟(jì)信息工程學(xué)院專 業(yè)：計(jì)算機(jī)科學(xué)與技術(shù)指導(dǎo)教師：李玉榮.Word文檔1 .課程設(shè)計(jì)題目與要求1.1 數(shù)據(jù)庫設(shè)計(jì)題目設(shè)計(jì)和實(shí)現(xiàn)一個(gè)網(wǎng)上在線考試系統(tǒng)，系統(tǒng)應(yīng)完成以下基本的業(yè)務(wù)：a.至少兩個(gè)教師，每個(gè)教一門課程b.每個(gè)班至少包含十名學(xué)生。c.教師能夠修改試卷的題目并模仿一名同學(xué)參加一門考試d.教師可提取一份試卷查卷e.教師可檢查其所教班級(jí)的成績、任一學(xué)生的成績以及查詢?nèi)嗟钠骄煽兒涂偝煽儭.每一套題至少包括十道選擇題

2、g.選擇題自動(dòng)閱卷，書寫題人工閱卷以上很粗略描述了網(wǎng)上考試系統(tǒng)的基本要求，你們可以根據(jù)實(shí)際問題的需要，補(bǔ)充和細(xì)化系統(tǒng)的要求。本課程設(shè)計(jì)包括兩個(gè)部分：數(shù)據(jù)庫和用戶界面（用戶圖形化界面基本要求就是登錄加試卷內(nèi)容的提取）1.2 數(shù)據(jù)庫設(shè)計(jì)要求a.使用java/c語言，源程序要有適當(dāng)?shù)淖⑨專钩绦蛞子陂喿xb.建議作出用戶界面c.學(xué)生可自動(dòng)增加新功能模塊2 .數(shù)據(jù)庫設(shè)計(jì)過程2.1 需求分析2.1.1 業(yè)務(wù)范圍a.增加、刪除、維護(hù)學(xué)生賬號(hào)，老師賬號(hào)，課程信息和選課信息b.在線出題、出題過程可修改試題、答題、分?jǐn)?shù)c.學(xué)生若有疑問可以由老師提出自己的答卷查卷d.統(tǒng)計(jì)一個(gè)班的學(xué)生的成績情況供老師分析e.提取試題

3、2.1.2 業(yè)務(wù)流程a.進(jìn)入在線考試系統(tǒng)b.通過輸入賬號(hào)密碼判斷身份，分別進(jìn)入不同界面：(1)進(jìn)入管理員界面后獲取管理員身份。管理員具有對(duì)整個(gè)系統(tǒng)的管理權(quán)限，可以管理所有賬戶，管理課程，以及對(duì)選課過程控制。(2)進(jìn)入教師界面后獲取教師身份。教師有權(quán)自出題、對(duì)題庫進(jìn)行管理以及對(duì)學(xué)生試卷進(jìn)行查詢。進(jìn)入出題模塊后，教師可自主命題，命題內(nèi)容分為單選題和填空題。進(jìn)入改題模塊后，首先判斷是否已有人做題，若已存在完成題目的記錄，則執(zhí)行修改題目功能；若無學(xué)生完成題目的記錄，則執(zhí)行修改題目和答案兩項(xiàng)功能。進(jìn)入查詢模塊后，教師可執(zhí)行試卷、答卷以學(xué)生成績的查詢功能。(3)進(jìn)入學(xué)生考試界面后，學(xué)生可選擇參加考試或成績

4、查詢。c.結(jié)束所有操作，退出界面。2.1.3 業(yè)務(wù)流程圖2.2 概念結(jié)構(gòu)設(shè)計(jì)2.2.1 步驟a.抽象數(shù)據(jù)并設(shè)計(jì)局部視圖b.集成局部視圖，得到全局概念結(jié)構(gòu)2.2.2 原則a.忠實(shí)性（設(shè)計(jì)忠實(shí)于應(yīng)用的具體要求，恰當(dāng)?shù)胤从超F(xiàn)實(shí)）b.盡可能減少冗余c.簡單性考慮2.2.3具體實(shí)現(xiàn). a ， SX3奪 JL 1 1 JL (1 )(eicji Qmum/X/、/nr- call manastu；調(diào)用完成。SQL select * Fron student where sno=J;&NUSNHMEFOE靠WORD41419041張牛輯123456登入老師后出題:T se lect * f ron t xt

5、 1g wIwfo cno=J 0004J and tntleno-041 ；NO TITLENOITLEOOHTENTNEWERSCORE40一S容容容向內(nèi)內(nèi)內(nèi)老師修改題目:SQL call XGTMf0印04修改后,用完成。SQL select * fro fi title where cno=, 00B4J and tit leno=, 84* ；pO TITLENOIITLECONTENTANSUEPSCOREM004 Q4修改后ns老師查卷（生成的視圖）：運(yùn)行結(jié)果:IJIn容容魯香正日內(nèi)內(nèi)內(nèi)內(nèi)安甯麴項(xiàng)用迎您RS日蠢選詵選褥的哈容有空rnnJ后 普內(nèi)內(nèi)內(nèi)茉 對(duì)項(xiàng)法項(xiàng)t-Ll 匚選空選摘

6、的容容容春者正曹內(nèi)內(nèi)內(nèi)案W百頁史項(xiàng)r立一之二3工T工工 分 日選選選謊確的幅容容走 內(nèi)內(nèi)內(nèi)一耋 40項(xiàng)項(xiàng)呼頂壕L 日進(jìn)選選選確的分試卷提取:W谷容容容歸內(nèi)內(nèi)內(nèi)內(nèi)時(shí)頂頂頂頂目選選藉#容塞省-內(nèi)內(nèi)內(nèi)內(nèi)瓦員負(fù)貝容容容容-內(nèi)內(nèi)內(nèi)內(nèi)項(xiàng)項(xiàng)項(xiàng)項(xiàng)選選選選容內(nèi)的一日選選選選87題6目的內(nèi)容施項(xiàng)內(nèi)叁 淳項(xiàng)電交 選項(xiàng)出石1逝目H的內(nèi)容這是一道填空題一電2題目12的內(nèi)容這是一道填空超一.，查看相關(guān)成績信息:9SN0SHANEGE A DE梟一如央良小美三天趙王李周王李劉胡505000555 098779978991410S51 1110050 141009 141084S 1410047 141SB46 1415FM

7、S 1413844 141B043 1410842BQL5- Ee lect augjgiade f rem sc 2 Vhcre sc-cno=JO001J i ?AUGselect * from sc wheresno=J41410051J；CNOSNOGRADE0S014141SCSI95pW241410051質(zhì)0341410051登陸界面:D http:/loc a I host:8084/WebApp li cationl /loginjspH:應(yīng)用 D網(wǎng)址導(dǎo)航D百度白霞 口天滿日尚寶特賣B裁戲大全D歡迎便自在線考試系統(tǒng)歡迎使用在線考試系統(tǒng)歡迎登錄！退出一4.源代碼4.1 建表/課程

8、表CREATE TABLE COURSE(CNO CHAR(4) NOT NULL,CNAME VARCHAR2(20) NOT NULL,CONSTRAINT COURSE_PK PRIMARY KEY(CNO)ENABLE);/創(chuàng)建學(xué)生信息表CREATE TABLE STUDENT(SNO CHAR(9) NOT NULL,SNAME CHAR(20) NOT NULL,PASSWORD VARCHAR2(20) NOT NULL,CONSTRAINT STUDENT_PK PRIMARY KEYSNO)ENABLE)；/創(chuàng)建選課表CREATE TABLE SC(CNO CHAR(4) N

9、OT NULL,SNO CHAR(9) NOT NULL,GRADE CHAR(4),CONSTRAINT SC_PK PRIMARY KEY(CNO,SNO)ENABLE);ALTER TABLE SCADD CONSTRAINT SC_FK1 FOREIGN KEY(CNO)REFERENCES COURSE(CNO)ENABLE;ALTER TABLE SCADD CONSTRAINT SC_FK2 FOREIGN KEY(SNO)REFERENCES STUDENT(SNO)ENABLE;/老師信息表CREATE TABLE TEACHER(TID CHAR(9) NOT NULL,P

10、ASSWORD VARCHAR2(20),CONSTRAINT TEACHER_PK PRIMARY KEY(TID)ENABLE);/試題信息表CREATE TABLE TITLE(CNO CHAR(4) NOT NULL,TITLENO CHAR(4) NOT NULL,TITLECONTENT VARCHAR2(40),ANSWER VARCHAR2(10),SCORE CHAR(4),CONSTRAINT TITLE_PK PRIMARY KEYCNO,TITLENO)ENABLE)；ALTER TABLE TITLEADD CONSTRAINT TITLE_FK1 FOREIGN K

11、EY(CNO)REFERENCES COURSE(CNO)ON DELETE CASCADE ENABLE;/創(chuàng)建學(xué)生答題表CREATE TABLE PAPER(CNO CHAR(4) NOT NULL,SNO CHAR(9) NOT NULL,TITLENO CHAR(4) NOT NULL,SANSWER VARCHAR2(10),GETSCORE CHAR(4),CONSTRAINT PAPER_PK PRIMARY KEY(CNO,SNO,TITLENO)ENABLE);ALTER TABLE PAPERADD CONSTRAINT PAPER_FK1 FOREIGN KEY(CNO)

12、REFERENCES COURSE(CNO)ON DELETE CASCADE ENABLE;ALTER TABLE PAPERADD CONSTRAINT PAPER_FK2 FOREIGN KEY(SNO)REFERENCES STUDENT(SNO)ON DELETE CASCADE ENABLE;ALTER TABLE PAPERADD CONSTRAINT PAPER_FK3 FOREIGN KEY(CNO,TITLENO)REFERENCES TITLE(CNO,TITLENO)ON DELETE CASCADE ENABLE;CREATE USER teacerl IDENTIF

13、IED BY 123456 ;4.2 存儲(chǔ)過程/管理員存儲(chǔ)過程Create or replace procedure manastu(sno in CHAR,sname in char,password in varchar2)IsBeginInsert into studentValues(SNO, SNAME, PASSWORD);Commit;End;CALL MANASTU (4141051,趙日天，123456);Create or replace procedure manatea(tid in CHAR,password in varchar2)IsBeginInsert int

14、o teacherValues(TID, PASSWORD);Commit;End;CALL MANATEA (teacherT,123456);Create or replace procedure manacou(cno in CHAR,cname in varchar2)IsBeginInsert into courseValues(CNO, cname);Commit;End;CALL MANACOU (0001,課程 1);Create or replace procedure manasc(cno in CHAR,sno in char)IsBeginInsert into SCV

15、alues(cno, sno,NULL);Commit;End;CALL MANASC (0001,41410051);/出題存儲(chǔ)過程Create or replace procedure ks(Cno_input in CHAR,Sno_input in char,Titleno_input in char,Sanswer_input in varchar2)Isanswer1 varchar2(10);scorel char(4);Cno1 char(4);Sno1 char(9);BeginSelect cno,sno INTO cno1,sno1 from scWHERE o=cno_

16、input and sc.sno = sno_input;exceptionwhen no_data_foundthen dbms_output.put_line(沒有選課不能考試！);select answer,score INTO answer1,score1 FROM titleWHERE o=cno_input AND title.titleno=Titleno_input;ifSanswer_input = answer1 THENInsert into paperValues(cno_input,sno_input,titleno_input,sanswer_inut,score1

17、); elseInsert into paperValues(cno_input,sno_input,titleno_input,sanswer_inut,0);END IF;Commit;End;Call KS(000T,41410051,0T,B);修改題目存儲(chǔ)過程/修改題目create or replace procedure XGTM(cno_input in char,titleno_input in char,titlecontent_input in varchar2)isbeginupdate title settitle.titlecontent = titlecontent

18、_inputWHERE o = cno_input AND title.titleno = titleno_input;commit;end;/修改答案create or replace procedure XGDA(cno_input in char,titleno_input in char,answer_input in varchar2)isbeginupdate title settitle.answer = answer_inputWHERE o = cno_input AND title.titleno = titleno_input;commit;end;/修改分?jǐn)?shù)create

19、 or replace procedure XGFS(cno_input in char,titleno_input in char,score_input in char)isbeginupdate title settitle.score = score_inputWHERE o = cno_input AND title.titleno = titleno_input;commit;end;/學(xué)生查卷過程create or replace procedure CJ (cno_input in char,sno_input in char)isv_sql varchar2(1024);be

20、ginv_sql := create or replace VIEWPAPER_VIEW(titleno,titlecontent,answer,sanswer,gerscore)ASSELECTtitle.titleno,title.titlecontent,title.answer,paper.sanswer,paper.getscoreFROMtitle,paperWHEREo=o AND title.titleno=paper.titleno ANDpaper.sno=sno_input AND o=cno_inputorder by titleno;EXECUTE IMMEDIATE

21、 v_sql using cno_input,sno_input;end;成績統(tǒng)計(jì)過程(學(xué)生考試完成點(diǎn)擊提交時(shí))create or replace procedure TJ(cno_input in char,sno_input in char)istotle number(10,0);beginselect SUM(GETSCORE) into totle from paperWHERE paper.CNO = cno_input AND paper.SNO = sno_input;update sc set sc.grade = totleWHERE sc.CNO = cno_input

22、AND SC.SNO = sno_input;commit;end;call TJ (0001,41410050);4.3 查詢查卷（視圖）CREATE or replace VIEWPAPER_VIEW_41410051（titleno,titlecontent,answer,sanswer,gerscore）AS SELECTtitle.titleno,title.titlecontent,title.answer,paper.sanswer,paper.getscoreFROMtitle,paperWHEREo=o AND title.titleno=paper.titleno ANDp

23、aper.sno=41410051order by titleno;查卷（sqlplus）set linesize 200 pagesize 999 newpage;Col answer format a47;SELECTtitle.titleno,title.titlecontent,正確答案是,title.answer,你的答案是,paper.sanswer,得分，paper.getscoreFROMtitle,paperWHEREo=o AND title.titleno=paper.titleno ANDpaper.sno=41410051order by titleno;/查看試題S

24、ELECTtitle.titleno,title.titlecontentFROMtitleWHEREo=000Torder by titleno;/查看課程考試成績情況Selectstudent.sno,student.sname,sc.gradefrom student,scwhere o=0001 ANDsc.sno = student.sno;/查看課程平均分Select avg(grade) from scWhere o=0001;4.4 登陸設(shè)計(jì)由于做登陸的時(shí)候分表查詢用戶信息一直出bug,經(jīng)過搜索解決辦法后決定建立一個(gè)教師學(xué)生用戶表來解決問題create table P_USER

25、(username CHAR(9),password VARCHAR2(20),)從此表中只判斷登陸是否成功。4.4.1 jsp頁面部分login.jsp login歡迎使用在線考試系統(tǒng)用戶名： 密碼：Welcome.jsplogin歡迎使用在線考試系統(tǒng)歡迎登陸！4.4.2 servlet 編寫UserServlet.javapackage com.baosight.servlet;import java.io.IOException;import java.io.PrintWriter;import javax.servlet.ServletException;import javax.se

26、rvlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.baosight.bean.UserBean;/* Title:UserServlet * Description:TODO * Company: * author zxy* date 2016-12-26 下午 10:50:57*/public class UserServlet extends HttpServlet public void d

27、oGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException doPost(request,response);public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException String method = request.getParameter(method);if(login.equals(me

28、thod)/ 登錄String username = request.getParameter(username);String password = request.getParameter(password);if(username=null|.equals(username.trim()|password=null|.equals(pas sword.trim()System.out.println(用戶名或密碼不能為空!);response.sendRedirect(login.jsp);return;UserBean userBean = new UserBean();boolean

29、 isValid = userBean.valid(username,password);if(isValid)System.out.println(登錄成功！);request.getSession().setAttribute(username, username);response.sendRedirect(welcome.jsp);return;elseSystem.out.println(用戶名或密碼錯(cuò)誤！);response.sendRedirect(login.jsp);return;else if(logout.equals(method)/ 退出登錄System.out.pr

30、intln(退出登錄！);request.getSession().removeAttribute(username);response.sendRedirect(login.jsp);return;4.4.3 javabeans 編寫DBAcess.javapackage com.baosight.bean;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/*數(shù)據(jù)庫操

31、作類* Title:DBAcess * Description:TODO * Company: * author zxy* date 2016-12-25 下午 12:40:24*/public class DBAcess private String driver = oracle.jdbc.driver.OracleDriver;private String url = jdbc:oracle: + thin:25:1158:orcl;private String username = system;private String password = 123456;pr

32、ivate Connection conn;private Statement stm;private ResultSet rs;/創(chuàng)建連接public boolean createConn() boolean b = false;try Class.forName(driver);/ 加載 Oracle 驅(qū)動(dòng)程序conn = DriverManager.getConnection(url, username, password);b = true; catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrac

33、e();/獲取連接catch (ClassNotFoundException e) / TODO Auto-generated catch blocke.printStackTrace();return b;/查詢public void query(String sql)try stm = conn.createStatement();rs = stm.executeQuery(sql); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace();判斷有無數(shù)據(jù)public boolean next(

34、)boolean b = false;try if(rs.next() b = true; catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace();return b;/獲取表字段值public String getValue(String field) String value = null;try if (rs != null) value = rs.getString(field); catch (SQLException e) / TODO Auto-generated catch bloc

35、k e.printStackTrace();return value;/關(guān)閉連接public void closeConn() try if (conn != null) conn.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace();關(guān)閉 statementpublic void closeStm() try if (stm != null) stm.close(); catch (SQLException e) / TODO Auto-generated catch block e.printStackTrace();關(guān)閉 ResultSe