数据库系统概念中文版课件rel-model100p

1、2022/9/131/100Book :数据库概念Ver.5 by A. Silberschatz 等Chapter 2: Relational Model12022/9/132/100Chapter 2: Relational ModelStructure of Relational Databases 关系库结构Fundamental Relational-Algebra-Operations 基本关系代数操作Additional Relational-Algebra-Operations 增加的本关系代数操作Extended Relational-Algebra-Operations 扩

2、展的关系代数操作Null Values 空值Modification of the Database 数据库修改2022/9/133/100Example of a Relation cp25 账号 支行 余额2022/9/134/1002.1 Basic Structure 基本结构 cp24属性 A1,A2,.An 是 抽象的符号D-Domain, 定义域， Di=Dom(Ai),至少含两个元素的集合叉积 直观概念： 吨 X 公里， 架 X 次， 地区 X 时间，风X马X牛定义 设Ai为属性，Di=Dom(Ai), i=1,2,n.则 模式R(A1,A2,.An)上的关系R是D1 x D2

3、 x x Dn的一个子集合。 R的元素称为元组 tuple 。 等价说法： a relation is a set of n-tuples (a1, a2, , an) where，ai Di2022/9/135/100Basic Structure 基本结构 cp24属性 A1,A2,.An 是 抽象非符号D-Domain, 定义域， Di=Dom(Ai),至少含两个元素的集合叉积 直观概念： 吨 X 公里， 架 X 次， 地区 X 时间定义 设Ai为属性，Di=Dom(Ai), i=1,2,n.则 模式R(A1,A2,.An)的关系R是D1 x D2 x x Dn的一个子集合。 R的元素称

4、为元组 tuple 。 等价说法： a relation is a set of n-tuples (a1, a2, , an) where，ai Di2022/9/136/100Basic Structure 基本结构 cp24Example: ifcustomer-name = Jones, Smith, Curry, Lindsaycustomer-street = Main, North, Parkcustomer-city = Harrison, Rye, PittsfieldThen r = (Jones, Main, Harrison), (Smith, North, Rye),

5、 (Curry, North, Rye), (Lindsay, Park, Pittsfield) is a relation over customer-name x customer-street x customer-city三个（定义）域元组元素 取自三个域2022/9/137/100Attribute Types 属性类型 ep24Each attribute of a relation has a name The set of allowed values for each attribute is called the domain of the attributeAttrib

6、ute values are (normally) required to be atomic, that is, indivisible E.g. multivalued attribute values are not atomicE.g. composite attribute values are not atomic元组元素 取自三个域属性是原子，不允许大字段套小字段定义域，允许的值的集合属性有名称2022/9/138/100Attribute Types 属性类型 ep24Each attribute of a relation has a name The set of allo

7、wed values for each attribute is called the domain of the attributeAttribute values are (normally) required to be atomic, that is, indivisible E.g. multivalued attribute values are not atomicE.g. composite attribute values are not atomic元组元素 取自三个域属性是原子，不允许大字段套小字段定义域，允许的值的集合2022/9/139/100Attribute Ty

8、pes 属性类型 cp24Each attribute of a relation has a nameThe set of allowed values for each attribute is called the domain of the attributeAttribute values are (normally) required to be atomic, that is, indivisibleE.g. multivalued attribute values are not atomicE.g. composite attribute values are not ato

9、micspecial null is a member of every domain空值符号The null value causes complications in the definition of many operations we shall ignore the effect of null values in our main presentation and consider their effect later空值在后面章节讨论2022/9/1310/100Relation Schema 关系模式 cp24A1, A2, , An are attributes R = (

10、A1, A2, , An ) is a relation schemaE.g. Customer-schema = (customer-name, customer-street, customer-city)r(R) is a relation on the relation schema RE.g.customer (Customer-schema) 2022/9/1311/100Relation Schema 关系模式 cp24A1, A2, , An are attributes R = (A1, A2, , An ) is a relation schemaE.g. Customer

