1 / 19

Understanding Relational Algebra: Set Operations and Relational Operations Explained

This resource delves into the fundamentals of relational algebra, focusing on key set operations such as union, intersection, difference, and Cartesian product. Learn how to perform relational operations like selection, projection, joins, and division. With practical examples including Venn diagrams, union compatibility, and the simulation of intersection using set difference, this guide emphasizes real-world applications through student and faculty data management. Master the essential techniques for managing relational databases effectively.

alfreda-reynolds
Télécharger la présentation

Understanding Relational Algebra: Set Operations and Relational Operations Explained

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Algebra

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

  3. 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

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

  5. 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

  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 ∩ Set2 = Set1 – (Set1 – Set2)

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

  10. 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

  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. • σcriteria ( Relation) • σMajor = ‘Bus’AND GPA > 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. • πa1, a2, … (Relation) • π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 • πsid, sname (Student) • πsname, sex (Student) • πsname, sex (σMajor = ‘Bus’(Student)) • σMajor = ‘Bus’ (π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 • π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’;

  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 = • πAll except the duplicated attributes (σ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

More Related