Martin,
Here's an example.
Assume the following rules;
1. You are creating a Project entity wehre you need to record a project, a member, and an advisor.
2. Each project has many members
3. a given person may be a member of many projects
4. each advisor works with only one member on any particular project
5. A project may have many advisors
6. An advisor may be associated with many projects
Given these rules a third normal form entity might be;
Entity Name: Project
Attribute: ProjectID
Attribute: MemberID
Attribute: AdvisorID
Now since any member can only be associate with a given project once, ProijectID + MemberID is a Candidate key.
And since an advisor-member combination can only occur once fror each project, then ProjectID + MemberID + AdvisorID is a candidate key.
Using the first candidate key (ProjectID + MemberID) we find that all three attributes are dependant on the entire PK (that is the combination of both the member and project). This is in 3rd NF.
Now checking the other candidate (all three attributes) we find that 3rd NF does not apply, in fact only two of the three are needed for the AdvisorID to be determined (a violation of 3rd NF). So this entity is not in BCNF.
BCNF would be the following design;
Entity Name: Advisor-Project
Attribute: AdvisorProjID (PK)
Attribute: ProjectID
Attribute: AdvisorID
Entity Name: Member-Advisor
Attribute: MemberAdvisorID (PK)
Attribute: MemberID
Attribute: AdvisorProjID (FK)
Here's a display of the problem with the first design, imagine the following data set;
ProjectID MemberID AdvisorID
101 Johnson Miller
101 Smith Brown
101 Stewart Miller
102 Johnson Brown
102 Frank Brown
102 Smith Andrews
The design problem is that as the number of project members grows the advisor-Project data, in the first design, must be duplicated for every member with a given advisor. In the second design the advisor-project data is stored only once, no matter how many members of any project work with a given advisor.
I hope this explains BCNF a little more clearly for you.
