Conflicting Foreign Key constraints

Oh those elusive little foreign key errors! I ran into one of these a couple months back and decided to reproduce it today to explain how it occurs and what to watch for to avoid.

In my example I have created a simple MOVIES DB with SQL Server 2008 R2. I won’t bother showing the Create Table statements, as that part of the process ran without error. Assume at this point I’ve already successfully created the MOVIES DB with the following 7 tables as follows:

         clip_image001

I need to point out that there are constraints with foreign keys as follows:

· Shelf has a foreign key pointing to DVDCase

· Movie has foreign keys pointing to Shelf and Genre

· Filmography has foreign keys pointing to Actor and Movie

· Review has a foreign key pointing to Movie

Following are my SQL Insert commands to add rows of data to my tables.

USE MOVIES

GO

INSERT DVDCase (CaseID, CaseName)

VALUES (1, ‘Eastwood/Westerns’),

            (2, ‘Comedy/Romance’),

            (3, ‘Mystery’),

            (4, ‘Science Fiction’),

            (5, ‘Drama’)

INSERT Shelf (ShelfID, ShelfName, CaseID)

VALUES (1, ‘Romance’, 2),

            (2, ‘Mystery’, 3),

            (3, ‘Eastwood’, 1),

            (4, ‘Bogart/Bacall’, 3),

            (5, ‘Comedy’, 2),

            (6, ‘Wayne Westerns’, 1)

INSERT Movie (MovieID, Title, ShelfID, GenreID, MovieYear)

VALUES (1, ‘The Good, the Bad, and the Ugly’, 3, 3, ’1966′),

            (2, ‘Dirty Harry’, 3, 2, ’1971′),

            (3, ‘The Outlaw Josey Wales’, 3, 3, ’1976′),

            (4, ‘Sudden Impact’, 3, 2, ’1983′),

            (5, ‘Bridges of Madison County’, 3, 1, ’1995′),

            (6, ‘Unforgiven’, 3, 3, ’1992′),

            (7, ‘Gran Torino’, 3, 2, ’2008′)

INSERT Genre (GenreID, GenreTitle)

VALUES (1, ‘Romance’),

            (2, ‘Drama’),

            (3, ‘Western’),

            (4, ‘Science Fiction’),

            (5, ‘Mystery’)

INSERT Actor (ActorID, FName, LName)

VALUES (1, ‘Clint’, ‘Eastwood’),

            (2, ‘Burt’, ‘Reynolds’),

            (3, ‘Lee’, ‘Marvin’),

            (4, ‘Sally’, ‘Field’),

            (5, ‘Humphrey’, ‘Bogart’)

INSERT Filmography (ActorID, MovieID)

VALUES (1, 1),

            (1, 2),

            (1, 3),

            (1, 4),

            (1, 5),

            (1, 6),

            (1, 7)

INSERT Review (Comments, MovieID)

VALUES (‘Action packed for Dirty Harry lovers.’, 2),

            (‘Josey Wales is one of his best western heroes!’, 3),

            (‘Where it all started. Spaghetti western fans!’, 1),

            (‘Really enjoyed scenery.’, 3),

            (‘Little rough for youngsters, but good movie.’, 2),

            (‘This is the classic for the man with no name.’, 1)

The above looked good, but when executing against my MOVIES DB I received the following errors:

Msg 547, Level 16, State 0, Line 15  —-  The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Genre”.  —- The conflict occurred in database “MOVIES”, table “dbo.Genre”, column’GenreID’.  —-  The statement has been terminated.

Msg 547, Level 16, State 0, Line 36  —-  The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Movie”.  —- The conflict occurred in database “MOVIES”, table “dbo.Movie”, column ‘MovieID’.  —- The statement has been terminated.

Msg 547, Level 16, State 0, Line 44  —-  The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Movie2″ —- The conflict occurred in database “MOVIES”, table “dbo.Movie”, column ‘MovieID’.  —- The statement has been terminated.

I went back and forth attempting to build in my foreign keys within my CREATEs and within ALTER commands, but that had no bearing upon the errors. I looked at Line 15, 36, and 44 to see exactly where I was having the problem. The lines were the INSERT commands for Movie, Filmography, and Review tables. I’ll show these lines again below:

Line 15:    INSERT Movie (MovieID, Title, ShelfID, GenreID, MovieYear)

