Business Process, Inc. (BPI), a consulting company offering business process reengineering and application system development expertise, wants to develop a prototype of a simple University Registration System (UNIVREG) to handle student/faculty information, course/section schedule, and co-op and lab information. Many small universities are in need of such a system. BPI believes that the profit potential from economies of scale alone in custom-fitting such an IS application to small universities who primarily offer a small number of programs is an attractive business opportunity. You have recently been hired by BPI and assigned to make use of the following data specifications and develop the conceptual design for this application.

A university has several departments and these departments employ faculty (professors) for purposes of teaching, research and administration. A department may have many professors but has to employ at least five. A professor, however, belongs to only one department at any time. In addition to teaching, some of the professors may work as department heads. Each department has a department head, but no more than one. Every department should continue to exist as long as it has at least one professor associated with it or it offers at least one course. If a faculty member serving as a department head leaves the university, some other professor (often, the most senior faculty member of the department) assumes the role by default.

The departments may offer several courses as part of their academic mission. However, any particular course is offered by only one department. Not all courses are offered all the time—but, every course is offered sometime. When offered, multiple sections of some of the courses may be offered during a quarter in a year. If a particular course is no longer offered, all offerings (sections) of that course should be deleted unless there are students enrolled in the course sections. If, however, a student leaves, that student’s enrollment in all associated course sections should be removed. A course may be a prerequisite for several other courses, but a course may have no more than one prerequisite. A course cannot be removed from the database as long as it is a prerequisite for other course(s); however, if it only has prerequisites, then its deletion should be accompanied by the removal of its links to all of its prerequisites. Some professors in the university also write textbooks. Sometimes, more than one professor may be co-authoring a book, but all textbooks used by the university need not have one of its professors as an author. Some professors are also authors of multiple textbooks. There is no plan to record authorship of professors that are not working in the university in this system. When a professor leaves the university, the university no longer keeps track of books written by that professor. Likewise, if a textbook is no longer in use, the authorship of the textbook is not preserved either. The system also needs to record which professor uses what book in which course. Removal of a textbook from the database is prohibited if it is used by a professor in a course. However, if a course is removed from the catalogue, its link to a professor using a particular textbook is also removed. Likewise, if a faculty member leaves the university, the link to the textbook used by the professor in a specific course is also deleted. All professors teach, and a professor may teach several course sections. A course section, however, is taught by just one professor and must have some professor assigned to teach it. Some of the course sections may have multiple lab sessions in a quarter. Each lab session caters to only one course section—that is, no joint lab sessions. If a course section has an associated lab session, cancellation of the course section is not permitted.

Students enroll in course sections. In fact, to remain a student, one has to take at least one course (section), but university rules forbid a student from taking more than six courses (sections) in a quarter. Each section has to have at least ten students enrolled; otherwise it will be cancelled. If a student has registered for a section, the section should continue to exist. Also, when a professor is assigned to teach a section, deletion of the professor record is prohibited. The university admits mostly graduate and undergraduate students, but a few non-matriculating students are also admitted. The undergraduate students may, as part of their academic program, enroll for professional practice (co-op) sessions with companies. Several students may be enrolled in the same co-op session and a co-op session has at least one undergraduate student enrolled in it. An undergraduate student can co-op more than once. When a student leaves the university for whatever reason, if it is a graduate student the associated graduate student record is deleted from the database; but, if it is an undergraduate student, the deletion of the student record is stopped so that the co-op status of the student can be properly verified. If, having verified the co-op status, the decision is made to drop the undergraduate student information from the system all co-op enrollments for that student should also be erased. Cancellation of a co-op session is prohibited if there is/are student(s) enrolled in it. As part of their academic experience some of the graduate students are assigned to conduct one or more lab sessions. A lab session can be conducted by at most one graduate student, but some lab sessions are not assigned to any graduate student. When a graduate student graduates, the lab sessions assigned to him/her cannot be cancelled; instead, the capability should exist to indicate that, for the present, the lab session is not handled by a graduate student.

Students borrow books from a single (main) library on the campus. A student may borrow a lot of books and a book may be borrowed by several students when available. Book-returns by students are also recorded in this system. The return pattern is the same as that of borrowing. Deletion of a student record is not allowed if he or she has any borrowed books outstanding. If a book is removed from the library catalogue, all borrow and return links for that book are removed. When a student leaves, the book-return links for that student are also discarded. It is important to note that a book should have been borrowed in order for it to be returned.

The registration system should capture student information like the name [o], address, and a unique student ID for each student. In addition the status of the student should be recorded. For undergraduate students, data on concentration should be available—all undergraduate students have multiple (at least two) concentrations. Thesis option [o] and the undergraduate major of each graduate student should be captured by this system. A co-op session is identified by year and quarter and each co-op session has a session manager [o]. A particular student during a particular co-op session works in a company and the database should record the name of the company and co-op assessment [o] for the student for each co-op session. Every professor has a name, employee ID, office [o], and phone [o]. Both professor name and employee ID have unique values. Data gathered about a department are: the department name [o], department code [o], location, and phone# [o]. For a department, the name and code are both unique. The courses offered have data on course name, credit hours, college [o], and course#. The course# is used to distinguish between courses. Each course may have multiple course sections with data including the classroom [o], class time, class size [o], section number, quarter, and year. There is no unique identifier for course section, because the course section has existence dependency on course—section number, quarter, and year together in conjunction with course# can uniquely identify course sections. The grade a student makes in a particular course should be available through the system. The lab sessions have information about the topic [o], time, lab location, and the lab session number for a given course section. Attributes of textbooks include ISBN, the unique identifier, Year [o], Title, and Publisher [o]. The library books, on the other hand are identified by a call#. The ISBN# and Copy# together also identify a copy of the book. The name of the book [o] and author [o] are also recorded. Note that optional attributes are marked by an [o]; so, the rest of the attributes are mandatory.

a. Develop a Presentation Layer ER model for the University Registration System (UNIVREG). The ERD should be fully specified with the unique identifiers, other attributes for each entity type, and the relationship types that exist among the various entity types. All business rules that can be captured in the ERD must be present in the ERD. Any business rule that cannot be captured in the ERD should be specified as part of a list of semantic integrity constraints.

b. Incorporate the following business rule into the Presentation Layer ER model: no two courses can be taught by the same professor using the same textbook.

c. Develop a Coarse-granular Design-Specific ER model for UNIVREG.

d. Transform the above design to Fine-granular ER diagram. Note that attribute characteristics are not provided and thus need not appear in the diagram.

Answer rating: 100% (QA)

Presentation Layer ER model for the University Registration System a The Presentation laye... View the full answer