Relational Algebra

Relational Algebra

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

Union • Set1={A, B, C} • Set2={C, D, E} • Union: Members in Set 1 or in Set 2 • Set1 U Set 2 = {A, B, C, D, E} • Or

Intersect • Members in Set 1 and in Set 2 • Set1 ∩ Set2={C} • And

Difference • Set1 – Set2: Members in Set1 but not in set2 = {A,B} • Set2 – Set1:Members in Set2 but not in set1 = {D, E} • Set1-Set2 ≠ Set2 – Set1

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.

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

Use Union and Difference to Simulate Intersect • Set1 ∩ Set2 = Set1 – (Set1 – Set2)

Venn Diagram Set 1: Major = ‘Business’ Set 2: Sex = ‘F’ Set 3: GPA > 3.0

Files as Sets • Business students’ file: BusSt • Science student’s file: SciSt • BusSt U SciSt: • BusSt ∩ SciSt • BusSt – SciSt • Spring 04 Student file: S04St • Fall 04 Student file: F04St • S04St – F04St • F04St – S04St

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

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:

Selection • Selection operation works on a single relation and defines a relation that contains records that satisfy the criteria. • σcriteria ( Relation) • σMajor = ‘Bus’AND GPA > 3.0(Student)

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. • πa1, a2, … (Relation) • πsid, sname (Student)

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 • πsid, sname (Student) • πsname, sex (Student) • πsname, sex (σMajor = ‘Bus’(Student)) • σMajor = ‘Bus’ (πsname, sex (Student))

Duplications due to Projection • WorkLog file: • EID PjID Hours • E1 P2 5 • E1 P1 4 • E2 P2 6 • E2 P1 8 • E3 P1 4 • πeid (WorkLog) • Relation contrains: no duplication • Eliminating duplicates may cause problems: • πHours (σ PjID = ‘P1 (WorkLog)) • In practice, users determine whether to eliminate duplicates: • SELECT DISTINCT EID FROM WorkLog; • SELECT HOURS FROM WorkLog WHERE PjID = ‘P1’;

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.

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 = • πAll except the duplicated attributes (σFaculty.FID = Student.FID(Faculty X Student)) • Note: Use RelationName.FieldName to make a field name unique.

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

Relational Algebra