Question

1. Please build an Entity Relationship Diagram using the following information:

Build a database for a regional university in Western New York called

Ithica South University. The university database administrator needs to keep track

of the students that attend the university, the courses offered by the university,

and the university employees.

Ithica tracks the first and last name of each student, as well as their target

degree. The university keeps track separately of two subclasses of students, as

students are either undergraduate or graduate students.

Graduate students can serve as research assistants or teaching assistants, and

these specializations are stored in the database. Certain students are fortunate

enough to serve as both research and teaching assistants simultaneously. Each

research assistant’s area of research is tracked, and the university keeps track of

each teaching assistant’s phone number.

The university keeps track of the data of its employees, including their first and last

name, email address, and the type of employee that they are. Ithica has two

types of hires – as employees are further classified as either faculty or staff.

Faculty and staff are both types of employees that Ithica tracks.

Administration would like to keep track of the number of vacation days that staff

have. Additionally, Ithica needs to keep track of each faculty member’s

degree and office hours.

Each member of the faculty is required to teach at least one course. A particular

course might be taught by several faculty members depending on when it is

offered. However, a new course might exist in the catalog but have not yet been

assigned a faculty member to teach it.

When a course is taught by a faculty member, a section of the course is created,

and the semester of that section is recorded. Some sections are assigned one

teaching assistant. If a student is serving as a teaching assistant, they must have at

least one section that they serve and can serve up to two sections at a time.

To be considered active, a student must be enrolled in at least one course. Each

course needs at least 10 students enrolled in it to run, but it may be that no

students enroll in the course during a particular semester. When there are enough

students in a course to run, the enrollment is kept track of with a grade for each

student.

There are full-time and part-time faculty members at Ithica – each faculty

member is classified as one of each of these sub-classes. Furthermore, full-time

faculty fall into two distinct categories of those on the tenure track and those that

have term contracts. The tenure track faculty have the year that they go up for

review for tenure kept track of as well.

Each tenure track faculty member can choose to have a research assistant,

although it is not required. Over the course of their career they may work with

several different research assistants. Given time constraints, however, each

research assistant may only work with one faculty member during their time at

Ithica. It is assumed that if they are hired as a research assistant that they

are actively working with a tenure track faculty member.

Answer 1

Below is the extended ER diagram of the given scenario. It consist of 4 super entities. Super entities further have sub entities. IS A relationship shown is generalization of the entities. All entities are connected via 4 relationship. Each entity has certain set of attributes and primary key is mentioned as underlined attribute.

Cardinality is shown using crow's feet notation.