Results 1 to 2 of 2

Thread: How to Normalize A Table Which Has A Foreign Key

  1. #1
    Join Date
    Dec 2020
    Posts
    1

    How to Normalize A Table Which Has A Foreign Key

    I'm trying to normalize a table and I have some difficulties. In my table, I have 3 attributes which are

    User(Username, Password, Employee_ID)

    Now I'm trying to normalize this table. First as far as I know I need to determine the Functional Dependencies

    Username->Password,Employee_ID

    Employee_ID-> Username,Password

    So there are 2 candidate keys yet something does not fit. So here is my first question comes

    If we have a primary key in a table, shouldn't we directly say that that table is in the second form since a single attribute is a candidate key? Also if we have a primary key and a foreign key in a table, how do we normalize this table?

    I thought creating another table as a composite entity which includes Username and Employee_ID, would be logical but I'm not sure whether it is the solution or not.

    Edit: Also additionaly I need to determine the functional dependency yet here the only ones that I could find are the ones that I wrote above. So from here if this table is not in the second form also I need to demonstrate the partial dependency yet I cannot find that too.

  2. #2
    Join Date
    Dec 2020
    Posts
    3
    "First as far as I know I need to determine the Functional Dependencies".

    In fact the FD's should be ***given*** to you. It is formally impossible to infer the business meaning of the content if all you have is something like "User(Username, Password, Employee_ID)". You can ***guess*** at that meaning and you can even *guess right*** 99.99% of the times, but if you do that the results of your work still won't have more dependability than "based on guesswork". That thing "User(Username, Password, Employee_ID)" might for example represent the business meaning "The employee with ID <Employee_ID> cracked the password of user <Username>, and that password was <Password>". Now assuming for a moment that this is the real meaning, what on earth allows you to decide that

    Username->Password,Employee_ID

    ??? (That would correspond to a statement detailing that no user's password can ever be cracked by more than one cracker employee. Which is theoretical nonsense.)

    Also what on earth would allow you conclude that

    Employee_ID-> Username,Password

    ??? (That would correspond to a statement detailing that no employee can crack more than one other user's password. Which is just as much theoretical nonsense.)

    What on earth would allow you to decide even that

    Employee_ID, Username -> Password ?

    ??? If the database was intended to document all past password cracks, then it might perfectly be that some user U has his password P1 cracked by employee E, changes his password to P2 and later has that password too cracked by same employee E. So (U,E) does not determine any P. In fact there ***are no FD's at all*** if that's the particular business meaning of this schema and the relation schema is all-key.

    That said, now further supposing that this schema represents the "obvious" (VERY LOUD SCAREQUOTES !!!) meaning "The employee with ID <Employee_ID> has username <Username>, and has password <Password>" accompanied by the business case specifications that no employee can have >1 username and no username can be had by >1 employee. Then your set of applicable FD's seems correct and so is your conclusion that there are 2 candidate keys.

    But normalisation theory (and in fact the entire known body of relational database theory altogether) has no need what so ever, wherever, to make any distinction between "primary" keys and, eurhm, well, the others. As far as theory is concerned, all keys are equal and no key is more equal than any of the others, so to speak. The factors that determine a choice for this or that candidate key to be labeled "primary" are ad-hoc and arbitrary and merely psychological. So answering your first question, no, primary or not is not a determining factor while assessing the NF level of a relation schema.

    Something similar holds for your second question. The absence or presence of foreign keys does not influence the NF level of a given relation schema (plus set of applicable FD's). But I suspect you misunderstand what a "foreign key" is, precisely, so I suggest you look at the definition of the term and study it closely. A "second candidate key besides the primary one" is not the condition it takes to be labeled "foreign key". Foreign keys relate to what is known in normalization theory as ***inclusion dependencies*** (IND's for short). They ***always*** involve ***TWO*** relation schema's (even if in "edge" cases the two relation schema's are not required to be distinct !!!). There are no two schema's in your case here, so foreign keys do not enter the picture.

    That you cannot demonstrate the partial dependency (assuming all the "most plausible" assumptions that could be made in this case) is because there isn't any. In fact, this schema is in BCNF.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •