Answer:
Check the explanation
Explanation:
Answer a):
From the given information, we can derive the following set of functional dependencies;
Borrower_id → name [given: every borrower is identified with an unique id]
Borrower_id → address [given: each borrower has only one address]
Borrower_id, Requestdate → loanamount [given: more than one loan cannot be requested by a single borrower]
Borrower_id, requestdate, repayment_date →repayment_amount [given: a borrower can make multiple repayments on a single day, but not on a single loan]
From the above set of FDs, it is evident that we can uniquely identify all the other attributes of Repayment table, if we know the values of (borrower_id, requestdate, repayment_date). That is,
Borrower_id, requestdate, repayment_date →name, address, loanamount, repayment_amount.
Hence, attributes (Borrower_id, requestdate, repayment_date) together forms a candidate key.
Answer b):
Is the given relation Repayment is in 1NF?
Yes. It has a key. Hence, we can make unique identification of records.
Is the given relation is in 2NF?
No. We have the following partial key dependencies.
1. We can easily derive name and address of every borrower if we know the borrower_id from the FDs Borrower_d →name, and Borrower_id →address.
2. We can derive the loanamount if we know borrower_id, and requestdate from the FD Borrower_id, Requestdate → loanamount.
Hence, the relation Repayment is not in 2NF. To convert it into a 2NF relation, we can decompose Repayment into the following relations;
Borrower (Borrower_id, Name, Address)
Borrower_loan (Borrower_id, Requestdate, Loanamount)
Repayment (Borrower_id, Requestdate, Repayment_date, Repayment_amount)
From the derived FDs, we know that all these tables are in 2NF.
Are these tables in 3NF?
Yes. There are no transitive dependencies present in the above tables’ set of functional dependencies. Hence, we would say that all these tables are in 3NF.
Are these tables in BCNF?
Yes. There are no more than one candidate keys present in the above set of tables. Hence the following decomposed tables are in Boyce-Codd Normal Form.
Borrower (Borrower_id, Name, Address)
Borrower_loan (Borrower_id, Requestdate, Loanamount)
Repayment (Borrower_id, Requestdate, Repayment_date, Repayment_amount)
