Northern University is a 4-year undergraduate school. Northern University is made up of five colleges: The College of Business, The College of Social Sciences and Humanities, the College of Performing Arts, the College of Sciences and Technology, and the College of Environmental Sciences. Jan Smith is the president of Northern University, and Dennis Jones is the provost (a provost is a vice president of academics; the deans of the colleges report to the provost). The Northern University Mentor Program recruits business professionals as mentors for Northern University students. The mentors are unpaid volunteers who work together with the students' advisers to ensure that the students in the mentoring program learn needed and relevant management skills. In this case study, you will develop a data model for the Mentor Program Information System. A. Draw an E-R data model for the Northern 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, Northern 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 Northern University, all students are required to live on campus and are assigned Northern University ID numbers and email accounts in the format FirstName.LastName@students.hu.edu. The student entity should track student last name, student first name, student University ID number, student email address, dorm name, dorm room number, and dorm phone number. NOTE: Visio insists on using primary keys rather than identifiers as we normally use in a data model, but we can live with that. Just remember that PK is showing a data model identifier at this point, not a true primary key. - At Northern University, all faculty advisers have on-campus offices and are assigned Northern University ID numbers and email accounts in the format FirstName.LastName@.nu.edu. The faculty entity should track faculty last name, faculty first name, faculty University ID number, faculty email address, department, office building name, office building room number, and office phone number. Northern University alumni live off campus and were previously assigned Northern University ID numbers. Alumni have private email accounts in the format FirstName.LastName@somewhere.com. The alumni entity should track alumnus last name, alumnus first name, alumnus former-student number, email address, home address, home city, home state, home ZIP code, and phone number. - Northern University mentors work for companies and use their company address, phone, and email address for contact information. They do not have Northern University ID numbers as mentors. Email addresses are in the format FirstName.LastName@companyname.edu. The mentor entity should track mentor last name, mentor first name, mentor email 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 members 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 while the following business rules need to be implemented in your design, the E-R model notation may not be able to express them, in which case you will need to add text notes to your diagram indicating which rules will be to be enforced in SQL structures or application code (alternatively, write a memo to accompany your E-R diagram that contains these notes). The relevant business rules are: A person may be a current student, an alumnus, or both, because Northern University does have alumni return for further study. A person may be a faculty member or a mentor, but not both. (SQL or code needed) - A person may be a faculty member and an alumnus. - A person may be a mentor and an alumnus. - A current student cannot be a mentor. (SQL or code needed) - Each mentor may be an alumnus, but mentors are not required to be alumni. Alumni cannot, of course, be required to become mentors. C. Extend and modify the E-R data model you created in part B to allow more data to be recorded in the MPIS system. Use the IE Crow's Foot E-R model for your E-R diagrams. Justify the decisions you make regarding minimum and maximum cardinality. The MPIS needs to record: - The date a student enrolled at Northern University, the date the student graduated, and the degree the student received - The date an adviser was assigned to a student and the date the assignment ended - The date an adviser was assigned to work with a mentor and the date the assignment ended - The date a mentor was assigned to a student and the date the assignment ended Northern University is a 4-year undergraduate school. Northern University is made up of five colleges: The College of Business, The College of Social Sciences and Humanities, the College of Performing Arts, the College of Sciences and Technology, and the College of Environmental Sciences. Jan Smith is the president of Northern University, and Dennis Jones is the provost (a provost is a vice president of academics; the deans of the colleges report to the provost). The Northern University Mentor Program recruits business professionals as mentors for Northern University students. The mentors are unpaid volunteers who work together with the students' advisers to ensure that the students in the mentoring program learn needed and relevant management skills. In this case study, you will develop a data model for the Mentor Program Information System. A. Draw an E-R data model for the Northern 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, Northern 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 Northern University, all students are required to live on campus and are assigned Northern University ID numbers and email accounts in the format FirstName.LastName@students.hu.edu. The student entity should track student last name, student first name, student University ID number, student email address, dorm name, dorm room number, and dorm phone number. NOTE: Visio insists on using primary keys rather than identifiers as we normally use in a data model, but we can live with that. Just remember that PK is showing a data model identifier at this point, not a true primary key. - At Northern University, all faculty advisers have on-campus offices and are assigned Northern University ID numbers and email accounts in the format FirstName.LastName@.nu.edu. The faculty entity should track faculty last name, faculty first name, faculty University ID number, faculty email address, department, office building name, office building room number, and office phone number. Northern University alumni live off campus and were previously assigned Northern University ID numbers. Alumni have private email accounts in the format FirstName.LastName@somewhere.com. The alumni entity should track alumnus last name, alumnus first name, alumnus former-student number, email address, home address, home city, home state, home ZIP code, and phone number. - Northern University mentors work for companies and use their company address, phone, and email address for contact information. They do not have Northern University ID numbers as mentors. Email addresses are in the format FirstName.LastName@companyname.edu. The mentor entity should track mentor last name, mentor first name, mentor email 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 members 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 while the following business rules need to be implemented in your design, the E-R model notation may not be able to express them, in which case you will need to add text notes to your diagram indicating which rules will be to be enforced in SQL structures or application code (alternatively, write a memo to accompany your E-R diagram that contains these notes). The relevant business rules are: A person may be a current student, an alumnus, or both, because Northern University does have alumni return for further study. A person may be a faculty member or a mentor, but not both. (SQL or code needed) - A person may be a faculty member and an alumnus. - A person may be a mentor and an alumnus. - A current student cannot be a mentor. (SQL or code needed) - Each mentor may be an alumnus, but mentors are not required to be alumni. Alumni cannot, of course, be required to become mentors. C. Extend and modify the E-R data model you created in part B to allow more data to be recorded in the MPIS system. Use the IE Crow's Foot E-R model for your E-R diagrams. Justify the decisions you make regarding minimum and maximum cardinality. The MPIS needs to record: - The date a student enrolled at Northern University, the date the student graduated, and the degree the student received - The date an adviser was assigned to a student and the date the assignment ended - The date an adviser was assigned to work with a mentor and the date the assignment ended - The date a mentor was assigned to a student and the date the assignment ended