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
Northwind Pty Ltd sells a variety of food products throughout the world.  They want to use the reporting features of SAP's Business Information Warehouse to better understand their

Example -Find the employee name, salary, department number and average salary of his/her department, for those employees whose salary is more than the average salary of that depart

What is trigger Triggers are statements that are implemented automatically by the system as the side effect of a change to the database.


We face problem of stock Opening balance and closing balance in Minus

An object-oriented design process  1. Define the context and modes 2. Designs the system architecture 3. Identifies the principal system objects 4. Identifies concurrency in

What is known as sequential file organization? In the sequential file organization, the records are kept in sequential order, according to the value of a "search key" of every

Define Join and Outer Join and differentiate between them.      Ans: Join - It generates all the combinations of tuples from two relations which satisfy a join condition.  Oute

What are the uses of functional dependencies? To test relations to see whether they are legal under a given set of functional dependencies. To state constraints on the set

Define Query Processing Query processing is the process of selecting the best plan or strategy to be employed in responding to a database request. The plan is then executed to