How to normalize the schema to BCNF

Question

I am having some issues with normalization. I have a schema REPAYMENT which looks like this:

Now, from what I've gathered the functional dependencies that hold in the schema is

{borrower_id} --> {name, address, request_date, loan_amount}

{request_date} --> {repayment_date, loan_amount}

{loan_amount] --> {repayment_amount}

(correct me if I'm wrong?)

I'm supposed to normalise the schema to BCNF, but I'm a bit confused. Is the candidate key request_date and borrower_id?

It can be used to register information on the re- payments on micro loans. A borrower, his name and address, are identified with an unique borrower_id. Borrowers can have multiple loans at the same time, but each of those loans ( specified by loan_amount, repayment_date and repayment_amount) have a different re- quest date. Thus a loan can be identified with the borrower ID and the request date of the loan. The borrower can repay multiple (different) loans on the same date, but each loan can only be repaid once (on one date with one amount). There is a system which for each request date and amount of a loan determines the repayment date and amount to be repaid. The loan amount requested and the repaid amount are not the same since there is an interest rate that applies.

Answer 1

From the definition of candidate key:

In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that:

1. The relation does not have two distinct tuples (i.e. rows or records in common database language) with the same values for these attributes (which means that the set of attributes is a superkey)

2. There is no proper subset of these attributes for which (1) holds (which means that the set is minimal).

Now your question :

Is the candidate key request_date and borrower_id?

It is a superkey, but not minimal one. Here's how we compute the candidate key.

Which attribute occurs only on the left side, considering all the F . D's ?

ITS borrower_id.This means that it must be a part of every key of this given schema. Now let us compute its closure.

Because of {borrower_id} --> {name, address, request_date, loan_amount}:

closure(borrower_id) = borrower_id, name, address, request_date, loan_amount.

Because of {request_date} --> {repayment_date, loan_amount} and closure(borrower_id) has request_date, this means

closure(borrower_id) = borrower_id, name, address, request_date, loan_amount, repayment_date

And finally because of {loan_amount] --> {repayment_amount} and closure(borrower_id) has loan_amount, this means

closure(borrower_id) = borrower_id, name, address, request_date, loan_amount, repayment_date, repayment_amount

Because closure of borrower_id contains all the attributes, borrower_id is a key and since it is minimal, it is indeed the candidate key and the only one.

Now let us decompose the schema into BCNF. The algorithm is:

Given a schema R.

1. Compute keys for R.

2. Repeat until all relations are in BCNF.

   • Pick any R' having a F.D A --> B that violates BCNF.
   • Decompose R' into R1(A,B) and R2(A,Rest of attributes).
   • Compute F.D's for R1 and R2.
   • Compute keys for R1 and R2.

Since {request_date} --> {repayment_date, loan_amount} and request_date is not a key, it violates BCNF so we split schema into two relations:

1. R1(request_date,repayment_date,loan_amount)

2. R2(borrower_id,name,address,request_date,repayment_amount)

Clearly R1 is in BCNF. But R2 is NOT in BCNF , because we missed the following F.D. which is:

address --> name

and we know address is not the key, so we split the R2 further as:

1. R3(borrower_id,address,request_date,repayment_amount)

2. R4(address,name)

Now, clearly both R3 and R4 are in BCNF. Had we not split the R2 further, we end up storing the same combination of address and name for every loan the person takes, which is redundancy.