高级数据库系统第二章关系模型.ppt

《高级数据库系统第二章关系模型.ppt》由会员分享，可在线阅读，更多相关《高级数据库系统第二章关系模型.ppt（96页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Database System Concepts,5th Ed.Silberschatz,Korth and SudarshanSee www.db-for conditions on re-use Chapter 2:Relational ModelSilberschatz,Korth and Sudarshan 2.2 Database System Concepts-5th Edition,June 15,2005Chapter 2:Relational Modeln Structure of Relational Databasesn Fundamental Relational-Al

2、gebra-Operationsn Additional Relational-Algebra-Operationsn Extended Relational-Algebra-Operationsn Null Valuesn Modification of the DatabaseSilberschatz,Korth and Sudarshan 2.3 Database System Concepts-5th Edition,June 15,2005Example of a RelationSilberschatz,Korth and Sudarshan 2.4 Database System

3、 Concepts-5th Edition,June 15,2005Attribute Typesn Each attribute of a relation has a namen The set of allowed values for each attribute is called the domain of the attributen Attribute values are(normally)required to be atomic;that is,indivisiblel E.g.the value of an attribute can be an account num

4、ber,but cannot be a set of account numbersn Domain is said to be atomic if all its members are atomicn The special value null is a member of every domainn The null value causes complications in the definition of many operationsl We shall ignore the effect of null values in our main presentation and

5、consider their effect laterSilberschatz,Korth and Sudarshan 2.5 Database System Concepts-5th Edition,June 15,2005Relation Scheman Formally,given domains 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 Din Schema of a relation consists o

6、fl attribute definitions 4 name4 type/domainl integrity constraintsSilberschatz,Korth and Sudarshan 2.6 Database System Concepts-5th Edition,June 15,2005Relation Instancen The current values(relation instance)of a relation are specified by a tablen An element t of r is a tuple,represented by a row i

7、n a tablen Order of tuples is irrelevant(tuples may be stored in an arbitrary order)JonesSmithCurryLindsaycustomer_nameMainNorthNorthParkcustomer_streetHarrisonRyeRyePittsfieldcustomer_citycustomerattributes(or columns)tuples(or rows)Silberschatz,Korth and Sudarshan 2.7 Database System Concepts-5th

8、Edition,June 15,2005Databasen A database consists of multiple relationsn Information about an enterprise is broken up into parts,with each relation storing one part of the informationn E.g.account:information about accounts depositor:which customer owns which account customer:information about custo

9、mersSilberschatz,Korth and Sudarshan 2.8 Database System Concepts-5th Edition,June 15,2005The customer RelationSilberschatz,Korth and Sudarshan 2.9 Database System Concepts-5th Edition,June 15,2005The depositor RelationSilberschatz,Korth and Sudarshan 2.10 Database System Concepts-5th Edition,June 1

10、5,2005Why Split Information Across Relations?n Storing all information as a single relation such as bank(account_number,balance,customer_name,.)results inl repetition of information 4 e.g.,if two customers own an account(What gets repeated?)l the need for null values 4 e.g.,to represent a customer w

11、ithout an accountn Normalization theory(Chapter 7)deals with how to design relational schemasSilberschatz,Korth and Sudarshan 2.11 Database System Concepts-5th Edition,June 15,2005Keysn Let K Rn K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation

12、r(R)l by“possible r”we mean a relation r that could exist in the enterprise we are modeling.l Example:customer_name,customer_street and customer_name are both superkeys of Customer,if no two customers can possibly have the same name4 In real life,an attribute such as customer_id would be used instea

13、d of customer_name to uniquely identify customers,but we omit it to keep our examples small,and instead assume customer names are unique.Silberschatz,Korth and Sudarshan 2.12 Database System Concepts-5th Edition,June 15,2005Keys(Cont.)n K is a candidate key if K is minimalExample:customer_name is a

14、candidate key for Customer,since it is a superkey and no subset of it is a superkey.n Primary key:a candidate key chosen as the principal means of identifying tuples within a relationl Should choose an attribute whose value never,or very rarely,changes.l E.g.email address is unique,but may changeSil

15、berschatz,Korth and Sudarshan 2.13 Database System Concepts-5th Edition,June 15,2005Foreign Keysn A relation schema may have an attribute that corresponds to the primary key of another relation.The attribute is called a foreign key.l E.g.customer_name and account_number attributes of depositor are f

16、oreign keys to customer and account respectively.l Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation.Silberschatz,Korth and Sudarshan 2.14 Database System Concepts-5th Edition,June 15,2005Schema DiagramSil

17、berschatz,Korth and Sudarshan 2.15 Database System Concepts-5th Edition,June 15,2005Query Languagesn Language in which user requests information from the database.n Categories of languagesl Procedurall Non-procedural,or declarativen“Pure”languages:l Relational algebral Tuple relational calculusl Dom

