数据库设计题题解

《数据库设计题题解》由会员分享，可在线阅读，更多相关《数据库设计题题解（5页珍藏版）》请在金锄头文库上搜索。

1、解题过程第 01 步，创建“学生成绩”数据库，导入 excel 文件中的数据到“原始数据”表，注意数 据表单的选择，生成表的字段类型的设置。第 02 步，创建表的分解，设计如下： 已经存在的关系： 原始数据（学号，姓名，姓别，生源省，课程名，课程名称，总评成绩） 分析，这是一个满足一范式要求的表。说到范式，需要回顾的知识点： 1 函数依赖：完全函数依赖，部分函数依赖，传递函数依赖。 2 范式定义：一范式，二范式，三范式 3 由冗余而引发的数据操作的异常：插入异常，删除异常，更新异常 再次观察原始数据表： Select * from 原始数据 order by 学号 Select * from

2、原始数据 order by 课程号 可以发现，学号，姓名有大量的重复，课程号，课程名也有大量的重复，有很多学生来源 于同一个省，生源省也大量的重复。这些就是数据冗余，而这些冗余会引发一些异常， 比如 1 删除某些学生的信息，可能导致个别生源省的信息也被删除掉了； 2 更新更个学生的记录，比如修改了生源省，也可能导致某个生源省的信息丢失；而且 要求同时更新多行记录，这样才不至于出现矛盾的数据：某名学生既在这个省，又在 那个省，数据的合理一致性很难维护； 3 批量插入数据时，也存在类似于上两个的问题；分析原因：最主要的原因是：“原始数据”这张表的范式级别太低了，它只满足了一范式 的要求。何为一范式

3、：字段只达到了不可再分的原子性要求。没有考虑字段之间的函数依赖关系。进一步分析：“原始数据”这张表中，起着一定的决定作用的是：学号，课程号。 自然语意应该是这样： 1 学号定了，学生姓名，学生性别，学生所在生源省也就定了； 2 课程号定了，课程名称也就定了； 3 学号并且课程号定了，成绩也就定了； 即，总的来说，学号，课程号将会是主键，起决定作用。 而这样一来，姓名，性别，生源省，课程名称都部分函数依赖于（学号，课程号） ，只有总 评成绩是完全函数依赖于（学号，课程号） ，故，这个范式不满足二范式要求。 何为二范式：首先满足一范式要求，且要求字段之间不存在部分函数依赖，即非主属性应 该对主属性

4、是完全的函数依赖。 为了消除因冗余引起的诸多异常，现分解该关系，提升关系的范式级别，使达到二范式要 求。分析关系，提升范式级别的方法： 1 识别决定因子，即起决定作用的字段； 2 取决定因子集合的所有非空子集，并依据这些子集建新表，给出适当的表名； 3 往这些新表中，把完全函数依赖于这些决定因子的原表中的字段，插入到新表中去； 4 删除只由决定因子字段本身构成的表。第 03 步 按上面的方法步骤，完成对“原始数据”表的分解： 1 识别决定因子字段：（学号，课程号） 2 列出非空子集：（学号） ， （课程号） ， （学号，课程号） ；建立新表如下： 学生（学号, ） 课程（课程号, ） 选课（学

5、号，课程号， ） 3 字段再分配，把完全函数依赖于这些相应表的决定因子字段的其他字段插入到相应的 表中： 学生（学号，姓名，性别，生源省） 课程（课程号，课程名称） 选课（学号，课程号，总评成绩） 4 没有仅仅只由决定因子列构成的表，这一步省掉了。到现在，我们得到了三张表。即： 学生（学号，姓名，性别，生源省） 课程（课程号，课程名称） 选课（学号，课程号，总评成绩） 自然而然，我们可以通过分析，得到三张表相应的主键和外键以及较合理情况下的一些约 束，如： 1 学号是学生表的主键，课程号是课程表的主键，学号，课程号共同构成了选课表的主 键； 2 肯定是学生选修了某门课程才会在选课表中产生记录，

6、而且，总评成绩会因为选课以后的考试而产生，故有这样的外键关系：“选课.学号”肯定是参照了“学生.学号” ， “选课.课程号”肯定是参照了“课程.课程号” ； 3 根据 2 的分析，总评成绩肯定是事先为 NULL，有成绩以后再录入。即总评成绩这个 字段，肯定是允许为空，且可以设置默认值为空； 4 观察“原始数据”表，并通过对表上字段长度的分析（见“原始数据分析操作.sql”文 件）可以发现，学号字段可以设数据类型和长度：char(10)；姓名字段为：char(8)，性 别字段：char(2)，而且性别只能取“男”和“女” ，即约束为 check(性别=男 or 性别 =女)，生源省字段：char

7、(6)；课程号：int，如果考虑周全，它应该有这样的约束： check(课程号1000 and 课程号0)，这一点，可以参照 studentdb 库的专业表中的专业代码的设置情况来看；课程名称，取可以跟最长兼容的 情况，不妨认为是最长为 18 个汉字：char(40)；而成绩，肯定是取整型：int，如果考 虑周全，它应该有这样的约束：check(成绩0 and 成绩100) 有了这些业务上的分析和认识以后，下面我们根据题设情况，完成的思路就比较清晰了。 创建这些表，然后往这些表中插入数据（见“创建表并填充数据等操作.sql” ） 。第 04 步 在导入数据完成以后，剩下来的就是优化数据库 在数

8、据库表上建立相应的索引，以成倍提高工作人员以下的日常查询业务的性能： (1)根据课程名称查询某门课程的全部成绩； (2)根据学生学号查询某学生的全部课程成绩； (3)根据学生姓名查询某学生的全部课程成绩； (4)根据学生姓名和课程名称查询某学生的某门课程成绩。 注意：并非每一查询业务都需要一单独的索引。 经常进行的查询列上，加上索引是可以提高查询的性能的（见“创建索引和生成视图.sql” ） 。第 05 步 建立名为“V_学生成绩“的视图： 要求打开该查询后，看到的内容与 Excel 表中完全一致，包括： (1)各数据列间的前后顺序及数据值； 这里的要求，也就是数据行和列上的排序要求： 行上：

9、其实通过观察可以发现，数据是按课程号排序，再按总评成绩由高到低排序， 再按学号升序，即第(3)要求； 列上：学号，姓名，性别，生源省，课程号，课程名称，平评成绩 共实，也就是通过三张基表：“学生” “课程” “选课”生成一个与“原始数据”表一 样的视图。 (2)总的数据行数； 36401 行，即学生选课的情况 (3)数据行间的前后顺序Excel 表中数据行间的排序规则为：课程号升序、成绩降 序、学号升序。 （见“创建索引和生成视图.sql” ） 到这里，本题解完。小结知识点：1 创建数据库对象：库，表，视图，索引等 2 创建约束：主键，外键，规则约束，确定域等 3 数据操纵：查询，转移，排序等

10、 4 数据库设计与优化 5 数据库备份 6 数据导入与转化 7 考试系统的使用方法，注意压包的方法强调：在企业管理器下，用适当的菜单命令可以完成，在查询分析器下，用适当的 sql 语 句也可以完成，建议大家熟悉什么，用什么。第 06 步 分析，并深度改进：生源省单独成表。分析，并深度改进：生源省单独成表。 原因：在学生表中，可以看到，生源省列的数据大量的重复，即冗余比较严重。而且这些 冗余会带来一些数据库数据维护上的异常。如： 1 删除某些学生的信息，如果在某个省只招收了两名学生，而这两名学生毕业了，数据 要删除或是转移，这时，学生表中本来有该生源省的数据信息的，有可能因为这两名 学生的数据的

11、去除，而把某个生源省的数据也去除了； 2 又比如，修改数据时，把新疆改为新疆维吾尔自治区。这里，所有新疆的学生的生源 省均要作此修改，否则，数据不一致将产生，并且在业务意义上，如果有的改了，有 的没有改，则会导致人们认为新疆和新疆维吾尔自治区是两个不同的业务含义； 3 插入数据时也一样会引起上面 1,2 的异常； 而这些异常，都是由于这里的“生源省”数据的冗余造成的。 而上升到理论层次，即学生（学号，姓名，性别，生源省）里出来出这样一种列，这列数 据不依赖于主键学号。即，生源省是非完全函数依赖于学号的。因此，生源省自己可以起 到自己的决定作用。 也就是说，学生关系不满足二范式要求了。即，学生表

12、中， “学号”是决定因子， “生源省” 也是决定因子。因此，分解成为两张表： 学生（学号，姓名，性别，生源省） 生源（生源省） 两张表，学生表主键为学号，生源表主键就是生源省，而且只有一列数据。但有很重要的 一点需要指出来的就是：“学生”表中的“生源省”将是外键，它参考的数据是“生源” 表中的“生源省”列。 也许很多人都不禁要问，也许很多人都不禁要问， 这样单独弄一个这样单独弄一个“生源生源”表出来有何意义？表出来有何意义？ 1 上述异常会因为生源省数据的独立成表而排除掉； 2 生源省的独立成表，而学生表中并未删除生源省，虽然造成了数据冗余，但在学生表 中用到生源省的地方，查询上不再需要表与表的连接运算，大大地节约了时间，相当 于优化了查询（即由空间换取了时间） ； 3 提高了范式的级别； 4 生源省数据的独立，避免了数据产生不一致性。管“生源省”数据的用户可以把注意 力集中到这一张表上，而用“生源省”的用户，则通过外键参考直接用就行了，不用 担心数据在插入，更新，删除时产生不一致的情况；第 07 步： 重新设计“学生成绩”数据库，再完成实施 学生（学号，姓名，性别，生源省） 生源（生源省） 课程（课程号，课程名称） 选课（学号，课程号，总评成绩）（见“满分参考答案之生源省讨论后重做.sql” ）