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.

 

Wait-die deadlock prevention algorithm in Animation

Animated Wait-die deadlock prevention algorithm / Wait-Die scheme explained / How does wait-die scheme prevents deadlock / Wait-die algorithm example in DBMS

Wait-Die Deadlock Prevention Algorithm

For more examples, please visit:

Deadlock prevention algorithms in database

Harddisk drive vs RAM - a comparison

Harddisk drive and Random access memory / Why RAM is considered as fastest when compared to hard disk? / Harddisk vs RAM / Computer performance and storage devices

Harddisk drive vs RAM - a comparison

	Hard Drive	RAM
Size	Large sizes Internal drives – Max 8 TB for desktops External drives – Max 8 TB	Small sizes Max 32 GB (as per the year 2015 for desktop computers)
Storage	Permanent (Non-volatile) Do not lose data due to power loss (Switched off).	Temporary (Volatile) Lose all data due to power loss (Switched off).
Time taken to read files	Vary for files. Depends on the size and location of a file.	Fixed for files. We are able to pick the data directly as per the size.
Units of storage	Bytes	Bytes
Requires power to retain content	No Hard drives retain contents as the writing surfaces are magnetic	Yes RAM cannot retain the content due to power loss is because of RAM is made up of circuits.
Made up of	Magnetic components	IC (Integrated Circuits) components
Storage capacity vs performance	No performance improvement. Increase in the size of the hard disk, say, from 500 GB to 1 TB would not show any performance improvements.	Performance changes. Increase in the size of RAM, say, from 4 GB to 8 GB will definitely show some improvements in performance.
How the files are stored?	Magnetically	Electronically
Flexibility in space	Flexible. We can delete unwanted files if we need more space (on the same disk)	Not flexible. If any program does not function due to less memory size, you need to upgrade your memory to make that program work.
Hardware dependency	Works on 32 bit and 64 bit computers	Upto 4 GB – supported by 32 bit computers Upto and above 4 GB – supported by 64 bit computers

SQL Exercise 5

SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries / Solved SQL exercises

Consider a relation REPAYMENT with the following schema;

REPAYMENT(BORROWER_ID, NAME, ADDRESS, LOANAMOUNT, REQUESTDATE, REPAYMENT_DATE, REPAYMENT_AMOUNT)

Assume that this table records the repayment of loans by the borrowers. A borrower may have multiple entries if he/she has paid multiple installments.

Write SQL statements (queries) to achieve the following;

Question (a)

Find all the records with information on repayments from the borrower with id equal to 42, and where the lent amount exceeds 1000.

Answer (a)

SELECT *

FROM Repayment

WHERE borrower_id=42 AND loanamount>1000;

Question (b)

Find the total amount repaid for every address in the repayment table.

Answer (b)

SELECT address, SUM(repayment_amount)

FROM Repayment

GROUP BY address;

Question (c)

Delete all information on the completed loans. (Note: you can find the status of the loan by summing the total repaid amount. If the total repaid amount is equal to the loan amount, then you would say that the loan is ended.)

Answer (c)

DELETE FROM Repayment A

WHERE loanamount=

(SELECT SUM(repayment_amount)

FROM Repayment B

WHERE B.borrower_id=A.borrower_id AND B.requestdate=A.requestdate);

Question (d)

Find all the borrower names who has unique address. (ie., you should not count the borrowers who are from the same address)

Answer (d)

SELECT name

FROM Repayment A

WHERE 1=

(SELECT COUNT(DISTINCT name)

FROM Repayment B

WHERE A.address=B.address);

Question (e)

Find the total number of repayments made by every borrower.

Answer (e)

SELECT borrower_id, count(*)

FROM repayment

GROUP BY borrower_id;

*******************