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
Define the following constraints for the table client. (i) BAL_DUE must be at least 1000. (ii) NAME is a unique key. (i)ALTER TABLE CLIENT ADD CONSTRAINT CLIENT_BAL_DUE_C1

List the drawbacks of relational database system A) Repetition of data B) Inability to s certain information.

Benefits of Hashed file Organisation 1.      Insertion or search on hash-key is quick. 2.      Best if equality search is required on hash-key. Drawbacks of Hashed file

State about the Tables- DBMS Data is stored in columns and rows (much like a spreadsheet -main difference is how data is organised). Every row in a table is known as a record

What is a security? Securities are claims on financial assets.  They can be illustrated as "claim checks" which give their owners the right to receive funds in the future.  Sec

What is a cascading update? Referential integrity constraints needs that foreign key values in one table correspond to primary key values in another. If the value of the primar

Consider entity type Employee with attributes empNumber (this is unique), empName, and the year when he/she was hired. An employee can either be a technical employee, a marketing e

What are the DBMS languages? Explain.  Ans: a) Data Definition Languages (DDL): It is employed to specify database schema for example CREATE DROP statements etc.     b)  Dat

what benefit can a company gain by managing its own infrasture and services

The insertion of a tuple in the aim of reference does not cause any destruction. Though, insertion of a tuple in the relation in which, we has the foreign key, for instance, in ASS