11、-schema = (customer-name, customer-street, customer-city)r(R) is a relation on the relation schema RE.g.customer (Customer-schema) 模式的数学描述，模式的实现：Create Table语句2022/9/1312/100Relation Schema 关系模式 cp24A1, A2, , An are attributes R = (A1, A2, , An ) is a relation schemaE.g. Customer-schema = (customer-

12、name, customer-street, customer-city)r(R) is a relation on the relation schema RE.g.customer (Customer-schema) 模式的实现：Create Table语句2022/9/1313/100Relation Schema 关系模式 cp24 小说的模式：复仇式， 奇遇式，模式下的 实例：XX恩仇记 哈克历险记 模式化的电影，容易写，容易演，容易猜，容易用数据库管理： 模式化电影 （片名，类型， 起，承，转，合，收尾） 模式化数据 好管理 模式化的论文 好写 （引言-相关工作-概念-主要工作-实

13、验-小结） 模式下实际的例子生活中的模式2022/9/1314/100Relation Instance 关系实例： cp24The current values (relation instance) of a relation are specified by a table An element t of r is a tuple, represented by a row in a tableJonesSmithCurryLindsaycustomer-nameMainNorthNorthParkcustomer-streetHarrisonRyeRyePittsfieldcustom

14、er-cityCustomer 顾客属性attributesTuples元组模式2022/9/1315/100Relations are Unordered 关系中元组次序无关紧要 cp25 Order of tuples is irrelevant (tuples may be stored in an arbitrary order) E.g. account relation with unordered tuples集合元素次序无关紧要2022/9/1316/100Database cp 25例如企业信息由多个关系保存账号account : stores information abo

15、ut account客户customer : stores information about customers银行 bank(account-number, balance, customer-name, .).数据库= 一组关系2022/9/1317/100The customer Relation 客户表 cp262022/9/1318/100The depositor Relation cp26 存款人姓名， 账号2022/9/1319/100E-R Diagram for the Banking Enterprise银行 ER 图 cp141 （V5移动到第6章）2022/9/13

16、20/100Keys 码 关键字 cp27 Let K RK is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) by “possible r” we mean a relation r that could exist in the enterprise we are modeling.Example: customer-name, customer-street and customer-name are both superk

17、eys of Customer, if no two customers can possibly have the same name.K is a candidate key if K is minimalExample: customer-name is a candidate key for Customer, since it is a superkey assuming no two customers can possibly have the same name), and no subset of it is a superkey.够资格可能有多余够资格，无多余与实体关键字有

18、区别2022/9/1321/100Keys 码 cp27Let K RK is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) by “possible r” we mean a relation r that could exist in the enterprise we are modeling.Example: customer-name, customer-street and customer-name are both

19、superkeys of Customer, if no two customers can possibly have the same name.K is a candidate key if K is minimalExample: customer-name is a candidate key for Customer, since it is a superkey assuming no two customers can possibly have the same name), and no subset of it is a superkey.够资格可能有多余够资格，无多余与

20、实体关键字有区别2022/9/1322/100A relation schema may have an attribute that corresponds to the primary key of another relation. The attribute is called a foreign key.Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relati

21、on.Schema diagram下页有例子Foreign Keys CP28在外关系中作关键字比喻：1 派出机构主官，2 派往殖民地的 总督） .殖民地被参照关系 ，被参照-被统治2022/9/1323/100Schema Diagram for the Banking Enterprise 银行模式图 CP28关键字箭头表外关键字，被指被参照，被统治它在本关系中地位不高，在account中却是关键字2022/9/1324/100Query Languages 查询语言 cp29Language in which user requests information from the data

22、base. Categories of languages 语言类型Procedural 过程式 how to donon-procedural 非过程式 what we want“Pure” languages: 纯粹语言Relational Algebra 纯关系代数Tuple Relational Calculus 纯元组关系代数Domain Relational Calculus 纯域演算Pure languages form underlying basis of query languages that people use.cpcv 2022/9/1325/1002.2 关系代数

