Description
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.
-
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.