[工学]ch02-Relation model.ppt

Database Systems DataBase System Su Xiangyang , School of Computer Science that is, indivisible Note: multivalued attribute(多值属性) values are not atomic Note: composite attribute (组合属性) values are not atomic FThe special value null is a member of every domain The null value causes complications in the definition of many operations 5 DataBase System Su Xiangyang , School of Computer Science the following are all relational-algebra 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 35 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FBanking 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) 36 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind the loan number for each loan of an amount greater than $1200. loan_numberbranch_nameamount L-11Round Hill900 L-14Downtown1500 L-15Perryridge1500 L-16Perryridge1300 L-17Downtown1000 L-23Redwood2000 L-93Mianus500 loan (amount 1200 (loan) loan_number L-14 L-15 L-16 L-23 注：除特别要求外，写查询表 达式时不需要写出结果集！ loan_number ( ) 37 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind the customers name who have at least one deposit of a balance greater than $700. account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 A-305Round Hill350 A-201Brighton900 A-222Redwood700 A-217Brighton750 account customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor balance700(acco unt) account_number( )deposito r Customer_name(account.account_number = depositor.account_number ( ) ) Customer_name(account.account_number = depositor.account_number balance700 ( account depositor ) ) Q1: Q2: 38 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind all customers who have at least two deposits. customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor D1.cnam e D1.a # D2.cnam e D2.a # HayesA-102HayesA-102 HayesA-102JohnsonA-101 HayesA-102JohnsonA-201 JohnsonA-101JohnsonA-201 JohnsonA-201JohnsonA-101 D1(cname,a#)(depositor) D2(cname,a#)( depositor) D1.cname(D1.cname=D2.cname D1.a# B.b (A B)? A1.balance (A1.balance 700(acco unt) account_number( )deposito r Customer_name(account.account_number = depositor.account_number ( ) ) Q1: Customer_name(account.account_number = depositor.account_number balance700 ( account depositor ) ) Q2: Find the customers name who have at least one deposit of a balance greater than $700. account depositor Q3:Customer_name(balance700 ( ) ) 47 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries F(exercise)Find all customers who have at least two deposits in different branch. customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 A-305Round Hill350 A-201Brighton900 A-222Redwood700 A-217Brighton750 account D1.Customer_name( D1.Customer_name =D2. Customer_name D1. Account_numberD2. branch_name( ) D2(depositor account)D1(depositor account) 48 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries F(exercise)Find all customers who have at least two deposits in different branch. customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 A-305Round Hill350 A-201Brighton900 A-222Redwood700 A-217Brighton750 account depositor.account_number=account.account_number(depositor account) D1(cname,a#,bname)(depositor.customer_name,account_number,branch_n ame D2(cname,a#,bname)(depositor.customer_name,account_number,branch_name depositor.account_number=account.account_number(depositor account) D1.cname(D1.cname=D2.cname D1.a#D2.bname( ) 49 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Additional Operations FDivision 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 R-S(r) u s tu r ) 50 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Additional Operations FDivision Operation(除) Relations r, s: r s: B A 1 2 AB 1 2 3 1 1 1 3 4 6 1 2 r s 51 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Additional Operations FDivision Operation(除) Another Defined as: Yx = y | trR x = trX y = trY / Yx : x的像集 AB a a a a a a a a CD 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: ABC Relations r, s: aa 52 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Additional Operations FDivision 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 = R-S (r) R-S ( ( R-S (r) s) R-S,S(r) ) 53 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind all customers who have an account at all branches located in Brooklyn city. branch_namebranch_cityassets BrightonBrooklyn7100000 DowntownBrooklyn9000000 MianusHorseneck400000 North TownRye3700000 PerryridgeHorseneck1700000 PownalBennington300000 RedwoodPaloAlto2100000 Round HillHorseneck8000000 branch branch_name (branch_city = “Brooklyn” (branch) account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 A-305Round Hill350 A-201Brighton900 A-222Redwood700 A-217Brighton750 account customer_ name Johnson Smith Hayes Turner Johnson Lindsay Jones customer_name, branch_name (depositor account) 54 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Extended Operations FOuter join Operation(外连接) The (left) Outer join is a join on which tuples from R that do not have matching values in the common attributes of S are alse included in the result relation. Missing values in the second relation are set to null. AB 1 2 s AC 10 10 20 10 D a a b b r ACDB 10a1 10a2 20b2 10b r s Notation: r s (left outer join ) 55 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Extended Operations loan_numberbranch_nameamount L-170Downtown3000 L-230Redwood4000 L-260Perryridge1700 loan customer_nameloan_number JonesL-170 SmithL-230 HayesL-155 borrower loan_numberbranch_Nameamount customer_name L-170Downtown3000Jones L-230Redwood4000Smith FInner Join: loan borrower FLeft Outer Join : loan borrower loan_numberbranch_Nameamount customer_name L-170Downtown3000Jones L-230Redwood4000Smith L-260Perryridge1700NULL 56 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Extended Operations loan_numberbranch_nameamount L-170Downtown3000 L-230Redwood4000 L-260Perryridge1700 loan customer_nameLoan_number JonesL-170 SmithL-230 HayesL-155 borrower loan_numbernranch_nameamount customer_name L-170Downtown3000Jones L-230Redwood4000Smith L-155Hayes loan_numberbranch_nameamount customer_name L-170Downtown3000Jones L-230Redwood4000Smith L-260Perryridge1700 L-155Hayes FRight Outer Join : loan borrower FFull Outer Join : loan borrower 57 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China 课后阅读 F2.3 F2.4.3 F2.5 58 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China 作业1 F1.教材 2.1, 2.5(关系模式和每小题必须抄在作业本上) 59 Database Systems DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Unit 2 The Relational Model Overview Relational Model Relational Algebra(关系代数) Relational Calculus(关系演算) (教材5.1节) 61 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Relational calculus FRelational Calculus(关系演算) Based on a branch of mathematical logical called the predicate calculus(谓词演算 ). FRelational 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, tA denotes the value of tuple t on attribute A, called component of t on attribute A(元组t在 属性A上的分量值) R(t) denotes that tuple t is in relation R P is a formula of the predicate calculus 62 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Relational calculus FTuple-relational-Calculus Formula R(t) is an atom formula(原子公式) (or or ) is an atom formula is one of the comparison operators: (e.g., , , , , , ) if P1, P2 are formulae, then P1, P1 P2, P1 P2, P1 P2 are formulae x(P(x), x (P(x) are formulae FSets of equivalent expressions: P1 P2 (P1 P2 ) P1 P2 P1 P2 t ( P(t) ) t ( P(t) ) 63 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Relational calculus FExample 987 654 321 CBA R 965 643 321 CBA S R1= t | S(t) t1 2 CBA 965 643 R2= t | R(t) S(t) ABC 456 789 R3= t | S(t) u(R(u) t3u1) ABC 456 789 65 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Relational calculus FExample 987 654 321 CBA R 965 643 321 CBA S R4= t | R(t) u(S(u)t3u1) ABC 456 789 R5= t | u v( R(u) S(v) u1v2 t1=u2 t2=v3 t3=u1 ) Process： 1. Ensure the schema of result； 2. Obtain tuples according the predicate calculus . R.AS.CR.B 435 798 768 738 66 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FBanking 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) 67 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind the loan_number, branch_name, and amount for loans of over $1200. loan_numberbranch_nameamount L-11Round Hill900 L-14Downtown1500 L-15Perryridge1500 L-16Perryridge1300 L-17Downtown1000 L-23Redwood2000 L-93Mianus500 loan t | loan(t) tamount 1200 Find the loan number for each loan of an amount greater than $1200. t | u( loan(u) u amount 1200 t loan_number = s loan_number ) 68 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind the customers name who have at least one deposit of a balance greater than $700. account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 A-305Round Hill350 A-201Brighton900 A-222Redwood700 A-217Brighton750 account customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor t | u( depositor(u) v( account(v) uaccount_number = vaccount_number vbalance 700 ) tcustomer_name = ucustomer_name ) 69 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind all customers who have at least two deposits. customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor t | u v( depositor(u) depositor(v) ucustomer_name = vcustomer_name uaccount_number uaccount_number tcustomer_name = u customer_name ) uv 70 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind the largest account balance. account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 account account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 account u v t | u ( account(u) v( account(v) ubalance vbalance) tbalance = u balance ) Q1: Q2: t | u ( account(u) v( account(v) ubalance vbalance) tbalance = u balance ) x( P(x) Q(x) ) x(P(x) Q(x) ) 71 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries F(exercise)Find all customers who have at least two deposits in different branch. customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 SmithA-215 TurnerA-305 depositor account_ number branch_ name balance A-101Downtown500 A-215Mianus700 A-102Perryridge400 A-305Round Hill350 A-201Brighton900 A-222Redwood700 A-217Brighton750 account t | u x ( depositor(u) account(x) uaccount_number = xaccount_number v y ( depositor(v) account(y) vaccount_number = yaccount_number ucustomer_name = vcustomer_name xbranch_name ybranch_name uaccount_number vaccount_number ) tcustomer_name = u customer_name ) u v x y 72 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind all customers who have an account at all branches located in Brooklyn city. branch_namebranch_city BrightonBrooklyn DowntownBrooklyn MianusHorseneck North TownRye PerryridgeHorseneck PownalBennington Round HillHorseneck branch account_ number branch_ name A-101Downtown A-215Mianus A-102Perryridge A-305Round Hill A-201Brighton A-217Brighton account t | u ( depositor(u) v (branch(v) vbranch_city = Brooklyn x(depositor(x) xcustomer_name = ucustomer_name w( account(w) waccount_number = xaccount_number wbranch_name = vbranch_name) ) tcustomer_name = ucustomer_name ) customer_ name account_ number HayesA-102 JohnsonA-101 JohnsonA-201 JonesA-217 LindsayA-222 TurnerA-305 depositor 73 DataBase System Su Xiangyang , School of Computer Science & Engineering , Xidian University , China Example Queries FFind all customers who have an account at all branches located in Brooklyn city. t | u ( depositor(u) v (branch(v) vbranch_city = Brooklyn x(depositor(x) xcustomer_name = ucustomer_name w( account(w) waccount_number = xaccount_number wbranch_name = vbranch_name) ) ) tcustomer_name = u customer_name ) 等价转换 t | u ( depositor(u) v (branch(v) vbranch_city = Brooklyn x(depositor(x) xcustomer_name = ucustomer_name w( account(w) waccount_number = xaccount_number wbranch_name = vbranch_name)