ER Diagram 02: College Classes

Updated 2020.03.19

A college keeps track of information regarding its employees, students, and classes. Employees are classified as either Faculty, Administrators, or Staff. Faculty members teach classes (if a person does not teach a class, he is not a faculty member), and administrators occasionally teach classes, too. Staff do not teach classes. The college keeps track of the same information for all employees. A class may have more than one instructor. Students take classes and receive grades for those classes. If a person has not taken at least one class, he is not considered a student, and no information is kept for that person (e.g., prospective students are not a part of this database). Scheduled classes are chosen from the college catalog, and a subset of those courses is offered each semester. The same class may be taught more than once in a semester (multiple sections), or it may not be taught at all. Courses are identified by department, course number, and section number. Note that the same department may offer the same course and section number in many semesters (e.g. CSCI201-01 may occur many times) and the description of the class (e.g. what appears in the college catalog) will always be the same for the same class (e.g. "Students learn how to program in an object-oriented programming language."). Note that the information in the college catalog refers to a generic class referred to below as a "catalog class" to distinguish it from a "scheduled class". The information in the schedule refers to "scheduled classes" (e.g. CSCI201-01 offered in the fall of 2020 at 8:00 on MWF in HH112). Each class has a meeting location, usually a classroom in a campus building, but classes can also be taught off-campus. Room capacity must be known to determine class size limits. The only rooms that need to be tracked in the information system are classrooms that have classes that meet in them. Students can sign up for any number of classes each semester, but they are not required to take classes every semester.

The college needs to produce (print) class schedules by term, by department, and by time. Faculty members need to be assigned to classes. It is possible for more than one faculty member to be assigned to a specific instance of a class (e.g., LIBA400-01 in the fall of 2016 may be taught by 2 faculty members). The college needs to be able to produce faculty load reports, showing which courses are being taught by each faculty member over a given period of time. Class descriptions need to be published every term (e.g. CSCI201: Computer Programming I. An introduction to computer programming…). The college needs to be able to produce reports listing all of the classes in each room and the times and days that the classes meet in those rooms. Printed class rosters are needed, showing which students are enrolled in classes for a given term. Students are assigned grades at the end of the term and report cards need to be printed and mailing labels created to mail those report cards. Note that although we would almost certainly keep track of addresses, phone numbers, etc., for employees, none of that is relevant in this situation, so you can disregard it. We do, however, need to keep track of address information for students so we can send report cards to them.


 

Sample attribute values:

Attribute name

Sample values

Employee ID

12345 (employee IDs begin with the digit 1)

Employee Type

F (for "faculty") A (for "administrator") S (for "staff")

Employee First Name

George

Employee Last Name

Washington

Student ID

234567 (student IDs begin with a digit other than 1)

Student First Name

Bill

Student Last Name

Gates

Student Address

1234 Main, Sioux City, IA  51104 (looks like 4 fields to me)

Room ID

HH112

Room Capacity

24

Catalog Class ID

CSCI201

Catalog Class Title

Computer Programming I

Catalog Class Description

Lots of fun stuff about computers

Catalog Class Credits

3 (Semester hours)

Scheduled Class ID

CSCI201

Scheduled Class Title

Computer Programming I

Scheduled Class Section

01

Scheduled Class Start Time

10:00

Scheduled Class Term

Fall

Scheduled Class Year

2016

Scheduled Class Days

MWF (this is just a string—not a date)

Scheduled Class Room

HH112

Student Grade

A-

 

Do the following:

·        Identify each entity class.

·        Identify all attributes for each entity class.

·        Identify all determinants. Note that this may require you to re-think your entity classes.

·        Identify a key for each entity class that will uniquely identify a single entity. Note that the key may be more than a single field. Underline the key field.

·        Identify each foreign key (not all entity classes will have a foreign key). Make foreign keys italic.

·        For each entity class, identify relationships between it and other entity classes.

·        Draw the ER diagram using the crow's foot format used in the textbook.