DB Lecture 3 PDF

Lecture 3: Additional, Extended Relational Algebra Operators and Modification Operations CSX3006 DATABASE SYSTEMS CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 1 Outline Additional Operators Extended Operators Modification Operations CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUD...

Lecture 3: Additional, Extended Relational Algebra Operators and Modification Operations CSX3006 DATABASE SYSTEMS CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 1 Outline Additional Operators Extended Operators Modification Operations CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 2 Relational Algebra Operators - 1 Fundamental Operators ◦ select:  ◦ project:  ◦ union:  ◦ set difference: – ◦ Cartesian product: x ◦ rename:  CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 3 Relational Algebra Operators - 2 Additional Operators ◦ set intersection:  ◦ natural join: ◦ division:  ◦ assignment:  Extended Operators ◦ Generalized Project ◦ Aggregate Functions ◦ Outer Joins Modification Operations ◦ Deletion, insertion and updating of tuples CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 4 Additional Operations Goal: simplify common queries Set intersection Natural join ◦ More general form of join known as theta join and equijoin Division Assignment CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 5 Set-Intersection Operation - 1 Notation: r  s ◦ Binary Operator ◦ The usual Set Intersect Operation ◦ Produces a new relation containing tuples that are present in both r and s ◦ r  s = { t | t  r and t  s } Assume: ◦ r, s have the same arity; (same number of attributes) ◦ Domains of the attributes of r and s are compatible Note: r  s = r – (r – s) CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 6  Set-Intersection Operation - 2 customer_name (depositor)  customer_name (borrower) ◦ What does the above relation algebra expression find? depositor relation borrower relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 7  Set-Intersection Operation - 3 customer_name (depositor)  customer_name (borrower) ◦ What does the above relation algebra expression find? depositor relation borrower relation customer_name Hayes Jones Smith CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 8 Set-Intersection Operation – Example Relation r, s: A B A B  1  2  2  3  1 s r rs CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 9 Set-Intersection Operation – Example Relation r, s: A B A B  1  2  2  3  1 s r rs A B  2 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 10 Natural-Join Operation - 1 Notation: r s Combine a Cartesian product and a selection Example 1: find the names of all customers who have a loan at the bank, along with the loan number, branch and the loan amount. borrower relation loan relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 11 Natural-Join Operation - 2 ◦ Find the names of all customers who have a loan at the bank, along with the loan number, branch and the loan amount. borrower relation loan relation ◦ borrower.loan_number = loan.loan_number (borrower x loan) ◦ Result relation’s schema: ◦ (borrower.customer_name, borrower.loan_number, loan.loan_number, loan.branch_name, loan.amount) CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 12  ◦ Find the names of all customers who have a loan at the bank, along with the loan number, branch and the loan amount. ◦ borrower.loan_number = loan.loan_number (borrower x loan) borrower.customer_name borrower.loan_number loan.loan_number loan.branch_name loan.amount Adams L-16 L-16 Perryridge 1300 Curry L-93 L-93 Mianus 500 Hayes L-15 L-15 Perryridge 1500 Jackson L-14 L-14 Downtown 1500 Jones L-17 L-17 Downtown 1000 Smith L-11 L-11 Round Hill 900 Smith L-23 L-23 Redwood 2000 Williams L-17 L-17 Downtown 1000 ◦ borrower loan customer_name loan_number branch_name amount Adams L-16 Perryridge 1300 Curry L-93 Mianus 500 Hayes L-15 Perryridge 1500 Jackson L-14 Downtown 1500 Jones L-17 Downtown 1000 Smith L-11 Round Hill 900 Smith L-23 Redwood 2000 Williams L-17 Downtown 1000 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 13 How to Generate Result of ?-1 Let r and s be relations on schemas R and S, respectively. Consider each pair of tuples tr from r and ts from s. ◦ If tr and ts have the same value on the common attributes in R and S, add a tuple t to the result, where ◦ t has the same value as tr on r ◦ t has the same value as ts on s CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 14 How to Generate Result of ?-2 Let r and s be relations on schemas R and S, respectively. Consider each pair of tuples tr from r and ts from s. ◦ If tr and ts have the same value on the common attributes in R and S, add a tuple t to the result, where ◦ t has the same value as tr on r ◦ t has the same value as ts on s borrower relation loan relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 15 Natural-Join Operation: Example 2 Relations r, s: A B C D B D E  1  a 1 a   2  a 3 a   4  b 1 a   1  a 2 b   2  b 3 b  r s r s CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 16 Natural-Join Operation: Example 2 Relations r, s: A B C D B D E  1  a 1 a   2  a 3 a   4  b 1 a   1  a 2 b   2  b 3 b  r s r s A B C D E  1  a   1  a   1  a   1  a   2  b  CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 17 Natural-Join Operation: Example 3 Find the name of all customers who have an account with the bank, along with his/her account number and the balance of the account. depositor relation account relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 18 Natural-Join Operation: Example 3 Find the name of all customers who have an account with the bank, along with his/her account number and the balance of the account. depositor relation account relation Result of Step 1 customer_name account_number branch_name balance Step 1: depositor account Johnson A-101 Downtown 500 Hayes A-102 Perryridge 400 Johnson A-201 Brighton 900 Smith A-215 Mianus 700 Jones A-217 Brighton 750 Lindsay A-222 Redwood 700 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG Turner A-305 Round Hill 19350  Natural-Join Operation: Example 3 Find the name of all customers who have an account with the bank, along with his/her account number and the balance of the account. ◦ Step 2: customer_name, account_number, balance (depositor account) Result of Step 1 Result of Step 2 customer_name account_number Branch_name balance customer_name account_number balance Johnson A-101 Downtown 500 Johnson A-101 500 Hayes A-102 Perryridge 400 Hayes A-102 400 Johnson A-201 Brighton 900 Johnson A-201 900 Smith A-215 Mianus 700 Smith A-215 700 Jones A-217 Brighton 750 Jones A-217 750 Lindsay A-222 Redwood 700 Lindsay A-222 700 Turner A-305 Round Hill 350 Turner A-305 350 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 20 Natural-Join Operation: Example 4 Find the names of all branches with customers who have an account in the bank and who live in the city of Harrison customer relation account relation depositor relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 21 Natural-Join Operation: Example 4 Find the names of all branches with customers who have an account in the bank and who live in the city of Harrison customer relation account relation depositor relation Check retrieved fields to identify associated relations CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 22 Natural-Join Operation: Example 4 Find the names of all branches with customers who have an account in the bank and who live in the city of Harrison customer relation account relation depositor relation Join relations to obtain needed attributes (customer depositor account) CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 23  (customer depositor account) customer customer customer account_ branch_name balance _name _street _city number Hayes Main Harrison A-102 Perryridge 400 Johnson Alma Palo Alto A-101 Downtown 500 Johnson Alma Palo Alto A-201 Brighton 900 Jones Main Harrison A-217 Brighton 750 Lindsay Park Pittsfield A-222 Redwood 700 Smith North Rye A-215 Mianus 700 Turner Putnam Stamford A-305 Round Hill 350 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 24  Natural-Join Operation: Example 4 Find the names of all branches with customers who have an account in the bank and who live in the city of Harrison customer_ customer_ customer account_ branch_name balance name street _city number Hayes Main Harrison A-102 Perryridge 400 Johnson Alma Palo Alto A-101 Downtown 500 Johnson Alma Palo Alto A-201 Brighton 900 Jones Main Harrison A-217 Brighton 750 Lindsay Park Pittsfield A-222 Redwood 700 Smith North Rye A-215 Mianus 700 Turner Putnam Stamford A-305 Round Hill 350 Select only records that satisfied the condition. customer_city = “Harrison” (customer depositor account) CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 25  customer_city = “Harrison” (customer depositor account) customer_ customer_ customer account_ branch_name balance name street _city number Hayes Main Harrison A-102 Perryridge 400 Jones Main Harrison A-217 Brighton 750 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 26  Natural-Join Operation: Example 4 Find the names of all branches with customers who have an account in the bank and who live in the city of Harrison customer_ customer_ customer account_ branch_name balance name street _city number Hayes Main Harrison A-102 Perryridge 400 Jones Main Harrison A-217 Brighton 750 Project only needed attributes. branch_name (customer_city = “Harrison” (customer depositor account)) Branch_name Perryridge Brighton CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 27  Natural-Join Operation: Example 4 Find the names of all branches with customers who have an account in the bank and who live in the city of Harrison ◦ There are many ways to write the relational algebra expression to produce the same result. ◦ E.g., branch_name (customer_city = “Harrison” (customer depositor account)) branch_name (customer_city = “Harrison” ((customer depositor) account)) branch_name (customer_city = “Harrison” (customer (depositor account) )) Branch_name Perryridge Brighton CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 28 Natural-Join Operation: Example 5 Find all customers who have both a loan and an account at the bank depositor relation borrower relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 29 Natural-Join Operation: Example 5 Find all customers who have both a loan and an account at the bank customer_name (depositor borrower) or customer_name (depositor)  customer_name (borrower) customer_name Hayes Jones Smith CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 30 Natural-Join Operation: Example 6 Find all account numbers managed by any of branches in the city of Horseneck. branch relation account relation branch_name branch_city assets Brighton Brooklyn 7100000 Downtown Brooklyn 9000000 Mianus Horseneck 400000 Mprtj Tpwm Rye 3700000 Perryridge Horseneck 1700000 Pownal Bennington 300000 Redwood Palo Alto 2100000 Round Hill Horseneck 8000000 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 31  Natural-Join Operation: Example 6 Find all account numbers managed by any of branches in the city of Horseneck. account_number ( branch_city = “Horseneck” (branch) account ) branch relation account relation branch_name branch_city assets Brighton Brooklyn 7100000 account_number Downtown Brooklyn 9000000 A-102 Mianus Horseneck 400000 A-215 Mprtj Tpwm Rye 3700000 A-305 Perryridge Horseneck 1700000 Pownal Bennington 300000 Redwood Palo Alto 2100000 Round Hill Horseneck 8000000 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 32 Theta Join Operator Theta Join (Condition Join): More general form of join operation ◦ r1 p r2 is equivalent to  p(r1 x r2) where p is a formula in propositional calculus consisting of terms connected by :  (and),  (or),  (not) Each term is one of: op or where op is one of: =, , >, , 1000 loan ) borrower relation loan relation customer_ amount name Adams 1300 Hayes 1500 Jackson 1500 Smith 2000 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 39 Theta Join, Natural join and equijoin Theta Join (Join op is one of: =, , >, , , , 25 (member) age > 25  food = “Pizza” (member) Name Age Food Name Age Food Jenny 33 null Jenny 33 null Sara 34 null Donna null Pizza Sara 34 null CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 84 How Do Relational Operations Deal with Null Values? - 2 Natural Join: ◦ If at least one of the two tuples have a null value in a common attribute → the tuples do not match. CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 85  Natural Join Operation with Null Values - Example Name Age Food Food Day Jenny 33 null Pizza Monday Donna null Pizza Burger Tuesday Roy 21 Steak Salad Wednesday Sara 34 null Pasta Thursday member relation null Friday menu relation member menu Name Age Food Day Donna null Pizza Monday CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 86 How Do Relational Operations Deal with Null Values? - 3 Projection: ◦ If two tuple in the projection result are exactly the same and both have nulls in the same fields → They are treated as duplicates. CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 87 Project Operation with Null Values - Example Name Age Food Jenny 33 null Donna null Pizza Roy 21 Steak Sara 34 null member relation food (member) Food null Pizza Steak CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 88 How Do Relational Operations Deal with Null Values? - 2 Union, intersection, difference, Generalized projection: ◦ Same as projection Aggregate functions 𝑮𝟏 𝑮𝟐 ,…,𝑮𝒏 𝓖𝑭𝟏 𝑨𝟏 ,𝑭𝟐 𝑨𝟐 ,…𝑭𝒎 𝑨𝒎 (𝑬) ◦ Null in grouping attributes (Gi): ◦ If two tuples are the same on all Gi → they are in the same group (even if some of their attribute values are null.) ◦ Null in aggregated attribute (Aj) ◦ Delete null values at the outset, before applying aggregation. ◦ If the resultant multiset is empty, the aggregate result is null. CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 89 Aggregate functions Involving null Examples Name Age Food Jenny 33 null Donna null Pizza Roy 21 Steak Sara 34 null member relation g sum(age) (member) sum(age) 88 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 90 Aggregate functions Involving null Examples Name Age Food Jenny 33 null Donna null Pizza grouped by Food Roy 21 Steak Sara 34 null member relation food g sum(age) (member) Food sum(age) null 67 Pizza null Steak 21 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 91 Aggregate functions Involving null Examples Name Age Food Jenny 33 null Donna null Pizza grouped by Food Roy 21 Steak Sara 34 null member relation food g count(age) (member) Food count(age) null 2 Pizza 0 Steak 1 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 92 Relational Algebra Operators Additional Operators ◦ set intersection:  ◦ natural join: ◦ division:  ◦ assignment:  Extended Operators ◦ Generalized Project ◦ Aggregate Functions ◦ Outer Joins Modification Operations ◦ Deletion, insertion and updating of tuples CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 93 Deletion Similar to a query, ◦ BUT instead of displaying those tuples, they are removed from DB Delete “the whole tuple” A deletion is expressed in relational algebra by: rr–E where r is a relation and E is a relational algebra query. CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 94 Deletion Example - 1 Delete all account records in the Perryridge branch. account  account –  branch_name = “Perryridge” (account ) Any problem? account relation depositor relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 95 Deletion Example - 2 Delete all account records in the Perryridge branch. account  account –  branch_name = “Perryridge” (account ) Any problem? ◦ Referential Integrity ◦ depositor.account_number is a foreign key referring to the account.account_number CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 96 Deletion Example - 3 Delete all account records in the Perryridge branch. Better solution: r1   branch_name = “Perryridge” (account ) account  account – r1 r2   customer_name, account_number(depositor r1) depositor  depositor – r2 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 97  Deletion Example - 5 Delete all accounts at branches located in the city of Brooklyn. realize that the branches still remain, but the accounts in the branches are removed branch relation account relation depositor relation branch_name branch_city assets Brighton Brooklyn 7100000 Downtown Brooklyn 9000000 Mianus Horseneck 400000 Mprtj Tpwm Rye 3700000 Perryridge Horseneck 1700000 Pownal Bennington 300000 Redwood Palo Alto 2100000 Round Hill Horseneck 8000000 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 98 Deletion Example - 6 Delete all accounts at branches located in the city of Brooklyn. realize that the branches still remain, but the accounts in the branches are removed r1   branch_city = “Brooklyn” (account branch ) r2   account_number, branch_name, balance (r1) r3   customer_name, account_number (r2 depositor) account  account – r2 depositor  depositor – r3 CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 99 Insertion Tuples inserted must be “compatible” to the schema of the relation being inserted ◦ Same arity (same number of attributes) ◦ Same domain for corresponding attributes in relational algebra, an insertion is expressed by: r r  E where r is a relation and E is a relational algebra expression. CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 100 Insertion Example - 1 Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch. (Assume Smith is an existing customer) account relation depositor relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 101 Insertion Example - 2 Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch. (Assume Smith is an existing customer) account  account  {(“A-973”, “Perryridge”, 1200)} depositor  depositor  {(“Smith”, “A-973”)} CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 102 Insertion Example - 3 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. borrower relation account relation loan relation depositor relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 103 Insertion Example - 4 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. r1  (branch_name = “Perryridge” (borrower loan)) account  account  loan_number, branch_name, 200 (r1) depositor  depositor  customer_name, loan_number (r1) CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 104 Updating A mechanism to change a value in a tuple without changing all values in the tuple Use the generalized projection operator to do this task r  F ,F ,,F , (r ) 1 2 l ◦ if the i th attribute is not updated ◦ Fi is the JUST an attribute of r or, ◦ if the i th attribute is to be updated ◦ Fi is an expression, involving only constants and the attributes of r, which gives the new value for the attribute CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 105 Update Example - 1 Make interest payments by increasing all balances by 5 percent. account relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 106 Update Example - 2 Make interest payments by increasing all balances by 5 percent. account   account_number, branch_name, balance * 1.05 (account) CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 107 Update Example - 3 Pay all accounts with balances over $10,000 6 percent interest and pay all others 5 percent account relation CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 108 Update Example - 4 Pay all accounts with balances over $10,000 6 percent interest and pay all others 5 percent account   account_number, branch_name, balance * 1.06 ( BAL  10000 (account ))   account_number, branch_name, balance * 1.05 (BAL  10000 (account)) CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 109 Note about Updating Make sure that the query expression specifying the updates cover all the tuples (and only the tuples) in the relation being updated. If less, then result in deletion of certain tuples If more, then result in insertion of extra tuples CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 110 Practice 3 1. Retrieve customers’ name, branch name and balance of an account whose balance is between 500 and 700 inclusive. 2. Retrieve all branch information that has assets more than the asset at the branch “Round Hill”. 3. Retrieve customers’ name whose loan account in both “Round Hill” and “Redwood”. 4. Retrieve customers’ name whose account either in “Downtown” or “Mianus” or both. 5. Retrieve customers’ name, account number and balance of customers who have joined account. 6. Retrieve customers’ name and account number who have more than one account. CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 111 Practice 3 (Cont.) 7. Retrieve highest total assets of all branches that are located in the same city. 8. Retrieve average balance of all customers who lives in “Harrison” and “Stamford” 9. Retrieve the number of customers who have more than one account. 10. Retrieve the number of accounts that have more than one account holder. CSX3006 LECTURE 3 BY ASST. PROF. DR. RACHSUDA SETTHAWONG 112