You are modeling an emergency communication system used to contact teachers in c

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? Assignment: Include your models and your responses to the questions.

Explanation / Answer

The design proposed is correct but yet in order to provide the good design. So to fulfill this condition it is required to provide the primary key to both teacher_id and teacher contact number so you have to use composite key concept

It basically says that in the table there can be more than one primary key. as the teacher's contact number cannot be null and it should be unique so it should also be the primary key

CREATE TABLE SAMPLE_TABLE
(teacher_id integer,
teacher_contact_number varchar(30),
email varchar(50),
PRIMARY KEY (teacher_id, teacher_contact_number));

This perfectly describes the new model which is required

note as email is not required so no constraint is given if it would be required then not null and unique can be used according to the need

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.