《MySQL数据库应用案例教程》651-5（马洁）教案 第23课 新闻发布系统数据库设计

PAGE10PAGE10PAGE9PAGE9

课题新闻发布系统数据库设计课时2课时（90min）教学目标知识技能目标：（1）了解新闻发布系统的需求（2）熟悉新闻发布系统的常见功能（3）掌握新闻发布系统的概念设计（4）掌握新闻发布系统中表的设计（5）掌握新闻发布系统中索引的设计（6）掌握新闻发布系统中视图的设计（7）掌握新闻发布系统中触发器的设计素质目标：（1）懂得团结合作能让工作事半功倍的道理（2）了解数据库的发展及主流国产数据库的应用，增强民族自豪感教学重难点教学重点：熟悉新闻发布系统的常见功能教学难点：掌握新闻发布系统的数据库设计教学方法案例分析法、问答法、讨论法、讲授法、实践法教学用具电脑、投影仪、多媒体课件、教材教学设计第1节课：→→→传授新知（38min）第2节课：→传授新知（20min）→课堂实训（15min）→课堂小结（3min）→作业布置（2min）教学过程主要教学内容及步骤设计意图第一节课课前任务【教师】布置课前任务，和学生负责人取得联系，让其提醒同学通过文旌课堂APP或其他学习软件，预习本节课要学习的知识【学生】完成课前任务通过课前任务，使学生预习本节课要学习的知识，增加学生的学习兴趣考勤（2min）【教师】使用文旌课堂APP进行签到【学生】班干部报请假人员及原因培养学生的组织纪律性，掌握学生的出勤情况问题导入（5min）【教师】提出以下问题：实现一个小型新闻发布系统的数据库的流程有哪些？·通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（38min）【教师】通过学生的回答引入要讲的知识，讲解和演示小型新闻发布系统数据库设计的需求分析、系统功能和数据库概念设计方法18.1需求分析本章介绍一个小型新闻发布系统的实现，管理员可以使用该系统发布和管理新闻信息、管理用户、管理角色权限、管理评论等。通过对实际情况的调查，要求本系统主要实现以下功能：具有用户注册功能。注册用户可以对个人信息进行修改。注册用户可以对新闻进行评论。管理员可以按角色分配权限。管理员可以发布和删除新闻。管理员可以对用户相关信息进行管理。18.2系统功能通过前面对需求的分析和总结，可将新闻发布系统分为6个模块，包括用户管理、管理员管理、权限管理、角色管理、新闻管理和评论管理。✈【教师】通过多媒体展示“新闻发布系统功能模块图”图片（详见教材），然后进行详细讲解各模块详细介绍如下。用户管理模块：实现添加、查看、修改和搜索用户信息功能。管理员管理模块：实现添加、查看、修改和搜索管理员信息功能。权限管理模块：实现添加、查看、修改和删除权限功能。角色管理模块：实现添加角色，给角色赋权，并将分配好权限的角色与管理员相关联等功能。新闻管理模块：实现有相关权限的管理员对新闻的增加、删除、修改和查看功能。评论管理模块：实现有相关权限的管理员对评论的审核和删除功能。18.3数据库概念设计数据库概念设计就是对用户要求描述的现实世界（可能是一个工厂、一个商场或者一个学校等），通过对其中各处的分类、聚集和概括，建立抽象的概念数据模型。这个概念模型应反映现实世界各部门的信息结构、信息流动情况、信息间的互相制约关系，以及各部门对信息储存、查询和加工的要求等。实际应用中常用E-R图表示。✈【教师】随机邀请学生回答以下问题：什么是E-R图？✈【学生】聆听、思考、回答✈【教师】总结学生的回答E-R图也称实体-联系图（EntityRelationshipDiagram），它是描述现实世界概念结构模型的有效方法，是表示概念模型的一种方式，用矩形表示实体型，矩形框内写明实体名；用椭圆表示实体属性，并用无向边将其与相应的实体型连接起来；用菱形表示实体型之间的联系，在菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1:1，1:n或m:n）。✈【教师】通过多媒体展示“新闻发布系统数据库E-R图”图片（详见教材），然后进行详细讲解通过需求分析和系统功能设计，本系统规划出用户实体、管理员实体、新闻分类实体、新闻实体、用户评论实体、权限实体、角色实体和管理员角色实体。18.4数据库逻辑结构与物理结构设计数据库逻辑结构设计，就是根据已经建立的概念数据模型，以及所采用的数据库管理系统的数据模型特性，按照一定的转换规则，把概念模型转换为该数据库管理系统所能接受的逻辑数据模型。通俗来讲，就是将E-R图转换成表格的形式。数据库物理结构设计，就是为一个确定的逻辑数据模型选择一个最适合应用要求的物理结构的过程。此处就是根据逻辑表结构在数据库中创建具体的数据表。本节首先以表为单位，逐个创建数据表的结构，并根据表结构在数据库中创建数据表。然后根据用户处理的要求，在基本表的基础上创建必要的索引、视图和触发器，最终完成数据库系统的设计。18.4.1设计表根据需求分析及数据库概念设计，可以得出本系统的数据库news中共存放8张表，分别是user、admin、category、news、comment、rule、role和admin_roles表。创建和选择news数据库的SQL代码如下。CREATEDATABASEnews;#创建news数据库USEnews;#选择news数据库接下来详细介绍本系统中各表的创建过程。1．user表user表用于存储用户信息，包括用户ID、用户名、昵称、密码、性别、邮箱、手机号和创建时间8个字段。该数据表的结构如表18-1所示。✈【教师】通过多媒体展示“user表结构”表格，介绍相关知识表18-1user表结构字段名数据类型长度允许空值说明idINT10否用户ID（主键）nameVARCHAR20否用户名nicknameVARCHAR20否昵称passwordVARCHAR10否密码sexTINYINT1否性别（1男2女）emailVARCHAR20否邮箱cellphoneCHAR10否手机号dateINT10否创建时间根据表18-1的内容创建user表，创建user表的SQL语句如下：CREATETABLEuser(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'用户ID',nameVARCHAR(20)NOTNULLCOMMENT'用户名',nicknameVARCHAR(20)NOTNULLCOMMENT'昵称',passwordVARCHAR(10)NOTNULLCOMMENT'密码',sexTINYINT(1)UNSIGNEDNOTNULLCOMMENT'性别（1男2女）',emailVARCHAR(20)NOTNULLCOMMENT'邮箱',cellphoneCHAR(11)NOTNULLCOMMENT'手机号',dateINT(10)NOTNULLCOMMENT'创建时间',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='用户信息';【提示】数据表创建完成后，可以使用DESC语句查看表的基本结构，也可以通过SHOWCREATETABLE语句查看表的详细信息。2．admin表admin表用于存储管理员信息，包括管理员ID、用户名、密码、真实姓名、手机号和创建时间6个字段。该数据表的结构如表18-2所示。✈【教师】通过多媒体展示“管理员信息表”表格，介绍相关知识表18-2管理员信息表字段名数据类型长度允许空值说明idINT10否管理员ID（主键）adminNameVARCHAR20否用户名passwordVARCHAR10否密码nameVARCHAR20否真实姓名cellphoneCHAR10否手机号dateINT10否创建时间根据表18-2的内容创建admin表，创建admin表的SQL语句如下：CREATETABLEadmin(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'管理员ID',adminNameVARCHAR(20)NOTNULLCOMMENT'用户名',passwordVARCHAR(10)NOTNULLCOMMENT'密码',nameVARCHAR(20)NOTNULLCOMMENT'真实姓名',telCHAR(11)NOTNULLCOMMENT'手机号',dateINT(10)UNSIGNEDNOTNULLCOMMENT'创建时间',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='管理员信息';3．category表category表用于存储新闻分类，包括分类ID、分类名称和分类描述3个字段。该数据表的结构如表18-3所示。✈【教师】通过多媒体展示“新闻分类表”表格，介绍相关知识表18-3新闻分类表字段名数据类型长度允许空值说明idINT10否分类ID（主键）titleVARCHAR30否分类名称describeVARCHAR50否分类描述根据表18-3的内容创建category表，创建category表的SQL语句如下：CREATETABLEcategory(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'分类ID',titleVARCHAR(30)NOTNULLCOMMENT'分类名称',`describe`VARCHAR(50)NOTNULLCOMMENT'分类描述',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='新闻分类';【提示】上述语句中，由于describe为MySQL关键字，所以需要使用反引号``将其引起来，该符号位于键盘左上角。为避免出错，在建表时也可以为所有字段加上反引号。4．news表news表用于存储新闻详情，包括新闻ID、分类ID、标题、发布人、发布时间、内容和审核7个字段。该数据表的结构如表18-4所示。✈【教师】通过多媒体展示“新闻信息表”表格，介绍相关知识表18-4新闻信息表字段名数据类型长度允许空值说明idINT10否新闻ID（主键）category_idINT10否分类IDtitleVARCHAR50否标题authorVARCHAR20否发布人dateINT10否发布时间contentTEXT否内容isshowTINYINT1否审核（1通过2未通过）默认设为2根据表18-4的内容创建news表，创建news表的SQL语句如下：CREATETABLEnews(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'新闻ID',category_idINT(10)UNSIGNEDNOTNULLCOMMENT'分类ID',titleVARCHAR(50)NOTNULLCOMMENT'标题',authorVARCHAR(20)NOTNULLCOMMENT'发布人',dateINT(10)NOTNULLCOMMENT'发布时间',contentTEXTNOTNULLCOMMENT'内容',isshowTINYINT(1)UNSIGNEDNOTNULLDEFAULT'2'COMMENT'审核（1通过2未通过）',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='新闻信息';5．comment表comment表用于存储用户评论信息，包括评论ID、用户ID、新闻ID、评论内容、评论时间和审核6个字段。该数据表的结构如表18-5所示。✈【教师】通过多媒体展示“用户评论表”表格，介绍相关知识表18-5用户评论表字段名数据类型长度允许空值说明idINT10否评论ID（主键）userIDINT10否用户IDnewsIDINT10否新闻IDcontentVARCHAR255否评论内容dateINT10否评论时间isshowTINYINT1否审核（1通过2未通过），默认为1根据表18-5的内容创建comment表，创建comment表的SQL语句如下：CREATETABLEcomment(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'评论ID',userIDINT(10)UNSIGNEDNOTNULLCOMMENT'用户ID',newsIDINT(10)UNSIGNEDNOTNULLCOMMENT'新闻ID',contentVARCHAR(255)NOTNULLCOMMENT'评论内容',dateINT(10)UNSIGNEDNOTNULLCOMMENT'评论时间',isshowTINYINT(1)UNSIGNEDNOTNULLDEFAULT'1'COMMENT'审核（1通过2未通过）',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='用户评论信息';6．rule表rule表用于存储权限信息，包括权限ID、父ID、权限名称、路径和状态5个字段。该数据表的结构如表18-6所示。✈【教师】通过多媒体展示“权限信息表”表格，介绍相关知识表18-6权限信息表字段名数据类型长度允许空值说明idINT10否权限ID（主键）pidINT10否父级权限IDtitleVARCHAR20否权限名称urlVARCHAR30否路径stateTINYINT1否状态（1显示2隐藏）默认设为1根据表18-6的内容创建rule表，创建rule表的SQL语句如下：CREATETABLErule(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'权限ID',pidINT(10)UNSIGNEDNOTNULLCOMMENT'父级权限ID',titleVARCHAR(20)NOTNULLCOMMENT'权限名称',urlVARCHAR(30)NOTNULLCOMMENT'路径',stateTINYINT(1)NOTNULLDEFAULT'1'COMMENT'状态（1显示2隐藏）',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='权限信息';7．role表role表用于存储角色权限信息，包括角色ID、父级角色ID、角色名称和权限信息4个字段。该数据表的结构如表18-7所示。✈【教师】通过多媒体展示“角色权限表”表格，介绍相关知识表18-7角色权限表字段名数据类型长度允许空值说明idINT10否角色ID（主键）pidINT10否父级角色IDtitleVARCHAR20否角色名称rulesVARCHAR50否权限信息根据表18-7的内容创建role表，创建role表的SQL语句如下：CREATETABLErole(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'角色ID',pidINT(10)UNSIGNEDNOTNULLCOMMENT'父级角色ID',titleVARCHAR(20)NOTNULLCOMMENT'角色名称',rulesVARCHAR(50)NOTNULLCOMMENT'权限信息',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='角色权限';8．admin_roles表admin_roles表用于存储管理员角色信息，包括管理员角色ID、管理员ID和角色ID3个字段。该数据表的结构如表18-8所示。✈【教师】通过多媒体展示“管理员角色表”表格，介绍相关知识表18-8管理员角色表字段名数据类型长度允许空值说明idINT10否管理员角色ID（主键）admin_idINT10否管理员IDrole_idINT10否角色ID根据表18-8的内容创建admin_roles表，创建admin_roles表的SQL语句如下：CREATETABLEadmin_roles(idINT(10)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'管理员角色ID',admin_idINT(10)UNSIGNEDNOTNULLCOMMENT'管理员ID',role_idINT(10)UNSIGNEDNOTNULLCOMMENT'角色ID',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='管理员角色信息';【学生】聆听、思考、记录通过教师的讲解和演示，使学生了解小型新闻发布系统数据库设计的需求分析、系统功能、数据库概念设计的相关知识第二节课问题导入（5min）【教师】提出以下问题：在新闻发布系统中需要查询新闻信息，应该如何提高查询速度？【学生】思考、举手回答通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（20min）【教师】通过学生的回答引入新知，介绍数据库逻辑结构与物理结构设计的相关知识18.4.2设计索引✈【教师】随机邀请学生回答以下问题：创建索引的方法有哪些？✈【学生】聆听、思考、回答✈【教师】总结学生的回答索引是对数据表中一列或多列值进行排序的一种结构，使用它可以有效提高访问数据表中特定信息的速度。在新闻发布系统中需要查询新闻信息，为提高查询速度就需要在某些特定字段上建立索引。本节使用CREATEINDEX语句和ALTERTABLE语句创建索引。1．在news表上创建索引新闻发布系统需要通过news表中的id字段、title字段、author字段和date字段查询新闻信息。由于建表时，已经将id字段设为主键，所以此处只需创建其他三个索引即可。具体创建索引的语句如下：使用CREATEINDEX语句给news表中title字段创建名为index_title的索引。SQL语句如下：CREATEINDEXindex_titleONnews(title);使用CREATEINDEX语句给news表中author字段创建名为index_author的索引。SQL语句如下：CREATEINDEXindex_authorONnews(author);使用CREATEINDEX语句给news表中date字段创建名为index_date的索引。SQL语句如下：CREATEINDEXindex_dateONnews(date);2．在comment表上创建索引新闻发布系统需要通过comment表中的id字段、userID字段和newsID字段查询评论内容。由于建表时，已经将id字段设为主键，所以此处只需创建其他两个索引即可。具体创建索引的语句如下。使用CREATEINDEX语句给comment表中的userID字段创建名为index_userID的索引。SQL语句如下：CREATEINDEXindex_userIDONcomment(userID);使用ALTERTABLE语句给comment表中newsID字段创建名为index_newsID的索引。SQL语句如下：ALTERTABLEcommentADDINDEXindex_newsID(newsID);18.4.3设计视图✈【教师】随机邀请学生回答以下问题：数据库会对视图所对应的数据进行实际存储吗？✈【学生】聆听、思考、回答✈【教师】总结学生的回答视图是基于数据库中一个或多个表而导出的虚拟表。数据库中只存储视图的定义，对视图所对应的数据不进行实际存储，在对视图中数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。本系统中设计了一个视图来改善查询操作。在本系统中查看新闻评论内容，如果直接以news表中的新闻ID为条件查询comment表，显示信息时会显示用户ID。这种显示对用户而言是不友好的，用户是不知道自己ID的，但是他知道自己的昵称，因此可以创建一个视图comment_view，显示评论ID、用户昵称、新闻ID、评论内容、评论时间和审核。创建视图comment_view的SQL语句如下：CREATEVIEWcomment_viewASSELECTc.id,u.nickname,c.newsID,c.content,c.date,c.isshowFROMcommentc,useruWHEREc.userID=u.id;上述SQL语句给comment表设置了别名c，user表设置了别名u，该视图从这两个表中取出相应的字段。【提示】视图创建完成后，可以使用SHOWCREATEVIEW语句查看其详细信息。18.4.4设计触发器✈【教师】随机邀