1 / 98

Chapter 3 relational model

Chapter 3 relational model. 3.1 An Informal Look at the Relational Model 3.2 The Relational Algebra 3.3 Extended Relational-Algebra Operation 3.4 Relational Calculus 3.5 The Tuple Relational Calculus 3.6 The Domain Relational Calculus 3.7 Modification of the Database

ann-house
Download Presentation

Chapter 3 relational model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 3 relational model • 3.1 An Informal Look at the Relational Model • 3.2 The Relational Algebra • 3.3Extended Relational-Algebra Operation • 3.4 Relational Calculus • 3.5 The Tuple Relational Calculus • 3.6 The Domain Relational Calculus • 3.7 Modification of the Database • 3.8 View Definition • 3.9 Exercises

  2. 3.1 An Informal Look at the Relational Model • Relational system are based on a formal foundation, or theory, called the relational model of data. Intuitively, what this statement means is that in such a system: 1. Structural aspect: The data in the database is perceived by the user as tables, and nothing but tables. 2. Integrity aspect: Those table satisfy certain integrity constraints. 3. Manipulative aspects: The operators available to the user for manipulating those tables—e.g. for purposes of data retrieval—are operators that derive tables from tables of those operators, three particular important ones are restrict, project, and join.

  3. 3.1.1 Basic Structure • The relational model can be regarded as having three principal parts, having to do with data structure, data integrity, and data manipulation. tuple, cardinality, attribute, degree, domain, primary key. INTEGER: operations: + - * / = …substr • Briefly, if we think of a relation as a table, then a tuple corresponds to a row of such a table and an attribute to a column; the number of tuples is called the cardinality and the number of attribute is called the degree; and a domain is a pool of values, from which the values of specific attributes of specific relation are taken.

  4. S# CITY NAME Paris… STATUE primary key S# S# SNAME NAME STATUS STATUS CITY CITY London Paris Athens Paris Smith Jones Blake Adams 20 10 30 20 S1 S2 S3 S4 relation tuple cardinality attribute degree 3.1.1 Basic Structure

  5. 3.1.2 Database Schema • Difference: database schemalogical design of the database, database instancesnapshot of the data in the database at a given instant in time relation schema relation relation instance int i i i=2

  6. 3.1.2 Database Schema • We adopt the convention of using lowercase names for relations, and names beginning with an uppercase letter for relation schemas. Example: we use Account-Schema to denote the relation schema for relation account Account-schema=(branch-name, account-number, balance) we denote the fact that account is a relation on Account-schema by account(Account-schema)

  7. branch account branch-name branch-city assets account-number branch-name balance loan loan-number branch-name amount 3.1.3 key and schema diagram Foreign key: referencing relation referenced relation referencing relation Foreign key referenced relation referencing relation

  8. 3.1.4 query language query language procedural language nonprocedural language relation algebra tuple relation calculus domain relation calculus

  9. 3.2 The Relational Algebra The relational algebra is a procedural query language. The consist of a set of operations that take one or two relations as input and produce a new relation as their result. The original algebra consisted of eight operators, two groups of four each: 1. The traditional set operators union, intersection, difference, and Cartesian product. 2. The special relational operators restrict (as known as select), project, join and divide.

  10. 1.The SelectOperation (  ) Select: Returns a relation containing all tuples from a specified relation that satisfy a specified condition. the select operation selects tuples that satisfy a given predicate. B=b(R) select A B C Relation R A B C a b c a b c b a f c b d c b d 3.2.1 Fundamental Operations Example:B=b(R)

  11. 3.2.1 Fundamental Operations Let relation A have attributesXandY, and let  be an operator-typically”＝”,”＞”, etc, --such that the condition X Y is well defined and, given particular values for X and Y, evaluates to a truth value(true or false), Then the --restriction of relation A on attributes X and Y—is a relation with the same heading as A and with body consisting of all tuples t of A such that the condition X Y evaluates to true for that tuple t . F(A) X＞ Y(A) X=1(A) [1] ＞[2](A)

  12. loan loan-number branch-name amount L-11 Round Hidd 900 L-14 Downtown 1500 L-15 Perryridge 1500 L-16 Perryridge 1100 L-17 Downtown 1000 loan-number branch-name amount L-15 Perryridge 1500 L-16 Perryridge 1100 Examples: ① select those tuples of the loan relation where the branch is “Perryridge” loan-number branch-name amount L-11 Round Hidd 900 3.2.1 Fundamental Operations ② select those tuples in which the amount lent is less than ＄1000 branch-name=“perryridge”(loan) amount<1000(loan)

  13. loan loan-number branch-name amount L-11 Round Hidd 900 L-14 Downtown 1500 L-15 Perryridge 1500 L-16 Perryridge 1100 L-17 Downtown 1000 loan-number branch-name amount L-15 Perryridge 1500 3.2.1 Fundamental Operations ③ select those tuples pertaining to loans of more than ＄1200 made by the Perryridge branch.  branch-name=“perryridge” ∧ amount>1200(loan)

  14. Loan-officer loan-number customer-name banker-name L-11 Curry Adams L-14 Jones Jackson L-15 Smith Smith loan-number customer-name banker-name L-15 Smith Smith 3.2.1 Fundamental Operations ④ find all customers who have the same name as their loan officer  customer-name=banker-name(loan-officer)

  15. project ∏A、C(R) A C Relation R a c A B C b f a b c c d b a f c b d 3.2.1 Fundamental Operations 2. The Project Operation pi ( ∏ ) Project: Returns a relation containing all tuples that remain in a specified relation after specified attributes have been removed. Example:

  16. loan loan-number branch-name amount L-11 Round Hidd 900 L-14 Downtown 1500 L-15Downtown1500 L-16 Perryridge 1300 L-17 Downtown 1000 loan-number amount L-11900 L-141500 L-151500 L-161300 L-171000 3.2.1 Fundamental Operations ① List all loan numbers and the amount of the loan ∏loan-number,amount(loan)

  17. 3.2.1 Fundamental Operations Notice: ⑴ No attribute can be mentioned more than once in the attribute name commalist. ⑵ If the attribute name commalist mentions all of the attributes of A, the projection is an identity projection. ⑶ A projection of the form A{}—I.e., one in which the attribute name conmmalist is empty—is legal. It represents a nullary projection.

  18. customer customer-street customer-city customer-name customer-name Spring Senator North Walnut Adams Brooks Glenn Hayes Pittsfield Harrison Rye Woodside Brooks 3.2.1 Fundamental Operations 3. Composition of Relational Operations relational—algebra operations can be composed together into a relational—algebra expression. Example: Find those customers who live in “Harrison” . ∏customer-name( customer-city=“Harrison”(customer))

  19. R ∪ S A B C Relation R Relation S A B C A B C abcb babg cfda a b c b g a b a f b a f c b d 3.2.1 Fundamental Operations 4. The Union Operation ( ∪ ) Union: Returns a relation containing all tuples that appear in either or both of two specified relations. Definition: Given two relations A and B of the same type, the union of those two relations, A∪B, is a relation of the same type, with body consisting all tuples t such that t appears in A or in B or in both. Example:

  20. depositor customer-name account-number Hayes L-16 Curry L-93 borrower customer-name loan-number Hayes A-102 Johnson A-101 customer-name Hayes Johnson Curry 3.2.1 Fundamental Operations ①Find the names of all bank customers who have either an account or a loan or both. ∏customer-name(borrower)∪∏customer-name(depositor)

  21. 3.2.1 Fundamental Operations For a union operation r∪s to be valid, we require that two conditions hold: ⑴ The relations r and s must be of thesame arity, that is, they must havethe same number of attributes. ⑵ The domains of the ith attribute of r and the ith attribute of s must be the same, for all i.

  22. R-S Relation R Relation S A B C A B C A B C a b c b g a b a f b a f c b d 3.2.1 Fundamental Operations 5. The set Difference Operation ( - ) Difference: Returns a relation containing all tuples that appear in the first and not the second of two specified relations. Definition: Given two relations A and B of the same type, then, the difference between those two relations, A-B, is a relation of the same type, with body consisting of all tuples t such that t appears in A are not in B. Example: a b c c b d

  23. depositor customer-name account-number Hayes L-16 Curry L-93 borrower customer-name loan-number Hayes A-102 Johnson A-101 customer-name Curry 3.2.1 Fundamental Operations ①Find all customers of the bank who have an account but not a loan. ∏customer-name(depositor)－∏customer-name(borrower)

  24. 3.2.1 Fundamental Operations Notice: ①For a difference operation A-B to be valid, we require that the relation ②A and B be of the same arity, and that the domains of the ith attribute of A and the ith attribute of B be the same.

  25. 3.2.1 Fundamental Operations 6. The Cartesian-Product Operation ( × ) Cartesian-Product: In mathematics, the Cartesian-Product of two sets is the set of all ordered pairs such that, in each pair, the first element comes from the first set and the second element comes fromthe second set. Thus, the Cartesian-Product of two relations would be a set of ordered pair of tuples We write the Cartesian-Product of relations r1 and r2 as r1× r2

  26. R×S A B C D E F Relation R Relation S A B C D E F a b c b g a b a f b a f c b d 3.2.1 Fundamental Operations Example: a b c b g a a b c b a f b a f b g a b a f b a f c d d b g a c d d b a f

  27. Problem: A problem will therefore arise if those two headings have any attribute name in common. How to distinguish between then. We do so here by attaching to an attribute the name of the relation from which the attribute originally name. For those attributes that appear in only one of the two schemas, we shall usually drop the relation-name prefex. loan borrower loan-number branch-name amount customer-name loan-number branch-name × customer-name loan-number branch-name loan-number branch-name amount 3.2.1 Fundamental Operations Example: r=borrower×loan the relation schema:(customer-name, borrower.loan-number, branch-name, loan.loan-number, branch-name,amount)

  28. borrower customer-name loan-number Hayes A-102 Johnson A-103 Smith A-101 loan loan-number branch-name amount A-103 Round Hidd 900 A-102 Perryridge 1500 A-101 Perryridge 1400 3.2.1 Fundamental Operations Example: Find the names of all customers who have a loan at the Perryride branch.

  29. customer-name branch-name amount borrower. loan. loan-number loan-number 3.2.1 Fundamental Operations × ∏customer-name( branch-name=“Perryridge” (borrower×loan)) Hayes A-102 A-103 Round Hidd900 A-102 Perryridge 1500 Hayes A-102 Hayes A-102 A-101 Perryridge 1400 Johnson A-103 A-103 Round Hidd900 Johnson A-103 A-102 Perryridge 1500 Johnson A-103 A-101 Perryridge 1400 A-103 Round Hidd900 Smith A-101 Smith A-101 A-102 Perryridge 1500 Smith A-101 A-101 Perryridge 1400

  30. customer-name amount branch-name borrower. loan. A-102 Perryridge 1500 Hayes A-102 loan-number loan-number A-101 Perryridge 1400 Hayes A-102 A-102 Perryridge 1500 Johnson A-103 Johnson A-103 A-101 Perryridge 1400 Smith A-101 A-102 Perryridge 1500 Smith A-101 A-101 Perryridge 1400 customer-name Hayes Smith 3.2.1 Fundamental Operations ∏customer-name( borrower.loan-number=loan.loan-number( branch-name=“Perryridge”(borrower×loan)))

  31. 3.2.1 Fundamental Operations 7. The Rename Operation (  ) ①The rename operator, denoted by the lower-case Greek letter rho(). Given a relational-algebra expression E, the expression x(E) returns the result of expression E under the name X. ② A second form of the rename operation is as follows: Assume that a relational algebra expression E has arity n, the expression x(A1,A2,……An)(E)returns the result of expression E under the name X, and with the attributes renamed to A1,A2,……An.

  32. branch-name account-number balance R Hidd L-101 300 Perry L-102 500 PerryL-103 450 branch-name account-number balance R Hidd L-101 300 Perry L-102 500 PerryL-103 450 Account × d 3.2.1 Fundamental Operations Examples: ① Find the largest account balance in the bank.

  33. account. d. account. account. d. account-number account-number branch-name balance balance d. branch-name R Hidd Perry Perry R Hidd Perry Perry R Hidd Perry Perry L-101 L-101 L-101 L-102 L-102 L-102 L-103 L-103 L-103 R Hidd R Hidd R Hidd Perry Perry Perry Perry Perry Perry L-101 L-102 L-103 L-101 L-102 L-103 L-101 L-102 L-103 300 300 300 500 500 500 450 450 450 300 500 450 300 500 450 300 500 450 3.2.1 Fundamental Operations

  34. account. d. account. account. d. account-number account-number branch-name balance balance d. branch-name Perry Perry Perry L-101 L-101 L-103 R Hidd R Hidd Perry L-102 L-102 L-102 300 300 450 500 450 500 account.balance 300 450 account.balance 500 account.balance 300 500 450 3.2.1 Fundamental Operations ∏balance(account) － ∏account.balance( account.balance<d.balance(account× d(account))) －

  35. 3.2.1 Fundamental Operations ② Find the names of all customers who live on the same street and in the same city as Smith. ∏customer.customer-name(customer.customer-street=smith-addr.street∧customer.customer-city=smith-addr.city(customer× smith-addr(street,city)(∏customer-street,customer-city(customer-name=“Smith(customer)))))

  36. 3.2.2 Additional Operations 1. TheSet-IntersectionOperation ( ∩ ) Suppose that we wish to find all customers who have both a loan and an account, using set intersection we can write. ∏customer-name(borrower)∩∏customer-name(depositor) Notice: We can rewrite any relation algebra expression using set intersection by replacing the intersection operation with a pair of set-difference operations as follows: r∩s=r－(r－s)

  37. R∩S R A B C S A B C A B C a e g c f i b d h a e i b d k c f l 3.2.2 Additional Operations Example: R－(R－S) a b c e d f

  38. 2.The Natural-Join Operation Typically, a query that involves a Cartesian Product includes a selection operation on the result of the Cartesian Product . The natural join is a binary operation that allows us to combine certain selections and a Cartesian Product into one operation. It is denoted by the “join ” symbol 3.2.2 Additional Operations

  39. Consider two relations r(R) and s(S). The natural join of r and s, denoted by r s is a relation on schema R∪S formally defined as following: ③ Remove duplicate attributes rs=∏R∪S ( r.A1=s.A1∧ r.A2=s.A2… ∧r.An=s.An(r×s)) ①Cartesian Product ② Select tuples Where R∩S={A1,A2, …An} 3.2.2 Additional Operations The natural join operation include three steps: ① From a Cartesian Product R×S ② Select those tuples which satisfy the requirements R.Ai=S.Ai ③ Remove duplicate attributes.

  40. R1 S1 Relation R1 Relation S1 A B C B C D A B C D 1 4 7 2 5 8 3 6 9 2 5 9 3 6 8 2 3 5 1 4 2 5 3 6 2 3 A B C B C D A B C B C D 3 6 1 1 1 4 2 2 2 5 3 3 3 6 2 5 9 9 3 6 8 8 2 3 5 5 1 4 2 5 3 6 2 5 2 3 R1 S1 A<D R1 S1 [3]=[2] 3.2.2 Additional Operations Example: ①

  41. ∏customer-name,loan-number,amount(borrower loan) ∏customer-name(borrowerdepositor) 3.2.2 Additional Operations ② Find the name of all customers who have a loan at the bank, and find the amount of the loan. Borrower-schema=(customer-name, loan-number) Loan-schema=(loan-number, branch-name,amount) ③ Find all customers who have both a loan and an account at the bank. Borrower-schema=(customer-name, loan-number) Depositor-schema=(customer-name, account-number)

  42. ∏branch-name(customer-city=“Harrison”(customeraccount depositor) 3.2.2 Additional Operations ④ Find the names of all brancheswith customers who have an account in the bank and who live in Harrison. Account-schema=(branch-name, account-number, balance) Customer-schema=(customer-name, customer-street, customer-city) Depositor-schema=(customer-name, account-number)

  43. R S 1 2 1 2 3 4 3 4 c1 d1 a1 b1 c1 d1 c2 d2 a1 b1 c2 d2 a1 b1 c3 d3 a2 b2 c1 d1 a2 b2 c2 d2 a3 b3 c1 d1 3.2.2 Additional Operations 3. The Division Operation ÷ Let r(R) and s(S) be relations, and let SR, that is every attribute of schema S is also in schema R. The relation r÷s is a relation on schema R－S—that is, on the schema containing all attributes of schema R that are not in schema S. r÷s a1 b1 a2 b2

  44. ∏customer-name,branch-name(depositor account) ÷∏branch-name(branch-city=“Brooklyn” (branch)) customer-name branch-name customer-name branch-name Johnson Hayes Johnson Johnson Turner Perryridge Downtown Brighton Brighton Downtown Brighton Example: find all customers who have an account at all the branches located in Brooklyn. 3.2.2 Additional Operations

  45. r÷s R S 1 2 3 4 3 4 1 2 a1 b1c1 d1 c1 d1 a1 b1 c2 d2 a1 b1c2 d2 3.2.2 Additional Operations A tuple t is in r÷s if and only if both of two conditions hold: ① t is in ∏R－S(r) ② For every tuples ts in s, there is a tuple tr in r satisfying both of the following: a. tr[S]= ts[S] b. tr[R－S]=t Given a division operation and the schemas of the relations, we can, in fact, define the division operation in terms of the fundament operations, let r(R) and s(S) be given, with S R r÷s= ∏R－S(r)－∏R－S((∏R－S(r) ×s)－∏R－S,S (r))

  46. R R S P P1 P2 P4 P2 P3 P2 P4 V V1 V1 V1 V2 V2 V3 V3 P P1 P2 P4 P2 P3 P2 P4 V V1 V1 V2 V2 V3 V3 V V1 V1 V1 V2 V2 V3 V3 P P2 P4 P2 P4 P2 P4 P P2 P4 V P V2 P4 V V1 V3 － V V1 V2 V3 × V V2 V V1 V2 V3 3.2.2 Additional Operations r÷s= ∏R－S(r)－∏R－S((∏R－S(r)×s)－∏R－S,S (r)) －

  47. 3.2.2 Additional Operations 4. The Assignment Operation ← It is convenient at times to write a relational-algebra expression in parts using assignment to a temporary relation variable. The assignment operation denoted by ←. Example:∏R-S(r)-∏R-S(∏R-S(r)×s) －r) temp1 ← ∏R-S(r) temp2 ←∏R-S(temp1×s) －r) result =temp1-temp2 For relation-algebra quires, assignment must always be made to a temporary relation variable. Assignments to permanent relations constitute a database modification.

  48. credit-info customer-namecredit-available curry 250 customer-namelimit credit-balance curry 1200 1750 3.3 Extended Relational-Algebra Operation allow arithmetic operations as part of projection outer-join aggregate operations ∏F1,F2,…Fn(E) • Generalized-projection Example: Suppose we have a relation credit-info which lists the credit limit and expenses so far. If we want to find how much more each person can spend, how to write the expression? ∏customer-name,(limit-credit-balance) as credit-available(credit-info)

  49. Examples: ① Find the total salary of all the part workers. pt-works employee-name branch-name salary Admas Perryridge 1500 Brown Perryridge 1300 sum of salary 16500 Gopal Perryridge 5300 Johnson Downtown 1500 Loreena Downtown 1300 Petrson Downtown 2500 count of branch-name Rao Austin 1500 3 Sato Austin 1600 3.3 Extended Relational-Algebra Operation • Aggregate function sum avg min max count ② Find the number of the branch in the pt-works relation. Gsum(salary)(pt-works) Gcount-distinct(branch-name)(pt-works)

  50. employee-name branch-name salary Admas Perryridge 1500 Brown Perryridge 1300 Gopal Perryridge 5300 branch-name sum of salary Johnson Downtown 1500 Loreena Downtown 1300 Perryridge 8100 Petrson Downtown 2500 Downtown 5300 Rao Austin 1500 Austin 3100 Sato Austin 1600 pt-works 3.3 Extended Relational-Algebra Operation Group branch-nameGsum(salary)(pt-works)

More Related