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

一、分析说明题

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

        

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

1)数据结构清晰：SQL数据库采用关系模型来组织数据，可以将数据分成多个表，每个表包含多个列，数据之间的关系清晰可见，易于管理和维护。

2)数据一致性：SQL数据库通过事务机制来保证数据的一致性，即要么全部执行，要么全部回滚，可以避免出现数据不一致的情况。

3)数据安全性：SQL数据库可以通过用户权限控制来限制用户对数据的访问权限，可以保证数据的安全性。

4)高可靠性：SQL数据库采用ACID事务模型，可以保证数据的可靠性，即原子性、一致性、隔离性和持久性。

        (2) 对于我国发展数据库技术，SQL关系数据库的成功经验可以借鉴如下：

1)技术创新：SQL关系数据库的发展离不开技术创新，需要不断地推陈出新，不断改进和完善技术，以满足不同需求和场景的应用。

2)技术标准化：SQL关系数据库的发展也得益于技术标准化，不同厂商采用相同的技术标准，可以保证数据库之间的互操作性，促进数据库技术的发展。

3)大国工匠精神：SQL关系数据库的成功也与工匠精神密不可分，需要不断地追求技术的卓越和品质的高超，以满足用户的需求和期望。

(3)技术创新、技术标准化和大国工匠精神对我国数据库软件技术发展都具有重要的作用，可以促进我国数据库技术的发展和壮大，提高我国数据库软件的市场竞争力。同时，也需要注重培养人才，加强技术研发，推动数据库技术与行业应用的深度融合，实现数据库技术的升级和转型。

二、SQL编程动手实践题

        针对一个房产信息管理系统开发，其设计的数据库EstateDB包括业主表（Owner）、房产表（Estate）、产权登记表（Registration）。各数据表的字段结构定义见表3-14到表3-16所示。

1. 编写并运行SQL语句，创建数据库EstateDB，结果如图2-1所示。

 

图2-1 创建数据库

1. 编写并运行SQL语句，在数据库EstateDB中创建业主表（Owner）、房产表（Estate）、产权登记表（Registration）三个数据库表，并定义其完整性约束，创建结果如图2-2所示。

 

图2-2 创建表及其完整性约束

1. 准备样本数据，编写并运行SQL语句，在上述三个数据库表中添加数据，插入样本数据过程如图2-3所示。

 

图2-3 插入样本数据

查询插入的业主表样本数据，如图2-4所示。

 

图2-4 查询业主表数据信息

查询插入的房产表样本数据，如图2-5所示。

 

图2-5 查询房产表数据信息

查询插入的产权登记表样本数据，如图2-6所示。

 

图2-6 查询产权登记表数据信息

1. 编写并运行SQL语句，查询类别为“商铺”的房产信息，查询结果如图2-7所示。

 

图2-7 查询商铺类型数据信息

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

 

图2-8 查询业90平米以上及竣工在2023-12-1房产数据信息

1. 编写并运行SQL语句，查询个人在各地购买住宅2套及以上的业主基本信息，查询结果如图2-9所示。

 

图2-9 查询个人在各地购买2套及以上业主数据信息

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

查询在成都市购买住宅2套以上的业主基本信息，结果如图2-10所示。

图2-10 查询个人在成都市购买2套及以上业主数据信息

查询在青岛市购买住宅2套及以上的业主基本信息，结果如图2-11所示。

 

 

图2-11 查询个人在青岛市购买2套及以上业主数据信息

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

查询2023年度成都市的各类房产销售面积，结果如图2-12所示。

 

图2-12 查询2023年成都市各类房产销售数据信息

查询2023年度青岛市的各类房产销售面积，结果如图2-13所示。

 

图2-13 查询2023年青岛市各类房产销售数据信息

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

 

图2-14 创建业主购置房产视图

对上述创建的视图进行检验，例如，查询身份证号为“371121199706126985”的业主张三购置房产信息，查询结果如图2-15所示。

 

图2-15 查询业主张三购置房产相关信息

1. 创建SQL视图，分组统计2023年度各城市的住宅销售套数与总销售金额，创建结果如图2-16所示。

 

图2-16创建2023年房产销售相关信息视图

查询创建视图结果，如图2-17所示。

 

                                        图2-17 查询2023房产销售视图相关数据

三、挑战性问题研究

教学管理数据库包含了学院表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三种查询方案的执行代价，请选出一个最优查询方案。
2. 在以上最优查询方案基础上，对College表和Teacher表的主外键列创建了索引，并且CollegeName列也创建了索引，其查询代价又如何？

        

1）Q1、Q2、Q3三种查询方案的执行代价分析如下：

Q1方案：

首先进行笛卡尔积操作，将College表和Teacher表进行连接，得到的结果集再进行筛选，选出CollegeName为“软件学院”的记录，最后进行投影操作，输出需要的三个属性。代价包括两个关系表的I/O代价和连接代价，连接代价为O(College×Teacher)，筛选代价为O(College×Teacher)，投影代价为O(College×Teacher)。

Q2方案：

首先进行连接操作，连接College表和Teacher表，得到结果集后再进行筛选操作，选出CollegeName为“软件学院”的记录，最后进行投影操作，输出需要的三个属性。代价包括两个关系表的I/O代价和连接代价，连接代价为O(Teacher)，筛选代价为O(Teacher)，投影代价为O(Teacher)。

Q3方案：

首先进行筛选操作，选出CollegeName为“软件学院”的记录，然后进行连接操作，将结果集连接到Teacher表，最后进行投影操作，输出需要的三个属性。代价包括两个关系表的I/O代价和连接代价，连接代价为O(Teacher)，筛选代价为O(College)，投影代价为O(Teacher)。

综合分析三种查询方案，Q3方案代价最小，因此选用Q3方案作为最优查询方案。

2）在以上最优查询方案基础上，对College表和Teacher表的主外键列创建了索引，并且CollegeName列也创建了索引，查询代价会有所降低。通过索引查询可以快速定位到符合条件的记录，减少了全表扫描的操作。尤其是对CollegeName列的索引，可以避免对整个表进行自然连接操作，大幅提升查询效率。