B.C.A. (Sem.

- I) Database Management
Systems

Database
Management System 2
2.1. Basic Concepts and Definitions in DBMS
2.2. DBMS
2.3. Views of Data
2.4. Data Model
2.5. Entity Relationship Diagram
2.6. Overall System Structure

Introduction :
A database is an organized collection of data. A database management
system (DBMS) is a collection of interrelated data and a set of programs to
access those data. This is a collection of related data with an implicit meaning
and hence is a database. A database view is a subset of the database sorted
and displayed in a particular way. A data model is a collection of high-level data
description constructs that hide many low-level storage details. A DBMS allows a
user to define the data to be stored in terms of a data model.

2.1 Basic Concepts and Definition in DBMS :

DBMS Stands for "Database Management System." In short, a DBMS is a
database program. Technically speaking, it is a software system that uses a
standard method of cataloging, retrieving, and running queries on data. This
section consists of basic concepts and definitions related to DBMS such as data
and information, data dictionary, record.

2.1
 B.C.A. (Sem. - I) Database Management
Systems

2.1.1 Data and Information:

Data is a raw fact. The word raw indicates that the facts have not yet been
processed to reveal their meaning. For example, suppose that a university tracks
data on faculty members for reporting to accrediting bodies. Information can be
used as the foundation for decision making. For example, the data summary for
the faculty can provide accrediting bodies with insights that are useful in
determining whether to renew accreditation for the university.
A) Data:
Data can be defined as a representation of facts, concepts or instruction in a
formalized manner which should be suitable for communication, interpretation
or processing by human or electronic machine. Data is represented with the
help of characters like alphabets (A-Z, a-z), digits (0-9) or special characters
(+,-, /,*, <,>, = etc).To get the data for each faculty member into the database,
it would provide a screen to allow for convenient data entry, complete with
drop-down lists, combo boxes, option buttons, and other data-entry validation
controls.
B) Information:
Information is organized or classified data so that it has some meaningful
values to the receiver. Information is a term with many meanings depending
on context, but is as a rule closely related to such concepts as meaning,
knowledge, instruction, communication, representation, and mental
stimulus. Information is the processed data on which decisions and actions
are based.
Characteristics of Information:
Data must qualify for the following characteristics:
a) Timely :
Information should be available when required.
b) Accuracy :
Information should be accurate.
c) Completeness :
Information should be complete.

2.1.2 Data vs. Information :

Data are plain facts. When data are processed, organized, structured or
presented in a given context so as to make them useful, they are called
Information.

2.2
 B.C.A. (Sem. - I) Database Management
Systems

Sr.No Points Data Information

1 Meaning Data usually refers to Information is "knowledge
raw data, or communicated or received
unprocessed data. It is concerning a particular fact
the basic form of data, or circumstance." Information
data that hasn’t been is a sequence of symbols
analyzed or processed that can be interpreted as a
in any manner. message.
2 Input/output Data is used as input for Information is the output of
the computer system. data.
3 Specificatio Data is not specific. Information is specific.
n
4 Example Each student's test The class' average score or
score is one piece of the school's average score is
data. the information that can be
concluded from the given
data.

2.1.3 Data Dictionary:

The data dictionary contains the data definition and its characteristics and
entity relationships. This may include the names and descriptions of the various
tables and fields within the database.

Fig 2.1 : Data Dictionary

2.3
 B.C.A. (Sem. - I) Database Management
Systems

A) Meaning:
Data dictionary is an integral part of database. The data dictionary (or data
repository) or system catalog is an important part of the DBMS. It contains
data about data (or metadata). It means that it contains the actual database
descriptions used by the DBMS. In most DBMS, the data dictionary is active
and integrated. It means that the DBMS checks the data dictionary every time
the database is accessed. A DBMS component is that stores metadata. It is
the self-describing nature of the database that provides program-data
independence. It is a tool for recording and processing information
(metadata) about the data that an organization uses.

B) Contents of Data Dictionary:

The data dictionary contains the following information:
1) Logical structure of database.
2) Schemas, mappings and constraints.
3) Description about application programs.
4) Descriptions of record types, data item types, and data aggregates in the
database.
5) Description about physical database design, such as storage structures,
access paths etc.
6) Descriptions about users of DBMS and their access rights.

2.1.4 Data Item or Field:

A field contains an item of data; that is, a character, or group of characters
that are related. For instance, a grouping of related text characters such as "John
Smith" makes up a name in the name field. Let's look at another example:
Suppose a group of senior citizens going to a picnic. For each person, they must
identify the name, address, city, state, zip code and telephone number. A field
would be established for each type of information in the list. The name field
would contain all of the letters of the first and last name. The zip code field would
hold all of the digits of a person's zip code, and so on. In summary, a field may
contain an attribute (e.g., employee salary) or the name of an entity (e.g., person,
place, or event).

2.4
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.2 : Field

2.1.5 Record:
A record is a collection of all the facts related to one physical or
conceptual entity; often referring to a single object or person, usually represented
as a row of data in a table, and sometimes referred to as a tuple in some,
particularly older, database management systems. A record is composed of a
group of related fields. As another way of saying it, a record contains a collection
of attributes related to an entity such as a person or product. Looking at the list of
potential gun control supporters, the name, address, zip code and telephone
number of a single individual would constitute a record. A payroll record would
contain the name, address, social security number, and title of each employee.

