Aljabar Relasi Operasi Tambahan Operasi tambahan menyederhanakan query
Aljabar Relasi
Operasi Tambahan Operasi tambahan menyederhanakan query • Set intersection • Natural join • Assignment
Set-Intersection • Notasi : r s = { t | t r and t s } – r, s memiliki kesamaan aritas – attribute r dan s harus bertipe sama • catatan: r s = r – (r – s)
Contoh Set-Intersection • Relasi r, s: A B 1 2 1 r • r s A B 2 3 s A B 2
Natural-Join n Notasi : r s • Contoh: R = (A, B, C, D) S = (E, B, D) – Hasil = (A, B, C, D, E) – r s didefinisikan sbg: r. A, r. B, r. C, r. D, s. E ( r. B = s. B r. D = s. D (r x s))
Natural Join • Relations r, s: A B C D B D E 1 2 4 1 2 a a b 1 3 1 2 3 a a a b b r n r s s A B C D E 1 1 2 a a b
Assignment • Contoh : temp 1 R (r ) temp 2 R (temp 1 x s ) result = temp 1 – temp 2
Bank Example Queries n Find the names of all customers who have a loan and an account at bank. customer_name (borrower) customer_name (depositor) • Find the name of all customers who have a loan at the bank and the loan amount customer_name, loan_number, amount (borrower loan) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower (customer_name, loan_number)
Bank Example Queries • Find all customers who have an account from at least the “Downtown” and the Uptown” branches. l Query customer_name ( branch_name = “Downtown” (depositor customer_name ( branch_name = “Uptown” (depositor account (account_number, branch_name, balance) depositor (customer_name, account_number) account )) account))
Operasi lain • Generalized Projection • Aggregate Functions • Outer Join
Projection Generalisasi • Pengembangan dari operasi projection yg menggunakan fungsi arithmetic di dalam daftar projection. • E : ekspresi aljabar relasi • F , …, F ekspresi arithmetic. • Misl: credit_info(customer_name, limit, credit_balance), find how much more each person can spend: 1 2 n customer_name, limit – credit_balance (credit_info)
Operasi & Fungsi Aggregate • Fungsi Aggregation. avg: average value min: minimum value max: maximum value sum: sum of values count: number of values • Operasi Aggregate dalam aljabar relasi E : ekspresi aljabar relasi – G 1, G 2 …, Gn : daftar attribut yg akan digrup (bisa kosong) – Fi function Agregate – Ai name atribut
Aggregate • Relation r: n g sum(c) (r) A B C 7 sum(c ) 27 7 3 10
Aggregate • Relasi account di group berdasarkan branch-name: branch_name Perryridge Brighton Redwood branch_name account_number balance A-102 A-201 A-217 A-215 A-222 400 900 750 700 g sum(balance) (account) branch_name Perryridge Brighton Redwood sum(balance) 1300 1500 700
Fungsi. Aggregate • Hasil agregasi tdk memiliki nama – Dapat menggunakan operasi rename utk memberikan nama branch_name g sum(balance) as sum_balance (account)
Join • Relation loan_number L-170 L-230 L-260 branch_name Downtown Redwood Perryridge amount 3000 4000 1700 n Relation borrower customer_name Jones Smith Hayes loan_number L-170 L-230 L-155
Join customer_name Jones Smith Hayes loan_number L-170 L-230 L-260 L-170 L-230 L-155 Downtown Redwood Perryridge Loan Borrower • branch_name Join loan borrower loan_number L-170 L-230 branch_name Downtown Redwood amount 3000 4000 customer_name Jones Smith amount 3000 4000 1700
customer_name Jones Smith Hayes loan_number Downtown Redwood Perryridge L-170 L-230 L-260 L-170 L-230 L-155 branch_name Loan Borrower n Left Outer Join loan borrower loan_number L-170 L-230 L-260 branch_name Downtown Redwood Perryridge amount 3000 4000 1700 customer_name Jones Smith null amount 3000 4000 1700
customer_name Jones Smith Hayes loan_number L-170 L-230 L-155 loan_number Downtown Redwood Perryridge L-170 L-230 L-260 Borrower branch_name Loan n Right Outer Join loan_number L-170 L-230 L-155 borrower branch_name Downtown Redwood null amount 3000 4000 null customer_name Jones Smith Hayes amount 3000 4000 1700
loan_number branch_name Downtown Redwood Perryridge L-170 L-230 L-260 amount 3000 4000 1700 customer_name Jones Smith Hayes Loan loan_number L-170 L-230 L-155 Borrower n Full Outer Join loan borrower loan_number L-170 L-230 L-260 L-155 branch_name Downtown Redwood Perryridge null amount 3000 4000 1700 null customer_name Jones Smith null Hayes
Modifikasi Database • • The content of the database may be modified using the following operations: – Deletion – Insertion – Updating All these operations are expressed using the assignment operator.
Deletion • • • 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. Can delete only whole tuples; cannot delete values on only particular attributes A deletion is expressed in relational algebra by: r r–E where r is a relation and E is a relational algebra query.
Deletion Examples • 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 account_number, branch_name, balance (r 1) r 3 customer_name, account_number (r 2 depositor) account – r 2 depositor – r 3 branch (branch_name, branch_city, assets) account (account_number, branch_name, balance) depositor (customer_name, account_number)
Insertion • • • To insert data into a relation, we either: – specify a tuple to be inserted – write a query whose result is a set of tuples to be inserted in relational algebra, an insertion is expressed by: r r E where r is a relation and E is a relational algebra expression. The insertion of a single tuple is expressed by letting E be a constant relation containing one tuple.
Insertion Examples • Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch. account {(“A-973”, “Perryridge”, 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 loan_number, branch_name, 200 (r 1) depositor customer_name, loan_number (r 1) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower (customer_name, loan_number)
Updating • • A mechanism to change a value in a tuple without charging all values in the tuple Use the generalized projection operator to do this task
Update Examples • Make interest payments by increasing all balances by 5 percent. account_number, branch_name, balance * 1. 05 (account) n Pay all accounts with balances over $10, 000 with 6 percent interest and pay all others 5 percent account_number, branch_name, balance * 1. 06 ( BAL 10000 (account )) account_number, branch_name, balance * 1. 05 ( BAL 10000 (account))
Contoh Banking branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower (customer_name, loan_number)
- Slides: 28