Data Modeling with the Entity-Relationship Model - Database Processing: Fundamentals, Design, and Implementation - page 227

a. Draw an E-R data model for the Highline University Mentor Program Information
System (MPIS). Use the IE Crow's Foot E-R model for your E-R diagrams. Justify the
decisions you make regarding minimum and maximum cardinality.
Your model should track students, advisers, and mentors. Additionally, Highline
University needs to track alumni because the program administrators view alumni as
potential mentors.
1. Create separate entities for students, alumni, faculty advisers, and mentors.
● At Highline University, all students are required to live on campus and are assigned
Highline University ID numbers and e-mail accounts in the format FirstName
[e-mail] . The student entity should track student last name,
student first name, student University ID number, student e-mail address,
dorm name, dorm room number, and dorm phone number.

● At Highline University, all faculty advisers have on-campus offices and are assigned

Highline University ID numbers and e-mail accounts in the format FirstName.
[e-mail] . The faculty entity should track faculty last name, faculty first
name, faculty University ID number, faculty e-mail address, department, office
building name, office building room number, and office phone number.

● Highline University alumni live off campus and were previously assigned High-

line University ID numbers. Alumni have private e-mail accounts in the format
[e-mail] . The alumni entity should track alumnus
last name, alumnus first name, alumnus former student number, e-mail address,
home address, home city, home state, home ZIP code, and phone number.

Highline University mentors work for companies and use their company
address, phone, and e-mail address for contact information. They do not
have Highline University ID numbers as mentors. E-mail addresses are in the
format [e-mail] . The mentor entity should
track mentor last name, mentor first name, mentor e-mail address, company
name, company address, company city, company state, company ZIP code,
and company phone number.
2. Create relationships between entities based on the following facts:
●

●

Each student is assigned one and only one faculty adviser and must have an
adviser. One faculty member may advise several students, but faculty mem-
bers are not required to advise students. Only the fact of this assignment is to
be recorded in the data model—not possible related data (such as the date
the adviser was assigned to the student).

● Each student may be assigned one and only one mentor, but students are not

required to have a mentor. One mentor may mentor several students, and a
person may be listed as a mentor before he or she is actually assigned students to
mentor. Only the fact of this assignment is to be recorded in the data model—not
possible related data (such as the date the mentor was assigned to the student).

● Each mentor is assigned to work and coordinate with one and only one faculty

member, and each mentor must work with a faculty member. One faculty
member may work with several mentors, but faculty members are not required
to work with mentors. Only the fact of this assignment is to be recorded in the
data model—not possible related data (such as the date the faculty member
was assigned to the mentor).

● Each mentor may be an alumnus, but mentors are not required to be alumni.

Alumni cannot, of course, be required to become mentors.

B. Revise the E-R data model you created in part A to create a new E-R data model based
on the fact that students, faculty, alumni, and mentors are all a PERSON. Use the IE
Crow's Foot E-R model for your E-R diagrams. Justify the decisions you make regarding
minimum and maximum cardinality. Note that:

● A person may be a current student, an alumnus, or both because Highline University
does have alumni return for further study.

● A person may be a faculty member or a mentor, but not both.