Sunday, December 11, 2016

Normalize the relational schema to third normal form

How to normalize a table to 3NF? / Show the steps in 3nf normalization / Normalization solved exercises / 1nf, 2fn, 3nf


Question:
Consider the relation schema Membership for a library database as follows;
Membership (MID, Name, Address, PhoneNum, ParentMID, ISBN, Title, Authors, BorrowDate, ReturnedDate, FineDue, FinePaid).
Here, ParentMID may have the values Null, Father_Name, Mother_Name or both. The following is the set F of functional dependencies that hold in Membership table;
F = { MID → Name, Address, PhoneNum, ParentMID;
(MID, ISBN, BorrowDate) → ReturnedDate, FinePaid, FineDue;
ISBN → Title, Authors}
Normalize the Membership schema to 3NF and show the steps.

Answer:
Given set of FDs;

  • FD 1: MID → Name, Address, PhoneNum, ParentMID
  • FD 2: (MID, ISBN, BorrowDate) → ReturnedDate, FinePaid, FineDue
  • FD 3: ISBN → Title, Authors

Is the table Membership in 1NF?

It is not in 1NF because of the attribute ParentMID which is a multi-valued attribute (when both parents MIDs are issued and stored for a member). To solve this we may create a separate table with MID and ParentMID attributes as follows;
Parent (MID, ParentMID)
For parent table the key is {MID, ParentMID}, hence the table is in 3NF.
After this decomposition, we have the following schemas;

  • Schema 1: Parent (MID, ParentMID)
  • Schema 2: Membership (MID, Name, Address, PhoneNum, ISBN, Title, Authors, BorrowDate, ReturnedDate, FineDue, FinePaid)
At this stage, the Membership table is in 1NF.

Is Membership in 2NF?

2NF is about eliminating partial keydependencies (if any) from a relational schema. To do this, we need to find the primary key for Membership table.
Let us find the closure of LHS attributes of given functional dependencies to check whether the LHS attributes form a key or not;
From FD1,
(MID)+ = MID, Name, Address, PhoneNum ≠ Membership
From FD2,
(MID, ISBN, BorrowDate)+ = MID, Name, Address, PhoneNum, ISBN, Title, Authors, BorrowDate, ReturnedDate, FineDue, FinePaid = Membership
Hence, the key for Membership is a composite key (MID, ISBN, BorrowDate).
Now let us check for partial dependencies;

  • The attributes MID, Name, Address, and PhoneNum can be determined by using MID alone.
  • The attributes ISBN, Title, and Authors can be determined by using ISBN alone. [from FD3].
So, the schema shows partial functional dependencies;
We shall break schema 2 to convert into a set of 2NF schemas;
Schema 2a: (MID, Name, Address, PhoneNum)
Schema 2b: (ISBN, Title, Authors)
Schema 2c: (MID, ISBN, BorrowDate, ReturnedDate, FineDue, FinePaid)
Is schema 2a in 2NF? – Yes. MID is the key and no partial key dependency.
Is schema 2b in 2NF? – Yes. ISBN is the key and no partial dependencies.
Is schema 2c in 2NF? – Yes. (MID, ISBN, BorrowDate) is the composite key and no partial dependencies.

Are schema 1, 2a, 2b and 2c are in 3NF?

As per the given set of functional dependencies, we do not have any transitive functional dependencies in any of these tables. Hence, the list of 3NF tables is as follows;

  • Schema 1: Parent (MID, ParentMID)
  • Schema 2a: (MID, Name, Address, PhoneNum)
  • Schema 2b: (ISBN, Title, Authors)
  • Schema 2c: (MID, ISBN, BorrowDate, ReturnedDate, FineDue, FinePaid)




 


3 comments:

  1. Shouldn't you be able to make put fines in a different table along with ISBN no, as if there is no fine then the cell will be empty

    ReplyDelete
    Replies
    1. Hi there, we cannot create a separate table for fine as you mentioned. Fine is collected from the member for an ISBN if the due is over. This may happen to any number of members for the same book. Hence, we cannot create a table with attributes (ISBN, Finedue).
      Have I answered your question? Please let me know more if your expectation is different.

      Delete

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery