数据库课程ch3 关系模型.ppt

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 视图,Basic Structure基本结构,每个关系的属性都有一个名称。 各属性的

2、取值范围集称为属性的域domain 。 给定一组域集A1, A2, . An ，则关系r是该域集的笛卡尔积的A1 x A2 x x An子集。即关系是N元集合(a1, a2, , an) ，其中每个元素ai都隶属于某个域Ai 。 例如: 如果 customer-name = Jones, Smith, Curry, Lindsaycustomer-street = Main, North, Parkcustomer-city = Harrison, Rye, Pittsfield那么 r = (Jones, Main, Harrison), (Smith, North, Rye), (Curr

3、y, North, Rye), (Lindsay, Park, Pittsfield) ，是 customer-name x customer-street x customer-city上的一个关系。,关系的当前值（关系实例）用表来表示。 r中元素t称为元组，由表中的行表示。 Account关系,元组的顺序不重要。（元组可以以任意的顺序存储）。 属性的值通常要求是原子性atomic的，即不能分割。 空值这一特殊的值包含在所有的域中。 空值导致了许多操作定义的复杂性。,Database Schema数据库模式,A1, A2, , An 是属性。 R = (A1, A2, , An ) 是关系模

4、式 例如： Account-schema= (account-number, branch-name,balance) 数据库实例 关系实例 r(R) 是关系模式R中的一个关系。 例如： account (Account-schema),E-R Diagram for the Banking Enterprise银行系统的E-R图,Keys码,superkey 超码 例如： 如果没有两个顾客可以同名，那么，customer-name, customer-street 和 customer-name 都是Customer的超码。 candidate key 候选码例如： 假设没有两个顾客可以同名

5、，即 customer-name 是一个超码，因为它没有真子集是一个超码，所以它是Customer的一个候选码。 primary key关键字,Determining Primary Keys from E-R Schema从E-R模式确定主码,Strong entity set强实体集 Weak entity set弱实体集 Relationship set联系集 Combined tables合并表格 Multivalued attributes多值属性,Schema Diagram for the Banking Enterprise银行系统的模式图,Foreign key外码（一个关系

6、模式中的属性包含另一个关系模式的主码） Referencing relation参照关系 Referenced relation被参照关系,Query Languages查询语言,查询语言用于从数据库中提取信息。 Categories of languages:语言的分类： procedural 过程化的 non-procedural 非过程化 “Pure” languages: “纯”语言“ Relational Algebra 关系代数 过程化的 Tuple Relational Calculus 元组关系演算 非过程化的 Domain Relational Calculus 域关系演算

7、纯语言构成了人们使用的查询语言的基础。,Relational Algebra关系代数,过程化语言 六种基本操作 select 选择 unary operations一元运算 project 投影 union 并 set difference 集合差 binary operations二元运算 Cartesian product 笛卡儿积 rename 更名 操作以一个或两个关系为输入，结果是一个新的关系。,Select Operation选择运算,记作： p(r) p 称为选取谓词。 选择谓词中用的比较运算符：=, , , , , 用连接符 (and), (or), (not)把几个谓词合并成

8、一个大的谓词。 p(r) X Y(r) X=1(r) 1 2(r),amount1000(loan), 找出loan关系中贷款额低于1000美元的元组。,branch-name=“perryridge”(loan),找出loan关系中由 Perryridge银行提供的超过1200美元的贷款元组。, branch-name=“perryridge” amount1200(loan), 找出loan-officer关系中和自己的贷款经纪同名的顾客元组。, customer-name=banker-name(loan-officer),Project Operation投影运算,记作： A1, A2

