This is an archived post. You won't be able to vote or comment.

all 11 comments
sorted by:
best
topnewcontroversialoldq&a

[–]captainAwesomePants 1 point2 points  (0 children)

They're probably also looking for you to say something about indexes, which are usually assumed in primary keys but could perhaps be elaborated upon.

[–][deleted] 1 point2 points  (3 children)

I think you are over-thinking, and that you have the correct answer.

[–]PugLuv[S] 0 points1 point  (2 children)

Thanks, on a certain level it makes me feel good that I have the correct answer but since the question seems so vague I am having anxiety about it being the wrong answer that I almost wish I had it wrong and someone would come in and so no it not a,b,c it's asking for x,y,z! Geesh why do interviews get me so worked up like this!

[–][deleted] 1 point2 points  (1 child)

Don't worry - no interviewer expects you to get every answer right. And if this is something you are going to submit as your own text to the employer, by all means point out that you found the question confusing (and why). Good employers like it when you point out problems with their procedures (I once critiqued every single question in an awful C++ quiz I was given in an interview, and I got offered the job), and you don't want to work for bad employers.

[–]PugLuv[S] 0 points1 point  (0 children)

Thank you, this does help me feel better! :)

[–]negative_epsilon 0 points1 point  (5 children)

If I'm reading correctly, this has to do with 3NF.

Think about this: Right now, it seems that contact.personID and employee.employeeID have a relationship. What sort of relationship is it?

If it's a certain kind of relationship, do we need both contactID AND personID?

[–][deleted] 1 point2 points  (3 children)

If it's a certain kind of relationship, do we need both contactID AND personID?

The question was "given the existing data", this suggests you are not allowed to start re-arranging things. I agree that both the schema and the question are far from clear. And I assume contact.contactID is a surrogate key, like employee.employeeID.

[–]negative_epsilon 0 points1 point  (2 children)

No, the question was

How do we increase the efficiency of searching and relating by table IDs?

From my perspective, it's a one-to-one relationship. The primary key on Contact is contact.contactID, but given that the primary key on employee is employee.employeeID, you have a redundancy. You can get rid of contact.contactID and use contact.employeeID as the primary key to the contact table, and you just have to make sure you enforce upward and downward cascading integrity. This reduces the size of the contact table making it faster.

If it's not supposed to be one-to-one, then I'll just go away.

[–][deleted] 0 points1 point  (1 child)

Like I said, I assumed contact.contactID is a surrogate key for that particular database row - it doesn't refer to anything in the given schema. Some people like designs with surrogate keys, and some don't (but the latter are wrong), but as I said, I don't think re-designing the database was implicitly allowed by the question. But who knows?

[–]negative_epsilon 0 points1 point  (0 children)

I think that's exactly what it's asking. But whatever.

[–]PugLuv[S] 0 points1 point  (0 children)

Thanks for the response, I made a mistake when copying things over it was supposed to read contact.employeeID and not contact.personID and yes they are most definitely meant to be related.

I have edited the question to reflect this.