2.5
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.3 : Record

2.2 Database Management System(DBMS) :

A database management system (DBMS) can be an extremely complex set
of software programs that controls the organization, storage and retrieval of data
(fields, records and files) in a database. It also controls the security and integrity
of the database. DBMS is a suite of programs which typically manage large
structured sets of persistent data, offering adhoc query facilities to many users.
The DBMS accepts requests for data from the application program and instructs
the operating system to transfer the appropriate data. They are widely used in
business applications. When a DBMS is used, information systems can be
changed much more easily as the organization’s information requirements
change. New categories of data can be added to the database without disruption
to the existing system.

Fig 2.4 : Structure of DBMS

2.2.1 Definition of DBMS :

2.6
 B.C.A. (Sem. - I) Database Management
Systems

A Database Management System is a large software package that controls

the specification, organization, storage, retrieval and update of data in a
database. DBMS is defined by many authors some of them are given below:

1) Dr. Naphtali Rishe :

"An updatable storage of information of an application's world and managing
software that conceals from the user the physical aspects of information
storage and information representation".
2) C.J. Date :
“A database is a collection of persistent data that is used by the application
systems of a given enterprise” .

2.2.2 Applications of DBMS:

Database systems are widely used in different areas because of their
numerous advantages. Some of the most common database applications are
listed here:
1) Airlines and Railways:
Airlines and railways use online databases for reservation, and for displaying
the schedule information.
2) Banking:
Banks use databases for customer inquiry, accounts, loans, and other
transactions.
3) Education:
Schools and colleges use databases for course registration, result, and other
information.
4) Telecommunications:
Telecommunication departments use databases to store information about
the communication network, telephone numbers, record of calls, for
generating monthly bills, etc.
5) Credit Card Transactions:
Databases are used for keeping track of purchases on credit cards in order to
generate monthly statements.
6) E-Commerce:
Integration of heterogeneous information sources (for example, catalogs) for
business activity such as online shopping, booking of holiday package,
consulting a doctor, etc.
7) Health Care Information Systems and Electronic Patient Record:

2.7
 B.C.A. (Sem. - I) Database Management
Systems

Databases are used for maintaining the patient health care details.
8) Digital Libraries and Digital Publishing:
Databases are used for management and delivery of large bodies of textual
and multimedia data.
9) Finance:
Databases are used for storing information such as sales, purchases of
stocks and bonds or data useful for online trading.
10) Sales:
Databases are used to store product, customer and transaction details.
11) Human Resources:
Organizations use databases for storing information about their employees,
salaries, benefits, taxes, and for generating salary checks.

2.2.3 File Processing vs. DBMS:

Differences between a file-processing system and a DBMS

Sr.N Points
DBMS File processing System
o
1 Meaning A Database File processing system is a
Management System system used to store and
(DBMS) is a set of manage data that involves
computer programs that each department or area
controls the creation, within an organization
maintenance, and the having its own set of files,
use of the database of often creating data
an organization and its redundancy and data
end users. isolation.
2 Data A database File-processing system is
Access management system is designed to allow
designed to allow predetermined access to
flexible access to data data (i.e., compiled
(i.e., queries). programs).

2.8
 B.C.A. (Sem. - I) Database Management
Systems

3 Redundanc Redundancy is Data redundancy &

y controlled. inconsistency

4 Security Providing backup and Security Problems.

recovery.

5 Backup and No backup and recovery.

DBMS provide backup
recovery
and recovery.

6 Authority Unauthorized access is Unauthorized access is not

restricted in DBMS. restricted

2.2.4 Advantages and Disadvantages of DBMS:

A) Advantages:
Advantages of DBMS are as follows:
1) Improved Availability:
One of the principle advantages of a DBMS is that the same information can
be made available to different users.
2) Minimized Redundancy:
The data in a DBMS is more concise because, as a general rule, the
information in it appears just once. This reduces data redundancy, or in other
words, they need to repeat the same data over and over again. Minimizing
redundancy can therefore significantly reduce the cost of storing information
on hard drives and other storage devices.
3) Accuracy:
Accurate, consistent, and up-to-date data is a sign of data integrity. DBMS
foster data integrity because updates and changes to the data only have to
be made in one place. The chances of making a mistake are higher if it
required changing the same data in several different places than if it only has
to make the change in one place.
4) Program and File Consistency:
Using a database management system, file formats and system programs
are standardized. This makes the data files easier to maintain because the

2.9
 B.C.A. (Sem. - I) Database Management
Systems

same rules and guidelines are apply across all types of data. The level of
consistency across files and programs also makes it easier to manage data,
when multiple programmers are involved.
5) User-Friendly:
Data is easier to access and manipulate with a DBMS than without it. In most
cases, DBMS also reduce the reliance of individual users on computer
specialists to meet their data needs.
6) Improved Security:
DBMS allow multiple users to access the same data resources. This
capability is generally viewed as a benefit, but there are potential risks for the
organization. Some sources of information should be protected or secured
and only viewed by selected individuals. Through the use of passwords,
database management systems can be used to restrict data access to only
those who should see it.
7) Data Atomicity:
A transaction in commercial databases is referred to as atomic unit of work.
For example, when someone purchases something from a point of sale
(POS) terminal, a number of tasks are performed such as;
i) Company stock is updated.
ii) Amount is added in company's account.
iii) Sales person's commission increases etc.
All these tasks collectively are called as atomic unit of work or transaction.
These tasks must be completed in all otherwise partially completed tasks are
rolled back. Thus, through DBMS, it is ensured that only consistent data
exists within the database.
8) Database Access Language:
Most of the DBMS provide SQL as standard database access language. It is
used to access data from multiple tables of a database.
9) Report Writers:
Most of the DBMS provide the report writer tools used to create reports. The
users can create reports very easily and quickly. Once a report is created, it
can be used many times and it can be modified very easily. The created
reports are also saved along with database and behave like a software
component.