Line 36:    INSERT Filmography (ActorID, MovieID)

Line 44:    INSERT Review (Comments, MovieID)

The Confusion:

This confused me because I also had the Shelf table which also used a foreign key, but it was not getting the error. I finally stepped back and took stock of what the tables had in common. I was able to see that Filmography and Review both used MovieID as a foreign key to tie it back to Movie table. So this was the common thread. I then inspected Movie table attributes one by one. I was able to isolate the problem down to GenreID by process of elimination. What was wrong with GenreID?

The Problem:

The problem that I had all along was that I was trying to assign a GenreID value into my Movie table, but I had not yet loaded the Genre table! Look above at my SQL script and you will see that I’m trying to use GnereID as a foreign key in line 15 (the INSERT statement for Movie table) before I ever establish this attribute in the Genre table INSERT. What that did was blow up the load of the Movie table, and that in turn (the darn sneaky trickle down attack) corrupted my Filmography and Review tables, because they were in turn trying to load in MovieID from the Movie table which never got loaded.

The Solution:

Once this dawned on me and I moved my INSERT of the Genre table ahead of the Movie table, all was well. The Movie table then loads fine, as do the ones dependent upon it. I hope this may help someone who might run into this same problem. Be careful to establish values for your attributes in your tables before you use them as foreign keys in other tables. Again, simply putting my INSERT for Genre table in front of the INSERT for Movie table fixed all the errors.

LP

From Farenheit 451 to Intelligent Agents

What do you think, Linda?

If Linda thinks it’s all right, it must be.

Do you have the answer, Linda?

clip_image002

Absolutely.

clip_image004

Linda, you’re right, you’re absolutely fantastic.

A look at yesterday…

Remember Farenheit 451, written by Ray Bradbury in 1953, or the movie by the same title starring Julie Christie? The main character Montag has a wife that gets most of her reality out of her wall-screen TV.  Bradbury is giving us his predictive modeling on one aspect of Intelligent Agents (IA) before such existed. The movie shows us interactive TV where the actor states issues or problems, pauses, and turns to Linda (Julie Christie) to ask for her advice.  Her TV will flash a red light (shown above) while it beeps and the actor turns towards her to let her know they are waiting for her valued input.  What the movie was getting at is a type of interactive TV that would pull the viewer in completely.  Bradbury was concentrating on psychological aspects relating to mind control and a zombie like existence. The technological application of Intelligent Agents was not born yet. I’m not disagreeing with the message Bradbury presents, but he leaves off where all of the benefits of IA begins.

A look at today…

Los Angeles is looking into immersive interactive film making which will bring about virtual reality on demand.  It will get into more abilities than Bradbury had foreseen with abilities like being able to look around more than the camera would normally have provided.  We will be able to control where we look to some degree with a mouse, keyboard, or controller, for example.   There will be clickable content that can take us into the lives, history, or background of characters, or perhaps deeper into a scene when we want it to.  This is what I’m seeing anyways as coming on the horizon with the help of intelligent agents.  Pretty cool, and thanks to Ray Bradbury for giving us some early vision!

Last week I talked about the progression from Data to Information. This week I’m focusing on progression from Information to Intelligent Agents. A very basic definition of an intelligent agent would be a program that gets information or does an automatic scheduled service.  Here are some more advanced definitions:

“An intelligent agent is a software system that can send information to and receive it from other agents using appropriate protocols (sensing and communication).  Such intelligent agents learn multiple objectives, create action plans, process the information received, and perform reasoning through AI techniques.  To manage activities, an agent has a controller function. The controller manages the agent’s interaction with the environment and selects the task to be performed according to the agent’s goals and capabilities” (Akerkar & Sajja, 2009).    What is described here is a system where agents can carry out a process on information as well as dealing with objectives and plans, much of which can become dynamic and automated.

Another higher level definition is found here: “Intelligent agent is an abstract noun that can represent all intelligent entities whether they are of natural intelligence or artificial intelligence. It is therefore used to describe a wide range of entities, such as human beings, robots, intelligent devices, and intelligent software.  In a certain environment an intelligent agent can sense the environment through sensors and affect the environment through effectors” (Zhou, Wang, & Lou, 2010). This gives us the sense that we are talking both hardware and software solutions.  There is a wide variety of intelligent agent application. 

