Question

Please develop an EER Model for the following situation. Your EER diagram must be complete, meaning that all necessary information must be presented. Make appropriate assumptions if necessary and clearly state them. The appropriateness of your assumption will be evaluated.

Situation

An international school of technology (IST) has hired you to create a database system to assist in its daily operation. After several interviews with the administrators, you have come up with the following list of entities, attributes, and initial business rules:

• IST depends on a large number of employees for its continued success. There are three groups of employees on whom IST is most dependent: Administrators, Instructors, and Staff. Some common attributes are shared by all employees: Emp_ID (identifier), Name, Address, City/State/Zip, Birth_Date, and Phone.

• Each of the three groups of employees has at least one unique attribute of its own. Staffs have Certificate, Instructors have Expertise and Rank, and Administrators have position. An employee must belong to at least one of these groups.

• Room is identified by Building_ID and Room_NO and also has a Capacity. There are three different kinds of rooms: Classroom, Meeting Room, and Office. At a given time, a room must be exactly one of these subtypes. An office may be shared by multiple employees, but an employee cannot have more than one office.

• Media is identified by MtypeID and has attributes of MediaType and TypeDescription. Note: Here we are tracking type of media (such as video, projector, etc.), not the individual piece of equipment. Tracking of equipment is outside of the scope of this project.

• Timeslot has identifier TSIS and has attributes DayofWeek, StartTime, and EndTime.

• Course has identifier CourseID and has attributes CourseDescription and Credits. Courses can have one, none, or many prerequisites. Courses also have one or more sections.

• Section has partial identifier SectionID and attribute EnrollmentLimit.

• IST has many departments. Attributes of Department are Department Name as the identifier, Phone No, and Location.

After some further discussions, you have come up with some additional business rules to help you create the initial design:

• A Department has many instructors, but an instructor belongs to only one department.

• A Department is managed by an instructor, and one of the instructors in the department manages the department.

• An instructor teaches one, none, or many sections of a course in a given semester.

• An instructor specifies preferred time slots.

• Scheduling data are kept for each semester, uniquely identified by semester and year.

• A classroom can be scheduled for one section or no section during one-time slot in a given semester of a given year. However, one classroom can participate in many schedules, one schedule, or no schedule; one-time slot can participate in many schedules, one schedule, or no schedules. Hint: Can you associate this to anything that you have seen before?

• A classroom and a meeting room can have one type of media, several types of media, or no media.

• Instructors are trained to use one, none, or many types of media.

Answer 1

EER diagram

below is the EER diagram. EER diagram Enhanced Entity Relationship diagram. which shows the relationship between entities.