18、ain relational calculusn Pure languages form underlying basis of query languages that people use.Silberschatz,Korth and Sudarshan 2.16 Database System Concepts-5th Edition,June 15,2005Relational Algebran Procedural languagen Six basic operatorsl select:l project:l union:l set difference:l Cartesian

19、product:xl rename:n The operators take one or two relations as inputs and produce a new relation as a result.Silberschatz,Korth and Sudarshan 2.17 Database System Concepts-5th Edition,June 15,2005Select Operation Examplen Relation rA B C D15122377310 A=B D 5(r)A B C D123710Silberschatz,Korth and Sud

20、arshan 2.18 Database System Concepts-5th Edition,June 15,2005Project Operation Examplen Relation r:A B C102030401112A C1112=A C112A,C(r)Silberschatz,Korth and Sudarshan 2.19 Database System Concepts-5th Edition,June 15,2005Union Operation Examplen Relations r,s:n r s:A B121A B23rsA B1213Silberschatz

21、,Korth and Sudarshan 2.20 Database System Concepts-5th Edition,June 15,2005Set Difference Operation Examplen Relations r,s:n r s:A B121A B23rsA B11Silberschatz,Korth and Sudarshan 2.21 Database System Concepts-5th Edition,June 15,2005Cartesian-Product Operation Examplen Relations r,s:n r x s:A B12A

22、B11112222C D 1010201010102010EaabbaabbC D10102010EaabbrsSilberschatz,Korth and Sudarshan 2.22 Database System Concepts-5th Edition,June 15,2005Rename Operationn Allows us to name,and therefore to refer to,the results of relational-algebra expressions.n Allows us to refer to a relation by more than o

23、ne name.n Example:x(E)returns the expression E under the name Xn If 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.Silberschatz,Korth and Sudarshan 2.23 Database System Concepts-5th Edition,June 15,2

24、005Composition of Operationsn Can build expressions using multiple operationsn Example:A=C(r x s)n r x sn A=C(r x s)A B11112222C D 1010201010102010EaabbaabbA B C D E122101020aabSilberschatz,Korth and Sudarshan 2.24 Database System Concepts-5th Edition,June 15,2005Banking Examplebranch(branch_name,br

25、anch_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)Silberschatz,Korth and Sudarshan 2.25 Database System Concepts-5th Edition,Ju

26、ne 15,2005Example Queriesn Find all loans of over$1200 n Find the loan number for each loan of an amount greater than$1200 amount 1200(loan)loan_number(amount 1200(loan)n Find the names of all customers who have a loan,an account,or both,from the bankcustomer_name(borrower)customer_name(depositor)Si

27、lberschatz,Korth and Sudarshan 2.26 Database System Concepts-5th Edition,June 15,2005Example Queriesn Find the names of all customers who have a loan at the Perryridge branch.n 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 ba

28、nk.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)Silberschatz,Korth and Sudarshan 2.27 Database System Concepts-5th Edition,June 15,2005

29、Example Queriesn Find the names of all customers who have a loan at the Perryridge branch.l customer_name(loan.loan_number=borrower.loan_number(branch_name=“Perryridge”(loan)x borrower)l customer_name(branch_name=“Perryridge”(borrower.loan_number=loan.loan_number(borrower x loan)Silberschatz,Korth a

30、nd Sudarshan 2.28 Database System Concepts-5th Edition,June 15,2005Additional Operationsn Additional Operationsl Set intersectionl Natural joinl Aggregationl Outer Joinl Divisionn All above,other than aggregation,can be expressed using basic operations we have seen earlierSilberschatz,Korth and Suda

31、rshan 2.29 Database System Concepts-5th Edition,June 15,2005Set-Intersection Operation Examplen Relation r,s:n r sA B121A B23r sA B 2Silberschatz,Korth and Sudarshan 2.30 Database System Concepts-5th Edition,June 15,2005Natural Join Operation Examplen Relations r,s:A B12412C DaababB13123DaaabbErA B1

32、1112C DaaaabEsn r sSilberschatz,Korth and Sudarshan 2.31 Database System Concepts-5th Edition,June 15,2005n Notation:r sNatural-Join Operationn 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:l Consider each pair of tuples tr from r a

33、nd ts from s.l If tr and ts have the same value on each of the attributes in R S,add a tuple t to the result,where4 t has the same value as tr on r4 t has the same value as ts on sn Example:R=(A,B,C,D)S=(E,B,D)l Result schema=(A,B,C,D,E)l 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)S

34、ilberschatz,Korth and Sudarshan 2.32 Database System Concepts-5th Edition,June 15,2005Bank Example Queriesn Find the largest account balancel Strategy:4 Find those balances that are not the largest Rename account relation as d so that we can compare each account balance with all others4 Use set difference to find those account balances that were not found in the earlier step.l The query is:balance(account)-account.balance(account.balance d.balance(account x d(account)