Consider the bank database of Figure 5.25. Write an SQL trigger to carry out the following action: On delete of an account, for each owner of the account, check if the owner has any remaining accounts, and if she does not, deleteher from the depositor relation
branch(branch name, branch city, assets)
customer (customer name, customer street, cust omer city)
loan (loan number, branch name, amount)
borrower (customer name, loan number) a
ccount (account number, branch name, balance )
depositor (customer name, account number)
Figure 5.25 Banking database
create trigger check-and-delete-trigger after delete on account
referencing old row as oldrow
for each row
delete from depositor
where depositor.customer name not in
( select customer name from depositor
where account number <> oldrow.account number )
end
Explanation:
This trigger (check-and-delete-trigger) first checks the customername in depositor and if the name is not found then it deletes from depositor
Consider the bank database of Figure 5.25. Write an SQL trigger to carry out the following...
1. Given the following BANKING database, formulate a Relational Algebra expression for each of the following questions. SELECT should be performed before any JOIN operation. Notation: use the symbol S for SELECT, P for PROJECT, J for INNER JOIN, * for NATURAL JOIN, LJ for LEFT JOIN, RJ for RIGHT JOIN, R for RENAME, and F for FUNCTION. Please type your answer; hand-writing is not accepted. branch(branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) loan (loan_number, branch_name, amount) borrower (customer_name, loan_number)...
Write SQL statements to answer the following questions using Assignment 3’s schema (tables from part 1). 1- Find how many branches had have loans over $2000.00. 2- For each branch, find the most expensive loan. Your output should include Branch Id, loan amount for the highest loan for that Branch. 3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer. 4- Find the total balance amount of...
Specify relational calculus expression for the following
descriptions on the Library database schema shown in Figure
4.6.
a) Retrieve Publishers’ name and phone number whose books where
borrowed from Baltimore Branch.
b) List the book title, book id and branch name with less than
10 copies
c) List customer card number, customer name, branch name and
book title for books which are currently on loan.
BOOK Book id Title Publisher_name BOOK_AUTHORS Book idAuthor name PUBLISHER Name Address Phone BOOK COPIES...
WRITE SQL QUERY THAT DOES THE FOLLOWING Q3| Find the customer's name and branch names of all customers who do have accounts but not a loan in the bank. And find those customers who do have loan but not have an account in the bank. GIVEN DATABASE TABLES 2 Account table Branch table Customer table Loan table BNAME 3 A# CNAME BNAME BAL BNAME ASSETS BCITY CNAME STREET CCITY CNAME AMT 1234 Baba 2222 Rahimi Sauthdale Ridgedale 150eee Minnetonka Minnetonka...
DATABASE Draw an ER diagram according to the following requirements. The bank application keeps track of the following information. As a customer of the bank, he/she must either own an account or borrow a loan. The banking application does not require a customer to both own account and borrow a loan. Customer information includes a SSN (unique), a name, and an address. A customer can borrow at most one loan. A loan has a loan number (unique) and an amount....
Write the following queries in Relational Algebra, for the following bank schema: account (ano, bname, balance) branch (bname, bcity, assest) customer (Cname, cstreet, ccity) loan (lno, bname, amount) deposit (cname, ano) borrower (cname, ino) -Select all loan tuples where branch name is Abha. -Select all loan tuples where branch name is Abha and loan amount less than 200000. Please type the solution on the keyboard so that I can copy and paste
Write the following SQL statements in Microsoft Access by using the Books database from Week 2 Assignment 2. Once complete, copy and paste the SQL statements in a Microsoft Word document: Write SQL statements: To update the publisher name from READ WITH US to READ FOR US To verify the updated name field for the publisher with ID 6 To make the following updates to the Publisher table (be careful with WHERE): Make Contact="John Travolta" for publisher with ID number...
Prepare and execute each of the queries listed using the "Adventureworks2012" database in SQL: Server: http://msftdbprodsamples.codeplex.com/releases/view/93587 When all of your queries are complete, cut and paste the SQL Syntax into a word document. In order to see what the column names are, you need to click on the table and then Columns to see the field names. Make sure to include column headings that make sense in the queries (use the as “Field Name” after the field selected). Multi-table Queries...
Use the Premiere Products database (see Figure 1-2 in Chapter 1) to complete the following exercises. If directed to do so by your instructor, use the information provided with the Chapter 3 Exercises to print your output. 1. List the part number and description for all parts. The part descriptions should appear in uppercase letters. 2. List the customer number and name for all customers located in the city of Grove.Your query should ignore case. For example, a customer with...