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.

 

Normalization - Solved exercises

Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of finding Key in a database - Examples

Primary key identification

• SET 1 - Finding key of a given relation

• SET 2 - Finding key of a given relation

   

Normalization

• SET 1 - Normalizing upto 3NF - User table

• SET 2 -Normalizing upto BCNF

   

Normalization - solved exercises set 1

Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database - Examples

1. Consider the following relation schema R and set of Functional Dependencies F:

R(A,B,C,D,E),   F = {AC → E, C → D, D → A}

One of the FDs contains an extraneous attribute that can be removed without changing the dependencies implied by the above set. Explain which one. 

Answer: Since the functional dependencies C → D and D → A imply C → A (transitive dependency), the A in AC → E is extraneous. C alone can determine the other attributes.

2. For the following relation schema R and set of Functional Dependencies F:

R(A,B,C,D,E),  F = {AC → E, B → D, E → A}

List all candidate keys.

Answer: From the given set F of functional dependencies, it is very evident that B and C must be in the candidate key as they are not present in the Right Hand Side (RHS) of the given set of FDs. Hence, at first we can check for BC as the candidate key as follows;

If you know B, then you know B and D through FD B → D. Along with this, if you know C, then you know BCD. That is, BC → BCD. B and C together cannot determine A and E, so BC cannot be a candidate key. 

Then we can try with the attributes that are present in the LHS like B and C. First let us take A. Then we have,

ABC → ABCDE. So, ABC is a candidate key.

Now we shall try with the other LHS attribute E. Then we have,

BCE → ABCDE. So, BCE is another candidate key.

Checking BCA and BCE, we see that both of them are candidate keys.

3. The relation schema given in question number 2 above is not in BCNF due to the reason that it has two candidate keys. List one functional dependency that violates the rules for BCNF.

Answer: E → A violates the rules. If we don’t have a functional dependency like this, we have only one candidate key, i.e, ABC.

Normalization - solved exercises set 2

Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database - Examples

1.Suppose that you are given a relation R = (A,B,C,D,E) with the following functional dependencies: {CE → D,D → B,C → A}.

Find all candidate keys.

Answer: From the given set of functional dependencies, we can observe that only the attributes C and E are present only on LHS. Hence, we can try with C and E attributes to find candidate keys.

C alone cannot determine all the other attributes.

Hence, C → CA

E alone cannot determine all the other attributes.

Hence, E → E

C and E together can form a candidate key.

CE → ABCDE. Hence, CE is the only candidate key for the given relation R.

2.Suppose that you are given a relation R = (A,B,C,D,E) with the following functional dependencies:

{BC → ADE, D → B}.

Find all candidate keys.

Answer: Let us start with LHS of given functional dependencies;

• From D → B, D cannot uniquely determine the values of all the other attributes. Hence, D alone cannot be a candidate key.

• From BC → ADE, it is very clear that if you know values of B and C, you can determine the values of attributes A, D, and E. Hence, BC → ABCDE is holding. So, BC is one candidate key.

• From D → B, if you know D then you know B. If you know C also, then you can determine all the other attributes. Hence, CD is another candidate key.

3.You are given the following set of functional dependencies for a relation R(A,B,C,D,E,F),

F = {AB → C, DC → AE, E → F}.

What are the keys of this relation?

Answer:From the given set of FDs F, it is very evident that we cannot have any one attribute as the key for R. Hence, we need to check with the different combination of attributes.

• Let us try this example with the algorithm that is used for finding Attribute Closure. Click in the above link to visit the page.

• Let us start with AB. Assume that the result is AB.

Result = AB;

From AB → C, (if you know A and B, then you would know C) Result = ABC.

We cannot move further. That is, AB can determine only A, B, and C. Hence, AB cannot be a key.

• Let us try with the other combination ABD.

Result = ABD;

From AB → C, Result = ABCD

From DC → AE, Result = ABCDE

From E → F, Result = ABCDEF.

At last, Result includes all the attributes of the relation R. Hence, ABD is one of the keys for the relation R.

• Let us try with the other combination BCD.

Result = BCD;

From DC → AE, Result = ABCDE

From E → F, Result = ABCDEF.

At last, Result includes all the attributes of the relation R. Hence, BCD is also one of the keys for the relation R.