ZeePedia Add to Favourites   |   Contact us


Database Management Systems

<<< Previous Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities Next >>>
 
img
Database Management System (CS403)
VU
Lecture No. 13
Reading Material
Case Study
Overview of Lecture
E ­ R Diagram of Examination System
o
Conceptual Data Base Design
o
Relationships and Cardinalities between the entities
o
In the previous lecture we discussed the Preliminary phase of the Examination
o
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
(0
- *). 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.
1
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
img
Database Management System (CS403)
VU
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-*
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
img
Database Management System (CS403)
VU
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:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules