Please create following tables
BRANCH(BranchID, branch_name, branch_city, assets, ModifiedDate)
BranchID and branch_name should be unique
assets not NULL.
ACCOUNT(AccountID, BranchID, AccountNumber, AccountType, Balance, ModifiedDate),
PRIMARY KEY ( AccountID ), FOREIGN KEY ( BranchID )
CUSTOMER( CustomerID,Name, Street, City, State, Zip, Country, ModifiedDate)
PRIMARY KEY ( CustomerID )
LOAN(LoanID,AccountID,BranchID,LoanNumber,LoanType,Amount, ModifiedDate),
PRIMARY KEY ( LoanID)
FOREIGN KEY ( AccountID ) REFERENCES ACCOUNT(AccountID)
ON DELETE CASCADE,
FOREIGN KEY ( BranchID ) REFERENCES Branch(BranchID) ON DELETE CASCADE,
Loan Type = (Personal/Home/Car)
DEPOSITOR(CustomerID,AccountID,ModifiedDate),
PRIMARY KEY ( CustomerID, AccountID ),
FOREIGN KEY ( AccountID ) REFERENCES ACCOUNT(AccountID) ON DELETE CASCADE, FOREIGN KEY ( CustomerID ) REFERENCES Customer(CustomerID)
BORROWER(CustomerID,LoanID, ModifiedDate),
PRIMARY KEY
( CustomerID, LoanID ),
FOREIGN
KEY
(
CustomerID ) REFERENCES Customer(CustomerID),
FOREIGN
KEY
(
LoanID ) REFERENCES Loan(LoanID)
TRANSACTION(TransactionID, AccountID, TranType, Amount, ModifiedDate),
PRIMARY KEY ( TransactionID ),
FOREIGN KEY ( AccountID ) REFERENCES ACCOUNT(AccountID) ON DELETE CASCADE
Tran Type can be (Loan payment/Loan Taken/Simple Deposit/Simple Withdraw)
You will need to:
• Insert atleast 8-10 entries in each table.
• Write the following queries
Update the balance of those customers by decreasing 3% of their balance whose balance is below 3000.
Delete the entry of customer whose balance is below 500 and savepoint (sp1).
List all the customer details who have taken atleast 2 loans.
Delete the customers who have taken all 3 type of loans.
Execute rollback command till sp1 and commit.
Lock(read) the table Account and increase the balance of the customers by
5% whose balance 10000.
Unlock the Account table and apply write lock on the same table(account). Then increase the balance of the customers by 5% whose balance 10000.
Unlock all the tables.
