rensselaer polytechnic institute csci 4380 database systems david goldschmidt ph d
Download
Skip this Video
Download Presentation
Database Systems {week 02}

Loading in 2 Seconds...

play fullscreen
1 / 11

Database Systems {week 02} - PowerPoint PPT Presentation


  • 121 Views
  • Uploaded on

Rensselaer Polytechnic Institute CSCI-4380 – Database Systems David Goldschmidt, Ph.D. Database Systems {week 02}. Selection (review). Find and select all tuples from relation R that satisfy some set of conditions Forms the basis of querying a database

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Database Systems {week 02}' - charles-salas


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
rensselaer polytechnic institute csci 4380 database systems david goldschmidt ph d
Rensselaer Polytechnic Institute

CSCI-4380 – Database Systems

David Goldschmidt, Ph.D.

Database Systems{week 02}

selection review
Selection (review)
  • Find and select all tuples from relation Rthat satisfy some set of conditions
    • Forms the basis of querying a database
    • The selectionC (R) is based on Boolean condition C over attributes of relation R
    • Example conditions include:
      • A = e, A > e, A >= e, A < e, A <= e, A <> e
      • A1 = A2, A1 <> A2
      • Any combination of conditions using AND, OR, NOT

A, A1, and A2 are attributes

e is a constant or expression

selection review1
Selection (review)
  • Selection selects a subset of tuples in relation R (with the schema unchanged)
    • C(R) = { t | t is a tuple in R and t satisfies the condition C on relation R }
    • Selection conditions can only refer toattributes in the given relation R
    • For conditions spanning multiple relations, we first must combine those relations (i.e. join)
cartesian product
Cartesian product
  • The Cartesian product combinestwo relations to form a new relation
    • The new relation has all of the attributesof the original two relations
    • It’s often a good idea to rename attributesin the original relations such thatthere’s no ambiguity
cartesian product1
Cartesian product
  • Given two relations R and S withschemas R(A1,A2,...,An) and S(B1,B2,...,Bm)
    • The Cartesian product RxS producesrelation T with schema T(A1,A2,...,An,B1,B2,...,Bm)
    • We can disambiguate attributesby using R.Ax and S.By
      • i.e. attributes Ax and By have the same name
cartesian product2
Cartesian product
  • The Cartesian product of relations R and S:
    • RxS = { (r,s) | r is a tuple in R and s is a tuple in S }
      • Note that (r,s) has all values in r and s
    • The Cartesian product is like multiplicationin that it produces a tuple for every pair oftuples from R and S
      • e.g. if R has 10 tuples and S has 5 tuples, then RxS will have 50 tuples

So why do we want to do this?

slide7
Join
  • Cartesian product RxS is often followed by a selection condition that specifies how tuples in R should be matched to tuples in S
    • name=student(Undergraduate x Advising)
    • Translation: Join the Undergraduate and Advising relations by selecting tuples in which the name attribute equals the student attribute
natural joins
Natural joins
  • When joining two relations, we often want to join on common (same-named) attributes
    • This is a natural join on relations R and Sand is denoted R⋈S
    • The selection condition selects tuples that have the same values for same-named attributes
    • Note that the schema does not repeat thesame-named attributes
theta joins
Theta joins
  • Joins based on specific conditions arecalled theta joins and are denoted R ⋈C S
    • To perform a theta join, first take theproduct of relations R and S
    • Next, select tuples that satisfy condition C
    • The resulting schema is the union of theschemas of R and S with R.Ax and S.By prefixes,if necessary

In a theta join, nodeduplicationof attributes is performed!

rename
Rename
  • The rename operator changes thename of the attributes of relation Rwith schema R(A1,A2,...,An)
    • S(B1,B2,...,Bn)(R) = relation S(B1,B2,...,Bn) in which R.A1 is renamed S.B1, R.A2 is renamed S.B2, and so on
    • To only change some attribute names,simply specify Bi = Ai
exercises
Exercises
  • Find all faculty who advise a student
  • Find all faculty who do not adviseany students
  • Find faculty who advise at least two students
  • Find faculty who advise a student that is not in their department (e.g. dual majors)
ad