0

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?

enter image description here

  • If this is a homework question (seems like it) - it would help if you explain more of what you tried or understand so far. We're not here to do your homework for you :) – LowlyDBA Jul 7 '16 at 21:32
  • I actually made that model and already turned it in, I got that problem wrong so i am trying to fully understand what i could do better. I have edited the question to include the full question. – SchoolQuestions Jul 8 '16 at 21:13
0

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.

  • >> Each teacher may have more than one of each contact type - This requires a one-to-many relationship, so having the Teacher_TeacherID in the phone table is correct. – kiwiron Jul 9 '16 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.