Relational Databases I Database Query Languages - PowerPoint PPT Presentation

1 / 22
About This Presentation
Title:

Relational Databases I Database Query Languages

Description:

Devised in 1970 by Codd 1. Relational database is a collection of relations which are time-varying and of ... intension ~ record type. extension ~ all instances ... – PowerPoint PPT presentation

Number of Views:108
Avg rating:3.0/5.0
Slides: 23
Provided by: davidala3
Category:

less

Transcript and Presenter's Notes

Title: Relational Databases I Database Query Languages


1
Relational Databases I Database Query
Languages
  • David Nelson
  • CAT
  • March 2007

2
Relational Database Querying
  • Relational Model
  • Relational Algebra
  • Relational Calculus
  • SQL

3
Relational Model
  • Devised in 1970 by Codd 1
  • Relational database is a collection of relations
    which are time-varying and of assorted degrees
    and cardinalities
  • Common terminology
  • relation table
  • tuple one record occurrence (a row)
  • attribute value one field occurrence (a
    column)
  • intension record type
  • extension all instances

1 E. F. Codd, A relational Model of Data for
Large Shared Data Banks, CACM, 13(6), June 1970
4
Relational Model
  • Based on set theory
  • based on mathematical relations
  • no duplicate tuples
  • no tuple ordering
  • no significant attribute ordering
  • all attribute values are from the same domain
  • all attribute values are atomic
  • Use keys to provide link between relations
  • superkey, primary key, candidate key, foreign key

5
Integrity Rules
  • Entity integrity
  • no attribute that is part of a primary key can
    have a null value
  • Referential integrity
  • all values for foreign key attributes must be
    locatable in the value-sets for their
    corresponding primary key attributes
  • Application dependent rules
  • e.g. if A 2 then X gt 100 and S gt 45, where A,
    X, and S are attributes

6
Manipulation
  • No system is fully relational unless it supports
    either the relational algebra or the relational
    calculus
  • A language is said to be relationally complete
    if it is at least as powerful as the algebra
    (Date, 2000)
  • i.e. can produce every query that the (original)
    relational algebra can
  • Relational calculus is the basis of SQL
  • ISO standard relational data manipulation
    language found in Oracle, Ingres, DB2, SQLServer,
    mySQL, etc.
  • Even Access!

7
Relational Algebra
  • Gives closure - results of operations are
    relations
  • Relationally complete
  • but not computationally complete
  • Original Operations
  • union ? intersect ?
  • product ? difference ?
  • select ? join-theta ?
  • project ? join-natural
  • divide /

8
Operations
  • Union (A ? B)
  • returns set of all tuples belonging to A and/or B
    relations. Duplicates are purged.
  • Intersect (A ? B)
  • returns set of all tuples belonging to A and B
    relations.
  • Difference (A - B)
  • returns set of all tuples belonging to A and not
    to B.
  • Union, Intersect and Difference are all union
    compatible.

9
Operations (cont)
  • Times/Product (A ? B)
  • concatenates tuples from one relation B to those
    in another relation A for all possible
    permutations.
  • Select (?agegt50(Patient))
  • return tuples from relation where qualifier is
    true.
  • Also known as RESTRICT
  • Project (?x,y(R))
  • returns selected attributes for all tuples in
    relation R

10
Operations (cont)
  • Join-natural (A B)
  • qualified extended cartesian product (times) of A
    and B, where the product is limited to inclusion
    of tuples with equal values for attributes of
    same name (and type)
  • A B ?A.a1, A.am, B.br, B,bs (?A.ciB.ci
    ? ? A.cj B.cj(A ? B))
  • Join-theta (A ? join-condition B)
  • any join that is not natural, i.e. has a join
    condition
  • equijoin is a theta-join where the comparison
    operator is

11
Operations (cont)
  • Division (?x,y(A) / ?y(B))
  • result is a unary relation containing x values
    that appear paired in A for all values in B.
  • Can be defined in terms of other operations
  • X ? ?x(A) candidates
  • Y ? ?x(A) ? ?y(B) all possible pairs
  • Z ? ?x,y(A) actual pairs
  • W ? Y - Z missing pairs
  • V ? ?x(W) As missing Bs
  • ANS ? X - V As not missing Bs

