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
What is a Database? To know what database is, we have to start from data, which is the basic building block of any DBMS. Data: Facts, figures, statistics etc. having no pa

what is RDBMS? how it works? good example.

What are composite objects? Objects that have other objects are known as complex objects or composite objects.

explain exhaustively the problems associated with ER diagrams. include illustrations in your answer

Describe the nested-loop join and block-nested loop join.   Ans:   The block nested- loop join algorithm is as described below:     for every block B r of relation R do beg

what is the relationship between receipt and owner entities in computer sales and service company

real life situations where database management system are most suitable

Many-to-many : Entities in A and B are related with any number of entities from each other. Example: Taught_by Relationship among course and faculty. One faculty member ca

The following relational database schema represents part of a simplified database used by a library: tblBook (BookID, Title, PublisherID, Category,Price) tblPublisher (Publishe

Domain Constraints Domain constraints are primarily formed for defining the logically correct values for an attribute of a relation. The relation allows attributes of a relatio