The second normal form (2nf), Database Management System

The Second Normal Form (2NF)

Definition: A relation is in 2NF if it is in 1NF and each non-key attribute is fully dependent on each candidate key of the relation.

Some of the points that should be noted here are:

  • A relation having a one attribute key has to be in 2NF.
  • In the case of composite key, partial dependency on key that is part of the key is not allowed.
  • 2NF tries to make sure that information in one relation is about one thing
  • Non-key attributes are those that are not part of any candidate key.

Let us now reconsider, which describes the FDs of the relation to the relation STUDENT (Enrolmentno, Sname, Address, Cno, Cname, Instructor, Office). These FDs can also be written as:

Enrolmentno     →         Sname,           Address     (1)

Cno                 →           Cname,           Instructor  (2)

Instructor        →                                   Office        (3)

 

The key attributes of the relation are (Cno + Enrolmentno). Rest of the attributes are non-key attributes. For the 2NF decomposition, we are concerned with the FDs (1) and (2) as above as they relate to partial dependence on the key that is (Cno +Enrolmentno). As these dependencies illustrates that relation in not in 2NF and therefore suffer from all the three anomalies and redundancy troubles as many non-key attributes can be derived from partial key attribute. To change the relation into 2NF, let us use FDs. As per FD (1) the Enrolment number uniquely verify student name and address, so one relation should be:

STUDENT1 (Enrolmentno, Address, Sname)

Now as per FD (2) we can decompose the relation more, but what about the attribute 'Office'?

We find in FD (2) that Course code (Cno) attribute uniquely shows the name of instructor (refer to FD 2(a)). Also the FD (3) means that name of the instructor uniquely shows office number. This can be written as:

Cno        →             Instructor                  (2 (a)) (without Cname)

Instructor          →                                    Office            (3)

⇒      Cno      →                                      Office  (This is transitive dependency)

 

Therefore, FD (2) now can be rewritten as:

Cno                  →         Cname, Instructor, Office           (2')

This FD, now provides us the second decomposed relation:

COU_INST (Cno, Cname, Instruction, Office)

Therefore, the relation STUDENT has been decomposed into two relations:

STUDENT1 (Enrolmentno, Sname, Address) COU_INST (Cno, Cname, Instruction, Office)

Is the decomposition into 2NF finish now?

No, how would you join the two relations formed above any way? Please note we have super FDs as, because (Cno +Enrolmentno) is the primary key of the relation STUDENT:

Enrolmentno, Cno       →        ALL ATTRIBUTES

Every attributes except for the key attributes that are Cno and Enrolmentno, Though, are covered on the right side of the FDs (1) (2) and (3), therefore, making the FD as redundant. But in any situation we have to have a relation that connects the two decomposed relations. This relation would cover any attributes of Super FD that have not been covered by the key attributes and the decomposition. Therefore, we need to make a joining relation as:

COURSE_STUDENT (Enrolmentno, Cno)

 So, the relation STUDENT in 2NF form would be:

STUDENT1 (Enrolmentno, Sname, Address)                   2NF(a)

 COU_INST (Cno, Cname, Instruction, Office)                 2NF(b)

 COURSE_STUDENT (Enrolmentno, Cno)                         2NF(c)

Posted Date: 3/7/2013 1:59:22 AM | Location : United States







Related Discussions:- The second normal form (2nf), Assignment Help, Ask Question on The second normal form (2nf), Get Answer, Expert's Help, The second normal form (2nf) Discussions

Write discussion on The second normal form (2nf)
Your posts are moderated
Related Questions
Objectives differentiate DDBMS and conventional DBMS; Describe Network topology for DDBMS; Describe the concepts of horizontal and vertical fragmentation; Descr

Aggregation : One limitation of the E-R diagram is that they do not permit representation of relationships between relationships. In such a case the relationship along with its ent

Describe the types of attributes? Types of attributes: An attribute's category determines the type of values that are permitted in the attribute. For instance, the value vers

When are they useful? Justify with an example. The temporary tables are commonly used to support specialized rollups or exact application processing needs. Unlike a permanent t

(a) Differentiate between information and data . (b) List and briefly explain five potential benefits of the database approach compared to conventional file systems. (c) Diff

Haw does it work one database application

what do you understand by enhanced entity-relationship

Project Description: This is going to be a School portal to handle each activities in school such as, School fees payment, online registration and every other payment, course re

Explain nested relations? Nested relations The assumption of INF is a natural one in the bank examples we have considered. However, not all applications are best modelled

Q.   Why would you choose a database system instead of simply storing data in operating system file? Define  at a base concept in details. Sol. Advantages of Database over Fil