Extended RelationalAlgebraOperations n Generalized Projection n Aggregate Functions

Extended Relational-Algebra-Operations n Generalized Projection n Aggregate Functions n Outer Join Database System Concepts 3. 1 ©Silberschatz, Korth and Sudarshan

Generalized Projection n Extends the projection operation by allowing arithmetic functions to be used in the projection list. F 1, F 2, …, Fn(E) n E is any relational-algebra expression n Each of F 1, F 2, …, Fn are arithmetic expressions involving constants and attributes in the schema of E. n Given relation credit-info(customer-name, limit, credit-balance), find how much more each person can spend: customer-name, limit – credit-balance (credit-info) Database System Concepts 3. 2 ©Silberschatz, Korth and Sudarshan

Aggregate Functions and Operations n 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 n Aggregate operation in relational algebra G 1, G 2, …, Gn g F 1( A 1), F 2( A 2), …, Fn( An) (E) H E is any relational-algebra expression H G 1, G 2 …, Gn is a list of attributes on which to group (can be empty) H Each Fi is an aggregate function H Each Ai is an attribute name Database System Concepts 3. 3 ©Silberschatz, Korth and Sudarshan

Example 1 n Relation r: g sum(c) (r) Database System Concepts A B C 7 7 3 10 sum-C 27 3. 4 ©Silberschatz, Korth and Sudarshan

Example 2 n Relation account grouped by branch-name: branch-name account-number Perryridge Brighton Redwood branch-name A-102 A-201 A-217 A-215 A-222 400 900 750 700 g sum(balance) (account) branch-name Perryridge Brighton Redwood Database System Concepts balance 3. 5 balance 1300 1500 700 ©Silberschatz, Korth and Sudarshan

Aggregate Functions (Cont. ) n Result of aggregation does not have a name H Can use rename operation to give it a name H For convenience, we permit renaming as part of aggregate operation branch-name Database System Concepts g sum(balance) as sum-balance (account) 3. 6 ©Silberschatz, Korth and Sudarshan

Outer Join n An extension of the join operation that avoids loss of information. n Computes the join and then adds tuples from one relation that does not match tuples in the other relation to the result of the join. n Uses null values H null signifies that the value is unknown or does not exist Database System Concepts 3. 7 ©Silberschatz, Korth and Sudarshan

Example n Relation loan-number branch-name L-170 L-230 L-260 Downtown Redwood Perryridge amount 3000 4000 1700 n Relation borrower customer-name loan-number Jones Smith Hayes Database System Concepts L-170 L-230 L-155 3. 8 ©Silberschatz, Korth and Sudarshan

Example (Cont. ) n Inner Join loan Borrower loan-number branch-name L-170 L-230 Downtown Redwood amount customer-name 3000 4000 Jones Smith amount customer-name n Left Outer Join loan borrower loan-number L-170 L-230 L-260 Database System Concepts branch-name Downtown Redwood Perryridge 3000 4000 1700 3. 9 Jones Smith null ©Silberschatz, Korth and Sudarshan

Example (Cont. ) n Right Outer Join loan borrower loan-number branch-name L-170 L-230 L-155 Downtown Redwood null amount 3000 4000 null customer-name Jones Smith Hayes n Full Outer Join loan borrower loan-number branch-name L-170 L-230 L-260 L-155 Downtown Redwood Perryridge null Database System Concepts amount 3000 4000 1700 null 3. 10 customer-name Jones Smith null Hayes ©Silberschatz, Korth and Sudarshan

Null Values n It is possible for tuples to have a null value, denoted by null, for some of their attributes n null signifies an unknown value or that a value does not exist. n The result of any arithmetic expression involving null is null. n Aggregate functions simply ignore null values H Is an arbitrary decision. Could have returned null as result instead. H We follow the semantics of SQL in its handling of null values n For duplicate elimination and grouping, null is treated like any other value, and two nulls are assumed to be the same H Alternative: assume each null is different from each other H Both are arbitrary decisions, so we simply follow SQL Database System Concepts 3. 11 ©Silberschatz, Korth and Sudarshan

Null Values n Comparisons with null values return the special truth value unknown H If false was used instead of unknown, then would not be equivalent to not (A < 5) A >= 5 n Three-valued logic using the truth value unknown: H OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown H AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown H NOT: (not unknown) = unknown H In SQL “P is unknown” evaluates to true if predicate P evaluates to unknown n Result of select predicate is treated as false if it evaluates to unknown Database System Concepts 3. 12 ©Silberschatz, Korth and Sudarshan

Modification of the Database n The content of the database may be modified using the following operations: H Deletion H Insertion H Updating n All these operations are expressed using the assignment operator. Database System Concepts 3. 13 ©Silberschatz, Korth and Sudarshan

