MySQL云數(shù)據(jù)庫(kù)應(yīng)用與實(shí)踐 課件 ch13-數(shù)據(jù)庫(kù)應(yīng)用開(kāi)發(fā)綜合實(shí)戰(zhàn)案例

第十三章

數(shù)據(jù)庫(kù)應(yīng)用開(kāi)發(fā)綜合實(shí)戰(zhàn)案例DatabaseSystem西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)系統(tǒng)李寧目錄1.選題背景2.需求分析3.數(shù)據(jù)庫(kù)設(shè)計(jì)4.數(shù)據(jù)庫(kù)實(shí)施5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)7.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)運(yùn)行1.選題背景源自真實(shí)場(chǎng)景：數(shù)據(jù)庫(kù)實(shí)驗(yàn)教學(xué)的痛點(diǎn)學(xué)生提交大量SQL作業(yè)增大教師批閱工作量SQL語(yǔ)句寫法靈活多樣導(dǎo)致批閱難度較大作業(yè)批閱結(jié)果可能也無(wú)法及時(shí)反饋給學(xué)生開(kāi)發(fā)一個(gè)高效的SQL-OJ（OnlineJudge）系統(tǒng)以提高數(shù)據(jù)庫(kù)課程的教學(xué)效率和效果。目錄1.選題背景2.需求分析3.數(shù)據(jù)庫(kù)設(shè)計(jì)4.數(shù)據(jù)庫(kù)實(shí)施5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)7.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)運(yùn)行2.需求調(diào)研——方法

調(diào)查組織機(jī)構(gòu)/用戶調(diào)查各個(gè)部門業(yè)務(wù)活動(dòng)明確新系統(tǒng)的各種要求確定新系統(tǒng)的邊界調(diào)查的方法跟班作業(yè)開(kāi)調(diào)查會(huì)專人介紹調(diào)查表查閱記錄其他信息需求+處理需求+非功能需求2.需求調(diào)研案例——MYSQL-OJ數(shù)據(jù)需求用戶信息題庫(kù)與題目信息試卷信息練習(xí)或考試信息答題信息功能需求教師：班級(jí)管理、班內(nèi)成員管理、題庫(kù)/題目/試卷/考試管理、統(tǒng)計(jì)信息學(xué)生：注冊(cè)、個(gè)人信息管理、考試答題功能、儀表盤管理員：學(xué)校管理、教師用戶管理非功能需求安全性需求:權(quán)限控制性能需求：支持1000人同時(shí)在線答題，題目3秒內(nèi)提交到系統(tǒng)分權(quán)限、管數(shù)據(jù)、定功能需求調(diào)研結(jié)果的分析和表達(dá)方法數(shù)據(jù)流圖數(shù)據(jù)字典數(shù)據(jù)項(xiàng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)流數(shù)據(jù)存儲(chǔ)處理過(guò)程2.需求調(diào)研——結(jié)果表達(dá)信息需求處理需求完整性需求安全性需求性能需求其他2.需求調(diào)研案例——MYSQL-OJ數(shù)據(jù)流圖2.需求調(diào)研案例——MYSQL-OJ數(shù)據(jù)字典：以該系統(tǒng)服務(wù)100所學(xué)校，10年為例數(shù)據(jù)處理數(shù)據(jù)流數(shù)據(jù)存儲(chǔ)目錄1.選題背景2.需求分析3.數(shù)據(jù)庫(kù)設(shè)計(jì)4.數(shù)據(jù)庫(kù)實(shí)施5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)7.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)運(yùn)行3.數(shù)據(jù)庫(kù)設(shè)計(jì)概念結(jié)構(gòu)設(shè)計(jì)：ER圖設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì):ER圖轉(zhuǎn)換成關(guān)系模式安全性與完整性設(shè)計(jì)：加密、防注入、約束、觸發(fā)器等物理結(jié)構(gòu)設(shè)計(jì)：存儲(chǔ)與存取，重點(diǎn)是索引物理數(shù)據(jù)庫(kù)3.1概念結(jié)構(gòu)設(shè)計(jì)數(shù)據(jù)抽象，局部視圖設(shè)計(jì)視圖集成DFDDD總E-R圖邏輯結(jié)構(gòu)設(shè)計(jì)返回用戶，征求意見(jiàn)到滿意為止需求分析分E-R圖

