第三讲第2章关系数据库.ppt

* * * * * * * * * * * * End of Chapter 2 Figure 2.01 Figure 2.02 Figure 2.03 Figure 2.04 Figure 2.05 Figure 2.06 Figure 2.07 Figure 2.10 Figure 2.11 Figure 2.12 Figure 2.13 第二章 关系数据库--- 思考与作业 本章练习： 本章思考题： 1、关系模型的组成？ 2、自然连接与条件连接的区别？ 1.如果关系R和S没有共同的列属性,根据定义说明表R×S等于表R∞S。 2.对CAP数据库，用关系代数完成下列查询。 1）找出顾客、代理商和商品都在同一个城市的三元组（cid,aid,pid）。 2）找出顾客、代理商和商品两两不在同一个城市的三元组（cid,aid,pid）。 3）列出所有在同一个城市代理商的aid对。 4）找出折扣率最大和最小的顾客cid。 5）取出销售过所有曾被顾客c002订购过的商品的代理商的名字。 6）找出只从一家代理商处订购过商品的顾客cid。 * * * * * * * * * * * * * * * * * * * * * Cartesian-Product Operation – Example Relations r, s: r x s: Cartesian-Product Operation Notation r x s Defined as: r x s = {t q | t ? r and q ? s} Assume that attributes of r(R) and s(S) are disjoint. (That is, R ? S = ?). If attributes of r(R) and s(S) are not disjoint, then renaming must be used. Composition of Operations Can build expressions using multiple operations Example: ?A=C(r x s) r x s ?A=C(r x s) Rename Operation Allows us to name, and therefore to refer to, the results of relational-algebra expressions. Allows us to refer to a relation by more than one name. Example: ? x (E) returns the expression E under the name X If a relational-algebra expression E has arity n, then returns the result of expression E under the name X, and with the attributes renamed to A1 , A2 , …., An . Example Query Find the largest salary in the university Step 1: find instructor salaries that are less than some other instructor salary (i.e. not maximum) using a copy of instructor under a new name d ?instructor.salary (? instructor.salary d,salary  (instructor x ?d (instructor))) Step 2: Find the largest salary ?salary (instructor) –  ?instructor.salary (? instructor.salary d,salary  (instructor x ?d (instructor))) Example Queries Find the names of all instructors in the Physics department, along with t