Building an Entity-Relationship Model

Building an Entity-Relationship Model

The Students Results Management System Example

1. Introduction

A data model aims to identify all the data which needs to be recorded by an information system, and what the connections are between these data items. E-R models identify the things which we need to store data about (entities) and the relationships between them. This becomes vital input to defining the data structure needed in a system, and in particular it forms the basis of relational database design.

A relational database stores its data in a set of linked tables. These tables can be imagined as the sort of lists which you would develop on paper – each table comprising a group of columns (fields) with headings which name what is in each column.

Some of the aims for a database are to ensure that:

(a) all required data items are stored;

(b) each data item is recorded in the system in as small a number of times/places as possible;

(c)data input and storage can be managed as efficiently as possible;

(d) the way in which the data is stored fits with the access and data control/management requirements;

(d) the links which are established between data tables enable queries on the data to cross between the tables.

The E-R model tries to develop a structure of tables which achieves these aims.

2. Getting started

If you are asked to identify what data items a student results management system has to store, the first and most obvious thing is that it stores student results for units. Therefore we could start by saying that there is an entity called Student Result. Hence our initial idea of an E-R diagram would look like this:

In theory, we could establish a system with thisas the only entity – all the data we store could be classified as an attribute of the student result. The corresponding database would contain a single table called Student Result, with fields corresponding to the attributes of student result. However, before we adopt this very simple structure, we need to think about the attributes of this entity, to see whether it may be necessary to modify it.

The obvious attributes we would have to record for Student result would be the data items needed to identify the student and the data items needed to record their result. Within a university system, students are identified both by name and by ID, and their result is recorded as a mark and a grade. Therefore our first draft of the table which would be associated with the Student Result entity is as follows:

Student Result
Student name / Student ID / Unit / Mark / Grade
John Smith / 12345678 / IMS1805 / 65 / C

These are clearly the main data items which are at the heart of the system, but is it sufficient for the system to record only these data items? If it is, then we were wasting our time worrying about building an E-R model in the first place; all we really need is a simple list and you don’t need to do data modeling for something that simple.

In practice, in a real university system, the answer as to what would be included in the E-R model would dependon:

• the scope of the system is in terms of the range and quantity of data it needs to keep, and
• the way in which this data is input into the system and accessed by system users.

The aim of the rest of this exercise is to start from this single ultra-simple picture, and show how a more sophisticated (and realistic) data model can be developed. Note that the decisions as to which of these elements of the more complex model are required would depend on what the user wants. The task of the analyst is to use the diagram to help develop their understanding and to prompt the user into explaining what they need. After I have developed the model step-by-step below, I will return to this point.

Note that as I identify each new set of data items which I might need to record, each time I have a choice – I can just addthe new data items as extra attributes to an existing entity, or I can create a new entity to store them. Sometimes I can decide this fairly quickly and easily – the new data items really are about something which is quite clearly a different thing and therefore I need a new entity. In other cases the distinction is not so clear and maybe the data items could just as easily be treated as attributes to an existing entity. In the latter cases, sometimes the reason for deciding to create a separate entity is the desire to match the data structure to the timing and methods by which real data is added into the system (see for example the first step below, where we add a Unit Enrolments entity).

The following example shows one possible step-by-step development of the picture of what the system might have to record. It lists a series of steps by which possible user requirements for data items might be added to our initial simple single entity. It is entirely artificial because I don’t have any actual user information to work on to help me decide what data elements are needed. However I hope it shows how an analyst might think in developing a big picture view of data needs and how they connect. Good analysts can start at something as simple as the initial basic need to record student results, and build out from that.

So, starting from the beginning, we have identified an entity called Student Results with attributes given above. Let’s now think step-by-step about more data items we might need to add and what entities we would need to create in our system model.
Step 1. Recording the unit enrolments

Who are the students who need to be given results? We cannot rely on the assignment and exam results only as our source of student names. All students enrolled in a unit must be given a result, regardless of whether they actually complete the assignments and exam. Likewise any student who is not correctly enrolled is not entitled to be given a result (eg if you have not paid your enrolment fees for a unit, the university will not release your result until you do). Students who withdraw from the unit do not get a result, even if they have completed some of the assessment tasks. Therefore the systems needs to be able to record the students who are correctly enrolled, or who were enrolled and then withdrew from the unit. This means our E-R diagram needs a new entity to record the enrolments in each unit for whom results will be required.(Note the establishment of a separate entity to hold this data is also a timing issue – Students enroll in units many months before they take their assessment, and teaching staff use the enrolment list to build their list of student names requiring results). Examples attributes of unit enrolment might be:

Student name / Student ID / Unit Code / Date of enrolment / Date of withdrawal / Enrolment status

Step 2. Adding the details to cope with multiple offerings of units

A unit will be offered many times, across different years, different semesters, different campuses, different times of day (day/evening class), etc. The system needs to be able to identify which offering a student was enrolled in. Therefore we need an entity to deal with this information. (Again the timing of data input is relevant here. Unit offerings need to be identified and recorded long before the students actually enroll, so it would not be very sensible to try to include all the unit offering details as part of the Unit Enrolment entity). Attributes of Unit Offering might be as follows:

Unit code / Year / Semester / Campus / Time (Day or evening class)

Step 3. Including further student details