Intelligent Agents are used in a large variety of application areas, and one very successful one is in e-commerce.  “The internet has experienced a rapid shift from information and entertainment to electronic commerce.  The amount of information available on the web, as well as the number of e-businesses and web shoppers has been growing exponentially and the influx is difficult to process.  Intelligent agents empower both buyers and sellers to accomplish e-commerce transactions by enabling efficient, precise, and comprehensive searches on the vast web community and information repository.  By operating in the background in lieu of user intervention, intelligent agents also circumvent problems related to slow internet access and free up prohibitively expensive surf and data mining time” (Sinmao, 1999).   So we are seeing a strong trend towards utilization of IAs with e-commerce.  The advantages of remembering (learning) a user’s transactions, shopping cart, and even wish lists, is powerful for making the shopping experience a friendly efficient one.

If we take a look back about a decade ago, some views on IA were a bit progressive to say the least.  I thought this was kind of like reading George Orwell or the like, where we may need to wait a bit longer for some things to evolve.   We were making great strides ten years ago but not quite as fast as what CNET News portrayed as occurring “within a year or two”:  “Whether by voice…or by typing, I think agents are going to be helping more people use the Internet,” said Ted Kunzog, analyst and editor for Internet Stock News. “Within a year or two you will not need to use a computer to use the Internet. Instead people will be speaking into a phone or a wristwatch, and some little software product will bring back what they’re looking for. That’s the promise of agents” (Festa, 1999).  The timing may not be exact but we are indeed starting to get here!

A look at the future…

Looking into the future is always difficult to predict and often not quite what we anticipate.  We can, however, safely say that ES, ANN, IA, and all the technological means to automate, learn, and advance our processes will continue.   “Our future work will look at the exploration of how new technological paradigms will affect the perceived quality of experience in pervasive interactive multimedia systems. These paradigms include hybrid artifacts, use of biotechnology, advanced interaction modalities, new forms of content and novel intelligent environments, immersive environments such as collaborative virtual environments and multi-user environments.”  (Lumsden, 2008)

If you research the phrase “advanced interaction modalities” you will likely find advanced research going on in usability and interface design. One very positive example is CURE, the Center for Usability Research & Engineering. The ways in which this kind of work and research can help in the future is practically unlimited and full of potential. CURE is a leading European organization in usability engineering that is making inroads into this field of technology: “We study users to gain deep insight in how users can interact with advanced interaction spaces covering 3D spaces, augmented reality, mixed realities and virtual reality with careful consideration of the context of use. Advanced interaction techniques incorporating intelligent agents, information visualization (e.g. focus and context) and multimodal aspects are investigated. Interaction with ubiquitous and pervasive systems as well as ambient intelligence in the office and the home domain are a research priority that deals with the disappearing user interface.” (Cure)

Today’s topic briefly touched the surface of multimedia IA. I will return in later weeks to other IA issues that will show the positive forces of change that can make a difference in our world. Multimedia IA is not limited to the psychological vision of science fiction authors, and is certainly showing vast potential for good. As with all advancements, resulting positive or negative impact is influenced by humanity.

Ref:

Akerkar, R., & Sajja, P. (2009). Knowledge-Based Systems. Sudbury, MA: Jones & Bartlett Publishers .

CURE,http://www.cure.at/researchpriorities.

Festa, P. (1999, October 28). Intelligent Agent technology staging a comeback . Retrieved June 29, 2011, from CNET News: http://news.cnet.com/2100-1023-232110.html

Lumsden, J. (2008). Handbook of Research on User Interface Design and Evaluation for Mobile Technology. Hershey, PA: IGI Global.

Sinmao, M. (1999, November 24). Intelligent Agents & E-Commerce. Retrieved June 29, 2011, from CIS.UDEL.EDU: http://www.cis.udel.edu/~wchen/Sinmaom.htm

Zhou, Z., Wang, H., & Lou, P. (2010). Manufacturing Intelligence for Industrial Engineering. Hershey, PA: IGI Global .

KM

I concluded on June 18 that knowledge is something of value to someone somewhere. It takes on even more meaning when we discuss Knowledge Management. I’ll address some of the questions posed by “JK” on June 23rd.  Thanks JK.

From Data to Knowledge                             

