数据库PPT课后习题 - 第二章

PPT 课后习题

2.1.1 如何理解“实体”和“关系表”？它们分别是哪类模型的概念？

1. 实体：包含数据特征的事物对象在概念模型世界中的抽象名称（也可以理解为现实世界中的一个独立存在的对象或事物）

2. 关系表：即关系，是指 具有关系特征、用于存放实体数据的二维表。

3. 二者都是关系型数据库模型的概念。关系型数据库模型以关系表为核心，通过实体间的关系来组织和管理数据。

2.1.2 关系表具有哪些特性？

1. 唯一性：关系中的每个元组（每一行）都是唯一的。
2. 无序性：关系中的元组是无序的，可以按任意顺序存储和检索。
3. 二维性：关系是一个二维结构，表中每行存储实体的一个实例数据、每列包含实体的一项属性数据。
4. 原子性：表中单元格只能存储单个值，即属性值不能再细分。

2.1.3 在关系中，为什么需要确定主键或复合键？

1. 唯一标志关系表的元组。
2. 与关联表的外键建立联系，实现关系表之间的连接。
3. 通过主键约束，可以防止关系表中出现重复的行，确保数据唯一性。
4. 使用主键索引可以快速检索数据。
5. 数据库文件使用主键值来组织关系表的元组数据存储。

2.1.4 在PostgreSQL数据库中，如何定义关系表的主键、外键？

1. 主键：

   • CREATE TABLE student (
         student_id SERIAL PRIMARY KEY,
         student_name VARCHAR(100),
         student_age INTEGER
     );
     

     或者

   • ALTER TABLE students
     ADD CONSTRAINT pk_student_id_name
     PRIMARY KEY (student_id, student_name);
     

2. 外键：

   • CREATE TABLE courses (
         course_id SERIAL PRIMARY KEY,
         course_name VARCHAR(100),
         student_id INTEGER REFERENCES student(student_id)
     );
     

     或者

   • ALTER TABLE courses
     ADD CONSTRAINT fk_student_id
     FOREIGN KEY (student_id)
     REFERENCES student(student_id);
     

2.2 概念：

• 关系模型的四个集合运算：
  
  
  
  

• 关系模型的选择、投影运算：

• 关系的 $\theta$ 连接运算（包括外连接和内连接）、自然连接（ $\theta$ 是 ‘=’ 时）：

​ 注：自然连接符号是 $\bowtie$ ，它是一种内连接，将两个关系中符合匹配条件的元组构成结果集。

​ 当R、S 关系没有公共属性时，R $\bowtie$ S 等价于 R$\times$S 。

• 外连接：左外连接 ⟕ 、右外连接 ⟖ 、全外连接 ⊗（PPT有点问题）

  ​ 左外连接：左表主键全得有，右无对应NULL守

​ 右外连接：右表主键全得有，左无对应NULL守

​ 全外连接：左右主键全得有，若无对应NULL守

• 完整性约束规则：

  • 主键：不为空且唯一
  • 外键：若关系 R 中的外键 F 与关系 S 中的主键 K 相关联，则 R 中外键 F 的值必须与 S 中主键 K 的值一致。
  • 用户自定义完整性：如列的 数据类型与取值范围、缺省值？列是否允许取空值？列取值是否唯一？列之间的数据依赖性？

2.2.1 教师关系模式为 Teacher(ID,name,college,tel)，请给出查询“软件学院”的教师工号和姓名的关系运算表达式。

​ $${\Pi }_{ID,name}({\sigma }_{college{=}^{\prime }软件学{院}^{\prime }}(Teacher))$$

2.2.2

课表关系模式为 ：
​ CourseTable(year,semester，courseID,courseName,teacherName,address,timeSetion)。
​请给出查询2023年和2024年“数据库原理及应用”课程的开课基本信息（年度，学期，课程编号，课程名称，教师名称）的关系运算表达式。

​ $${\Pi }_{year,semester,courseID,courseName,teacherName}({\sigma }_{(year=2023\vee year=2024)\wedge courseName{=}^{\prime }数据库原理及应{用}^{\prime }}(CourseTable))$$

2.2.3

出版社关系模式为 Publisher(PubID,PubName,Addr)

图书关系模式为 Book(ISBN,BookName,Auther,PubID,PubDate,Price)。

请给出查询机械工业出版社的图书信息列表 (ISBN,BookName,Auther,PubID,PubDate,Price) 的关系运算表达式。

​ $${\Pi }_{ISBN,BookName,Auther,PubID,PubDate,Price}({\sigma }_{PubID{=}^{\prime }机械工业出版{社}^{\prime }}(Publisher\bowtie Book))$$

注：不需要额外指出例如 Publisher.PubID，而且自然连接也不需要指出 Publisher.PubID=Book.PubID，因为在进行自然连接时，系统会自动根据外键关系进行连接

2.3.1

1. 首先，创建 Register 表，包括两个外键 CoursePlanID 和 StudentID。这两个外键分别参照开课计划表（Plan）的主键 CoursePlanID 和学生表（Student）的主键 StudentID。
2. 确保 Plan 表中 CoursePlanID 列是主键，并且 Student 表中 StudentID 列也是主键。这样可以确保这两个列的唯一性。
3. 创建外键约束来确保参照完整性。在 Register 表中，CoursePlanID 和 StudentID 分别参照了 Plan 表和 Student 表的主键，通过外键约束来确保 Register 表中的这两列的值必须是 Plan 表和 Student 表中已存在的值。

即 通过主键和外键的参照完整性约束来回答

2.3.2

CREATE TABLE Course (
    CourseID SERIAL PRIMARY KEY,
    CourseName VARCHAR(20),
    -- 其他 Course 表的属性
    
    CoursePeriod SMALLINT,
    TestMethod VARCHAR(10) DEFAULT '闭卷',
    CONSTRAINT check_course_period CHECK (CoursePeriod BETWEEN 16 AND 64)
);

2.3.3

1. ${\Pi }_{ISBN,Title,Authors,Publisher}({\sigma }_{Publisher{=}^{\prime }机械工业出版{社}^{\prime }}(Book))$
2. ${\Pi }_{ISBN,Title,Authors,Publisher}({\sigma }_{LOAN.Date{=}^{\prime }2024-03-06^{\prime }}LOAN\bowtie BOOK)$
3. ${\Pi }_{ISBN,Title,Authors,Publisher}({\sigma }_{Age<20}READER\bowtie LOAN\bowtie BOOK)$

学堂在线补充

无

作业 2 第三题

教学管理数据库包含了学院表College（CollegeID，CollegeName），CollegeID是主键和教师表Teacher（TeacherID，TeacherName，CollegeID），TeacherID是主键，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={\Pi }_{A.CollegeName,B.TeacherID,B.TeacherName}({\sigma }_{A.CollegeID=B.CollegeID\wedge A.CollegeName{=}^{\prime }软件学{院}^{\prime }}(College\times Teacher))$$

$$Q2={\Pi }_{A.CollegeName,B.TeacherID,B.TeacherName}({\sigma }_{A.CollegeName{=}^{\prime }软件学{院}^{\prime }}(College\bowtie Teacher))$$

$$Q3={\Pi }_{A.CollegeName,B.TeacherID,B.TeacherName}(Teacher\bowtie ({\sigma }_{A.CollegeName{=}^{\prime }软件学{院}^{\prime }}(College)))$$

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

Q1: 笛卡尔积 $\to$ 选择 $\to$ 投影

$$Q1={\Pi }_{A.CollegeName,B.TeacherID,B.TeacherName}({\sigma }_{A.CollegeID=B.CollegeID\wedge A.CollegeName{=}^{\prime }软件学{院}^{\prime }}(College\times Teacher))$$

• 先进行笛卡尔积操作：将 College 表和 Teacher 表进行笛卡尔积，产生的结果为 20 * 4000 = 80000 行。
• 然后进行选择操作：筛选出 CollegeID 相等且 CollegeName 为 ‘软件学院’ 的行。
• 最后进行投影操作：选择需要输出的列。

Q2: 自然连接 $\to$ 选择 $\to$ 投影

$$Q2={\Pi }_{A.CollegeName,B.TeacherID,B.TeacherName}({\sigma }_{A.CollegeName{=}^{\prime }软件学{院}^{\prime }}(College\bowtie Teacher))$$

• 先进行自然连接操作：将 College 表和 Teacher 表进行自然连接，连接条件为 CollegeID 相等。
• 然后进行选择操作：筛选出 CollegeName 为 ‘软件学院’ 的行。
• 最后进行投影操作：选择需要输出的列。

Q3: 选择 $\to$ 自然连接 $\to$ 投影

$$Q3={\Pi }_{A.CollegeName,B.TeacherID,B.TeacherName}(Teacher\bowtie ({\sigma }_{A.CollegeName{=}^{\prime }软件学{院}^{\prime }}(College)))$$

• 先进行选择操作：在 College 表中筛选出 CollegeName 为 ‘软件学院’ 的行。

• 然后进行自然连接操作：将筛选出的 College 表与 Teacher 表进行自然连接，连接条件为 CollegeID 相等。

• 最后进行投影操作：选择需要输出的列。

• 分析与比较：

  • Q1 中的笛卡尔积操作会产生大量中间结果，而且筛选条件在后续操作中进行，不利于性能。（非常多余的列）
  • Q2 中的自然连接操作会包含所有列，会产生多余的列。（多余的列）
  • Q3 先进行选择操作，减少了不必要的数据量，然后再进行连接和投影操作，这可能是最优的选择。

  综上所述，Q3 的执行流程中 选择操作在最前面 ，可以减少不必要的计算开销，因此在执行效率上应该更优。

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

1. 主、外键列的索引：对 College 表的主键列 CollegeID 和 Teacher 表的外键列 CollegeID，以及 Teacher 表的主键列 TeacherID 创建索引，可以加速等值连接操作的执行速度。索引使得系统能够更快地定位到匹配的行，减少了对表的扫描次数。
2. CollegeName 列的索引：对 CollegeName 列创建索引可以加速选择操作的执行速度。由于 选择条件是基于 CollegeName 列进行筛选的 ，索引将允许系统更快地定位到符合条件的行，减少了搜索时间。

​ 综上所述，对 College 表和 Teacher 表的主外键列以及 CollegeName 列创建索引后，最优查询方案 Q3 的执行效率会进一步提高，查询代价会进一步降低。这是因为索引的存在可以减少数据库的扫描次数和搜索时间，提高了查询的效率和性能。