I need to create an ER Diagram for the scenario described below.
You are commissioned to design a database to keep records of a small college. You talked with various key members of this community and obtained the following information:
- The college keeps information about students and student performance. Every student is known by her/his first name, initials and last name. They are also identified by a unique StudentID provided by the University. Every student also belongs to one of five Schools (Arts & Sciences, Education, Management, Music and Engineering), and has a Major within that School (History, Chemistry, etc.). All programs of study require 4 years of study, and students are classified as freshmen, sophomore, junior, or senior, depending of the year that are currently attending. Each student is assigned and advisor from Faculty.
- The college also keeps track of Faculty information. Besides their first name, initials and last name, Faculty members are also issued a unique FacultyID for the institution. Each one is also given an official title for their position in the University, an office number and a telephone number.
- Every school within the college has a number of different departments. Each department may offer courses identified by a unique CourseID (i.e. IS361). They also have a course name, and a number of credits hours they are worth for.
- Every semester the departments offer some of the courses associated with them. These offerings are uniquely identified by a Semester and a List number. There are 3 semesters per year (Fall, Spring and Summer) and they are given a name that includes the year, i.e.: Fall 2016 or Spring 2017. A List Number is unique within a semester, but it may be repeated from semester to semester and assigned to different courses between semesters. Different sections of the same course within a semester are assigned different List Numbers. Besides referencing a course, every offering also identifies the faculty member who will teach the course, and the classroom number.
- When a semester ends, student’s grades are stored in a file of Official Records that references the student, the semester, the list number, and the final grade (A, B, C, D, F or I).
- The college also wants to keep track of student associations that are advised by a Faculty member. To this effect they give each of these associations a unique AssociationID and an official name. Each association must have only one advisor, but any student may belong to as many associations as they want. A list of all student members of each association is also kept up-to-date.