1)抽象數(shù)據(jù)+設(shè)計(jì)局部視圖

2)集成局部視圖，得到全體的概念結(jié)構(gòu)3.1概念設(shè)計(jì)案例—MYSQL-OJ

語(yǔ)義描述與數(shù)據(jù)抽象用戶相關(guān)實(shí)體：學(xué)校、班級(jí)、用戶（管理員、教師、學(xué)生）考試相關(guān)實(shí)體：題庫(kù)、題目、試卷、考試或者練習(xí)活動(dòng)根據(jù)現(xiàn)實(shí)世界與應(yīng)用需求分析，實(shí)體之間語(yǔ)義描述如下：一個(gè)學(xué)校擁有多個(gè)班級(jí)，一個(gè)班級(jí)只能屬于某個(gè)學(xué)校；一個(gè)班級(jí)有多名學(xué)生，一個(gè)學(xué)生只能屬于一個(gè)班級(jí)。系統(tǒng)可以有多個(gè)管理員，任意一個(gè)管理員可以增刪改所有學(xué)校、教師的基本信息；每位教師管理所負(fù)責(zé)班級(jí)的學(xué)生信息。一位教師可以創(chuàng)建多個(gè)題庫(kù)（題庫(kù)指一個(gè)事先準(zhǔn)備好的數(shù)據(jù)庫(kù)，可包含多張表以及部分?jǐn)?shù)據(jù)），每個(gè)題庫(kù)只能被一位老師創(chuàng)建?；谝粋€(gè)題庫(kù)可以出多道題目，每個(gè)題目只能屬于某一個(gè)題庫(kù)。每位教師可以出若干道題目。一套試卷可以由任意多道題目組成，相同題目可出現(xiàn)在多套試卷中，且相同題目在不同試卷中分值可以不同。每位教師可給所負(fù)責(zé)的一個(gè)或多個(gè)班級(jí)，將任意一套可訪問(wèn)的試卷發(fā)布為一次考練活動(dòng)。學(xué)生可以作答自己可見(jiàn)的未過(guò)期考練活動(dòng)。3.1概念設(shè)計(jì)案例—MYSQL-OJ

實(shí)體屬性描述3.1概念設(shè)計(jì)案例—MYSQL-OJ系統(tǒng)總ER圖（12個(gè)實(shí)體+14個(gè)聯(lián)系）分ER圖3.2邏輯結(jié)構(gòu)設(shè)計(jì)—ER圖轉(zhuǎn)換