Deletion n A delete request is expressed similarly to a query, except instead of displaying tuples to the user, the selected tuples are removed from the database. n Can delete only whole tuples; cannot delete values on only particular attributes n A deletion is expressed in relational algebra by: r r–E where r is a relation and E is a relational algebra query. Database System Concepts 3. 14 ©Silberschatz, Korth and Sudarshan

Deletion Examples n Delete all account records in the Perryridge branch. account – branch-name = “Perryridge” (account) n Delete all loan records with amount in the range of 0 to 50 loan – amount 0 and amount 50 (loan) n Delete all accounts at branches located in Needham. r 1 branch-city = “Needham” (account branch) r 2 branch-name, account-number, balance (r 1) r 3 customer-name, account-number (r 2 depositor) account – r 2 depositor – r 3 Database System Concepts 3. 15 ©Silberschatz, Korth and Sudarshan

Insertion n To insert data into a relation, we either: H specify a tuple to be inserted H write a query whose result is a set of tuples to be inserted n in relational algebra, an insertion is expressed by: r r E where r is a relation and E is a relational algebra expression. n The insertion of a single tuple is expressed by letting E be a constant relation containing one tuple. Database System Concepts 3. 16 ©Silberschatz, Korth and Sudarshan

Insertion Examples n Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch. account {(“Perryridge”, A-973, 1200)} depositor {(“Smith”, A-973)} n Provide as a gift for all loan customers in the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account. r 1 ( branch-name = “Perryridge” (borrower loan)) account branch-name, account-number, 200 (r 1) depositor customer-name, loan-number, (r 1) Database System Concepts 3. 17 ©Silberschatz, Korth and Sudarshan

Updating n A mechanism to change a value in a tuple without charging all values in the tuple n Use the generalized projection operator to do this task r F 1, F 2, …, FI, (r) n Each F, is either the ith attribute of r, if the ith attribute is not updated, or, if the attribute is to be updated n Fi is an expression, involving only constants and the attributes of r, which gives the new value for the attribute Database System Concepts 3. 18 ©Silberschatz, Korth and Sudarshan

Update Examples n Make interest payments by increasing all balances by 5 percent. account AN, BAL * 1. 05 (account) where AN, BN and BAL stand for account-number, branch-name and balance, respectively. n Pay all accounts with balances over $10, 000 6 percent interest and pay all others 5 percent account Database System Concepts AN, BAL * 1. 06 ( BAL 10000 (account)) AN, BAL * 1. 05 ( BAL 10000 (account)) 3. 19 ©Silberschatz, Korth and Sudarshan

Views n Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view. n A view is defined using the create view statement which has the form create view v as <query expression> where <query expression> is any legal relational algebra query expression. The view name is represented by v. n Once a view is defined, the view name can be used to refer to the virtual relation that the view generates. Database System Concepts 3. 20 ©Silberschatz, Korth and Sudarshan

Examples n Consider the view (named all-customer) consisting of branches and their customers. create view all-customer as branch-name, customer-name (depositor account) branch-name, customer-name (borrower loan) n We can find all customers of the Perryridge branch by writing: branch-name ( branch-name = “Perryridge” (all-customer)) Database System Concepts 3. 21 ©Silberschatz, Korth and Sudarshan

Updates Through View n Database modifications expressed as views must be translated to modifications of the actual relations in the database. n Consider the person who needs to see all loan data in the loan relation except amount. The view given to the person, branchloan, is defined as: create view branch-loan as branch-name, loan-number (loan) n Since we allow a view name to appear wherever a relation name is allowed, the person may write: branch-loan {(“Perryridge”, L-37)} Database System Concepts 3. 22 ©Silberschatz, Korth and Sudarshan

Updates Through Views (Cont. ) n The previous insertion must be represented by an insertion into the actual relation loan from which the view branch-loan is constructed. n An insertion into loan requires a value for amount. The insertion can be dealt with by either. H rejecting the insertion and returning an error message to the user. H inserting a tuple (“L-37”, “Perryridge”, null) into the loan relation n Some updates through views are impossible to translate into database relation updates H create view v as branch-name = “Perryridge” (account)) v v (L-99, Downtown, 23) n Others cannot be translated uniquely H all-customer (Perryridge, John) 4 Have to choose loan or account, and create a new loan/account number! Database System Concepts 3. 23 ©Silberschatz, Korth and Sudarshan

Views Defined Using Other Views n One view may be used in the expression defining another view n A view relation v 1 is said to depend directly on a view relation v 2 if v 2 is used in the expression defining v 1 n A view relation v 1 is said to depend on view relation v 2 if either v 1 depends directly to v 2 or there is a path of dependencies from v 1 to v 2 n A view relation v is said to be recursive if it depends on itself. Database System Concepts 3. 24 ©Silberschatz, Korth and Sudarshan