Over time a unit may also change some of its details. It may have alias names or codes or it may change name, code, etc, while still remaining the same unit. The system needs to be able to identify these variations, to prevent students from doing the same unit twice under different names/codes. Therefore, we may need a new entity to hold this sort of information about units. Sample attributes of Unit might be:

Unit Name / Unit Code / Alias name / Alias code

Step 4. Including further student details

We want to record lots of information about students apart from just their enrolment in a particular unit (eg address, e-mail, phone, date of birth, etc), so we would not want to have to record all that in every unit enrolment. Therefore we want a separate entity to record details about each student. And of course most students are enrolled in a course, so we would like a separate entity to define the details of each course. Most units are also associated with particular courses, so we could add that link to if required. Sample attributes for these entities might be:

Student = Student name + Student ID+ E-mail +Postal address+ Phone, etc

Course = Course name+ Course code +Course type (bachelors, Masters by Research, masters by Coursework, etc) +Course co-ordinator, etc

Step 5. Adding the details of who manages the unit and course

Courses are offered by faculties and units are run by departments, so we may want/need to extend our model to include information about these entities too. Possible attributes might be as follows:

Faculty = Faculty name + Faculty code + Faculty contact person + Phone + etc

Department = Department name + Department code + Department contact person+ Phone etc

(note that these entities like these are sometimes created simply to define a code by which the faculty or department can be identified; eg at Monash, SIMS is Department Code 931; a code like this can simplify data entry because the name need not be written in full).

Step 6. What are the requirements for the classes which are run for the unit?

If our system wants to cover resource allocation information, then we may wish to add information about the rooms and other resources needed to run a unit. For example:

Room requirements = Unit code + Lecture room requirements + Tutorial room requirements + Lab requirements + Studio requirements + etc

Resource requirements = Unit code + Software needed + No. of licences + Specialist hardware needed + etc

Step 7. What rooms and staff are allocated to a particular unit offering?

For any given offering of a unit we may want to record information about the staff and rooms which were allocated to each unit offering:

Staff allocations = / Unit code / Unit offering No / Lecturer / Tutors
Room allocations = / Unit code / Unit offering No / Lecture theatre / Tute rooms / Labs

Step 8. Recording the details of assessment items

Leaving the unit information and returning to our original Student Results entity, our results system might want to record the detailed information about assignment and exam marks for each student. Therefore this would add two more entities:

Student Name / Student ID / Assign 1 Mark / Assign 1 value / Assign 2 Mark / Assign 2 value / Assign total mark

and

Student Name / Student ID / Exam Q1 mark / Exam Q2 mark / Exam Q3 mark / Exam Q4 mark / Exam Q5 mark / … etc / Exam Total

Step 9. Including therecording of special consideration information

Special consideration applications affect the Student result, so we may wish to add an entity to record the details of applications and their outcomes:

Student name / Student ID / Date of application / Reasons/condition / Decision

Step 10. Adding the rules for the grading system

The grade could be entered directly into the student result entity, or it could be calculated from the mark by referring to this Entity. Different departments/faculties may use different grading systems, so this could be connected all the way back to the Faculty entity mentioned earlier.

Grade / Letter abbreviation / Lower limit mark / Upper limit mark

Combining all these E-R fragments, we finish up with the following big picture.

The Big Picture

Final Observations

Note that we have now identified a lot of data items, some of which are only very indirectly related to the actual student results. For example, entities like Room Requirements and Staff Allocations and Faculty may seem to be completely unrelated to Results.

This may be true. In this example I have just added a range of possible entities to show how data items lead on from one to the next. The analyst needs to carefully consider the needs of a particular system in order to decide which data elements and entities are needed, and which can be left out. This will depend on the users and what they need.

For example, the Monash Student Records System only records the students’ final mark and grade; it does not include the details of exam and assignment marks, nor does it store details of Special Consideration applications and decision. So for a system being built to support Student records, the entities relating to these things can be removed from the system model. However the faculty office staff who process special consideration applications are required to store all the applications, make sure lecturers have taken them into account and recorded the outcome; therefore they would need that entity included if the system was being developed for them. Lecturers need to be able to tell students how they went in all assessable work, so they need the Assignment Mark and Exam Mark entities if the system is being built for them.

We can partition an E-R model just like a DFD to separate out different groups of entities which are related to different parts of a system – eg from your knowledge of the system you are using for your assignment work in this unit, you should be able to recognize how some of these entities in this example could relate to different system functions from the assignment system, such as unit offerings, resource needs, staff allocation, course and unit management and so on.

However, although some of these entities may seem a bit remote and unconnected from student results, you might be surprised at how some may link in to aspects of results analysis. For example, as a lecturer I may wish to compare marks across tutorial classes to see whether all tutors are marking to the same standard; therefore I may want to be able to do a database query which connects the staff allocation to student assignment marks. In some universities, different faculties use different grading rules, so if the system is going to be able to check whether students in a unit have been given acceptable grades, the grading rules entity will have to include details of the different rules used by each faculty, and then the connections between entities will have to be in place to enable the system to cross-check back from the unit results to the unit to the department which runs the unit to the faculty which owns that department to decide which grading system should apply.

I have left the connections fairly simple for this model, but it can become very complex at times, if you need to be able to cater for a wide variety of types of queries; if you leave out a connection, then it may be impossible for a user to make that query.