IF I have multiple candidate keys which one is a primary key and justify your choice ??

Question

Given:

R = { Account , Analyst , Assets, Broker, Client, Commission, Company, Dividend, Exchange, Investment, Office, Profile, Return, Risk_profile, Stock, Volume}

and a set of functional dependencies:

F{fd1, fd2,fd3, fd4, fd5,fd6, fd7, fd8, fd9, fd10, fd11}

where:

fd1: Client -> Office
fd2: Stock -> Exchange, Dividend
fd3: Broker -> Profile
fd4: Company -> Stock
fd5: Client -> Risk_profile, Analyst
fd6: Analyst -> Broker
fd7: Stock, Broker -> Invenstment, Volume
fd8: Stock -> Company
fd9: Investment,Commission -> Return
fd10: Stock, Broker -> Client
fd11: Account -> Assests

these are candidate key(s) :

(Account, Commission,Analyst ,Company)
(Account, Commission,Analyst ,Stock)
(Account ,Commission,Broker ,Company)
(Account ,Commission,Broker ,Stock)
(Account ,Commission,Client, Company)
(Account ,Commission,Client ,Stock)

(Q) Select a primary key and justify your choice ?

I was select
(Account ,Commission,Broker ,Stock) as a primary key ???

I chose that because it has the most direct dependencies compared to other ones. e.g. more attributes are functionally dependent on this primary key.

please check if my answer is it true ? or Not

I'm waiting your answer asap

thank you

Answer 1

I would create a dummy unique id to identify the row and link it to other tables as I have had consistent bad experience with compound keys. A single id field just works a lot better.

I suspect they are just evil.

For all the relevant possible keys, i would recommend to create unique indexes, which would give the advantages (guaranteed uniqueness and fast retrieval) and none of the disadvantages (do not get me started).

I also suspect that the key fields proposed might change from time to time. You really want your key to be immutable, since it will be used as a reference.

Answer 2

I would recommend that you do you own homework. Justify your choice!?!? Come on! you might as well have said "please do my homework for me"

Answer 3

In logical database design, singling out a candidate key to be "primary" at the expense of all the others becoming "secondary" is a completely arbitrary and artificial choice.

That is why Date has ditched the notion of "primary key" over 15 years ago. Every key corresponds to a certain uniqueness rule, and no single key is "more unique" than any of the others. Period.

Database systems should never have been such that they force the database designer to make such insignificant choices. The reason that they do force the database designer to make such choices, is partly for historical reasons (times were when the primary/secondary distinction was believed to bear some relevance), and partly because the big dogs in dbms land are quite happy NOT improving their existing cashflow-generating systems to any relational extent.