Problem 7 – Extra Credit

Consider the following relational schema which can be used to store information on the repayments on loans.

REPAYMENT (borrower_id, name, address, request_date, request_amount loan_amount, repayment_date, replayment_amount)

A borrower is identified with a unique borrower_id, and has only on address. Borrowers can request multiple loans, but they always have different request dates. The borrower can make multiple repayments on the same date, but not more than one repayment per loan per date.   

(a) What are the significant functional dependencies in this relation?

(b) What is/are the candidate key(s) of this relation?

(c) is this relation in 1NF, 2NF, 3NF, BCNF? And why?

Cheers

Solution for the above given task:

(a) What are the significant functional dependencies in this relation?

It helps in establishing keys that are used to define normal forms for this relations.

(b) What is/are the candidate key(s) of this relation?

The candidate key is borrower_id and requestdate.

(c) is this relation in 1NF, 2NF, 3NF, BCNF? And why?

This relation is in 1NF

Step-by-step explanation

(a) What are the significant functional dependencies in this relation?

It helps in establishing keys that are used to define normal forms for this relations.

Functional dependencies are expressed in a form of A→ B.

where B is a subset of A)

A is a superkey for the schema relation.

Therefore to define our function dependence we can split the table into three as follows:  

borrower(borrower_id, name, address)
borrower_loan(borrower_id,requestdate, loanamount)
loan(requestdate, loanamount, repayment_date, request_amount)

(b) What is/are the candidate key(s) of this relation?

The candidate key is borrower_id and requestdate.

A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.

 Therefore the borrower_id and requestdate are the candidate key because it holds the unique for every rows and it is the primary key for the table we have generated in part a using the functional dependence.

(c) is this relation in 1NF, 2NF, 3NF, BCNF? And why?

This relation is in 1NF

All the attributes in the relation should have atomic domains. This means each cell in the table should have only a single value.