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 DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full 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 relationalalgebra 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 relationalalgebra 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 .
 Spring '08
 Conry

Click to edit the document details