數(shù)據(jù)庫設(shè)計(jì)案例詳解.ppt

數(shù)據(jù)庫技術(shù) Database technology 數(shù)據(jù)庫設(shè)計(jì)案例詳解,湖南大學(xué)軟件學(xué)院 Software college of Hunan University 楊金民 2013.09,2,Course Information,上課老師: 楊金民 教授 辦 公 室: 湖南大學(xué)軟件大樓507 電 話:Email： rj_jmyang QQ： 909485030; 網(wǎng)站：,主要教學(xué)內(nèi)容,信息系統(tǒng)案例分析； 典型的業(yè)務(wù)表單； 典型的業(yè)務(wù)需求； 數(shù)據(jù)庫設(shè)計(jì)； 數(shù)據(jù)庫實(shí)現(xiàn)； 數(shù)據(jù)庫設(shè)計(jì)經(jīng)驗(yàn)總結(jié) 應(yīng)用程序設(shè)計(jì)； 應(yīng)用程序開發(fā)；,數(shù)據(jù)庫系統(tǒng)的最本質(zhì)特征,數(shù)據(jù)庫中表的定義和現(xiàn)實(shí)中使用的業(yè)務(wù)表單不能直接一一對(duì)應(yīng)； 根源在于數(shù)據(jù)正確性的取得和數(shù)據(jù)操作簡單性的取得存在矛盾。 數(shù)據(jù)正確性的取得要求嚴(yán)格按照類和關(guān)系的原則來設(shè)計(jì)表； 業(yè)務(wù)表單通常都是由多個(gè)類的數(shù)據(jù)綜合而成的； 類之間存在關(guān)系，即表之間存在聯(lián)系，因此可以沿著表之間的聯(lián)系，通過聯(lián)接運(yùn)算把多個(gè)表中的數(shù)據(jù)綜合起來，形成業(yè)務(wù)表單； 正是這一最本質(zhì)的特征，引出了數(shù)據(jù)庫設(shè)計(jì)這一專業(yè)知識(shí)。,SQL,data 操作DML；添加，刪除，修改，查詢，統(tǒng)計(jì)（5種） Scheme DDL：創(chuàng)建，刪除，修改； 完整性DDL：主鍵，外鍵，域，業(yè)務(wù)規(guī)則；(觸發(fā)器，存儲(chǔ)過程)； 操作簡單性DDL：view, procedure（存儲(chǔ)過程） 安全性DDL: view, user/role， object， privilege 事物DDL：transaction begin；數(shù)據(jù)庫備份， 性能DDL：創(chuàng)建索引index,業(yè)務(wù)表單是綜合信息的呈現(xiàn)(一),課程名稱：高級(jí)數(shù)據(jù)庫技術(shù) 課程編號(hào)：001 學(xué) 期：2010/01,上課老師：楊金民,學(xué)生信息； 課程信息； 老師信息； 學(xué)生選課信息； 教師教課信息；,該表單包含的信息：,課程成績單,該業(yè)務(wù)表單中的數(shù)據(jù)項(xiàng)分別來自哪些表？,業(yè)務(wù)表單是綜合信息的呈現(xiàn)(二),學(xué)院：軟件學(xué)院 學(xué)生姓名：張三 學(xué)號(hào)：001,學(xué)生信息； 課程信息； 學(xué)院信息； 學(xué)生選課信息；,該表單包含的信息：,學(xué)生成績單,業(yè)務(wù)表單和數(shù)據(jù)庫中表的不一致性,學(xué)院名稱：軟件學(xué)院 學(xué)院編號(hào)：01 地址：湖南大學(xué)軟件大樓,院長：林亞平,學(xué)生信息； 學(xué)院信息；,該表單包含的信息：,學(xué)生表單,根據(jù)上述業(yè)務(wù)表單，采取一一對(duì)應(yīng)方法，設(shè)計(jì)出的表就不正確，存在下述5個(gè)問題:,不正確的數(shù)據(jù)庫設(shè)計(jì),student-department,這個(gè)表是根據(jù)上述業(yè)務(wù)表單，采取一一對(duì)應(yīng)方法而設(shè)計(jì)出來的，很多人通常就是這樣干的。,上述不正確的數(shù)據(jù)庫設(shè)計(jì)存在的問題(一),問題： 1) 數(shù)據(jù)冗余問題：例如，軟件學(xué)院信息多個(gè)地方重復(fù)； 解決方法 ：數(shù)據(jù)庫設(shè)計(jì),student-department,不正確的數(shù)據(jù)庫設(shè)計(jì)存在的問題(二),問題： 2) 刪除問題：例如，刪除一個(gè)學(xué)院信息時(shí)，附帶也把 其學(xué)生信息也刪除了； 解決方法 ：數(shù)據(jù)庫設(shè)計(jì),student-department,不正確的數(shù)據(jù)庫設(shè)計(jì)存在的問題(三),問題：3) 插入抵制問題：例如，新建一個(gè)學(xué)院時(shí)，由于還沒有學(xué)生 ，出現(xiàn)插入不成功的問題，原因是studentNo 是主鍵； 解決方法 ：數(shù)據(jù)庫設(shè)計(jì),student-department,不正確的數(shù)據(jù)庫設(shè)計(jì)存在的問題(四),問題：4) 修改問題：例如，修改軟件學(xué)院的地址時(shí)， 要修改多行記錄，而不是一條記錄； 解決方法 ：數(shù)據(jù)庫設(shè)計(jì),student-department,不正確的數(shù)據(jù)庫設(shè)計(jì)存在的問題(五),問題： 5) 統(tǒng)計(jì)問題：例如，統(tǒng)計(jì)有多少個(gè)學(xué)院時(shí)，由于信息 重復(fù)出現(xiàn)使得統(tǒng)計(jì)困難； 解決方法 ：數(shù)據(jù)庫設(shè)計(jì),student-department,正確的數(shù)據(jù)庫設(shè)計(jì),student,department,引用,如果把它設(shè)計(jì)成如下的2張表，則不存在上述5種問題。,數(shù)據(jù)正確性要求嚴(yán)格地按照類別概念來組織數(shù)據(jù)，不能把不同類別的數(shù)據(jù)混合到一張表中存儲(chǔ)；而業(yè)務(wù)表單則通常要求多個(gè)類別的數(shù)據(jù)綜合呈現(xiàn)。,再次強(qiáng)調(diào)數(shù)據(jù)庫的最本質(zhì)特征,數(shù)據(jù)庫中表的定義和現(xiàn)實(shí)中使用的業(yè)務(wù)表單不能直接一一對(duì)應(yīng)； 根源在于數(shù)據(jù)正確性的取得和數(shù)據(jù)操作簡單性的取得存在矛盾。 數(shù)據(jù)正確性的取得要求嚴(yán)格按照類和關(guān)系的原則來設(shè)計(jì)表； 業(yè)務(wù)表單通常都是由多個(gè)類的數(shù)據(jù)綜合而成的； 類之間存在關(guān)系，即表之間存在聯(lián)系，因此可以沿著表之間的聯(lián)系，通過聯(lián)接運(yùn)算把多個(gè)表中的數(shù)據(jù)綜合起來，形成業(yè)務(wù)表單； 正是這一最本質(zhì)的特征，引出了數(shù)據(jù)庫設(shè)計(jì)這一專業(yè)知識(shí)。,數(shù)據(jù)庫設(shè)計(jì)流程圖,用戶業(yè)務(wù) 1,建模 識(shí)別、提煉、抽象、歸納,用戶業(yè)務(wù) n,ER-diagram,Relations,用戶外模式 1,用戶外模式 n,變換,規(guī)范化,選用 DBMS實(shí)現(xiàn),Requirements,調(diào)查、分析、研究,合理的Relations,視圖,存儲(chǔ)過程,數(shù)據(jù)庫設(shè)計(jì)的內(nèi)容和面臨的挑戰(zhàn),數(shù)據(jù)庫設(shè)計(jì)要回答的三個(gè)問題： 1) 覆蓋性問題：確定一個(gè)單位有哪些數(shù)據(jù)項(xiàng)（字段）？ 2） 劃分問題： 確定哪些字段構(gòu)成一個(gè)表？ 3） 關(guān)系問題： 確定表之間有什么關(guān)系？ 挑戰(zhàn)： 1）業(yè)務(wù)表單和數(shù)據(jù)庫中表的不一致； 業(yè)務(wù)表單中通常包含的是綜合信息； 而數(shù)據(jù)庫中的一個(gè)表只能存儲(chǔ)單一類的信息； 2）見到的和聽到的是局部，而數(shù)據(jù)庫設(shè)計(jì)要站在全局來考慮；,如果直接把業(yè)務(wù)表單中的數(shù)據(jù)項(xiàng)組成一個(gè)表，會(huì)帶來一系列的數(shù)據(jù)正確性問題；,案例大學(xué)教學(xué)管理系統(tǒng),湖南大學(xué)本科教學(xué)的業(yè)務(wù)情況 情況如下：學(xué)校有多個(gè)學(xué)院，學(xué)院信息有名稱，電話，地址，院長。每個(gè)學(xué)院開設(shè)一些課程，課程信息有課程名稱，課程編號(hào)，課時(shí)，教材。每個(gè)學(xué)院也有一些老師，老師信息有工號(hào)，姓名，職稱，電話，email。,案例大學(xué)教學(xué)管理系統(tǒng),湖南大學(xué)本科教學(xué)的業(yè)務(wù)情況 每個(gè)學(xué)期的教學(xué)過程是：老師首先提出開課申請(qǐng)，一個(gè)老師可以開一門或者多門課。一門課程也允許有多個(gè)老師同時(shí)開； 然后學(xué)生對(duì)老師開出的課進(jìn)行選課, 學(xué)生信息有學(xué)號(hào)，姓名，性別，班級(jí)，所在學(xué)院； 一個(gè)學(xué)生一個(gè)學(xué)期可以自由決定是否選課，也可選多門課. 修完一門課后得到一個(gè)考試分?jǐn)?shù)，如果考試不及格，可以以后重選、重修。,典型的教學(xué)管理業(yè)務(wù)表單(1),女,女,男,男,性別,1988/01/29,2008043315,劉麗,1988/07/09,2008043332,張珊,1992/02/21,2008043214,汪兵,1990/12/14,2008043101,周山,出生日期,學(xué)號(hào),姓名,學(xué)院名稱：軟件學(xué)院 學(xué)院編號(hào)：01 地址：湖南大學(xué)軟件大樓,院長：林亞平,學(xué)生名冊(cè),典型的教學(xué)管理業(yè)務(wù)表單(2),課程名稱：數(shù)據(jù)庫系統(tǒng)與設(shè)計(jì) 課程編號(hào)：001 學(xué) 期：2010/01,上課老師：張三,課程成績單,典型的教學(xué)管理業(yè)務(wù)表單(3),學(xué)院名稱：軟件學(xué)院 學(xué) 期：2010/01,上課老師名單匯總表,典型的教學(xué)管理業(yè)務(wù)表單(4),學(xué)院：軟件學(xué)院 學(xué)生姓名：李湘草 學(xué)號(hào)：200843001,學(xué)生本科階段學(xué)習(xí)成績單,典型的教學(xué)管理業(yè)務(wù)表單(5),學(xué)院：軟件學(xué)院 年級(jí)：2008,學(xué)生本科學(xué)習(xí)總成績排名表,典型的教學(xué)管理業(yè)務(wù)表單(6),教師年度教學(xué)工作量匯總表,單位：軟件學(xué)院 年度：2010,典型的教學(xué)管理業(yè)務(wù)表單(7),學(xué)院年度教學(xué)工作量匯總表,年度：2010,典型的教學(xué)管理業(yè)務(wù)需求(1),在2010/01學(xué)期選了楊金民老師開設(shè)的“數(shù)據(jù)庫系統(tǒng)”課程的學(xué)生姓名、學(xué)號(hào)、班級(jí)清單； 2010/01學(xué)期“數(shù)據(jù)庫系統(tǒng)”課程的班級(jí)平均分清單； 2010/01學(xué)期“數(shù)據(jù)庫系統(tǒng)”課程，在軟件學(xué)院的2008級(jí)學(xué)生中沒有選修該課程的同學(xué)清單； 2006級(jí)軟件學(xué)院畢業(yè)班，輸出其學(xué)生成績排名；輸出前25名，作為研究生保送生； 輸出2010/01學(xué)期每門課程的最高分、最低分； 輸出2010/01學(xué)期“數(shù)據(jù)庫系統(tǒng)”課程，沒有及格的同學(xué)的清單；,典型的教學(xué)管理業(yè)務(wù)需求(2),統(tǒng)計(jì)軟件學(xué)院2009年度教師的教學(xué)工作量清單； 軟件學(xué)院2009年度教學(xué)工作量未達(dá)到要求（150學(xué)時(shí)）的教師清單； 輸出軟件學(xué)院2006級(jí)畢業(yè)班“張山”同學(xué)的成績清單(課程名稱，學(xué)分，成績； 輸出軟件學(xué)院2010/01學(xué)期每門課的選修人數(shù)清單； 統(tǒng)計(jì)湖南大學(xué)各個(gè)學(xué)院2009年度的教學(xué)工作量清單； 對(duì)軟件學(xué)院每個(gè)學(xué)生，給其家長輸出2010/01學(xué)期成績單； 定義視圖、存儲(chǔ)過程，完成上述業(yè)務(wù)表單數(shù)據(jù)的輸出；,使用PowerDesigner創(chuàng)建E-R模型圖,從實(shí)體關(guān)系圖得到的表,Student(sno, sname, sex, native, birthday, classno, addr, phone,dno),Course(cno, cname, hours, term, credit, dno),Teacher (tno, tname, rank,dno),Department (dno, dname, header, addr, phone),Enroll (tno, cno, sno, semester, score),Teach (tno, cno, semester),：選課表,：開課表,實(shí)體表,關(guān)系表,檢查數(shù)據(jù)庫設(shè)計(jì)是否能滿足業(yè)務(wù)要求,檢查業(yè)務(wù)表單能否完整生成； 檢查常用的業(yè)務(wù)需求能否滿足，主要指查詢和統(tǒng)計(jì)；,在數(shù)據(jù)庫中創(chuàng)建表,CREATE TABLE Teacher ( tno CHAR(7) NOT NULL, tname VARCHAR(8) NOT NULL, rank VARCHAR(6) CHECK (rank IN (NULL,教授,副教授,講師), dno VARCHAR (8), PRIMARY KEY (tno), FOREIGN KEY (dno) REFERENCES Department(dno) );,在數(shù)據(jù)庫中創(chuàng)建表,CREATE TABLE Student ( sno CHAR(10) NOT NULL, sname CHAR(10) NOT NULL, sex VARCHAR(6) CHECK (rank IN (男,女), native VARCHAR(8), birthday DATE, classno CHAR(2), addr VARCHAR(48), phone VARCHAR(12), dno VARCHAR(8), PRIMARY KEY (sno), FOREIGN KEY (dno) REFERENCES Department(dno) );,在數(shù)據(jù)庫中創(chuàng)建表,CREATE TABLE Enroll ( tno CHAR(7) NOT NULL, cno CHAR(8) NOT NULL, sno CHAR(10) NOT NULL, semester VARCHAR(7), score NUMBER(5, 1), PRIMARY KEY (tno, cno, sno, semester), FOREIGN KEY (tno) REFERENCES Teacher(tno), FOREIGN KEY (cno) REFERENCES Course(cno), FOREIGN KEY (sno) REFERENCES student(sno) );,插入、修改、刪除表中的記錄,插入記錄： INSERT INTO student VALUES (馬麗,200904301,女, DATE1990-03-05, 590000); 修改記錄： UPDATE student SET birthdate = DATE1991-03-15 WHERE studentNo = 2008043332; 刪除記錄： DELETE FROM student WHERE studentNo LIKE 2007%;,錄入基礎(chǔ)數(shù)據(jù),department,錄入基礎(chǔ)數(shù)據(jù),student,錄入基礎(chǔ)數(shù)據(jù),course,錄入基礎(chǔ)數(shù)據(jù),teacher,錄入基礎(chǔ)數(shù)據(jù),teacher_course,錄入基礎(chǔ)數(shù)據(jù),Enroll,關(guān)系型數(shù)據(jù)庫的表特征,Student,enroll,Course,不同表中記錄之間的關(guān)系,1:1關(guān)系：一個(gè)院系對(duì)應(yīng)一個(gè)院長，一個(gè)院長對(duì)應(yīng)一個(gè)學(xué)院；,department,Dean,不同表中記錄之間的關(guān)系,1:m關(guān)系：一個(gè)院系有多個(gè)教師；,department,teacher,不同表中記錄之間的關(guān)系,n:m關(guān)系：一門課程可以有多個(gè)學(xué)生來選擇， 一個(gè)學(xué)生可以選擇多門課程；,student,take_course,course,關(guān)系型數(shù)據(jù)庫,DBMS 正確性(Correctness) 性能(Performance),Database,SQL,1,2,3,4,表的集合,SQL執(zhí)行是否成功； 返回的結(jié)果(table)；,使用二維表結(jié)構(gòu)來組織和存儲(chǔ)數(shù)據(jù)。 現(xiàn)有數(shù)據(jù)庫系統(tǒng)都是關(guān)系型數(shù)據(jù)庫；,簡單,業(yè)務(wù)表單是綜合信息的呈現(xiàn)(一),課程名稱：高級(jí)數(shù)據(jù)庫技術(shù) 課程編號(hào)：001 學(xué) 期：2010/01,上課老師：張三,學(xué)生信息； 課程信息； 老師信息； 學(xué)生選課信息； 教師教課信息；,該表單包含的信息：,課程成績單,業(yè)務(wù)表單是綜合信息的呈現(xiàn)(二),學(xué)院：軟件學(xué)院 學(xué)生姓名：張三 學(xué)號(hào)：001,學(xué)生信息； 課程信息； 學(xué)院信息； 學(xué)生選課信息；,該表單包含的信息：,學(xué)生成績單,業(yè)務(wù)需求的滿足：查找和統(tǒng)計(jì)(1),在2010/01學(xué)期選了楊金民老師開設(shè)的“數(shù)據(jù)庫系統(tǒng)”課程的學(xué)生姓名、學(xué)號(hào)、班級(jí)清單：滿足上課點(diǎn)名需求； SELECT sno, sname, classno FROM student AS s, teacher AS t, course AS c, enroll AS e WHERE t.tname=楊金民 AND ame=數(shù)據(jù)庫系統(tǒng) AND e.semester=2010/01 AND s.sno=e.sno AND t.tno=e.tno AND o=o ORDER BY classno, sno;,典型業(yè)務(wù)處理方法(2),2010/01學(xué)期“數(shù)據(jù)庫系統(tǒng)”課程的班級(jí)平均分清單； SELECT classno, AVG(score) FROM student AS s, course AS c, enroll AS e WHERE ame=數(shù)據(jù)庫系統(tǒng) AND e.semester=2010/01 AND s.sno=e.sno AND o=o GROUP BY classno;,典型業(yè)務(wù)處理方法(3),2010/01學(xué)期“數(shù)據(jù)庫系統(tǒng)”課程，在軟件學(xué)院的2008級(jí)學(xué)生中沒有選修該課程的同學(xué)清單； SELECT s.* FROM student AS s, department AS d WHERE d.dname= 軟件學(xué)院 AND s.sno LIKE 2008% AND s.dno=d.dno EXCEPT SELECT s.* FROM student AS s, course AS c, Enroll AS e WHERE ame=數(shù)據(jù)庫系統(tǒng) AND e.semester=2010/01 AND s.sno=sct.sno AND o=o;,典型業(yè)務(wù)處理方法(4),輸出2010/01學(xué)期每門課程的最高分、最低分； SELECT cno, cname, MAX(score), MIN(score) FROM course AS c, enroll AS e WHERE e.semester=2010/01 AND o=o GROUP BY cno, cname;,典型業(yè)務(wù)處理方法(5),輸出2010/01學(xué)期“數(shù)據(jù)庫系統(tǒng)”課程，沒有及格的同學(xué)的清單； SELECT sno, sname, classno, score FROM student AS s, course AS c, enroll AS e WHERE ame=數(shù)據(jù)庫系統(tǒng) AND e.semester=2010/01 AND e.score60 AND s.sno=e.sno AND o=o;,典型業(yè)務(wù)處理方法(6),統(tǒng)計(jì)軟件學(xué)院2009年度教師的教學(xué)工作量清單； SELECT tno, tname, SUM(c.hours) FROM teacher AS t, department AS d, course AS c, teacher _course AS tc WHERE d.dname=軟件學(xué)院 AND tc.semester LIKE 2009% AND t.dno=d.sno AND t.tno=tc.tno AND o=o GROUP BY tno, tname;,統(tǒng)計(jì)表中的數(shù)據(jù)(7),軟件學(xué)院2009年度教學(xué)工作量未達(dá)到要求（150學(xué)時(shí)）的教師清單； SELECT t.tno, t.tname, SUM(c.hours) AS workamount FROM teacher AS t, department AS d, course AS c, teacher _course AS tc WHERE d.dname=軟件學(xué)院 AND tc.semester LIKE 2009% AND t.dno=d.dno AND t.tno=tc.tno AND o=o GROUP BY tno, tname HAVING workamount 150; ORDER BY workmount DESC;,典型業(yè)務(wù)處理方法(8),輸出軟件學(xué)院2006級(jí)畢業(yè)班“張山”同學(xué)的成績清單(課程名稱，學(xué)分，成績； SELECT ame, c.credit, e.score FROM student AS s, department AS d, course AS c, enroll AS e WHERE d.dname= 軟件學(xué)院 AND s.sno LIKE 2006% AND s.sname=張山 AND s.dno=d.dno AND s.sno=e.sno AND o=o;,典型業(yè)務(wù)處理方法(9),輸出軟件學(xué)院2010/01學(xué)期每門課的選修人數(shù)清單； SELECT o, ame, COUNT(*) FROM department AS d, course AS c, enroll AS e WHERE d.dname=軟件學(xué)院 AND e.semester=2010/01 AND d.dno=c.dno AND o=o GROUP BY o, ame;,典型業(yè)務(wù)處理方法(10),統(tǒng)計(jì)湖南大學(xué)各個(gè)學(xué)院2009年度的教學(xué)工作量清單； SELECT d.dno, d.dname, sum(c.hours) AS workamount FROM teacher AS t, department AS d, course AS c, teacher _course AS tc WHERE tc.semester LIKE 2009% AND t.dno=d.sno AND t.tno=tc.tno AND o=o GROUP BY d.dno, d.dname ORDER BY workamount DESC;,典型業(yè)務(wù)處理方法(11),對(duì)軟件學(xué)院每個(gè)學(xué)生，給其家長輸出2010/01學(xué)期成績單； SELECT s.sno, s.sname, ame, e.score FROM student AS s, department AS d, course AS c, enroll AS e WHERE e.semester=2010/01 AND d.dname=軟件學(xué)院 AND s.dno=d.dno AND s.sno=e.sno AND o=o ORDER BY s.sno, s.sname ASC;,典型業(yè)務(wù)處理方法(12),2006級(jí)軟件學(xué)院畢業(yè)班，輸出其學(xué)生成績排名；輸出前25名，作為研究生保送生； SELECT * FROM (SELECT sno, sname, AVG(score) AS avg_score FROM student AS s, department AS d, enroll AS sct WHERE s.sno LIKE 2006% AND d.dname=軟件學(xué) 院 AND s.dno=d.dno AND s.sno=sct.sno GROUP BY s.sno, s.sname ORDER BY avg_score) WHERE ROWNUM =25 ;,數(shù)據(jù)庫設(shè)計(jì)正確帶來的神奇功效,從上述案例可以看到，盡管這個(gè)教學(xué)管理數(shù)據(jù)庫中僅僅只有6張表 ，但是形形色色的業(yè)務(wù)表單都能完整生成，五花八門的業(yè)務(wù)需求都能夠得到滿足。,數(shù)據(jù)庫設(shè)計(jì)惡習(xí)的表現(xiàn)形式,實(shí)際中，在設(shè)計(jì)數(shù)據(jù)庫中的表時(shí)，最常見的錯(cuò)誤是：把業(yè)務(wù)表單的內(nèi)容直接對(duì)應(yīng)成一張表，例如前面的“院系的學(xué)生名單”，直接對(duì)應(yīng)出的“ student-department”表。 每當(dāng)碰到一個(gè)新的業(yè)務(wù)需求或者業(yè)務(wù)表單，發(fā)現(xiàn)已有的表不能滿足，于是只好在數(shù)據(jù)庫中再添加一張表。常此下去，數(shù)據(jù)庫中的表不斷增加，于是數(shù)據(jù)不一致問題、數(shù)據(jù)冗余問題就會(huì)非常突出。用戶經(jīng)常發(fā)現(xiàn)數(shù)據(jù)莫名其妙地不正確，厭惡、反感、失望的情緒就上來了，最終導(dǎo)致系統(tǒng)失敗。,SQL經(jīng)驗(yàn),根據(jù)業(yè)務(wù)要求，檢查條件對(duì)應(yīng)哪些表中的哪些字段； 根據(jù)業(yè)務(wù)要求，檢查輸出內(nèi)容分別為哪些表中的哪些字段； 根據(jù)業(yè)務(wù)含義，檢查上述所涉及的表是否關(guān)聯(lián)在一起，如果沒有關(guān)聯(lián)在一起，則進(jìn)一步添加關(guān)系表，把所涉及的表聯(lián)接起來； 如果業(yè)務(wù)涉及統(tǒng)計(jì)，則要考慮分組的問題； 考慮分組后的統(tǒng)計(jì)結(jié)果是否還要過濾； 考慮結(jié)果是否要排序；,實(shí)現(xiàn)每學(xué)期選課不超過25學(xué)分,CREATE TRIGGER trigger_insertEnroll INSTEAD OF INSERT ON enroll REFERENCING NEW ROW AS newrow FOR EACH ROW credit integer SELECT SUM(credit) INTO credit FROM course WHERE courseId IN (SELECT courseId FROM enroll WHERE studentId = newrow.studentId AND semester =newrow.semester) OR courseId = newrow.courseId; WHEN ( credit = 25 ) INSERT INTO enroll(studentId, semester, courseId, teracherId) VALUES( newrow.studentId, newrow.semester, newrow.courseId, newrow.teracherId);,SQL經(jīng)驗(yàn),SQL操作單位是記錄：一張表中的一行記錄；,操作簡單性的層次實(shí)現(xiàn)策略,應(yīng)用程序 存儲(chǔ)過程 視圖 表 DBMS,數(shù)據(jù)庫專業(yè)知識(shí) (關(guān)系、聯(lián)接，復(fù)雜的查詢表達(dá)）,簡單了解數(shù)據(jù)庫常識(shí) （表的5種操作）,編程人員，（函數(shù)調(diào)用，表的遍歷）,普通用戶 （點(diǎn)擊鼠標(biāo)，敲鍵盤）,數(shù)學(xué)、軟件天才（特征發(fā)掘，思想的實(shí)現(xiàn)）,少,多,倒立金字塔,外模式,給出軟件學(xué)院的學(xué)生列表，滿足該學(xué)院學(xué)生輔導(dǎo)員的需求； 給出課程名稱和教材，以滿足教材科訂購教材的業(yè)務(wù)需求； 2010年第1學(xué)期選了數(shù)據(jù)庫課程的學(xué)生學(xué)號(hào)，姓名列表，以滿足該課程上課老師的考勤和點(diǎn)名需求；,對(duì)每個(gè)客戶，呈給他的數(shù)據(jù)應(yīng)該是他期望的數(shù)據(jù)，不能多，也不能少。多了，他認(rèn)為與他無關(guān)，感到莫名其妙。少了，不能滿足他的業(yè)務(wù)需求；,外模式,1) 給出軟件學(xué)院的學(xué)生列表; CREATE VIEW SoftwareStudent AS SELECT student.* FROM student, department WHERE student.deptNo = department.deptNo AND deptname = Software college;,Student,外模式,2)給出課程名稱和教材; CREATE VIEW course_textbook AS SELECT name, textbook FROM course;,Course,外模式,3) 給出在2010年第1學(xué)期選了楊金民老師負(fù)責(zé)上的數(shù)據(jù)庫課程的學(xué)生學(xué)號(hào)，姓名，班級(jí)列表; CREATE VIEW list_student AS SELECT studentNo, name, class FROM student AS S, enroll AS E, course AS C, teacher AS T WHERE S.studentNo = E.studentNo AND C.courseNo = E.courseNo AND T.teacherNo =E.teacherNo AND.name = 數(shù)據(jù)庫 AND .semester = 2010/01 AND T.name=楊金民;,Student,存儲(chǔ)過程,CREATE PROCEDURE Specified_students(semesterV IN VARCHAR, courserV IN VARCHAR, teacherV IN VARCHAR）AS BEGIN SELECT studentNo, name, class FROM student AS S, enroll AS E, course AS C, teacher AS T WHERE S.studentNo = E.studentNo AND C.courseNo = E.courseNo AND T.teacherNo =E.teacherNo AND.name = courseV AND .semester =semesterV AND T.name=teacherV; END;,CALL Specified_students( 2013/01, 數(shù)據(jù)庫設(shè)計(jì), 楊金民);,通過引入變量，使得視圖通用，滿足所有老師獲得其上課的學(xué)生名單的需求，為上課點(diǎn)名提供清單；,外模式的好處,數(shù)據(jù)獨(dú)立 表結(jié)構(gòu)改變不影響用戶視圖，只需要修改映射的定義； 安全 對(duì)不同的用戶，見到不同的數(shù)據(jù)視圖； 簡化數(shù)據(jù)訪問 避免寫復(fù)雜的SQL語句； 自定義給用戶的數(shù)據(jù)不多不少，恰到好處；,應(yīng)用程序的設(shè)計(jì),基本思想：分類，分層，依賴關(guān)系和業(yè)務(wù)流程。 分類： 1）用戶分類： 學(xué)生、教師、教務(wù)管理人員；,數(shù)據(jù)庫系統(tǒng),信息管理系統(tǒng)：數(shù)據(jù)庫、DBMS、數(shù)據(jù)庫應(yīng)用程序。數(shù)據(jù)庫服務(wù)器，應(yīng)用服務(wù)器（中間件）。,用戶,數(shù)據(jù)庫應(yīng)用程序,網(wǎng)絡(luò),數(shù)據(jù)庫服務(wù)器,操作系統(tǒng),DBMS,數(shù)據(jù)庫,數(shù)據(jù)庫系統(tǒng)架構(gòu),SELECT * FROM student,Host, Port, Server, Database,driver connection statement resultSet,邦聯(lián)式的信息系統(tǒng),學(xué)生數(shù)據(jù)錄入界面,類別 年級(jí) 姓名 性別 出生日期,本科,2010,男,學(xué)院 班級(jí) 學(xué)號(hào) 民族 聯(lián)系電話,軟件學(xué)院,1,漢,家庭地址,學(xué)生信息錄入,保存,數(shù)據(jù)庫訪問模式：5步曲,/1) 加載數(shù)據(jù)庫訪問驅(qū)動(dòng)程序： Class.forName(“com.mysql.jdbc.Driver“); /2) 建立與數(shù)據(jù)庫的鏈接： Connection connection = DriverManager.getConnection( “jdbc:mysql:/00:3306/education“,“root“,“admin“); /3) 向數(shù)據(jù)庫發(fā)送數(shù)據(jù)操作指令，響應(yīng)結(jié)果放在resultSet中: Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery( “SELECT name, phone FROM student WHERE sex =男“ ); System.out.println(“姓名 電話號(hào)碼”); /4)掃描/遍歷結(jié)果集： while (rs.next() ) System.out.println(rs.getString(1) + “ ” + rs.getString(2); /5) 關(guān)閉與數(shù)據(jù)庫的聯(lián)接: connection.close();,分層,院系,班級(jí),老師,課程,年級(jí),學(xué)生,開課,年度學(xué)期,選課,依賴關(guān)系和業(yè)務(wù)流程,系統(tǒng)功能,教務(wù)管理人員： 添加：院系、課程、老師、學(xué)生； 修改：院系、課程、老師、學(xué)生； 刪除：院系、課程、老師、學(xué)生； 查詢：院系、課程、老師、學(xué)生， 開課， 選課， 成績； 報(bào)表：上述典型的教學(xué)管理業(yè)務(wù)表單樣例，以及典型的教學(xué)管理業(yè)務(wù)樣例；,系統(tǒng)功能,老師： 添加：開課； 修改：成績； 刪除：無； 查詢：院系、課程、老師、學(xué)生， 開課， 選課， 成績； 報(bào)表：上述與老師相關(guān)的教學(xué)管理業(yè)務(wù)表單樣例，以及教學(xué)管理業(yè)務(wù)樣例；,系統(tǒng)功能,學(xué)生： 添加：選課； 修改：無； 刪除：無； 查詢：院系、課程、老師、學(xué)生， 開課， 選課， 成績； 報(bào)表：成績單；,界面樣例,基本要素： 菜單：樹形結(jié)構(gòu)； 路徑：當(dāng)前位置； 返回； 下潛；,界面布局面向教務(wù)管理,界面友好性和信息精準(zhǔn)性,用戶一旦登錄，就可記錄其類別信息，所在院系信息，標(biāo)識(shí)號(hào)信息； 對(duì)于學(xué)生，可推理的信息有：當(dāng)前所處的學(xué)期信息，選課時(shí)，可以僅只把他/她所在的學(xué)期的課程的開課信息列出來，以及沒有通過的課程信息列出來，實(shí)現(xiàn)所供信息的精準(zhǔn)性；,登錄界面：,確定,數(shù)據(jù)庫系統(tǒng)架構(gòu),MySQL,Apache Web server,PHP,Index.htm (1), 登錄頁面 ,Index.htm (2), 用戶類型: -學(xué)生- -教師- 用 戶 名: ,Index.htm (3), 密碼: ,Switch.php (1),?php include DB.php; $user_role=$_POST “role“; $user_name = $_POST “user_name“; $user_passwd = $_POST “user_passwd“; If ($user_role=1) /教師 $user_sql = “select * from teacher where tname=$user_name and tno=$user_passwd“; Else /學(xué)生 $user_sql = “select * from student where sname=$user_name and sno=$user_passwd“; $user_result = mysql_query ( $user_sql ); $rows = mysql_num_rows( $user_result ); mysql_free_result($user_result); mysql_close($link);,Switch.php (2),if ($rows = 0) echo “用戶名或密碼錯(cuò)誤“; else echo “; switch ($user_role) case 0 : echo “l(fā)ocation.href=student.php?name=$user_name;“; break; default: echo “l(fā)ocation.href=teacher.php?name=$user_name;“; echo “ “; ?,DB.php,configture.php,Teacher.php (1),$name 老師開課信息“; echo “; echo “課程編號(hào)課程名稱授課學(xué)時(shí)開課學(xué)期課程學(xué)分課程歸屬院系“; echo “;,Teacher.php (2),if($num0) $flag=true; while ($result_row=mysql_fetch_array($result) $result_cno = $result_row“cno“; $result_cnnme = $result_row“cname“; $result_hours = $result_row“hours“; $result_semester = $result_row“semester“; $result_credit = $result_row“credit“; $result_dname = $result_row“dname“; $flag=$flag?false:true; $color=$flag?“#9999FF“:“#99CCFF“; echo “; echo “$result_cno$result_cnnme$result_hours$result_semester$result_credit$result_dname“; echo “; ,Teacher.php (3),else echo “; echo “無開課信息“; echo “; echo “; mysql_close ( $link ); ?,student.php (1),$name 個(gè)人信息“; echo “; echo “學(xué)號(hào)性別出生年月所在院系班級(jí)“; echo “;,student.php (2),$result_row=mysql_fetch_array($result); $result_sno=$result_row“sno“; $result_sex=$result_row“sex“; $result_birthday