CS403 - Database Management Systems - Lecture Handout 12

User Rating:  / 0

Related Content: CS403 - VU Lectures, Handouts, PPT Slides, Assignments, Quizzes, Papers & Books of Database Management Systems

Overview of Lecture

In today’s lecture we will discuss the ER Data model for an existing system and will go through a practice session for the logical design of the system The system discusses is an examination section of an educational institute with the implementation of semester system.

Steps in the Study of system

Preliminary study of the system

  • Students are enrolled in programs.
  • The programs are based on courses
  • Different courses are offered at the start of the semester
  • Students enroll themselves for these courses at the start of semesters
  • Enrolled courses by students and offered courses must not be same.
  • The difference is due to the individual situation of every student, because if one student has not pass a certain course ‘A’ in the previous semester he will not be able to register for a course ‘B’ offered in this semester as the course ‘A’ is the prerequisite for course ‘B’.

  • After valid registration classes start.
  • A Course which is offered is assigned to a teacher also
  • There can be any mid term exams and in this system we have only one mid term
  • All the students are given assignments and quizzes and are awarded marks against their performance.
  • Result of the student is prepared on the basis of assignment marks, sessional and mid term marks and the final exam.
  • GP (Grade point) for students is calculated in each subject.
  • Average grade point is calculated on the basis of GPs in individual subjects
  • And the Cumulative GPA is calculated for all the passed semesters.

Outputs Required

  • Teachers and controller need class list or attendance sheet, class result; subject and overall
  • Students need transcripts, semester result card, subject result

Entities associated with the system

  • Students
  • Teachers
  • Controllers

Once the analysis of the system is done it is important to make a draft of the system using a standard tool which specifies the component and design of the system. This design is useful because anyone using the design can work on the existing system and clearly understand the working without working on the system from the scratch.

Tool used for such graphical design is Data Flow Diagram (DFD) In the Figure -1 of the system we have a context diagram of the system which shows integration of different entities with the examination system, these include Registration system, controller, student and teacher entities.

Entities associated with the system

  • From the diagram we can understand basic functionality of the system and can find how the data is flowing in the system and how different external entities are communicating or interacting with the system.
  • First of all we have registration system, which provides the data of students to the systems once the registration process has been completed, this data is now free of errors in terms of validity of a certain student for a certain course or a semester.
  • Second external entity interacting with the system is the teacher, a Teacher is given a list of students who are enrolled in a class and the registration system has declared them as valid students for that very course. Then the teacher allows those students in the class and continues the process of teaching the class, during this process the teacher takes test of the students and prepares papers for the students and also prepares quizzes to be submitted by students. All the data of students’ attendance quizzes and assignments along-with different sessional results is then submitted by the teacher to the examination system which is responsible for preparation of results of the students
  • Third interacting entity with the system is the controller’s office it is provided with the semester overall result, subject results and also the result of each class fir performance evaluation and many other aspects.
  • Fourth entity is student which externally interacts with the system for getting its result, the result is submitted to the student and may be in one of different forms such as, transcript and result card etc.

Level 0 Diagram

The three major modules which have been identified are given below our level 0 diagram will be based on these three modules and will elaborate and describe each of the modules in details.

  • Subject registration
  • Result submission
  • Result calculation

Level 0 Diagram

The first module identified in the system is the Registration of the students for the system As the DFD show a student applies for registration along-with certain registration information which is required by the system, Process 1.0 of the system checks the validity of information in the form if the Registration form is found to be valid the information in the form is passed onto the second process where the validity of registration is determined by checking certain prerequisites for the courses to which student wishes to be enrolled. After the prerequisite checking the data of the student is stored in a registration database for use by other processes in the system.

During this process the result of the students is also checked for the previous semester or previously studied subject to confirm whether the student has passed a certain pre- requisite subject before he can attempt to enroll for a second course which is based on that prerequisite.

Level 0 Diagram 1

The Second DFD is in fact combination of the last diagram and some new details to the DFD this portion adds the result submission to the whole process of the system The teacher is the external entity here which is submitting the result, the result collection process is numbered 3.0, result is submitted by the teacher in parts, i.e. –separately for assignments, quizzes, tests, sessional and final result. The Collection process then forward the collected result to the Calculate GP Process, this process calculates the Grade point for the subject, the result with GP calculated is then moved forward to the update result process which then makes a change in the result data store by updating the result data for that specific student.

Level 0 Diagram 2After the process of result submission the result for all the subjects is taken and the GPA is calculated, once the GPA is calculated the it is used for further calculation of CGPA and is forwarded to another process which is numbered 7.0 this process will calculate the CGPA by taking all the results of the current and previous semesters.
Further detailed diagram i.e.—Detailed DFD can be created using the given level 0 DFD and by expanding all the Processes further.

Cross Reference Matrix: doth:

This matrix is used to find out that what values or attributes will appear in which reports, for this purpose we write the major item names on a matrix in the row wise order and the reports which will be generated will be written on top or in column wise order.

Cross Reference Matrix

Cross Reference Matrix

This process infact is just cross link So the first Item transcript which may be or it will be needed by a specific student, second is Result card, next is attendance sheet then we have Class result (Subject wise) and finally the Class result as a whole, here by subject wise class result means that all the results of a specific class for a specific student considering each component, such as assignments, quizzes, sessional and terminal results.
Similarly all the mentioned items are marked with a tick which may needed by a certain output.
Let us see how the DFD and CRM are used in creating the ER-Diagram The process of Creating ER-Diagram in fact lies in the Analysis phase and is started with identifying different entities which are present in the system. For this purpose we can use the DFD first of all.
Lets check our DFD, from there we can find the following entities.

Student Controller

Courses Teachers

Courses Offered Programs

Registration Results


Here the point to be noted is that, we have picked the controller as the entity, although the controller is acting as an external entity for providing or getting information from the system, but in case of ER-Diagram the controller can not be represented as an entity because there is only one controller in any examination system and for such an entity instances a complete entity is not used. So in this way we can exclude the controller entity, we will also take care of other entities before including them in our ED-Diagram. Another such example is results, which may not be as it is, added to the ER-Diagram, because there can be a number of result types at different stages of the Process, so there will be a number of different results. We use our CRM in creating the ER-Diagram, because when we see the CRM, it has a number of item/attributes appearing on it, now from there we can see that whether these items belong to the same entity or more than one entity. And even if they belong to multiple entities we can find the relationship existing between those entities. Considering our CRM we have transcript, it has a number of items appearing on it , as we know that there is to appear result for each semester on the transcript. So the attributes which belong to the personal information of the student shall be placed in the student entity and the data which belongs to the students’ academic data will be placed in the courses or results entity for that student.

In the next phase we have to draw different entity type and the relationship which exist between those entities.

These we will discuss in the next lecture that how we draw relationships between different entities.