第二次”数据库原理及应用”课程大作业

一、分析说明题

SQL关系数据库为什么有强大长久的生命力？其中原因对我国发展数据库技术有何借鉴意义？推崇技术创新、技术标准化、大国工匠精神对我国数据库软件技术发展有何作用？

答：

（1）关系型数据库是创建在关系模型基础上的数据库，具有数据集中控制、数据独立性高、数据共享性好、数据冗余度小、数据结构化和统一的数据保护功能等特点。SQL是针对关系数据库的通用操作语言，它具有一体化、使用方式灵活、非过程化和语言语法简单等特点。

之所以有强大长久的生命力，是因为SQL关系数据库有以下优点：

   ①采用二维表结构，非常贴近正常开发逻辑，关系型数据模型相对层次型数据模型和网状型数据模型等其他模型，更容易理解；

   ②支持通用的SQL语句；

   ③丰富的完整性，减少了数据冗余和数据不一致的问题，并且全部由表结构组成，文件格式一致；

   ④使用简单，SQL这个优秀的抽象层，它完全屏蔽了底层的实现细。可以用SQL句子多个表之间做非常繁杂的查询；

   ⑤提供对事务的支持，能保证系统中事务的正确执行，同时提供事务的恢复、回滚、并发控制和死锁问题的解决；

   ⑥数据存储在磁盘中，安全可靠。

（2）对于我国数据库技术发展，有以下三点借鉴意义：

    ①我们要关注关键应用中SQL的执行效率。国产数据库的CBO优化器目前的水平与Oracle相差甚远，如果某条SQL执行做错了执行计划，那么性能会有巨大的损失。我们应该构建国产数据库执行计划的性能基线，并定期对核心SQL的执行计划基线进行比对分析。一旦发现执行计划存在严重的恶化，才能够尽快找出问题，并进行解决；

    ②我国数据库技术基础薄弱，人才匮乏，大多数技术来源于开源项目。充分利用开源技术能够加速国产数据库产业的发展，缩短与国外头部企业的差距。不过利用开源技术不等于完全依靠开源技术，而是应该在开源技术基础上进行大量的自主创新，加入自己的技术；

   ③利用各类工具组件，做到数据库应用“平民化”。为达到政务人员深度应用信息科技工具的目的，一方面在加强政务人员信息化能力同时，数据库应用过程也需尽量做到“平民化”，让数据库系统简单化，提高可用性。

（3）推崇技术创新、技术标准化、大国工匠精神，实际上就是培养更多高素质技术技能人才。在激烈的市场竞争和转型升级压力下，“工匠精神”被赋予以创新为导向、以技术为生命、以质量为追求的新内涵。

作为关键的信息基础设施，数据库的安全、稳定、可靠、性能、容量等对国计民生有着根本性的影响。推崇技术创新、技术标准化、大国工匠精神，有利于各公司重视科技创新，重投科研，将持续投入核心技术研发加大自有技术分享并助力实体经济数字化升级；有利于增强自研力量和创新能力，在多项技术领域突破“卡脖子”技术问题；有利于自主研发安全可靠数据库产品，摘下这颗“基础软件皇冠上的明珠”。

二、SQL编程动手实践题

1. 编写并运行SQL语句，创建数据库estatedb。

      如图1，执行SQL语句后刷新，创建成功。

图 1 创建数据库

2. 编写并运行SQL语句，在数据库estatedb中创建上述三个数据库表，并定义其完整性约束。

    （1）创建业主表（owner），并定义主键（primary key）以及默认值是否允许为空值。

图 2 创建owner表

（2）创建房产表（estate），并定义主键（primary key），使用关键词CHECK定义estatetype列取值范围为，使用关键词DEFAULT定义yearlength列默认值。

图 3 创建estate表

• 创建产权登记表（registration），定义主键（primary key）。

    在定义外键时，要注意遵循实际业务要求。

personid在owner表中做主键，在此表中做外键。若在父表中更新personid，则在子表中一并进行更新；若某人不再是业主，在父表中删除personid对应元组，在子表中一并删除。所以使用约束on delete cascade on update cascade。

estateid在estate表中做主键，在此表中做外键。若在父表中更新estateid，则在子表中一并进行更新；若房产楼盘被取缔，在父表中删除estateid对应元组，但在子表中保留原房产的estateid。所以使用约束on delete no action on update cascade。

图 4 创建registration表

