9. Intro to Relational Algebra

1
9. Intro to Relational Algebra

• J. Stephen Schiavo
• Missouri Southern State University

2
Intro to Relational Algebra

• What is Relational Algebra
• relational algebra
• ltdatabase, theorygt A family of well-founded
  semantics used for modeling the data stored in
  relational databases, and defining queries on it.
  
• (Free Online Dictionary of Computers)
• Definitions, syntax, examples
• Exercises

3
What is Relational Algebra

• A formal structure for defining sets, and
  operations upon those sets
• (A database is nothing but a rigorously
  structured set.)
• A way of describing a database operation with
  precision
• A specification language

4
Definitions Restrict (Select) Project

• Restrict filters rows , not, lt, lt, gt, gt, v
  (or), (and)
• Symbolized as s (small sigma) sLName
  Smith(Student)
• Some textbooks use WhereStudent where LName
  Smith

Some sources use Select instead of Restrict, but
Its easy to confuse the algebraic Select with
the SQL Select the former filters rows the
latter filters columns!
SQL Select from Student Where LName Smith
5
Definitions Restrict (Select) Project

• Project filters columns
• Sometimes symbolized as ? (capital pi) ?
  CustomerID(Client)
• Some textbooks use square bracketsClientCustomer
  ID

SQL Select CustomerID from Client
6
Definitions Cartesian Product

• Cartesian product ,symbolized by X, combines
  information from rows of one set with rows of
  another
• If set a has n members, and set b has m members,
  then a X b has n x m members.
• For 5,000 students, and 250 faculty Student X
  Faculty returns 1,250,000 rows.
• Only when you Select those (for instance)
  where AdvisorSSN Faculty.SSN is the product
  very useful, as a rule.

SQL Select CompanyName, OrderID from Customers,
Orders Where Customers.CustomerId
Orders.CustomerID
7
Definitions Joins

• Natural join (also called inner, or simple
  join)filters rows of a Cartesian product
• Rows of one set match up with rows of another.

SQL Select CompanyName, OrderID from Customers,
Orders Where Customers.CustomerId
Orders.CustomerID or Select CompanyName,
OrderID from Customers Inner Join Orders On
Customers.CustomerId Orders.CustomerID
8
Definitions Joins

• Outer join filters rows of a Cartesian product
• including all rows of one set, and only matching
  rows of the other.

() means If no match, leave these fields null
SQL Select CompanyName, OrderID from Customers,
Orders Where Customers.CustomerId
Orders.CustomerID() or Select CompanyName,
OrderID from Customers, Orders Where
Customers.CustomerId Orders.CustomerID or
Select CompanyName, OrderID from Customers Left
Join Orders On Customers.CustomerId
Orders.CustomerID
() means If no match, leave right fields null
or Left Outer Join
9
Definitions Natural Join

• Students Faculty
• Name GPA Advisor Name Dept Tenure
• Able 3.15 Oakes Collins CIS No
• Baker 2.75 Herr Herr CIS No
• Carter 2.98 Collins Mays CIS Yes
• Davis 3.98 Mays Tunnell CIS Yes
• Results of the join
• Baker 2.75 Herr Herr CIS No
• Carter 2.98 Collins Collins CIS No
• Davis 3.98 Mays Mays CIS Yes

Note Student Able and Faculty Tunnel do not
appear in the result, because they are unmatched
in the other set
10
Definitions Outer Join

• Students Faculty
• Name GPA Advisor Name Dept Tenure
• Able 3.15 Oakes Collins CIS No
• Baker 2.75 Herr Herr CIS No
• Carter 2.98 Collins Mays CIS Yes
• Davis 3.98 Mays Tunnell CIS Yes
• Results of a Left Outer Join of Students to
  Faculty
• Able 3.15 Oakes - - - - - - -
  - - -
• Baker 2.75 Herr Herr CIS No
• Carter 2.98 Collins Collins CIS No
• Davis 3.98 Mays Mays CIS Yes

All Students (on the left) are included, but only
the matching advisors.What would a Right Outer
Join of Students to Faculty look like?
11
Definitions Union, Intersection, Difference

• Set-intersection returns tuples occurring in both
  of two different sets.
• Symbolized by nStudentClass Senior n
  StudentGPA gt 3.0
• Some textbooks use andStudent where Class
  Senior and Student where GPA gt 3.0

SQL Select CompanyName from Customers Where
Country USA and CustomerID like A
12
Definitions Union, Intersection, Difference

• Union returns all tuples that occur in either of
  two (or more) sets.
• Symbolized by U textbook uses orStudentClass
  Senior U StudentGPA gt 3.0Student where
  Class Senior Or Student where GPA gt 3.0

SQL Select CompanyName from Customers Where
Country USA or CustomerID like A
13
Definitions Union, Intersection, Difference

• Set difference returns tuples that are in one set
  but not in another.
• Symbolized by StudentClass Senior
  StudentGPA gt 3.0Student where Class Senior
  Student where GPA gt 3.0

SQL Select CompanyName from Customers Where
Country USA and CustomerID not like A
14
Definitions Rename Assign

• Rename gives the results of a relational-algebra
  expression a name, to be referred to later.
• Sometimes symbolized by ?
• Used to avoid having to specify the same set
  operation more than once as when you create a
  set operation, then refer to it again in the same
  line.
• Some textbooks use as ..
• Assignment  (?) assigns an expression to a
  temporary relation variable.

SQL Select Country, count() as NumCust from
Customers Group by country
15
Summary
16
Exercises

• Create the R. A., and name the operation(s)
• List LastName of Students with GPA below 2.0
• List CompanyName of Clients in 64804 ZIP code
  whose LineOfBusiness is Retail
• List Company of Clients alongside their
  SalesReps LName (assume SalesRep field of
  Client points to the SSN field of SalesRep)
• List SSN of Faculty advising Student number
  111-22-3333
• List LName and FName of every Student who is a
  Senior and a CIS major but does not have an
  Advisor (field is null or unmatched no ref.
  integ.)

17
Links

• http//www.csam.iit.edu/cs561/algebra/glossary.ht
  ml
• http//www.cs.rochester.edu/users/faculty/nelson/c
  ourses/csc_173/relations/algebra.html
• http//www.cs.sfu.ca/CC/354/zaiane/material/notes/
  Chapter3/node7.html
• http//www.tc.umn.edu/hause011/code/SQLexample.tx
  t ? examples of where you really need RA
• http//www.cs.ualberta.ca/mn/291/Notes/04ra.pdf
• http//www-ufrima.imag.fr/PLACARD/RICM2/BD/relatio
  nalModel/relationalModel_11.html