Solved–Lab Assignment 6– Solution

Description

Please create following tables

1. BRANCH(BranchID, branch_name, branch_city, assets, ModifiedDate)

1. • BranchID and branch_name should be unique

1. • assets not NULL.

2. ACCOUNT(AccountID, BranchID, AccountNumber, AccountType, Balance, ModifiedDate),

PRIMARY KEY ( AccountID ), FOREIGN KEY ( BranchID )

3. CUSTOMER( CustomerID,Name, Street, City, State, Zip, Country, ModifiedDate)

PRIMARY KEY ( CustomerID )

4. 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)

5. DEPOSITOR(CustomerID,AccountID,ModifiedDate),

PRIMARY KEY ( CustomerID, AccountID ),

FOREIGN KEY ( AccountID ) REFERENCES ACCOUNT(AccountID) ON DELETE CASCADE, FOREIGN KEY ( CustomerID ) REFERENCES Customer(CustomerID)

6. BORROWER(CustomerID,LoanID, ModifiedDate),

PRIMARY KEY		( CustomerID, LoanID ),
FOREIGN	KEY	(	CustomerID ) REFERENCES Customer(CustomerID),
FOREIGN	KEY	(	LoanID ) REFERENCES Loan(LoanID)

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

• Write the following queries

1. Update the balance of those customers by decreasing 3% of their balance whose balance is below 3000.

2. Delete the entry of customer whose balance is below 500 and savepoint (sp1).

3. List all the customer details who have taken atleast 2 loans.

4. Delete the customers who have taken all 3 type of loans.

5. Execute rollback command till sp1 and commit.

6. Lock(read) the table Account and increase the balance of the customers by

5% whose balance > 10000.

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

8. Unlock all the tables.