Overview of Relational Algebra Thursday, November 20, 2003

1
Overview of Relational AlgebraThursday, November
20, 2003
Consider a set of tables as shown below
2
Algebraic Operations

• ?curtis(SUBJECT) is a table as shown

• ?lecturer(SUBJECT) is a table as shown

3
Algebraic Operations

• ?lecturecurtis(SUBJECT) is as shown

• ?name Joe OR name Hector(STUDENT) is a table
  as shown

• Student - ? name Ling(student) is a table as
  shown

• ? name Ling(STUDENT ? REGISTERED) is a table as
  shown

4
Algebraic Operations

• ? name Joe (STUDENT X REGISTERED) is a table as
  shown

5
An Other Example
Consider another set of tables fro some more
algebraic operatons.


6
Another Example








7
An Other Example

8

Union-Compatibility

• Two relations are said to be union compatible if
  (1) they have the same number of columns and (2)
  corresponding columns have the same domains.
• For example, we might have two tables each
  containing a column of Social Security Numbers,
  yet the column might be named "Student ID" in one
  table and Faculty ID in the other table. If, in
  other respects, the tables appeared similar, then
  to decide whether they were actually
  union-compatible, you would have to make a
  careful examination of the definitions of the
  domains of their respective attributes to verify
  that these domains are, in fact, the same.
• For example, the relation FACULTY is almost
  union-compatible with HONOR_STUDENT. After
  checking the definitions of the domains, you can
  see that the only real problem with FACULTY is
  that its Column 2, "Dept", would have to be moved
  to Column 4 in order to make union-compatibility
  possible.
• Thus, it is possible to define a new relation,
  FACULTY1(Fnum, Fname, Lname, Dept), which differs
  from FACULTY(Fnum, Dept, Fname, Lname) only in
  the order of its attributes. Then FACULTY1 and
  HONOR_STUDENT would be union-compatible.

9
Relational-Algebraic Operations

• Union the first step in forming the union of
  tables GRAD and HONOR_STUDENT is to put together
  all the rows that occur in either or both of
  these tables

• After the necessary operation of eliminating any
  and all rows that are duplicates of another row
  (here, the second Lynn Lee row), we have the
  final step in the union of GRAD and
  HONOR_STUDENT. It is a table that contains
  entries for all students who are either graduate
  students or honor students or both.

10
Union, Difference
 
The difference of tables GRAD and HONOR_STUDENT.
The difference table is what results when we
subtract HONOR_STUDENT from GRAD the set
consisting of those rows of GRAD that are left
after we remove from GRAD any row that occurs
also in HONOR_STUDENT.
11
Intersection, Product
 

• The intersection of tables GRAD and HONOR_STUDENT
  is the set of only those rows that occur in both
  GRAD and HONOR_STUDENT. In words, it is the set
  of the graduate students who are also honor
  students.

 
12
Product
13
Equijoin of Two Tables

• We can perform this operation on table, GRAD
  cross ENROLLMENT, by selecting the rows where SSN
  and SID are matched. Thus discarding all other
  rows, we will obtain a relation that provides
  information about the classes in which the grads
  are enrolled. This relation, let us call it
  "GRAD_COURSES" is Equijoin between the tables of
  GRAD and ENROLLMENT.

 
14
Natural Join (Inner Join) of Two Tables

• Clearly, GRAD_COURSES still has some redundant
  information in it viz., the two attributes with
  matching contents, SSN and SID. To polish the
  above result, we can perform a projection
  operation on GRAD_COURSES, selecting all columns
  except the SID column. We shall call the result
  GRAD_COURSES1

15
Left Outer Join
 

• What a left outer join does not show directly is
  whether there are rows in the left-hand table
  that fail to match any rows in the right-hand
  table. For example, suppose we have a table
  GRAD1, a slightly modified version of GRAD
  containing a row for an imaginary fourth graduate
  student, Mae East, a drama major who is not
  enrolled in any course during the current
  semester.

16
Left Outer Join Cont

• What a left outer join does not show directly is
  whether there are rows in the left-hand table
  that fail to match any rows in the right-hand
  table. For example, suppose we have a table
  GRAD1, a slightly modified version of GRAD
  containing a row for an imaginary fourth graduate
  student, Mae East, a drama major who is not
  enrolled in any course during the current
  semester.

17
Right Outer-Join

• The right outer join of tables GRAD and
  ENROLLMENT is the same table as that shown
  earlier with the title GRAD_COURSES. This is
  because in our example every row in the left-hand
  table, GRAD, happens to be matched by at least
  one row in the right-hand table, ENROLLMENT, as
  is shown in GRAD_COURSES, so that there are no
  rows with empty cells in the last three columns.
• Slightly different from the right outer join of
  GRAD and ENROLLMENT (viz., GRAD_COURSES) is the
  right outer join of GRAD1 and ENROLLMENT. This
  latter right outer join has a row for Mae East,
  with blanks in the last three columns. However,
  the right outer join of GRAD1 and ENROLLMENT
  fails to show whether there are any courses
  offered in Grandiose State University in addition
  to those shown for example, this right outer
  join lacks even a hint of the existence of course
  PHIL123.

18
Thank You For Attending.