MySQL云数据库应用与实践 课件 ch13-数据库应用开发综合实战案例

第十三章

数据库应用开发综合实战案例DatabaseSystem西北工业大学NorthwesternPolytechnicalUniversity数据库系统李宁目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行1.选题背景源自真实场景：数据库实验教学的痛点学生提交大量SQL作业增大教师批阅工作量SQL语句写法灵活多样导致批阅难度较大作业批阅结果可能也无法及时反馈给学生开发一个高效的SQL-OJ（OnlineJudge）系统以提高数据库课程的教学效率和效果。目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行2.需求调研——方法

调查组织机构/用户调查各个部门业务活动明确新系统的各种要求确定新系统的边界调查的方法跟班作业开调查会专人介绍调查表查阅记录其他信息需求+处理需求+非功能需求2.需求调研案例——MYSQL-OJ数据需求用户信息题库与题目信息试卷信息练习或考试信息答题信息功能需求教师：班级管理、班内成员管理、题库/题目/试卷/考试管理、统计信息学生：注册、个人信息管理、考试答题功能、仪表盘管理员：学校管理、教师用户管理非功能需求安全性需求:权限控制性能需求：支持1000人同时在线答题，题目3秒内提交到系统分权限、管数据、定功能需求调研结果的分析和表达方法数据流图数据字典数据项数据结构数据流数据存储处理过程2.需求调研——结果表达信息需求处理需求完整性需求安全性需求性能需求其他2.需求调研案例——MYSQL-OJ数据流图2.需求调研案例——MYSQL-OJ数据字典：以该系统服务100所学校，10年为例数据处理数据流数据存储目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行3.数据库设计概念结构设计：ER图设计逻辑结构设计:ER图转换成关系模式安全性与完整性设计：加密、防注入、约束、触发器等物理结构设计：存储与存取，重点是索引物理数据库3.1概念结构设计数据抽象，局部视图设计视图集成DFDDD总E-R图逻辑结构设计返回用户，征求意见到满意为止需求分析分E-R图

1)抽象数据+设计局部视图

2)集成局部视图，得到全体的概念结构3.1概念设计案例—MYSQL-OJ

语义描述与数据抽象用户相关实体：学校、班级、用户（管理员、教师、学生）考试相关实体：题库、题目、试卷、考试或者练习活动根据现实世界与应用需求分析，实体之间语义描述如下：一个学校拥有多个班级，一个班级只能属于某个学校；一个班级有多名学生，一个学生只能属于一个班级。系统可以有多个管理员，任意一个管理员可以增删改所有学校、教师的基本信息；每位教师管理所负责班级的学生信息。一位教师可以创建多个题库（题库指一个事先准备好的数据库，可包含多张表以及部分数据），每个题库只能被一位老师创建。基于一个题库可以出多道题目，每个题目只能属于某一个题库。每位教师可以出若干道题目。一套试卷可以由任意多道题目组成，相同题目可出现在多套试卷中，且相同题目在不同试卷中分值可以不同。每位教师可给所负责的一个或多个班级，将任意一套可访问的试卷发布为一次考练活动。学生可以作答自己可见的未过期考练活动。3.1概念设计案例—MYSQL-OJ

实体属性描述3.1概念设计案例—MYSQL-OJ系统总ER图（12个实体+14个联系）分ER图3.2逻辑结构设计—ER图转换

逻辑结构设计：ER图转换为逻辑结构基本转换实体与属性转换1对1联系：合并到任意1端[强制参与]或单独转成新关系1对多转换：合并到N端或单独成新关系多对多转换：联系转换成单独成新关系特殊转换：复合属性、弱实体、ISA等设计优化范式理论判断与分解基于应用特征分析的分解与合并外模式设计3.2逻辑设计案例ER图中的实体转换ER图中的联系转换3.2逻辑模式优化案例1.模式优化：以Exam、Exam_class为例分析。其他关系均满足BCNF。1)Exam(考试ID,考试名,考试描述,开始时间,结束时间,活跃状态,是否公布答案,教师ID,试卷ID)：该关系的码是考试ID，不存在非主属性和主属性（考试ID）对码的部分函数依赖和传递函数依赖，因此满足BCNF。2）Exam_class(考试ID(FK),班级ID(FK),发布时间)：假设允许把某次考试给某个班级发布多次，则码中需要加入发布时间。由此该关系为全码，无非主属性，满足BCNF。2.其他优化（反范式化设计、分解或合并等）

题目名：该系统中教师身份登录时，以班级为单位进行作答统计信息，在访问题

目的作答记录表时，会频繁使用题目名称，因此在该表中设计如下的冗余题目名。

coding_exam_ques_answer_rec：考试题目记录ID,学生ID,题目ID,考试ID,

提交答案,答案正确性,最新提交时间,提交次数,本题得分,题目名。3.外模式设计

创建一个教师信息视图：Teacher，仅包含教师ID和教师姓名即可。3.3安全性完整性设计安全性权限控制（管理员、教师、学生各自的权限设计）数据加密（用户的密码）防SQL注入等（ORM框架等）完整性完整性约束实体完整性示例：

User_Student表的student_id为主键。参照完整性示例：

User_Student表的class_id为外键。用户自定义完整性：User_User表的priority取值：0:学生,1:教师,2:管理员。触发器T1：为了避免统计功能中的实时统计给系统带来过大压力，设计了触发器：在完成某个学生的判卷后更新Coding_ExamAnswerRec表的成绩字段时，自动更新单独设计的统计信息表Coding_Stat中相关信息。3.4物理结构设计1.确定数据库物理存储结构确定数据库物理结构的内容1.确定数据的存放位置和存储结构2.确定系统配置影响数据存放位置和存储结构的因素1.硬件环境2.应用需求3.存取时间4.存储空间利用率5.维护代价例如：将表和索引放在不同的磁盘（机械盘和SSD盘）；日志和数据分开；分区数据根据应用情况将易变部分与稳定部分、存取频率较高部分与存取频率较低部分分开存放，确定热点数据，以提高系统性能。3.4物理结构设计2.确定数据库物理存取方法哪些属性列建立索引（启发式规则）查询条件聚集函数连接条件建立什么样的索引聚集索引（主键索引）/二级索引（非主键索引）B+树索引/Hash索引唯一索引单一索引/复合索引全文索引3.4物理设计案例—MYSQL-OJ数据存储：

本例中，在数据存储方面，为了提高系统的高可用性，在云数据库实例创建时，选择了主备架构，该架构可以定期备份主库的数据，防止数据丢失。其他采用默认。数据存取

结合系统具体的应用功能需求，列出系统中可能相对频繁执行的查询需求，然后逐一考虑索引设计。系统中每个人仅能看到自己或者自己关联班级的相关信息，学生ID、教师ID、班级ID经常出现在连接条件中。答题功能中，频繁使用考试ID、试卷ID、题目ID等属性，但因为这些字段都有主键索引，所以无需额外创建索引。学生仪表盘的统计功能中，需统计该学生所有考试的总提交次数。该功能将对coding_exam_ques_answer_rec表的提交次数做求和计算，该表数据量较大，因此按照索引设计的启发式规则，对频繁使用聚集函数的字段设计复合索引（学生ID，提交次数）。3.4数据库设计产出成果物最终形成共13张表(含视图）3.4数据库设计产出成果物最终形成共13张表(含视图）3.4数据库设计产出成果物目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行4.数据库实施创建数据库表手动创建DDL语句基于建模工具生成DDL语句加载数据测试数据：造数脚本或者造数工具

（华为云数据库、存储过程、python\sh脚本等）生产数据：如果是已有库中有数据

（数据导入导出、数据迁移工具等，如kettle等）4.数据库实施造数脚本程序例子：python+MYSQL：loaddataLOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/userinfo.csv'INTOTABLEuserinfofieldsterminatedby',';目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行5.数据库应用程序设计功能设计

——确定功能模块可以用思维导图、模块图等形式等表示5.数据库应用程序设计功能设计

——分模块详细描述5.数据库应用程序设计功能设计

——分模块详细描述（简洁版）简要说明每个界面的功能5.数据库应用程序设计功能设计

——分模块详细描述（详细版）1.概要描述该页面功能。2.针对每个用户可操作或者查看的部分进行详细的逐一说明。5.数据库应用程序设计UI原型设计工具：摹客RP/read/cv11318966/team/wyqernekvf5.数据库应用程序设计案例考练列表（学生）统计——考练详情（教师）统计——仪表盘（学生）5.数据库应用程序设计架构设计

——确定语言和架构（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数据库服务器KingBaseWEB前端(浏览器)WEB后端(web服务)B/S架构C/S架构JDBC/ODBC/LIB库等（DBMS厂商提供的驱动或接口）C++(QT)C#C++(MFC)Java...GuassDB5.数据库应用程序设计Django的MVT架构ORM：ObjectRelationalMapping讲解与示例：/SeddonShen/Django_StudentMS5.数据库应用程序设计model层：数据库的每张表有一个继承自models类的对应子类view层：new_book:Book类的实例，该类基类方法save()可以插入数据目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行6.数据库应用程序开发—实现关键代码示例重要实现逻辑描述编程语言：Python前端：Bootstrap后端：Django版本管理：Git功能模块详细信息6.数据库应用程序开发—测试开发人员内部单元测试开发人员内部功能测试第三方测试人员的功能/性能测试用户参与的体验测试6.数据库应用程序开发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.数据库应用程序开发DEMO各个DEMO程序的概要说明DEMO参考：/lining_nwpu/DBAppDemo/tree/master目录1.选题背景2.需求分析3.数据库设计4.数据库实施5.数据库应用程序设计6.数据库应用程序开发7.数据库应用系统运行7.数据库应用程序运行数据库应用程序实训建议