Relational Algebra

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: –

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

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

12. Set Operations

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

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

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

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

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

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

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

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

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

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

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

24. Match Making V W V W =

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

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

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

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

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

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

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

33. 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?

34. 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’}