《数据库管理系统：ch02 Relational Model》由会员分享，可在线阅读，更多相关《数据库管理系统：ch02 Relational Model（96页珍藏版）》请在金锄头文库上搜索。
1、Database System Concepts, 5th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use Chapter 2: Relational ModelSilberschatz, Korth and Sudarshan2.2Database System Concepts - 5th Edition, June 15, 2005Chapter 2: Relational ModelnStructure of Relational DatabasesnFundamental Relatio
2、nal-Algebra-OperationsnAdditional Relational-Algebra-OperationsnExtended Relational-Algebra-OperationsnNull ValuesnModification of the DatabaseSilberschatz, Korth and Sudarshan2.3Database System Concepts - 5th Edition, June 15, 2005Example of a RelationSilberschatz, Korth and Sudarshan2.4Database Sy
3、stem Concepts - 5th Edition, June 15, 2005Basic StructurenFormally, given sets D1, D2, . Dn a relation r is a subset of D1 x D2 x x DnThus, a relation is a set of n-tuples (a1, a2, , an) where each ai DinExample: Ifcustomer_name = Jones, Smith, Curry, Lindsaycustomer_street = Main, North, Parkcustom
4、er_city = Harrison, Rye, PittsfieldThen r = (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield) is a relation over customer_name x customer_street x customer_citySilberschatz, Korth and Sudarshan2.5Database System Concepts - 5th Edition, June 15, 2005Attrib
5、ute TypesnEach attribute of a relation has a namenThe set of allowed values for each attribute is called the domain of the attributenAttribute values are (normally) required to be atomic; that is, indivisiblelNote: multivalued attribute values are not atomiclNote: composite attribute values are not
6、atomicnThe special value null is a member of every domainnThe null value causes complications in the definition of many operationslWe shall ignore the effect of null values in our main presentation and consider their effect laterSilberschatz, Korth and Sudarshan2.6Database System Concepts - 5th Edit
7、ion, June 15, 2005Relation SchemanA1, A2, , An are attributesnR = (A1, A2, , An ) is a relation schemaExample:Customer_schema = (customer_name, customer_street, customer_city)nr(R) is a relation on the relation schema RExample:customer (Customer_schema)Silberschatz, Korth and Sudarshan2.7Database Sy
8、stem Concepts - 5th Edition, June 15, 2005Relation InstancenThe current values (relation instance) of a relation are specified by a tablenAn element t of r is a tuple, represented by a row in a tableJonesSmithCurryLindsaycustomer_nameMainNorthNorthParkcustomer_streetHarrisonRyeRyePittsfieldcustomer_
9、citycustomerattributes(or columns)tuples(or rows)Silberschatz, Korth and Sudarshan2.8Database System Concepts - 5th Edition, June 15, 2005Relations are Unorderedn Order of tuples is irrelevant (tuples may be stored in an arbitrary order)n Example: account relation with unordered tuplesSilberschatz,
10、Korth and Sudarshan2.9Database System Concepts - 5th Edition, June 15, 2005DatabasenA database consists of multiple relationsnInformation about an enterprise is broken up into parts, with each relation storing one part of the informationaccount : stores information about accounts depositor : stores
11、information about which customer owns which account customer : stores information about customersnStoring all information as a single relation such as bank(account_number, balance, customer_name, .)results inlrepetition of information (e.g., a customers own several accounts)lthe need for null values
12、 (e.g., represent a customer without an account)nNormalization theory (Chapter 7) deals with how to design relational schemasSilberschatz, Korth and Sudarshan2.10Database System Concepts - 5th Edition, June 15, 2005The customer RelationSilberschatz, Korth and Sudarshan2.11Database System Concepts -
13、5th Edition, June 15, 2005The depositor RelationSilberschatz, Korth and Sudarshan2.12Database System Concepts - 5th Edition, June 15, 2005KeysnLet K RnK is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) lby “possible r ” we mean a relation r
14、that could exist in the enterprise we are modeling.lExample: customer_name, customer_street and customer_name are both superkeys of Customer, if no two customers can possibly have the same name.nK is a candidate key if K is minimalExample: customer_name is a candidate key for Customer, since it is a
15、 superkey (assuming no two customers can possibly have the same name), and no subset of it is a superkey.nPrimary Key Silberschatz, Korth and Sudarshan2.13Database System Concepts - 5th Edition, June 15, 2005Query LanguagesnLanguage in which user requests information from the database.nCategories of
16、 languageslProcedurallNon-procedural, or declarativen“Pure” languages:lRelational algebralTuple relational calculuslDomain relational calculusnPure languages form underlying basis of query languages that people use.Silberschatz, Korth and Sudarshan2.14Database System Concepts - 5th Edition, June 15,
17、 2005Relational AlgebranProcedural languagenSix basic operatorslselect: lproject: lunion: lset difference: lCartesian product: xlrename: nThe operators take one or two relations as inputs and produce a new relation as a result.Silberschatz, Korth and Sudarshan2.15Database System Concepts - 5th Editi
18、on, June 15, 2005Select Operation ExamplenRelation rABCD15122377310A=B D 5 (r)ABCD123710Silberschatz, Korth and Sudarshan2.16Database System Concepts - 5th Edition, June 15, 2005Select OperationnNotation: p(r)np is called the selection predicatenDefined as: p(r) = t | t r and p(t)Where p is a formul
19、a in propositional calculus consisting of terms connected by : (and), (or), (not)Each term is one of:op or where op is one of: =, , , . 1200 (loan)loan_number (amount 1200 (loan)Silberschatz, Korth and Sudarshan2.29Database System Concepts - 5th Edition, June 15, 2005Example QueriesnFind the names o
20、f all customers who have a loan, an account, or both, from the bankcustomer_name (borrower) customer_name (depositor)Silberschatz, Korth and Sudarshan2.30Database System Concepts - 5th Edition, June 15, 2005Example QueriesnFind the names of all customers who have a loan at the Perryridge branch.n Fi
21、nd the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank.customer_name (branch_name = “Perryridge” (borrower.loan_number = loan.loan_number(borrower x loan) customer_name(depositor)customer_name (branch_name=“Perryridge” (borrower.lo
22、an_number = loan.loan_number(borrower x loan)Silberschatz, Korth and Sudarshan2.31Database System Concepts - 5th Edition, June 15, 2005Example QueriesnFind the names of all customers who have a loan at the Perryridge branch.l Query 2 customer_name(loan.loan_number = borrower.loan_number ( (branch_na
23、me = “Perryridge” (loan) x borrower)lQuery 1 customer_name (branch_name = “Perryridge” ( borrower.loan_number = loan.loan_number (borrower x loan)Silberschatz, Korth and Sudarshan2.32Database System Concepts - 5th Edition, June 15, 2005Example QueriesnFind the largest account balancelStrategy:4Find
24、those balances that are not the largestRename account relation as d so that we can compare each account balance with all others4Use set difference to find those account balances that were not found in the earlier step. lThe query is: balance(account) - account.balance (account.balance d.balance (acc
25、ount x d (account)Silberschatz, Korth and Sudarshan2.33Database System Concepts - 5th Edition, June 15, 2005Formal DefinitionnA basic expression in the relational algebra consists of either one of the following:lA relation in the databaselA constant relationnLet E1 and E2 be relational-algebra expre
26、ssions; the following are all relational-algebra expressions:lE1 E2lE1 E2lE1 x E2lp (E1), P is a predicate on attributes in E1ls(E1), S is a list consisting of some of the attributes in E1l x (E1), x is the new name for the result of E1Silberschatz, Korth and Sudarshan2.34Database System Concepts -
27、5th Edition, June 15, 2005Additional OperationsWe define additional operations that do not add any power to therelational algebra, but that simplify common queries.nSet intersectionnNatural joinnDivisionnAssignmentSilberschatz, Korth and Sudarshan2.35Database System Concepts - 5th Edition, June 15,
28、2005Set-Intersection OperationnNotation: r snDefined as:nr s = t | t r and t s nAssume: lr, s have the same arity lattributes of r and s are compatiblenNote: r s = r (r s)Silberschatz, Korth and Sudarshan2.36Database System Concepts - 5th Edition, June 15, 2005Set-Intersection Operation ExamplenRela
29、tion r, s:nr sA B121A B23rsA B 2Silberschatz, Korth and Sudarshan2.37Database System Concepts - 5th Edition, June 15, 2005n Notation: r sNatural-Join OperationnLet r and s be relations on schemas R and S respectively. Then, r s is a relation on schema R S obtained as follows:lConsider each pair of t
30、uples tr from r and ts from s. lIf tr and ts have the same value on each of the attributes in R S, add a tuple t to the result, where4t has the same value as tr on r4t has the same value as ts on snExample:R = (A, B, C, D)S = (E, B, D)lResult schema = (A, B, C, D, E)lr s is defined as: r.A, r.B, r.C
31、, r.D, s.E (r.B = s.B r.D = s.D (r x s)Silberschatz, Korth and Sudarshan2.38Database System Concepts - 5th Edition, June 15, 2005Natural Join Operation ExamplenRelations r, s:AB12412CDaababB13123DaaabbErAB11112CDaaaabEsnr sSilberschatz, Korth and Sudarshan2.39Database System Concepts - 5th Edition,
32、June 15, 2005Division OperationnNotation: nSuited to queries that include the phrase “for all”.nLet r and s be relations on schemas R and S respectively wherelR = (A1, , Am , B1, , Bn )lS = (B1, , Bn)The result of r s is a relation on schemaR S = (A1, , Am)r s = t | t R-S (r) u s ( tu r ) Where tu m
33、eans the concatenation of tuples t and u to produce a single tupler s Silberschatz, Korth and Sudarshan2.40Database System Concepts - 5th Edition, June 15, 2005Division Operation ExamplenRelations r, s:nr s:AB12AB12311134612rsSilberschatz, Korth and Sudarshan2.41Database System Concepts - 5th Editio
34、n, June 15, 2005Another Division ExampleABaaaaaaaaCDaabababbE11113111nRelations r, s:nr s:DabE11ABaaCrsSilberschatz, Korth and Sudarshan2.42Database System Concepts - 5th Edition, June 15, 2005Division Operation (Cont.)nProperty lLet q = r slThen q is the largest relation satisfying q x s rnDefiniti
35、on in terms of the basic algebra operationLet r(R) and s(S) be relations, and let S Rr s = R-S (r ) R-S ( ( R-S (r ) x s ) R-S,S(r )To see whylR-S,S (r) simply reorders attributes of rlR-S (R-S (r ) x s ) R-S,S(r) ) gives those tuples t in R-S (r ) such that for some tuple u s, tu r.Silberschatz, Ko
36、rth and Sudarshan2.43Database System Concepts - 5th Edition, June 15, 2005Assignment OperationnThe assignment operation () provides a convenient way to express complex queries. l Write query as a sequential program consisting of4a series of assignments 4followed by an expression whose value is displ
37、ayed as a result of the query.lAssignment must always be made to a temporary relation variable.nExample: Write r s as temp1 R-S (r ) temp2 R-S (temp1 x s ) R-S,S (r )result = temp1 temp2lThe result to the right of the is assigned to the relation variable on the left of the .lMay use variable in subs
38、equent expressions.Silberschatz, Korth and Sudarshan2.44Database System Concepts - 5th Edition, June 15, 2005Bank Example QueriesnFind the names of all customers who have a loan and an account at bank.customer_name (borrower) customer_name (depositor)nFind the name of all customers who have a loan a
39、t the bank, loan number and the loan amount.Silberschatz, Korth and Sudarshan2.45Database System Concepts - 5th Edition, June 15, 2005lQuery 2 customer_name, branch_name (depositor account) temp(branch_name) (“Downtown” ), (“Uptown” )Note that Query 2 uses a constant relation.Bank Example QueriesnFi
40、nd all customers who have an account from at least the “Downtown” and the Uptown” branches.lQuery 1customer_name (branch_name = “Downtown” (depositor account ) customer_name (branch_name = “Uptown” (depositor account)Silberschatz, Korth and Sudarshan2.46Database System Concepts - 5th Edition, June 1
41、5, 2005nFind all customers who have an account at all branches located in Brooklyn city.Example Queriescustomer_name, branch_name (depositor account) branch_name (branch_city = “Brooklyn” (branch)Silberschatz, Korth and Sudarshan2.47Database System Concepts - 5th Edition, June 15, 2005Extended Relat
42、ional-Algebra-OperationsnGeneralized ProjectionnAggregate FunctionsnOuter JoinSilberschatz, Korth and Sudarshan2.48Database System Concepts - 5th Edition, June 15, 2005Generalized ProjectionnExtends the projection operation by allowing arithmetic functions to be used in the projection list.nE is any
43、 relational-algebra expressionnEach of F1, F2, , Fn are are arithmetic expressions involving constants and attributes in the schema of E.nGiven relation credit_info(customer_name, limit, credit_balance), find how much more each person can spend: customer_name, limit credit_balance (credit_info)Silbe
44、rschatz, Korth and Sudarshan2.49Database System Concepts - 5th Edition, June 15, 2005Aggregate Functions and OperationsnAggregation function takes a collection of values and returns a single value as a result.avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of va
45、luesnAggregate operation in relational algebra E is any relational-algebra expressionlG1, G2 , Gn is a list of attributes on which to group (can be empty)lEach Fi is an aggregate functionlEach Ai is an attribute nameSilberschatz, Korth and Sudarshan2.50Database System Concepts - 5th Edition, June 15
46、, 2005Aggregate Operation ExamplenRelation r:ABC77310ng sum(c) (r)sum(c )27Silberschatz, Korth and Sudarshan2.51Database System Concepts - 5th Edition, June 15, 2005Aggregate Operation ExamplenRelation account grouped by branch-name:branch_name g sum(balance) (account)branch_name account_numberbalan
47、cePerryridgePerryridgeBrightonBrightonRedwoodA-102A-201A-217A-215A-222400900750750700branch_namesum(balance)PerryridgeBrightonRedwood13001500700Silberschatz, Korth and Sudarshan2.52Database System Concepts - 5th Edition, June 15, 2005Aggregate Functions (Cont.)nResult of aggregation does not have a
48、namelCan use rename operation to give it a namelFor convenience, we permit renaming as part of aggregate operationbranch_name g sum(balance) as sum_balance (account)Silberschatz, Korth and Sudarshan2.53Database System Concepts - 5th Edition, June 15, 2005Outer JoinnAn extension of the join operation
49、 that avoids loss of information.nComputes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. nUses null values:lnull signifies that the value is unknown or does not exist lAll comparisons involving null are (roughly speaking)
50、false by definition.4We shall study precise meaning of comparisons with nulls laterSilberschatz, Korth and Sudarshan2.54Database System Concepts - 5th Edition, June 15, 2005Outer Join ExamplenRelation loannRelation borrowercustomer_name loan_numberJonesSmithHayesL-170L-230L-155300040001700loan_numbe
51、ramountL-170L-230L-260branch_nameDowntownRedwoodPerryridgeSilberschatz, Korth and Sudarshan2.55Database System Concepts - 5th Edition, June 15, 2005Outer Join ExamplenInner Joinloan Borrowerloan_numberamountL-170L-23030004000customer_nameJonesSmithbranch_nameDowntownRedwoodJonesSmithnullloan_numbera
52、mountL-170L-230L-260300040001700customer_namebranch_nameDowntownRedwoodPerryridgen Left Outer Join loan BorrowerSilberschatz, Korth and Sudarshan2.56Database System Concepts - 5th Edition, June 15, 2005Outer Join Exampleloan_numberamountL-170L-230L-15530004000nullcustomer_nameJonesSmithHayesbranch_n
53、ameDowntownRedwoodnullloan_numberamountL-170L-230L-260L-155300040001700nullcustomer_nameJonesSmithnullHayesbranch_nameDowntownRedwoodPerryridgenulln Full Outer Join loan borrowern Right Outer Join loan borrowerSilberschatz, Korth and Sudarshan2.57Database System Concepts - 5th Edition, June 15, 2005
54、Null ValuesnIt is possible for tuples to have a null value, denoted by null, for some of their attributesnnull signifies an unknown value or that a value does not exist.nThe result of any arithmetic expression involving null is null.nAggregate functions simply ignore null values (as in SQL)nFor dupl
55、icate elimination and grouping, null is treated like any other value, and two nulls are assumed to be the same (as in SQL)Silberschatz, Korth and Sudarshan2.58Database System Concepts - 5th Edition, June 15, 2005Null ValuesnComparisons with null values return the special truth value: unknownnThree-v
56、alued logic using the truth value unknown:lOR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknownlAND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknownlNOT: (not unknown) = unknownlIn SQL “P is unknown” evaluates to true if
57、 predicate P evaluates to unknownSilberschatz, Korth and Sudarshan2.59Database System Concepts - 5th Edition, June 15, 2005Modification of the DatabasenThe content of the database may be modified using the following operations:lDeletionlInsertionlUpdatingnAll these operations are expressed using the
58、 assignment operator.Silberschatz, Korth and Sudarshan2.60Database System Concepts - 5th Edition, June 15, 2005DeletionnA delete request is expressed similarly to a query, except instead of displaying tuples to the user, the selected tuples are removed from the database.nCan delete only whole tuples
59、; cannot delete values on only particular attributesnA deletion is expressed in relational algebra by:r r Ewhere r is a relation and E is a relational algebra query.Silberschatz, Korth and Sudarshan2.61Database System Concepts - 5th Edition, June 15, 2005Deletion ExamplesnDelete all account records
60、in the Perryridge branch.n Delete all loan records with amount in the range of 0 to 50loan loan amount 0and amount 50 (loan)account account branch_name = “Perryridge” (account )Silberschatz, Korth and Sudarshan2.62Database System Concepts - 5th Edition, June 15, 2005InsertionnTo insert data into a r
61、elation, we either:lspecify a tuple to be insertedlwrite a query whose result is a set of tuples to be insertednin relational algebra, an insertion is expressed by:r r Ewhere r is a relation and E is a relational algebra expression.nThe insertion of a single tuple is expressed by letting E be a cons
62、tant relation containing one tuple. Silberschatz, Korth and Sudarshan2.63Database System Concepts - 5th Edition, June 15, 2005Insertion ExamplesnInsert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch.n Provide as a gift for all loan customers in
63、the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account.account account (“Perryridge”, A-973, 1200)depositor depositor (“Smith”, A-973)r1 (branch_name = “Perryridge” (borrower loan)account account (loan_number, branch_name (r1) x(200
64、)depositor depositor customer_name, loan_number (r1)Silberschatz, Korth and Sudarshan2.64Database System Concepts - 5th Edition, June 15, 2005UpdatingnA mechanism to change a value in a tuple without charging all values in the tuplenUse the generalized projection operator to do this tasknEach Fi is
65、either lthe I th attribute of r, if the I th attribute is not updated, or,lif 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 attributeSilberschatz, Korth and Sudarshan2.65Database System Concepts - 5th Edition,
66、June 15, 2005Update ExamplesnMake interest payments by increasing all balances by 5 percent.n 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 *
67、1.05 (BAL 10000 (account)account account_number, branch_name, balance * 1.05 (account)Database System Concepts, 5th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use End of Chapter 2Silberschatz, Korth and Sudarshan2.67Database System Concepts - 5th Edition, June 15, 2005Figur
68、e 2.3. The branch relationSilberschatz, Korth and Sudarshan2.68Database System Concepts - 5th Edition, June 15, 2005Figure 2.6: The loan relationSilberschatz, Korth and Sudarshan2.69Database System Concepts - 5th Edition, June 15, 2005Figure 2.7: The borrower relationSilberschatz, Korth and Sudarsha
69、n2.70Database System Concepts - 5th Edition, June 15, 2005Figure 2.8: Schema diagramSilberschatz, Korth and Sudarshan2.71Database System Concepts - 5th Edition, June 15, 2005Figure 2.9Result of branch_name = “Perryridge” (loan) Silberschatz, Korth and Sudarshan2.72Database System Concepts - 5th Edit
70、ion, June 15, 2005Figure 2.10: Loan number and the amount of the loanSilberschatz, Korth and Sudarshan2.73Database System Concepts - 5th Edition, June 15, 2005Figure 2.11: Names of all customers who have either an account or an loanSilberschatz, Korth and Sudarshan2.74Database System Concepts - 5th
71、Edition, June 15, 2005Figure 2.12: Customers with an account but no loanSilberschatz, Korth and Sudarshan2.75Database System Concepts - 5th Edition, June 15, 2005Figure 2.13: Result of borrower |X| loanSilberschatz, Korth and Sudarshan2.76Database System Concepts - 5th Edition, June 15, 2005Figure 2
72、.14Silberschatz, Korth and Sudarshan2.77Database System Concepts - 5th Edition, June 15, 2005Figure 2.15Silberschatz, Korth and Sudarshan2.78Database System Concepts - 5th Edition, June 15, 2005Figure 2.16Silberschatz, Korth and Sudarshan2.79Database System Concepts - 5th Edition, June 15, 2005Figur
73、e 2.17Largest account balance in the bankSilberschatz, Korth and Sudarshan2.80Database System Concepts - 5th Edition, June 15, 2005Figure 2.18: Customers who live on the same street and in the same city as SmithSilberschatz, Korth and Sudarshan2.81Database System Concepts - 5th Edition, June 15, 200
74、5Figure 2.19: Customers with both an account and a loan at the bankSilberschatz, Korth and Sudarshan2.82Database System Concepts - 5th Edition, June 15, 2005Figure 2.20Silberschatz, Korth and Sudarshan2.83Database System Concepts - 5th Edition, June 15, 2005Figure 2.21Silberschatz, Korth and Sudarsh
75、an2.84Database System Concepts - 5th Edition, June 15, 2005Figure 2.22Silberschatz, Korth and Sudarshan2.85Database System Concepts - 5th Edition, June 15, 2005Figure 2.23Silberschatz, Korth and Sudarshan2.86Database System Concepts - 5th Edition, June 15, 2005Figure 2.24: The credit_info relationSi
76、lberschatz, Korth and Sudarshan2.87Database System Concepts - 5th Edition, June 15, 2005Figure 2.25Silberschatz, Korth and Sudarshan2.88Database System Concepts - 5th Edition, June 15, 2005Figure 2.26: The pt_works relationSilberschatz, Korth and Sudarshan2.89Database System Concepts - 5th Edition,
77、June 15, 2005Figure 2.27The pt_works relation after regroupingSilberschatz, Korth and Sudarshan2.90Database System Concepts - 5th Edition, June 15, 2005Figure 2.28Silberschatz, Korth and Sudarshan2.91Database System Concepts - 5th Edition, June 15, 2005Figure 2.29Silberschatz, Korth and Sudarshan2.9
78、2Database System Concepts - 5th Edition, June 15, 2005Figure 2.30The employee and ft_works relationsSilberschatz, Korth and Sudarshan2.93Database System Concepts - 5th Edition, June 15, 2005Figure 2.31Silberschatz, Korth and Sudarshan2.94Database System Concepts - 5th Edition, June 15, 2005Figure 2.32Silberschatz, Korth and Sudarshan2.95Database System Concepts - 5th Edition, June 15, 2005Figure 2.33Silberschatz, Korth and Sudarshan2.96Database System Concepts - 5th Edition, June 15, 2005Figure 2.34