23、基本运算 查询语言 cp29Language in which user requests information from the database. Categories of languages 语言类型Procedural 过程式 how to donon-procedural 非过程式 what we want“Pure” languages: 纯粹语言Relational Algebra 纯关系代数Tuple Relational Calculus 纯元组关系代数Domain Relational Calculus 纯域演算Pure languages form underlyin

24、g basis of query languages that people use. 2022/9/1326/100Relational Algebra 关系代数 cp29Procedural language 过程语言,how to doSix basic operators 6个运算Select 选 （一元运算）Project 投Union 并set difference 差 -Cartesian product 迪卡积 XRename 更名 （一元运算） The operators take two or more relations as inputs and give a new

25、relation as a result.二元运算2022/9/1327/100Relational Algebra 关系代数 cp29Procedural language 过程语言,how to doSix basic operators 6个运算Select 选 （一元运算）Project 投Union 并set difference 差Cartesian product 迪卡积Rename 更名 （一元运算）The operators take two or more relations as inputs and give a new relation as a result.二元运

26、算2022/9/1328/100Select Operation Example 自学 或快速复习 cp29 Relation rABC=B D 5 (r)ABCD123710选择2022/9/1329/100Select Operation 自学或快速复习 cp29 Notation: p(r)p is called the selection predicateDefined as: p(r) = t | t r and p(t)Where p is a formula in propositional calculus consisting of terms c

