Jim, new day, new rounds. Yesterday it was 01:00 am for me, and when I woke up this morning I realized that I didn't even answer upon your "customer" like I intended (without knowing what you'd come up with). That's why your reply now is as it is. So please allow me to first give the appropriate answer upon "customer". I pasted in your original text :

--------------------------
>Ok the design for a database requires that all tables have a non-meaningful Primary key (Surrogate PK). Below is the design of the customer table;
>
>CustID I PK
>CustNo C(10) CK
>Name C(35)
>...
>
>Virtually every table in this database will have a PK and some other cobination of fields that could be the PK (defined as CK).
>
>You are correct in that BCNF to fix a violation will require that certain CKs be removed to new entities, however it is BCNF does not disallow the existance of a CK.
-------------------------

So jim,

All starts with your first line : "the design for a database requires that all tables have a non-meaningful Primary key (Surrogate PK)"

I know I will be very challenging by saying that this rule somehow came into our IT-lives, I missed where it was, and I certainly don't agree at all. Please note that I am NOT saying that you are wrong on this, but I do say it is bad for logical design, and even worse for physical implementation.
And ... yesterday I didn't read your above quoted line carefully, because of the coincidence of the example you came up with. About this first :

Talking about phenomena like Customer (which is in fact a "Relation" -> I hope this is right English, but I mean a Relation could be a Customer, Supplier, Debtor, Creditor, Person, all 1:1 to eachother) : Phenomena like this are potential for having a generated key, though the user can have rules in order to "create" them himself. Thus, you as the Relation (= Person) might be BOOTH01 or BOOTHCONN01 or whatever. I think you know what I mean when I'd present 999 other entities like Item, Language etc., which DO have a formal (meaningful) key intrinsicly. That's why your example is more difficult compared to the subject here. So, with the example of Item (product), according to the non-meningful key, you'd propose Item_ID = PK and ItemNumber is CK. Right ?

