Thursday, 14 January 2016

Normalization - solved exercise

Normalization process - solved exercise / How to find candidate key? / How to normalize to BCNF? / Normalize to 2NF, 3NF, BCNF / Normalization Examples in DBMS / Normalization in Database





The following relation schema can be used to register information on the repayments on micro loans.

Repayment (borrower_id, name, address, loanamount, requestdate, repayment_date, repayment_amount)

A borrower is identified with an unique borrower_id, and has only one address. Borrowers can have multiple simultaneous loans, but they always have different request dates. The borrower can make multiple repayments on the same day, but not more than one repayment per loan per day.
a) State a key (candidate key) for Repayment.
b) Make the normalization to BCNF. Show the steps.

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)


Go back to Normalization Exercises page.

1 comment:

  1. Greetings Sir,

    I have encountered an issue in terms of functional dependencies.
    As long as the attribute values are given along the table I have no problems identifying the FDs.
    but whenever the table's characteristics are given within the problem's statements, I am completely hopeless in identification of functional dependencies.
    could you help me out please

    ReplyDelete

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...