【正文】 er,branch_name ?D2(ame,a,bname)(,account_number,branch_name ?=(depositor account)) (?= ? ? ( ) DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 50 Additional Operations ?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 schema R – S = (A1, … , Am) ? Defined as: r ? s = { t | t ? π RS(r) ? ? u ? s → tu ? r ) } DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 51 Additional Operations ?Division Operation(除 ) Relations r, s: r ? s: B A 1 2 A B ? ? ? ? ? ? ? ? ? ? ? 1 2 3 1 1 1 3 4 6 1 2 r s ? ? DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 52 Additional Operations ?Division Operation(除 ) ? Another Defined as: )}(|][{ ][ SYRtXtSR YXtrr r ??????Yx = {y | tr∈ R ? x = tr[X] ? y = tr[Y] } // Yx : x的像集 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 D a b E 1 1 r s r ? s: A B C Relations r, s: ? a ? ? a ? DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 53 Additional Operations ?Division Operation(除 ) ? Property ? Let q is the result of r ? s ? Then q is the largest relation satisfying q s ? r ? Definition in terms of the basic algebra operation Let r(R) and s(S) be relations, and let S ? R r ? s = π RS (r) –πRS ( (π RS (r) s) – πRS,S(r) ) DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 54 Example Queries ? Find all customers who have an account at all branches located in Brooklyn city. branch_name branch_city assets Brighton Brooklyn 7100000 Downtown Brooklyn 9000000 Mianus Horseneck 400000 North Town Rye 3700000 Perryridge Horseneck 1700000 Pownal Bennington 300000 Redwood PaloAlto 2100000 Round Hill Horseneck 8000000 branch πbranch_name (?branch_city = “Brooklyn” (branch)) 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 Johnson Smith Hayes Turner Johnson Lindsay Jones ? π customer_name, branch_name (depositor account) ? DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 55 Extended Operations ?Outer join Operation(外連接 ) ? The (left) Outer join is a join on which tuples from R that do not have matching values in the mon attributes of S are alse included in the result relation. Missing values in the second relation are set to null. A B ? ? 1 2 s A C ? ? ? ? 10 10 20 10 D a a b b r A C D B ? 10 a 1 ? 10 a 2 ? 20 b 2 ? 10 b r s ? Notation: r s (left outer join ) DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 56 Extended Operations loan_number branch_name amount L170 Downtown 3000 L230 Redwood 4000 L260 Perryridge 1700 loan customer_name loan_number Jones L170 Smith L230 Hayes L155 borrower loan_number branch_Name amount customer_name L170 Downtown 3000 Jones L230 Redwood 4000 Smith ? Inner Join: loan borrower ? Left Outer Join : loan borrower loan_number branch_Name amount customer_name L170 Downtown 3000 Jones L230 Redwood 4000 Smith L260 Perryridge 1700 NULL DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 57 Extended Operations loan_number branch_name amount L170 Downtown 3000 L230 Redwood 4000 L260 Perryridge 1700 loan customer_name Loan_number Jones L170 Smith L230 Hayes L155 borrower loan_number nranch_name amount customer_name L170 Downtown 3000 Jones L230 Redwood 4000 Smith L155 Hayes loan_number branch_name amount customer_name L170 Downtown 3000 Jones L230 Redwood 4000 Smith L260 Perryridge 1700 L155 Hayes ? Right Outer Join : loan borrower ? Full Outer Join : loan borrower DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 58 課后閱讀 ? ? ? DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 59 作業(yè) 1 ? , (關(guān)系模式 和 每小題 必須抄在作業(yè)本上 ) Database Systems DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 61 Unit 2 The Relational Model ?Overview ?Relational Model ?Relational Algebra(關(guān)系代數(shù) ) ?Relational Calculus(關(guān)系演算 ) (教材 ) DataBase System Su Xiangyang , School of Computer Science amp。 Engineering , Xidian University , China 62 Relational calculus ? Relational Calculus(關(guān)系演算 ) Based on a branch of mathematical logical called the predicate calculus(謂詞演算 ). ? Relational Calculus is A nonprocedural query language, where each query is of the form : {t | P(t) } ? It is the set of all tuples t such that predicate P is true for t ? t is a tuple variable, t[A] denotes the value of tuple t on attribute A, called ponent of t on attribute A(元組 t在屬性 A上的分量值 ) ? R(t) de