Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, Vishal Pawar > BISQL – Laymen to SQL Developer # 32 – Functional Dependencies and Normalization For Relational Databases #3 – Boyce Codd Normal Form (BCNF)

BISQL – Laymen to SQL Developer # 32 – Functional Dependencies and Normalization For Relational Databases #3 – Boyce Codd Normal Form (BCNF)

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files

>>Chapter 5 : Entity-Relationship Model

>>Chapter 6 : Relational Algebra

>>Chapter 7 : SQL

>>Chapter 8 : Functional Dependencies and  Normalization For Relational Databases<You are Here>

Continuing from my previous post on this series.

We are going to Cover the Following Points in this article

  • Boyce Codd Normal Form (BCNF)

Boyce Codd Normal Form (BCNF)

Database relations are designed so that they are neither partial dependencies nor transitive dependencies, because these types of dependencies result in update anomalies. functional dependency describes the relationship between attributes in a relation. For example, ‘A’ and ‘B’ are attributes in relation R. ‘B’ is functionally dependent on ‘A’ (A B) if each value of ‘A’ is associated with exactly one value of ‘B’.

The left_hand side and the right_hand side functional dependency are sometimes called the determinant and dependent respectively.

A relation is in BCNF if and only if every determinant is a Candidate key.

The difference between the third normal form and BCNF is that for a functional dependency A B, the third normal form allows this dependency in a relation if ‘B’ is a primary_key attribute and ‘A’ is not a Cndidate key.

Where as in BCNF. ‘A’ must be Candidate Key. Therefore BCNF is a stronger form of the third normal form.

PRODUCT (prd#,prdname,price)

Prd#->prodname,price

CUSTOMER (cust#,custname,custaddr) Cust#->custname,custaddr

ORDER (ord#,cust#mord#,qty,amt)

Ord#->qty,amt

The PRODUCT scheme is in BCNF. Since the prd# is a candidate key, similarly customer schema is also in BCNF.

The schema ORDER, however is not in BCNF, because ord# is not a super key for ORDER, i.e. we could have a pair of tuples representing a single ord#.

For e.g.

(1234,145,13,789) (1234,123,53,455)

here ord# is not a candidate key. However, the FD ord#->amt is not trivial; therefore ORDER does not satisfy the definition of CNF. It suffers from the problem of repetition of information. This redundancy can be eliminated by decomposing into ORDER1, ORDER2.

ORDER1(ord#,cust#)

ORDER2(prd#,qty,amt)

Example 2:

Consider for example LOTS relation. It has got a 5 functional dependency FD1 to FD4, Suppose we have thousands of lots in the relation but the lots are from only two countries: A and B. suppose lot size in country A is

0.5.0.6….1.0 acres, where as lot size in country B is restricted to

1.1.1.2…..2.0 acres. In such a situation we would have additional functional dependency FD5: area -> country_name. Here FD5 can be represented by

16 tuples in a separate relation R(Area,Country_name), since there are only

16 possible area values. This representation reduces the redundancy of repeating the same information in thousands of LOTS1A tuples.

clip_image002

Figure Boyce-Codd normal form (a) BCNF normalization of LOTS1A with the functional dependency FD2 being lost in the decomposition

(b) A schematic relation with FDS; it is in 3NF but not in BCNF

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: