A university is transferring its course catalog to an automated online system.
Courses are offered by departments, which are identified by codes of between two and four letters, such as ENG for English, MATH for mathematics, JOUR for journalism, or CS for computer science. For each department, we want to store a name, description, and the location and phone number of the department office.
The college has a notion of a degree-granting program, which is not identical to a department. Each degree-granting program is administered by a single department, but a department can run more than one program. For example, the computer science department offers a regular bachelor's in computer science and a certificate degree in web design. For each program, we want to store its name, description, and type of degree (such as certificate, bachelor's, master's, or doctoral).
Each department offers multiple courses, identified by the department code and an additional three-digit number: for example ENG 412, CS 111, or MATH 201. For each course, we want to store a name and description.
Each program has a set of required courses, which may be from various departments. It also has a required number of electives and a set of allowed elective courses.
Some courses have prerequisites: for example, students must complete CS 111 before enrolling in CS 112, or ENG 101 before enrolling in JOUR 200. Some courses have more than one prerequisite, and some are prerequisites for more than one other course.
You wish to create a database storing the above information and describing the relationships between courses, departments, and programs, along with the prerequisite relationships between courses. Write a database outline and draw an entity-relationship diagram.