Assignment Task:
Question: Consider the bank database above.
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, delete her from the depositor relation.
Based on the following requirements below:
Design the Bank database according the following data requirements. Use postgrSQL to implement the bank application.
The Data Requirements for the Bank Database is following:
a) The bank is organized into branches. Each branch is located in a particular city and is identified by a unique name. The bank monitors the assets of each branch.
b) Bank customers are identified by their customer_id values. The bank stores each customer's name and the street and city where the customer lives. Customers may have accounts and can take out loans. A customer may be associated with a particular banker, who may act as a loan officer or personal banker for that customer.
c) Bank employees are identified by their employee_id values. The bank administration stores the name and telephone number of each employee, the names of the employee's dependents, and the employee_id number of the employee's manager. The bank also keeps track of the employee's start date, thus, length of employment.
d) The bank offers two types of accounts --savings and checking accounts. Accounts can be held by more than one customer, and a customer can have more than one account. Each account is assigned a unique account number. The bank maintains a record of each account's balance and the most recent date on which account was accessed by each customer holding the account. In addition, each saving account has an interest rate and overdrafts are recorded for each checking account.
e) A loan originates at a particular branch and can be held by one or more customers. A loan is identified by a unique loan number. For each loan, the bank keeps track of the loan amount and the loan payment, although a loan payment number does not uniquely identify a particular payment among those for all the bank's loans, a payment number does identify a particular payment for a specific loan. The data and amount are recorded for each payment.