- Slides: 15
Session 3 Welcome: To session 3 -the sixth learning sequence “ Relational algebra “ Recap : In the previous learning sequences, we discussed the eight operators of relational algebra. Present learning: We shall explore the following topic: - Generalized Projection & Group Functions 1
Relational Algebra 2
Relational Algebra u. More Relational Algebra operators u u Generalized Projection Group Functions u. The operators take one or more relations as inputs and give a new relation as a result.
Generalized Projection u. Extends the projection operation by allowing arithmetic functions to be used in the projection list. F 1, F 2, …, Fn(E) u. E is any relational-algebra expression u. Each of F 1, F 2, …, Fn are arithmetic expressions involving constants and attributes in the schema of E.
Generalized Projection u. Given relation credit-info(customername, limit, credit-balance). ufind how much more each person can spend: customer-name, limit – credit-balance, limit*2 (credit-info)
Generalized Projection Credit-info customername Limit creditbalance Ali 1500 380 Ahmed 2000 1400 Rana 1000 500 Kasim 3500 1400
Generalized Projection The result relation is: customer- Limit-credit name -balance Ali 1120 Ahmed 600 Rana 500 Kasim 2100
Group Functions w A type of request that cannot be expressed in the basic relational algebra is to specify mathematical group functions on collections of values from the database. w Examples of such functions include retrieving the average or total salary of all employees or the total number of employee tuples. These functions are used in simple statistical queries that summarize information from the database tuples. w Common functions applied to collections of numeric values include SUM, AVERAGE, MAXIMUM, and MINIMUM. The COUNT function is used for counting tuples or values.
Group Functions (Cont. ) u. Group functions take a collection of values and return a single value as a result. avg: average value min: minimum value max: maximum value sum: sum of values count: number of values
Group Functions G 1, G 2, …, Gn w w g F 1(A 1), F 2(A 2), …, Fn(An) ( E) E is any relational-algebra expression G 1, G 2 …, Gn is a list of attributes on which to group (can be empty) Each Fi is an group function Each Ai is an attribute name
Group Functions – Example u Relation r: g sum(c) (r) A B C 7 sum-C 27 7 3 10
Group Functions – Example account branch-name account-number Perryridge Brighton Redwood branch-name balance A-102 A-201 A-217 A-215 A-222 400 900 750 700 g sum(balance) (account) branch-name Perryridge Brighton Redwood balance 1300 1500 700
Group Functions (Cont. ) u. Result of aggregation does not have a name branch-name g sum(balance) as sum-balance (account) w Can use rename operation to give it a name w For convenience, we permit renaming as part of group operation Branch-name Sum-balance Perryridge 1300 Brighton 1500 Redwood 700
Relational Algebra Summary: In this learning sequence, we discussed another basic operators of the topic relational algebra. 14