When does data turn into knowledge?  It is the IT journey that starts with the raw data, gets processed into information, and is utilized as knowledge to address challenges or problems facing us.  When we talk knowledge management we are talking value and value comes in part from its relevancy to the entity using the knowledge.  This means that the information must be selective and therefore useful, and it also needs to be current or of a timeframe we need.  When data moves to knowledge, it becomes something that management can act upon and use in decision making.

If we look at KMS Cycle we see the following flow:

Raising Knowledge

Create & Store –>  Viability –> Availability 

This process is on-going and resembles a living process that grows and ages.  I guess we could think of our knowledge as a part of us or our organizations.  We can invest in it and care for it, or neglect it.   The amount of data is going to get unwieldy without KM process, and it can become out-of-date if not kept current.  

Origin of Knowledge 

Where does knowledge come from?  In the context of our Knowledge Management topic we are likely looking at system or human generated information, and often both.   JK, you mentioned research or experience, and I presume you had both in mind.  Both can input into this process and both become valuable pieces of the process. 

Intellectual Capital

“Collective knowledge (whether or not documented) of the individuals in an organization or society. This knowledge can be used to produce wealth, multiply output of physical assets, gain competitive advantage, and/or to enhance value of other types of capital.  Intellectual capital is now beginning to be classified as a true capital cost because (1) investment in (and replacement of) people tantamount to investment in machines and plants, and (2) expenses incurred in education and training (to maintain the shelf life of intellectual assets) are equivalent to depreciation costs of physical assets.” (Phoenix)

So we can see where value comes into play.  Knowledge coming from either technical sources or from human experience can amount to considerable value to the organization.   The question of cost vs. value is a good one.  Even if we are talking knowledge coming from the experiences of employees, it can often take planning to harvest it. 

Psychology of Knowledge

Not all employees will see the value in knowledge sharing and they might instead feel that knowledge they possess brings them a certain level of job stability.  This is commonly known and nothing new.  The challenge for competitive companies of today needing this knowledge is to find ways to bring employees on board with the proper mission and goals.  Perhaps incentives, perhaps education, perhaps realization of how this benefits the company they work for will help bring motivation to the knowledge sharing process. 

Accessibility – Availability – Viability

For Knowledge Management to flourish it must have not only the willing cooperation but the ability for employees to share.  This means providing an easy to access collaboration system, and giving them adequate time to perform needed sharing and documentation tasks.  There are risks that will always be present which the KMS can help with.  There will have to be support to foster knowledge sharing, and a system put into place for efficient collaboration.  As knowledge repositories grow they must be accessible and they must be kept current so they are meaningful and relevant to the needs of the organization.

LP

ref:

Phoenix, U. o. (n.d.). Intellectual Capital. Retrieved 25 2011, June, from BusinessDictionary.Com: http://www.businessdictionary.com/definition/intellectual-capital.html

From KM to Folksonomy

 

                                                

I do enjoy good folk music on a regular basis, but that is not quite what folksonomy is about.  Folksonomy applies to knowledge management and collaborative techniques.  Let’s explore an example:

Knowledge management (KM) is about getting knowledge where it needs to go, so collaboration is part of this process.  KM helps enable collaboration in an effort to help an organization reach objectives.   There are all kinds of ways that knowledge can move between individuals, groups, or companies.  In our world today we have many tools available to help get this job done.  Most of us have used blogs, Live Meeting, IM, SharePoint, and social networking.   Why is all this important to business today?

The marketplace today is one of fast moving competition.  Organizations need to learn quickly and be able to disseminate the right information to the right people at the right time.  We might call this Right Info3, but it already has an industry name called knowledge management (KM).  The idea here is that knowledge can be learned, stored and leveraged with the aim of using it to improve and gain competitive edge.            

There are many examples of KM successes. One that I read about was a school system in New South Wales, a suburb and urban center of Sydney, Australia.  The Catholic Education Office of Parramatta needed a way to centralize all documentation for its office, schools, and records for 41,000 students.  SharePoint 2010 was chosen and utilized what they call folksonomy functionality.  Here is a little bit on folksonomy: 

“Folksonomies are generally used to organize information and support information retrieval (IR). The public face of many folksonomies is often a tag cloud, with tags usually listed alphabetically and weighted by popularity.  Users might navigate the folksonomy by following tags they’ve used, related tags, popular tags or recent tags. In many cases folksonomies are also used to support search. A site with a huge database of photos like Flickr, for example, might rely almost entirely on a folksonomy for search since there is no full text to fall back on.”  (Morrison, 2007)

