【文章內(nèi)容簡介】 ? ? 2 3 s A B ? ? ? ? 1 2 1 3 The 1st column of r deals with the same type of values(domain) as does the 1st column of s ,and 2nd column also, and also for all attributes. DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 27 Basic operators ?Difference Operation(差操作 ) ? Notation: r – s r minus s ? Defined as: r – s = {t | t ? r ? t ? s} ? Set differences must be taken between patible relations. ? r and s must have the same degree ? attribute domains of r and s must be patible DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 28 Basic operators ?Difference Operation (差操作 ) Relations r, s: A B ? ? ? 1 2 1 r A B ? ? 2 3 s r – s : A B ? ? 1 1 DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 29 Basic operators ?CartesianProduct Operation(笛卡爾積操作 ) ? Notation: r s r times s ? Defined as: r s = {tq | t ? r ? q ? s} ? Assume that attributes of r(R) and s(S) are disjoint. (That is, R ? S = ? , NO same attribute in R and S). ? If attributes of r(R) and s(S) are not disjoint, then renaming must be used. DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 30 Basic operators ?CartesianProduct Operation(笛卡爾積操作 ) Relations r, s: r 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 ? ? 1 2 r C D ? ? ? ? 10 10 20 10 E a a b b s DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 31 Basic operators ?Rename Operation(重命名操作 ) ? Allows us to name, and therefore to refer to, the results of relationalalgebra 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 relationalalgebra expression E has degree n, then ?x (A1, A2, … , An) (E) A1, A2, … ., An returns the result of expression E under the name X, and with the attributes renamed to A1, A2, … ., An. DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 32 課后閱讀 ? ? Database Systems DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 34 Unit 2 The Relational Model ?Relational Model ?Relational Algebra(關(guān)系代數(shù) ) ?Relational Calculus(關(guān)系演算 ) DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 35 Relational Algebra expression ? A basic expression in the relational algebra consists of either one of the following: ? A relation in the database ? A constant relation ? Let E1 and E2 be relationalalgebra expressions。 the following are all relationalalgebra expressions: ? E1 ? E2 ? E1 – E2 ? E1 E2 ??p(E1), 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 DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 36 Example Queries ?Banking DataBase: branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) depositor (customer_name, account_number) account (account_number, branch_name, balance) borrower (customer_name, loan_number) loan (loan_number, branch_name, amount) DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 37 Example Queries ? Find the loan number for each loan of an amount greater than $1200. loan_number branch_name amount L11 Round Hill 900 L14 Downtown 1500 L15 Perryridge 1500 L16 Perryridge 1300 L17 Downtown 1000 L23 Redwood 2022 L93 Mianus 500 loan (?amount ?1200? (loan) loan_number L14 L15 L16 L23 注： 除特別要求外，寫查詢表達式時 不需要 寫出結(jié)果集！ πl(wèi)oan_number ( ) DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 38 Example Queries ? Find the customers name who have at least one deposit of a balance greater than $700. account_ number branch_ name balance A101 Downtown 500 A215 Mianus 700 A102 Perryridge 400 A305 Round Hill 350 A201 Brighton 900 A222 Redwood 700 A217 Brighton 750 account customer_ name account_ number Hayes A102 Johnson A101 Johnson A201 Jones A217 Lindsay A222 Smith A215 Turner A305 depositor ?balance’700’(account) π account_number( ) depositor πCustomer_name(? = ( ) ) πCustomer_name(? = ? balance’700’ ( account depositor ) ) Q1: Q2: DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 39 Example Queries ? Find all customers who have at least two deposits. customer_ name account_ number Hayes A102 Johnson A101 Johnson A201 Jones A217 Lindsay A222 Smith A215 Turner A305 depositor Hayes A102 Hayes A102 Hayes A102 Johnson A101 Hayes A102 Johnson A201 …… …… …… …… Johnson A101 Johnson A201 …… …… …… …… Johnson A201 Johnson A101 …… …… …… …… ?D1(ame,a)(depositor) ?D2(ame,a)( depositor) (?= ∧ ( ) ) DataBa