2.10
 B.C.A. (Sem. - I) Database Management
Systems

B) Disadvantages:
1) Security:
Even with safeguards in place, it may be possible for some unauthorized
users to access the database. In general, database access is an all or
nothing proposition. Once an unauthorized user gets into the database, they
have access to all the files, not just a few. Depending on the nature of the
data involved, these breaches in security can also pose a threat to individual
privacy. Steps should also be taken to regularly make backup copies of the
database files and store them because of the possibility of fires and
earthquakes that might destroy the system.
2) Cost of Hardware & Software:
A processor with high speed of data processing and memory of large size is
required to run the DBMS software. It means that user have to upgrade the
hardware used for file-based system. Similarly, DBMS software is also Very
costly.
3) Cost of Data Conversion:
When a computer file-based system is replaced with a database system, the
data stored into data file must be converted to database files. It is difficult and
time consuming method to convert data of data files into database. User have
to hire DBA (or database designer) and system designer along with
application programmers; alternatively, he have to take the services of some
software houses. So, a lot of money has to be paid for developing database
and related software.
4) Cost of Staff Training:
Most DBMS are often complex systems so the training for users to use the
DBMS is required. Training is required at all levels, including programming,
application development, and database administration. The organization has
to pay a lot of amount on the training of staff to run the DBMS.
5) Appointing Technical Staff:
The trained technical persons such as database administrator and application
programmers, etc. are required to handle the DBMS. Organization has to pay
handsome salaries to these persons. Therefore, the systems cost increases.
6) Database Failures:

2.11
 B.C.A. (Sem. - I) Database Management
Systems

In most of the organizations, all data is integrated into a single database. If

database is corrupted due to power failure or it is corrupted on the storage
media, then our valuable data may be lost or whole system stops.

2.2.5 Users of DBMS:

A primary goal of a database system is to retrieve information from and store
new information into the database. People who work with a database can be
categorized as database users or database administrators. There are four
different types of database-system users, differentiated by the way they expect to
interact with the system. Different types of user interfaces have been designed
for the different types of users.
A) Database Designer:
Database Designers come into picture before a database is built. They gather
requirements from all prospective users; level of access required for each
user or a group and designs the DB which meets the requirement. They are
responsible for identifying the data to be populated and appropriate data
structures for the database. They are generally part of Database
administrators.
Responsibilities of Database Designer:
1) Identification of Data:
Database designers are responsible for identifying the data to be stored in
the database and for choosing appropriate structures to represent and store
this data. These tasks are mostly undertaken before the database is actually
implemented and populated with data.
2) Communication with Database User:
It is the responsibility of database designers to communicate with all
prospective database users in order to understand their requirements and to
create a design that meets these requirements.
3) Interaction:
Database designers typically interact with each potential group of users and
develop views of the database that meet the data and processing
requirements of these groups. Each view is then analyzed and integrated with
the views of other user groups. The final database design must be capable of
supporting the requirements of all user groups.
B) Application Programmers or Ordinary Users:

2.12
 B.C.A. (Sem. - I) Database Management
Systems

These users write application programs to interact with the database.

Application programs can be written in some programming language such a
COBOL, PL/I, C++, JAVA or some higher level fourth generation language.
Such programs access the database by issuing the appropriate request,
typically a SQL statement to DBMS. Application programmers implement
these specifications as programs; then they test, debug, document, and
maintain these canned transactions. Such analysts and programmers
commonly referred to as software Developers or software engineers should
be familiar with the full range of Capabilities provided by the DBMS to
accomplish their tasks.

Role of Application Programmers:

Application Programmers are responsible for writing application programs
that use the database. These programs could be written in General Purpose
Programming languages such as Visual Basic, Developer, C, FORTRAN,
COBOL etc. to manipulate the database. These application programs operate
on the data to perform various operations such as retaining information,
creating new information, deleting or changing existing information.
C) Sophisticated User:
Sophisticated users interact with the system without writing programs.
Instead, they form their requests in a database query language. They submit
each such query to a query processor, whose function is to break down DML
statements into instructions that the storage manager understands. Analysts
who submit queries to explore data in the database fall in this category.
D) End Users:
End users are the users, who use the applications developed. End users
need not know about the working, database design, the access mechanism
etc. They just use the system to get their task done. End users are of two
types:
1) Direct Users:
Direct users are the users who see the computer, database system directly
by following instructions provided in the user interface. They interact using
the application programs already developed, for getting the desired result.
E.g. People at railway reservation counters, who directly interact with
database.

2.13
 B.C.A. (Sem. - I) Database Management
Systems

