The second normal form (2nf), Database Management System

Assignment Help:

The Second Normal Form (2NF)

Definition: A relation is in 2NF if it is in 1NF and each non-key attribute is fully dependent on each candidate key of the relation.

Some of the points that should be noted here are:

  • A relation having a one attribute key has to be in 2NF.
  • In the case of composite key, partial dependency on key that is part of the key is not allowed.
  • 2NF tries to make sure that information in one relation is about one thing
  • Non-key attributes are those that are not part of any candidate key.

Let us now reconsider, which describes the FDs of the relation to the relation STUDENT (Enrolmentno, Sname, Address, Cno, Cname, Instructor, Office). These FDs can also be written as:

Enrolmentno     →         Sname,           Address     (1)

Cno                 →           Cname,           Instructor  (2)

Instructor        →                                   Office        (3)

 

The key attributes of the relation are (Cno + Enrolmentno). Rest of the attributes are non-key attributes. For the 2NF decomposition, we are concerned with the FDs (1) and (2) as above as they relate to partial dependence on the key that is (Cno +Enrolmentno). As these dependencies illustrates that relation in not in 2NF and therefore suffer from all the three anomalies and redundancy troubles as many non-key attributes can be derived from partial key attribute. To change the relation into 2NF, let us use FDs. As per FD (1) the Enrolment number uniquely verify student name and address, so one relation should be:

STUDENT1 (Enrolmentno, Address, Sname)

Now as per FD (2) we can decompose the relation more, but what about the attribute 'Office'?

We find in FD (2) that Course code (Cno) attribute uniquely shows the name of instructor (refer to FD 2(a)). Also the FD (3) means that name of the instructor uniquely shows office number. This can be written as:

Cno        →             Instructor                  (2 (a)) (without Cname)

Instructor          →                                    Office            (3)

⇒      Cno      →                                      Office  (This is transitive dependency)

 

Therefore, FD (2) now can be rewritten as:

Cno                  →         Cname, Instructor, Office           (2')

This FD, now provides us the second decomposed relation:

COU_INST (Cno, Cname, Instruction, Office)

Therefore, the relation STUDENT has been decomposed into two relations:

STUDENT1 (Enrolmentno, Sname, Address) COU_INST (Cno, Cname, Instruction, Office)

Is the decomposition into 2NF finish now?

No, how would you join the two relations formed above any way? Please note we have super FDs as, because (Cno +Enrolmentno) is the primary key of the relation STUDENT:

Enrolmentno, Cno       →        ALL ATTRIBUTES

Every attributes except for the key attributes that are Cno and Enrolmentno, Though, are covered on the right side of the FDs (1) (2) and (3), therefore, making the FD as redundant. But in any situation we have to have a relation that connects the two decomposed relations. This relation would cover any attributes of Super FD that have not been covered by the key attributes and the decomposition. Therefore, we need to make a joining relation as:

COURSE_STUDENT (Enrolmentno, Cno)

 So, the relation STUDENT in 2NF form would be:

STUDENT1 (Enrolmentno, Sname, Address)                   2NF(a)

 COU_INST (Cno, Cname, Instruction, Office)                 2NF(b)

 COURSE_STUDENT (Enrolmentno, Cno)                         2NF(c)


Related Discussions:- The second normal form (2nf)

Improved performance, Improved performance: As the data is located near the...

Improved performance: As the data is located near the site of its demand, and given the inherent parallelism because of multiple copies, speed of database access may be good for di

Describe the development process of object oriented methods, Describe the d...

Describe the development process of Object oriented methods Object oriented methods have roots which are strongly anchored back in the 60s, functional and structured methods we

Data warehousing and business intelligence, I need help with data warehousi...

I need help with data warehousing and business intelligence course project about designing a data warehouse for a jewelry company that does not have one

DBMS, to develop elrctronic database from manual forms

to develop elrctronic database from manual forms

Shadow paging, Describe the shadow paging technique.

Describe the shadow paging technique.

Explain transaction, What is transaction? Collections of operations tha...

What is transaction? Collections of operations that form a single logical unit of work are known as transactions.

The result of the union operation between r1 and r2, The result of the UNIO...

The result of the UNION operation between R1 and R2 is a relation that includes? The result of the operation between R1 and R2 includes all the tuples of R1 and R2 which have c

Entity - relation modeling and transformation, Represent the following scen...

Represent the following scenarios with E-R diagrams drawn using the ER Assistant software. The diagrams should indicate cardinality and optionality of relationships. Transform each

Illustrate the definition of the classes and operation, The definition of t...

The definition of the classes and operation can often be adjusted to increase the amount of inheritance". Justify the above statement.   Adjustment of Inheritance The def

Developer competent in data cleansing-data scrubbing, Data Scrubbing 70,000...

Data Scrubbing 70,000 records Project Description: We need services of a developer competent in data cleansing/Data scrubbing. Scope: - 70,000 records in CSV format.

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd