期末题型

判断题+7个大题

郭哥非常好啊，直接提出了：“基本都见过的题，课件上的例题”

所以我这里考虑把PPT上出现过的Examples都摘出来。

可以划分一下内容层面

• Relation Model(关系模型)，关系代数之类的
• SQL语句相关的应用，Chapter 3 - 5都是在讲这个
• E-R模型E-R Model
• 关系模型，根据E-R转关系模型，正则化，3NF,BCNF之类
• 索引Index
• 查询优化
• 事务：Conflict Serializability(如何识别？如何画前趋图),Recoverability(如何识别是否可恢复，是否级联回滚)
• 并发控制

这里应该摘出来PPT中所有的Examples和例题，如果有缺漏可以找我补:D

Chapter 2 Relational Model

Chapter 3 SQL

P24

Q:Find **names of all instructors ** who have taught some course and the course_id

A:

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID

Q:Find names of all instructors in Art department who have taught some course and the course_id

A:

select name, course_id
from instructor, teaches
where instructor.dept_name = "Art"
and instructor.ID = teaches.ID

• 子句from 包括多个关系表
  • 要在where子句中加入连接条件
  • 防止出现costly 多表笛卡尔积操作
• 频繁执行SQL查询，from子句表的个数不要过多
  • 避免耗时费力的多表连接操作
  • 不要超过4个表
  • 如果频繁执行查询设计$N\ge 4$张表，将这 N张表的数据进行合并

Chapter 4 Intermediate SQL

Not Found

Chapter 5 Advanced SQL

DBAS = users + application programs + DBMS + DB Triggers例题

Chapter 6 Database Design Using the E-R Model

答案： E-R diagrams

The E-R diagrams is then reduced to some relational tables

• For entity set Customer and its multi-valued attribute ship-to addresses, there are two tables

  • customer=(C-number, c-name, discount, balance)
  • ship-to-addresses = (C-number, ship-to-addresses)

• For entity set Order and its composite attribute date which should be decomposed , there is a table

  • order1=(O-number, ship-to-address, year, month, day) 复合属性需要解开

• For entity set Product, there is a table

  • product=(P-number, p-name, plant, quantity-on-hand)

• For relationship set make, there are no descriptive attributes, mapping cardinality from Customer to Order is one-to-many, and Order totally participate make. So, make should not be reduced into a separate table, and can be represented by adding the primary key C-number of Customer , that is C-number into the table order

  • order2=(O-number, C-number, ship-to-address, year, month, day)
  • note: the primary key C-number is already in the table order

Chapter 7 Relational Database Design：Schema Normalization

本章的习题类型

Page25,这是函数依赖相关的知识

Whenever two tuples t1 and t2 agree on the attributes $\alpha$,also agree on the attributes $\beta$ $t_1[\alpha ]=t_2[\alpha ]\Rightarrow t_1[\beta ]=t_2[\beta ]$ 表现为数据项的相等，但是本质上是数据之间语义的关系，比如身份证号确定一个人

A：t2和t3的属性A相等，但是对应的属性B不相等，故A不能确定B B：AC的t2和t3相等，但是对应的B属性不相等 C：BC没有相等的行 D：B的t1和t3相等，但是对应的C不相等，故$B\nrightarrow C$

Page 39, Lossless Decomposition check

A decomposition of R into $R_1$ and $R_2$ is lossless join if and only if at least one of the following dependencies is in $F^{+}$:

• $R_1\cap R_2\to R_1$

• $R_1\cap R_2\to R_2$

• If only $R_1\cap R_2\to R_1$ holds, then $R_1\cap R_2$ is the primary key of $R_1$, and the foreign key of $R_2$.

• It is a sufficient condition

不同子表间存在外键关联

Page46, 2NF Examples

Page49 BCNF Example Page50, Decomposing a schema into BCNF Not always possible to achieve both BCNF and dependency preservation

转为BCNF范式不一定能保持所有的函数依赖

Page58, 3NF example

Page65, SQL for FD Judgment example

Page 72, Example of $F^{+}$

Page 76, Example of Attributes Set Closure Closure of Attributes Sets

Page 94, Computing a Cannonical Cover Testing for Dependency Preservation的相关例子比较简单，只需要从每个被分解出的Schema的FD去尝试推导原Schema的FD即可，即$F^{\prime +}$是否等于$F^{+}$

Page113, BCNF decomposition

Page 127, 3NF decomposition Example

Page 135 求候选键

Chapter13 Data Storage Structures

Chapter 14 Indexing

重点应该是这几张图 例题

Chapter 15 Query Processing

Not Found

Chapter 16 Query Optimization

Join Order 例子

Heuristic Optimization

例子3 例子4

Chapter 17 Transactions

Chapter 18

Only at page 69