3. 准备样本数据，编写并运行SQL语句，在上述三个数据库表中添加数据。

  （1）首先准备样本数据，owner表、estate表和registration表的数据依次如图5、6、7所示。

图 5 owner表数据

图 6 estate表数据

图 7 registration表数据

（2）如图8，将数据插入owner表。插入成功后查看数据，如图9。

图 8 数据插入owner表

图 9 查看owner表

（3）如图10，将数据插入estate表。插入成功后查看数据，如图11。

图 10 数据插入estate表

图 11 查看estate表

（4）如图12，将数据插入registration表。插入成功后查看数据，如图13。

图 12 数据插入registration表

图 13 查看registration表

4. 编写并运行SQL语句，查询类别为“商铺”的房产信息。使用SELECT语句查询单表estate中指定元组。查询结果如图14所示。

图 14 “商铺”房产信息

5. 编写并运行SQL语句，查询竣工日期为2023年12月1日后，产权面积90平米以上的“住宅”的房产信息。

使用SELECT语句查询单表estate中指定元组，使用WHERE子句

限制竣工日期、产权面积和房产类型，条件之间用AND连接。使用ORDER BY语句让结果按照竣工日期、产权面积升序排列。

查询结果如图15所示。

图 15 查询结果

6. 编写并运行SQL语句，查询个人在各地购买住宅2套以上的业主基本信息。

  使用连接查询关联多表，使用GROUP BY分组子句完成统计，使用聚合函数count()表示条件“住宅2套以上”。查询结果如图16。

图 16 查询结果

下面验证查找结果的正确性。

如图17，查找身份证号为“53312319850807002X”的业主名下拥有的住宅，数量为3，符合查询条件“住宅>2”。

图 17 验证结果

    类似地，查找身份证号为“513001198409040012”的业主名下拥有的住宅，数量为4，如图18，符合查询条件“住宅>2”。

图 18 验证结果

      综上，图17的查找结果正确。

7. 编写并运行SQL语句，查询个人在特定城市购买住宅2套以上的业主基本信息。

此处查询个人在成都市购买住宅2套以上的业主基本信息。查询结果如图19所示。

图 19 查询结果

    下面验证查询结果的正确性。如图20，查询该业主在成都市的住宅房产，数量为3。图19的查询结果正确。

图 20 验证结果

8. 编写并运行SQL语句，统计2023年度成都市的各类房产销售面积。

  使用SUM()聚合函数计算列数据的总和，使用WHERE子句限制

房产城市为“成都市”，竣工日期为“2023-01-01至2023-12-31”。查询结果如图21。

图 21 查询结果

9. 创建SQL视图，通过视图查询指定身份证号下，该业主的购置房产信息（“登记编号”，“房产名称”，“房产类型”，“产权面积”，“购买金额”，“购买日期”，“房产楼盘”，“房产城市”），并按日期降序排列。

  首先创建estateview视图。执行视图创建语句，使用ORDER

BY...DESC按购买日期降序排列，如图22。

图 22 视图创建语句

      视图创建结果如图23。

图 23 视图创建结果

      接下来在视图中查询身份证号为“53312319850807002X”的业主购置房产的信息，如图24。

图 24 查询结果

10. 创建SQL视图，分组统计2023年度各城市的住宅销售套数与总销售金额。

      创建estategroup视图。如图25，使用WHERE子句限制房产类型为“住宅”，销售日期为“2023年”。使用GROUP BY子句，使视图按“房产城市”输出。

图 25 estategroup视图创建

      使用SELECT语句查询视图，查询结果如图26所示。

图 26 estategroup查询结果

三、挑战性问题研究

教学管理数据库包含了学院表College（CollegeID，CollegeName）和教师表Teacher（TeacherID，TeacherName，CollegeID）。学院表College中已有20个学院信息，教师表Teacher中已有4000名教师信息。假定需要查询输出“软件学院”的教师名单，其查询SQL语句如下：

SELECT  A._CollegeName,  B._TeacherID,  B._TeacherName

FROM  College  AS  A,  Teacher  AS  B

WHERE  A._CollegeID=B._CollegeID AND A._{CollegeName=’软件学院’}

在执行处理该查询语句时，可以采用如下3种等价的关系运算表达式来解析该查询语句。

Q1=Π_{A.CollegeName,B.TeacherID,B.TeacherName}(σ_{A.CollegeID=B.CollegeID} L _{A.CollegeName=’软件学院’}(College×Teacher))

