The third normal form (3nf), Database Management System

The Third Normal Form (3NF)

Even though, transforming a relation that is not in 2NF into a number of relations that are in 2NF take out many of the anomalies, it does not necessarily take out all anomalies. Therefore, further Normalisation is sometimes required to ensure further removal of anomalies. These anomalies occur because a 2NF relation may have attributes that are not directly linked to the candidate keys of the relation.

Definition: A relation is in third normal form, if it is in 2NF and each non-key attribute of the relation is non-transitively dependent on every candidate key of the relation.

But what is non-transitive dependence?

Let A, B and C be 3 attributes of a relation R such that A Æ B and B Æ C. By these FDs, we might be derives A Æ C. This dependence A Æ C is transitive.

Now, let us reconsider the relation 2NF (b)

COU_INST (Cno, Cname, Instruction, Office)

Suppose that Cname is not original and thus Cno is the only candidate key. The following functional dependencies exists

 

Cno       →       Instructor            (2 (a))

Instructor             →          Office    (3)

Cno        →            Office    (This is transitive dependency)

 

We had derived Cno  → Office from the functional dependencies 2(a) and (3) for decomposition to 2NF. The relation is though not in 3NF since the attribute 'Office' is not directly dependent on attribute 'Cno' but is transitively dependent on it and should, thus, be decomposed as it has all the anomalies. The primary complexity in the relation above is that an instructor may be responsible for various subjects, needs one tuple for each course. Thus, his/her office number will be repeated in each tuple. This leads to all the troubles such as update, insertion, and deletion anomalies. To overcome these problems we require to decompose the relation 2NF(b) into the following two relations:

COURSE (Cno, Cname, Instructor) INST (Instructor, Office)

Please note these two relations and 2NF (a) and 2NF (c) are already in 3NF. Therefore, the relation STUDENT in 3 NF would be:

STUDENT1 (Enrolmentno, Sname, Address) COURSE (Cno, Cname, Instructor)

INST (Instructor, Office) COURSE_STUDENT (Enrolmentno, Cno)

The 3NF is generally quite adequate for most relational database designs. There are though some cases where a relation may be in 3 NF, but have the anomalies.

For example, let consider the relation NEWSTUDENT (Enrolmentno, Sno, Sname, Cno, Cname) having the set of FDs:

 

 Enrolmentno     →         Sname

Sname     →            Enrolmentno

Cno        →         Cname

Cname  →         Cno

The relation is in 3NF. Why? the functional diagram for this relation given in Figure please refer this.

                  1434_The third normal form (3nf).png

 

                                                                  Key attributes

                                  Figure: Functional Diagram for NEWSTUDENT relation

 Every attributes of this relation are part of candidate keys, but have dependency among the non-overlapping portions of overlapping candidate keys. Therefore, the 3NF may not eliminate all the inconsistencies and redundancies. Therefore, there is a need of further Normalisation using the BCNF.

Posted Date: 3/7/2013 2:00:49 AM | Location : United States







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

Write discussion on The third normal form (3nf)
Your posts are moderated
Related Questions
3NF Normal Form A relation schema R is in 3NF if it is in 2NF and for each FD X A either of the following is true X is a Super-key of R. A is a prime attribute of R

FDs in Entities : Student entity :                                                Enrolment number →  Student name, Address Course Entity :

#question. Draw a picture of a moving source and the waves surrounding it according to what you observed in this experiment. How does the spacing of the wave-fronts in front of the


What is called mirroring? The simplest approach to introducing redundancy is to duplicate each disk. This method is called mirroring or shadowing.


Define physical and logical data independence. How does this architecture help in achieving these? Data independence entails that change in one view must not need a change in t

Convert the following ER - diagram (Entity Relationship Diagram) into a relational database (the primary keys are underlined):       Ans:  The relational database schema f

i have assignments I don''t have time to finish because of work and other assignments, can you help with this? All are from the book Darabase design, application development and a

what is meant semantic query optimization? how does it differ from other query optimization techniques?