Assist with creating EER Model

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:

1) 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.

Hint: Is this a supertype / subtype situation? Hint: Where does the common attributes go?

Hint: Could there be employees other than the three most dependent groups?

2) 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.

Hint: Can an employee belong to more than one group?

3) Room is identified by Building_ID and Room_NO and also has a Capacity. There are the two different kinds of rooms: Classroom 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.

Hint: Try to use the above hints (1&2) and apply them to this situation.

4) Timeslot has identifier TSIS and has attributes DayofWeek, StartTime, and EndTime.

5) 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.

Hint: What kind of a relationship is "pre-requisite" (unary, binary or ternary)? Hint: Would the pre-requisite of a course be present in the course table?

6) Section has identifier SectionID and an attribute EnrollmentLimit.

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

7) A section can be scheduled for no classroom or many classrooms for semesters. 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. You are required to keep track of semester and years of each schedule.

Hint: What kind of a relationship is "schedule" (unary, binary, or ternary)? Hint: How many entities are required to schedule?

Hint: How do you keep track of schedule details? Is there an entity specifically for this purpose?

8) An instructor teaches one, none, or many sections of a course scheduled in a particular semester. Each section of a course scheduled in a seminar has only one instructor.