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
System level permissions : With the use of data dictionary you can view them.       Let us take the table name as user_sys_privs (used in oracle).       DESCRIBE USER_SYS_PRI

Explain the Cartesian product:  The Cartesian product operation does not need relations to union-compatible.  The meaning of this is that the involved relations may have dissim

Single-Valued Dependencies A database is a collection of related information and it is therefore inevitable that some items of information in the database would depend on some

Mention the type of constraints we can specify in the CREATE command of DDL. The type of constraints that can be specified are as follows:- PRIMARY KEY, NOT NULL, UNIQUE, FOR

(a)    Provide the description of the following ODMG schema in the Object Definition Language (ODL). (b)   Based on the above object database, write the following queries i

What is meant by heuristic optimisation?Discuss the major heuristics that are applied during query optimisation.

tell me the functionality,advantages and disadvantages of student database

What is a heterogeneous distributed database? In a heterogeneous distributed database, dissimilar sites may use different schemas, and dissimilar dbms s/w. The sites may not be

List the requirements needed to design a trigger. The requirements are:- Specifying when a trigger is to be implemented. State the actions to be taken when the trigger im

Explain Alternate key? Alternate Key - All the candidate keys except primary key are known as alternate keys.