2) Indirect Users:
Indirect users are those users, who desire benefit from the work of DBMS
indirectly. They use the outputs generated by the programs, for decision
making or any other purpose. They are just concerned with the output and
are not bothered about the programming part.

2.3 Views of Data :

In database management systems, a view is a particular way of looking at a
database. A single database can support numerous different views. Typically, a
view arranges the records in some order and makes only certain fields visible.
Note that the different views do not affect the physical organization of the
database. A database management system provides the ability for many different
users to share data and process resources. But as there can be many different
users, there are many different database needs. A DBMS minimizes these
problems by providing two views of the database data: a physical view and a
logical view.
Levels of Abstraction in a DBMS:

Fig 2.5: The Three Levels of Database Abstraction

1) Physical Level:
The lowest level of abstraction describes how the data are actually stored.
The physical level describes complex low-level data structures in detail.
2) Logical Level:

2.14
 B.C.A. (Sem. - I) Database Management
Systems

The next-higher level of abstraction describes what data are stored in the
database, and what relationships exist among those data. The logical level
describes the entire database in terms of a small number of relatively simple
structures. Although implementation of the simple structures at the logical
level may involve complex physical-level structures, the user of the logical
level does not need to be aware of this complexity. Database administrators,
who must decide what information to keep in the database, use the logical
level of abstraction.
3) View Level:
The highest level of abstraction describes only part of the entire database.
Even though the logical level uses simpler structures, complexity remains
because of the variety of information stored in a large database. Many users
of the database system do not need all this information; instead, they need to
access only a part of the database. The view level of abstraction exists to
simplify their interaction with the system. The system may provide many
views for the same database.

2.4 Data Model :

A data model provides a way to describe the design of a database at the
physical, logical, and view levels. A database is a collection of interrelated data
and the way data is related to each other depends upon the model being used.
Data model is a way of storing and retrieving the data. Underlying the structure of
a database is the data model: a collection of conceptual tools for describing
data, data relationships, data semantics, and consistency constraints. The
following are the basic type’s data models:

2.15
 B.C.A. (Sem. - I) Database Management
Systems

Data
Model

Object based logical Record based logical

model model

Object oriented data Entity relationship data

model model

Relational Network Hierarchical

model model model

Fig 2.6 : Types of Data Model

2.4.1 Object Based Logical Model:

Object based data models use concepts such as entities, attributes, and
relationships. Object based logical models provide flexible structuring capabilities
and allow data constraints to be specified explicitly. An entity is a distinct object
(a person, place, concept, and event) in the organization that is to be
represented in the database. An attribute is a property that describes some
aspect of the object that wish to record, and a relationship is an association
between entities. Object based data adds database functionality to object
programming languages. They bring much more than persistent storage of
programming language objects.

Object based Logical Model

Object oriented Data Model Entity Relationship Data Model

MoMoModel
Fig 2.7 : Object based logical model

2.16
 B.C.A. (Sem. - I) Database Management
Systems

A) Object Oriented Data Model:

The object-oriented data model also supports a rich type system, including
structured and collection types. In the 1980, several database systems based
on the object-oriented data model were developed. The major database
vendors presently support the object-relational data model, a data model that
combines features of the object-oriented data model and relational data
model. It extends the traditional relational model with a variety of features
such as structured and collection types, as well as object orientation.
Basic Object-Oriented Concepts:
1) Object:
An object is a software bundle of related state and behavior. Software objects
are often used to model the real-world objects that in everyday life.
2) Attributes and Methods:
Every object has a state (the set of values for the attributes of the object) and
a behavior (the set of methods - program code - which operate on the state of
the object). The state and behavior encapsulated in an object are accessed
or invoked from outside the object only through explicit message passing.
3) Class:
A class is a blueprint or prototype from which objects are created. This
defines a class that models the state and behavior of a real-world object. It
intentionally focuses on the basics, showing how even a simple class can
cleanly model state and behavior.
4) Inheritance :
Inheritance provides a powerful and natural mechanism for organising and
structuring software. This explains how classes inherit state and behavior
from their super classes, and explains how to derive one class from another
using the simple syntax provided by the Java programming language.
B) Entity Relationship Data Model:
An Entity-relationship model (ERM) is an abstract and conceptual
representation of data. ER modeling is a DB modeling method, used to
produce a type of conceptual schema of a system. Diagrams created by this
process are called ER diagrams. The entity-relationship (ER) data model
allows us to describe the data involved in a real-world enterprise in terms of
objects and their relationships and is widely used to develop an initial
database design. The ER model is important primarily for its role in database
design. It provides useful concepts that allow us to move from an informal

2.17
 B.C.A. (Sem. - I) Database Management
Systems

description of what users want from their database to a more detailed and
precise, description that can be implemented in a DBMS.
Elements of Entity Relationship Model:
1) Entity:
An Entity is a person, place, object, event, or concept that an organization
wants to maintain data on. Each entity has a unique identity that differentiates
it from other entities. A point of distinction must be made between entity types
and entity instances.
2) Attribute:
An Attribute is a characteristic of an entity that is relevant to the organization.
When defining an attribute, an analyst should state why the attribute is
important, what is included in the attribute’s value, the source of the value,
and whether or not that value can change.

City
Surname

Name Person
Fig 2.8: Person entity have Name, surname, city, state
Stateare attributes

3) Relationship:
A relationship represents some association between two or more entities. In
an E-R diagram, a relationship is represented as a diamond shape,
containing the name of the relationship between the entities. Returning to our
final year project database relations from earlier,
STUDENT (ID, Surname, Forename, Programme, Date_of_Birth).
PROJECT (Proj-ID, Project-Title, Student-ID, Year).
It can represent the relationship between these relations as follows:
1 1 1
Student Assigned Projects

Fig : 2.9
In above diagram it can show the Connectivity of the relationship, which in
this case is a one-to-one (1:1) relationship. The one-to-one relationship

2.18
 B.C.A. (Sem. - I) Database Management
Systems

shown reflects the fact that each student is assigned (at most) one project
and that each project is assigned to (at most) one student.

2.4.2 Record Based Logical Model:

Record based logical models are used in describing data at the logical and
view levels. In contrast to object based data models, they are used to specify the
overall logical structure of the database and to provide a higher-level description
of the implementation. Record based models are so named because the
database is structured in fixed format records of several types. Each record type
defines a fixed number of fields, or attributes, and each field is usually of a fixed
length.
The three most widely accepted record based data models are:
A) Relational Model:
In the relational model, the table in a database has fixed record length with
fixed number of attributes or fields. The Relational Model was the first
theoretically founded and well thought out Data Model, proposed by E. F.
Codd in 1970, then a researcher at IBM. It has been the foundation of most
database software and theoretical database research ever since. The basic
idea behind the relational model is that a database consists of a series of
unordered tables (or relations) that can be manipulated using non-procedural
operations that return tables. This model was in vast contrast to the more
traditional database theories of the time that were much more complicated,
less flexible and dependent on the physical storage methods of the data. The
relational database model is based on the Relational Algebra, set theory and
predicate logic. Relational databases work on the principle that each table
has a key field that uniquely identifies each row, and that these key fields can
be used to connect one table of data to another.

2.19
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.10 : Relational model

B) Network Model:
A network model is represented in the form of records and links. However,
records in a database are represented graphically. The Network model
replaces the hierarchical tree with a graph thus allowing more general
connections among the nodes. The main difference of the network model
from the hierarchical model, is its ability to handle many to many (N:N)
relations. In other words, it allows a record to have more than one parent.
Suppose an employee works for two departments. The strict hierarchical
arrangement is not possible here and the tree becomes a more generalized
graph - a network. The network model was evolved to specifically handle
non-hierarchical relationships. As shown below data can belong to more than
one parent. In network database terminology, a relationship is a set. Each set
is made up of at least two types of records: an owner record (equivalent to
parent in the hierarchical model) and a member record (similar to the child
record in the hierarchical model).

2.20
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.11 : Network model

C) Hierarchical Model:
In a Hierarchical model, data is represented by a collection of records, and
relationships between the data are represented by links. Hierarchical
Database model is one of the oldest database models, dating from late
1950s. One of the first hierarchical databases Information Management
System (IMS) was developed jointly by North American Rockwell Company
and IBM.
In this model, data is stored in the form of a tree. The data is represented by
parent-child relationship. Each tree contains a single root record and one or
more subordinate records. For example, each batch is root and students of
the batch will be subordinates. This model supports only one-to-many
relationship between entities. This structure implies that a record can have
repeating information, genera-lly in the child data segments. Data is a series
of records, which have a set of field values attached to it. It collects all the
instances of a specific record together as a record type.

2.21
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.12 : Hierarchical model

2.5 Entity Relationship Diagram :

The Entity-Relationship (ER) model was originally proposed by Peter in 1976
as a way to unify the network and relational database views. A basic component
of the model is the Entity-Relationship diagram which is used to visually
represent data objects. An entity relationship diagram is a graphical
representation of an organization’s data storage requirements. Entity relationship
diagrams are abstractions of the real world which simplify the problem to be
solved while retaining its essential features. Entity relationship diagrams are used
to identify the data that must be captured, stored and retrieved in order to support
the business activities performed by an organization; and identify the data
required to derive and report on the performance measures that an organization
should be monitoring.
A) Meaning:
Entity relationship diagram is a graphical representation of the entities and
the relationships between them. Entity relationship diagrams are a useful
medium to achieve a common understanding of data among users and
application developers. In data modeling, an Entity-Relationship Model (ERM)
is a representation of structured data; entity-relationship modeling is the
process of generating these models. The end-product of the modeling
process is an entity-relationship diagram (ERD), a type of Conceptual Data
Model or Semantic Data Model.

2.22
 B.C.A. (Sem. - I) Database Management
Systems

B) Structure of Entity Relationship Diagram:

Attribute Attribute
Attribute
Attribute

Entity Relationship Entity

Relationship Relationship

Entity

Attribute
Attribute

Attribute

Fig 2.13 : Structure of Entity Relationship Diagram

C) Notations of Entity Relationship Diagram:

1) Entity:
Entity

Symbol of Entity:
An entity is an object or concept about use to store information. An entity is a
“thing” or “object” in the real world that is distinguishable from all other

2.23
 B.C.A. (Sem. - I) Database Management
Systems

objects. For example, each person in a university is an entity. An entity has

