Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities

<< Steps in the Study of system

Relational Data Model, Mathematical Relations, Database Relations >>

Database Management System (CS403)

Lecture No. 13

Reading Material

Case Study

Overview of Lecture

E R Diagram of Examination System

Conceptual Data Base Design

Relationships and Cardinalities between the entities

In the previous lecture we discussed the Preliminary phase of the Examination

system. We discussed the outputs required from the system and then we drew

the data flow diagrams DFDs. From this lecture we will start the conceptual

model of the system through E-R Diagram.

Identification of Entity Types of the Examination System

We had carried out a detailed preliminary study of the system, also drawn the data

flow diagrams and then identified major entity types. Now we will identify the major

attributes of the identities, then we will draw the relationships and cardinalities in

between them and finally draw a complete E-R Diagram of the system.. So first of all

we will see different attributes of the entities.

Program:

This entity means that what different courses are being offered by an institute, like

MCS, BCS etc. Following are the major attributes of this entity:-

o pr_Code. It can be used as a primary key of the entity as it would always be

unique for example MBA, MCS, etc.

o max_Dur It means that what is the maximum duration of any particular

course , like 1 year , 2 years and so on.

o no_of_Semesters How many semesters this program has like four ,six and so

on.

o Pr_Lvl This course is of undergraduate, graduate or post graduate level.

Student:

118

Database Management System (CS403)

Following are the major attributes of this entity:-

o Reg_No This can be used as a primary key for this entity as it will be unique

for every student.

o st_Name This would be the first name of all the students of an institute.

o St_Father_name This would represent the father's name of a student.

o St_date_of_Birth. The date of birth of all students including year , month

and day.

o st_Phone_no

o st_GPA This is a very important attribute. Now to know the GPA of any

student, we need to know the student reg no and the particular semester. So

this is a multi valued attribute as to know the GPA, different attributes values

are required. So this represented by a relation, which will be discussed in the

relationships in between entities.

o st_Subj_Detail This is also a multi valued attribute ,as to know the marks in

mid terms and final papers , student reg no and the particular subject are

required

Teacher:

Following are the major attributes of this entity: -

o teacher_Reg_No This can be used as a primary key for this entity as it will be

unique for every teacher.

o teacher_Name This would be the first name of all the teachers of an institute.

o teacher_Father_name This would represent the father's name of a teacher.

o Qual. The qualification of a teacher like Masters or Doctorate.

o Experience This can also be a multi-valued attribute or a single valued

attribute. If only total experience of any teacher is required then it can be

single valued, but if details are required as per the different appointments, then

in that case it would be multi valued.

o teacher_Sal The total salary of the teacher.

There is one thing common in between teacher and student an entity that is the

personal details of both, like name, father's name and addresses.

Course:

Following are the major attributes of this entity: -

o course_Code This can be used as a primary key for this entity as it will be

unique for every course like CS-3207.

o course_Name

o course_Prereq This would also be a multi valued attribute as there can be a

multiple requisites of any course . For example, Networking can have pre-

requisites of Operating System and Data Structures. In this case this is a

119

Database Management System (CS403)

recursive relation as pre-requisite of a course is a course. We will treat it as a

recursive relation here.

o Courses_Offered_in This is also a multi valued attribute as to know the

courses offered , program and semester both are required so this can also be

represented by a relation

Semester:

Following are the major attributes of this entity: -

o semester_Name This can be used as a primary key for this entity as it will be

unique for every semester like fall 2003 or spring 2004.

o semester_Start_Date The starting date of the semester

o semester_End_Date The ending date of the semester

Derived Attributes

There are certain attributes in the examination system which is derived like CGPA of

a student can only be achieved from the semesters GPA. Similarly FPA of any

particular semester can be achieved from subjects GPA of the semester. So this has to

be kept in mind while drawing the E-R Diagram of the system.

Relationships and Cardinalities in between Entities

Relationships and cardinalities in between entities is very important. We will now see

the relationship of different entities one by one. The block diagrams of different

entities are as under: -

COURSES

PROGRAM

CRS_OFFERED

STUDENT

SEMESTER

TEACHER

120

Database Management System (CS403)

Program and Courses

The relationship between program and courses is that, if we want to know the courses

in any particular program then the course codes and program codes are required. The

cardinality between program and courses is of one to many (1 -*) , which means that

any program will have minimum one course, and as many as required for that

particular program. The cardinality of courses and program can be zero to many

- *). It means that if an institution wants, it can have a course without even any

program as well. This course can be included in any program later on.

1-*

PROGRAM

COURSES

0-*

Students and Programs

The cardinality in between student and program is one, which means that every

student can have minimum and maximum one program at any time. The cardinality in

between programs and students can be zero to many (0 - *), which means that

depending upon the requirements of any organization it can have a program which is

presently not being offered to any students.

STUDENTS

PROGRAM

0-*

Semester and Course

The relationship in between semester and course is many to many. But it is essential

to know the course offered during any particular semester so there is a requirement of

an attribute, which is of relationship and when it is many to many it, can also serve as

entity which is represented by a diamond in a rectangle. So here this can be a courses

offered attribute, which would also be an entity. The primary key of semester that is

semester code and primary key of course that is course code, after combining it

becomes composite key which would be used to identify any particular course.

Course Offered and Teacher

There is a relationship in between course offered and teacher. The cardinality of

course offered and teacher is one that is a teacher can only have one course at a time.

Similarly the cardinality in between teacher and course offered is one to many, which

means that a teacher can teach many courses.

121

Database Management System (CS403)

Student and Course Offered

The relationship in between student and course offered is many to many so this

relationship is also through enrolled attribute, which can also serve as entity type. The

primary keys of semester, course and student are used as composite key to identify,

that which student has got which course.

Semester and Student

To find out GPA of any student the semester is also required to be known. So the

relationship in between these two can be through result whose attribute GPA can be

used. There is a many to many relationship in these two entities.

1-*

COURSE OFFERED

1-*

0-*

COURSE

TEACHER

SEMESTER

1-*

GPA

ENROLLED

STUDENT

1-*

Conceptual Database Design

The outcome of analysis phase is the conceptual database design, which is drawn

through E-R model. This design is independent of any tool or data model. This design

can be implemented in multiple data models like network, relational or hierarchal

models.

Logical Database Design

This is the next phase of database design, in which appropriate data model is chosen,

and from here onwards it becomes tool dependent. We will select relational data

model and our further lectures will be concerning relational data models

122

Database Management System (CS403)

Conclusion

The E R Model of Examination system of an educational institute discussed above

is just a guideline. There can certainly be changes in this model depending upon the

requirements of the organization and the outputs required. After drawing an E-R

model, all the outputs, which are required, must be matched with the system. If it does

not fulfill all the requirements then whole process must be rehashed once again. All

necessary modifications and changes must be made before going ahead. For Example,

if in this system attendance sheet of the students is required then program code,

semester and course codes are required, this composite key will give the desired

attendance sheet of the students.

123

Table of Contents: