【数据库系统原理】关系模型

1、第三章：关系模型Chapter 3: Relational Model,关系数据库的结构Structure of Relational Databases 关系代数Relational Algebra 元组关系演算Tuple Relational Calculus 域关系演算Domain Relational Calculus 扩展关系代数操作Extended Relational-Algebra-Operations 数据库更新Modification of the Database 视图Views,关系示例Example of a Relation,基本结构Basic Structure,

2、给定一组域集，则关系是该域集的笛卡尔积的子集。即关系是N元集合，其中每个元素都隶属于某个域。Formally, given sets D1, D2, . Dn a relation r is a subset of D1 x D2 x x DnThus a relation is a set of n-tuples (a1, a2, , an) where ai Di 例：Example: if customer-name = Jones, Smith, Curry, Lindsaycustomer-street = Main, North, Parkcustomer-city = Harri

3、son, Rye, PittsfieldThen r = (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield) 其中，r是基于上述三个域的关系。is a relation over customer-name x customer-street x customer-city,属性类型Attribute Types,每个关系的属性都有一个名称。Each attribute of a relation has a name 各属性的取值范围集称为属性的域。The

4、 set of allowed values for each attribute is called the domain of the attribute 属性的值通常要求是原子性的，即不能分割。Attribute values are (normally) required to be atomic, that is, indivisible 例如，多值属性可以不是原子性的。E.g. multivalued attribute values are not atomic 例如，组合属性值不是原子性的。E.g. composite attribute values are not atom

5、ic 空值这一特殊的值包含在所有的域中。The special value 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,关系模式Relation Sch

6、ema,A1, A2, , An 是属性are attributes R = (A1, A2, , An )是关系模式 is a relation schema 例如，E.g. Customer-schema = (customer-name, customer-street, customer-city) r(R) 是关系模式R中的一个关系。is a relation on the relation schema R 例如，E.g.customer (Customer-schema),关系实例Relation Instance,关系的当前值（关系实例）用表来表示。The current va

7、lues (relation instance) of a relation are specified by a table r中元素t称为无组，由表中的行表示。的An element t of r is a tuple, represented by a row in a table,Jones Smith Curry Lindsay,customer-name,Main North North Park,customer-street,Harrison Rye Rye Pittsfield,customer-city,customer,属性attributes,元组tuples,关系是无

8、序的Relations are Unordered,元组的顺序不重要（元组可以以任意的顺序存储）Order of tuples is irrelevant (tuples may be stored in an arbitrary order) 例如，下面是无序的客户表。E.g. account relation with unordered tuples,数据库Database,数据库由多个关系组成。A database consists of multiple relations 企业信息被划分成若干部分，每一部分都存储着部分的信息。Information about an enterpr

9、ise is broken up into parts, with each relation storing one part of the information例如，E.g.: account :客户表存储帐户信息。 stores information about accounts depositor : 存储客户拥有的帐户的信息。stores information about which customer owns which account customer : 存储客户信息。stores information about customers 将所有信息存储在一个关系中会导致一

10、些问题：Storing all information as a single relation such as bank(account-number, balance, customer-name, .)results in 信息重复（如，两个客户拥有同一帐户时）repetition of information (e.g. two customers own an account) 需要空值（如，没有帐户的客户）the need for null values (e.g. represent a customer without an account) 范式理论（第七章）用于设计关系模式

11、。Normalization theory (Chapter 7) deals with how to design relational schemas,客户关系The customer Relation,储户关系The depositor Relation,银行系统的E-R图E-R Diagram for the Banking Enterprise,码Keys,Let K R 如果K是可以唯一R中元组的，则K是超级码，它应该可以对任何可能的关系都有这种唯一性。K is a superkey of R if values for K are sufficient to identify a

12、 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 superkeys of Customer, if no two customers can possibly have the same na

13、me. 如果K是最小的形式，则K是候选码。K is a candidate key if K is minimal例：Example: 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.,在E-R集中确定码Dete

14、rmining Keys from E-R Sets,强实体集。实体集的主码即可作为关系的主码。Strong entity set. The primary key of the entity set becomes the primary key of the relation. 弱实体集。关系的主码由强实体集的主码和弱实体集的分辨符组合而成。Weak entity set. The primary key of the relation consists of the union of the primary key of the strong entity set and the dis

