Session 3 Welcome To session 3 the sixth

  • Slides: 15
Download presentation
Session 3 Welcome: To session 3 -the sixth learning sequence “ Relational algebra “

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 2

Relational Algebra u. More Relational Algebra operators u u Generalized Projection Group Functions u.

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

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

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

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

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

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

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),

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

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

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

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

Relational Algebra Summary: In this learning sequence, we discussed another basic operators of the topic relational algebra. 14

END 15

END 15