邏輯結(jié)構(gòu)設(shè)計(jì)：ER圖轉(zhuǎn)換為邏輯結(jié)構(gòu)基本轉(zhuǎn)換實(shí)體與屬性轉(zhuǎn)換1對(duì)1聯(lián)系：合并到任意1端[強(qiáng)制參與]或單獨(dú)轉(zhuǎn)成新關(guān)系1對(duì)多轉(zhuǎn)換：合并到N端或單獨(dú)成新關(guān)系多對(duì)多轉(zhuǎn)換：聯(lián)系轉(zhuǎn)換成單獨(dú)成新關(guān)系特殊轉(zhuǎn)換：復(fù)合屬性、弱實(shí)體、ISA等設(shè)計(jì)優(yōu)化范式理論判斷與分解基于應(yīng)用特征分析的分解與合并外模式設(shè)計(jì)3.2邏輯設(shè)計(jì)案例ER圖中的實(shí)體轉(zhuǎn)換ER圖中的聯(lián)系轉(zhuǎn)換3.2邏輯模式優(yōu)化案例1.模式優(yōu)化：以Exam、Exam_class為例分析。其他關(guān)系均滿足BCNF。1)Exam(考試ID,考試名,考試描述,開(kāi)始時(shí)間,結(jié)束時(shí)間,活躍狀態(tài),是否公布答案,教師ID,試卷ID)：該關(guān)系的碼是考試ID，不存在非主屬性和主屬性（考試ID）對(duì)碼的部分函數(shù)依賴和傳遞函數(shù)依賴，因此滿足BCNF。2）Exam_class(考試ID(FK),班級(jí)ID(FK),發(fā)布時(shí)間)：假設(shè)允許把某次考試給某個(gè)班級(jí)發(fā)布多次，則碼中需要加入發(fā)布時(shí)間。由此該關(guān)系為全碼，無(wú)非主屬性，滿足BCNF。2.其他優(yōu)化（反范式化設(shè)計(jì)、分解或合并等）

題目名：該系統(tǒng)中教師身份登錄時(shí)，以班級(jí)為單位進(jìn)行作答統(tǒng)計(jì)信息，在訪問(wèn)題

目的作答記錄表時(shí)，會(huì)頻繁使用題目名稱，因此在該表中設(shè)計(jì)如下的冗余題目名。

coding_exam_ques_answer_rec：考試題目記錄ID,學(xué)生ID,題目ID,考試ID,

提交答案,答案正確性,最新提交時(shí)間,提交次數(shù),本題得分,題目名。3.外模式設(shè)計(jì)

創(chuàng)建一個(gè)教師信息視圖：Teacher，僅包含教師ID和教師姓名即可。3.3安全性完整性設(shè)計(jì)安全性權(quán)限控制（管理員、教師、學(xué)生各自的權(quán)限設(shè)計(jì)）數(shù)據(jù)加密（用戶的密碼）防SQL注入等（ORM框架等）完整性完整性約束實(shí)體完整性示例：

User_Student表的student_id為主鍵。參照完整性示例：

User_Student表的class_id為外鍵。用戶自定義完整性：User_User表的priority取值：0:學(xué)生,1:教師,2:管理員。觸發(fā)器T1：為了避免統(tǒng)計(jì)功能中的實(shí)時(shí)統(tǒng)計(jì)給系統(tǒng)帶來(lái)過(guò)大壓力，設(shè)計(jì)了觸發(fā)器：在完成某個(gè)學(xué)生的判卷后更新Coding_ExamAnswerRec表的成績(jī)字段時(shí)，自動(dòng)更新單獨(dú)設(shè)計(jì)的統(tǒng)計(jì)信息表Coding_Stat中相關(guān)信息。3.4物理結(jié)構(gòu)設(shè)計(jì)1.確定數(shù)據(jù)庫(kù)物理存儲(chǔ)結(jié)構(gòu)確定數(shù)據(jù)庫(kù)物理結(jié)構(gòu)的內(nèi)容1.確定數(shù)據(jù)的存放位置和存儲(chǔ)結(jié)構(gòu)2.確定系統(tǒng)配置影響數(shù)據(jù)存放位置和存儲(chǔ)結(jié)構(gòu)的因素1.硬件環(huán)境2.應(yīng)用需求3.存取時(shí)間4.存儲(chǔ)空間利用率5.維護(hù)代價(jià)例如：將表和索引放在不同的磁盤（機(jī)械盤和SSD盤）；日志和數(shù)據(jù)分開(kāi)；分區(qū)數(shù)據(jù)根據(jù)應(yīng)用情況將易變部分與穩(wěn)定部分、存取頻率較高部分與存取頻率較低部分分開(kāi)存放，確定熱點(diǎn)數(shù)據(jù)，以提高系統(tǒng)性能。3.4物理結(jié)構(gòu)設(shè)計(jì)2.確定數(shù)據(jù)庫(kù)物理存取方法哪些屬性列建立索引（啟發(fā)式規(guī)則）查詢條件聚集函數(shù)連接條件建立什么樣的索引聚集索引（主鍵索引）/二級(jí)索引（非主鍵索引）B+樹(shù)索引/Hash索引唯一索引單一索引/復(fù)合索引全文索引3.4物理設(shè)計(jì)案例—MYSQL-OJ數(shù)據(jù)存儲(chǔ)：