15、criminator of the weak entity set. 关系集。相关实体集的主码的并成为关系的超级码。Relationship set. The union of the primary keys of the related entity sets becomes a super key of the relation. 在二元多对一关系集中，多方实体集的主码成为关系的主码。For binary many-to-one relationship sets, the primary key of the “many” entity set becomes the relation

16、s primary key. 在一对一关系集中，关系的主码可以是任一实体集的主码。For one-to-one relationship sets, the relations primary key can be that of either entity set. 在多对多关系集中，关系的主码是主码的并。For many-to-many relationship sets, the union of the primary keys becomes the relations primary key,银行系统的模式图Schema Diagram for the Banking Enterp

17、rise,查询语言Query Languages,查询语言用于从数据库中提取信息。Language in which user requests information from the database. 语言的分类：Categories of languages 过程化的procedural 非过程化的non-procedural “纯”语言“Pure” languages: 关系代数Relational Algebra 元组关系演算Tuple Relational Calculus 域关系演算Domain Relational Calculus 纯语言构成了人们使用的查询语言的基础。Pu

18、re languages form underlying basis of query languages that people use.,关系代数Relational Algebra,过程化语言Procedural language 六个基本操作Six basic operators 选取select 投影project 并union 集合差set difference 笛卡尔积Cartesian product 重命名rename 操作以二个或多个关系为输入，结果是一个新的关系。The operators take two or more relations as inputs and

19、give a new relation as a result.,选取操作示例Select Operation Example,Relation r,A,B,C,D, , ,1 5 12 23,7 7 3 10,A=B D 5 (r),选取操作Select Operation,记作：Notation: p(r) p 称为选取谓词。is called the selection predicate 定义为：Defined as: p(r) = t | t r and p(t) 其中p是命题演算公式，可以由与、或、非连接各公式项。Where p is a formula in propositio

20、nal calculus consisting of terms connected by : (and), (or), (not)每一项的形式可以下面中的一种：Each term is one of: op 或or 其中op是比较操作符。where op is one of: =, , , . . 选取示例：Example of selection: branch-name=“Perryridge”(account),投影操作示例Project Operation Example,Relation r:,A,B,C, ,10 20 30 40,1 1 1 2,=,A,C (r),投影操作Pr

21、oject Operation,记作：Notation:A1, A2, , Ak (r) 其中A是属性名，r是关系名。where A1, A2 are attribute names and r is a relation name. 结果是包含K个列的关系，其它的列被删除了。The result is defined as the relation of k columns obtained by erasing the columns that are not listed 由于关系是集合，故结果中消除了重复的行。Duplicate rows removed from result, si

22、nce relations are sets 例如，消除了属性“部门名”的帐户如下：E.g. To eliminate the branch-name attribute of account account-number, balance (account),并操作示例Union Operation Example,Relations r, s:,r s:,A,B, ,1 2 1,A,B, ,2 3,r,s,并操作Union Operation,记作：Notation: r s 定义：Defined as: r s = t | t r or t s 并操作成立的要求：For r s to b

23、e valid. 1. r, s 拥有相同的数量（属性数目相同）must have the same arity (same number of attributes) 2. 属性域兼容（如r的第2列与s的第2列具有相同类型的值）The attribute domains must be compatible (e.g., 2nd column of r deals with the same type of values as does the 2nd column of s) 例如，找出有帐户或者有贷款的所有客户。E.g. to find all customers with either

24、 an account or a loan customer-name (depositor) customer-name (borrower),集合差操作示例Set Difference Operation Example,Relations r, s:,r s:,A,B, ,1 2 1,A,B, ,2 3,r,s,A,B, ,1 1,集合差操作Set Difference Operation,记作：Notation r s r s = t | t r and t s 定义：Defined as: 集合差必须在兼容（同类）关系间进行。Set differences must be taken

25、 between compatible relations. r and s拥有相同的数量。 must have the same arity 属性域兼容。attribute domains of r and s must be compatible,笛卡尔积操作示例Cartesian-Product Operation-Example,Relations r, s:,r x s:,A,B, ,1 2,C,D, ,10 10 20 10,E,a a b b,r,s,笛卡尔积操作Cartesian-Product Operation,记作：Notation r x s 定义：Defined as

26、: r x s = t q | t r and q s 假设属性不相交（即R 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

27、示例：Example: A=C(r x s) r x s A=C(r x s),A,B, ,1 1 1 1 2 2 2 2,C,D, ,10 19 20 10 10 10 20 10,E,a a b b a a b b,A,B,C,D,E, ,1 2 2, ,10 20 20,a a b,重命名操作Rename Operation,允许命名，即引用关系代数表达式的结果。Allows us to name, and therefore to refer to, the results of relational-algebra expressions. 允许关系拥有多个引用名称。Allows u

28、s to refer to a relation by more than one name. 示例：Example: x (E) 返回E表达式的名称X。returns the expression E under the name X 如果关系代数表达式E的数量为N，则：If a relational-algebra expression E has arity n, then x (A1, A2, , An) (E) 返回表达式E的名称X作为结果，且相关属性分别重命名为： A1, A2, ., An returns the result of expression E under the

29、name X, and with the attributes renamed to A1, A2, ., An.,银行示例Banking Example,branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-only) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-nu

