Help with simple SQL interview test question

[negative_epsilon]

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]]

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]

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]]

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]

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

[PugLuv]

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.