数据库ch2-relationalmodel.ppt

Chapter 2:Relational Model,Chapter2 Relational Model,2,Contents,Structure of Relational DatabasesFundamental Relational-Algebra-OperationsAdditional Relational-Algebra-OperationsExtended Relational-Algebra-OperationsNull ValuesModification of the Database,Chapter2 Relational Model,3,Why study,Widely usedSimpledata structure,Chapter2 Relational Model,4,On the logical level,relational database is a collection of 2-D tables called Relations,Structure of Relational Database,relations represents an entity set or a relationship set,A row of a relation represents a entity or a relationship,Chapter2 Relational Model,5,Basic Structure,Each row of a relation can be referred to as tupleEach column of a relation has a name named columns of a relation are referred to as attributes The set of allowed values for each attribute is called the domain of the attribute,Chapter2 Relational Model,6,Basic Structure,Domain-legal type and range of values of an attributedenoted by dom(Ai)Attribute:Age Domain:0-100Attribute:EmpName Domain:50 alphabetic charsAttribute:Salary Domain:non-negative integer,Chapter2 Relational Model,7,Relation Customer,Example,Has 3 attributes customer_name,customer_street,customer_city,D1:The set of all possible customer names,D2:The set of all possible customer streets,D3:The set of all possible customer city,Relation customer is a subset of:D1 D2D3,Tuple,Chapter2 Relational Model,8,Cartesian Product,Relation,Formally,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 Di,Chapter2 Relational Model,9,例如：D1=MAN=王兵，李平，张英，D2=WOMAN=丁梅，吴芳 D3=CHILD=王一，李一，李二笛卡尔积可以表示为一个二维表，表中的每一行对应一个元组，每一列对应一个域,Chapter2 Relational Model,10,MAN WOMAN CHILD王兵 丁梅 王一王兵 丁梅 李一王兵 丁梅 李二王兵 吴芳 王一王兵 吴芳 李一王兵 吴芳 李二李平 丁梅 王一李平 丁梅 李一李平 丁梅 李二李平 吴芳 王一李平 吴芳 李一李平 吴芳 李二,MAN WOMAN CHILD张英 丁梅 王一张英 丁梅 李一张英 丁梅 李二张英 吴芳 王一张英 吴芳 李一张英 吴芳 李二,续左表,Chapter2 Relational Model,11,例如：假设王兵的妻子是丁梅，他们的孩子是王一，李平的妻子是吴芳，他们的孩子是李一和李二，则取笛卡尔积的一个子集构造一个关系FAMILY,MAN WOMAN CHILD王兵 丁梅 王一李平 吴芳 李一李平 吴芳 李二,FAMILY,Chapter2 Relational Model,12,Relation Schema,A1,A2,An are attribute namesR=(A1,A2,An)is a relation schemacustomer_schema=(customer_name,customer_street,customer_city)r(R)denotes a relation r on the relation schema Rcustomer(customer_schema)The current values(relation instance)of a relation are specified by a table,Chapter2 Relational Model,13,Attribute values are(normally)required to be atomic;that is,indivisible the value of a tuple on an attribute can be an account number,but cannot be a set of account numbersThe special value null is a member of every domain,Cont.,Basic structure,Chapter2 Relational Model,14,Basic structure,A special null value is used to represent values that are:Not applicable(phone number for a client that has no phone)Missing values(there is a phone number but we do not know it yet)Not known(we do not know whether there is a phone number or not),Chapter2 Relational Model,15,Relations are Unordered,Order of tuples is irrelevant(tuples may be stored in an arbitrary order),account relation with unordered tuples,Example,Chapter2 Relational Model,16,Relational Database,A database consists of multiple relationsInformation about an enterprise is broken up into parts,with each relation storing one part of the information,account:stores information about accountsdepositor:stores information about which customer owns which accountcustomer:stores information about customers,Example,Chapter2 Relational Model,17,Cont.,Relational Database,Storing all information as a single relation repetition of information the need for null values Normalization theory(Chapter 7)deals with how to design relational schemas,if two customers own an account(What gets repeated?),to represent a customer without an account,bank(account_number,balance,customer_name,.),Result in,Chapter2 Relational Model,18,Keys,K is a set of attributes,let K RK is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R),Chapter2 Relational Model,19,Example,If no two customers can possibly have the same name，customer_name,customer_street and customer_name are both superkeys of customer,Chapter2 Relational Model,20,Cont.,Keys,Superkey K is a candidate key if K is minimalMinimal means no subset of it is superkey,customer_name is a candidate key for customer,since it is a superkey and no subset of it is a superkey.,Example,Chapter2 Relational Model,21,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.,Cont.,Keys,email address is unique,but may change,is not suitable for primary key,Example,Chapter2 Relational Model,22,Foreign Keys,A relation r1 may have an attribute that corresponds to the primary key of another relation r2.The attribute is called a foreign keyr1 is referencing relationr2 is referenced relation,customer_name and account_number attributes of depositor are foreign keys to customer and account respectively.depositor is referencing relation,customer and account are referenced relation,Example,Chapter2 Relational Model,23,Primary Key,Foreign Key,Corresponds to the primary key of relation Customer,Foreign Key,Corresponds to the primary key of relation Account,Example,Chapter2 Relational Model,24,Referencing Constraint,Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation,Is subset of,Chapter2 Relational Model,25,Schema Diagram,Chapter2 Relational Model,26,Relation Integrity(完整性）,Entity IntegrityThe values of primary key are not nullReferential IntegrityUser-defined Integrity,Chapter2 Relational Model,27,Query Languages,Language in which user requests information from the database.Categories of languagesProceduralNon-procedural,or declarative,Chapter2 Relational Model,28,Relational Algebra,Consists of a set of operators take one or two relations as inputs and produce a new relation as a resultSix basic operatorsselect:project:union:set difference:cartesian product:xrename:,Chapter2 Relational Model,29,Select Operation,Select tuples that satisfy a given predicate（谓词）,Relation r,A=B D 5(r),Example,Chapter2 Relational Model,30,Cont.,Select Operation,p(r)=t|t r and p(t),Where p is a formula in propositional calculus（命题演算）consisting of terms connected by:(and),(or),(not)，Each term is one of:op or where op is one of:=,.,selection predicate,Notation:p(r)Defined as:,Chapter2 Relational Model,31,Find the basic information of all students whos age is bellow 20,Sage 20(Student)4 20(Student),Example,Chapter2 Relational Model,32,Project Operation,A Unary operation,picking certain columns,Example,Relation r,A,C(r),Chapter2 Relational Model,33,Notation:The result is defined as the relation of k columns obtained by erasing the columns that are not listedDuplicate rows removed from result,since relations are sets,Project Operation,attribute names,Cont.,Chapter2 Relational Model,34,Example,Find the names and the departments of all students,Sname,Sdept(Student)or 2,5(Student),Chapter2 Relational Model,35,Union Operation*,Analogous to set union operation,Example,r s,r,s,Chapter2 Relational Model,36,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,2nd column of r deals with the same type of values as does the 2nd column of s,Chapter2 Relational Model,37,Set Difference Operation,Analogous to set difference operation,r s,s,r,Example,Chapter2 Relational Model,38,Set Difference Operation,Notation r sDefined as:Set differences must be taken between compatible relations.r and s must have the same arityattribute domains of r and s must be compatible,r s=t|t r and ts,Chapter2 Relational Model,39,Cartesian-Product Operation,Pair each tuple of one relation with each tuple of another,s,r,r x s,Example,Chapter2 Relational Model,40,Cartesian-Product Operation,Notation r x sDefined as:Assume that attributes of r(R)and s(S)are disjoint.(That is,R S=).If attributes of r(R)and s(S)are not disjoint,then renaming must be used,r x s=t q|t r and q s,Chapter2 Relational Model,41,Composition of Operations*,Can build expressions using multiple operations,Example,r x s,A=C(r x s),Chapter2 Relational Model,42,Example,account customer,(,),account.account_number=customer.accoont_number,(,),customer_name,account.account_number,balance,Find the names of all customers who have an account at the bank,along with the account number and the balance,Chapter2 Relational Model,43,Rename Operation,x(E),returns the expression E under the name X,returns the result of expression E under the name X,and with the attributes renamed to A1,A2,.,An,Allows us to name,and therefore to refer to,the results of relational-algebra expressionsAllows us to refer to a relation by more than one name,Chapter2 Relational Model,44,?.cutomer._city,smith_info.customer_city,(,customer,(customer smith_infor,Example,Find the names of all customers who live in the same city as smith,customer_name=“smith”(customer),(,),),customer.customer_city=,(,),customer.customer_name,Chapter2 Relational Model,45,branch(branch_name,branch_city,assets（资产)customer(customer_name,customer_street,customer_city)account(account_number,branch_name,balance)loan(loan_number,branch_name,amount)depositor(customer_name,account_number)borrower(customer_name,loan_number),Banking Example,Chapter2 Relational Model,46,Find all loans of over$1200Find the loan number for each loan of an amount greater than$1200Find the names of all customers who have a loan,an account,or both,from the bank,amount 1200(loan),loan_number(amount 1200(loan),customer_name(borrower)customer_name(depositor),Example Queries,Chapter2 Relational Model,47,Find the names of all customers who have a loan at the Perryridge branch,Example Queries,Query 1 customer_name(branch_name=“Perryridge”(borrower.loan_number=loan.loan_number(borrower x loan),Query 2 customer_name(loan.loan_number=borrower.loan_number(branch_name=“Perryridge”(loan)x borrower),Cont.,Chapter2 Relational Model,48,Cont.,Example Queries,Find 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),Chapter2 Relational Model,49,Find the largest account 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:,Example Queries,Cont.,balance(account)-account.balance(account.balance d.balance(account x rd(account),Chapter2 Relational Model,50,Additional Operations,We define additional operations that do not add any power to the relational algebra,but that simplify common queriesSet intersection Natural join Division Assignment,Chapter2 Relational Model,51,Set-Intersection Operation,Notation:r sDefined as:Assume:r,s have the same arity attributes of r and s are compatibleNote:r s=r(r s),r s=t|t r and t s,Chapter2 Relational Model,52,Example,s,r,r s,Chapter2 Relational Model,53,Join,Generating all possible combinations of tuples is not usually meaningful.Join is a cartesian product followed by a selection-join,Chapter2 Relational Model,54,An Introduction to Database System,Join,Relation R,S：,Chapter2 Relational Model,55,An Introduction to Database System,Join,一般连接 R S的结果如下：,CE,Chapter2 Relational Model,56,An Introduction to Database System,Equijoin,Equijoin（等值连接）R S 的结果如下：,Chapter2 Relational Model,57,An Introduction to Database System,Nature join,自然连接 R S的结果如下：,B,Chapter2 Relational Model,58,Natural-Join Operation,Notation:r sLet 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,Chapter2 Relational Model,59,Example,s,r,Natual-Join connects two relations by:Equating attributes of the same name And Projecting out one copy of each pair of equated attributes.,Chapter2 Relational Model,60,Natural-Join Operation,account customer,(,),account.account_number=customer.accoont_number,(,),customer_name,account.account_number,balance,Find the names of all customers who have an account at the bank,along with the account number and the balance,Chapter2 Relational Model,61,Natural-Join Operation,(account customer),customer_name,account.account_number,balance,Find the names of all customers who have an account at the bank,along with the account number and the balance,Chapter2 Relational Model,62,Example Queries,Find the name of all customers who have a loan at the bank and the loan amount,customer_name,loan_number,amount(borrower loan),branch(branch_name,branch_city,assets)customer(customer_name,customer_street,customer_city)account(account_number,branch_name,balance)loan(loan_number,branch_name,amount)depositor(customer_name,account_number)borrower(customer_name,loan_number),Chapter2 Relational Model,63,Example Queries,Find the names of all customers who have a loan and an account at bank,customer_name(borrower)customer_name(depositor),customer_name(borrower depositor),Chapter2 Relational Model,64,Division,Let A have two fields x and yLet B have one field yA/B contains all x tuples,such that for every y tuple in B there is a xy tuple in A,A,B,/,=,Chapter2 Relational Model,65,Division Operation,Notation: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 schemaR S=(A1,Am)r s=t|t R-S(r)u s(tu r)Where tu means the concatenation(连结)of tuples t and u to produce a single tuple,r s,Chapter2 Relational Model,66,Example,A,r s,s,r,Chapter2 Relational Model,67,r s,s,r,Example,Chapter2 Relational Model,68,A,B,Division using basic operations,Chapter2 Relational Model,69,Division using basic operations*,Cont.,Let A and B be relations,and let B ACompute all possible combinations of the first column of A and B.Then remove those rows that exist in AKeep only the first column of the result.These are the disqualified valuesx(x(A)B)A)A/B is the first column of A except the disqualified valuesA/B=x(A)x(x(A)B)A),Chapter2 Relational Model,70,Division(Cont.),x(A)B=,=,Chapter2 Relational Model,71,Division(Cont.),(x(A)B)-A=,=,x(A)x(x(A)B)A)=,=,Example Division,Find the Employment numbers of the pilots who can fly all MD planesCan_Fly/Model_No(Maker=MDPlane),Cont.,Example Queries,Find all customers who have an account from at least the“Downtown”and the“Uptown”branches.,Query 1,Query 2,customer_name,branch_name(depositor account)temp(branch_name)(“Downtown”),(“Uptown”),Cont.,Example Queries,Find all customers who have an account at all branches located in Bro