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?

3NF is about eliminating transitivefunctional dependencies.

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)

Go to normalization solved exercises page

Go to normalization multiple choice questions page