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

1、西南財(cái)經(jīng)大學(xué)Southwestern University of Finance and Economics課程實(shí)驗(yàn)報(bào)告課程名稱： 數(shù)據(jù)庫(kù)學(xué)生姓名： 周小鈺、李佳穎 學(xué) 院： 經(jīng)濟(jì)信息工程學(xué)院 專 業(yè)： 計(jì)算機(jī)科學(xué)與技術(shù) 指導(dǎo)教師： 李 玉 榮 . 2016年 12月1. 課程設(shè)計(jì)題目與要求1.1數(shù)據(jù)庫(kù)設(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í)的成績(jī)、任一學(xué)生的成績(jī)以及查詢?nèi)嗟钠骄煽?jī)和總成績(jī)。f

2、. 每一套題至少包括十道選擇題g. 選擇題自動(dòng)閱卷，書寫題人工閱卷以上很粗略描述了網(wǎng)上考試系統(tǒng)的基本要求，你們可以根據(jù)實(shí)際問題的需要，補(bǔ)充和細(xì)化系統(tǒng)的要求。本課程設(shè)計(jì)包括兩個(gè)部分:數(shù)據(jù)庫(kù)和用戶界面（用戶圖形化界面基本要求就是登錄加試卷內(nèi)容的提?。?.2數(shù)據(jù)庫(kù)設(shè)計(jì)要求a.使用java/c語(yǔ)言，源程序要有適當(dāng)?shù)淖⑨專钩绦蛞子陂喿xb.建議作出用戶界面c.學(xué)生可自動(dòng)增加新功能模塊2. 數(shù)據(jù)庫(kù)設(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é)生的成績(jī)

3、情況供老師分析e.提取試題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ì)題庫(kù)進(jìn)行管理以及對(duì)學(xué)生試卷進(jìn)行查詢。進(jìn)入出題模塊后，教師可自主命題，命題內(nèi)容分為單選題和填空題。進(jìn)入改題模塊后，首先判斷是否已有人做題，若已存在完成題目的記錄，則執(zhí)行修改題目功能；若無(wú)學(xué)生完成題目的記錄，則執(zhí)行修改題目和答案兩項(xiàng)功能。進(jìn)入查詢模塊后，教師可執(zhí)行試卷、答卷以學(xué)生成績(jī)的查詢功能。（3）進(jìn)入學(xué)生考試界面后

4、，學(xué)生可選擇參加考試或成績(jī)查詢。c.結(jié)束所有操作，退出界面。 2.1.3業(yè)務(wù)流程圖 圖1 業(yè)務(wù)流程圖2.2概念結(jié)構(gòu)設(shè)計(jì)步驟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.簡(jiǎn)單性考慮2.2.3具體實(shí)現(xiàn) 圖2-1 總ER圖圖2-2 管理員圖2-3 選課表圖2-4 課程信息圖2-5 填空題庫(kù)圖2-6 學(xué)生圖2-7 答卷圖2-8 試卷2.3邏輯結(jié)構(gòu)設(shè)計(jì)2.3.1設(shè)計(jì)目的把概念結(jié)構(gòu)設(shè)計(jì)階段設(shè)計(jì)好的概念模型轉(zhuǎn)換為邏輯模型，即將基本E-R圖通過轉(zhuǎn)換規(guī)則轉(zhuǎn)換為關(guān)系模型，然后根據(jù)優(yōu)化方法得到優(yōu)化的數(shù)據(jù)模型。