12
Additional Operators
  • EXTEND
  • allow computation and aggregate operators
  • COUNT, SUM, AVG, MAX, MIN, ALL, ANY
  • Distinct COUNTD, SUMD, AVGD
  • e.g. EXTEND LEASE ADD RENT_PM12 AS RENT_PY
  • SUMMARIZE
  • equivalent to a group by expression in SQL
  • SUMMARIZE SP BY P ADD SUM QTY AS TOTAL
  • Derives relation including attributes P and TOTAL

13
More JOINS
  • REGULAR (INNER) JOIN
  • Loses information when no match on join condition
  • SEMIJOIN
  • Equivalent to natural join but only includes
    attributes from left relation
  • OUTER JOIN
  • Adds nulls to result to avoid information loss
  • Three versions
  • LEFT preserve all tuples in the left relation
  • RIGHT preserve all tuples in the right relation
  • FULL preserve all tuples in both relations
  • Also OUTER UNION, INTERSECT, DIFFERENCE

14
Purpose of Relational Algebra
  • Fundamental intent of the algebra is to allow
  • Writing of expressions
  • Can be used for
  • Retrieval, updates, view definitions, security
    rules,
  • A relational database normally provides SQL as
    the user interface
  • But the system will generally convert the query
    into algebra
  • Allows for easy optimisation of the queries using
    transformation rules
  • Even XML uses a query algebra!

15
Relational Calculus
  • Alternative to relational algebra
  • algebra provides a collection of explicit
    operations to tell system how to construct some
    desired relation from other sets, i.e. a
    procedural language
  • calculus is a notation for stating definition of
    the desired relation in terms of given relations
  • descriptive rather than prescriptive
  • i.e. non-procedural
  • there is a one-to-one mapping between the algebra
    and calculus

16
Relational Calculus
  • Based on predicate calculus
  • Two versions when applied to databases
  • tuple-oriented
  • domain-oriented
  • We will look at tuple-oriented

17
Tuple-Oriented Relational Calculus
  • Interested in finding tuples for which a
    predicate is true
  • Based on the use of tuple variables
  • a variable that ranges over a named relation
  • e.g. RANGE OF S IS Staff
  • S P(S)
  • where P is the Predicate applied to S, e.g.
  • S S.Salary gt 10000

18
Relational Calculus Quantifiers
  • There are two quantifiers
  • existential quantifier ? (there exists)
  • universal quantifier ? (for all)
  • e.g. RANGE OF B IS Branch
  • B ? B (B.Bno S.Bno ? B.City London)
  • B ? B (B.City Paris)
  • B ? B (B.City Paris)
  • SQL has direct support of EXISTS

19
SQL
  • SQL is a mix of relational calculus and
    relational algebra
  • includes direct support of join and union from
    relational algebra
  • uses range variables and existential quantifier
    of the calculus
  • SQL queries formulated as table expressions
  • e.g. SELECT PX.COLOUR, PX.CITY
  • FROM P AS PX
  • WHERE PX.CITY ltgt Paris AND PX.WEIGHT gt 10.0

20
Example Query
  • SQL
  • SELECT PX.COLOUR, PX.CITY
  • FROM P AS PX
  • WHERE PX.CITY ltgt Paris AND PX.WEIGHT gt 10.0
  • Algebra
  • ?COLOUR, CITY (sCITY ltgt Parisgt ? WEIGHT gt
    10.0(P))
  • Calculus
  • RANGE OF PX IS P
  • PX.COLOUR, PX.CITY PX.CITY ltgt Paris ?
    PX.WEIGHT gt10.0

21
Summary
  • Relational Algebra and Relational Calculus are
    relationally complete
  • Algebra is prescriptive (procedural)
  • Calculus is descriptive (non-procedural)
  • Relational Calculus forms the basis of SQL
  • But also provides algebra operations, e.g. UNION,
    JOIN
  • Relationally complete
  • Relational algebra is normally used in optimisers
    in RDBMS

22
Further Reading
  • Date chapters 6 and 7.
  • Connolly and Begg chapters 3 and 4.
Write a Comment
User Comments (0)
About PowerShow.com