地砖种类

地砖种类 ????? www.365xueyuan.com ????? ?????? ?? 本文由zhaoxinyuelily贡献 文档可能在WAP端浏览体验不佳。建议您优先选择TXT，或下载源文件到本机查看。 PART 2 RELATIONAL DATABASES Chapter 2 Relational Model Introduction to Chapter 2 Relational data model relational data structure ( syntax) integrity constraints (semantic) constraints on attributes of schemas, e.g. value domain, type constraints on dependencies among attributes of a schema constraints on dependencies among attributes of different schemas relational normal forms (范式，chapter 7) operations on the model March 2010 Database System Concepts - Chapter2 Relational Model - 3 Main Parts in Chapter 2 Relational data structure (syntax ,?2.1.1-2.1.3) basic elements in relational data model, i.e. tables and attributes, and relationships among them Integrity constraints (semantic) constraints on keys in relational schema Relational algebra (关系代数，?2.2-2.5) (operations on the model) fundamental operations (?2.2) additional operations (?2.3) extended operations (?2.4) Using relational algebra operations to modify (delete, insert, updating) database (?2.6) March 2010 Database System Concepts - Chapter2 Relational Model 4 Main Parts in Chapter 2 (cont.) Categories of relational operations relational operations/arithmetic relational algebra relational calculus tuple relational calculus domain relational calculus tuple relational calculus (元组关系演算,?5.1) domain relational calculus (域关系演算, ?5.2) March 2010 Database System Concepts - Chapter2 Relational Model - 5 ?2.1 Structure of Relational Databases A relational database consists of a collection of relational tables a row in a table represents a relationship among a set of values a table is such a collection of relationships. 2.1.1 Basic structure Given sets D1, D2, „. Dn , a relation r is a subset of D1 ? D2 ? „ ? Dn r = {(a1, a2, „, an )} ? D1 ? D2 „..? Dn relation r = { tuple } /*元组 元组 March 2010 Database System Concepts - Chapter2 Relational Model - 6 2.1.1 Basic Structure (cont.) Attributes of relation r A1, A2, „, An domain of the relation’s attributes D1, D2, „. Dn the set of allowed values for each attribute Notes relation r in DBS is the limited set (有限集合) attributes in tuples are non-oredered (无序性) e.g. (d1, d2, „, dn) = (d2, d1, „, dn) the order in which the tuples appear in a relations is irrelevant Fig.2.1 vs. Fig.2.2 several attributes may have the same domain March 2010 Database System Concepts - Chapter2 Relational Model 7 2.1.1 Basic Structure (cont.) the special value null is a member of every domain. the null value causes complications in the definition of many operations Example customer-name = {Jones, Smith, Curry, Lindsay} customer-street = {Main, North, Park} customer-city = {Harrison, Rye, Pittsfield} then r = { (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield)} is a relation over customer-name ? customer-street ? customer-city March 2010 Database System Concepts - Chapter2 Relational Model 8 2.1.1 Basic Structure (cont.) A domain is atomic if its elements are considered to be indivisible all domains of R should be atomic, first formal norm e.g. multivalued attribute values are not atomic, { {1, 2}, {4}, {4, 6, 7} } e.g. composite attribute address={city, street, zipcode} is not atomic March 2010 Database System Concepts - Chapter2 Relational Model - 9 2.1.2 Database schema For attributes A1, A2, „, An , R = (A1, A2, „, An ) is a relation schema e.g. in Fig. 2.4, Customer-schema = (customer-name, customer-street, customer-city) orders of attributes is irrelevant r(R) is a relation on the relation schema R e.g. customer (customer-name, customer-street, customercity) on Customer-schema Relation instance the current values of a relation r at a particular time specified by a table March 2010 Database System Concepts - Chapter2 Relational Model 10 ?2.1.2 Relational schema (cont.) the term relation and relation instance are used interchangeably in the textbook Tabular representation of r(R) the current values of a relation r(R) (i,.e., relation instance of the relation r) are specified by a table an element t in set r is a tuple, represented by a row in a table tuple variable t, t[Ai] = the value of t on the attribute Ai , or t[{ Ai }] = the value set of t on the attribute set {Ai } e.g. in Fig.2.4, t2[customer-name]= Smith orders of tuples is irrelevant, i.e. tuples may be stored in an arbitrary order March 2010 Database System Concepts - Chapter2 Relational Model 11 ?2.1.2 Relational schema (cont.) attributes (or columns) schema t1 t2 t3 t4 customer-name customer-street Jones Smith Curry Lindsay Main North North Park customer-city Harrison Rye Rye Pittsfield tuples (or rows) Fig. 2.4 The Customer relation March 2010 Database System Concepts - Chapter2 Relational Model - 12 2.1.3 Keys Key : 键，码 A superkey is a set of one or more attributes that, taken collectively, can be used to identify uniquely a tuple in the relation Def. Given relation schema R(A1, A2, „, An), a subset K of R is the super key of R , if no two distinct tuples in relation r(R) have the same values on all attributes in K that is, if t1and t2 are in r and t1?t2, then t1[K]?t2[K] e.g. {customer-name} vs {customer-name, customer-street} K is a candidate key if K is minimal super key e.g.{customer-name} is a candidate key for Customer, assuming no two customers can possibly have the same name March 2010 Database System Concepts - Chapter2 Relational Model 13 2.1.3 Keys (cont.) A relation may have several candidate keys. Primary key is a candidate key chosen by the database designer as principal means to identify tuples within a relation Def. Primary attributes(主属性!) 主属性! 主属性 taking the key as a attribute set, the attributes in the primary/candidate key, i.e. the element of the primary key is called the primary attributes non-primary attributes(非主属性) (非主属性) March 2010 Database System Concepts - Chapter2 Relational Model - 14 2.1.3 Keys (cont.) Def. If X is one or more attributes in relation r1, and X is also the primary-key of another relation r2 taking Fig.2.8 as case studies X is called a foreign key from r1 referencing r2 由r1参照/关联r2的外键 r1 is called the referencing relation of the foreign key dependency 外键的参照/关联关系 r2 is called the referenced relation of the foreign key 外键的被参照/关联关系 e.g. branch-name in Branch-schema (Fig.2.8) and Accountschema March 2010 Database System Concepts - Chapter2 Relational Model 15 2.1.3 Keys (cont.) A database schema, along with primary key and foreign key dependency, can be depicted as the schema diagram e.g. Fig.2.8 node schema, attributes, primary key directed arc foreign key dependency, from referencing relation r1 to referenced relation r2 March 2010 Database System Concepts - Chapter2 Relational Model - 16 Fig. 2.8 Schema Diagram 2.1.4 Query language Query Language the 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 Pure languages form the underlying basis of query languages in practical uses March 2010 Database System Concepts - Chapter2 Relational Model 18 ?2.2 Fundamental Relation Algebra Operations The relational algebra is procedural query language a set of operations, take one or two limited relations as input and produce a new limited relation as output Three types of operations/operators on relations fundamental operations, additive operations, and extended operations similar to the logical operators in propositional calculus and predicate calculus fundamental : minimal complete set {?, ?}, or {?, ?} additive: implication ?, equivalence ? extended: ?, ? March 2010 Database System Concepts - Chapter2 Relational Model 19 ?2.2 Fundamental Relation Algebra Operations (cont.) Unary operations select, project and rename σ, ?, ρ Binary operations union, set difference, and cartesian product ?, —, ? Fig. 2.6 The loan relation March 2010 Database System Concepts - Chapter2 Relational Model 20 ?2.2 Fundamental Relation Algebra Operations (cont.) Fig. 2.7 The borrower Relation March 2010 Database System Concepts - Chapter2 Relational Model - 21 2.2.1 The Select Operation The select operation σ on r (R) selects tuples that satisfy a given predicate, resulting in a subset of r SQL statement Select * From r Where P E.g.1 σ branch-name=“Perryridge”(loan) Fig. 2.9 March 2010 Database System Concepts - Chapter2 Relational Model 22 2.2.1 The Select Operation (cont.) E.g.2 A B C 1 5 12 D 7 7 3 A B C 1 D 7 α α β β α β β β α β α β 23 10 23 10 σA=B ^ D > 5 (r) relation r Fig.2.01 March 2010 Database System Concepts - Chapter2 Relational Model 23 2.2.1 The Select Operation (cont.) σp (r) σp (r) = {t | t ? r and p(t)} p is called the selection predicate, and is a formula in propositional calculus consisting of terms connected by : ? (and), ? (or), ? (not) each term is one of op op or , where op is one of: =, ?, >, ?. <. ? note: 对同一元组中各属性取值的比较 March 2010 Database System Concepts - Chapter2 Relational Model 24 2.2.2 The Project Operation The project operation on r list some attributes and their values in relation r the duplicate rows are removed from the result, since relations are sets E.g.1 A B 10 20 30 40 C 1 1 1 2 A C 1 1 1 2 = A C 1 1 2 α α β β α α β β α β β relation r March 2010 Fig.2.02 ?A,C (r) 25 Database System Concepts - Chapter2 Relational Model - 2.2.2 The Project Operation (cont.) ?A1, A2, „, Ak (r): selecting columns corresponding to A1, A2 , Ak in relational table r SQL statement Select A1, A2 , Ak From r E.g. ?loan-number, amount (loan) in Fig. 2.10 March 2010 Database System Concepts - Chapter2 Relational Model - 26 2.2.3 Composition of Relational Operations Several relational algebra operations can be composed together into a relational algebra expression E.g.2. ?loan-number, amount (σ branch-name=“Perryridge (loan)) March 2010 Database System Concepts - Chapter2 Relational Model - 27 2.2.4 The Union Operation Union operation on relation r and s is defined as r ? s = {t | t ? r or t ? s} SQL statement r Union s E.g. A B 1 2 1 r s A B 2 3 A B 1 2 1 3 α α β α β α α β β Fig.2.03 March 2010 r?s 28 Database System Concepts - Chapter2 Relational Model - 2.2.4 The Union Operation (cont.) E.g.2. ?customer-name (depositor) ? ?customer-name (borrower) in Fig.2.11 For r ? s to be valid r, s must be of the same arity the domain of the ith attribute of r and ith attribute of s must be the same March 2010 Database System Concepts - Chapter2 Relational Model - 29 2.2.5 The Set-Difference Operation Set difference operation on relation r and s is defined as r – s = {t | t ? r and t ? s } SQL statement r Except s E.g.1 A B 1 2 1 r A B 2 3 s A B 1 1 α α β α β α β r–s Fig.2.04 March 2010 Database System Concepts - Chapter2 Relational Model 30 2.2.5 The Set-Difference Operation (cont.) E.g.2. ?customer-name (depositor) – ?customer-name (borrower) in Fig.2.12 Set differences must be taken between compatible relations. r and s must have the same arity attribute domains of r and s must be compatible March 2010 Database System Concepts - Chapter2 Relational Model - 31 2.2.6 The Cartesian-Product Operation E.g.1 t1 A B 1 2 C D 10 10 20 10 s E a a b b A B 1 1 1 1 2 2 2 2 C D 10 10 20 10 10 10 20 10 E a a b b a a b b α β t2 r α β β γ t SQL Statement: Select * From r, s Fig.2.05 α α α α β β β β α β β γ α β β γ r?s Cartesian-product operation on relation r and s r ? s = {t | t = t1 t2 and t1? r and t2 ? s } March 2010 Database System Concepts - Chapter2 Relational Model 32 2.2.6 The Cartesian-Product Operation (cont.) For relation r(R) and s(S), r ? s is a relation whose schema is R’ , which is the concatenation of R and S R (A1, A2, „, Am), S(B1, B2, „, Bn) R’ (A1, A2, „, Am, B1, B2, „, Bn) for t ? r ? s , there are t1 ? r and t2 ? s such that s, t[R’] = t1[R] t[R’] = t2[S] assume that attributes of r(R) and s(S) are disjoint. i.e. {A1, A2, „, Am} ? {B1, B2, „, Bn} = ? if attributes of r(R) and s(S) are not disjoint, then renaming must be used. March 2010 Database System Concepts - Chapter2 Relational Model 33 Fundamental Operations vs Select statement in SQL A typical SQL query has the form select A1, A2, „„, An from r1, r2, „„, rm where P Ais represent attributes ris represent relations P is a predicate Equivalent to the relational algebra expression ?? A1, A2, „„, An(σP (r1 x r2 x „„ x rm)) March 2010 Database System Concepts - Chapter2 Relational Model - 34 2.2.6 The Cartesian-Product Operation – An Example Find the names of all customers who have a loan at Perryridge branch Step1. information about branch in relation loan and about customer in relation borrower is needed, so borrower ? loan Step2. information about Perryridge branch and customers in this branch is needed σ loan.loan-number= borrower.loan-number (σ branch-name=“Perryridge”( borrower ? loan) ) March 2010 Database System Concepts - Chapter2 Relational Model - 35 2.2.6 The Cartesian-Product Operation – An Example (cont.) Step3. names of customers in Perryridge branch ?customer-name ( σ loan.loan-number= borrower.loan-number (σ branch-name=“Perryridge”( borrower ? loan) ) ) March 2010 Database System Concepts - Chapter2 Relational Model - 36 2.2.7 The Rename Operation The rename operation on relation r or relational algebra expression E ρ x (r) , ρ x (E) renaming r or E as X If a relational-algebra expression E has arity n, then ρx (A1, A2, „, An) (E) returns the result of expression E under the name X, and with the attributes renamed to A1, A2, „., An E.g. ρs (A1, A2, „, An) ( r (B1, B2, „, Bn)) = s (A1, A2, „, An) March 2010 Database System Concepts - Chapter2 Relational Model 37 2.2.7 The Rename Operation – An Example Find the largest balance in the bank Principle /*将对一个关系account中同一个 属性balance的不同取值 的比较转换为利用换名操作、迪卡尔乘积和选择操作对 关系 account和它的副本ρ d (account)在同一属性balance上 取值的比较, refer to Fig.2.0.6 Step1. account ? ρ d (account) Step2. all tuples that have non-largest balance values in account relation: σ account .balance < d.balance (account ? ρ d (account)) the result are tuple t2, t3, t6 Database System Concepts - Chapter2 Relational Model 38 March 2010 2.2.7 The Rename Operation – An Example (cont.) accountnumber 120005 130007 198032 branchname New York Brooklyn Perryridge balance 1 2 3 accountnumber 120005 130007 198032 branchname New York Brooklyn Perryridge balance 1 2 3 (a) account Fig.2.06-1 relation account and d March 2010 ρ d (account) (b) d = Database System Concepts - Chapter2 Relational Model - 39 account accountnumber 120005 d balanc e 1 1 1 2 2 2 3 3 3 branchname New York New York New York Brooklyn Brooklyn Brooklyn Perryridge Perryridge Perryridge accountnumber 120005 130007 198032 120005 130007 198032 120005 130007 198032 ? branchname New York Brooklyn Perryridge New York Brooklyn Perryridge New York Brooklyn Perryridge balance 1 2 3 1 2 3 1 2 3 t2 t3 120005 120005 130007 130007 t6 130007 198032 198032 198032 Fig.2.06-2 account ρ d (account) 2.2.7 The Rename Operation – An Example (cont.) Step3. all non-largest balance values in relation account ?account-balance ( σ account .balance < d.balance (account ? ρ d (account)) ) the result is {1, 2} Step4. All largest balance values in relation account ?balance (account) — ?account.balance ( σ account .balance < d.balance (account ? ρ d (account)) ) the result is {1, 2, 3} — {1, 2} = {3} March 2010 Database System Concepts - Chapter2 Relational Model 41 2.2.7 The Rename Operation – An Example (cont.) This query can also be solved by Select statement in SQL, by means of tuple variables in ?3.3.5 set comparison in ?3.7.2 aggregate function max in ?3.5 Homework and Questions 是否可用下式代替step4中的查询表达式 是否可用 ?account.balance ( σ account .balance >= d.balance (account ? ρ d (account)) ) ) 如何求解 “Find the smallest balance in the bank ” March 2010 Database System Concepts - Chapter2 Relational Model 42 2.2.8 Formal Definition of Relational Algebra Relational algebra expression E is defined as follows a basic expression is a relation r in the database, or a constant relation C= {c1, c2, .., cn}, which is written by listing its tuples in the relation a general expression is defined as: if E1 and E2 are relational algebra expressions, then E1? E2 E1— E2 E1 ? E2 March 2010 Database System Concepts - Chapter2 Relational Model 43 2.2.8 Formal Definition of Relational Algebra (cont.) σp (E1) ?s (E1), ρx (E1 ) are all relational algebra expressions March 2010 Database System Concepts - Chapter2 Relational Model - 44 ?2.3 Additional Operations Additional operations ?, set-intersection(集合交) ?, natural-join (连接) ?, division(除，商) ( ?，assignment(赋值) With respect to querying powers, additional operations are equivalent with fundamental operations, i.e. can be replaced (or re-represented ) by the six basic operations operations a additional operation can be replaced/re-represented by basic operations additional operations simplify representations of queries March 2010 Database System Concepts - Chapter2 Relational Model 45 2.3.1 The Set-Intersection Operation For relation r and s, set-intersection operation on r and s is defined as r ? s ={ t | t ? r and t ? s } r, s have the same arity attributes of r and s are compatible r ? s = r - (r - s) SQL statement intersect March 2010 Database System Concepts - Chapter2 Relational Model 46 2.3.2 The Natural-join Operation E.g.1 A B (1) α 1 β 2 γ 4 α 1 δ 2 r (1) r March 2010 C D α a γ a β b γ a β b A B α 1 α 1 α 1 α 1 δ 2 C D α a α a γ a γ a β b B (1) 1 3 1 2 3 E D a a a b b α β γ δ ? s E s α γ α γ δ Fig.2.07 47 Database System Concepts - Chapter2 Relational Model - 2.3.2 The Natural-join Operation (cont.) For relations r(R) and s(S), assuming R?S = {A1, A2, „, An }, the natural join of r and s, denoted by r s , is a relation on schema R?S, r s= ?R?S (σr.A1=s.A1?r.A2=s.A2 ?„?r.An=s.An ( r ? s )) E.g.1 (cont.) R = (A, B, C, D), S = (B, D , E ), r(R) and s(S), r s= ?r.A, r.B, r.C, r.D, s.E (σr.B = s.B ? r.D = s.D (r ? s)) defined on the schema (A, B, C, D, E) March 2010 Database System Concepts - Chapter2 Relational Model 48 2.3.2 The Natural-join Operation (cont.) Natural-join operation combine σ and ? on some relations into one operation to improve execution efficiency of query for more details, refer to Query Optimization in Chapter14 E.g.2 Find the names of all customers who have a loan at the bank , along with the loan number and the loan amount ?customer-name, loan_number, amount (σ loan.loan-number= borrower.loan-number ( borrower ? loan) ) Select customer-name, loan_number, amounts From borrower, loan Where loan.loan-number= borrower.loan-number March 2010 Database System Concepts - Chapter2 Relational Model 49 2.3.2 The Natural-join Operation (cont.) ?customer-name , loan_number, amounts ( borrower loan) Properties of natural join r ( s t ) =( r s ) t if R ?S = Φ, then r(R) s(S) = r(R) ? s(S) March 2010 Database System Concepts - Chapter2 Relational Model - 50 2.3.2 The Theta Join An extension to natural join, theta join operation r θ s = σθ( r ? s ) ,where θ is a predicate on attributes in R?S E.g.3 r AθB s = σAθB ( r ? s ) = {t1 t2 | (t1 ? r ) and ( t2 ? s ) and ( t1[A]θt2[B]) } , where θ ? { =, ?, ?, >, ?, < } March 2010 Database System Concepts - Chapter2 Relational Model - 51 2.3.2 The Theta Join (cont.) E.g.4 A B α 1 β 2 β 0 r r B?D C α γ α D E 1 α 3 β s s A B C D E α 1 α 1 α β 2 γ 1 α Fig.2.08 March 2010 Database System Concepts - Chapter2 Relational Model 52 2.3.3 The Division Operation E.g1. A B (a*b)?b = a B A 1 2 3 1 1 3 6 1 2 r (被除数) March 2010 α α α β γ δ ? ? β 1 2 s (除数) α β r?s (商) s ? r ( r? s ) ? Fig.2.09 ? Database System Concepts - Chapter2 Relational Model - 53 (被除数) R (A1,„, Am, B1,„, Bn) (除数) S (B1,„, Bn) (s01,„, s0n) (s11,„, s1n) „. (sk1,„, skn) s( S )= { ts } (商) R – S= (A1,„ , Am) (t1,„, tm) „. (t1,„, tm, s01,„, s0n) „. (t1,„, tm, s11,„, s1n) (v1,„, vm, s11,„, s0n) (t1,„, tm, sk1,„, skn) (t1,„, tm, u1,„, un) r( R ) = { tr } Fig.2.0.10 r?s={t} notes: 1. ( r? s ) ? s ? r ? 2. similar to: ( a * b ) ? b = a 2.3.3 The Division Operation (cont.) Formally, r?s = { t | t ? ? R-S (r) ? ? ts ? s ( t ts ? r ) } s, how to find r ? s Given r and refer to E.g.2 and E.g.3 March 2010 Database System Concepts - Chapter2 Relational Model - 55 2.3.3 The Division Operation (cont.) E.g.2 A a a b e e a B C D b b c d d b c e e c e d d f f d f e Fig.2.0.11 56 C D c e d f A a e B b d ts ? s ( S ) t ? r? s ? on R-S tr ? r ( R ) March 2010 Database System Concepts - Chapter2 Relational Model - 2.3.3 The Division Operation (cont.) E.g.3 A B C C D E D E a 1 α a γ α a 1 b 1 γ a γ γ a 1 γ b 1 s r?s γ a 1 γ b 3 γ a 1 γ b 1 Fig.2.0.12 β b 1 r For more examples about division and other relational algebra operations, refer to Appendix B. Examples About The Division Operation A B α a α a α a β a β a γ a γ a γ a March 2010 Database System Concepts - Chapter2 Relational Model 57 2.3.3 The Division Operation (cont.) As a additional operation, division can be defined in terms of the fundamental operations r(R) and s(S), S ? R r?s = ?R-S (r) –?R-S ( (?R-S (r) × s) – ?R-S, S(r)) ?R-S, S(r) simply reorders attributes of r ?R-S(?R-S (r) × s) – ?R-S,S(r)) gives those tuples t in ?R-S (r) such that for some tuple ts ? s, t ts ? r.!!! March 2010 Database System Concepts - Chapter2 Relational Model 58 2.3.3 The Division Operation (cont.) How to use division, ? fit for queries that include the phrase “for all” /*在属性„..上同时取„.的所有值 E.g4. Find customers who have an account at all the branches located in Brooklyn step1. obtain r1={all branches located in Brooklyn} ?branch-name (σbranch-city = “Brooklyn” (branch)) e.g. r1 March 2010 Database System Concepts - Chapter2 Relational Model - 59 2.3.3 The Division Operation (cont.) branch-name Downtown Brighton Fig. r1, branches located in Brooklyn account) Πcustomer-name, branch-name(depositor customerbranch- Fig. 2.23 r2, result of step2. find all pairs r2 = (customer-name, branch-name), by ?customer-name, branch-name (depositor account) e.g. r2 in Fig. 2.23 March 2010 Database System Concepts - Chapter2 Relational Model 60 2.3.3 The Division Operation (cont.) step3. find customers who appear in r2 with every branch name in r1, by using division operations. ?customer-name, branch-name (depositor account) ? ?branch-name (σbranch-city = “Brooklyn” (branch)) e.g. r2 ? r1 customer-name Johnson For more examples about division, refer to Appendix B. Examples About The Division Operation March 2010 Database System Concepts - Chapter2 Relational Model 61 ?2.3.4 The Assignment Operation Assign (?) relation algebra expression E to a relational variable var_r, i.e. denoting E as var_r. var_r ? E The assignment operation (?) provides a convenient way to express complex queries E.g. Write r ? s as temp1 ? ?R-S (r) temp2 ? ?R-S ((temp1 × s) – ?R-S,S (r)) result = temp1 – temp2 March 2010 Database System Concepts - Chapter2 Relational Model 62 ?2.3.4 The Assignment Operation (cont.) Assignment must always be made to a temporary relation variable assignments to permanent relations constitute a database modification March 2010 Database System Concepts - Chapter2 Relational Model - 63 ?2.4 Extended Relational Algebra Operations Generalized project, aggregate functions, outer join these operations extends query powers of basic and additive relational algebra operations 2.4.1 Generalized Project (一般化 扩展投影操作 一般化/扩展投影操作 一般化 扩展投影操作) Generalized project extends the projection operation by allowing arithmetic functions to be used in the projection list. ? F1, F2, „, Fn(E) , where E is any relational-algebra expression each of F1, F2, „, Fn are are arithmetic expressions involving constants and attributes in the schema of E. March 2010 Database System Concepts - Chapter2 Relational Model 64 2.4.1 Generalized Project (cont.) involving arithmetic operation on values of different attributes in one relation, e.g. E.Ai — E.Aj E.g. For relation credit-info(customer-name, limit, credit-balance) in Fig.2.24, find how much more each person can spend ?customer-name, (limit – credit-balance) as credit-available (credit-info) the result is shown in Fig. 2.25 March 2010 Database System Concepts - Chapter2 Relational Model - 65 Figure 2.24: The credit_info relation Figure 2.25 2.4.2 Aggregation Function Aggregation functions mapping of a collection of attribute values in a relation r into a single value, i.e. attribute domains ? domain avg: average value min: minimum value max: maximum value sum: sum of values count: number of values A convenient scheme for computing and comparing of the values in one attribute of the relation, in contrast with March 2010 Database System Concepts - Chapter2 Relational Model - 67 Select statement with aggregation functions in SQL In SQL Select statement, aggregation functions are embedded in select and having subclause E.g. 1 select branch-name, avg (salary) from pt-works group by branch-name having avg (salary) > 1600 refer to Fig.2.27 March 2010 Database System Concepts - Chapter2 Relational Model - 68 Select statement with aggregation functions in SQL (cont.) Select A1, A2, ., ag_fun(Ai),.., Aj1 , „, Aj2 ., Ak,„, An From r1, r2, „„, rm Where P1 Group by Aj1 , „, Aj2 having P2 notes: P1 is defined on all attributes of r1, r2, „„, rm Aj1 , „, Aj2 must be listed in select subclause P2 is defined on A1, A2, ., Ai,.., Aj1 , „, Aj2 ,„ , Ak, „, An, as the constraints on the group formed by Group by Aj1 , „, Aj2 , so ag_fun (Ak) appear in P2 March 2010 Database System Concepts - Chapter2 Relational Model 69 ?2.2.2 Aggregation Function (cont.) Aggregate operation in relational 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 (may be empty) a list of attributes in E on which to group (分组) , i.e. tuples in E are classified into several groups E1, E2, „, En, each group Ei is a subset of E, and tuples in each group Ei have the same values for G1,G2,„,Gn if G1, G2,„,Gn do not appears, aggregations are conducted on all tuples in E, that is, E are not classified into groups March 2010 Database System Concepts - Chapter2 Relational Model 70 ?2.2.2 Aggregation Function (cont.) each Ai is an attribute on which to conduct aggregation functions Fi also the attribute names for aggregation results aggregation functions Fi , e.g. avg, count, min, max, sum computing procedure separating E into groups on the basis of G1, G2 „, Gn computing F1, F2,„, Fn on each group, and return computing results denoted as A1, A2, „, An March 2010 Database System Concepts - Chapter2 Relational Model - 71 Fig. 2.26 pt-works relations Fig. 2.27 pt-works relations after grouping on branch_name ?2.2.2 Aggregation Function (cont.) E.g 2. For relation pt-works in Fig.2.26 branch-name g sum(salary) ( pt-works) in Fig.2.28 Fig.2.28 March 2010 Database System Concepts - Chapter2 Relational Model - 73 2.4.2 Aggregation Function (cont.) E.g 2. For relation pt-works in Fig.2.26 branch-name g sum(salary) as sum-salary, max(salary) as max-salary ( pt-works) in Fig. 2.30 Fig. 2.29 March 2010 Database System Concepts - Chapter2 Relational Model - 74 2.4.2 Aggregation Function (cont.) E.g.4 Relation account grouped by branch-name branch-name account-number balance Perryridge Perryridge Brighton Brighton Redwood branch-name A-102 A-201 A-217 A-215 A-222 400 900 750 750 700 branch-name sum-balance g sum(balance) as sum-balance (account) Perryridge Brighton Redwood 75 Fig.2.0.13 March 2010 1300 1500 700 Database System Concepts - Chapter2 Relational Model - 2.4.3 Outer Join For two relations r and s, r s may miss some information contained in r and s e.g. for relations employee, ft-work in Fig.2.30 in next slide, employee ft-work in Fig.2.31 has no information about Smith and Gates Fig. 2.31 employee March 2010 ft-work 76 Database System Concepts - Chapter2 Relational Model - Fig.2.30 The employee and ft-works Relations 2.4.3 Outer Join (cont.) Outer join is an extension of the join operation that avoids loss of information computes the join and then adds tuples from one relation that does not match tuples in the other relation to the result of the join. Left Outer Join employee ft-works in Fig. 2.32 Fig. 2.32 March 2010 Database System Concepts - Chapter2 Relational Model 78 2.4.3 Outer Join (cont.) Right Outer Join employee ft-works in Fig. 2.33 Full Outer Join employee ft-works in Fig. 2.34 March 2010 Database System Concepts - Chapter2 Relational Model - 79 ?2.5 Null Values Uses null values null signifies that the value is unknown or does not exist all comparisons involving null are (roughly speaking) false by definition How to deal with null in various relational algebra operations for more details, refer to Appendix C March 2010 Database System Concepts - Chapter2 Relational Model - 80 ?2.6 Modification of the Database On the basis of fundamental operations, additive operations, and extended operations, the contents of the database may be modified deletion insertion updating 2.4.1 Deletion Delete on relation r removes the selected tuples in r from the database March 2010 Database System Concepts - Chapter2 Relational Model - 81 2.6.1 Deletion (cont.) Deletion is expressed in relational algebra by r?r–E where r is a relation and E is a relational algebra query can delete only whole tuples; cannot delete values on only particular attributes SQL statement: delete from r where P E.g.1 Delete all account records in the “Perryridge ” branch account ? account – σ branch-name = “Perryridge” (account) E.g.2 Delete all loan records with amount in the range of 0 to 50 ” loan ? loan – σ amount ? 0 and amount ? 50 (loan) March 2010 Database System Concepts - Chapter2 Relational Model 82 2.6.1 Deletion (cont.) E.g.3 Delete all accounts at branches located in “Needham” r1 ? σ branch-city = “Needham” (account r2 ? ?branch-name, account-number, balance (r1) r3 ? ? customer-name, account-number (r2 account ? account – r2 depositor ? depositor – r3 depositor) branch) March 2010 Database System Concepts - Chapter2 Relational Model - 83 2.6.2 Insertion Insertion on relation r add some tuples into r. In relational algebra, an insertion is expressed by: r? r ? E where r is a relation and E is a relational algebra expression. SQL statement insert into r values (‘A-973’, ‘Brooklyn’, ‘1200’) insert into r select A1, A2, „, Am from r1, r2, .., rn where P March 2010 Database System Concepts - Chapter2 Relational Model 84 2.6.2 Insertion (cont.) The tuples to be added can be expressed by specifying explicitly tuples to be inserted, such as constant relations e.g. insertion of a single tuple is expressed by letting E be a constant relation containing only one tuple r ? r ? { < 1, 2, 3, „.., m>} writing a query E whose result is a set of tuples to be inserted refer to E.g.1 and E.g.2 in next slide March 2010 Database System Concepts - Chapter2 Relational Model - 85 2.6.2 Insertion (cont.) E.g.1. Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch account ? account ? {(“Perryridge”, A-973, 1200)} depositor ? depositor ? {(“Smith”, A-973)} E.g.2. 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 r1 ? (σbranch-name = “Perryridge” (borrower loan)) account ? account ? ?branch-name, account-number,200 (r1) depositor ? depositor ? ?customer-name, loan-number(r1) March 2010 Database System Concepts - Chapter2 Relational Model 86 customername acountnumber 100-103 100-300 200-320 branch-name Perryridge Perryridge Perryridge amount 1000 800 1200 r1 : Smith Austin John ?branch-name, account-number, 200 (r1) branch-name Perryridge Perryridge Perryridge account-number 100-103 100-300 200-320 balance 200 200 200 Fig.2.0.14 2.6.3 Updating Updating on DB provides a mechanism to change the value of tuples in relations In relational algebra, updating can be defined by using the generalized projection operator r ? ? F1, F2, „, Fn (r) each Fi is either the ith attribute of r, if the ith 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 March 2010 Database System Concepts - Chapter2 Relational Model - 88 2.6.3 Updating (cont.) E.g.1 Pay all accounts with balances over $10,000 6 percent interest and pay all others 5 percent account ? ? account-number, branch-name, balance * 1.06 (σ BAL > 10000 (account)) ? ? account-number, branch-name, balance * 1.05 (σBAL ? 10000 (account)) E.g.SQL statement: update account set balance = case when balance <= 10000 then balance *1.05 else balance * 1.06 end March 2010 Database System Concepts - Chapter2 Relational Model 89 Appendix A Turing Prize Winners in Database Areas 1973年图灵奖获得者 Charless W.Bachman 网状数据库之父 生于1924年，在GE期间， 主持设计开发了最早的网 状数据库系统IDS(1964) 积极推动与促进了数据库技术

标准

excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载

的 制定—— DBTG

报告

软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载

与DBTG系统结构 1981年图灵奖获得者Edgar Frank Codd 关系数据库 之父 生于1923年，IBM研究人员 “A Relational Model of Data for Large Shared Data Bank ” in Communication of the ACM in 1970, and relational database theory appeared March 2010 Database System Concepts - Chapter2 Relational Model 90 Appendix A Turing Prize Winners in Database Areas (cont.) 发展完善了关系数据库理论，提出了关系代数和关系 演算，研究了关系

规范

编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载

化，定义了 关系的基本操作， 奠定了SQL语言的基础 IBM 开展 System R项目。推出商用化的DB2和 SQL等 关系数据库产品 1998年图灵奖获得者James Gray 数据库技术和“事务处理”专家 生于1944年，先后供职于Bell, IBM, DEC, Microsoft IBM期间，参与和主持过IMS, System R, SDL/DS, DB2等项目的开发;在微软主持开发了MS SQL Server 7.0 March 2010 Database System Concepts - Chapter2 Relational Model 91 Appendix A Turing Prize Winners in Database Areas (cont.) 提出并完善了数据库“事务处理”技术，事务的 ACID特性，事务并发控制和死锁处理， 事务两阶段 提交

协议

离婚协议模板下载合伙人协议 下载渠道分销协议免费下载敬业协议下载授课协议下载

，事务故障恢复机制 1998年ACM授予IBM System R软件系统将6位 获奖人 之一 March 2010 Database System Concepts - Chapter2 Relational Model - 92 Appendix B Examples About Division Operation E.g.1. For the database shown in Fig. 2.9, find all customers who have an account from at least the “Downtown” and the Uptown” branches query1 ?customer-name(σbranch-name=“Downtown”(depositor account)) ? ?customer-name(σbranch-name=“Uptown”(depositor account)) March 2010 Database System Concepts - Chapter2 Relational Model - 93 Appendix B Examples About Division Operation (cont.) query 2 ?customer-name, branch-name (depositor ? account) ρtemp(branch-name) ({(“Downtown”), (“Uptown”)}) temp branch-name Downtown Uptown March 2010 Database System Concepts - Chapter2 Relational Model - 94 Appendix B Examples About Division Operation (cont.) 学生课程数据库中包括3个关系 S(S#, Sname, Age, Sex), C(C#, Cname, Teacher), SC(S#, C#, Grade) E.g.2. 检索至少选修了课程DBS和课程OS的学生的学号: 方法1 ρ temp (A1, A2, A3, A4, A5, A6) (SC ? SC) ?A1 (σA1=A4?A2=“DBS”? A5=“OS” temp) A1/S# 001 001 001 001 001 March 2010 A2/C# DBS DBS DBS DBS DBS A3/Grade 90 90 90 90 90 A4/S# 001 001 001 002 002 A5/C# DBS OS Compiler DBS OS A6/Grade 90 85 85 80 90 95 temp: Database System Concepts - Chapter2 Relational Model - Appendix B Examples About Division Operation (cont.) 方法2 设立临时关系 temp(C#)={1, 3}, 或: temp= ?C# (σC# =“1” OR C# =“3” ( C ) ) ? S# , C# ( SC ) ? temp E.g.3 检索选修“数据库”课程的学生的姓名、学号和成绩 ? Sname, S#, Grade (σCname =“数据库” ( S E.g.4 检索“李强”不学课程的课程号 SC C )) ?C# (C ) — ?C# (σSname =“李强” (S) March 2010 SC ) 96 Database System Concepts - Chapter2 Relational Model - Appendix B Examples About Division Operation (cont.) E.g.5 检索选修全部课程的学生的姓名 全部课程 All-course= ?C# (C ) ? Sname ( S (? S# , C# ( SC ) ? All-course))) E.g.6 检索至少选修“王老师”所授全部课程的学 生的姓名 王老师”所授全部课程 temp= ?C# (σTeacher =“王老师” (C )) ? Sname (S ( ? S# , C# ( SC) ? temp)) March 2010 Database System Concepts - Chapter2 Relational Model - 97 Appendix B Examples About Division Operation (cont.) 考虑以下关系模式 供应商 (Supplier): S(S#, sname, scity) 零件(Part): P(P#, pname, color, weight) 工程 (Job): J(J#, jname, jcity) 供应关系SPJ: SPJ(S#, P#, J# , QTY) QTY: 供应商S#为工程J#提供零件P#的零件供应量 用基本和附加关系代数完成下列查 询 查出能同时提供零件P1和P2的供应商号 设立临时关系 temp= ?P# (σpname=“P1” OR pname =“P2” ( P ) ) ? S# , P# ( SPJ ) ? temp March 2010 Database System Concepts - Chapter2 Relational Model 98 Appendix B Examples About Division Operation (cont.) 查出为某项工程供应红色的、零件号等于2013的零件，并 且供应量大于1000件的供应 商的S# ? S#(σcolor=“red” AND P#=“P1” AND QTY>1000 (SPJ P)) 找出至少用了S#=“Simens”的供应商所提供的全部零件的 工程的工程号 ? J#, P#(SPJ) ? ? P#(σS#=“Simens” (S SPJ)) March 2010 Database System Concepts - Chapter2 Relational Model - 99 Appendix B Examples About Division Operation (cont.) 找出下述条件的工程的工程号: 至少有一个与该工程不在 同一城市的供应者向它提供 零件 ? P#(σscity ? jcity (S SPJ P)) 找出既不提供P#=001的零件、也不提供P#=002的零件的供 应商的S#( ? S#(S) —? S#(σP#=“001” OR P#=“002” (SPJ)) March 2010 Database System Concepts - Chapter2 Relational Model - 100 Appendix C Null Values It is possible for tuples to have a null value, denoted by null, for some of their attributes null signifies an unknown value or that a value does not exist. The result of any arithmetic expression involving null is null. Aggregate functions simply ignore null values is an arbitrary decision. Could have returned null as result instead. we follow the semantics of SQL in its handling of null values March 2010 Database System Concepts - Chapter2 Relational Model - 101 Appendix C Null Values(cont.) For duplicate elimination and grouping, null is treated like any other value, and two nulls are assumed to be the same Alternative: assume each null is different from each other Both are arbitrary decisions, so we simply follow SQL Comparisons with null values return the special truth value unknown if false was used instead of unknown, then not (A < 5) would not be equivalent to A >= 5 Three-valued logic using the truth value unknown: OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown March 2010 Database System Concepts - Chapter2 Relational Model 102 Appendix C Null Values(cont.) AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown NOT: (not unknown) = unknown In SQL “P is unknown” evaluates to true if predicate P evaluates to unknown Result of select predicate is treated as false if it evaluates to unknown March 2010 Database System Concepts - Chapter2 Relational Model - 103 Have a rest March 2010 Database System Concepts - Chapter2 Relational Model - 104 1