Borrowe(borrower id, name, address) Loan(loan id, borrower_id, requestdate, loan

Question

Borrowe(borrower id, name, address) Loan(loan id, borrower_id, requestdate, loanamount) Repayment(borrower id, loan id, repayment date, repayment_amount) Assume that the following four SQL commands are known to be frequent: 1. SELECT DISTINCT name, address FROM Borrower WHERE borrower_id = ?; 2. SELECT * FROM Repayment WHERE borrower_id = ? AND repayment_date > ?; 3. SELECT borrower_id, loanamount FROM Loan WHERE loanamount BETWEEN ? AND ?; a. Suggest one or more indexes, taking into account the above. State the indexed attributes and argue shortly for your choices. b. Suggest one or more partitioning options, taking into account the above. State the partitioning attributes and the type of partitioning. Argue shortly for your choices.

Explanation / Answer

a. Index

Index improves the performance if it is been created on most used attribute of the table.

Borrower table should be indexed on borrower_id attribute: Beacause this table is mostly queried based on borrower_id attribute.

Repayment table should be indexed on borrower_id attribute:Beacause this table is mostly queried based on borrower_id attribute.

loan table should be indexed on loanamount attribute: Beacause this table is mostly queried based on loanammount attribute.

b. Partitioning:

Partitionaing allows table to be divided into smaller pieces according to specific strategy. Partitioning improves the performance, manageability and availability of the databases.

So, similar to indexes, we should partition the tables according to the frequent queries such we can improve the performance as much as posssible. i.e. based on the most queried attribute.

Borrower table should be partitioned on borrower_id attribute: Beacause this table is mostly queried based on borrower_id attribute.

Repayment table should be partitioned on borrower_id attribute:Beacause this table is mostly queried based on borrower_id attribute.

loan table should be partitioned on loanamount attribute: Beacause this table is mostly queried based on loanammount attribute.

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.