In this case, the many to many relationships should be obvious, but I feel I must point out:
Based on that text specifically, all that's needed is that the patient and hospital each have a doctor ID column. How many would get it if you specifically pointed out that they need to consider real-world circumstances in this, and / or if you enumerated all the requirements?
Many many many times, when stating a hypothetical question, real-world handling responses are not desirable to the questioners. If the interviewee realizes this, they also realize that they risk losing points no matter how they answer: answering based on the specified requirements fails with you, but answering based on real-world uses fails with people testing against over-engineering / people religious about specific answers to specific questions. And being verbose risks both groups, based pretty much solely on the interviewer, of whom they have effectively no knowledge.
Or have you done this, and it just wasn't included in the example above?
There's no sure way to win with everyone, but usually you get more points by asking the interviewer what sort of answer he or she is looking for than by blindly making assumptions. Part of the point is to see whether people have good communication skills and can deal with ambiguity.
Certainly. And if that's part of the test, then by all means do that.
But if it's not, you might be artificially deflating some fantastic people because a) they guessed wrong, and b) you're not aware of your implicit expectations. Be aware, it's in your best interest in many many ways.
In what real-world instance/s would you prefer a doctor id column in Employee and Hospital tables to junction tables?
You'd either end up with the same employee or hospital records listed multiple times with different doctor ids (lots of repeated data), or with a field in each employee or hostpital record containing a list of doctor ids (searching by or joining on doctor id requires less simple SQL).
I would think the obvious answer to someone familiar with relational databases would be the OP's expected answer - normalize all three tables, then use junction tables to enable many-many relationships.
What you're missing is that the question-as-stated does not necessarily reflect the real world. The real world does need junction tables. A fully-correct answer to the question-as-stated does not. Change the nouns to anything you desire; the stated problem remains precisely the same, but for this interviewer the answer is different. But not for all interviewers - some do want answers off only what was stated, not off what baggage our language and culture applies to those words.
The question is an artificial, massively-simplified hypothetical problem. What was removed in simplification cannot be inferred with accuracy unless it is stated, and depending on the interviewer, there is always a risk that your answer, however accurate, may be "wrong". It's a guaranteed loss (statistically) for the person being interviewed, and thus most likely for the interviewer as well.
thank you, that's the first thing I thought. "doctors can have multiple patients, OK, so patient->doctor_id right ?"
the way interviewers flip between "real world answer" and "assume X, Y, Z, Q, P, R don't exist answer" drives me batty. Write a union of two sets ? Sure. x.union(y). Oh now its "hypothetical" time, right lets break out the pointers..
Ah, sorry, I left that part vague. I meant 3 tables, not just two. The point I was trying to make was that your correct answer required junction tables; other interviewers' correct answers require _only_ doctorID, because that's all your text required. And still other interviewers dock points for answering "cleverly" and covering both bases.
Without explicit context, how is the interviewee to know which the interviewer wants? They risk a lower review score no matter how they answer.
Asking the interviewer for more information? The ability to ask questions is far important than whether anyone can remember the details of database normal forms!
Based on that text specifically, all that's needed is that the patient and hospital each have a doctor ID column. How many would get it if you specifically pointed out that they need to consider real-world circumstances in this, and / or if you enumerated all the requirements?
Many many many times, when stating a hypothetical question, real-world handling responses are not desirable to the questioners. If the interviewee realizes this, they also realize that they risk losing points no matter how they answer: answering based on the specified requirements fails with you, but answering based on real-world uses fails with people testing against over-engineering / people religious about specific answers to specific questions. And being verbose risks both groups, based pretty much solely on the interviewer, of whom they have effectively no knowledge.
Or have you done this, and it just wasn't included in the example above?