Relational Algebra (ii)

1
Relational Algebra (ii)
2
Aggregate Functions

• Max, Min, Sum, Count, Avg
• Ex. Student SID,Sname, GPA, Sex, Major
• F count(SID) (Student)
• F avg(GPA) (Student)
• F count(SID), avg(GPA) (Student)

3
Aggregates by Group

• Sex F count(SID) (Student)
• Major F count(SID) (Student)
• Major, Sex F count(SID) (Student)
• GPA F count(SID) (Student) ?
• Grouping field
• EmpID, Ename, DOB, Salary, Gender, Race

4

• Student SID,Sname, GPA, Sex, Major
• Enroll SID, CID
• Course CID, Cname, Credits
• Queries
• Number of students in each course
• CID, CName, NumbeOfStudents
• Total credits for each student
• SID, Sname, TotalCredits

5
Other Joins

• Theta join Apply any specified condition on the
  product of two relations.
• Example
• BoyStudent BID, Bname, GPA
• GirlStudent GID, Gname, GPA
• Boys want to date with girls who have better GPA.

6
Outer Join

• Records in a relation that do not have matching
  values are included in the result relation.
  Missing values are set to null.

7
Outer Join Exmple

• Product Table
• PID Pname
• P1 TV
• P2 VCR
• P3 Computer
• P4 Tape
• P5 DVD
• TotalSales
• PID TotalSales
• P1 50
• P3 60
• P5 40
• Product Join TotalSales
• Product OuterJoin Totalsales

8
Branch BID City B1 SF B2 SM B3 SJ Full
Outer Join BID City PID B1 SF P3 B2 SM Null B
3 SJ P2 Null LA P1 Right Outer
Join BID City PID B1 SF P3 B3 SJ P2 Null LA P
1
Property PID City P1 LA P2 SJ P3 SF Left
Outer Join BID City PID B1 SF P3 B2 SM Null B
3 SJ P2
9
How to Implement Join

• Outer Inner loop
• Sort Merge
• Index on the join attributes
• Faculty Join Student

10
Division

• Assume relation R is defined over the attribute
  set A and relation S is defined over the
  attribute set B where B is a subset of A. Let C
  A B, then the division operation defines a
  relation over the attribute C that consists of
  the set of records from R that match the
  combination of every records in S.

11
Division Example

• Relation R
• A B
• a x
• a y
• a z
• b x
• c z

Relation S B x z
R S A a
12
Applicant Position Skill ApplicantSkill Positi
onSkill AID SID PID SID A1 S1 P1 S1 A1 S2 P1
S2 A2 S2 P2 S1 A3 S1 P3 S2 A4 S1 A4 S2 A4
S3 Query Find applicants that meet the skills
required by position P1.