SQL Server

Relational Database Management System (RDBMS) and Normalization Forms — Part 9

Third (3rd) Normal Form

The second normal form (2NF) tables or relation that was recognized in the preceding section signify an important enhancement over first normal form (1NF) relation or tables. But, the tables still suffer from the similar kinds of irregularities as the first normal form (1NF) relation or tables even though for various causes related with transitive dependencies. When a transitive (practical) dependency occurs in a relation or table, it means that two (2) distinct facts are signified in that table or relation, one (1) fact for every single practical dependency connecting a different left side. For an instance, if an individual remove any data from the database, which includes removing the appropriate rows or tuples from Department and Order_Details, an individual have the unexpected result of removing the association between Dept_ID, Dept_Name, and Dept_Address as well. If an individual can project table Department over Report_ID, Editor, and Dept_ID to form a relation or table Department1, as well as project Department over Dept_ID, Dept_Name to form table Department12, an individual can eradicate this problem. Example relation or tables for Department1 and Department12 are presented below:-

Report_ID

Editor

Dept_ID

5335

John

06

1259

Bella

07

Department1

Dept_ID

Dept_Name

06

Marketing

07

Technical

Department12

Author_ID

Author_Name

Author_Address

55

Carl

Street 1

32

Paul

Street 3

45

Fred

Street 5

65

Dave

Street 7

32

Paul

Street 3

55

Carl

Street 1

12

Mike

Street 2

Aurthor_Details

Report_ID

Author_ID

5335

55

5335

32

5335

45

1259

65

1259

32

1259

55

Order_Details

A table or relation is in third normal form (3NF) when every single non trivial practical dependency X -> Y, where X as well as Y is either simple or composite columns or attributes, one (1) of two (2) conditions should hold true. Either column or attribute X is a super key, and column or attribute Y is a member of a candidate key. If column or attribute Y is a member of a candidate key, Y is known as a prime column or attribute. A trivial practical dependency is of the form RZ -> Z. In the above instance, after projecting Aurthor_Details into Department1 and Department12 to remove the transitive dependency Report_ID -> Dept_ID -> Dept_Name an individual have the above mentioned third normal form (3NF) relations or tables as well as their practical dependencies

Department1: Report_ID -> Editor, Dept_ID

Department12: Dept_ID -> Dept_Name

Aurthor_Details: Author_ID -> Author_Name, Author_Address

Order_Details: Report_ID, Author_ID is a candidate key (no practical dependencies)

Boyce – Codd (BCNF) Normal Form

Third normal form (3NF) which removes maximum of the irregularities that are identified in databases and it is the most common standard for normalization in commercial databases as well as in CASE tools. A small number of left behind irregularities can be removed by means of the Boyce – Codd normal form (BCNF). Boyce – Codd normal form (BCNF) is considered to be a robust variation of third normal form (3NF).

A table or relation R is in Boyce – Codd normal form (BCNF) when for every non trivial practical dependencies X – > Y, X is a super key.

Boyce – Codd normal form (BCNF) is a robust form of normalization than third normal form (3NF) as it removes the second (2nd) condition for third normal form (3NF), which permits the right side of the practical dependencies to be a chief attribute or column. Hence, every single left side of a practical dependency in a table or relation should be a super key. Every single table or relation which is Boyce – Codd normal form (BCNF) is also third normal form (3NF), second normal form (2NF), as well as first normal form (1NF), as per the earlier descriptions.

The subsequent instance displays a third normal form (3NF) relation or table which is not Boyce – Codd normal form (BCNF). Such relations or tables have delete anomalies alike to those in the lower normal forms.

Assertion 1: For a given employee team, every single employee is directed through only one (1) team leader. But a particular team can be directed by more than one (1) team leader.

Employee_Name, Team_Name – > Leader_Name

Assertion 2: Every single leader directs only one (1) team.

Leader_Name – > Team_Name

This table is third normal form (3NF) with a composite candidate key Employee_ID, Team_ID:

Employee_Name

Team_Name

Leader_Name

Carl

Marketing

John

Paul

Technical

Bella

Fred

Outsourcing

Kimberly

Dave

Marketing

Trevor

Paul

Technical

Leonard

Carl

Technical

Leonard

Table – Team

The Team table or relation has the subsequent delete anomaly:

When Paul drops out of the Technical team, then an individual will have no record of Bella leading the Technical team. As presented by Date [1999], this kind of irregularity cannot have a lossless decomposition as well as reserve each practical dependency. A lossless decomposition necessitates that when an individual decompose the table or relation into two (2) smaller tables or relation by means of projecting the parent relation or table over two (2) overlapping subgroups of the scheme, the natural join of those subgroup tables or relation should result out in the parent relation or table without any additional unwanted rows or tuples. The simplest method to dodge the delete anomaly for this type of circumstances is to form an isolated table or relation for every single of the two (2) assertions. These two (2) tables or relation are in some measure repetitive, sufficient enough to dodge the delete anomaly. This decomposition is lossless which is trivially as well as conserves practical dependencies; on the other hand it cut down the update performance because of repetitive, as well as requires additional storage space. The interchange is often worth it as the delete anomaly is dodged.

In the upcoming part of this article we will be discussing Lossless – Join Decomposition and Dependency Protection Decomposition.

Facebooktwittergoogle_plusredditpinterestlinkedinmail