Data Modeling constraint issue

Question

Can someone help me with this school question? I have this data model shown below:

here is the question:

You are modeling an emergency communication system used to contact teachers in case inclement weather emergencies cause delayed openings or closures of the school. You may reach teachers by phone and/or email. The school must have at least one means of contact (phone or email) for each teacher. Each teacher may have more than one of each contact type. Which type of entity would you use in conjunction with the TEACHER entity to model a teacher’s contact information?

You decide to model a CONTACT table with primary key of Teacher-ID and Contact-Info, which is the contact information itself. The school principal, a major stakeholder, tells you she wants to track the area code for each teacher’s phone number(s):

Will your original modeling concept work? If not, how would you change the model to reflect the principal’s request? How many entities would you need to represent the unique attributes of telephone numbers and email addresses so that no entity contained null values? The principal reminds you that every teacher must have at least one phone number, but having an email address is not a requirement. Where would you reflect the phone number constraint in your model?

Can someone explain how to enforce the constraint?

Answer 1

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
• Your 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:

• user
• domain

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.

Answer 2

In your screenshot, you have Phone_PhoneID as a field in your Teacher table. What is this field? If this field is supposed to map to the Phone table, then make PhoneID a Primary Key in the Phone table. Then make the PhoneID field in the Teacher table a Foreign Key that maps back to PhoneID from the Phone table.

I am not sure you need Teacher_TeacherID in the Phone table. If you add PhoneID to the Phone table and have it as a Foreign Key in the Teacher table, you shouldn't need the TeacherID field in the Phone table.

I am also not sure you need the TeacherID field in the Email table. Create an EmailID field as a Primary Key in the Email table, then create a Foreign Key in the Teacher table mapping back to EmailID in the Email table.

I think these steps will create the necessary constraints you are asking for.