Draw your ER diagram using a tool, such as PowerPoint, Vizio, or draw.io. A hand-drawn ER diagram wont be accepted.

(2 points) Design an ER diagram for the Student Registration System based on the provided Requirements Document. Remember to indicate the key for each entity set and the connectivity of each relationship. Use (min, max) format to indicate connectivity. Note that many constraints cannot be represented in the ER diagram, and they will be represented at later stages of the database design. Question 3 of this homework asks you to list these constraints.

(0.5 points) Discuss whether it is a good idea or not to create a super entity set for Students and Faculty in the ER diagram for the Student Registration System.

(0.5 points) Identify constraints in the Requirements Document for the Student Registration System that cannot be expressed using the ER model we discussed in class. First list the constraints not represented in the ER diagram for each entity set separately. Then list the constraints involving multiple entity sets.

1. Introduction

The objectives of the Student Registration System are to allow

1. students to register and deregister for classes and to obtain reports on their status;

2. the staff at the registration office to obtain student status report, to enter student grades, and to maintain information about students, courses, and registrations.

Note: This system is simplified yet complete enough to make the assignment reasonably realistic.

2. Information to be contained

The following information will be stored in the Student Registration System.

1. Student information. For each student, we keep the following information (suggested attribute names are in parentheses): an id number (sid), a name (including first name and last name), a status (status), i.e., whether a student is a freshman, a sophomore, a junior, a senior or a graduate, a GPA (gpa), and an email address (email). The GPA of a student is computed from the number grades the student has received. It is the sum of the number grades divided by the number of courses (excluding those with letter grade I) the student has completed. Students are uniquely identified by sid. In addition, different students have different email addresses.

2. Course information. For each course, we keep: a course id (cid), a title (title), and the number of credit hours (credits). Courses are uniquely identified by cid. Each cid consists of a department code (dept_code such as CS for Computer Science) and a 3-digit course number (course#, like 432 or 532). All undergraduate courses have course# between 100 and 499 and all graduate courses have course# between 500 and 799. For simplicity, it is assumed that all graduate courses have three credit hours and all undergraduate courses have four credit hours.

3. Prerequisite information. Each course has zero or more pre-specified prerequisite courses.

4. Class information. A class is an implementation of a course for a particular semester. Each course may have zero or more section in a specific semester. For each class, we keep: a section number (sect#), the year of offering (year), the semester of offering (semester), the maximum number of students allowed (limit), the actual size of the class (size), a classroom assigned to the class (classroom), the capacity of the classroom (capacity), the time of the class (time) with starting time (start_time) and ending time (end_time), and the days of the class (days). It is required that each class must have at least 5 students. (Hint: A class should be a weak entity set.) 2

5. Enrollment information. We keep track of who is registered in what classes. A student receives a letter grade (lgrade) and a number grade (ngrade) for each class the student registers and completes. Only {A, B, C, D, F, I, null} are valid letter grades and the corresponding number grades are: A = 4, B = 3, C = 2, D = 1, F = 0 and I = null. null is a special value that is usually used in database systems to indicate that a normal value is not available or not applicable. Each student must enroll in at least one class but no more than five classes in the same semester. No student is allowed to enroll in different classes of the same course more than once.

6. Department information. For each department, we keep: a name (deptname), a telephone number (phone#) and an office location (office). Departments are uniquely identified by name. Different departments have difference offices. Each department must have at least one faculty member but it is not required to have any student.

7. Faculty information. For each faculty member, we keep: an id number (fid), a name (name), including first name and last name, an office (office), a rank (rank) and an email address (email). Faculty members are uniquely identified by fid. Different faculty members have different offices and email addresses.

8. Teaching assignment information. We keep track of which faculty member teaches what classes. Each class is taught by exactly one faculty member. A faculty member may teach zero to three classes in a semester.

9. Student major. Each student is associated with either one or two departments, i.e., the majoring departments.

10. Faculty affiliation information. Each faculty member belongs to one department and a department may have one or more faculty members.

11. Course offering. Each course is offered by exactly one department and a department may offer many (zero or more) courses.

3. Integrity Constraints

Many integrity constraints have already been included in the description in Section 2. The following describes additional constraints that need to be maintained.

1. GPA is a decimal number between 0 and 4.

2. Courses and their prerequisite courses do not form cycles.

3. Classes are uniquely identified by the combination of the following attributes: cid, sect#, year, and semester.

4. The values of days are limited to {Monday, Tuesday, Wednesday, Thursday, Friday}.

5. The values of semester are limited to {Spring, Fall, Summer 1, Summer 2}.

6. The actual size of a class must not exceed the limit of the class.

7. The limit of a class must not exceed the capacity of the assigned classroom.

8. No classes of overlapping times can be assigned to the same classroom.

9. No faculty member can teach classes with overlapping times.

10. Valid values for faculty rank are {lecturer, assistant professor, associate professor, professor}.

11. For a student to be successfully registered in a class, the following conditions must all be satisfied:

a. The class still has room for new students, i.e., size < limit.

b. The student has not been registered in a different section of the same course.

c. The student has completed all the prerequisite courses with a grade of at least C.

d. The student has enrolled in no more than four other classes; that is, no student can enroll in more than five classes in one semester.

e. The time of the class does not overlap with the times of the classes the student has already enrolled in.