L10 - Relational Algebra 4

L10 - Relational Algebra 4 - Remember that we are talking...

This preview shows pages 1–7. Sign up to view the full content.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Remember that we are talking about Remember that we are talking about the Relational Algebra the Relational Algebra with some operators added with some operators added Procedural language Six basic operators select: project: union: set difference: Cartesian product: x rename: and four additional operators Set intersection Natural join Division Assignment Extended relational algebra operators Generalized Projection Aggregate Functions Outer Join Null Values and issues that must be handled Modification of the database Deletion Insertion Updating Generalized Projection Generalized Projection Extends the projection operation by allowing arithmetic functions to be used in the projection list. F 1 , F 2 , F n (E) E is any relational-algebra expression Each of F 1 , F 2 , , F n are are arithmetic expressions involving constants and attributes in the schema of E . Example: Given relation credit_info(customer_name, limit, credit_balance), find how much more each person can spend: customer_name, limit credit_balance (credit_info) Aggregate Functions Aggregate Functions An aggregation function takes a collection of values and returns a single value as a result. avg : average value min : minimum value max : maximum value sum : sum of values count : number of values An aggregate operation in relational algebra is given by G 1 , G 2 , G n F 1 (A 1 ), F 2 (A 2 ), F m (A m ) (E) where E is any relational-algebra expression G 1 , G 2 , G n is a list of attributes on which to group (can be empty) Each F i is an aggregate function Each A i is an attribute name Aggregate Function Examples Aggregate Function Examples Lets go back to a University scenario thats similar to one weve seen before: students (name, major, dorm, gpa) faculty (name, department, salary, year_hired) chair (name, department) teaches (name, course) enrolls (name, course) List the number of students taking ES250 select count(*) from enrolls where course = ES250; students (name, major, dorm, gpa) faculty (name, department, salary, year_hired) chair (name, department) teaches (name, course) enrolls (name, course) More Aggregate Function More Aggregate Function Examples Examples List the average gpa of students taking ES250 select avg(s.gpa) from students s, enrolls e where s.name = e.name and e.course = ES250; Suppose that we only wanted to know the average gpa of ES250 students majoring in CpE or SwE select avg(s.gpa) from students s, enrolls e where s.name = e.name and e.course = ES250 and major in (CpE, SwE); More Aggregate Function More Aggregate Function Examples students (name, major, dorm, gpa) faculty (name, department, salary, year_hired)...
View Full Document

This note was uploaded on 04/08/2008 for the course EE 468 taught by Professor Conry during the Spring '08 term at Clarkson University .

Page1 / 25

L10 - Relational Algebra 4 - Remember that we are talking...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online