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.

 

Comparison of Normal Forms

Compare Normal Forms / 1NF vs 2NF vs 3NF vs BCNF / Differentiate between normal forms

	1 NF	2 NF	3 NF	BCNF
Properties to hold	All the attributes of the relation are atomic (indivisible into meaningful sub parts), Every attribute contains single value (per record).	At the first place the table is in 1NF, All the non-key attributes of the table are fully functionally dependent on the Primary key of the table.	The table is in 2NF, There is no Functional Dependency such that both Left Hand Side and Right Hand Side attributes of the FD are non-key attributes. In other words, no transitive dependency is allowed	For all the Functional Dependencies (FDs) hold in the relation R, if the FD is non-trivial then the determinant (LHS of FD) of that FD should be a Super key
Achievability	Always achievable	Always achievable	Always achievable	Not always
Lossless Join Decomposition	Always achievable	Always achievable	Always achievable	Sometimes not achievable
Dependency Preserving Decomposition	N/A		Possible	Either lossless join or dependency preserving decomposition is possible. Not both.
Anomalies	May allow some anomalies	May allow some anomalies	May allow some anomalies	Always eliminates anomalies
What is eliminated?	Eliminate repeating groups	Eliminate redundant data	Eliminate columns not dependent on key	Eliminate multiple candidate keys
Identification of Functional Dependencies	Not necessary	Must	Must	Must
Attribute Domain	Should be atomic	Should be atomic	Should be atomic	Should be atomic
Handling of Update Anomalies	Does not handle.	Handles	Handles	Handles
Composite Primary Key	Allowed	Allowed (if no partial dependency exists)	Allowed	Not allowed
Partial key dependencies (if AB → C, and if C can be fully determined by either A or B, then this dependency is partial key dependencies)	Permitted	Not permitted	Not permitted	Not permitted
Transitive dependencies (if A → B, and B → C then A → C)	Can be permitted	Can be permitted	Cannot be permitted	Cannot be permitted
Overview	It is about shape of a record type	It is about the relationship between key and non-key fields	It is about the relationship between key and non-key fields	It is about determinant should be a superkey.

*Please give your valuable input

Normalization - solved exercises - Normal forms 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 / Normalization to 1NF, 2NF, 3NF

Let us assume a table User_Personal as given below;

UserID	U_email	Fname	Lname	City	State	Zip
MA12	Mani@ymail.com	MANISH	JAIN	BILASPUR	CHATISGARH	458991
PO45	Pooja.g@gmail.co	POOJA	MAGG	KACCH	GUJRAT	832212
LA33	Lavle98@jj.com	LAVLEEN	DHALLA	RAIPUR	CHATISGARH	853578
CH99	Cheki9j@ih.com	CHIMAL	BEDI	TRICHY	TAMIL NADU	632011
DA74	Danu58@g.com	DANY	JAMES	TRICHY	TAMIL NADU	645018

• Is this table in First Normal Form?

Yes. All the attributes contain only atomic values.

• Is this table in Second Normal Form?

To verify this property, we need to find all the functional dependencies which are holding in User_Personal table, and have to identify a Primary key.

Let us do that by using the sample data. This leads to the following set of FDs;

F = { UserID →U_email Fname Lname City State Zip, 
Zip → City State, 
City →Zip State }

As UserID attribute can uniquely determine all the other attributes, we can have UserID as the Primary key for User_Personal table.

The next step is to check for the 2NF properties;

Property 1 – The table should be in 1NF.

Property 2 – There should not be any partial key dependencies.

Our table is in 1NF, hence property 1 is holding.

Primary key of our table is UserID and UserID is single simple attribute. As the key is not composite, there is no chance for partial key dependency to hold. Hence property 2 is also holding.

User_Personal table is in 2NF.

• Is User_Personal in 3NF?

To verify this we need to check the 3NF properties;

Property 1 – Table should be in 2NF.

Property 2 – There should not be any Transitive Dependencies in the table.

Table User_Personal is in 2NF, hence property 1 is satisfied.

User_Personal table holds the following Transitive dependency;

UserID →Zip, Zip →City State

Hence, property 2 is not satisfied and the table is not in 3NF.

Solution:

Decompose User_Personal. For this, we can use the functional dependencies Zip →City State and UserID →U_email Fname Lname City State Zip.

As a result, we can have the following tables (primary keys are underlined);

User_Personal (UserID, U_email, Fname, Lname, Zip)

City (Zip, City, State)

UserID	U_email	Fname	Lname	Zip
MA12	Mani@ymail.com	MANISH	JAIN	458991
PO45	Pooja.g@gmail.co	POOJA	MAGG	832212
LA33	Lavle98@jj.com	LAVLEEN	DHALLA	853578
CH99	Cheki9j@ih.com	CHIMAL	BEDI	632011
DA74	Danu58@g.com	DANY	JAMES	645018

Table - User_Personal

Zip	City	State
458991	BILASPUR	CHATISGARH
832212	KACCH	GUJRAT
853578	RAIPUR	CHATISGARH
632011	TRICHY	TAMIL NADU
645018	TRICHY	TAMIL NADU

Table – City

Both tables are in 3NF.

Hence, tables are normalized to Third Normal Form.