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)

Which data type can store unstructured data, Which data type can store unst...

Which data type can store unstructured data? Raw data type can store unstructured data.

What are ways of finding out derived attributes, What are ways of finding o...

What are ways of finding out either an update is required or not for derived attributes? The ways to find out an update is required or, not are as: Explicit update

Strict 2pl-two phase locking (2pl), Strict 2PL :Though, this basic 2PL suff...

Strict 2PL :Though, this basic 2PL suffers from the trouble that it can result into loss of isolation / atomic property of transaction as theoretically speaking once a lock is rele

Create a database of a motor bike racing club, You have a friend that runs ...

You have a friend that runs a motor bike club that holds racing events at various venues on weekends, on a monthly basis. Some venues are private race tracks and some are dirt trac

What is odbc and what is its function, Question: (a) What is ODBC and w...

Question: (a) What is ODBC and what is its function? (b) What is Web application server, and how does it work from a database perspective? (c) What does e-commerce mean

Apriori algorithm-find all frequent large itemsets, Your friend owns a comp...

Your friend owns a computer store in Yuen Long, selling Desktop and Notebook PCs and other computer peripherals. Having been rather successful with his business there, he decided t

What is thread splitting, What is Thread splitting A thread of cont...

What is Thread splitting A thread of control is a path by which a set of state diagrams on that a single object is active at a time. Objects are shared between threads

Explain the ansi -sparc schema architecture, (a) Differentiate between info...

(a) Differentiate between information and data . (b) List and briefly explain five potential benefits of the database approach compared to conventional file systems. (c) Diff

Describe hashing in dbms, Describe Hashing in DBMS? Hashing: Hashing ...

Describe Hashing in DBMS? Hashing: Hashing is a technique to store data within an array so which storing, searching, inserting and deleting data is fast (in theory it's O(1))

Keys - relational database integrity, Candidate Key:  In a relation R, a ca...

Candidate Key:  In a relation R, a candidate key for R is a subset of the set of attributes of R, which have the differnt two properties: (1)        Uniqueness:                N

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