27、onnected by : (and), (or), (not)Each term is one of:op or where op is one of: =, , , . 1200 (loan)在上例结果中进一步查处贷款号Find the loan number for each loan of an amount greater than $1200 loan-number (amount 1200 (loan)2022/9/1342/100Example Queries 查询例 v4Find all loans of over $1200 查出贷款超过1200的人 amount 1200

28、 (loan)在上例结果中进一步查处贷款号Find the loan number for each loan of an amount greater than $1200 loan-number (amount 1200 (loan)2022/9/1343/100Example Queries查询例Find the names of all customers who have a loan, an account, or both, from the bank找出有账号 或 有贷款的人的姓名customer-name (borrower) customer-name (depositor

29、)找出有账号 且 有贷款的人的姓名Find the names of all customers who have a loan and an account at bank.customer-name (borrower) customer-name (depositor)2022/9/1344/100Example Queries 查询例Find the names of all customers who have a loan at the Perryridge branch. customer-name (branch-name=“Perryridge” (borrower.loan

30、-number = loan.loan-number (borrower x loan)找出在 这支行 有贷款的人的姓名连接条件2022/9/1345/100Example Queries 查询例Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank. 找出在 这支行 有贷款 但 在任何支行无账号的的人的姓名 customer-name (branch-name = “Perryridge” (bor

31、rower.loan-number = loan.loan-number(borrower x loan) customer-name(depositor)减去有账号的的人2022/9/1346/100Example Queries 查询例Find the names of all customers who have a loan at the Perryridge branch.找出在 这支行 有贷款的人的姓名Query 1 customer-name(branch-name = “Perryridge” (borrower.loan-number = loan.loan-number(b

32、orrower x loan) Query 2 customer-name(loan.loan-number = borrower.loan-number( (branch-name = “Perryridge”(loan) x borrower) ) 2022/9/1347/100Example Queries 查询例Find the largest account balance 找最大余额Rename account relation as dThe query is: balance(account) account.balance (account.balance d.balance

33、 (account x rd (account)减去 余额不是最大的2022/9/1348/100Formal Definition 关系代数表达式 形式化定义代数系统=集合运算及其封闭性一组规律（结合分配，如群 = 集合乘法结合律逆。环： = 集合乘法加法结合律 交换律，分配律。下定义的几种方法（补充）1 Let .be, Then XXX is said to be a YYYY,if .2 元组法。 XXX is a tuple(U,V,P) where U.3 运算构造法（递归法），如下页：A basic expression in the relational algebra con

34、sists of either one of the following:A relation in the databaseA constant relation2022/9/1349/100Formal Definition 关系代数表达式 形式化定义代数系统=集合运算及其封闭性一组规律（结合分配，如群 = 集合乘法结合律逆。环： = 集合乘法加法结合律 交换律，分配律。下定义的几种方法（补充）1 Let .be, Then XXX is said to be a YYYY,if .2 元组法。 XXX is a tuple(U,V,P) where U.3 运算构造法（递归法），如下页：

35、A basic expression in the relational algebra consists of either one of the following:A relation in the databaseA constant relation2022/9/1350/100Formal Definition 关系代数表达式 形式化定义 ep34Let E1 and E2 be relational-algebra expressions; the following are all relational-algebra expressions:E1 E2E1 - E2E1 x

36、E2p (E1), P is a predicate on attributes in E1s(E1), S is a list consisting of some of the attributes in E1 x (E1), x is the new name for the result of E1设E1,E2是，则6个基本运算的结果也是2022/9/1351/100Additional Operations 附加的运算 cp35We define additional operations that do not add any power to therelational alge

37、bra, but that simplify common queries.Set intersection 交Natural join 自然连接Division 除法Assignment 赋值2022/9/1352/100Set-Intersection Operation 交集 自学 cp35 Notation: r sDefined as:r s = t | t r and t s Assume: r, s have the same arity attributes of r and s are compatibleNote: r s = r - (r - s)rs2022/9/135

38、3/100Set-Intersection Operation Example自学Relation r, s:r sA B121A B23rsA B 22022/9/1354/100Natural-Join Operation 自然连接 cp35Notation: r s 直观。 电话线， 2红4兰，事故切断，接线有4种可能。2红线接2红线 ：2可能4兰线接4兰线 ：4!=24可能综合起来： 2X24=48 可能切断后连接的可能性 比原来 更多2022/9/1355/100Natural-Join Operation 自然连接 cp35 形式化定义要会写（想得正确，说得明白，写得清楚，三项基本

39、功）Notation: r s Let r and s be relations on schemas R and S respectively.The result is a relation on schema R S which is obtained by considering each pair of tuples tr from r and ts from s. If tr and ts have the same value on each of the attributes in R S, a tuple t is added to the result, wheret ha

40、s the same value as tr on rt has the same value as ts on sExample:R = (A, B, C, D)S = (E, B, D)Result schema = (A, B, C, D, E)r s is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.B r.D = s.D (r x s)先叉后选最后投影2022/9/1356/100Natural-Join Operation 自然连接 cp35Notation: r sLet r and s be relations on schemas

41、R and S respectively.The result is a relation on schema R S which is obtained by considering each pair of tuples tr from r and ts from s. If tr and ts have the same value on each of the attributes in R S, a tuple t is added to the result, wheret has the same value as tr on rt has the same value as t

42、s on sExample:R = (A, B, C, D)S = (E, B, D)Result schema = (A, B, C, D, E)r s is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.B r.D = s.D (r x s)先叉后选最后投影2022/9/1357/100Natural Join Operation ExampleRelations r, s: 连接点 公共属性B,D 值相同AB12412CDaababB13123DaaabbErAB11112CDaaaabEsr s(1,a)在 r, s中各2行。结果中2X2=4行

43、2022/9/1358/100Division Operation cp36另一种观点看除法实数 a b=c iff b x c=aR S =T 在某种程度上是 R=S x T 的 逆运算 T=Max T: S x T R 乘不溢出Max 能 保证惟一性多个T满足包含式， 选其中最大的一个2022/9/1359/100Division Operation cp36适合解答“共同的”，“全部”之内的查询Let r and s be relations on schemas R and S respectively whereR = (A1, , Am, B1, , Bn)S = (B1, , B

44、n)The result of r s is a relation on schemaR S = (A1, , Am) 要点1 ：模式相减r s = t | t R-S(r) u s ( tu r ) 要点2 : S x T R 乘不溢出r s =T2022/9/1360/100Division Operation ExampleRelations r, s:r s:AB12AB12311134612rsA兴趣组，B学生姓名，找出学生1和2 共同的兴趣 r s=t:2022/9/1361/100Another Division ExampleABaaaaaaaaCDaabababbE11113

45、111Relations r, s:r s:DabE11ABaaCrs2022/9/1362/100除法公式Property ，Let q r sThen q is the largest relation satisfying q x s rDefinition in terms of the basic algebra operationLet r(R) and s(S) be relations, and let S Rr s = R-S (r) R-S ( (R-S (r) x s) R-S,S(r)下面解释这个公式2022/9/1363/100Division Operation (

46、Cont.) r s =t解释公式：r s = R-S (r) R-S ( (R-S (r) x s) R-S,S(r)注意 R = (A1, , Am, B1, , Bn) S = (B1, , Bn) ，T=R S = (A1, , Am) 要点1 ：模式相减 R S = (A1, , Am) =T 要点2 : s x t r 满足 “乘不溢出” 的的最大t2022/9/1364/100r s = tr s = tR-S=TT = T (r) T( (T (r) x s) T,S(r)1 。 r s = t, “乘不溢出” 要求 结果t是 T (r) 的子集2 。T (r) 中可能有一些不

47、安分的元组t, 使得tXS在R之外，*乘要溢出“ 设法找出其中那些 不安分的元组t。 把它们扣除4 T(r ) x s 先投影投影到T上再乘S ，属性集是还是R, 但元组可能会比r多多了一些不安分的元组，他们造成 乘要溢出5 相减，把一处溢出r的减去.剩下的全是 乘而溢出的结果3 它就是r6 再投影到T, 子集中乘要溢出“ 那些 不安分的元组t。 2022/9/1365/100r s = tr s = tR-S=TT = T (r) T( (T (r) x s) T,S(r)1 r s = t“乘不溢出” 要求 结果t 是 T (r) 的子集2 。T (r) 中可能有一些不安分的元组t, 使得

48、tXS在R之外，*乘要溢出“ 设法找出其中那些*乘要溢出元组t。 把它们扣除4 T(r ) x s 先投影投影到T上再乘S ，属性集是还是R, 但元组可能会比r多多了一些不安分的元组，他们造成 乘要溢出5 相减，把一处溢出r的减去.剩下的全是 乘而溢出的结果3 它就是r6 再投影到T, 子集中乘要溢出“ 那些 不安分的元组t。 2022/9/1366/100r s = tr s = tR-S=TT = T (r) T( (T (r) x s) T,S(r)1 r s = t, “乘不溢出” 要求 结果t是 T (r) 的子集2 。T (r) 中可能有一些不安分的元组t, 使得tXS在R之外，*

49、乘要溢出“ 设法找出其中那些 不安分的元组t。 把它们扣除4 T(r ) x s 先投影投影到T上再乘S ，属性集是还是R, 但元组可能会比r多多了一些不安分的元组，他们造成 乘要溢出5 相减，把一处溢出r的减去.剩下的全是 乘而溢出的结果3 它就是r6 再投影到T, 子集中乘要溢出“ 那些 不安分的元组t。 2022/9/1367/100r s = tr s = tR-S=TT = T (r) T( (T (r) x s) T,S(r)1 r s = t, “乘不溢出” 要求 结果t是 T (r) 的子集2 。T (r) 中可能有一些不安分的元组t, 使得tXS在R之外，*乘要溢出“ 设法找

50、出其中那些 不安分的元组t。 把它们扣除4 T(r ) x s 先投影投影到T上再乘S ，属性集是还是R, 但元组可能会比r多多了一些 乘而溢出 的元组5 相减，把一处溢出r的减去.剩下的全是 乘而溢出的结果3 它就是r6 再投影到T, 子集中乘要溢出“ 那些 不安分的元组t。 2022/9/1368/100r s = tr s = tR-S=TT = T (r) T( (T (r) x s) T,S(r)1 r s = t, “乘不溢出” 要求 结果t是 T (r) 的子集2 。T (r) 中可能有一些不安分的元组t, 使得tXS在R之外，*乘要溢出“ 设法找出其中那些 不安分的元组t。 把

51、它们扣除4 T(r ) x s 先投影投影到T上再乘S ，属性集是还是R, 但元组可能会比r多多了一些 乘而溢出 的元组5 相减，把一处溢出r的减去.剩下的全是 乘而溢出的结果3 它就是r6 再投影到T, 子集中乘要溢出“ 那些 不安分的元组t。 2022/9/1369/100r s = tr s = tR-S=TT = T (r) T( (T (r) x s) T,S(r)1 r s = t , “乘不溢出” 要求 结果t是 T (r) 的子集2 。T (r) 中可能有一些不安分的元组t, 使得tXS在R之外，*乘要溢出“ 设法找出其中那些 不安分的元组t。 把它们扣除4 T(r ) x s

52、 先投影投影到T上再乘S ，属性集是还是R, 但元组可能会比r多多了一些 乘而溢出 的元组5 相减，把一处溢出r的减去.剩下的全是 乘而溢出的结果3 它就是r6 再投影到T, 子集中乘要溢出“ 那些 不安分的元组t。 2022/9/1370/100Division Operation (Cont.)Property ，Let q r sThen q is the largest relation satisfying q x s rDefinition in terms of the basic algebra operationLet r(R) and s(S) be relations,

53、and let S Rr s = R-S (r) R-S ( (R-S (r) x s) R-S,S(r)理解：。理解每个括号的语义，从内到外To see whyR-S,S(r) simply reorders attributes of rR-S(R-S (r) x s) R-S,S(r) gives those tuples t in R-S (r) such that for some tuple u s, tu r.2022/9/1371/100Assignment Operation 赋值语句 cp37assignment operation () 用于复杂的程序，Assignmen

54、t must always be made to a temporary relation variable. 中间用 临时变量Example: Write r s as temp1 R-S (r) temp2 R-S (temp1 x s) R-S,S (r)result = temp1 temp2The result to the right of the is assigned to the relation variable on the left of the .May use variable in subsequent expressions.2022/9/1372/100Exa

55、mple Queries 例找出在 Downtown 和 Uptown 两支行 都有账户的客户.Query 1 CN(BN=“Downtown”(depositor account) CN(BN=“Uptown”(depositor account)where CN denotes customer-name and BN denotes branch-name.Query 2 用除法，都 customer-name, branch-name (depositor account) temp(branch-name) (“Downtown”), (“Uptown”) 2022/9/1373/1

56、00Example Queries 例找出在 Downtown 和 Uptown 两支行 都有账户的客户.Query 1 CN(BN=“Downtown”(depositor account) CN(BN=“Uptown”(depositor account)where CN denotes customer-name and BN denotes branch-name.Query 2 用除法，都有 customer-name, branch-name (depositor account) temp(branch-name) (“Downtown”), (“Uptown”) 2022/9/

57、1374/100Find all customers who have an account at all branches located in Brooklyn city.在所有支行有账户的客户 customer-name, branch-name (depositor account) branch-name (branch-city = “Brooklyn” (branch)Example Queries2022/9/1375/100Extended Relational-Algebra-Operations 扩展关系运算 cp37Generalized Projection 投影Ou

58、ter Join 外连接Aggregate Functions 聚集2022/9/1376/100Generalized Projection 广义投影 cp38Extends the projection operation by allowing arithmetic functions to be used in the projection list. F1, F2, , Fn(E)E is any relational-algebra expressionEach of F1, F2, , Fn are are arithmetic expressions involving con

59、stants and attributes in the schema of E.找出每人可以消费多少，（算术表达式作新字段值）Given relation credit-info(customer-name, limit, credit-balance), find how much more each person can spend: customer-name, limit credit-balance (credit-info)投影的字段 允许用算术表达式把投影和计算一次做完2022/9/1377/100Aggregate Functions and Operations 聚集函数

60、cp39Aggregation function takes a collection of values and returns a single value as a result.avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values2022/9/1378/100Aggregate Functions and Operations 聚集函数 cp39Aggregate operation in relational algebra关系表达方式G1, G2