Perform conceptual design for the following case. Create an ER diagram and a specification document that includes additional constraints not shown on the diagram. If you need to make additional assumptions to resolve any ambiguities in the case description, clearly state them in the specification.
Continental Palms Community College (CPCC) is considering creating an information system. You have been hired to design a conceptual schema for the database in the information system first.
CPCC has several academic departments. The data held on a department include name, code (abbreviation of the name), location (campus, building), phone number, and fax number.
Each department provides several programs. For example, the Department of Business Administration has four programs: Information Technology Management, Accounting, Marketing, and Supply Chain. The data held on a program include name and minimum requirement (number of credits and GPA) for graduation. For any program, the number of credits required cannot be lower than 20 and the GPA required cannot be lower than 2.0.
Everybody at CPCC is assigned a CPCC ID and a CPCC email address. Additional data held on everybody include name (first name and last name) and gender. Additional data held on an employee include job title and salary (in the range of 5000 to 90000 dollars). Every employee works in one department. Every department has one chair, who must be an employee working in the department. Further, additional data held on an instructor include the highest degree earned (degree, field, university, and year). Additional data held on a student include high school GPA (a minimum of 2.0 is required for admission), number of credits taken so far, and current cumulative GPA. A student may also be an employee. Every student majors in one program.
Each department offers several courses. Each course is identified by the department code and a number between 100 and 399. The number is unique within a department but not across departments. For example, there may be a BUS 301 offered by the Department of Business Administration and an ENG 301 offered by the Department of Engineering. Additional data held on a course include title and number of credits (between one and six). Each program requires a set (at least two) of courses and allows another set (at least two) of courses as possible electives. Every course must be included, as a required course or elective, in at least one program. A course may require another course as a prerequisite. However, a course cannot require another higher level course as prerequisite, that is, a 100 level course cannot have a 200 or 300 level prerequisite and a 200 level course cannot have a 300 level prerequisite. To allow students to graduate on time, the course prerequisite relationship is hierarchical up to three levels.
CPCC holds four semesters each academic year. The data held on a semester include academic year, term (fall, winter, spring, and summer), starting date, and ending date. The duration of a semester is between 4 and 16 weeks.
There may be multiple sections of a course offered in a semester. Each section has a sequential section number, which is unique among all sections of the same course in the same semester. Additional data held on a section include classroom (building, room number), meeting days (a section may meet on one to three days, between Monday and Friday, per week), meeting time (a period of 45 to 150 minutes, from 8am to 8:30pm), number of seats (between 10 and 200), number of students enrolled, and number of open seats. No section can be over-enrolled by more than 10%, that is, the number of students enrolled cannot exceed the number of seats by 10%. Scheduling conflicts must be avoided, that is, no two course sections should be scheduled in the same classroom at the same time.
Each section is taught by one instructor, who may choose to use any number of required or optional textbooks. The data held on a textbook include ISBN, title, edition, publisher, and publication year. The maximum teaching load for an instructor in one semester is four sections.
A student may take up to six courses per semester. CPCC uses a four-point grading scheme (A, B, C, D, and F for 4, 3, 2, 1, and 0 point, respectively). A student can retake a course only if he or she fails, that is, gets an F. A student cannot enroll in multiple sections of the same course in the same semester.