本例中，在數(shù)據(jù)存儲(chǔ)方面，為了提高系統(tǒng)的高可用性，在云數(shù)據(jù)庫(kù)實(shí)例創(chuàng)建時(shí)，選擇了主備架構(gòu)，該架構(gòu)可以定期備份主庫(kù)的數(shù)據(jù)，防止數(shù)據(jù)丟失。其他采用默認(rèn)。數(shù)據(jù)存取

結(jié)合系統(tǒng)具體的應(yīng)用功能需求，列出系統(tǒng)中可能相對(duì)頻繁執(zhí)行的查詢需求，然后逐一考慮索引設(shè)計(jì)。系統(tǒng)中每個(gè)人僅能看到自己或者自己關(guān)聯(lián)班級(jí)的相關(guān)信息，學(xué)生ID、教師ID、班級(jí)ID經(jīng)常出現(xiàn)在連接條件中。答題功能中，頻繁使用考試ID、試卷ID、題目ID等屬性，但因?yàn)檫@些字段都有主鍵索引，所以無(wú)需額外創(chuàng)建索引。學(xué)生儀表盤的統(tǒng)計(jì)功能中，需統(tǒng)計(jì)該學(xué)生所有考試的總提交次數(shù)。該功能將對(duì)coding_exam_ques_answer_rec表的提交次數(shù)做求和計(jì)算，該表數(shù)據(jù)量較大，因此按照索引設(shè)計(jì)的啟發(fā)式規(guī)則，對(duì)頻繁使用聚集函數(shù)的字段設(shè)計(jì)復(fù)合索引（學(xué)生ID，提交次數(shù)）。3.4數(shù)據(jù)庫(kù)設(shè)計(jì)產(chǎn)出成果物最終形成共13張表(含視圖）3.4數(shù)據(jù)庫(kù)設(shè)計(jì)產(chǎn)出成果物最終形成共13張表(含視圖）3.4數(shù)據(jù)庫(kù)設(shè)計(jì)產(chǎn)出成果物目錄1.選題背景2.需求分析3.數(shù)據(jù)庫(kù)設(shè)計(jì)4.數(shù)據(jù)庫(kù)實(shí)施5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)7.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)運(yùn)行4.數(shù)據(jù)庫(kù)實(shí)施創(chuàng)建數(shù)據(jù)庫(kù)表手動(dòng)創(chuàng)建DDL語(yǔ)句基于建模工具生成DDL語(yǔ)句加載數(shù)據(jù)測(cè)試數(shù)據(jù)：造數(shù)腳本或者造數(shù)工具

（華為云數(shù)據(jù)庫(kù)、存儲(chǔ)過(guò)程、python\sh腳本等）生產(chǎn)數(shù)據(jù)：如果是已有庫(kù)中有數(shù)據(jù)

（數(shù)據(jù)導(dǎo)入導(dǎo)出、數(shù)據(jù)遷移工具等，如kettle等）4.數(shù)據(jù)庫(kù)實(shí)施造數(shù)腳本程序例子：python+MYSQL：loaddataLOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/userinfo.csv'INTOTABLEuserinfofieldsterminatedby',';目錄1.選題背景2.需求分析3.數(shù)據(jù)庫(kù)設(shè)計(jì)4.數(shù)據(jù)庫(kù)實(shí)施5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)7.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)運(yùn)行5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)功能設(shè)計(jì)

