《数据库原理与系统ch.ppt》由会员分享，可在线阅读，更多相关《数据库原理与系统ch.ppt（97页珍藏版）》请在三一办公上搜索。
1、Chapter 2:Relational Model,Chapter 2:Relational Model,Structure of Relational DatabasesFundamental Relational-Algebra-OperationsAdditional Relational-Algebra-OperationsExtended Relational-Algebra-OperationsNull ValuesModification of the Database,Example of a Relation,Basic Structure,Formally,given s
2、ets 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 DiExample:Ifcustomer_name=Jones,Smith,Curry,Lindsay,/*Set of all customer names*/customer_street=Main,North,Park,/*set of all street names*/customer_city=Harrison,Rye,Pittsfield,/*set
3、of all city names*/Then r=(Jones,Main,Harrison),(Smith,North,Rye),(Curry,North,Rye),(Lindsay,Park,Pittsfield)is a relation over customer_name x customer_street x customer_city,Attribute Types,Each attribute of a relation has a nameThe set of allowed values for each attribute is called the domain of
4、the attributeAttribute values are(normally)required to be atomic;that is,indivisibleE.g.the value of an attribute can be an account number,but cannot be a set of account numbersDomain is said to be atomic if all its members are atomicThe special value null is a member of every domainThe null value c
5、auses complications in the definition of many operationsWe shall ignore the effect of null values in our main presentation and consider their effect later,Relation Schema,A1,A2,An are attributesR=(A1,A2,An)is a relation schemaExample:Customer_schema=(customer_name,customer_street,customer_city)r(R)d
6、enotes a relation r on the relation schema RExample:customer(Customer_schema),Relation Instance,The current values(relation instance)of a relation are specified by a tableAn element t of r is a tuple,represented by a row in a table,DATABASEProgramming Languagerelation schema type definitionrelation
7、variablerelation instance value of a variable,Relations are Unordered,Order of tuples is irrelevant(tuples may be stored in an arbitrary order)Example:account relation with unordered tuples,Database,A database consists of multiple relationsInformation about an enterprise is broken up into parts,with
8、 each relation storing one part of the informationaccount:stores information about accounts depositor:stores information about which customer owns which account customer:stores information about customersStoring all information as a single relation such as bank(account_number,balance,customer_name,.
9、)results inrepetition of information e.g.,if two customers own an account(What gets repeated?)the need for null values e.g.,to represent a customer without an accountNormalization theory(Chapter 7)deals with how to design relational schemas,The customer Relation,The depositor Relation,Keys,Let K RK
10、is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R)by“possible r”we mean a relation r that could exist in the enterprise we are modeling.Example:customer_name,customer_street and customer_name are both superkeys of Customer,if no two customers
11、can possibly have the same nameIn real life,an attribute such as customer_id would be used instead of customer_name to uniquely identify customers,but we omit it to keep our examples small,and instead assume customer names are unique.,Keys(Cont.),K is a candidate key if K is minimalExample:customer_
12、name is a candidate key for Customer,since it is a superkey and no subset of it is a superkey.Primary key:a candidate key chosen as the principal means of identifying tuples within a relationShould choose an attribute whose value never,or very rarely,changes.E.g.email address is unique,but may chang
13、e,Foreign Keys,A relation schema may have an attribute that corresponds to the primary key of another relation.The attribute is called a foreign key.E.g.customer_name and account_number attributes of depositor are foreign keys to customer and account respectively.Only values occurring in the primary
14、 key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation.Schema diagram,Query Languages,Language in which user requests information from the database.Categories of languagesProceduralNon-procedural,or declarative“Pure”languages:Relational algebraTu
15、ple relational calculusDomain relational calculusPure languages form underlying basis of query languages that people use.,2.2 Relational Algebra,Procedural languageSix basic operatorsselect:project:union:set difference:Cartesian product:xrename:The operators take one or two relations as inputs and p
16、roduce a new relation as a result.,Select Operation Example,Relation r,A,B,C,D,151223,77310,A=B D 5(r),A,B,C,D,123,710,Select Operation,Notation:p(r)p is called the selection predicateDefined as:p(r)=t|t r and p(t)Where p is a formula in propositional calculus consisting of terms connected by:(and),
17、(or),(not)Each term is one of:op or where op is one of:=,.Example of selection:branch_name=“Perryridge”(account),Project Operation Example,Relation r:,A,B,C,10203040,1112,A,C,1112,=,A,C,112,A,C(r),Project Operation,Notation:where A1,A2 are attribute names and r is a relation name.The result is defin
18、ed as the relation of k columns obtained by erasing the columns that are not listedDuplicate rows removed from result,since relations are setsExample:To eliminate the branch_name attribute of account account_number,balance(account),Union Operation Example,Relations r,s:,r s:,A,B,121,A,B,23,r,s,A,B,1
19、213,Union Operation,Notation:r sDefined as:r s=t|t r or t sFor r s to be valid.1.r,s must have the same arity(same number of attributes)2.The attribute domains must be compatible(example:2nd column of r deals with the same type of values as does the 2nd column of s)Example:to find all customers with
20、 either an account or a loan customer_name(depositor)customer_name(borrower),Set Difference Operation Example,Relations r,s:,r s:,A,B,121,A,B,23,r,s,A,B,11,Set Difference Operation,Notation r sDefined as:r s=t|t r and t sSet differences must be taken between compatible relations.r and s must have th
21、e same arityattribute domains of r and s must be compatible,Cartesian-Product Operation Example,Relations r,s:,r x s:,A,B,12,A,B,11112222,C,D,1010201010102010,E,aabbaabb,C,D,10102010,E,aabb,r,s,Cartesian-Product Operation,Notation r x sDefined as:r x s=t q|t r and q sAssume that attributes of r(R)an
22、d s(S)are disjoint.(That is,R S=).If attributes of r(R)and s(S)are not disjoint,then renaming must be used.E.g.r=borrower xloan relation schema:(borrower.customer_name,borrower.loan_number,loan.loan_number),Composition of Operations,Can build expressions using multiple operationsExample:A=C(r x s)r
23、x sA=C(r x s),A,B,11112222,C,D,1010201010102010,E,aabbaabb,A,B,C,D,E,122,101020,aab,Rename Operation,Allows us to name,and therefore to refer to,the results of relational-algebra expressions.Allows us to refer to a relation by more than one name.Example:x(E)returns the expression E under the name XI
24、f a relational-algebra expression E has arity n,then returns the result of expression E under the name X,and with theattributes renamed to A1,A2,.,An.,Banking Example,branch(branch_name,branch_city,assets)customer(customer_name,customer_street,customer_city)account(account_number,branch_name,balance
25、)loan(loan_number,branch_name,amount)depositor(customer_name,account_number)borrower(customer_name,loan_number),Example Queries,Find all loans of over$1200,Find the loan number for each loan of an amount greater than$1200,amount 1200(loan),loan_number(amount 1200(loan),Find the names of all customer
26、s who have a loan,an account,or both,from the bank,customer_name(borrower)customer_name(depositor),Relation Loan,R-Borrower,R-depositer,Example Queries,Find the names of all customers who have a loan at the Perryridge branch.,Find the names of all customers who have a loan at the Perryridge branch b
27、ut 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.loan_number=loan.loan_number(borrower x loan),Example Queries,Find the names of a
28、ll customers who have a loan at the Perryridge branch.,Query 2 customer_name(loan.loan_number=borrower.loan_number(branch_name=“Perryridge”(loan)x borrower),Query 1 customer_name(branch_name=“Perryridge”(borrower.loan_number=loan.loan_number(borrower x loan),Example Queries,Find the largest account
29、balanceStrategy:Find those balances that are not the largestRename account relation as d so that we can compare each account balance with all othersUse set difference to find those account balances that were not found in the earlier step.The query is:,balance(account)-account.balance(account.balance
30、 d.balance(account x rd(account),Formal Definition,A basic expression in the relational algebra consists of either one of the following:A relation in the databaseA constant relationLet E1 and E2 be relational-algebra expressions;the following are all relational-algebra expressions:E1 E2E1 E2E1 x E2p
31、(E1),P is a predicate on attributes in E1s(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,Additional Operations,We define additional operations that do not add any power to therelational algebra,but that simplify common queries.Set intersectio
32、nNatural joinDivisionAssignment,Set-Intersection Operation,Notation:r sDefined as:r s=t|t r and t s Assume:r,s have the same arity attributes of r and s are compatibleNote:r s=r(r s)(in r and s),Set-Intersection Operation Example,Relation r,s:r s,A B,121,A B,23,r,s,A B,2,Notation:r s,Natural-Join Op
33、eration,Let r and s be relations on schemas R and S respectively.Then,r s is a relation on schema R S obtained as follows:Consider each pair of tuples tr from r and ts from s.If tr and ts have the same value on each of the attributes in R S,add a tuple t to the result,wheret has the same value as tr
34、 on rt has the same value as ts on sExample:R=(A,B,C,D)S=(E,B,D)Result schema=(A,B,C,D,E)r s is defined as:r.A,r.B,r.C,r.D,s.E(r.B=s.B r.D=s.D(r x s),Natural Join Operation Example,Relations r,s:,A,B,12412,C,D,aabab,B,13123,D,aaabb,E,r,A,B,11112,C,D,aaaab,E,s,Division Operation,Notation:Suited to qu
35、eries that include the phrase“for all”.Let r and s be relations on schemas R and S respectively whereR=(A1,Am,B1,Bn)S=(B1,Bn)The result of r s is a relation on schemaR-S=(A1,Am)(Note:Is the difference of attributes R and S)r s=t|t R-S(r)u s(tu r)Where tu means the concatenation of tuples t and u to
36、produce a single tuple(each t concatenation all u),r s,Division Operation Example,Relations r,s:,r s:,A,B,12,A,B,12311134612,r,s,Another Division Example,A,B,aaaaaaaa,C,D,aabababb,E,11113111,Relations r,s:,r s:,D,ab,E,11,A,B,aa,C,r,s,Division Operation(Cont.),Property Let q=r sThen q is the largest
37、relation satisfying q x s rDefinition 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 whyR-S,S(r)simply reorders attributes of rR-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.,Assignment Oper
38、ation,The assignment operation()provides a convenient way to express complex queries.Write query as a sequential program consisting ofa series of assignments followed by an expression whose value is displayed as a result of the query.Assignment must always be made to a temporary relation variable.Ex
39、ample:Write r s as temp1 R-S(r)temp2 R-S(temp1 x s)R-S,S(r)result=temp1 temp2The result to the right of the is assigned to the relation variable on the left of the.May use variable in subsequent expressions.,Bank Example Queries,Find the names of all customers who have a loan and an account at bank.
40、,customer_name(borrower)customer_name(depositor),Find the name of all customers who have a loan at the bank and the loan amount,customer_name,loan_number,amount(borrower loan),Bank Example Queries,Find all customers who have an account from at least the“Downtown”and the Uptown”branches.,Find all cus
41、tomers who have an account at all branches located in Brooklyn city.,Bank Example Queries,Extended Relational-Algebra-Operations,Generalized ProjectionAggregate FunctionsOuter Join,Generalized Projection,Extends the projection operation by allowing arithmetic functions to be used in the projection l
42、ist.E is any relational-algebra expressionEach of F1,F2,Fn are are arithmetic expressions involving constants and attributes in the schema of E.Given relation credit_info(customer_name,limit,credit_balance),find how much more each person can spend:customer_name,limit credit_balance(credit_info),Aggr
43、egate Functions and Operations,Aggregation 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 valuesAggregate operation in relational algebra E is any relational-algebra expressionG1,G2,Gn i
44、s a list of attributes on which to group(can be empty)(分组条件：各元组中属性G1,G2,Gn 相等的分为一组；为空仅分一组）Each Fi is an aggregate functionEach Ai is an attribute nameGi and Fi（Ai）be presented in result relation,Aggregate Operation Example,Relation r:,g sum(c)(r),分组属性为空，只分一组,B g sum(c)(r),按B分组，分2组,Aggregate Operatio
45、n Example,Relation account grouped by branch-name:,branch_name g sum(balance)(account),branch_name,account_number,balance,PerryridgePerryridgeBrightonBrightonRedwood,A-102A-201A-217A-215A-222,400900750750700,branch_name,sum(balance),PerryridgeBrightonRedwood,13001500700,Aggregate Functions(Cont.),Re
46、sult of aggregation does not have a nameCan use rename operation to give it a nameFor convenience,we permit renaming as part of aggregate operation,branch_name g sum(balance)as sum_balance(account),Outer Join,An extension of the join operation that avoids loss of information.Computes the join and th
47、en adds tuples form one relation that does not match tuples in the other relation to the result of the join.Uses null values:null signifies that the value is unknown or does not exist All comparisons involving null are(roughly speaking)false by definition.We shall study precise meaning of comparison
48、s with nulls later,Outer Join Example,Relation loan,Relation borrower,Outer Join Example,Join loan borrower,Outer Join Example,Null Values,It is possible for tuples to have a null value,denoted by null,for some of their attributesnull signifies an unknown value or that a value does not exist.The res
49、ult of any arithmetic expression involving null is null.Aggregate functions simply ignore null values(as in SQL)For duplicate elimination and grouping,null is treated like any other value,and two nulls are assumed to be the same(as in SQL),Null Values,Comparisons with null values return the special
50、truth value:unknownIf false was used instead of unknown,then not(A=5Three-valued logic using the truth value unknown:OR:(unknown or true)=true,(unknown or false)=unknown(unknown or unknown)=unknownAND:(true and unknown)=unknown,(false and unknown)=false,(unknown and unknown)=unknownNOT:(not unknown)
