You are to assume the role of a database analyst that has been assigned to develop an entity-relationship diagram (ERD) for a small-town university. The following information has been obtained for use in constructing the ERD:

The university consists of a number of separate colleges, each with a unique name (e.g., Business Administration, Engineering, etc.). Each college has one office location and each office has multiple phone numbers (to accommodate the large number of calls received by the office).

Each college is made up of one or more departments. (A given department can belong to only one college.) Each department has assigned to it a unique three-letter departmental code (e.g., BIO, MKT, etc.). In addition to the code, each department has a name (e.g., Biology, Marketing, etc.), an office location, and one phone number.

Each department offers numerous courses. (A given course is offered by only one department.) Each course is identified by a combination of department code and course number (e.g., BIO 2001). Other information recorded for each course is the course name (e.g., Introduction to Biology) and the number of credits that the course is worth.

A course may be offered in several different sections. (A given section is an offering of exactly one course. Not all courses in the university’s catalog are currently offered in a section, however.) Each section has a unique combination of department code, course number, section number and semester to identify it (e.g., BIO 2001 001 FA07). Other information stored for each section includes the days offered (e.g., MWF, TR, T, etc.), the time offered (e.g., 8:00-9:15, 2:00-3:15, etc.) and the location where the class meets.

Information on students is also stored. Included in this information is each student’s name and home address. (Specific details to record include the student’s first name, middle name and last name, and the student’s street, city, state, and zip.) The student’s phone number and status (e.g., FR, SO, JR, etc.) is also recorded. Students are identified by a unique student number assigned to each student.

Students may enroll in one or more sections. Some students, however, are not currently enrolled in any sections. Sections typically enroll numerous students; however, it is possible for a section to be recorded that has no students yet enrolled for it.

Some students serve as counselors for other students (e.g., incoming freshmen). Counselors are volunteers that help other students with problems related with student life. Not all students serve as counselors, but those that do often counsel multiple students. Not all students have a counselor, but those that do are assigned to only one counselor.

All students are also assigned one professor as an academic advisor. Most, though not all, professors serve as advisors and typically advise numerous students.

Information to be stored on each professor includes their faculty ID (a unique number assigned to all faculty), their name (consisting of first name, middle name and last name) and their office phone number.

Professors, of course, also teach the sections of courses that are offered. Most professors teach one or more sections, but some professors may be involved exclusively with research and perform no teaching role. A given section may be taught by a single professor, or “team taught” by more than one professor.

All professors are employed by the individual departments. No professor is employed by more than one department. Each department employs at least one professor. Each department also has one professor that serves as the chairperson of that department.

REQUIRED:

Prepare an ERD that reflects the data and relationships of this business. Use the attached University Relational Schema as a guide to prepare your ERD.