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.
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 |
SQL Server Mentalist 