Q2=Π_{A.CollegeName,B.TeacherID,B.TeacherName}(σ_{A.CollegeName=’软件学院’}(College∞Teacher))

Q3=Π_{A.CollegeName,B.TeacherID,B.TeacherName}(Teacher∞(σ_{A.CollegeName=’软件学院’}(College)))

1. 研究分析Q1、Q2、Q3三种查询方案的执行代价，请选出一个最优查询方案。

解：

分析Q1：

（1）计算广义笛卡儿积。

• 首先在内存中尽可能多地装入College表的若干块，留出一块存放Teacher表的元组。在这里假设内存的一个块能装10个College元组或100个Teacher元组。
• 把Teacher中的每个元组和College中每个元组连接，连接后的元组装满一块后就写到中间文件上。在这里假设每次内存中能存放2块College元组和1块Teacher元组，则读取总块数为20/10＋(20/20) * (4000/100) =2+1×40=42块。其中，读College表2块，读Teacher表1遍，每遍40块。若每秒读写20块，则总计要花2.1s。
• 连接后的元组数为20×4×10³=8×10⁴。设每块能装10个元组，则写出这些块要用8×10⁴/10/20=400s。

（2）作选择操作

• 依次读入连接后的元组，按照选择条件“A._CollegeID=B._CollegeID∧  A._{CollegeName=’软件学院’}”，选取满足要求的元组。
• 假定内存处理时间忽略，读取中间文件花费的时间需8×10⁴/10/20=400s。
• 假设满足条件的元组有50个，均可放在内存。

（3）作投影操作

把第2步的结果在作投影输出，得到最终结果（内存处理时间忽略）。

Q1执行查询的总时间≈2.1+400+400≈800s。

 

分析Q2：

（1）计算广义笛卡儿积

• 类似地，读取42块，花费2.1s。
• 此处是自然连接，连接后的元组有4000个，写出这些数据需4000/10/20=20s，时间大大减少。

   （2）作选择操作

• 依次读入连接后的元组，按照选择条件“A.CollegeName=’软件学院’”，选取满足要求的元组。
• 假定内存处理时间忽略，读取中间文件花费的时间需4000/10/20=20s。
• 假设满足条件的元组有50个，均可放在内存。

   （3）作投影操作

把第2步的结果在作投影输出，得到最终结果（内存处理时间忽略）。

Q2执行查询的总时间≈2.1+20+20≈40s。

 

分析Q3:

（1）先对College表作选择运算，只需读一遍College表，存取2块，花费时间为0.1s。满足条件的元组50个，不必使用中间文件（假设内存够用）。

（2）读取Teacher表，把读入的Teacher元组和内存中的College元组作连接。也只需读一遍Teacher表，共40块，花费时间为2s。

（3）把连接结果投影输出。

Q3执行查询的总时间≈0.1+2≈2s。

 

结论：Q3的总时间明显小于其他两种方案。除此之外，Q3总读写数据块仅40块左右，Q1总读写数据块为10⁵个左右，Q2总读写数据块为10⁴个左右。

综上，Q3是最优查询方案。

 

2. 在以上最优查询方案基础上，对College表和Teacher表的主外键列创建了索引，并且CollegeName列也创建了索引，其查询代价又如何？

答：当创建索引时，系统依据定义时给出的列和索引类型在内存中建立一个数据结构，用于存储列值和列值对应的物理地址之间的映射关系。当使用查询语句时，PostgreSQL会首先查询索引，再使用这个索引中的物理地址直接访问数据。这样，查询就可以直接定位到有用数据的物理位置，不需要扫描整张表，从而可以大幅缩短查询时间。

当表数据量越来越大时查询速度会下降，在表的条件字段上使用索引，快速定位到可能满足条件的记录，不需要遍历所有记录。

资料显示，在几万条数据的情况之下，索引的优势并不明显。数据达到几十万条以后，索引的效果显著，能明显提升查询速度，数据量越大，索引越发重要。当数据量有了千万级别时，有无索引可导致性能相差千倍！

在这个例子中，对Collegename建立索引，考虑到College表只有20个元组，是一个很小的数目，所以是否使用索引对于查询速度影响不大。同时，建立索引会降低更新表的速度，会占用磁盘空间。从这方面来说，代价反而更大。在Teacher表与Collge表连接时也是如此。

所以在此例中，在数据量较小的情况下，建立索引且建立三个索引不一定会让查询速度提升，反而会影响表的更新速度，占用存储空间。不建立索引或许是更好的选择。