1 / 17

Intro to Relational Model: Structure, Algebra, and Query Languages

This chapter provides an introduction to the relational model, covering the structure of relational databases, relational algebra, and query languages. Topics include relation schema, keys, query languages, and the six basic operators of relational algebra.

mkatz
Download Presentation

Intro to Relational Model: Structure, Algebra, and Query Languages

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 2Intro to Relational Model 2nd Semester, 2017 Sanghyun Park

  2. Outline • Structure of relational databases • Relational algebra

  3. Structure of Relational Databases • Given sets D1, D2, …, Dn,a relation r is a subset of D1 x D2 x … Dn Order of tuples is irrelevant (tuples may be stored in an arbitrary order)

  4. Relation Schema • Each attribute of a relation has a name • The set of allowed values for each attribute is called domain of the attribute • When A1, A2, …, An are attributes,R = (A1, A2, …, An) is a relation schema;e.g. Instructor-schema = (ID, name, dept_name, salary) • r(R) is a relation on the relation schema R;e.g. instructor(Instructor-schema)

  5. Keys • Let R = (A1, A2, …, An) be a relation schema and K  R • K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) • K is a candidate key if K is minimal • K is a primary key if it is a candidate key and it is chosen by the database designer as the principal means of identifying tuples within a relation

  6. Query Languages • Language with which user requests information from the database • Categories of languages • Procedural • Non-procedural • “Pure” languages • Relational algebra (procedural) • Tuple relational calculus (non-procedural) • Domain relational calculus (non-procedural) • Pure languages form underlying basis of query languages that people use

  7. Relational Algebra • Procedural language • Six basic operators • Select • Project • Union • Set difference • Cartesian product • Rename • The operators take one or more relations as inputsand give a new relation as a result

  8. Select Operation A=B ^ D > 5 (r) relationr A B C D A B C D         1 5 12 23 7 7 3 10     1 23 7 10

  9. Project Operation A,C (r) relationr A B C A C A C     10 20 30 40 1 1 1 2     1 1 1 2    1 1 2 =

  10. Union Operation relationr relations r s A B A B A B    1 2 1   2 3     1 2 1 3

  11. Set Difference Operation relationr relations r -s A B A B A B    1 2 1   2 3   1 1

  12. Cartesian Product Operation relationr relations r xs C D E A B A B C D E     10 10 20 10 a a b b   1 2         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b

  13. Rename Operation • Allows us to name the results of relational-algebra expressions • Allows us to refer to a relation by more than one name • x(E) returns the expression E under the name X • If a relational-algebra expression E has arity n, thenx(A1,A2,…,An) (E)returns the result of expression E under the name X, and with the attributes renamed to A1, A2, …, An

  14. Additional Operations • We define additional operations that do not addany power to the relational algebra, but that simplify common queries • Set intersection: r  s • Natural join: r s • Division: r  s • Assignment: temp1 R-S(r)

  15. Natural Join • 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 attributesin R  S, add a tuple t to the result, where t has the same value as tr on r, and t has the same value as ts on s

  16. Natural Join Example relationr relations r s B D E A B C D E A B C D      1 1 1 1 2      a a a a b      1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b

  17. Outer Join • An extension of the join operation that avoids loss of information • Computes the join and then adds tuples from one relation that do not match tuples in the other relation to the result of the join • Uses NULL values • Left outer join, right outer join, full outer join

More Related