Relational%20Algebra

1
Relational Algebra
2
Relational Algebra

• Set operations Union, intersection, difference,
  Cartesian product
• Relational operations Selection, projection,
  join, division

3
Union

• Set1A, B, C
• Set2C, D, E
• Union Members in Set 1 or in Set 2
• Set1 U Set 2 A, B, C, D, E
• Or

4
Intersect

• Members in Set 1 and in Set 2
• Set1 n Set2C
• And

5
Difference

• Set1 Set2 Members in Set1 but not in set2
  A,B
• Set2 Set1Members in Set2 but not in set1 D,
  E
• Set1-Set2 ? Set2 Set1

6
Union Compatibility

• Two relations that have the same number of
  attributes and same type of attributes.
• Union, Intersect and difference operators require
  the two relations to be union compatible.

7

• File 1
• SID 9 characters
• Sname 25 characters
• File 2
• SSN 9 characters
• Ename 25 characters
• File 3
• Ename 25 characters
• EID 9 characters

8
Use Union and Difference to Simulate Intersect

• Set1 n Set2 Set1 (Set1 Set2)

9
Venn Diagram
Set 1 Major Business Set 2 Sex F Set
3 GPA gt 3.0
10
Files as Sets

• Business students file BusSt
• Science students file SciSt
• BusSt U SciSt
• BusSt n SciSt
• BusSt SciSt
• Spring 04 Student file S04St
• Fall 04 Student file F04St
• S04St F04St
• F04St S04St

11
Product

• Set1 a, b, c
• Set2 X, Y, Z
• Set1 X Set2 aX, aY aZ, bX, bY, bZ, cX, cY, cZ

12

• Faculty File
• FID Fname
• F1 Chao
• F2 Smith
• Student File
• SID Sname FID
• S1 Peter F1
• S2 Paul F2
• S3 Smith F1
• Faculty X Student

13
Selection

• Selection operation works on a single relation
  and defines a relation that contains records that
  satisfy the criteria.
• s criteria ( Relation)
• s Major Bus AND GPA gt 3.0 (Student)

14
Projection

• Projection operation works on a single relation
  and defines a vertical subset of the relation,
  extracting the values of specified attributes and
  eliminating duplicates.
• p a1, a2, (Relation)
• p sid, sname (Student)

15

• Student file
• SID, Sname Sex Major
• S1 Peter M Bus
• S2 Paul M Art
• S3 Mary F Bus
• S4 Nancy F Sci
• S5 Peter M Art
• p sid, sname (Student)
• p sname, sex (Student)
• p sname, sex (s Major Bus (Student))
• s Major Bus (p sname, sex (Student))

16
Duplications due to Projection

• WorkLog file
• EID PjID Hours
• E1 P2 5
• E1 P1 4
• E2 P2 6
• E2 P1 8
• E3 P1 4
• p eid (WorkLog)
• Relation contrains no duplication
• Eliminating duplicates may cause problems
• p Hours (s PjID P1 (WorkLog))
• In practice, users determine whether to eliminate
  duplicates
• SELECT DISTINCT EID FROM WorkLog
• SELECT HOURS FROM WorkLog WHERE PjID P1

17
Natural Join

• The two relations must have common attributes.
• Combines two relations to form a new relation
  where records of the two relations are combined
  if the common attributes have the same value.
  One occurrence of each common attribute is
  eliminated.

18
Faculty File FID Fname F1 Chao F2 Smith Student
File SID Sname FID S1 Peter F1 S2 Paul F2 S3
Smith F1 Faculty Join Student p All except
the duplicated attributes (s Faculty.FID
Student.FID (Faculty X Student)) Note Use
RelationName.FieldName to make a field name
unique.
19
Examples

• University database
• Student SID, Sname, Sex, Major, GPA, FID
• Account SID, Balance
• Faculty FID, Fname
• Course CID, Cname, Credits
• StudentCourse SID, CID