a set of properties, and the values for some set of properties may uniquely
identify an entity. An entity set is a set of entities of the same type that share
the same properties, or attributes. The set of all people who are instructors at
a given university, it can be defined as the entity set instructor. Similarly, the
entity set student might represent the set of all students in the university.
Example:
In this example Customer, Order, Items, Invoice, Payment are the entities of
invoice system.

Fig 2.14 : Entity

2) Weak Entity:
Symbol of Weak Entity:
Entity

The entity set which does not have sufficient attributes to form a primary key
is called as Weak entity set. In a database, a Weak Entity is an entity that
cannot be uniquely identified by its attributes alone; therefore, it must use a
foreign key in conjunction with its attributes to create a primary key.
Consider an entity set Payment which has three attributes: payment_number,
payment_date and payment_amount. Although each payment entity is
distinct but payment for different loans may share the same payment number.

2.24
 B.C.A. (Sem. - I) Database Management
Systems

Thus, this entity set does not have a primary key and it is an entity set. Each
weak set must be a part of one-to-many relationship set.
Example:
1 M
Building has Room

Fig 2.15: Weak Entity

In above example, building is a strong entity upon which rooms are

dependent. So, room entity is a weak entity
3) Attribute:

Symbol of Attribute: Attribute

An attribute is the unique, distinguishing characteristic of the entity. An entity

is represented by a set of attributes. Attributes are descriptive properties
possessed by each member of an entity set. The designation of an attribute
for an entity set expresses that the database stores similar information
concerning each entity in the entity set; however, each entity may have its
own value for each attribute. Possible attributes of the instructor entity set are
ID, name, dept name, and salary.
Example:
1) Employee's social security number might be the employee's key attribute.

Subject_Name
RollNo Name

Surname

have
Student Subject
1 M

Address

SubjectNo
Fig 2.16 : Attribute

2.25
 B.C.A. (Sem. - I) Database Management
Systems

In above diagram, one student have many subjects, in that case Name,
RollNo, Surname, Address are attributes of Students. Subject_Name,
SubjectNo are attributing of entity Subject.
4) Multivalued Attribute:
Symbol of Multivalued Attribute:
Attribute

A multivalued attribute can have more than one value. For example, an
employee entity can have multiple skill values. An instructor may have zero,
one, or several phone numbers, and different instructors may have different
numbers of phones. This type of attribute is said to be multivalued.
Example:
a) A customer can have multiple phone numbers, email id's etc
b) A person may have several college degrees.
5) Derived Attribute:
Symbol of Derived Attribute: Attribute

A derived attribute is based on another attribute. For example, an employee's

monthly salary is based on the employee's annual salary. The value for this
type of attribute can be derived from the values of other related attributes or
entities. For instance, let us say that the instructor entity set has an attribute
students advised, which represents how many students an instructor advises.
It can derive the value for this attribute by counting the number of student
entities associated with that instructor.
Example:
In this diagram Age is depend on Birthdate of a person.

2.26
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.17 : Derived Attribute

6) Relationships:
Symbol of Relationship: Relationship

A Relationship is an association among several entities. A relationship is how

the data is shared between entities. Relationships illustrate how two entities
share information in the database structure.
Types of Relationships:
There are three types of relationships between entities:
a) One-to-One:
One instance of an entity (A) is associated with one other instance of another
entity (B). For example, in a database of employees, each employee name
(A) is associated with only one social security number (B).
Example:
1 make 1
Student Project
Fig 2.18 : (a)
b) One-to-Many:
One instance of an entity (A) is associated with zero, one or many instances
of another entity (B), but for one instance of entity B there is only one
instance of entity A. For example, for a company with all employees working
in one building, the building name (A) is associated with many different
employees (B), but those employees all share the same singular association
with entity A.

2.27
 B.C.A. (Sem. - I) Database Management
Systems

Example:
1 M
Teacher teaches Student

Fig 2.18: (b)

c) Many-to-Many:
One instance of an entity (A) is associated with zero, one or many instances
of another entity (B), and one instance of entity B is associated with one, zero
or many instances of entity A. For example, for a company in which all of its
employees work on multiple projects, each instance of an employee (A) is
associated with many instances of a project (B), and at the same time, each
instance of a project (B) has multiple employees (A) associated with it.
Example:
Many students read many books.
M read M
Student Books

Fig 2.18 : (c)

7) Cardinality:
Cardinality specifies how many instances of an entity relate to one instance of
another entity. In other words, cardinality specifies the maximum number of
relationships and cardinality specifies the absolute minimum number of
relationships. Cardinalities are used for creating an E/R diagram, and show
the relationships between entities/ tables.
Example:

2.28
 B.C.A. (Sem. - I) Database Management
Systems

8) Line:
Symbol of line:
It link attribute to entity and entity to attribute.
D) Example of Entity Relationship Diagram:
ERD for Employee Department Relationship.

Fig 2.19 : Entity Relationship Diagram

2.29
 B.C.A. (Sem. - I) Database Management
Systems

2.5.1 Extended Features of ERD:

Although the basic E-R concepts can model most database features, some
aspects of a database may be more aptly expressed by certain extensions to the
basic E-R model. In this section, discuss the extended E-R features of
specialization, generalization, higher and lower-level entity sets, attribute
inheritance, and aggregation. There are four extended features of entity
relationship diagram.
A) Generalization:
Generalization is the process of extracting shared characteristics from two or
more classes, and combining them into a generalized super class. Shared
characteristics can be attributes, associations, or methods.
A generalization is defined as a broad statement or an idea that applies to a
group of people or things. Often times, generalizations are not entirely true,
because there may be examples of individuals or situations wherein the
generalization does not apply. For example, person is the higher-level entity
set and customer and employee are lower-level entity sets as shown in below
diagram