5、2.3.2具體實(shí)現(xiàn)將上述E-R圖轉(zhuǎn)換優(yōu)化后得到的關(guān)系模式為: Admin(ID,password);Teacher(tID,password);Student(sno,sname,password);Course(cno,cname);Sc(cno,sno,grade);Paper(cno,sno,titleno,sanswer,getscore);Title(cno,titleno,titlecontent,answear,score);Title1(cno,titleno,titlecontent,A,B,C,D,answear,score);Testpaper(cno,testpaper

6、no,titleno,titlecontent);后期title1刪去，統(tǒng)一使用了title0題庫(kù)表表圖2-9 ER圖轉(zhuǎn)關(guān)系模式 2.4數(shù)據(jù)庫(kù)實(shí)施和維護(hù)針對(duì)數(shù)據(jù)庫(kù)維護(hù)部分，我們小組將及時(shí)對(duì)該系統(tǒng)數(shù)據(jù)庫(kù)進(jìn)行備份，將各用戶信息保存在文件中，保證用戶信息的安全性。3. 運(yùn)行結(jié)果登入（以管理員為例）：管理員管理相關(guān)表信息：登入老師后出題：老師修改題目：老師查卷（生成的視圖）：運(yùn)行結(jié)果：試卷提取：查看相關(guān)成績(jī)信息：學(xué)生答題后生成的數(shù)據(jù)學(xué)生查詢自己的成績(jī)（后兩門暫時(shí)沒有考試）：登陸界面：4. 源代碼 4.1建表/課程表 CREATE TABLE COURSE ( CNO CHAR(4) NOT NULL

7、, 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 KEY ( SNO ) ENABLE );/創(chuàng)建選課表CREATE TABLE SC ( CNO CHAR(4) NOT NULL , SNO CHA

8、R(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

9、NULL , PASSWORD 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 KEY ( CNO , TITLENO ) ENABLE );ALTER TABLE TIT

10、LEADD CONSTRAINT TITLE_FK1 FOREIGN KEY( 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

11、 );ALTER TABLE PAPERADD CONSTRAINT PAPER_FK1 FOREIGN KEY( CNO )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 )REFERENC

12、ES TITLE( CNO , TITLENO )ON DELETE CASCADE ENABLE;CREATE USER teacer1 IDENTIFIED 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','趙日天'

13、;，'123456')；Create or replace procedure manatea(tid in CHAR,password in varchar2)IsBeginInsert into teacherValues(TID, PASSWORD);Commit;End;CALL MANATEA ('teacher1','123456')；Create or replace procedure manacou(cno in CHAR,cname in varchar2)IsBeginInsert into courseValues(CNO

14、, cname);Commit;End;CALL MANACOU ('0001','課程1')；Create or replace procedure manasc(cno in CHAR,sno in char)IsBeginInsert into SCValues(cno, sno,NULL);Commit;End;CALL MANASC ('0001','41410051')；/出題存儲(chǔ)過程Create or replace procedure ks(Cno_input in CHAR,Sno_input in char,T

15、itleno_input in char,Sanswer_input in varchar2)Isanswer1 varchar2(10);score1 char(4);Cno1 char(4);Sno1 char(9);BeginSelect cno,sno INTO cno1,sno1 from scWHERE o=cno_input and sc.sno = sno_input;exception when no_data_found then dbms_output.put_line('沒有選課不能考試！'); select answer,score INTO answ

16、er1,score1 FROM title WHERE o=cno_input AND title.titleno=Titleno_input;if Sanswer_input = answer1 THEN Insert into paper Values(cno_input,sno_input,titleno_input,sanswer_inut,score1);else Insert into paper Values(cno_input,sno_input,titleno_input,sanswer_inut,'0');END IF;Commit;End;Call KS(

17、'0001','41410051','01','B');/修改題目存儲(chǔ)過程/修改題目create or replace procedure XGTM( cno_input in char, titleno_input in char, titlecontent_input in varchar2)isbeginupdate title settitle.titlecontent = titlecontent_inputWHERE o = cno_input AND title.titleno = titleno_input;com

18、mit;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 or replace procedure XGFS( cno_input in char, titleno_input

19、 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);beginv_sql := 'create or replace VIEW PAPER_VIEW(titlen

20、o,titlecontent,answer,sanswer,gerscore)AS SELECT title.titleno,title.titlecontent,title.answer,paper.sanswer,paper.getscoreFROM title,paperWHERE o=o AND title.titleno=paper.titleno AND paper.sno=sno_input AND o=cno_inputorder by titleno'EXECUTE IMMEDIATE v_sql using cno_input,sno_input;end;/成績(jī)統(tǒng)計(jì)