——確定功能模塊可以用思維導(dǎo)圖、模塊圖等形式等表示5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)功能設(shè)計(jì)

——分模塊詳細(xì)描述5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)功能設(shè)計(jì)

——分模塊詳細(xì)描述（簡(jiǎn)潔版）簡(jiǎn)要說(shuō)明每個(gè)界面的功能5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)功能設(shè)計(jì)

——分模塊詳細(xì)描述（詳細(xì)版）1.概要描述該頁(yè)面功能。2.針對(duì)每個(gè)用戶可操作或者查看的部分進(jìn)行詳細(xì)的逐一說(shuō)明。5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)UI原型設(shè)計(jì)工具：摹客RP/read/cv11318966/team/wyqernekvf5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)案例考練列表（學(xué)生）統(tǒng)計(jì)——考練詳情（教師）統(tǒng)計(jì)——儀表盤（學(xué)生）5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)架構(gòu)設(shè)計(jì)

——確定語(yǔ)言和架構(gòu)（JAVA、Python、C#）常用WEB前端框架：react,vue，bootstrap,angular,jquery等常用WEB后端框架：python:Django(Model),FlaskJava:SpringBoot（MyBatis）C#:ASP.NET（ADO.NET）Golang:FiberJS:Express.js（Window客戶端）（WEB客戶端）MySQL數(shù)據(jù)庫(kù)服務(wù)器KingBaseWEB前端(瀏覽器)WEB后端(web服務(wù))B/S架構(gòu)C/S架構(gòu)JDBC/ODBC/LIB庫(kù)等（DBMS廠商提供的驅(qū)動(dòng)或接口）C++(QT)C#C++(MFC)Java...GuassDB5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)Django的MVT架構(gòu)ORM：ObjectRelationalMapping講解與示例：/SeddonShen/Django_StudentMS5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)model層：數(shù)據(jù)庫(kù)的每張表有一個(gè)繼承自models類的對(duì)應(yīng)子類view層：new_book:Book類的實(shí)例，該類基類方法save()可以插入數(shù)據(jù)目錄1.選題背景2.需求分析3.數(shù)據(jù)庫(kù)設(shè)計(jì)4.數(shù)據(jù)庫(kù)實(shí)施5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)7.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)運(yùn)行6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)—實(shí)現(xiàn)關(guān)鍵代碼示例重要實(shí)現(xiàn)邏輯描述編程語(yǔ)言：Python前端：Bootstrap后端：Django版本管理：Git功能模塊詳細(xì)信息6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)—測(cè)試開(kāi)發(fā)人員內(nèi)部單元測(cè)試開(kāi)發(fā)人員內(nèi)部功能測(cè)試第三方測(cè)試人員的功能/性能測(cè)試用戶參與的體驗(yàn)測(cè)試6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)DEMOJava(Web)JDBC（原生JDBC，未使用任何框架）【IntelliJIDEA+jdk】MybatisTest（Mybatis）【IntelliJIDEA+maven】StudentManageSystem

【Springboot+Mybatis

】TestManageSystem【Vue+Springboot+MybatisPlus

】Python(Web)StudentManage(Django)/SeddonShen/Django_StudentMSSQL-OJ(Django)

/Steve-Xyh/SQL_memOJiC#(CS)DEMO(ADO.NET)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)DEMO各個(gè)DEMO程序的概要說(shuō)明DEMO參考：/lining_nwpu/DBAppDemo/tree/master目錄1.選題背景2.需求分析3.數(shù)據(jù)庫(kù)設(shè)計(jì)4.數(shù)據(jù)庫(kù)實(shí)施5.數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)6.數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)7.數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)運(yùn)行7.數(shù)據(jù)庫(kù)應(yīng)用程序運(yùn)行數(shù)據(jù)庫(kù)應(yīng)用程序?qū)嵱?xùn)建議