So just how does a Catholic school system in Australia use folksonomy, and how does it apply to KM?  Basically, the school staff and teachers at Parramatta can create their own classifications and tags making this a quick and efficient system.  An interesting part of their decision was based on the fact that they were split just about down the middle between PC and Mac technology and systems.  SharePoint 2010 with support for Safari browser was a viable solution for Mac as well.  As of late 2010 the first phase of a document management system rolled out.  It began providing benefits right away with rapid search ability and user friendly design.   “The managed metadata service is easy to use and allowed us to develop a structured hierarchy of documents based on existing classifications … staff can also configure particular parts of the knowledge management system to their own taste, skills and priorities…This helps to make the way people work more open and flexible, and means they are not restricted. It means that everyone can add material where they think it’s important.” (Microsoft, 2010)

I hope this helped to shed a bit of light on what folksonomy is, and how it relates to KM and not guitars.

 

Ref:

Morrison, P. J. (2007, November). Why Are They Tagging, and Why Do We Want Them To? . Retrieved June 16, 2011, from Special Section Bulletin: http://www.asis.org/Bulletin/Oct-07/Morrison_OctNov07.pdf

Microsoft. (2010, November 14). SharePoint 2010 Empowers Knowledge Management in Catholic Education System. Retrieved June 16, 2011, from Microsoft Case Studies: http://www.microsoft.com/casestudies/Microsoft-Sharepoint-Server/Catholic-Education-Office-Parramatta/SharePoint-2010-Empowers-Knowledge-Management-in-Catholic-Education-System/4000008687

Knowledge

                        

What do we define knowledge as?  This is a good starting place before we get into the topics of Knowledge Management and Collaborative Technologies. 

I feel knowledge is something that we know.  Maybe this is not completely correct either, as how do I know that I know something? In this sense knowledge could mean perception or belief.  There are many times in history when a belief was sold to many and changed events thereafter.  Knowledge is truth for the group that believes it to be the truth at the time.  It does not have to be knowledge of the truth as it can be knowledge of fraudulent assumptions.  It is concepts of anything really, as long as we believe them to be concepts.  Knowledge is something that is accepted as factual for us or for our point of reference in the world. 

OK, I have visited the thesaurus.  Knowledge is not just facts, but can also correlate to ability.  We often sell our ability in the job market based on what we know.  Knowledge can also come in the form of doctrine, dogma, education, facts, philosophy, science, and other more tangible and measurable definitions.   The last thing hitting me out of this thesaurus is wisdom and education.  I’m now seeing perhaps 3 types of knowledge: what we can offer, what we possess, and what we have learned.  These are all parts of the same thing which boil down to knowledge being something of value, at least to the business world.

I have honed my definition to be shorter and more generic: “Knowledge is something of value to someone at some time”.   My brother is perhaps the best authority in the world on Napoleon, but how do we assess value on this?  I’m not sure it has as much value these days at it once did, but this probably would apply to the mass and not the select.  The same may be said of studying the literary greats like Dickens, Tolstoy, Dostoevsky, Chaucer, or Milton … Knowles, Salinger,  Twain or Steinbeck.  How many of the younger generation are actively pursuing from this list?  The value may go up or down depending upon the group you are applying the question to.  Whatever our interests or beliefs, are they not of some value to us if  we hold them within our thoughts and memory?  The aspect of value may increase or decrease but it still is part of my definition.   So I again turn to my definition of knowledge as “something of value to someone at some time.”

FD, 3NF and BCNF

Let’s get a good yet simple definition or grasp on FD or functional dependency:

The definition of a functional dependency looks like A->B. In this case B is a single attribute but it can be as many attributes as required (for instance, X->J,K,L,M). In the functional dependency, the determinant (the left hand side of the -> sign) can determine the set of attributes on the right hand side of the -> sign. This basically means that A selects a particular value for B, and that A is unique. In the second example X is unique and selects a particular set of values for J,K,L, and M. It can also be said that B is functionally dependent on A. In addition, a particular value of A ALWAYS gives you a particular value for B, but not vice-versa.

ref: http://db.grussell.org/section008.html

