Create Presentation
Download Presentation

Download Presentation
## from relational algebra to sql

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**1. **From Relational Algebra to SQL CS 157B
Enrique Tang

**3. **Relational Algebra Defined:Tuple An ordered set of data values.
{ a1 , a2 , a3 ,
, an }

**4. **Relational Algebra Defined: Relation A set of tuples.
{ { a1, a2, a3,
, an },
{ b1, b2, b3,
, bn },
{ c1, c2, c3 ,
, cn },
.
.
}

**5. **Relational Algebra Defined: Algebra Any formal mathematical system consisting of a set of objects and operations on those objects.
Based on operators and a domain of values
Operators map arguments from domain into another domain value
Example: x = 3.5 * y

**6. **Relational Algebra Defined: Relational Algebra An algebra whose objects are relations and whose operators transform relations into other relations.
Domain: set of relations, i.e., result is another relation
Basic operators: select, project, union, set difference, Cartesian product (or cross product)

**7. **Relational Algebra Defined:Where is it in DBMS?

**8. **Operations (Unary):Selection, Projection Selection: ? <condition(s)> (<relation>)
Picks tuples from the relation
Projection: ? <attribute-list> (<relation>)
Picks columns from the relation

**9. **Operations (Set):Union, Set Difference Union: (<relation>) U (<relation>)
New relation contains all tuples from both relations, duplicate tuples eliminated.
Set Difference: R S
Produces a relation with tuples that are in R but NOT in S.

**10. **Operations (Set):Cartesian Product, Intersect Cartesian Product: R x S
Produces a relation that is concatenation of every tuple of R with every tuple of S
The Above operations are the 5 fundamental operations of relational algebra.
Intersection: R S
All tuples that are in both R and S

**11. **Operations (Join):Theta Join, Natural Join Theta Join: R F S = ? F (R x S)
Select all tuples from the Cartesian product of the two relations, matching condition F
When F contains only equality =, it is called Equijoin
Natural Join: R S
Equijoin with common attributes eliminated

**12. **Operations:Outer Join, Semi Join (left) Outer Join: R S
Natural join relations while preserving all tuples from the outer side -> NULL values incurred.
Semi Join: R F S = ?A (R F S)
Join two relations and only keeps the attributes seem in relation R
There are Semi-Theta Join, Semi-Equijoin and Semi-Natural Join

**13. **Operations:Division Division: R χ S
Produce a relation consist of the set of tuples from R that matches the combination of every tuple in S R S RχS
T1 ? ?c (R)
T2 ? ?c ((SxT1)R)
T ? T1 T2

**14. **Translation to SQL FROM clause produces Cartesian product (x) of listed tables
WHERE clause assigns rows to C in sequence and produces table containing only rows satisfying condition ( sort of like ? )
SELECT clause retains listed columns (? )

**15. **Translation to SQL (Cont.) SELECT C.CrsName
FROM Course C, Teaching T
WHERE C.CrsCode=T.CrsCode AND T.Sem=F2003
List CS courses taught in F2003
Tuple variables clarify meaning.
Join condition C.CrsCode=T.CrsCode
eliminates garbage
Selection condition T.Sem=F2003
eliminates irrelevant rows
Equivalent (using natural join) to:
?CrsName(Course ?Sem=F2003 (Teaching) )
?CrsName (?Sem=F2003 (Course Teaching) )