21、過程（學(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 AND SC.SNO = sno_input;commit;end;call TJ

22、 ('0001','41410050');4.3 查詢/查卷（視圖）CREATE or replace VIEW PAPER_VIEW_41410051(titleno,titlecontent,answer,sanswer,gerscore)AS SELECT title.titleno,title.titlecontent,title.answer,paper.sanswer,paper.getscoreFROM title,paperWHERE o=o AND title.titleno=paper.titleno AND paper.sno='4

23、1410051'order by titleno;/查卷（sqlplus）set linesize 200 pagesize 999 newpage;Col answer format a47;SELECT title.titleno,title.titlecontent,'正確答案是',title.answer,'你的答案是',paper.sanswer,'得分',paper.getscoreFROM title,paperWHEREo=o AND title.titleno=paper.titleno ANDpaper.sno=

24、9;41410051'order by titleno;/查看試題SELECT title.titleno,title.titlecontentFROM titleWHERE o='0001'order by titleno;/查看課程考試成績(jī)情況Select student.sno,student.sname,sc.gradefrom student,scwhere o='0001' ANDsc.sno = student.sno;/查看課程平均分Select avg(grade) from scWhere o='0001'4.4 登陸

25、設(shè)計(jì)由于做登陸的時(shí)候分表查詢用戶信息一直出bug，經(jīng)過搜索解決辦法后決定建立一個(gè)教師學(xué)生用戶表來(lái)解決問題create table P_USER ( username CHAR(9), password VARCHAR2(20), ) 從此表中只判斷登陸是否成功。4.4.1 jsp頁(yè)面部分login.jsp<%page contentType="text/html" pageEncoding="UTF-8"%><!DOCTYPE html><html> <head> <meta http-equiv=&

26、quot;Content-Type" content="text/html; charset=UTF-8"> <title>login</title> </head> <body> <form action="http:/localhost:8080/loginServlet/LoginServlet" method="post"> 歡迎使用在線考試系統(tǒng) 用戶名：<input type="text" name="userna

27、me" /><br/> 密碼：<input type="password" name="password" /><br/> <input type="submit" value="登錄" /> </form> </body></html>Welcome.jsp<%page contentType="text/html" pageEncoding="UTF-8"%>&

28、lt;!DOCTYPE html><html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>login</title> </head> <body> <form action="http:/localhost:8080/loginServlet/LoginServlet" method="post"&g

29、t; 歡迎使用在線考試系統(tǒng)歡迎登陸！ </form> </body></html>4.4.2 servlet編寫UserServlet.javapackage ; import ; import ; import ; import ; import ; import ; import ; /* * <p>Title:UserServlet </p> * <p>Description:TODO </p> * <p>Company: </p> * author zxy * date 2016

30、-12-26 下午10:50:57*/ public class UserServlet extends HttpServlet public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException doPost(request,response); public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletExcep

31、tion, IOException String method = request.getParameter("method"); if("login".equals(method)/登錄 String username = request.getParameter("username"); String password = request.getParameter("password"); if(username=null|"".equals(username.trim()|password

32、=null|"".equals(password.trim() ("用戶名或密碼不能為空！"); response.sendRedirect("login.jsp"); return; UserBean userBean = new UserBean(); boolean isValid = userBean.valid(username,password); if(isValid) ("登錄成功！"); request.getSession().setAttribute("username",

33、 username); response.sendRedirect("welcome.jsp"); return; else ("用戶名或密碼錯(cuò)誤！"); response.sendRedirect("login.jsp"); return; else if("logout".equals(method)/退出登錄 ("退出登錄！"); request.getSession().removeAttribute("username"); response.sendRedirec

34、t("login.jsp"); return; 4.4.3 javabeans編寫DBAcess.javapackage ; import ; import ; import ; import ; import ; /*數(shù)據(jù)庫(kù)操作類 * <p>Title:DBAcess </p> * <p>Description:TODO </p> * <p>Company: </p> * author zxy * date 2016-12-25 下午12:40:24*/ public class DBAcess pr

35、ivate String driver = "" private String url = "jdbc:oracle:" + "thin:25:1158:orcl" private String username = "system" private String password = "123456" private Connection conn; private Statement stm; private ResultSet rs; /創(chuàng)建連接 public bool

36、ean 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 block e.printStackTrace(); / 獲取連接 catch (ClassNotFoundException e) / TODO Auto-generated catch block

37、e.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(); /判斷有無(wú)數(shù)據(jù) public boolean next() boolean b = false; try if(rs.next() b = true; catch (SQLException

38、 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 block 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)閉statement public void closeStm() try if (stm !=