Perso
n

ISA

Employee Customer

ISA

Secretary
Officer Engineer
2.30
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.20: Generalization (Customer Employee)

Example:
In below diagram Account is a saving and current.

Fig 2.21: Generalization (Saving and Current)

B) Specialization:
Specialization is opposite to generalization. It is top-down approach in which
one higher level entity can be broken into two lower level entities. In
specialization some higher level entities may not have lower level entity sets
at all. An entity set may include sub groupings of entities that are distinct in
some way from other entities in the set. For instance, a subset of entities
within an entity set may have attributes that are not shared by all the entities
in the entity set. The E-R model provides a means for representing these
distinctive entity groupings. The process of designating sub groupings within
an entity set is called specialization. The specialization of person allows us to
distinguish among persons according to whether they are employees or
customers.
Person

Is A
A

Fig : 2.22 Specialization (Employee or

Customer Customer)
Employee

Example:

2.31
 B.C.A. (Sem. - I) Database Management
Systems

In below diagram a student is an ex –student or current student.

Fig 2.23: Specialization (ex-student and current student)

C) Aggregation:
A feature of the entity relationship model that allows a relationship set to
participate in another relationship set. This is indicated on an ER diagram by
drawing a dashed box around the aggregation. Aggregation is a process
when relation between two entities is treated as a single entity.
Example:

Here the relation between center and course is acting as an entity in relation
with visitor.

Fig 2.24 : Aggregation

2.32
 B.C.A. (Sem. - I) Database Management
Systems

D) Attribute Inheritance:
A crucial property of the higher- and lower-level entities created by
specialization and generalization is attribute inheritance. The attributes of
the higher-level entity sets are said to be inherited by the lower-level entity
sets. Attribute inheritance applies through all tiers of lower-level entity sets;
thus, instructor and secretary, which are subclasses of employee, inherit the
attributes ID, name, and address from person, in addition to inheriting salary
from employee. A lower-level entity set (or subclass) also inherits
participation in the relationship sets in which its higher-level entity (or super
class) participates. Like attribute inheritance, participation inheritance applies
through all tiers of lower-level entity sets.
For example, suppose the person entity set participates in a relationship
person dept with department. Then, the student, employee, instructor and
secretary entity sets, which are subclasses of the person entity set, also
implicitly participate in the person dept relationship with department. The
above entity sets can participate in any relationships in which the person
entity set participates.
Example:

Fig 2.25 : Attribute Inheritance

E) Difference between specialization and generalization:
Sr. Point Specialization Generalization
No.

2.33
 B.C.A. (Sem. - I) Database Management
Systems

1) Meaning Specialization means Generalization is the process of

creating new subclasses extracting shared
from an existing class. characteristics from two or more
classes, and combining them
into a generalized super class.
2) Position In specialization, some In generalization, each higher
of entity higher level entities may level entity must also be a lower
not belong to lower-level level entity.
entity sets at all.
3) Structure Specialization is Generalization is developed
developed top-down bottom-up.

4) Example In specialization, an Using generalization, an analyst

employee entity would be first develops engineer,
broken down into manager, and secretary entities
engineer, manager, and and then creates the employee
secretary entities. entity.

2.6 Overall System Structure :

DBMS (Database Management System) acts as an interface between the
user and the database. The user requests the DBMS to perform various
operations (insert, delete, update and retrieval) on the database. The
components of DBMS perform these requested operations on the database and
provide necessary data to the users.

2.34
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.26 : Overall System Structure

1) DDL Compiler:
Data Description Language compiler processes schema definitions specified
in the DDL. It includes metadata information such as the name of the files,
data items, storage details of each file, mapping information and constraints
etc.
2) DML Compiler and Query Optimizer:
The DML commands such as insert, update, delete, retrieve from the
application program are sent to the DML compiler for compilation into object
code for database access. The object code is then optimized in the best way
to execute a query by the query optimizer and then send to the data
manager.
3) Data Manager:

2.35
 B.C.A. (Sem. - I) Database Management
Systems

The Data Manager is the central software component of the DBMS also
knows as Database Control System. It is sometimes referred to as the
database control system. One of the functions of the data manager is to
convert operations in the user's queries coming directly via the query
processor or\ indirectly via an application program from the user's logical view
to a physical file system.
The data manager is responsible for interfacing with the file system as
shown. It is also the responsibility of the data Manager to provide the
synchronization in the simultaneous operations performed by concurrent
users and to maintain the backup and recovery operations.
4) Data Dictionary:
Data dictionary stores information about the structure of the database. Data
Dictionary is a repository of description of data in the database It contains
information about Data - names of the tables, names of attributes of each
table, length of attributes, and number of rows in each table. It consists of
detailed information on physical database design such as storage structure,
access paths, files and record sizes.
5) Data Files:
It contains the data portion of the database. It store the database itself.
6) Compiled DML:
The DML complier converts the high level queries into low level file access
commands known as compiled DML.
7) End Users:
End Users are the people who interact with the database through applications
or utilities
Examples of ERD:
1) Library Management System:
Entities:
Person, Librarian, Book, Publisher

