PPT 课后习题
2.1.1 如何理解“实体”和“关系表”?它们分别是哪类模型的概念?
-
实体:包含数据特征的事物对象在概念模型世界中的抽象名称(也可以理解为现实世界中的一个独立存在的对象或事物)
-
关系表:即关系,是指 具有关系特征、用于存放实体数据的二维表。
-
二者都是关系型数据库模型的概念。关系型数据库模型以关系表为核心,通过实体间的关系来组织和管理数据。
2.1.2 关系表具有哪些特性?
- 唯一性:关系中的每个元组(每一行)都是唯一的。
- 无序性:关系中的元组是无序的,可以按任意顺序存储和检索。
- 二维性:关系是一个二维结构,表中每行存储实体的一个实例数据、每列包含实体的一项属性数据。
- 原子性:表中单元格只能存储单个值,即属性值不能再细分。
2.1.3 在关系中,为什么需要确定主键或复合键?
- 唯一标志关系表的元组。
- 与关联表的外键建立联系,实现关系表之间的连接。
- 通过主键约束,可以防止关系表中出现重复的行,确保数据唯一性。
- 使用主键索引可以快速检索数据。
- 数据库文件使用主键值来组织关系表的元组数据存储。
2.1.4 在PostgreSQL数据库中,如何定义关系表的主键、外键?
-
主键:
-
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);
-
-
外键:
-
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 θ 是 ‘=’ 时):
注:自然连接符号是 ⋈ \Join ⋈ ,它是一种内连接,将两个关系中符合匹配条件的元组构成结果集。
当R、S 关系没有公共属性时,R ⋈ \Join ⋈ S 等价于 R × \times ×S 。
-
外连接:左外连接 ⟕ 、右外连接 ⟖ 、全外连接 ⊗(PPT有点问题)
左外连接:左表主键全得有,右无对应NULL守
右外连接:右表主键全得有,左无对应NULL守
全外连接:左右主键全得有,若无对应NULL守
-
完整性约束规则:
- 主键:不为空且唯一
- 外键:若关系 R 中的外键 F 与关系 S 中的主键 K 相关联,则 R 中外键 F 的值必须与 S 中主键 K 的值一致。
- 用户自定义完整性:如列的 数据类型与取值范围、缺省值?列是否允许取空值?列取值是否唯一?列之间的数据依赖性?
2.2.1 教师关系模式为 Teacher(ID,name,college,tel),请给出查询“软件学院”的教师工号和姓名的关系运算表达式。
Π I D , n a m e ( σ c o l l e g e = ′ 软件学 院 ′ ( T e a c h e r ) ) \Pi_{ID,name}(\sigma_{college='软件学院'}(Teacher)) ΠID,name(σcollege=′软件学院′(Teacher))
2.2.2
课表关系模式为 :
CourseTable(year,semester,courseID,courseName,teacherName,address,timeSetion)。
请给出查询2023年和2024年“数据库原理及应用”课程的开课基本信息(年度,学期,课程编号,课程名称,教师名称)的关系运算表达式。
Π y e a r , s e m e s t e r , c o u r s e I D , c o u r s e N a m e , t e a c h e r N a m e ( σ ( y e a r = 2023 ∨ y e a r = 2024 ) ∧ c o u r s e N a m e = ′ 数据库原理及应 用 ′ ( C o u r s e T a b l e ) ) \Pi_{year,semester,courseID,courseName,teacherName}(\sigma_{(year=2023 \vee year=2024)\wedge courseName='数据库原理及应用'}(CourseTable)) Πyear,semester,courseID,courseName,teacherName(σ(year=2023∨year=2024)∧courseName=′数据库原理及应用′(CourseTable))
2.2.3
出版社关系模式为 Publisher(PubID,PubName,Addr)
图书关系模式为 Book(ISBN,BookName,Auther,PubID,PubDate,Price)。
请给出查询机械工业出版社的图书信息列表 (ISBN,BookName,Auther,PubID,PubDate,Price) 的关系运算表达式。
Π I S B N , B o o k N a m e , A u t h e r , P u b I D , P u b D a t e , P r i c e ( σ P u b I D = ′ 机械工业出版 社 ′ ( P u b l i s h e r ⋈ B o o k ) ) \Pi_{ISBN,BookName,Auther,PubID,PubDate,Price}(\sigma_{PubID='机械工业出版社'}(Publisher\bowtie Book)) ΠISBN,BookName,Auther,PubID,PubDate,Price(σPubID=′机械工业出版社′(Publisher⋈Book))
注:不需要额外指出例如 Publisher.PubID,而且自然连接也不需要指出 Publisher.PubID=Book.PubID,因为在进行自然连接时,系统会自动根据外键关系进行连接
2.3.1
- 首先,创建 Register 表,包括两个外键 CoursePlanID 和 StudentID。这两个外键分别参照开课计划表(Plan)的主键 CoursePlanID 和学生表(Student)的主键 StudentID。
- 确保 Plan 表中 CoursePlanID 列是主键,并且 Student 表中 StudentID 列也是主键。这样可以确保这两个列的唯一性。
- 创建外键约束来确保参照完整性。在 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
- Π I S B N , T i t l e , A u t h o r s , P u b l i s h e r ( σ P u b l i s h e r = ′ 机械工业出版 社 ′ ( B o o k ) ) \Pi_{ISBN,Title,Authors,Publisher}(\sigma_{Publisher='机械工业出版社'}(Book)) ΠISBN,Title,Authors,Publisher(σPublisher=′机械工业出版社′(Book))
- Π I S B N , T i t l e , A u t h o r s , P u b l i s h e r ( σ L O A N . D a t e = ′ 2024 − 03 − 0 6 ′ L O A N ⋈ B O O K ) \Pi_{ISBN,Title,Authors,Publisher}(\sigma_{LOAN.Date='2024-03-06'}LOAN \bowtie BOOK) ΠISBN,Title,Authors,Publisher(σLOAN.Date=′2024−03−06′LOAN⋈BOOK)
- Π I S B N , T i t l e , A u t h o r s , P u b l i s h e r ( σ A g e < 20 R E A D E R ⋈ L O A N ⋈ B O O K ) \Pi_{ISBN,Title,Authors,Publisher}(\sigma_{Age<20}READER \bowtie LOAN \bowtie BOOK) ΠISBN,Title,Authors,Publisher(σAge<20READER⋈LOAN⋈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种等价的关系运算表达式来解析该查询语句。
Q 1 = Π A . C o l l e g e N a m e , B . T e a c h e r I D , B . T e a c h e r N a m e ( σ A . C o l l e g e I D = B . C o l l e g e I D ∧ A . C o l l e g e N a m e = ′ 软件学 院 ′ ( C o l l e g e × T e a c h e r ) ) Q1=\Pi_{A.CollegeName,B.TeacherID,B.TeacherName}(\sigma_{A.CollegeID=B.CollegeID \land A.CollegeName='软件学院'}(College \times Teacher)) Q1=ΠA.CollegeName,B.TeacherID,B.TeacherName(σA.CollegeID=B.CollegeID∧A.CollegeName=′软件学院′(College×Teacher))
Q 2 = Π A . C o l l e g e N a m e , B . T e a c h e r I D , B . T e a c h e r N a m e ( σ A . C o l l e g e N a m e = ′ 软件学 院 ′ ( C o l l e g e ⋈ T e a c h e r ) ) Q2=\Pi_{A.CollegeName,B.TeacherID,B.TeacherName}(\sigma_{A.CollegeName='软件学院'}(College \bowtie Teacher)) Q2=ΠA.CollegeName,B.TeacherID,B.TeacherName(σA.CollegeName=′软件学院′(College⋈Teacher))
Q 3 = Π A . C o l l e g e N a m e , B . T e a c h e r I D , B . T e a c h e r N a m e ( T e a c h e r ⋈ ( σ A . C o l l e g e N a m e = ′ 软件学 院 ′ ( C o l l e g e ) ) ) Q3=\Pi_{A.CollegeName,B.TeacherID,B.TeacherName}(Teacher \bowtie (\sigma_{A.CollegeName='软件学院'}(College))) Q3=ΠA.CollegeName,B.TeacherID,B.TeacherName(Teacher⋈(σA.CollegeName=′软件学院′(College)))
1)研究分析Q1、Q2、Q3三种查询方案的执行代价,请选出一个最优查询方案。
Q1: 笛卡尔积 → \rarr → 选择 → \rarr → 投影
Q 1 = Π A . C o l l e g e N a m e , B . T e a c h e r I D , B . T e a c h e r N a m e ( σ A . C o l l e g e I D = B . C o l l e g e I D ∧ A . C o l l e g e N a m e = ′ 软件学 院 ′ ( C o l l e g e × T e a c h e r ) ) Q1=\Pi_{A.CollegeName,B.TeacherID,B.TeacherName}(\sigma_{A.CollegeID=B.CollegeID \land A.CollegeName='软件学院'}(College \times Teacher)) Q1=ΠA.CollegeName,B.TeacherID,B.TeacherName(σA.CollegeID=B.CollegeID∧A.CollegeName=′软件学院′(College×Teacher))
- 先进行笛卡尔积操作:将 College 表和 Teacher 表进行笛卡尔积,产生的结果为 20 * 4000 = 80000 行。
- 然后进行选择操作:筛选出 CollegeID 相等且 CollegeName 为 ‘软件学院’ 的行。
- 最后进行投影操作:选择需要输出的列。
Q2: 自然连接 → \rarr → 选择 → \rarr → 投影
Q 2 = Π A . C o l l e g e N a m e , B . T e a c h e r I D , B . T e a c h e r N a m e ( σ A . C o l l e g e N a m e = ′ 软件学 院 ′ ( C o l l e g e ⋈ T e a c h e r ) ) Q2=\Pi_{A.CollegeName,B.TeacherID,B.TeacherName}(\sigma_{A.CollegeName='软件学院'}(College \bowtie Teacher)) Q2=ΠA.CollegeName,B.TeacherID,B.TeacherName(σA.CollegeName=′软件学院′(College⋈Teacher))
- 先进行自然连接操作:将 College 表和 Teacher 表进行自然连接,连接条件为 CollegeID 相等。
- 然后进行选择操作:筛选出 CollegeName 为 ‘软件学院’ 的行。
- 最后进行投影操作:选择需要输出的列。
Q3: 选择 → \rarr → 自然连接 → \rarr → 投影
Q 3 = Π A . C o l l e g e N a m e , B . T e a c h e r I D , B . T e a c h e r N a m e ( T e a c h e r ⋈ ( σ A . C o l l e g e N a m e = ′ 软件学 院 ′ ( C o l l e g e ) ) ) Q3=\Pi_{A.CollegeName,B.TeacherID,B.TeacherName}(Teacher \bowtie (\sigma_{A.CollegeName='软件学院'}(College))) Q3=ΠA.CollegeName,B.TeacherID,B.TeacherName(Teacher⋈(σA.CollegeName=′软件学院′(College)))
-
先进行选择操作:在 College 表中筛选出 CollegeName 为 ‘软件学院’ 的行。
-
然后进行自然连接操作:将筛选出的 College 表与 Teacher 表进行自然连接,连接条件为 CollegeID 相等。
-
最后进行投影操作:选择需要输出的列。
-
分析与比较:
- Q1 中的笛卡尔积操作会产生大量中间结果,而且筛选条件在后续操作中进行,不利于性能。(非常多余的列)
- Q2 中的自然连接操作会包含所有列,会产生多余的列。(多余的列)
- Q3 先进行选择操作,减少了不必要的数据量,然后再进行连接和投影操作,这可能是最优的选择。
综上所述,Q3 的执行流程中 选择操作在最前面 ,可以减少不必要的计算开销,因此在执行效率上应该更优。
2)在以上最优查询方案基础上,对College表和Teacher表的主外键列创建了索引,并且CollegeName列也创建了索引,其查询代价又如何?
- 主、外键列的索引:对 College 表的主键列 CollegeID 和 Teacher 表的外键列 CollegeID,以及 Teacher 表的主键列 TeacherID 创建索引,可以加速等值连接操作的执行速度。索引使得系统能够更快地定位到匹配的行,减少了对表的扫描次数。
- CollegeName 列的索引:对 CollegeName 列创建索引可以加速选择操作的执行速度。由于 选择条件是基于 CollegeName 列进行筛选的 ,索引将允许系统更快地定位到符合条件的行,减少了搜索时间。
综上所述,对 College 表和 Teacher 表的主外键列以及 CollegeName 列创建索引后,最优查询方案 Q3 的执行效率会进一步提高,查询代价会进一步降低。这是因为索引的存在可以减少数据库的扫描次数和搜索时间,提高了查询的效率和性能。