Transcript S6
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 More Relational Algebra operators Generalized Projection Group Functions The operators take one or more relations as inputs and give a new relation as a result. Generalized Projection Extends the projection operation by allowing arithmetic functions to be used in the projection list. F1, F2, …, Fn(E) E is any relational-algebra expression Each of F1, F2, …, Fn are are arithmetic expressions involving constants and attributes in the schema of E. Generalized Projection Given relation credit-info(customer- name, limit, credit-balance). find 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: customername Ali Limitcreditbalance 1120 Ahmed 600 Rana 500 Kasim 2100 Group Functions 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. 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. 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.) 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 G1, G2, …, Gn g F1(A1), F2(A2),…, Fn(An) (E) E is any relational-algebra expression G1, G2 …, 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 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 Perryridge Brighton Brighton Redwood branch-name g balance A-102 A-201 A-217 A-215 A-222 sum(balance) 400 900 750 750 700 (account) branch-name Perryridge Brighton Redwood balance 1300 1500 700 Group Functions (Cont.) Result of aggregation does not have a name branch-name g sum(balance) as sum-balance (account) Can use rename operation to give it a name 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 END 15