Well, since we are talking logical level here, there is no way we both can deal with this, since all entities will have CK's. But, I'd say that it is not allowed to bring up the generated key here, because it's a technical thing, and not a logical thing.
Now back to my answer from yesterday, your customer sadly needs the generated key (well, formally it does), because there would be really nothing else for the key. Now, your CustID and CustNo already BOTH are generated keys. I mean, why to have CustID if already CustNO should be generated, because of the formally not present key (with this I mean : from the Customer's attribute nothing suits at the logical level to say "this is the key", unlike Item which has ItemNo for key obviously).

Supposed I'm right in the above, you'd have CustNo and Name (and more) attributes. Now again this is not right, because CustNo was already created by the designer, and "in real life" it just isn't there. So what's left is Name and the other attributes, let's say Street, Streetnumber and City (never mind these being an Address-entity really). Now what BNCF tries to do, is forming the key out of those, which just really can't be done because in the end nothing is unique. Note that it lookse like being unique, but it isn't because at one adress two customers (companies) with the same name could reside (stupid but formally this is true).

From this may follow some rule that a generated key will be needed, and sadly (to the example) for Customer this is true, and here's our CustNo again. Now it is normalized and already in NF1 or so (again, never mind the adress being an entity really) (but see later).

Now the above may lead to our adaption that we just act like this for all entities in our world, but though this technically can be done, it is stupid. I.e., it is just not necessary and by having generated keys all over the place there will be not much left from readability, understanding and all. I think you know too, that when an SQL diagram is drawn from a DB setup like this, there won't be any logic in there, and checks upon the logic can't be done at all; PK's and FK's are mixed up unless the generated keys are held elsewhere as the FK's which even can't be done. The example is the relator with it's two logical key-parts, referring to PK's elsewhere, but both being in an other table. Thus, the generated key in the relator never can be related to the originating tables by a diagram. Right ? You'd just have to know.

I know, the generated keys for general rule is proposed to have easier maintenance, and well, a certainly disagree with that. I think it goes too far to debate that here, so I let that rest.
The point is, there is no reason at all to have all keys generated, where the logic tells there's just a key available in one of the attributes.

Now to conclude the Customer, it needs a generated key (better : "invented" key because the user may come up with BOOTH01 and 00001, 00002, 00003 is just not necessary), and this is just normalized immedeately (but still see later).

BTW, you may come up with the reason for generated keys because the relational model (DBMS) doesn't allow for changing keys (which I heard / read), but this is stupid, because it is just not allowed to change keys at all (IMHO). Just think of ERP (as an app with very many relations all over the place) and "just" change an Item number, and the whole company will be in deep stress. The changeing of a key is just at the logical level, and doing it implies a lot to be done in the complete organization. Hard to explain in brief, but let's say it's may experience. However ...

Keys just DO change in real life though it is very very hard to find the example in real life, and in fact I don't have any at the logical level;
Will an ItemNo change ? well, many companies say yes, but in fact one No becomes obsolete and another starts living (see below further);
Will a CustID change ? heheh, yes it will looking at HEART01 and I change my company's name into IT-Company. So the customer is really potential for changing the key ? oh no, because we invented the ID where it should have been a generated key. Just think of this carefully.

Quite another thing is, that in some cases it is not practical not being able to change keys, which already may be derived from typos (in the ID) and the entity started living already. Or, the really generated key for Customer is not that handy, so the invented key is used again. Now with the example of BOOTHCONN01, Jim Booth just has to move to another city and the trouble is there.

From the latter may follow that the (design stage of the) app has to recognize this, and over the invented key has to be a generated key. And hey, we are back to the JB solution (CustID, CustNo, Name, ...). But are we ?
Sure not, because the CustNo from my example is the BOOTHCONN01 and nothing defines that it is unique and therefor it is no CK at all. Worse is, that we not even have the opportunity to have it remained unique, unless another index is on CustNo (and such things will be applied).
Fact is, that for the proper solution to Customer, indeed it will be your proposed entity Jim. And that's why your example is so lousy, and it's about the only one where this applies to.
I note that you could have come up with this one just because it is THE example for the CK thing, but I don't think so (look at your applied rule on having non-meaningful keys).

Note : in our app all keys CAN be changed indeed, by means of a technical solution which just scans the complete database for the key to change. This is transparent to the developer, and the user may change any key to any entity by means of entering the attribute-name (which must be key somewhere), the old key-value and the new key-value. The app finds out the rest.
I only want to say, this is OUR technical solution, which for us works much much better than having a database without logic (see earlier).

More comments below ...

>Peter,
>
>> Not that the remainder atributes are Candidate Keys; They just are not, because they won't be unique anyhow. Right ?

This was my far too fast response without further explanation (now in the above).


>
>A candidate key is any attribute or group of attributes that can be used to uniquely identify an instance of the entity. In my example both the CustID (PK) and the CustNum (CK) will be unique for every customer record. They must be because the business rules state that they must be unique. Because they must be unique they are both "Candidates" for being the primary key, candidate keys. We choose one of them to work as the primary key thus relegating the other to being a candidate or alternate key.

The theory put like this, makes you right, but I state it never happens but for the "Relation" (Customer) example. Additional : I would say that in each other situation when there are two logical keys, one of them just is not, or all is wrong from the beginning. The fun is, that all examples to BCNF (let's say that the CK subject is derived from that) I have seen, all were completely wrong and not properly normalized from the beginning. Thus, when I normalized the examples properly, BCNF wasn't reached anymore. Jim, I hoped that you came up with such an example, but "sadly" you did not, and instead you came up with the one and only example it could apply. But don't hasitate to come up with another one.
BTW, please note that you can shoor me all over the place, because where I (or you) propose to have the subject around BCNF where in the end it is not, many lines could be interpreted wrongly. I hope to have focussed to the subject by means of the beginning of this Message.

Further remember that I already estimated that you could outsmart me, and I still think you can. For this matter : all what I say was "invented" by me only and in fact I go against the appreciated rules. This is ignorant, but I have my reasons too : better normalization.

To keep you awake, just think of two of my golden rules that imply almost all NF's at once :

1. Never add more than one logical key-field at the time when designing a new entity, unless the new entity is a relator. When you find yourself doing this anyway, something is wrong i.e. somewhere another entity is missing.
2. Never have more than two logical key-fields in a relator. When you find ... same.

This just always applies.
BTW, a "logical key-field" is allowed to be dateField+timeField.



>
>>Sadly your example just doesn't allow to remove the CKs to new entities (as you state yourself).
>
>That is because they are, in fact, CKs. If they could be removed to another entity it would be because they violate some other NF. For example;


(before I didn't interpret the example properly, so your comment is right ;)

>
>Three entities Projects PK PN, members PK MN, adn Advisors PK AN. Rules;
>
>1. projects may have many members
>2. members may have many advisors
>3. Projects may have many advisors
>4. a member on a Project has only one advisor


Yeah, these are your rules i.e. it defines the systems boundaries. Now no matter what you are going to say below (didn't read that yet), nr.4 is wrong. Yes, it is the rule, but the rule is not from real life, and will therefor not be applied by me like this. Please note that overhere we won't listen to our customer's (implied) boundaries, and we just do it right. So, a Member on a Project can have more Advisors. THIS is real life, but ... "just" doing it would extend the boundaries of the asked for system indeed, so it'd be wrong;
Instead, here some NF according the time-aspect should be applied, and of course you will agree that in time the Advisor may change or temporarily replaced etc. So no matter what this is N:N too in my model, and now I expect to undermine your expressions below (not knowing what thet are yet ;)


>
>Initaial design to track projects and their advisors and members
>
>PN (PK part 1)
>MN (PK part 2)
>AN
>
>This design is in 3rd NF because AN is dependent on the complete PK (the combination of PN and MN)

Sadly I'm not sure how to read this, i.e. what entity is this ? I'm in lack of the logical entry-point or so.
From my own rule "not add more than one key-field at a time" the above should be a relator. So we have Projects and Members, and your above will be the relator between them. Let's name it MembersOfProjects;

Now I feel some strangeness in the role of the Advisor, because "he" can advise Projects and he can advise Members, and I'm pretty sure if we had one entity only for Advisor, it wouldn't be normalized. I mean, "my" real life tells me that the Advisor for Projects needs some other attributes than the Advisor for Members. In the end this is not important, when the lot is approached like this (as I usually do) :

I'm pretty sute that our app (and the logic) will benefit from entities like AdvisorForProject and AdvisorForMember. So, relators again (between which entities is obvious);

From this follows by nature that an Advisor is able to serve more Members, and this is what my real life told me already (see earlier). But, since I applied some time-related (nor formally existing) NF, the date/time boundaries (as one logical keyfield) will be in the key to AdvisorForMember.

Okay, next step is, that nothing from the system's description tells me that the AdvisorForMember should be the same as AdvisorForProject (= the related according) Project), and in fact this is a point where I should ask the customer some questions. For now I assume the answer is "indeed, the Advisor of the according Project can be another";

Now all is related to echother, this comes to :

Project:AdvisorForProject:Advisor = 1:N:1
Member:AdvisorForMember:Advisor = 1:N:1 but, "one at a time" solved by processing.

What's still missing is Project:Member, being N:N, thus needing a relator again : MemberInProject. Thus :

Project:MemberInProject:Member = 1:N:1

Now draw this (I just did) and all IMO looks right, and all can be done with it;
My mentioned possibly not normalized Advisor is automatically solved by the relators AdvisorForProject and AdvisorForMember, and the (possibly) attributes not relating to Advisor only, can be hold in the according relators.

Now where did I meet our friend Boyce ?

Note : Below you state (I see now) that a member can be "on the project once"; I interpret this as that the member cannot join a Project several times or so; IMO this is solved in my model by nature (because of the key). In addition I'd say that this system encourages for more than the customer (you) told be, and the definition of "Project" implies more - time-related aspects (a Project runs in a period of time, is due to "some" other thing (who is the project for etc.); I only want to say : when this is applied (like real life will need) the model will be better again, and your "a member can be in the Project once" will be solved by "better nature" than right now.
Note too that my model allows a Member to be in several Projects, and when this system goes life it is out of control without the further needed time-aspects. Think of biling (or any stuff which legitimates the time spent), and again this will be solved by nature. I mean, the Member can be in more Projects, but formally can't be working on two Projects at the same time. Now I could blablabla some further, because in real life this CAN happen, thinking of mixing the one can of paint, due to the painting of two walls from two different "Projects".
The time-aspect is important here, and I use some "invented" NF's for it.

But anyway, I didn't meet BCNF here, and that is what I stated. Right ?
Or maybe I implied it somewhere underway. Just don't know that.
So, I did not much more than applying my two main rules, but nr.2 was not even encountered. I'm sure I'd have to apply it when was stated that the AdvisorForProject and AdvisorForMember MUST be the same in the Project/Member/Advisor relation(s); But would get connected, and my rule "have two PK-fields in the relator only would have implied some new entity (enitities ?). Well, that's what I expect.
Note that similar will happen when Advisor and Member are normalized properly, because right now they are not; bot will relate to "Person" or so, and supposed this one would be there, to my rules it will NOT be allowed to just join in another FK (PersonID) into f.e. AdvisorForProject.


Jim, I think this is some more-often used example, and I wonder how this is "usually" solved, and how my model differs; When I interpreted my customer (you) right, the model should be okay.
Just think of what would have come of my reasoning this out when I started thinking of generated keys; all (IMO) would have been possible, and the logic wouldn't be there, and we wouldn't have been able to communicate to eachother over this. But again, talking generated keys at the logical level is not legitimate, not matter whether they are applied or not.

Regards,
Peter

>
>The combination of AN + MN is also unique by definition since a member can only be on the project once and that member has a single advisor. This means that the following is also a possible design;
>
>MN (PK Part 1)
>AN PK (Part 2)
>PN
>
>However, in this situation the PN is NOT dependent on the entire PK but on only a subset of the PK causing this design to NOT be in 3rd NF.
>
>This does not mean that BCNF prcludes the existance of valid and necessary CKs but rather that if there are any CKs one must apply the BCNF to flesh out problems in the design.