9、, , Ak (r) 其中A1, A2 是属性名，r是关系名。 结果是包含K个列的关系，其它的列被删除了。 由于关系是集合，故结果中消除了重复的行。,例子： 列出所有贷款的贷款号和贷款额。,loan-number,amount(loan),Composition of Relational Operations关系运算的组合,多个关系代数运算可以组合成一个关系代数表达式。 例子：找出所有住在“Harrison” 的顾客的姓名清单。,customer-name( customer-city=“Harrison”(customer),Union Operation并运算,记作： r s 定义为：

10、r s = t | t r or t s 返回的关系中包含了两个关系的所有元组。（该元组只出现在其中一个关系中，或者在两个关系中都出现。） 为了使r s 是有效的： 1. 关系r、s必须是同元的（属性数目相同）。 2. 对所有的i，r的第i个属性的域必须和s的第i个属性的域相同。,例子： 找出有贷款或有存款或既有贷款又有存款顾客的姓名清单。,customer-name(borrower)customer-name(depositor),Union Operation Example,Relations r, s:,r s:,A,B, ,1 2 1,A,B, ,2 3,r,s,A,B, ,1 2

11、 1 3,Set Difference Operation集合差运算,记作： r s 定义为： r s = t | t r and t s 返回的关系包含所有出现在第一个关系中，但不出现在第二个关系中的元组。 集合差必须操作在相容关系上。 r 和s 必须同元. r 和s的属性的域必须相容。,Example: 找出所有有存款但没有贷款的顾客的姓名清单。,customer-name(depositor)customer-name(borrower),Set Difference Operation Example,Relations r, s:,r s:,A,B, ,1 2 1,A,B, ,2 3

12、,r,s,A,B, ,1 1,Cartesian-Product Operation笛卡尔积运算,记作： r x s 定义为： r x s = t q| t r and q s Assume that attributes of r(R) and s(S) are disjoint. (This is, R S = ). 假设属性不相交 If attributes of r(R) and s(S) are not disjoint, then renaming must be used. 如果属性是相交的，则必须重命名。 Assume that r has n1 tuples and s ha

13、s n2 tuples,there are n1*n2 tuples in r.,Example:,a b c,a b c,b a f,b a f,c d d,c d d,b g a,b g a,b g a,b a f,b a f,b a f,Find the names of all customers who have a loan at the Perryridge branch.,customer-name( branch-name=“Perryridge” (borrowerloan),Hayes A-102,A-103 Round Hidd 900,A-102 Perryridge

14、 1500,A-101 Perryridge 1400,Hayes A-102,Hayes A-102,Johnson A-103,Johnson A-103,Johnson A-103,A-103 Round Hidd 900,A-102 Perryridge 1500,A-101 Perryridge 1400,Smith A-101,Smith A-101,Smith A-101,A-103 Round Hidd 900,A-102 Perryridge 1500,A-101 Perryridge 1400, Query 1 查询1 customer-name( borrower.loa

15、n-number = loan.loan-number (branch-name = “Perryridge”(borrower x loan) Query 2 查询2 customer-name( borrower.loan-number = loan.loan-number ( (branch-name = “Perryridge”(loan) xborrower),自然连接示例,Rename Operation更名运算,Allows us to name, and therefore to refer to, the results of relational-algebra expre

16、ssions.允许命名，即引用关系代数表达式的结果。 Allows us to refer to a relation by more than one name.允许关系拥有多个引用名称。 Example: x (E) returns the expression E under the name X. 示例：返回在名字x下表达式E的结果。 If a relational-algebra expression E has arity n, then 如果关系代数表达式E是n元的，则： x (A1, A2, , An) (E) returns the result of expression

17、E under the name X, and with the attributes renamed to A1, A2, ., An.返回在名字x下表达式E的结果，且将各属性更名为： A1, A2, ., An。,Examples: Find the largest account balance in the bank.,balance(account) account.balance( account.balanced.balance (account d(account), Find the names of all customers who live on the same st

18、reet and in the same city as Smith: customer.customer-name ( customer.customer-street=smith-addr.street customer.customer- city=smith-addr-city (customer x rsmith-addr(street,city) (customer-street,customer-city( customer-name=“Smith”. (customer ),Formal Definition形式化定义,A basic expression in the rel

19、ational algebra consists of either one of the following:关系代数中的基本表达式由下列两种形式表示： A relation in the database 数据库中的关系。 A constant relation 常量关系 Let E1 and E2 be relational-algebra expressions; the following are all relational-algebra expressions:设E1和E2是关系代数表达式，则下列表达式都是关系代数表达式： E1 E2 E1 - E2 E1 x E2 p (E1

20、), P is a predicate on attributes in E1 s(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,Additional Operations附加运算,We define additional operations that do not add any power to the relational algebra, but that simplify common queries.定义附加的操作

21、并不能增强关系代数的功能，但可以简化常用的查询。 集合交Set intersection 自然连接Natural join 除法Division 赋值Assignment,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集合交运算示

22、例,Relation r, s: r s:,A B, 2,A B, ,1 2 1,A B, ,2 3,r,s,Natural-Join Operation自然连接运算,Notation: r s The natural join operation include three steps: From a Cartesian Product RS Select those tuples which satisfy the requirements R.Ai=S.Ai Remove duplicate attributes. Example: R = (A, B, C, D) S = (E, B,

23、 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),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,A,B, ,1 1 1 1 2,C,D, ,a a a a b,E, ,s,r s,笛卡儿积示例,Depositor-schema=(customer-name,

24、account-number),Borrower-schema=(customer-name, loan-number), Find all customers who have both a loan and an account at the bank.,customer-name(borrower)customer-name( depositor), Find the names of all branches with customers who have an account in the bank and who live in Harrison.,Customer-schema=

25、(customer-name, customer-street, customer-city),Account-schema=(branch-name, account-number, balance),Depositor-schema=(customer-name, account-number),连接：从RS的结果集中，选取在指定的属性集上满足条件的元组，组成新的关系。 连接记作： = AB(RS) A和B分别为R和S上度数相等且可比的属性组。 是比较运算符（ = ）。 在运算结果中，如果出现同名的属性，需要指出属性来自哪个关系，则可在属性前面加上前缀“R.”或“S.”。 连接的特征： 两

26、个关系参加运算，不一定有公共属性。 结果关系中不去掉公共属性。,Theta Join Operation 连接,例1：,例2： U AD U.BV.BV =？,关系U,关系V,Division Operation除运算,Suited to queries that include the phrase “for all”. Let r and 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

27、 schema:R S = (A1, , Am) R与S的除法运算得到一个新关系P(X)，(其中X = A1, , Am )， P是R中满足下列条件的元组在X属性列上的投影：元组在X上的分量值x的象集Yx包含S在Y上的投影的集合。 Definition in terms of the basic algebra operation.Let 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) T= R-S( r ) W=( Ts )-r V=R-S( W ) rs=T-V,例1：

28、,关系R,关系S,a,b的象集(c,d),(e,f),(d,e) b,c的象集(e,f) e,d的象集(c,d),(e,f),S在(C,D)上的投影: (c,d),(e,f),a,b, e,d的象集包含了S在(C,D)上的投影,RS,t r s if and only if both of two conditions hold: 1.t R-S(r) 2.For every tuple ts in s, there is a tuple tr in r satisfying both of the following: a. trs= tss b. trR-S=t,例2： RS=？,关系R,

29、关系S,T=A,B(R) W=(TS)-R V=A,B(W) RS=T-V,关系T,TS,关系W,T=A,B(R) W=(TS)-R V=A,B(W) RS=T-V,关系V,关系W,关系T,RS,T=A,B(R) W=(TS)-R V=A,B(W) RS=T-V,Example: find all customers who have an account at all the branches located in Brooklyn.,Assignment Operation赋值运算,The assignment operation () provides a convenient way

30、to express complex queries, write query as a sequential 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. Example: Write r s as temp1 R-S (r) temp2 R-S (temp1 x s

31、) R-S,S (r)result = temp1 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.,Extended Relational-Algebra-Operations扩展的关系代数运算,Generalized Projection Outer Join Aggregate Functions,Generalized Projection广义投影,Extend

32、s the projection operation by allowing arithmetic functions to be used in the projection list. F1, F2, , Fn(E) E is any relational-algebra expression Each of F1, F2, , Fn are are arithmetic expressions involving constants and attributes in the schema of E. Given relation credit-info(customer-name, l

33、imit, credit-balance),目前为止的花费 find how much more each person can spend: customer-name, limit credit-balance (credit-info) Rename the attribute customer-name, limit credit-balance as credit-available(credit-info),Outer Join外连接,An extension of the join operation that avoids loss of information. Comput

34、es 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 signifies that the value is unknown or does not exist All comparisons involving null are false by definition.,Outer Join Example,Relation employee,Rel

35、ation ft-works,Outer Join Example,employee ft-works,employee ft-works,Outer Join Example,employee ft-works,employee ft-works,Aggregate Functions聚集函数,Aggregation operator takes a collection of values and returns a single value as a result. avg: average valuemin: minimum valuemax: maximum valuesum: su

36、m of valuescount: number of values F-distinct grouping Aggregation operation : G1, G2, , Gn F1( A1 ), F2 ( A2 ), , Fm ( Am )(E) E is any relational-algebra expression G1, G2 , Gn is a list of attributes on which to group Fi is an aggregate function Ai is an attribute name For each group ( g1, g2 , g

37、n ),the result has a tuple ( g1, g2 , gn , a1, a2 , am).,Aggregate Function Example,Relation r:,A,B, , ,C,7 7 3 10, sum(c) (r) count-distinct(c) (r),sum-C,27,Aggregate Function Example,Relation account grouped by branch-name:,branch-name sum (balance) (account),branch-name,account-number,balance,Per

38、ryridge Perryridge Brighton Brighton Redwood,A-102 A-201 A-217 A-215 A-222,400 900 750 750 700,branch-name,balance,Perryridge Brighton Redwood,1300 1500 700,branch-name sum (balance) as sum-balance , max(balance) as max-balance (account),branch-name,sum-balance,Perryridge Brighton Redwood,1300 1500

39、700,max-balance,900 750 700,Modification of the Database数据库的修改,The content of the database may be modified using the following operations: Deletion Insertion Updating All these operations are expressed using the assignment operator.,Deletion删除,A delete request is expressed similarly to a query, exce

40、pt instead of displaying tuples to the user, the selected tuples are removed from the database. Can delete only whole tuples; cannot delete values on only particular attributes A deletion is expressed in relational algebra by: r r E where r is a relation and E is a relational algebra query.,Deletion

41、 Examples,Delete all account records in the Perryridge branch. Depositor-schema=(customer-name, account-number) account account branch-name = “Perryridge” (account) Delete all loan records with amount in the range of 0 to 50 Loan-schema=( branch-name, loan-number ,amount) loan loan amount 0and amoun

42、t 50 (loan),Delete all accounts at branches located in Needham. Branch-schema=(branch-name, branch-city,assets) Account-schema=(branch-name, account-number, balance) r1 branch-city = “Needham” (account branch) r2 branch-name, account-number, balance (r1) r3 customer-name, account-number (r2 deposito

43、r) account account r2 depositor depositor r3,Insertion插入,To insert data into a relation, we either: specify a tuple to be inserted write a query whose result is a set of tuples to be inserted in relational algebra, an insertion is expressed by: r r E where r is a relation and E is a relational algeb

44、ra expression. The insertion of a single tuple is expressed by letting E be a constant relation containing one tuple.,Insertion Examples,Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch. Account-schema=(branch-name, account-number, balance)

45、 Depositor-schema=(customer-name, account-number) account account (“Perryridge”, A-973, 1200) depositor depositor (“Smith”, A-973),Provide as a gift for all loan customers in the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account. B

46、orrower-schema=(customer-name, loan-number) Loan-schema=( branch-name, loan-number ,amount) Account-schema=(branch-name, account-number, balance) Depositor-schema=(customer-name, account-number) r1 (branch-name = “Perryridge” (borrower loan) r2 branch-name, loan-number (r1)对account的插入也可以只用loan accou

47、nt account (r2(200) depositor depositor customer-name, loan-number, (r1),Updating更新,A mechanism to change a value in a tuple without changing all values in the tuple Use the generalized projection operator to do this task r F1, F2, , Fn, (r)对所有元组更新 Each Fi, is either the ith attribute of r, if the i

48、th attribute is not updated, or, if the attribute is to be updated,Fi is an expression, involving only constants and the attributes of r, which gives the new value for the attribute. If we want to select some tuples from r and to update only them,we can use the following expression: r F1, F2, , Fn,

49、(P (r) (r P (r)对部分元组更新,Update Examples,Make interest payments by increasing all balances by 5 percent. account BN,AN, BAL * 1.05 (account) where BN, AN and BAL stand for branch-name, account-number and balance, respectively. Pay all accounts with balances over $10,000 6 percent interest and pay all

50、others 5 percent. account BN,AN, BAL * 1.06 ( BAL 10000 (account) BN,AN,BAL * 1.05 (BAL 10000(account),Views视图,In some cases, it is not desirable for all users to see the entire logical model (i.e., all the actual relations stored in the database.) 某些情形下，没有必要让所有用户看到整个逻辑模型（如存储在数据库中的实际关系）。 Consider a

51、person who needs to know a customers loan number but has no need to see the loan amount. This person should see a relation described, in the relational algebra, by 有些人只需要知道客户的贷款号，而不必知道贷款额。这些人看到的关系用关系代数描述如下： customer-name, loan-number (borrower loan) Any relation that is not of the conceptual model b

52、ut is made visible to a user as a “virtual relation” is called a view. 不属于概念模型，但作为“虚关系”让用户查询的关系，称为视图。,View Definition视图定义,A view is defined using the create view statement which has the form：视图的定义使用如下形式的create view语句： create view v as where is any legal relational algebra query expression. The view

53、name is represented by v. 其中查询表达式是任意关系代数查询表达式，该视图名称表示为v。 Once a view is defined, the view name can be used to refer to the virtual relation that the view generates.一旦定义了视图，就可以用视图名指代视图产生的虚关系。 When a view is defined,the database system stores the definition of the view itself,rather than the result of

54、 evaluation of the relational-algebra expression that defines the view.Wherever a view relation is used in a query,it is replaced by the stored query expression. 视图定义时，数据库系统存储视图定义本身，而不是存储定义视图的关系代数表达式的求值结果。,View Examples,Consider the view (named all-customer) consisting of branches and their customer

55、s. create view all-customer as branch-name, customer-name (depositor account) branch-name, customer-name (borrower loan) We can find all customers of the Perryridge branch by writing: customer-name (branch-name = “Perryridge” (all-customer),Updates Through View通过视图进行更新,Database modifications express

56、ed as views must be translated to modifications of the actual relations in the database.用视图表达的数据库更新，必须将其转换成对数据库中实际关系的更新。 Consider the person who needs to see all loan data in the loan relation except amount. The view given to the person, branch-loan, is defined as: 对于只需要loan关系中除amount外的其他所有数据的人，可以定义

57、如下的视图： create view branch-loan as branch-name, loan-number (loan) Since we allow a view name to appear wherever a relation name is allowed, the person may write:由于允许视图名出现在任何关系名允许出现之处，如下的表达式： branch-loan branch-loan (“Perryridge”, L-37) The previous insertion must be represented by an insertion into

58、the actual relation loan from which the view branch-loan is constructed.前述插入需要转换成对构造视图branch-loan的实际关系loan进行插入。,Updates Through Views (Cont.),An insertion into loan requires a value for amount. The insertion can be dealt with by either:插入loan需要amount值。该插入可能会如下处理： rejecting the insertion and returning an error message to the user. 拒绝插入，并返回错误信息。 inserting a tuple (“Perryridge”, L-37, null) into the loan relation. 将amount的值视为空值后，再行插入。 Some updates through views are impossible to translate int