2.36
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.27 : Library Management System

2) Hospital Management System:

Entities :
Patient, Employees, Doctor, Record

2.37
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.28 : Hospital Management System

3) Student System :
Entities :
Administrator, student, Course, Department, Attendance,Exam

2.38
 B.C.A. (Sem. - I) Database Management
Systems

Fig 2.29 : Student System

4) Company Structure :
Entities :
Worker, Department, Project, Product, Supplier

Fig 2.30 : Company Structure

5) An Insurance Agent sells insurance policies to clients. Policies can be of
different types such as Vehicle Insurance, Life Insurance, and Accident
Insurance etc. The agent collects monthly premiums on the policies in the
form of cheques of local banks.

2.39
 B.C.A. (Sem. - I) Database Management
Systems

Entities :
a) Agent
b) Policies
c) Client
d) Monthly premium
Relationship :
a) The agent table is related with policies table as a one to many relationship
b) The insurance table is related with client table as many to one relationship.
c) The agent table is related with policies table as one to many relationship

AgentName Amount

AgentNo
1 M
Collects
Agent Monthly premium

1
Sells on
M

PoliciesNo M
to Client ClientNo
Policies
1

ClientName
ISA
PoliciesName

Vehicle Life Accident

Insurance Insurance Insurance
Fig 2.31 : Entity Relationship Diagram
6) A company has several departments. Each department has a supervisor and
at least one employee. Employees must be assigned to at least one, but
possibly more departments.
Entities :

2.40
 B.C.A. (Sem. - I) Database Management
Systems

a) Company
b) Department
c) Supervisor
d) Employee
Relationship :
a) The company table may have one to many relationship with department
table
b) The Department table may have one to one relationship with Supervisor
table.
c) The Department table may have one to many relationship with employee
tables.
d) The employee Table may have one to one relationship with department table.
Entity Relationship Diagram :
C_address
D_no
C_name
D_name
M
Company
has Department

1 1 1
has

1
have Supervisor S_no

M S_name

Employee

E_no

E_name

Fig : 2.32
7) Daulatnagar Electric Supply Company is a distributor of electricity in a small
town having about 20,000 consumers. The consumers are divided into three
categories. Agricultural, Commercial, Domestic for whom different rates are

2.41
 B.C.A. (Sem. - I) Database Management
Systems

charged. The bills are made according to the meter readings which are paid
by the consumers.
Entities :
a) Distributor b) Consumer c) Bill
Relationship :
a) Table distributor may have one to many relationship with consumer table
b) Table consumer may have one to many relationship with table bill
Entity Relationship Diagram :
D_no

D_name

Distributor

1
have

C_no

M
M 1
Bill has Paid Consumer

C_name

Bill_no
Bill_amt

ISA

Agricultural Commercial Domestic

Fig : 2.33
8) In a nursery, the plants are sold to the customers. These plants are flowering
and non-flowering only. Nutrients are given to the plant with some quantity.
Nutrients includes pesticides, watering and manure :
Entities :
a) Nursery b) Plant c) Customer d) Nutrients

2.42
 B.C.A. (Sem. - I) Database Management
Systems

Relationship :
a) The table nursery may have one to many with relationship plant table
b) The customer table may have one to many with relationships plant table.
c) The nutrient table may have many
d) to one relationship with plant table.
Entity Relationship Diagram :

Fig : 2.34

Review Questions

2.43
 B.C.A. (Sem. - I) Database Management
Systems

Q. 1. Define the term DBMS. Which are the applications of DBMS?

Q. 2. What are the advantages and disadvantages of DBMS
Q. 3. Explain the users of DBMS.
Q. 4. Define the term Data model. Explain object oriented data model.
Q. 5. Explain the Record base logical model with its type.
Q. 6. What is mean by entity relationship diagram? Explain it.
Q. 7. Explain the attribute inheritance with example.
Q. 8. Write a short notes on :
a) Data and information
b) Data dictionary
c) Field
d) Views of DBMS
e) entity relationship data model
f) Record
g) generalization and specialization
h) DBMS system structure
Q. 9. A university database contains information about professors (identified by
social security number, or SSN) and courses (identified by courseid).
Professors teach courses; each of the following situations concerns the
Teaches relationship set. For each situation, draw an ER diagram that
describes it (assuming that no further constraints hold). Define entities
and relationship.
Q. 10. Each musician that records at Notown has an SSN, a name, an address,
and a phone number. Each instrument that is used in songs recorded at
Notown has a name (e.g., guitar, synthesizer, flute) and a musical key
(e.g., C, B-flat, E-flat).Each album that is recorded on the Notown label
has a title, a copyright date, a format(e.g., CD or MC), and an album
identifier. Each song recorded at Notown has a title and an author. Each
musician may play several instruments, and a given instrument may be
played by several musicians. Each album has a number of songs on it,
but no song may appear on more than one Album. Each song is

2.44
 B.C.A. (Sem. - I) Database Management
Systems

performed by one or more musicians, and a musician may perform a

number of songs. Each album has exactly one musician who acts as its
producer. A musician may produce several albums, of course. Draw an
ER diagram and define entities and relationship.

2.45