The problem statement says:
You decide to model a CONTACT table with primary key of Teacher-ID and Contact-Info, which is the contact information itself.
Your solution doesn't do this, which is why your professor didn't like your answer, I suspect.
Your solution has a few other issues that may have affected the professor's assessment:
- You have abstracted out the email domain to a separate table. I'm guessing you did this because you thought that you were normalizing out the domain name. This is not really how normalization works. It's not about removing any data that appears to be repeated. It's about removing out data which is semantically identical so that when it changes you only have to change the data in one place. The only rational reason to "normalize" out the email domain is if you expect email domains to change and you want to get every email address with that domain updated in one operation. The chance of this happening realistically is vanishingly small, so it's not worthwhile to do.
- The problem statement states outright that the contact table key is a composite key which includes an element which is natural
- Your solution tries to address the requirement for a constraint that enforces the "at least one phone number" business rule by including a foreign key to phone in the
Teacher table, but this relationship is not reflected in the diagram with a relationship line.
- Your solution may or may not address the part of the problem that states your entities should
represent the unique attributes of telephone numbers and email addresses so that no entity contained null values
Phone table does not have a primary key indicated
I believe what you were expected to do was to propose an Entity Sub-type solution for this problem. Consider the following:
- You want a single
CONTACT table with a primary key consisting of a Teacher-ID and Contact-Info, which is the contact information, itself, as per the problem statement
- You want to represent the distinct attributes of email addresses and phone numbers
- You want to avoid nullable attributes
- You want to have a constraint which enforces the requirement for one (primary) phone number included in the model itself (as opposed to code)
The parts of a phone number (in USA and Canada) would include:
- Country Code (1)
- Area Code (000) (aka NPA)
- Exchange (000) (aka NXX)
- Subscriber (0000)
The parts of an email address are:
Note that email domain is a tricky one because the arbitrary constraint in the problem statement precludes dividing the email domain into subdomain (optional), domain name and TLD.
So taking all of this into account, you might have an ERD that looks something like this:
NOTE: This is not a good solution to a real-world problem, but it addresses the arbitrary requirements of the problem statement.
By using entity-subtyping, you can respect the problem constraint that says you have a CONTACT table with a compound key made up of the TeacherID and the contact information itself.
Furthermore, the two entity subtypes (PHONE_CONTACT) and (EMAIL_CONTACT) can each represent the parts of those types of contact methods without nullable fields. Note that in order to do that and also respect the foreign key needs of the subtypes you would want the whole contact information to be a derived column (made up of the individual parts) if your RDBMS has a feature which supports this, otherwise you would have to resort to redundant data, surrogate keys, violating the business rule that CONTACT includes the contact information itself in it's primary key, or a complex check constraint on each subtype.
The other issue with this solution for practical purposes is that it will require a complicated transaction to establish a new TEACHER record because the teacher and their primary phone number will all need to be established within a single unit of work and constraints may need to be relaxed inside that unit of work an re-imposed and checked at the end. Such is the way of canned problems with arbitrary rules. They are good for learning concepts but they don't always make for sensible, supportable business applications in the real world.