8.2 Example of a university registration data model
Here is a statement of the data requirements for a product to support the registration of and provide help to students of a fictitious e-learning university.
A UK-based e-learning university needs to keep details of its students and staff, the courses that it offers and the performance of the students who study its courses. The university is administered in four geographical regions (England, Scotland, Wales and Northern Ireland).
Information about each student should be initially recorded at registration. This includes the student's identification number issued at the time, name, year of registration and the region in which the student is located. A student is not required to enrol on any courses at registration; enrolment on a course can happen at a later time.
Information recorded for each member of the tutorial and counselling staff must include the staff number, name and the region in which he or she is located. Each staff member may act as a counsellor to one or more students, and may act as a tutor to one or more students on one or more courses. It may be the case that, at any particular point in time, a member of staff may not be allocated any students to tutor or to counsel.
Each student has one counsellor, allocated at registration, who supports the student throughout his or her university career. A student is allocated a separate tutor for each course on which he or she is enrolled. A staff member may only counsel or tutor a student who is resident in the same region as that member of staff.
Each course that is available for study must have a course code, a title, and a value in terms of credit points. A course is either a 15-point course or a 30-point course. A course may have a quota for the number of students enrolled on it on any one presentation. A course need not have any students enrolled on it (such as a course that has just been written and offered for study).
Students are constrained in the number of courses they can be enrolled on at any one time. They may not take courses simultaneously if their combined points total exceeds 180 points.
For assessment purposes, a 15-point course may have up to three assignments per presentation and a 30-point course may have up to five assignments per presentation. The grade for an assignment on any course is recorded as a mark out of 100.
Figure 13 is one possible data model that describes the above set of requirements. The model has several parts, beginning with an E–R diagram and followed by a written description of entity types, constraints and assumptions.
Student (Studentld, Name, Registered, Region)
Staff (StaffNo, Name, Region)
Course (CourseCode, Title, Credit, Quota)
Enrolment (Studentld, CourseCode)
Assignment (Studentld, CourseCode, AssignmentNo, Grade)
A staff member may only tutor or counsel students who are located in the same region as the member of staff.
Students may not enrol for more than 180 points worth of courses at any one time.
The attribute Credit (of Course) has a value of 15 or 30 points.
A 30-point course may have up to five assignments; a 15-point course may have up to three assignments.
The attribute Grade (of Assignment) has a value that is a mark out of 100.
A student has at most one enrolment on a course as only current enrolments are recorded.
An assignment may be submitted only once.