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
Hi I have got an assignment for making an er diagram for market management software can any budy help me?

Submission Requirements All answers must be computer generated (including text and diagrams). The hand-in version must include a header page (or with sufficient space)

Develop C# schoolproject ASAP Helping me with schoolproject. The project is prepared in C# and haves a database connection. We have some small issues asking up data from t

Transactions And Concurrency Management  Introduction One of the main benefits of storing data in an integrated repository or a database is to permit sharing of it betwee

Which two tools are used to administer users for Active Directory?

QUESTION (a) Consider a car database consisting of car model, car make, car origin, Engine capacity, color, cost.

Define NULL value NULL value NULL means something is unknown. It does NOT mean null (the digit 0). Null is as well employed as attribute value for a particular entity in which


What is called a query -execution engine? The query implementation engine takes a query evaluation plan, implements that plan, and returns the answers to the query.

What is Outer join?  They can be employed while we want to keep all the tuples in R, or all those in S or all those in both relations in the result of the JOIN in spite of of w