ER Exercise


One of the major functions of any academic department is student advising. In order to assist a department in better serving its majors, several reports are generated. Hence, this exercise is well suited for report requirements analysis.

A college listing provides a list of all majors for each department in each college. Entries are grouped under a heading that includes the name of the school or college as well as its dean. Each group consists of a department entry that lists the department name, chair and number of majors

report: College Listing

Table 1

A faculty advisor listing includes the faculty advisor name, office, and phone number for each college and department.

report: Faculty Advisor Listing

Table 2

A department and major report lists each major by student name, student number, address, phone, major, and faculty advisor for each college and department.

report: Department and Major Report

Table 3

Overall each school is directed by a dean, and encompasses several departments. Each department includes several faculty members who act as student advisors. One of those faculty members also serves as the department chair. Students are associated with a department by means of their major, and with a faculty member by means of the advising process.

Begin the database design process by listing the entities and the relationships, including the connectivity (1:1, 1:M, or M:N) for each relationship. Then draw the E-R diagram, listing the attributes separately in the format

ENTITY_NAME (attribute1, attribute2, ... , attributen)

Be sure to underline each primary key, and explain any assumptions or constraints that you find necessary.



Process

List the objects under College Listing:

List the objects under Faculty Advisor Listing:

List the objects under Department and Major Report:



Analyze the objects under College Listing:

Analyze the objects under Faculty Advisor Listing:

Analyze the objects under Department and Major Report:



Compile and evaluate combined object list.:



Now that we've gotten this far, determine the relationships and connectivity, and develop the ER Model.