1 / 34

Understanding Relational Algebra: Key Concepts and Operations in Data Management

This content provides an in-depth look at relational algebra, a fundamental theoretical framework for database management, akin to SQL. Covering the transformation of entity-relationship diagrams (ERD) into relational schemas, it explores core concepts like one-to-one, one-to-many, and many-to-many relationships. The document explains essential operations such as selection, projection, union, intersection, and join operations, highlighting their application in data retrieval. Ideal for students and professionals, this guide enhances understanding of relational data models and algebraic operations.

cyndi
Télécharger la présentation

Understanding Relational Algebra: Key Concepts and Operations in Data Management

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 Chapter 4 CIS 458 Sungchul Hong

  2. Last Class • Relational data model. • Transform ERD to relational database schema. • One-to-one relationship • One-to-many relationship • Many-to-many relationship

  3. Today • Relational Algebra • Theoretical language • Similar to SQL • Data Retrieval concept

  4. Sets • A set is any well-defined list, collection, or class of objects. • Elements or members of the set. • A = {1, 3, 7, 10} • B= {x | x is even}

  5. Subsets • If every element in a set A is also a member of a set B, then A is called a subset of B. • AB (Proper)

  6. Union • The union of sets A and B is the set of all elements which belong to A or to B or to both. • AB • S={a,b,c,d} T={f,b,d,g}

  7. The Relational Algebra • The relational algebra is a theoretical language with operations that work on one or more relations to define another relation without changing the original relation(s). • Operands and results are relations. • name, address (Student) • The output from one operation can become the input to another operation. • name=“John”(name, address (Student))

  8. Five Basic Operations • Selection:  • Projection:  • Cartesian product: X • Union:  • Intersection:  • Set difference: –

  9. Selection • salary>10000(Staff) • AND, OR, NOT • , , ~ salary>10000(Staff)

  10. Projection • staffNo,fName, lName, salary(Staff)

  11. Set Operations

  12. Union • RS • city(Branch)  city(PropertyForRent)

  13. city(Branch)  city(PropertyForRent)

  14. Result table • city(Branch)  city(PropertyForRent)

  15. Set Difference • R – S • city (Branch) - city (PropertyForRent)

  16. city (Branch) - city (PropertyForRent)

  17. Result • city (Branch) - city (PropertyForRent)

  18. Intersection • city (Branch)  city (PropertyForRent) • R S = R – (R –S)

  19. city (Branch)  city (PropertyForRent)

  20. Result • city (Branch)  city (PropertyForRent)

  21. Division • R  S • The Division operation defines a relation over the attributes C that consists of the set of tuples from R that match the combination of every tuple in S.

  22. List the elements of A in V that satisfies all the condition in B (W).

  23. Match Making V W V W =

  24. Cartesian Product • clinetNo,fName,lName(Client) X (clientNo,propertyNo,comment(Viewing)) • clinet.clinetNo=Viewing.clientNo (clinetNo,fName,lName(Client) X (clientNo,propertyNo,comment(Viewing)))

  25. clinetNo,fName,lName(Client) X (clientNo,propertyNo,comment(Viewing))

  26. clinet.clinetNo=Viewing.clientNo (clinetNo,fName,lName(Client) X (clientNo,propertyNo,comment(Viewing)))

  27. Application • Join • Selection () and Cartesian product (X) • One of the major database data retrieval technique.

  28. Join Operations • Theta join • Equijoin • Natural join • Outer join • Semijoin

  29. Theta Join • R ⊳⊲F S • The Theta join operation defines a relation that contains tuples satisfying the predicate F from the Cartesian product of R and S. The predicate F is of the form R.aj S.bi where  may be one of the comparison operators ( <, , >, , =, ) • E.g.) R ⊳⊲R.salary  S.salary S

  30. Equijoin • clinetNo,fName,lName(Client)⊳⊲clinet.clinetNo=Viewing.clinetNoclientNo,propertyNo,comment(Viewing) • A special case of theta join.

  31. Natural Join • R ⊳⊲ S • The Natural join is an Equijoin of the two relations R and S over all common attributes x. One occurrence of each common attribute is eliminated from the result. • clinetNo,fName,lName(Client) ⊳⊲ clientNo,propertyNo,comment(Viewing) • No common attribute?

  32. Other Topics • Outer Join • Aggregation (COUNT, SUM AVG, MIN, MAX), Group • Relational Calculus • Tuple Relational Calculus • {S | Staff(S) S.salary > 10000} • Domain Relational Calculus • List the staff who manage properties for rent in Glasgow • {sN, fN, lN, posn, sex, DOB, sal, bN|(sN1, city)(Staff(sN, fN, lN, posn, sex, DOB, sal, bN) PropertyForRent(pN, st, cty, pc, typ, rms, rnt, oN, sN1, bN1) (sN = sN1) cty=‘Glasgow’}

More Related