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
Need for Multiple Access Paths In practice, most of the online information systems need the support of multi-key files. For example, consider a banking database application hav

#floating point representation is used to store

i want to database for online assignment submission system

What are the functions of an accumulator? The accumulator is the register associated with the ALU operations and sometimes I/O operations. It is an integral part of ALU. It hol

Define database management system? Database management system (DBMS) is a set of interrelated data and a combination of programs to access those data.

Describe disk manager and their work? Disk Manager: Responsible for fetching data regulated through file Manager from disk storage within Main memory and deciding what data to

What is the max. no. Of structures that can be included in a table or structure. Nine.

Explain Authorization and Integrity Manager ? Authorization and Integrity Manager: Tests for the satisfaction of integrity constraints and checks the authority of users to acce

In problems 1 - 4, you are given the columns of a table, and a set of functional dependencies.  Determine the normal form of this table.  Remember that the normal form is the HIGHE

What is the way a particular application views the data from the database that the application uses? Sub schema is the way a particular application views the data from the data