数据库系统概念原书第5版(英文)第二章ppt.ppt

* Dept. of Comp. Sci. Tech., Tongji Uni. * Deletion Examples Delete all accounts at branches located in Needham. r1 ? ??branch_city = “Needham” (account branch) r2 ? ?branch_name, account_number, balance (r1) r3 ? ? customer_name, account_number (r2 depositor) account ? account – r2 depositor ? depositor – r3 Delete all loan records with amount in the range of 0 to 50 loan ? loan – ??amount ??0?and amount ? 50 (loan) Delete all account records in the Perryridge branch. account ? account – ??branch_name = “Perryridge” (account) * Dept. of Comp. Sci. Tech., Tongji Uni. * 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 * Dept. of Comp. Sci. Tech., Tongji Uni. * Insertion Examples Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch. account ? account ? {(A_973, “Perryridge”, 1200)} depositor ? depositor ? {(“Smith”, A_973)} 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) * Dept. of Comp. Sci. Tech., Tongji Uni. * Updating A mechanism to change a value in a tuple without changing all other values in the tuple Use the generalized projection operator to do this task r ? ? F1, F2, …, Fi, (r) Each Fi is either the ith attribute of r, if the ith attribute is not updated, or, if the attribute to be updated Fi is an expression, involving only constants and the attributes of r, which gives the