Your model includes referential cycles. This comes up quite a bit when you're modeling and you haven't worked through the differences between what relationships exist and what relationships are important to your system.
Without knowing more about your business rules and their justification, I would suggest that you consider dropping teachers.topic_id and possibly also teachers.subject_id.
Your lessons table relates subjects and topics. Since you have a many-to-many relationship between teachers and lessons, you can use lessons (via teachers_and_lessons) to tell you which teachers can teach which topics and which subjects.
I doubt that neither teachers.topic_id nor teachers.subject_id are correct anyway, since this would imply that any given teacher can only teach one topic and one subject. I don't know about your school, but it sounds much too specialized to me!
Edit: Recognizing Teaching Qualifications
Based on a comment by OP (below) the question is how can a teacher be recognized as being qualified to teach a topic without there being an actual lesson assignment made (yet)?
Issue 1: The relationship of topics to subjects:
It isn't clear from the original question whether topics belong to one subject or possibly to multiple subjects. If topics (e.g. fractions) belong to only one subject (e.g. math) then there should be a foreign key like so: topics.subject_id. If this were so, then lessons.subject_id would be redundant and could also be dropped. This would simplify the model and make things much clearer while reducing the risk of inconsistent foreign keys.
Issue 2: What teachers can do vs. what they are doing:
The teachers_and_lessons table shows which lessons teachers are actually teaching. Before this assignment is made, OP would like to be able to record which topics a teacher is qualified to teach. This could be done with a second M:N intersection, like so:
teacher_qualifications:
(teacher_id (PK/FK), topic_id (PK/FK))
This design assumes the simplification suggested under Issue 1, above. One potential drawback of this simplistic solution is that there is no constraint against assigning a teacher to teach a lesson for which they are unqualified. This constraint, if it is important, could be accommodated by replacing the teacher_qualifications and the teachers_and_lessons tables with new tables as follows:
teacher_qualifications:
(qualification_id (PK), teacher_id (FK,UN1), topic_id (FK,UN1))
lesson_instructor:
(qualification_id (PK,FK), lessons_id (PK,FK))
Again, these tables assume the simplification from Issue 1. Even without that simplification, these tables could be amended to use an analogous approach.
If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this:
By putting the TeamId in the People table you enforce the fact that a Person can only be on one team. You then have a many:many relationship using a cross join table between People and Roles. This allows a person to have multiple roles. By putting the email in the PeopleRole table you enforce that a person has to have a separate email for each role. You would also want to put a unique index on the PeopleRole.Email column to enforce that all email address have to be different.
The only thing I believe I'm missing is enforcing that a team can only have one leader and one contact. You might be able to do that by adding TeamId to the PeopleRole and then creating a filtered unique index on TeamId & RoleId WHERE the RoleIds are those that can only have one per team. I haven't tried it though.
Best Answer
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:
Teachertable, but this relationship is not reflected in the diagram with a relationship line.represent the unique attributes of telephone numbers and email addresses so that no entity contained null valuesPhonetable does not have a primary key indicatedI believe what you were expected to do was to propose an Entity Sub-type solution for this problem. Consider the following:
CONTACTtable with a primary key consisting of a Teacher-ID and Contact-Info, which is the contact information, itself, as per the problem statementThe parts of a phone number (in USA and Canada) would include:
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.