This same reference above gives us example of 3NF:

3NF is an even stricter normal form and removes virtually all the redundant data :

  • A relation is in 3NF if, and only if, it is in 2NF and there are no transitive functional dependencies
  • Transitive functional dependencies arise:
  • when one non-key attribute is functionally dependent on another non-key attribute:
  • FD: non-key attribute -> non-key attribute
  • and when there is redundancy in the database

Let’s go to another section from Grussell for Boyce-Codd definition:

http://db.grussell.org/section009.html

  • When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF.
  • 3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys
  • i.e. composite candidate keys with at least one attribute in common.
  • BCNF is based on the concept of a determinant.
  • A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent.
  • A relation is in BCNF is, and only if, every determinant is a candidate key.

OK, let’s review what we have learned so far (from Grussell or other basic knowledge):

  1. BCNF is based on determinant.
  2. Determinant is a single or composite attribute where another attribute is dependent.
  3. BCNF is where every determinant is a Candidate Key.  In other words, we can’t remove any attribute from the determinant or it will lose its uniqueness.  It already is decomposed down as far as possible.

Pondering 3NF to BCNF

The table’s superkeys are:

  • S1 = {Court, Start Time}
  • S2 = {Court, End Time}
  • S3 = {Rate Type, Start Time}
  • S4 = {Rate Type, End Time}
  • S5 = {Court, Start Time, End Time}
  • S6 = {Rate Type, Start Time, End Time}
  • S7 = {Court, Rate Type, Start Time}
  • S8 = {Court, Rate Type, End Time}
  • ST = {Court, Rate Type, Start Time, End Time}, the trivial superkey

Good examples and all here from Wikipedia.  Allow me to ramble a bit on my findings.

A ⊂ B means A is a subset of B.

I’d like to add just a little bit more from the example:

“However, only S1 to S4 are candidate keys (that is, minimal Superkeys for that relation) because e.g. S1 ⊂ S5, so S5 cannot be a candidate key.

The table does not adhere to BCNF. This is because of the dependency Rate Type → Court, in which the determining attribute (Rate Type) is neither a candidate key nor a superset of a candidate key.”

Now, I hope I am getting this right from this example.  I just want to break this down a bit more, and forgive if this is old news.  It just has helped me when I started to see these…   

Start Time by itself, or End Time by itself can’t be considered Superkeys for this table.  The reason is as cited, that they are not unique. There are 2 courts, so there can be more than one start time or end time.  S1-S8 and the trivial Superkey are all Superkeys since they are unique.

Only S1-S4 are considered Candidate Keys because you can’t decompose these any further without breaking uniqueness.  For example, S3 is comprised of Rate Type and Start Time which can be used to discover what has been scheduled (since there is a different rate for either of the courts), but if you remove any part of this Candidate Key it will lose that qualification as well as being a Superkey.  It no longer will be unique.

What I’ve been leading up to is BCNF.   We will find that BCNF is based on the concept of a determinant.  I don’t really think this is the most important definition of BCNF, but I wanted to define what I found on it.  If you look up the definition of determinant you will find it simply means an attribute where another attribute is dependent. 

Definition: A determinant in a database table is any attribute that you can use to determine the values assigned to other attribute(s) in the same row.

ref: http://databases.about.com/cs/specificproducts/g/determinant.htm

All examples above from S1-S8 are in this category since each one of these can be used to determine the values in question. 

The most important definition to me on BCNF is that every determinant for BCNF has to be a Candidate Key.   The point here is that the table from our example is not in BCNF because it uses Court, Start Time, End Time, and Rate Type in this table.  As shown above, this can’t be a Candidate Key because it would not qualify to be a minimal Superkey.  In other words, we can remove an attribute or two and still have it be a determinant and a Superkey. 
What this means is that we would have to re-design our database in order to achieve BCNF.  If the design was decomposed down to Rate Types and Today’s Bookings (as shown in Wikipedia’s example), it would include the decomposed Candidate Keys and would achieve BCNF.  This would require additional Joins, and in some situations it might mean the loss of some dependencies.  I would put 3NF in my toolbox if I could only have one.  Having both BCNF and 3NF to choose from is of course the blessing, or perhaps the curse if I don’t keep my eye on all these issues (smile).  I don’t know if this helps or not, but I wanted to share the things I was looking at and pondering.