1 / 29

Relational Database Models

Relational Database Models. Basic Concepts Relational Theory. Summary: Basic concepts of relational model. Relation: relation, attribute, tuple Relation as analogue of file: cf. file, field type, record Relational scheme for a database: cf. file system

dvaughn
Download Presentation

Relational Database Models

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. Relational Database Models Basic Concepts Relational Theory CS319 Theory of Databases

  2. Summary: Basic concepts of relational model • Relation: relation, attribute, tuple • Relation as analogue of file: cf. file, field type, record • Relational scheme for a database: cf. file system • - Degree and cardinality of a relation • - Intensional & extensional views of a relational scheme • Keys: primary, candidate, foreign • Constraints: key, referential, integrity CS319 Theory of Databases

  3. Query Languages for Relational Databases 1 • Issue: how do we model data extraction formally? • E.F. (“Ted”) Codd is the pioneer of relational DBs • Early papers: 1969, 70, 73, 75 • Two classes of query language: algebra / logic • 1. Algebraic languages • a query = evaluating an algebraic expression • 2. Predicate Calculus languages • a query = finding values satisfying predicate CS319 Theory of Databases

  4. Query Languages for Relational Databases 2 • Issue: how do we model data extraction formally? • 2. Predicate Calculus languages • a query = finding values satisfying predicate • Two kinds of predicate calculus language • Terms (primitive objects) tuples xor domain values: • tuples  tuple relational calculus • domain values  domain relational calculus CS319 Theory of Databases

  5. Query Languages for Relational Databases 3 • Examples of Query Languages • algebraic: ISBL - Information System Base Language • tuple relational calculus: QUEL, SQL • domain relational calculus: QBE - Query by Example • Issue: how are these languages to be compared? CS319 Theory of Databases

  6. Query Languages for Relational Databases 4 • Issue: how are query languages to be compared? • Answer (Codd) • Can formulate a notion of completeness, and show that the core queries in these languages have equivalent expressive power • mathematical notion, based on relationalalgebra • in practice, is a basic measure of expressive power: • practical query languages are ‘more than complete’ CS319 Theory of Databases

  7. Relational Algebra 1 • Relational Algebra • algebra = underlying set with operations on it • elements of the underlying set are referred to as • "elements of the algebra" • relational algebra = set of relations + ops on relations • cf set of polynomials with addition and multiplication CS319 Theory of Databases

  8. Relational Algebra 2 • … relational algebra = set of relations + ops on relations • Definition: a (mathematical) relation • is a subset of D1 D2 ....  Dr • where D1, D2, ...., Dr are domains • Typical element of a relation is (d1, d2, ...., dr) • where di Di for 1  i  r • D1 D2 ....  Dr is the type of the relation • r is the arity of the relation CS319 Theory of Databases

  9. Relational Algebra 3 • Mathematical relation is an abstraction • types are restricted to mathematical types • e.g. height, weight and currency all numerical data • components of a mathematical relation are indexed • don't use named attributes in the mathematical treatment - in effect, named attributes just make it more convenient to specify relational expressions • .... ‘abstract’ expressive power unchanged CS319 Theory of Databases

  10. Summary of Relational Algebra concepts • Primitive operations: • 1. Union R  S • 2. Set Difference R – S • 3. Cartesian Product R × S • 4. Projection i(1), i(2), ..., i(t) (R) • 5. Selection F(R) • Derived operations: intersection, natural join, quotient • Codd’s definition of completeness: • a query language is complete if it can simulate all 5 basic operations on relations CS319 Theory of Databases

  11. ISBL: A Relational Algebra Query Language 1 • ISBL - Information System Base Language • Devised by Todd in 1976 • IBM Peterlee Relational Test Vehicle (PRTV) • PL/1 environment with query language ISBL • One of the first relational query languages • … closely based on relational algebra • The six basic operations in ISBL are union, difference, intersection, natural join, projection and selection CS319 Theory of Databases

  12. ISBL: A Relational Algebra Query Language 2 • Operators in ISBL are ‘+’, ‘-’, ‘%’, ‘:’ and ‘*’ . • R+S union of relations • R - S difference operation with extended semantics • R % A, B, ... , Z projection onto named attributes • R : F selection of tuples subject to boolean formula F • R . S intersection • R * S natural join • R - S is defined whenever R and S have some attribute names in common: delete tuples from R that agree with S on all common attributes. CS319 Theory of Databases

  13. ISBL: A Relational Algebra Query Language 3 • Comparison: Relational Algebra vs ISBL • R  S R+S • R – S R-S subsumes • R × S no direct counterpart • i(1), i(2), ..., i(t) (R) R % A, B, ... , Z • F(R) R : F • contrived derived op R * S • To prove completeness of ISBL, enough to show that can express Cartesian product using the ISBL operators - return to this issue later CS319 Theory of Databases

  14. ISBL: A Relational Algebra Query Language 4 • ISBL as a query language • Two types of statement in ISBL • LIST <exp> print the value of exp • R = <exp> assign value of exp to relation R • In this context, R is a variable whose value is a relation • Notation: use R(A,B,...,Z) to refer to a relation with attributes A, B, ..., Z CS319 Theory of Databases

  15. ISBL: A Relational Algebra Query Language 5 • Example ISBL query to specify the composition of two binary relations R(A,B) and S(C,D) where A,B,C,D are attributes defined over the same domain X (as when defining composition of functions XX): • Specify composition of R and S as RCS, where • RCS = (R * S) : B=C % A, D • In this case: R * S = R × S because attribute names (A, B), (C, D) are disjoint [cf. completeness of ISBL] • Illustrates archetypal form of query definition: • projection of selection of join CS319 Theory of Databases

  16. ISBL: A Relational Algebra Query Language 6 • Assignment and call-by-value • After the assignment • RCS = (R * S) : B=C % A, D • the variable RCS retains its assigned value whatever happens to the values of R and S • Hence all subsequent "LIST RCS" requests obtain same value until reassignment • cf call-by-value parameter passing mechanisms CS319 Theory of Databases

  17. ISBL: A Relational Algebra Query Language 7 • Delayed evaluation and call-by-name • have a delayed evaluation mechanism to change the semantics of assignment cf. a "definitive notation" or a spreadsheet definition • to delay the evaluation of the relation named R in an expression, use N!R in place of R • RCS = (N!R * N!S) : B=C % A, D • this means that the variable RCS is evaluated on a call-by-name basis: i.e. it’s value is computed as required using the current values of R and S • whenever the user invokes "LIST RCS" in this case, the value of RCS is re-computed CS319 Theory of Databases

  18. ISBL: A Relational Algebra Query Language 8 • Uses for delayed evaluation • definition of views is facilitated • allows incremental definition of complex expressions: use sub-expressions with temporary names, supply extensional part later • useful for optimisation: assignment means immediate computation at every step, delayed evaluation allows intelligent updating of values CS319 Theory of Databases

  19. ISBL: A Relational Algebra Query Language 9 • Renaming • For union & intersection, attribute names must match • e.g. R(A,B) + S(A,C) is undefined etc. • To overcome this can rename attributes of R by • (R%A, B  C) • This project-and-rename creates relation R(A,C). • Can use this to make attributes of R & S disjoint, so that • R * S = R × S, • proving that ISBL is a complete query language CS319 Theory of Databases

  20. Tensions between theory and practice in ISBL • Mathematical relations abstract away certain characteristics of data that are important to the human interpreter – e.g. types, order for table inspection • Certain activities that are an essential part of data processing, such as updating relations, forming aggregates etc are not easy to describe formally • Classical algebra uses homogeneous data types, doesn’t deal elegantly with exceptions 3/0 = ? etc CS319 Theory of Databases

  21. ISBL: A Relational Algebra Query Language 10 • Limitations of ISBL • ISBL is complete, but lacks features of QUEL, SQL etc • e.g. no aggregate operators • no insertion, deletion and modification • Primarily a declarative query language • Address these issues in the PRTV environment - user can also access relations via the general-purpose programming language PL/1 CS319 Theory of Databases

  22. ISBL: A Relational Algebra Query Language 11 • Illustrative examples of ISBL use • Refer to the Happy Valley Food Company [Ullman 82] • Relations in this DB are: • MEMBERS(NAME, ADDRESS, BALANCE) • ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) • SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) CS319 Theory of Databases

  23. ISBL: A Relational Algebra Query Language 12 • Illustrative examples of ISBL use • MEMBERS(NAME, ADDRESS, BALANCE) • ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) • SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • 1. Print the names of members in the red: • LIST MEMBERS : BALANCE < 0 % NAME • i.e. select members with negative balance and project out their names CS319 Theory of Databases

  24. ISBL: A Relational Algebra Query Language 13 • Illustrative examples of ISBL use • MEMBERS(NAME, ADDRESS, BALANCE) • ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) • SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • 2. Print the supplier names, items & prices for suppliers who supply at least one item ordered by Brooks • OS = ORDERS * SUPPLIERS • LIST OS: NAME="Brooks" % SNAME, ITEM, PRICE • ... a simple example of project-select-join CS319 Theory of Databases

  25. ISBL: A Relational Algebra Query Language 14 • Illustrative examples of ISBL use • MEMBERS(NAME, ADDRESS, BALANCE) • ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) • SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • 2. (commentary on answer) Need two of the relations: • SUPPLIERS required for supplier details • ORDERS to know what Brooks has ordered • The join OS holds tuples where item field contains item • "ordered with associated order info” and • "supplied by supplier with assoc supplier info" • … tuples featuring Brooks' name correspond to an item ordered by Brooks with its associated supplier details CS319 Theory of Databases

  26. ISBL: A Relational Algebra Query Language 15 • 3. Print suppliers who supply every item ordered by Brooks • "Every item" is universal quantification • Strategy: translate (x)(p(x)) to (x)(p(x)) • find suppliers who don't supply at least one of the items that is ordered by Brooks, and take the complement of this set of suppliers • Notation:  is “for all”,  is “there exists”,  is “not” CS319 Theory of Databases

  27. ISBL: A Relational Algebra Query Language 16 • 3. ... suppliers supplying every item ordered by Brooks • S = SUPPLIERS % SNAME • I = SUPPLIERS % ITEM • NS = (S * I) - (SUPPLIERS % SNAME, ITEM) • S records all supplier names, and I all items supplied • NS is the "does not supply" relation: all supplier-item pairs with pairs such that s supplies i eliminated • Now specify items ordered by Brooks ... • B = ORDERS : NAME="Brooks" % ITEM CS319 Theory of Databases

  28. ISBL: A Relational Algebra Query Language 17 • 3. … suppliers supplying every item ordered by Brooks • NS = "doesn't supply" relation • B = "items ordered by Brooks" • ... find suppliers who don't supply at least one item in B • NSB = NS.(S * B) • .... set of (supplier, item) pairs such s doesn't supply i and Brooks ordered i. • Answer is the complement of this set: • S - NSB % SNAME CS319 Theory of Databases

  29. To follow …Relational Theory: Algebra and CalculusSQL review CS319 Theory of Databases

More Related