30、mber) borrower (customer-name, loan-number),查询示例Example Queries,找出所有超过1200美元的贷款。Find all loans of over $1200 amount 1200 (loan) 找出贷款客额超过1200美元的贷款号。Find the loan number for each loan of an amount greater than $1200 loan-number (amount 1200 (loan),amount1200,loan,查询示例Example Queries,从银行中找出所有有贷款、帐户、或两者

31、兼有的客户名字。Find the names of all customers who have a loan, an account, or both, from the bank customer-name (borrower) customer-name (depositor) 找出所有即有贷款，又有帐户的客户名字。Find the names of all customers who have a loan and an account at bank. customer-name (borrower) customer-name (depositor),查询示例Example Que

32、ries,找出在某支行（如Perryridge)有贷款的客户名字。Find the names of all customers who have a loan at the Perryridge branch. customer-name (branch-name=“Perryridge” (borrower.loan-number = loan.loan-number(borrower x loan) 找出在Perryidge支行有贷款，但在整个银行中并无帐户的客户名字。Find the names of all customers who have a loan at the Perry

33、ridge branch but do not have an account at any branch of the bank. customer-name (branch-name = “Perryridge” (borrower.loan-number = loan.loan-number(borrower x loan) customer-name(depositor),查询示例Example Queries,找出在Perryridge支行有贷款的客户名字。Find the names of all customers who have a loan at the Perryridg

34、e branch. 查询1，Query 1 customer-name(branch-name = “Perryridge” (borrower.loan-number = loan.loan-number(borrower x loan) 查询2，Query 2 customer-name(loan.loan-number = borrower.loan-number( (branch-name = “Perryridge”(loan) x borrower) ),查询示例Example Queries,找出最大的帐户余额。Find the largest account balance 将

35、帐户关系重命名为d。Rename account relation as d 则查询为：The query is: balance(account) - account.balance (account.balance d.balance (account x rd (account),形式化定义Formal Definition,关系代数中的基本表达式由下列两种形式表示：A basic expression in the relational algebra consists of either one of the following: 数据库中的关系。A relation in the

36、database 常量关系A constant relation 设E1和E2是关系代数表达式，则下列表达式都是关系代数表达式：Let E1 and E2 be relational-algebra expressions; the following are all relational-algebra expressions: E1 E2 E1 - E2 E1 x E2 p (E1), P是关系属性的谓词 is a predicate on attributes in E1 s(E1), S是某些属性 is a list consisting of some of the attribut

37、es in E1 x (E1), x是结果集的新命名 is the new name for the result of E1,附加操作Additional Operations,定义附加的操作并不能增强关系代数的功能，但可以简化常用的查询。We define additional operations that do not add any power to the relational algebra, but that simplify common queries. 集合交Set intersection 自然连接Natural join 除法Division 赋值Assignment

38、,集合交操作Set-Intersection Operation,记作：Notation: r s 假设：Assume: r, s 有相同的数量。have the same arity 属性兼容。attributes of r and s are compatible 定义Defined as: r s = t | t r and t s 注：Note: r s = r - (r - s),集合交操作示例Set-Intersection Operation - Example,Relation r, s: r s,A B, ,1 2 1,A B, ,2 3,r,s,A B, 2,自然连接操作N

39、atural-Join Operation,记作Notation: r s 设r和s分别是模式R和S的关系。结果是模式R和S的并下的关系，其中元组部分来自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. 如果tr 和ts的值取自R S模式下相对应属性下的值，则其组合而成的元组t就是结果

40、之一。其中： If tr and ts have the same value on each of the attributes in R S, a tuple t is added to the result, where t中的tr 部分的值与r中的相同。t has the same value as tr on r t中的ts 部分的值与s中的相同。 t has the same value as ts on s 示例Example: R = (A, B, C, D) S = (E, B, D) 结果集模式Result schema = (A, B, C, D, E) r s 定义为：

41、is defined as: r.A, r.B, r.C, r.D, s.E (r.B = s.B r.D = s.D (r x s),自然连接操作示例Natural Join Operation Example,Relations r, s:,A,B, ,1 2 4 1 2,C,D, ,a a b a b,B,1 3 1 2 3,D,a a a b b,E, ,r,s,除法操作Division Operation,适合包含短误“全部”的查询。Suited to queries that include the phrase “for all”. r和s分别为模式R和S的关系。Let r an

42、d s be relations on schemas R and S respectively where R = (A1, , Am, B1, , Bn) S = (B1, , Bn) 则除法的结果是建立在下列模式下的关系。The result of r s is a relation on schema R S = (A1, , Am) r s = t | t R-S(r) u s ( tu r ) ,r s,说明：Yx=y|trRy=trYx=trX。Yx为x在R中的象集x=trX（即Yx为属性 X取值x）时，相应的Y值的集合。当中首次出现了关于集合的比较。,除法操作示例Divisio

43、n Operation Example,Relations r, s:,r s:,B,1 2,A,B, ,1 2 3 1 1 1 3 4 6 1 2,r,s,另一除法示例Another Division Example,A,B, ,a a a a a a a a,C,D, ,a a b a b a b b,E,1 1 1 1 3 1 1 1,Relations r, s:,r s:,D,a b,E,1 1,A,B, ,a a,C, ,r,s,除法操作（续）Division Operation (Cont.),特性：Property 设Let q r s 则q是满足q x s r 的最大关系。T

44、hen q is the largest relation satisfying q x s r 按照基本代数操作的定义，令r(R)和s(S)为关系，且S R 。则有：Definition in terms of the basic algebra operationLet r(R) and s(S) be relations, and let S R r s = R-S (r) R-S ( (R-S (r) x s) R-S,S(r) 原因是：To see why R-S,S(r)只是对r的属性重排序。 simply reorders attributes of r R-S(R-S (r)

45、x s) R-S,S(r) 所指的是R-S (r)中的元组t，其中有些元组u s, tu r 。gives those tuples t in R-S (r) such that for some tuple u s, tu r.,赋值操作Assignment Operation,赋值操作() 能够方便表达复杂的查询，可以将查询用一组赋值构成的连续程序来表示，最终的结果也可以由表达式显示。The assignment operation () provides a convenient way to express complex queries, write query as a seque

46、ntial program consisting of a series of assignments followed by an expression whose value is displayed as a result of the query. 赋值用于临时的关系变量。Assignment must always be made to a temporary relation variable. 示例：可以将r s 写成如下形式：Example: Write r s as temp1 R-S (r) temp2 R-S (temp1 x s) R-S,S (r)result = t

47、emp1 temp2 将 右边的结果赋值给 左边的关系变量。The result to the right of the is assigned to the relation variable on the left of the . 在随后的表达式中可以使用这些变量。May use variable in subsequent expressions.,查询示例Example Queries,找出在市中心和郊区支行都有帐户的客户。Find all customers who have an account from at least the “Downtown” and the Uptow

48、n” branches. 查询1。Query 1 CN(BN=“Downtown”(depositor account) CN(BN=“Uptown”(depositor account) 其中CN表示客户名称，BN表示支行名称。where CN denotes customer-name and BN denotes branch-name. 查询2。Query 2 customer-name, branch-name (depositor account) temp(branch-name) (“Downtown”), (“Uptown”),找出在Brooklyn（布鲁克林）市中所有支行都

49、有帐户的客户。Find 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 Queries,扩展关系代数操作Extended Relational-Algebra-Operations,广义投影Generalized Projection 外连接Outer Join 聚集函数Aggreg

50、ate Functions,广义投影Generalized Projection,扩展投影操作，使之允许在投影列表中使用算术函数。Extends the projection operation by allowing arithmetic functions to be used in the projection list. F1, F2, , Fn(E) E 是关系代数表达式。is any relational-algebra expression F1, F2, , Fn 是包含常量或E模式下属性的算术表达式。Each of F1, F2, , Fn are are arithmeti

51、c expressions involving constants and attributes in the schema of E. 给定关系credit-info(customer-name, limit, credit-balance) ，找出每个人可以消费的金额。Given relation credit-info(customer-name, limit, credit-balance), find how much more each person can spend: customer-name, limit credit-balance (credit-info),聚集函数和

52、操作Aggregate Functions and Operations,聚集函数使用一组值，并返回单值。Aggregation 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 values 聚集操作在关系代数中的表示如下：Aggregate operation in relationa

53、l algebra G1, G2, , Gn g F1( A1), F2( A2), Fn( An) (E) E是关系代数表达式 is any relational-algebra expression G1, G2 , Gn 是分组的属性列表（可以为空）is a list of attributes on which to group (can be empty) Each Fi 是聚集函数is an aggregate function Each Ai 是属性名称is an attribute name,聚集操作示例Aggregate Operation Example,Relation

54、r:,A,B, , ,C,7 7 3 10,g sum(c) (r),sum-C,27,聚集操作示例Aggregate Operation Example,帐户关系按支行名称分组：Relation account grouped by branch-name:,branch-name g sum(balance) (account),branch-name,account-number,balance,Perryridge Perryridge Brighton Brighton Redwood,A-102 A-201 A-217 A-215 A-222,400 900 750 750 700

55、,聚集函数（续）Aggregate Functions (Cont.),聚集的结果的命名Result of aggregation does not have a name 使用重命名操作给其命名。Can use rename operation to give it a name 方便起见，将重命名作为聚集操作的一部分来对待。For convenience, we permit renaming as part of aggregate operation,branch-name g sum(balance) as sum-balance (account),外连接Outer Join,连接

56、操作的扩展，可以避免信息的丢失。An extension of the join operation that avoids loss of information. 按正常方式作连接，并把一个关系中有而在另一关系中没有匹配元组的元组作为放在结果中。Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. 使用空值：Uses null values: null 表示未知的或不存在的值。si

57、gnifies that the value is unknown or does not exist 所有包含空值的比较，定义其结果为false（笼统而言）。All comparisons involving null are (roughly speaking) false by definition. 后面再仔细介绍空值比较。Will study precise meaning of comparisons with nulls later,外连接示例Outer Join Example,Relation loan,loan-number,amount,L-170 L-230 L-260

58、,3000 4000 1700,Relation borrower,customer-name,loan-number,Jones Smith Hayes,L-170 L-230 L-155,branch-name,Downtown Redwood Perryridge,外连接示例Outer Join Example,内连接Inner Joinloan Borrower,loan borrower,左外连接Left Outer Join,loan-number,amount,L-170 L-230 L-260,3000 4000 1700,customer-name,Jones Smith n

59、ull,branch-name,Downtown Redwood Perryridge,外连接示例Outer Join Example,右外连接Right Outer Join loan borrower,loan-number,amount,L-170 L-230 L-155,3000 4000 null,customer-name,Jones Smith Hayes,loan-number,amount,L-170 L-230 L-260 L-155,3000 4000 1700 null,customer-name,Jones Smith null Hayes,loan borrower,全外连接Full Outer Join,branch-name,Downtown Redwo