CHAPTER 1: INTRODUCTION OF DBMS

1.1 BASIC CONCEPTS AND DEFINITIONS Difference between Data and Information:

1.1.1 Data Data Information

Data is the raw fact. It is a processed form of
Data is defined as a known fact that can be recorded and data.
that have implicit meaning. Data are raw or isolated facts It is not significant to a It is significant to a
from which the required information is produced. For business. business.
example, name, mobile number, address etc. We may have Data is an atomic level piece of It is a collection of data.
recorded this data in an indexed address book, or we may information.
have stored it on a hard drive and software such as It is a phenomenal fact. It is organized data.
Microsoft Access, or Excel. This is the primary level of It is a secondary level of
intelligence. intelligence.
Data is organised in the following layered structure: May or may not be meaningful. Always meaningful.
Understanding is difficult. Understanding is easy.
i. Operational data: These data are stored in various Example: Name of student. Example: Report card of
operational systems throughout the organisation both student.
internal and external systems.

ii. Reconciled data: These data are stored in the 1.1.3 Data Warehouse
organisation data warehouse and in operational data store.
They are detailed and current data, which is intended asA Data Warehouse is separate from DBMS, it stores a huge
amount of data, which is typically collected from multiple
the single, authoritative source for all decision support
applications. heterogeneous sources like files, DBMS, etc. The goal is to
produce statistical results that may help in decision-
iii. Derived data: These data are stored in each of the making.
data mart (a selected, limited and summarised data
warehouse). Derived data are selected, formatted and Data warehouse is a collection of data designed to support
aggregated for end-user decision support applications. management in the decision-making process. It is a
subject-oriented, integrated, timevariant, non-updatable
collection of data used in support of management decision-
making processes and business intelligence. It contains a
wide variety of data that present a coherent picture of
business conditions at a single point of time. It is a unique
kind of database, which focuses on business intelligence,
external data and time-variant data and not just current
data.

1.1.4 Metadata

1.1.2 Information A metadata or data dictionary is the data about the data. It
is also called the system catalog, which is the self-
Information is processed, organised or summarised data or describing nature of the database that provides program-
Information is the data that has been converted into more data independence. The system catalog integrates the
useful or intelligent form. It may be defined as collection of metadata. The metadata is the data that describes objects
related data that when put together, communicate in the database and makes easier for those objects to be
meaningful and useful message to a recipient who uses it, accessed or manipulated. It describes the database
to make decision or to interpret the data to get the structure, constraints, applications, authorisation, sizes of
meaning. data types and so on. These are often used as an integral
tool for information resource management.
The information is needed for the following reasons:
Metadata is used by developers who rely on it to help them
• To gain knowledge about the surroundings.
develop the programs, queries, controls and procedures to
manage and manipulate the warehouse data. Metadata is
• To keep the system up to date.
available to database administrators (DBAs), designers and
authorised users as on-line system documentation. This
• To know about the rules and regulations of the society.
improves the control of DBAs over the information system
and the users’ understanding and use of the system.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 1: INTRODUCTION OF DBMS

Types of Metadata 1.1.7 Records

The advent of data warehousing technology has A record is a collection of logically related fields or data
highlighted the importance of metadata. There are items, with each field possessing a fixed number of bytes
following types of Metadata are available: and having a fixed data type. A record consists of values
for each field. It is an occurrence of a named collection of
zero, one, or more than one data items or aggregates. The
data items are grouped together to form records. The
grouping of data items can be achieved through different
ways to form different records for different purposes. These
records are retrieved or updated using programs.

1.1.8 Files

A file is a collection of related sequence of records. In many

cases, all records in a file are of the same record type. If
every record in the file has exactly the same size (in bytes),
the file is said to be made up of fixed-length records. If
i. Operational metadata: It describes the data in the
different records in the file have different sizes, the file is
various operational systems that feed the enterprise data
said to be made of variable-length records.
warehouse. Operational metadata typically exist in a
number of different formats and unfortunately are often of 1.2 DATA DICTIONARY
poor quality.
Data dictionary or information repositories are mini
ii. Enterprise data warehouse metadata: These types of database management systems that manages metadata. It
metadata are derived from the enterprise data model. They is a repository of information about a database that
describe the reconciled data layer as well as the rules for documents data elements of a database. The data
transforming operational data to reconciled data. dictionary is an integral part of DBMS and stores
metadata, or information about the database, attribute
iii. Data mart metadata: They describe the derived data
names and definitions for each table in the database. Data
layer and the rules for transforming reconciled data to
dictionaries aid the database administrator in the
derived data.
management of a database, user view definitions as well as
their use.
1.1.5 System Catalog

A system catalog is a repository of information describing The most general structure of a data dictionary is shown
the data in the database, that is the metadata (or data in following figure:
about the data). System catalog is a system-created
database that describes all database objects, data
dictionary information and user access information. It also
describes table-related data such as table names, table
creators or owners, column names, data types, data size,
foreign keys and primary keys, indexed files, authorized
users, user access privileges and so forth.

1.1.6 Data Item or Fields

A data item is the smallest unit of the data that has It contains descriptions of the database structure and
meaning to its user. It is traditionally called a field or data database use. The data in the data dictionary are
element. It is an occurrence of the smallest unit of named maintained by several programs and produce diverse
data. It is represented in the database by a value e.g. reports on demand. Most data dictionary systems are
Names, telephone numbers, etc. Data items are the stand-alone systems, and their database is maintained
molecules of the database. There are atoms and sub- independently of the DBMS, thereby enabling
atomic particles composing each molecule (bits and bytes), inconsistencies between the database and the data
but they do not convey any meaning on their own right dictionary. To prevent them, the data dictionary is
and so are of little concern to the users. A data item may integrated with DBMSs in which the schema and user view
be used to construct other, more complex structures. definitions are controlled through the data dictionary and
are made available to the DBMS software.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 1: INTRODUCTION OF DBMS

Data dictionary is usually a part of the system catalog thatii. The passive data dictionary: It is also called non-
is generated for each database. A useful data dictionary integrated data dictionary. It is the one used only for
system usually stores and manages the following types of documentation purposes. Data about fields, files, people
information: and so on, in the data processing environment are entered
into the dictionary and cross-referenced. Passive
• Descriptions of the schema of the database. dictionary is simply a self-contained application and a set
• Detailed information on physical database design, of files is used for documenting the data processing
such as storage structures, access paths and file and environment.
record sizes.
• Description of the database users, their It is managed by the users of the system and is modified
responsibilities and their access rights. whenever the structure of the database is changed. Since
• High-level descriptions of the database transactions this modification must be performed manually by the user,
and applications and of the relationships of users to it is possible that the data dictionary will not be current
transactions. with the current structure of the database. However, the
• The relationship between database transactions and passive data dictionaries may be maintained as a separate
the data items referenced by them. This is useful in database. Thus, it allows developers to remain
determining which transactions are affected when independent from using a particular relational database
certain data definitions are changed. management system for as long as possible.
• Usage statistics such as frequencies of queries and
1.3 DATABASE
transactions and access counts to different portions of
the database.
A database is defined as a collection of logically related
data stored together that is designed to meet the
1.2.1 Components of Data Dictionaries
information needs of an organisation. It is basically an
Data dictionary contains the following components: electronic filing cabinet, which contain computerized data
files. It can contain one data file or large number of data
1. Entities files depending on organisational needs. A database is
organised in such a way that a computer program can
Entity is the real physical object or an event; the user is quickly select desired pieces of data.
interested in keeping track of. In other words, any item
about which information is stored is called entity. A database is designed, built and populated with data for a
specific purpose. It has an intended group of users and
2. Attributes some preconceived applications in which these users are
interested. In other words, database has some source from
An attribute is a property or characteristic of an entity. where data is derived, some degree of interaction with
events in the real world and an audience that is actively
3. Relationships
interested in the contents of the database. A database can
be of any size and of varying complexity. It may be
The associations or the ways that different entities relate
generated and maintained manually or it may be
to each other is called relationships.
computerized. A computerized database may be created
4. Key and maintained either by a group of application programs
written specifically for that task or by a database
The data item for which a computer uses to identify a management system.
record in a database system is referred to as key. Key is a
single attribute or combination of attributes of an entity A database consists four components:
set that is used to identify one or more instances of the
1.Data item: It is a distinct piece of information.
set.
2.Relationships: It represent a correspondence between
1.2.2 Active and Passive Data Dictionaries
various data elements.
i. An active data dictionary It is also called integrated
3. Constraints: It predicates that define correct database
data dictionary. It is managed automatically by the
states.
database management software. Since active data
dictionaries are maintained by the system itself, they are
4. Schema: It describes the organisation of data and
always consistent with the current structure and definition
relationships within the database. It defines various views
of the database. Most of the relational database
of the database for the use of the various system
management systems contain active data dictionaries that
components of the database management system and for
can be derived from their system catalog.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 1: INTRODUCTION OF DBMS

application security. A schema separates the physical to retrieve specific data, updating to reflect changes and
aspect of data storage from the logical aspects of data generating reports from the data) for various applications.
representation.
Typically, a DBMS has three basic components:
An organisation of a database is consisting of the following
three independent levels: 1. Data description language (DDL): It allows users to
define the database, specify the data types, and data
i. The internal schema defines how and where the data structures, and the constraints on the data to be stored in
are organised in physical data storage. the database, usually through data definition language.
DDL translates the schema written in a source language
ii. The conceptual schema defines the stored data 2into the object schema, thereby creating a logical and
structure in terms of the database model used. physical layout of the database.

iii. The external schema defines a view of the database 2. Data manipulation language (DML) and query
for particular users. A database management system facility: It allows users to insert, update, delete and
provides for accessing the database while maintaining the retrieve data from the database, usually through data
required correctness and consistency of the stored data. manipulation language (DML). It provides general query
facility through structured query language (SQL).

3. Software for controlled access of database: It

provides controlled access to the database, for example,
preventing unauthorized user trying to access the
database, providing a concurrency control system to allow
shared access of the database, activating a recovery
control system to restore the database to a previous
consistent state following a hardware or software failure
and so on.

1.4 DATABASE SYSTEM

A database system, also called database management

system (DBMS), is a generalized software system for
manipulating databases. It is basically a computerized
record-keeping system; which it stores information and
allows users to add, delete, change, retrieve and update The users can perform a variety of operations on database
that information on demand. It provides for simultaneous systems. Some of the important operations performed on
use of a database by multiple users and tool for accessing such files are as follows:
and manipulating the data in the database. DBMS is also
a collection of programs that enables users to create and • Inserting new data into existing data files
maintain database. It is a general-purpose software system • Adding new files to the database
that facilitates the process of defining (specifying the data • Retrieving data from existing files
types, structures and constraints), constructing (process of • Changing data in existing files
storing data on storage media) and manipulating (querying • Deleting data from existing files
• Removing existing files from the database.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 1: INTRODUCTION OF DBMS

1.5 DATABASE ADMINISTRATOR organisation or to alter the physical organisation to

improve performance.
A database administrator (DBA) is an individual person or
group of persons with an overview of one or more 5. Routine maintenance: The DBA maintains periodical
databases who controls the design and the use of these back-ups of the database, either onto hard disks, compact
databases. A DBA provides the necessary technical disks or onto remote servers, to prevent loss of data in
support for implementing policy decisions of databases. case of disasters. It ensures that enough free storage space
Thus, a DBA is responsible for the overall control of the is available for normal operations and upgrading disk
system at technical level. A DBA is the central controller of space as required. A DBA is also responsible for repairing
the database system who oversees and manages all the damage to the database due to misuse or software and
resources such as database, DBMS and related software. hardware failures. DBAs define and implement an
The DBA is responsible for authorizing access to the appropriate damage control mechanism involving periodic
database, for coordinating and monitoring its use and for unloading or dumping of the database to backup storage
acquiring software and hardware resources as needed. device and reloading the database from the most recent
They are accountable for security system, appropriate dump whenever required.
response time and ensuring adequate performance of the
database system and providing a variety of other technical 6. Job monitoring: DBAs monitor jobs running on the
services. The database administrator is supported with a database and ensure that performance is not degraded by
number of staff or a team of people such as system very expensive tasks submitted by some users. With
programmers and other technical assistants. change in requirements (for example, reorganising of
database, DBAs are responsible for making appropriate
Functions and Responsibilities of DBAs adjustment or tuning of the database

Following are some of the functions and responsibilities of 1.6 FILE-ORIENTED SYSTEM VERSUS DATABASE
database administrator and his staff: SYSTEM

1. Defining conceptual schema and database creation: Computer-based data processing systems were initially
A DBA creates the conceptual schema (using data used for scientific and engineering calculations. With
definition language) corresponding to the abstract level increased complexity of business requirements, gradually
database design made by data administrator. The DBA they were introduced into the business applications. The
creates the original database schema and structure of the manual method of filing systems of an organisation, such
database. The object from the schema is used by DBMS in as to hold all internal and external correspondence
responding to access requests. relating to a project or activity, client, task, product,
customer or employee, was maintaining different manual
2. Storage structure and access-method definition: folders. These files or folders were labelled and stored in
DBA decides how the data is to be represented in the one or more cabinets or almirahs under lock and key for
stored database, the process called physical database safety and security reasons. As and when required, the
design. Database administrator defines the storage concerned person in the organisation used to search for a
structure (called internal schema) of the database (using specific folder or file serially starting from the first entry.
data definition language) and the access method of the Alternatively, files were indexed to help locate the file or
data from the database. folder more quickly. Ideally, the contents of each file folder
were logically related. For example, a file folder in a
3. Granting authorisation to the users: One of the supplier’s office might contain customer data; one file
important responsibilities of a DBA is the liaising with folder for each customer. All data in that folder described
end-users to ensure availability of required data to them. A only that customer’s transaction.
DBA grants access to use the database to its users. It
regulates the usage of specific parts of the database by Similarly, a personnel manager might organise personnel
various users. The authorisation information is kept in a data of employees by category of employment (for example,
special system structure that the database system technical, secretarial, sales, administrative, and so on).
consults whenever someone attempts to access the data in Therefore, a file folder leveled ‘technical’ would contain
the system. DBAs assist the user with problem definition data pertaining to only those people whose duties were
and its resolution. properly classified as technical.

4. Physical organisation modification: The DBA carries The manual system worked well as data repository as long
out the changes or modification to the description of the as the data collection were relatively small and the
database or its relationship to the physical organisation of organisation’s managers had few reporting requirements.
the database to reflect the changing needs of the However, as the organisation grew and as the reporting
requirements became more complex, it became difficult in

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 1: INTRODUCTION OF DBMS

keeping track of data in the manual file system. Also, system. Each application has its own private files and
report generation from a manual file system could be slow users have little opportunity to share data outside
and cumbersome. Thus, this manual filing system was their own applications.
replaced with a computer-based filing system. File-
oriented systems were an early attempt to computerize the • Inadequate data manipulation capabilities: Since
manual filing system that we are familiar with. Because File-oriented systems do not provide strong
these systems performed normal recordkeeping functions, connections between data in different files and
they were called data processing (DP) systems. Rather than therefore its data manipulation capability is very
establish a centralised store for organisation’s operational limited.
data, a decentralised approach was taken, where each • Excessive programming effort: There was a very high
department, with the assistance of DP department staff, interdependence between program and data in file-
stored and controlled its own data. oriented system and therefore an excessive
programming effort was required for a new application
Advantages of Learning File-oriented System program to be written.
• Security problems: Every user of the database system
Although the file-oriented system is now largely obsolete, should not be allowed to access all the data. Each user
following are the several advantages of learning file-based should be allowed to access the data concerning his
systems: area of application only. Since, applications programs
are added to the file-oriented system in an ad hoc
• It provides a useful historical perspective on how we
manner, it was difficult to enforce such security
handle data.
system.
• The characteristics of a file-based system helps in an
overall understanding of design complexity of database Advantages of DBMS
systems.
• Understanding the problems and knowledge of Due to the centralised management and control, the
limitation inherent in the file-based system helps avoid DBMS has numerous advantages. Some of these are as
these same problems when designing database follows:
systems and thereby resulting in smooth transition.
• Minimal data redundancy: In a database system,
Disadvantages of File-oriented System views of different user groups (data files) are integrated
during database design into a single, logical,
Conventional file-oriented system has the following centralised structure. By having a centralised
disadvantages: database and centralised control of data by the DBA
the unnecessary duplication of data are avoided. Each
• Data redundancy: Since a decentralised approach primary fact is ideally recorded in only one place in the
was taken, each department used their own database.
independent application programs and special files of
• Program-data independence: The separation of
data. This resulted into duplication of same data and
metadata (data description) from the application
information in several files,
programs that use the data is called data
• Data inconsistency (or loss of data integrity): Data independence. In the database environment, it allows
redundancy also leads to data inconsistency (or loss of for changes at one level of the database without
data integrity), since either the data formats may be affecting other levels.
inconsistent or data values (various copies of the same
• Efficient data access: DBMS utilizes a variety of
data) may no longer agree or both.
sophisticated techniques to store and retrieve data
• Program-data dependence: As we have seen, file efficiently. This feature is especially important if the
descriptions (physical structure, storage of the data data is stored on external storage devices.
files and records) are defined within each application
• Improved data sharing: Since, database system is a
program that accesses a given file.
centralised repository of data belonging to the entire
• Poor data control: A file-oriented system being organisation (all departments), it can be shared by all
decentralised in nature, there was no centralised authorized users.
control at the data element (field) level. It could be very
• Improved data consistency: Inconsistency is the
common for the data field to have multiple names
corollary to redundancy. In the file-oriented system,
defined by the various departments of an organisation
when the data is duplicated and the changes made at
and depending on the file it was in.
one site are not propagated to the other site, it results
• Limited data sharing: There is limited data sharing into inconsistency. Such database supplies incorrect
opportunities with the traditional file oriented or contradictory information to its users. So, if the
redundancy is removed or controlled, chances of

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 1: INTRODUCTION OF DBMS

having inconsistence data is also removed and to the end-users. This provides a system with
controlled. potentially much more functionality. Many DBMSs
• Improved data integrity: Data integrity means that provide query languages or report writers that allow
the data contained in the database is both accurate users to ask ad hoc questions and to obtain the
and consistent. Integrity is usually expressed in terms required information almost immediately at their
of constraints, which are consistency rules that the terminal, without requiring a programmer to write
database system should not violate. some software to extract this information from the
• Improved security: Database security is the database.
protection of database from unauthorised users. The • Reduced program maintenance: The problems of
database administrator (DBA) ensures that proper high maintenance effort required in file-oriented
access procedure is followed, including proper system, are reduced in database system.
authentication schemes for access to the DBMS and • Improved backup and recovery services: DBMS
additional checks before permitting access to sensitive provides facilities for recovering from hardware or
data. ADBA can define (which is enforced by DBMS) software failures through its backup and recovery
user names and passwords to identify people subsystem.
authorised to use the database. Different levels of • Improved data quality: The database system provides
security could be implemented for various types of a number of tools and processes to improve data
data and operations. quality.
• Increased productivity of application development:
The DBMS provides many of the standard functions Disadvantages of DBMS
that the application programmer would normally have
to write in a file-oriented application. It provides all the In spite of the advantages, the database approach entails
low-level file-handling routines that are typical in some additional costs and risks that must be recognized
application programs. The provision of these functions and managed when implementing DBMS. Following are
allows the application programmer to concentrate on the disadvantages of using DBMS:
the specific functionality required by the users without
• Increased complexity: A multi-user DBMS becomes
having to worry about low-level implementation
an extremely complex piece of software due to
details.
expected functionality from it. It becomes necessary
• Enforcement of standards: With central control of
for database designers, developers, database
the database, a DBA defines and enforces the
administrators and end-users to understand this
necessary standards. Applicable standards might
functionality to full advantage of it.
include any or all of the following: departmental,
• Requirement of new and specialized manpower:
installation, organisational, industry, corporate,
Because of rapid changes in database technology and
national or international. Standards can be defined for
organisation’s business needs, the organisation’s need
data formats to facilitate exchange of data between
to hire, train or retrain its manpower on regular basis
systems, naming conventions, display formats, report
to design and implement databases, provide database
structures, terminology, documentation standards,
administration services and manage a staff of new
update procedures, access rules and so on.
people.
• Economy of scale: Centralising of all the
• Large size of DBMS: The large complexity and wide
organisation’s operational data into one database and
functionality makes the DBMS an extremely large
creating a set of application programs that work on
piece of software. It occupies many gigabytes of
this source of data resulting in drastic cost savings.
storage disk space and requires substantial amounts
The DBMS approach permits consolidation of data and
of main memory to run efficiently.
applications. Thus reduces the amount of wasteful
• Increased installation and management cost: The
overlap between activities of data-processing personnel
large and complex DBMS software has a high initial
in different projects or departments.
cost. It requires trained manpower to install and
• Balance of conflicting requirements: Knowing the
operate and also has substantial annual maintenance
overall requirements of the organisation (instead of the
and support costs.
requirements of individual users), the DBA resolves
• Additional hardware cost: The cost of DBMS
the conflicting requirements of various users and
installation varies significantly, depending on the
applications. A DBA can structure the system to
environment and functionality, size of the hardware
provide an overall service that is best for the
and the recurring annual maintenance cost of
organisation.
hardware and software.
• Improved data accessibility and responsiveness: As
• Conversion cost: The cost of conversion (both in
a result of integration in database system, data that
terms of money and time) from legacy system (old file-
crosses departmental boundaries is directly accessible

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 1: INTRODUCTION OF DBMS

oriented and/or older database technology) to modern

DBMS environment is very high.
• Need for explicit backup and recovery: For a
centralised shared database to be accurate and
available all times, a comprehensive procedure is
required to be developed and used for providing
backup copies of data and for restoring a database
when damage occurs.
• Organisational conflict: A centralised and shared
database (which is the case with DBMS) requires a
consensus on data definitions and ownership as well
as responsibilities for accurate data maintenance.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

2.1 SCHEMAS, SUBSCHEMA AND INSTANCES 2.1.2 SUBSCHEMA

When the database is designed to meet the information A subschema is a subset of the schema and inherits the
needs of an organisation, plans or scheme of the database same property that a schema has. The plan or scheme for
and actual data to be stored in it becomes the most a view is often called subschema. Subschema refers to an
important concern of the organisation. It is important to application programmer’s (user’s) view of the data item
note that the data in the database changes frequently, types and record types, which he or she uses. It gives the
while the plans remain the same over long periods of time. users a window through which he can view only that part
The database plans consist of types of entities that a of the database, which is of interest to him.
database deals with, the relationships among these
entities and the ways in which the entities and In other words, subschema defines the portion of the
relationships are expressed from one level of abstraction to database as “seen” by the application programs that
the next level for the users’ view. The users’ view or logical actually produced the desired information from the data
scheme of the data should be in a form that is most contained within the database. Therefore, different
convenient for the users and they should not be concerned application programs can have different view of data.
about the way data is physically organised. Therefore, a
DBMS should do the translation between the logical Individual application programs can change their
organisation and the physical organisation of the data in respective subschema without effecting subschema views
the database. of others. The DBMS software derives the subschema data
requested by application programs from schema data. The
2.1.1 SCHEMA DBA ensures that the subschema requested by application
programs is derivable from schema.
The plan or formulation of scheme of the database is
known as schema. Schema gives the names of the entities The application programs are not concerned about the
and attributes. It specifies the relationship among them. Itphysical organisation of data. The physical organisation of
is a framework into which the values of the data items are data in the database can change without affecting
fitted. The plans or the format of schema remains the application programs. In other words, with the change in
same. But the values fitted into this format changes from physical organisation of data, application programs for
instance to instance. subschema need not be changed or modified. Subschemas
also act as a unit for enforcing controlled access to the
The schema is a complete description of a database, database.
including the names and descriptions of all areas, records,
elements, and sets. The major purpose of the schema is to The subschema can restrict access to the database in the
provide definitions from which to generate subschemas. following ways:

The database system can have several schemas partitioned • The subschema identifies the areas, records, elements,
according to the levels of abstraction. In general, schema and sets which are accessible.
can be categorised in two parts;
• The subschema identifies the Data Manipulation
(1) Logical schema Language (DML) functions which can be performed.

The logical schema is concerned with exploiting the data Subschemas also allow us to define logical records. Logical
structures offered by a DBMS in order to make the scheme records are a view of one or more base records and a set of
understandable to the computer. operations performed on those records.

(2) Physical schema 2.1.3 INSTANCES

The physical schema, on the other hand, deals with the When the schema framework is filled in the data item
manner in which the conceptual database shall get values or the contents of the database at any point of time,
represented in the computer as a stored database. it is referred to as an instance of the database. The term
instance is also called as state of the database or
The logical schema is the most important as programs use snapshot. Each variable has a particular value at a given
it to construct applications. The physical schema is hidden instant. The values of the variables in a program at a point
beneath the logical schema and can usually be changed in time correspond to an instance of a database schema,
easily without affecting application programs.
The difference between database schema and database
state or instance is very distinct. In the case of a database
schema, it is specified to DBMS when new database is

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

defined, whereas at this point of time, the corresponding conceptual view is the sum total of user or external view of
database state is empty with no data in the database. data.
Once the database is first populated with the initial data,
from then on, we get another database state whenever an 2.2.1 Internal Level
update operation is applied to the database. At any point
of time, the current state of the database is called the Internal level is the physical representation of the database
instance. on the computer and this view is found at the lowest level
of abstraction of database. This level indicates how the
2.2 THREE-LEVEL DATA BASE ARCHITECTURE data will be stored in the database and describes the data
structures, file structures and access methods to be used by
In 1975, ANSI-SPARC (American National Standards the database. It describes the way the DBMS and the
Institute-Standards Planning and Requirements operating system perceive the data in the database. Just
Committee) produced a three-tier architecture with a below the internal level there is physical level data
system catalog. The architecture of most commercial organisation whose implementation is covered by the
DBMSs available today is based to some extent on ANSI- internal level to achieve routine performance and storage
SPARC proposal. ANSI-SPARC three-tier database space utilization.
architecture consists of following three levels:
The internal schema defines the internal level (or view).
1. Internal level The internal schema contains the definition of the stored
record, the method of representing the data fields (or
2. Conceptual level attributes), indexing and hashing schemes and the access
methods used. Internal level provides coverage to the data
3. External level structures and file organisations used to store data on
storage devices.

Essentially, internal schema summarizes how the relations

described in the conceptual schema are actually stored on
secondary storage devices such as disks and tapes. It
interfaces with the operating system access methods to
place the data on the storage devices, build the indexes,
retrieve the data and so on. Internal level is concerned
with the following activities:

• Storage space allocation for data and storage.

• Record descriptions for storage with stored sizes for
data items.
• Record placement.
The view at each of the above levels is described by a • Data compression and data encryption techniques.
scheme or schema. A schema is an outline or plan that
The process arriving at a good internal or physical schema
describes the records, attributes and relationships existing
is called physical database design. The internal schema is
in the view. The term view, scheme and schema are used
written using SQL or internal data definition language.
interchangeably. A data definition language (DDL), is used
to define the conceptual and external schemas. SQL
2.2.2 Conceptual Level
commands are used to describe the aspects of the physical
or internal schema. Information about the internal, The conceptual level is the middle level in the three-tier
conceptual and external schemas is stored in the system architecture. At this level of database abstraction, all the
catalog. database entities and relationships among them are
included. Conceptual level provides the community view of
The data has been abstracted in three levels corresponding
the database and describes what data is stored in the
to three views namely internal, conceptual and external
database and the relationships among the data. It contains
views. The lowest level of abstraction of data contains a
the logical structure of the entire database as seen by the
description of the actual method of storing data and is
DBA.
called the internal view. The second level of abstraction is
the conceptual or global view. The third level is the highest One conceptual view represents the entire database of an
level of abstraction seen by the user or application organisation. It is a complete view of the data requirements
program and is called the external view or user view. The of the organisation that is independent of any storage
considerations. The conceptual schema defines conceptual

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

view. It is also called the logical schema. There is only one • The user is not concerned about the physical data
conceptual schema per database. This schema contains storage details. The user’s interaction with the
the method of deriving the objects in the conceptual view database is independent of physical data storage
from the objects in the internal view. Conceptual level is organisation.
concerned with the following activities: • The internal structure of the database is unaffected
by changes to the physical storage organisation, such
• All entities, their attributes and their relationships. as changeover to a new storage device.
• Constraint on the data. • DBA is able to change the database storage structures
• Semantic information about the data. without affecting the user’s view.
• Checks to retain data consistency and integrity. • The DBA is able to change the conceptual structure of
• Security information. the database without affecting all users.

The process of arriving at a good conceptual schema is 2.3 DATA INDEPENDENCE

called conceptual database design. The conceptual schema
is written using conceptual data definition language. Data independence is a major objective of implementing
DBMS in an organisation. It may be defined as the
2.2.3 External Level immunity of application programs to change in physical
representation and access techniques. Data independence
The external level is the user’s view of the database. This is the characteristics of a database system to change the
level is at the highest level of data abstraction where only schema at one level without having to change the schema at
those portions of the database of concern to a user or the next higher level. In other words, the application
application program are included. In other words, this programs do not depend on any one particular physical
level describes that part of the database that is relevant to representation or access technique. This characteristic of
the user. Any number of user views, even identical, may DBMS insulates the application programs from changes in
exist for a given conceptual or global view of the database. the way the data is structured and stored.
Each user has a view of the “real world” represented in a
form that is familiar for that user. The data independence in achieved by DBMS through the
use of the three-tier architecture of data abstraction. There
The external view includes only those entities, attributes are two types of data independence as shown in following
and relationships in the “real world” that the user is three tier architecture diagrams
interested in. Other entities, attributes and relationships
that are not of interest to the user, may be represented in
the database, but the user will be unaware of them.

An external schema describes each external view. The

external schema consists

• The definition of the logical records and the

relationships in the external view.
• It also contains the method of deriving the objects for
example, entities, attributes and relationships, in the
external view from the object in the conceptual view.
• External schemas allow data access to be customized
at the level of individual users or groups of users.

The external schema is written using external data

definition language (external DDL).

Advantages of three tier architecture

2.3.1 Physical Data Independence
Following are the advantages of a three-tier database
architecture: Immunity of the conceptual or external schemas to
changes in the internal schema is referred to as physical
• Each user is able to access the same data but have a
data independence. In physical data independence, the
different customized view of the data as per their own
conceptual schema insulates the users from changes in
needs. Each user can change the way he views the
the physical storage of the data. Changes to the internal
data and this change does not affect other users of the
schema, such as using different file organisations or
same database.
storage structures, using different storage devices,

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

modifying indexes or hashing algorithms, must be possible combination of records in physical storage that constitute
without changing the conceptual or external schemas. a logical record in the conceptual schema, together with
physical data independence indicates that the physical any constraints to be enforced on the operations for that
storage structures or devices used for storing the data could logical record.
be changed without necessitating a change in the
conceptual view or any of the external views. It also allows any differences in entity names, attribute
names, attribute orders, data types, and so on, to be
2.3.2 Logical Data Independence resolved. In case of any change in the structure of the
stored database, the conceptual/internal mapping is also
Immunity of the external schemas or application changed accordingly by the DBA, so that the conceptual
programs, to changes in the conceptual schema is referred schema can remain invariant. Therefore, the effects of
to as logical data independence. In logical data changes to the database storage structure are isolated
independence, the users are shielded from changes in the below the conceptual level in order to preserve the physical
logical structure of the data or changes in the choice of data independence.
relations to be stored.
2.4.2 External/Conceptual Mapping
Changes to the conceptual schema, such as the addition
and deletion of entities, addition and deletion of attributes, Each external schema is related to the conceptual schema
or addition and deletion of relationships, must be possible by the external mapping. The external mapping defines the
without changing existing external schemas or having to correspondence between a particular external view and the
rewrite application programs. Only the view definition and conceptual view. It gives the correspondence among the
the mapping need be changed in a DBMS that supports records and relationships of the external and conceptual
logical data independence. It is important that the users views. It enables the DBMS to map names in the user’s
for whom the changes have been made should not be view on to the relevant part of the conceptual schema. Any
concerned. In other words, the application programs that number of external views can exist at the same time, any
refers to the external schema constructs must work as number of users can share a given external view and
before, after the conceptual schema undergoes a logical different external view can overlap.
reorganisation.
There could be one mapping between conceptual and
2.4 MAPPINGS internal levels and several mappings between external and
conceptual levels. The conceptual/internal mapping is the
The three schemas and their levels are the description of key to physical data independence while the
data that actually exists in the physical database. In the external/conceptual mapping is the key to the logical data
three-schema architecture database system, each user independence. The information about the mapping
group refers only to its own external schema. Hence, the requests among various schema levels are included in the
user’s request specified at external schema level must be system catalog of DBMS. The DBMS uses additional
transformed into a request at conceptual schema level. software to accomplish the mappings by referring to the
The transformed request at conceptual schema level mapping information in the system catalog. When schema
should be further transformed at internal schema level for is changed at some level, the schema at the next higher
final processing of data in the stored database as per level remains unchanged. Only the mapping between the
user’s request. The final result from processed data as per two levels is changed. Thus, data independence is
user’s request must be reformatted to satisfy the user’s accomplished.
external view.
2.5 Structure of a DBMS
The process of transforming requests and results between
the three levels are called mappings. DBMS is responsible The DBMS software is partitioned into several modules.
for this mapping between internal, conceptual and Each module or component is assigned a specific
external schemas. The three-tier architecture of ANSI- operation to perform. Some of the functions of the DBMS
SPARC model provides the following two stage mappings: are supported by OS to provide basic services and DBMS
is built on top of it. The physical data and system catalog
2.4.1 Conceptual/Internal Mapping are stored on a physical disk. Access to the disk is
controlled primarily by OS, which schedules disk
The conceptual schema is related to the internal schema input/output. Therefore, while designing a DBMS its
through conceptual/internal mapping. The conceptual interface with the OS must be taken into account.
internal mapping defines the correspondence between the
conceptual view and the stored database. It specifies how A typical structure of a DBMS with its components and
conceptual records and fields are presented at the internal relationships between them is shown in following figure:
level. It enables DBMS to find the actual record or

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

capable of being sent to the run time data manager for

execution. The query processor uses the data dictionary to
find the structure of the relevant portion of the database
and uses this information in modifying the query and
preparing an optimal plan to access the database.

ii. Run time database manager:

Run time database manager is the central software

component of the DBMS, which interfaces with user-
submitted application programs and queries. It handles
database access at run time. It converts operations in
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. It accepts queries and
examines the external and conceptual schemas to
determine what conceptual records are required to satisfy
the users request. The run time data manager then places
a call to the physical database to perform the request. It
enforces constraints to maintain the consistency and
integrity of the data, as well as its security. It also
performs backing and recovery operations. Run time
database manager is sometimes referred to as the
database control system and has the following
Following steps are followed while executing users request components:
to access the database system:
• Authorization control: The authorization control
i. Users issue a query using particular database module checks that the user has necessary
language, for example, SQL commands. authorization to carry out the required operation.
ii. The passed query is presented to a query optimiser, • Command processor: The command processor
which uses information about how the data is stored processes the queries passed by authorization control
to produce an efficient execution plan for evaluating module.
the query. • Integrity checker: The integrity checker checks for
iii. The DBMS accepts the users SQL commands and necessary integrity constraints for all the requested
analyses them. operations that changes the database.
iv. The DBMS produces query evaluation plans, that is, • Query optimizer: The query optimizer determines an
the external schema for the user, the corresponding optimal strategy for the query execution. It uses
external/conceptual mapping, the conceptual schema, information on how the data is stored to produce an
the conceptual/internal mapping, and the storage efficient execution plan for evaluating query.
structure definition. Thus, an evaluation plan is a • Transaction manager: The transaction manager
blueprint for evaluating a query. performs the required processingof operations it
v. The DBMS executes these plans against the physical receives from transactions. It ensures that
database and returns the answers to the users. (a) transactions request and release locks according to
a suitable locking protocol and
Components of a DBMS
(b) schedules the execution of transactions.
The DBMS accepts the SQL commands generated from a • Scheduler: The scheduler is responsible for ensuring
variety of user interfaces, produces query evaluation plans, that concurrent operations on the database proceed
executes these plans against the database, and returns the without conflicting with one another. It controls the
answers. The major software modules or components of relative order in which transaction operations are
DBMS are as follows: executed.
• Data manager: The data manager is responsible for
i. Query processor the actual handling of data in the database. This
module has the following two components:
The query processor transforms users queries into a series Recovery manager: The recovery manager ensures
of low-level instructions directed to the run time database that the database remains in a consistent state
manager. It is used to interpret the online user’s query and in the presence of failures. It is responsible for
convert it into an efficient series of operations in a form (a) transaction commit and abort operations,

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

(b) maintaining a log, and be defined on them, and enforce set of constraints (or
(c) restoring the system to a consistent state after a integrity rules) to ensure accuracy of data.
crash.
Buffer manager: The buffer manager is responsible It consists of the following:
for the transfer of data between the main
memory and secondary storage. It brings in pages • Static properties, for example, objects, attributes and
from the disk to the main memory as needed in relationships.
response to read user requests. Buffer manager is • Integrity rules over objects and operations.
sometimes referred as the cache manager • Dynamic properties, for example, operations or rules
defining new database states based on applied state
iii. DML processor: changes.

Using a DML compiler, the DML processor converts the Data models can be broadly classified into the following
DML statements embedded in an application program into three categories:
standard function calls in the host language. The DML
compiler converts the DML statements written in a host 1. Record-based data models
programming language into object code for database 2. Object-based data models
access. The DML processor must interact with the query 3. Physical data models
processor to generate the appropriate code.
2.6.1 Record-based Data Models
iv. DDL processor:
A record-based data models are used to specify the overall
Using a DDL compiler, the DDL processor converts the logical structures of the database. In the record-based
DDL statements into a set of tables containing metadata. models, the database consists of a number of fixed-format
These tables contain the metadata concerning the records possibly of different types. Each record type
database and are in a form that can be used by other defines a fixed number of fields, each typically of a fixed
components of the DBMS. These tables are then stored in length. Data integrity constraints cannot be explicitly
the system catalog while control information is stored in specified using record-based data models. There are three
data file headers. The DDL compiler processes schema principle types of record-based data models:
definitions, specified in the DDL and stores description of
1. Hierarchical data model.
the schema (metadata) in the DBMS system catalog. The
2. Network data model.
system catalog includes information such as the names of
3. Relational data model.
data files, data items, storage details of each data file,
mapping information amongst schemas, and constraints.
1. Hierarchical data model
2.6 DATA MODELS
This is one of the oldest models in a data model which was
developed by IBM, in the 1950s. In a hierarchical model,
A model is an abstraction process that concentrates
data are viewed as a collection of tables, or we can say
essential and inherent aspects of the organisation’s
segments that form a hierarchical relation. In this, the
applications while hides superfluous or accidental details.
data is organized into a tree-like structure where each
It is a representation of the real-world objects and events
record consists of one parent record and many children.
and their associations. A data model or database model is
Even if the segments are connected as a chain-like
a mechanism that provides this abstraction for database
structure by logical associations, then the instant
application. It represents the organisation itself. It provides
structure can be a fan structure with multiple branches.
the basic concepts and notations to allow database
designers and end-users unambiguously and accurately
In the hierarchical model, segments pointed to by the
communicate their understanding of the organisational
logical association are called the child segment and the
data.
other segment is called the parent segment. If there is a
segment without a parent is then that will be called
Data modelling is used for representing entities of interest
the root and the segment which has no children are called
and their relationships in the database. It allows the
the leaves. The main disadvantage of the hierarchical
conceptualisation of the association between various
model is that it can have one-to-one and one-to-many
entities and their attributes. A data model is a conceptual
relationships between the nodes.
method of structuring data. It provides mechanism to
structure data (consisting of a set of rules according to
Hierarchical Database Advantages
which databases can be constructed) for the entities being
modelled, allow a set of manipulative operations (for • Fast and efficient data retrieval: One of the key
example, updating or retrieving data from the database) to advantages of hierarchical databases is that they allow

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

for fast and efficient data retrieval, since data is which each set is composed of at least two record types.
organized in a predictable and structured way. First record type is called an owner record that is
equivalent to the parent in the hierarchical model. Second
• Easy to add/delete information: In this type of record type is called a member record that is equivalent to
database, we can easily add or delete the information. child in the hierarchical model. The connection between an
owner and its member records is identified by a link to
• Predictable data structure: The hierarchical database which database designers assign a set-name.
model has a predictable and structured data
structure, which makes it easier to understand and This set-name is used to retrieve and manipulate data.
navigate. Just as the branches of a tree in the hierarchical data
models represent access path, the links between owners
• Efficient storage of data: Since hierarchical and their members indicate access paths in network
databases store data in a parent-child relationship, models and are typically implemented with pointers. In
they can be more efficient in terms of storage network data model, member can appear in more than one
compared to other database models. set and thus may have several owners, and therefore, it
facilitates many-to-many (n:m) relationships. A set
• Good performance: Hierarchical databases can have represents a one-to-many (1:m) relationship between the
good performance in terms of data retrieval and owner and the member.
querying, especially for large datasets.
Advantages of Network Data Model
Hierarchical Database Disadvantages
• Simplicity: Similar to hierarchical data model,
• Limited flexibility: One of the major disadvantages of network model is also simple and easy to design.
hierarchical databases is that they are not as flexible
• Facilitating more relationship types: The network
as other types of databases, and are not well suited for
model facilitates in handling of one-to-many and
handling complex data relationships or changes in
many-to-many relationships, which helps in modelling
data structures.
the real-life situations.
• Superior data access: The data access and flexibility
• Difficult to maintain and update: Hierarchical
is superior to that is found in the hierarchical data
databases can be difficult to maintain and update,
model. An application can access an owner record and
since changes to the data structure can impact the
all the members record within a set. If a member
entire database.
record in the set has two or more, then one can move
• Limited support for data manipulation: Hierarchical from one owner to another.
databases do not support complex data manipulation • Database integrity: Network model enforces database
operations, such as data aggregation or data mining. integrity and does not allow a member to exist without
an owner. First of all, the user must define the owner
• Limited interoperability: Hierarchical databases are record and then the member.
not compatible with other database models, which can • Data independence: The network data model provides
limit their interoperability with other systems and sufficient data independence by at least partially
applications. isolating the programs from complex physical storage
details. Therefore, changes in the data characteristics
• Lack of standardization: Since hierarchical do not require changes in the application programs.
databases are not as widely used as other database • Database standards: Unlike hierarchical model,
models, there is a lack of standardization and best network data model is based on the universal
practices for their use and management. standards formulated by DBTG/CODASYL and
augmented by ANSI-SPARC.
2. Network Data Model
Disadvantages of Network Data Model
The Database Task Group of the Conference on Data
System Languages (DBTG/CODASYL) formalized the • System complexity: Like hierarchical data model,
network data model in the late 1960s. The network data network model also provides a navigational access
models were eventually standardised as the CODASYL mechanism to the data in which the data are accesses
model. The network data model is similar to a hierarchical one record at a time. This mechanism makes the
model except that a record can have multiple parents. The system implementation very complex.
network data model has three basic components such as • Absence of structural independence: It is difficult to
record type, data items (or fields) and links. Further, in make changes in a network database. If changes are
network model terminology, a relationship is called a set in made to the database structure, all subschema

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

definitions must be revalidated before any applications the growth of database, the poor design results into
programs can access the database. slower system, degraded performance and data
• Not a user-friendly: The network data model is not a corruption.
design for user-friendly system and is a highly skill-
oriented system. 2.6.2 Object-based Data Models

3. Relational Data Model Object-based data models are used to describe data and
its relationships. It uses concepts such as entities,
E.F. Codd of IBM Research first introduced the relational attributes and relationships. It has flexible data
data model in a paper in 1970. The relational data model structuring capabilities. Data integrity constraints can be
is implemented using very sophisticated Relational explicitly specified using object-based data models.
Database Management System (RDBMS). The RDMS
performs the same basic functions of the hierarchical and The object-oriented data models extend the definition of an
network DBMSs plus a host of other functions that make entity to include not only the attributes that describe the
the relational data models easier to understand and state of the object but also the actions that are associated
implement. The relational data model simplified the user’s with the object, that is, its behaviour. Following are the
view of the database by using simple tables instead of the common types of object-based data models:
more complex tree and network structures.
1. Entity-relationship
It is a collection of tables (also called relations) in which 2. Semantic
data is stored. Each of the tables is a matrix of a series of 3. Functional
row and column intersections. Tables are related to each 4. Object-oriented
other by sharing common entity characteristic.
1. E-R data model
Advantages of Relational Data Model
An entity-relationship (E-R) model is a logical database
• Simplicity: A relational data model is even simpler model, which has a logical representation of data for an
than hierarchical and network models. It frees the enterprise of business establishment. It was introduced by
designers from the actual physical data storage Chen in 1976. E-R data model is a collection of objects of
details, thereby allowing them to concentrate on the similar structures called an entity set. The relationship
logical view of the database. between entity sets is represented on the basis of number
• Structural independence: Unlike hierarchical and of entities from entity set that can be associated with the
network models, the relational data model does not number of entities of another set such as one-to-one (1:1),
depend on the navigational data access system. one-to-many (1:n), or many-to-many (n:n) relationships.
Changes in the database structure do not affect the The E-R diagram is shown graphically.
data access.
The rectangular boxes represent entity, ellipses (or oval
• Ease of design, implementation, maintenance and
boxes) represent attributes (or properties) and diamonds
uses: The relational model provides both structural
represent relationship (or association) among entity sets.
independence and data independence. Therefore, it
There is no industry standard notation for developing E-R
makes the database design, implementation,
diagram.
maintenance and usage much easier.
• Flexible and powerful query capability: The Advantages of E-R Data Model
relational database model provides very powerful,
flexible, and easy-to-use query facilities. Its structured • Straightforward relational representation: Having
query language (SQL) capability makes ad hoc queries designed an E-R diagram for a database application,
a reality. the relational representation of the database model
becomes relatively straightforward.
Disadvantages of Relational Data Model • Easy conversion for E-R to other data model:
Conversion from E-R diagram to a network or
• Hardware overheads: The relational data models need
hierarchical data model can easily be accomplished.
more powerful computing hardware and data storage
• Graphical representation for better understanding:
devices to perform RDMS-assigned tasks.
An E-R model gives graphical and diagrammatical
• Easy-to-design capability leading to bad design:
representation of various entities, its attributes and
Easy-to-use feature of relational database results into
relationships between entities. This in turn helps in
untrained people generating queries and reports
the clear understanding of the data structure and in
without much understanding and giving much
minimizing redundancy and other problems.
thought to the need of proper database design. With

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

Disadvantages of E-R Data Model system for financial, telecommunications, and World Wide
Web (WWW) applications. It is suited for multimedia
• No industry standard for notation: There is no applications as well as data with complex relationships
industry standard notation for developing an E-R that are difficult to model and process in a relational
diagram. DBMS.
• Popular for high-level design: The E-R data model is
especially popular for high-level database design. Advantages of Object-oriented Data Model

2. Object-oriented Data Model • Capable of handling a large variety of data types:

Unlike traditional databases (such as hierarchical,
Object-oriented data model is a logical data model that network or relational), the object-oriented database are
captures the semantics of objects supported in an object- capable of storing different types of data, for example,
oriented programming. It is a persistent and sharable pictures, voices, video, including text, numbers and so
collection of defined objects. It has the ability to model on.
complete solution. Object-oriented database models • Combining object-oriented programming with
represent an entity and a class. A class represents both database technology: Object-oriented data model is
object attributes as well as the behaviour of the entity. capable of combining object-oriented programming
with database technology and thus, providing an
For example, a CUSTOMER class will have not only the integrated application development system.
customer attributes such as CUST-ID, CUST-NAME,
• Improved productivity: Object-oriented data models
CUST-ADD and so on, but also procedures that imitate
provide powerful features such as inheritance,
actions expected of a customer such as update-order.
polymorphism and dynamic binding that allow the
Instances of the class-object correspond to individual
users to compose objects and provide solutions
customers. Within an object, the class attributes takes
without writing object-specific code. These features
specific values, which distinguish one customer (object)
increase the productivity of the database application
from another. However, all the objects belonging to the
developers significantly.
class, share the behaviour pattern of the class. The object-
• Improved data access: Object-oriented data model
oriented database maintains relationships through logical
represents relationships explicitly, supporting both
containment.
navigational and associative access to information. It
further improves the data access performance over
The object-oriented database is based on encapsulation of
relational value-based relationships.
data and code related to an object into a single unit, whose
contents are not visible to the outside world. Therefore,
Disadvantages of Object-oriented Data Model
object-oriented data models emphasise on objects (which
is a combination of data and code), rather than on data • No precise definition: It is difficult to provide a
alone. This is largely due to their heritage from object- precise definition of what constitutes an object-
oriented programming languages, where programmers can oriented DBMS because the name has been applied to
define new types or classes of objects that may contain a variety of products and prototypes, some of which
their own internal structures, characteristics and differ considerably from one another.
behaviours. Thus, data is not thought of as existing by
• Difficult to maintain: The definition of objects is
itself. Instead, it is closely associated with code (methods required to be changed periodically and migration of
of member functions) that defines what objects of that type
existing databases to confirm to the new object
can do (their behaviour or available services). The definition with change in organisational information
structure of object-oriented data model is highly variable. needs. It posses real challenge when changing object
Unlike traditional databases (such as hierarchical, definitions and migrating databases.
network or relational), it has no single inherent database
• Not suited for all applications: Object-oriented data
structure. The structure for any given class or type of
models are used where there is a need to manage
object could be anything a programmer finds useful, for
complex relationships among data objects. They are
example, a linked list, a set, an array and so forth.
especially suited for specific applications such as
Furthermore, an object may contain varying degrees of
engineering, e-commerce, medicines and so on, and
complexity, making use of multiple types and multiple
not for all applications. Its performance degrades and
structures.
requires high processing requirements when used for
ordinary applications.
The object-oriented database management system
(OODBMS) is among the most recent approaches to
database management. They started in the engineering
and design domain applications, and became the favoured

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

2.6.3 Physical Data Models We will discuss about some of the important types of
DBMS system, which are presently being used.
Physical data models are used for a higher-level
description of storage structure and access mechanism. 1. Centralised Database System
They describe how data is stored in the computer,
representing information such as record structures, record The centralised database system consists of a single
orderings and access paths. It is possible to implement the processor together with its associated data storage devices
database at system level using physical data models. There and other peripherals. It is physically confined to a single
are not as many physical data models so far. The most location. The system offers data processing capabilities to
common physical data models are as follows: users who are located either at the same site, or, through
remote terminals, at geographically dispersed sites. The
1. Unifying model management of the system and its data are controlled
2. Frame memory model centrally form any one or central site.

Comparison between Data models

Advantages of Centralised Database System

• Most of the functions such as update, backup, query,

control access and so on, are easier to accomplish in a
centralised database system.
• The size of the database and the computer on which it
resides need not have any bearing on whether the
2.7 TYPES OF DATABASE SYSTEMS
database is centrally located.
The classification of a DBMS is greatly influenced by the
Disadvantages of Centralised Database System
underlying computing system on which it runs, in
particular of computer architecture such as parallel, • When the central site computer or database system
networked or distributed. However, the DBMS can be goes down, then every users is blocked from using the
classified according to the number of users, the database system until the system comes back.
site locations and the expected type and extent of use. • Communication costs from the terminals to the central
site can be expensive.
1. On the basis of the number of users
2. Parallel Database System
i. Single-user DBMS
ii. Multi-user DBMS Parallel database systems architecture consists of a
multiple CPUs and data storage disks in parallel. Hence,
2. On the basis of the site locations
they improve processing and I/O speeds. Parallel database
i. Centralised DBMS. systems are used in the applications that have to query
ii. Parallel DBMS. extremely large databases or that have to process an
iii. Distributed DBMS. extremely large number of transactions per second.
iv. Client/server DBMS. Several different architectures can be used for parallel
database systems such as Shared data storage disk,
3. On the basis of the type and the extent of use Shared memory, Hierarchical and Independent resources.

i. Transactional or production DBMS. Advantages of a Parallel Database System

ii. Decision support DBMS.
iii. Data warehouse. • Parallel database systems are very useful for the
applications that have to query extremely large

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

databases or that have to process an extremely large operating systems, network protocols and other software
number of transactions per second. are interconnected as a network and work in concert to
• In a parallel database system, the throughput (that is, achieve user goals. It is well suited for online transaction
the number of tasks that can be completed in a given processing and decision support applications, which tend
time interval) and the response time (that is, the to generate a number of relatively short transactions and
amount of time it takes to complete a single task from require a high degree of concurrency.
the time it is submitted) are very high.
Advantages of Client/server Database System
Disadvantages of a Parallel Database System
• Client-server system has less expensive platforms to
• In a parallel database system, there is a startup cost support applications that had previously been running
associated with initiating a single process and the only on large and expensive mini or mainframe
startup-time may overshadow the actual processing computers.
time, affecting speedup adversely. • Clients offer icon-based manu-driven interface, which
• Since processes executing in a parallel system often is superior to the traditional commandline, dumb
access shared resources, a slowdown may result from terminal interface typical of mini and mainframe
interference of each new process as it competes with computer systems.
existing processes for commonly held resources, such • Client/server environment facilitates in more
as shared data storage disks, system bus and so on. productive work by the users and making better use of
existing data.
3. Client/Server Database System • Client-server database system is more flexible as
compared to the centralised system.
Client/server architecture of database system has two
• Response time and throughput is high.
logical components namely client, and server. Clients are
• The server (database) machine can be custom-built
generally personal computers or workstations whereas
(tailored) to the DBMS function and thus can provide a
server is large workstations, mini range computer system
better DBMS performance.
or a mainframe computers system. The applications and
• The client (application database) might be a personnel
tools of DBMS run on one or more client platforms, while
workstation, tailored to the needs of the end users and
the DBMS software reside on the server. The server
thus able to provide better interfaces, high availability,
computer is called backend and the client’s computer is
faster responses and overall improved ease of use to
called front-end. These server and client computers are
the user.
connected into a network. The applications and tools act
• A single database (on server) can be shared across
as clients of the DBMS, making requests for its services.
several distinct client (application) systems.
The DBMS, in turn, processes these requests and returns
the results to the client(s). Client/server architecture
Disadvantages of Clien/Server Database System
handles the graphical user interface (GUI) and does
computations and other programming of interest to the • Labour or programming cost is high in client/server
end user. The server handles parts of the job that are environments, particularly in initial phases.
common to many clients, for example, database access • There is a lack of management tools for diagnosis,
and updates. performance monitoring and tuning and security
control, for the DBMS, client and operating systems
The client/server database architecture consists of three
and networking environments.
components namely, client applications, a DBMS server
and a communication network interface. The client 4. Distributed Database System
applications may be tools, user-written applications or
vendor-written applications. They issue SQL statements Distributed database systems are similar to client/server
for data access. The DBMS server stores the related architecture in a number of ways. Both typically involve
software, processes the SQL statements and returns the use of multiple computer systems and enable users to
results. The communication network interface enables access data from remote system. However, distributed
client applications to connect to the server, send SQL database system broadens the extent to which data can be
statements and receive results or error messages or error shared well beyond that which can be achieved with the
return codes after the server has processed the SQL client/server system.
statements. In client/server database architecture, the
in distributed database system, data is spread across a
majority of the DBMS services are performed on the server.
variety of different databases. These are managed by a
The client/server architecture is a part of the open variety of different DBMS software running on a variety of
systems architecture in which all computing hardware, different computing machines supported by a variety of

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 2: ARCHITECTURE OF DBMS

different operating systems. These machines are spread (or Disadvantages of Distributed Database System
distributed) geographically and connected together by a
variety of communication networks. In distributed Recovery from failure is more complex in distributed
database system, one application can operate on data that database systems than in centralized systems.
is spread geographically on different machines. Thus, in
distributed database system, the enterprise data might be
distributed on different computers in such a way that data
for one portion (or department) of the enterprise is stored
in one computer and the data for another department is
stored in another. Each machine can have data and
applications of its own.

However, the users on one computer can access to data

stored in several other computers. Therefore, each
machine will act as a server for some users and a client for
others.

Advantages of Distributed Database System

• Distributed database architecture provides greater

efficiency and better performance.
• Response time and throughput is high.
• The server (database) machine can be custom-built
(tailored) to the DBMS function and thus
• can provide better DBMS performance.
• The client (application database) might be a personnel
workstation, tailored to the needs of the end users and
thus able to provide better interfaces, high availability,
faster responses and overall improved ease of use to
the user.
• A single database (on server) can be shared across
several distinct client (application) systems.
• As data volumes and transaction rates increase, users
can grow the system incrementally.
• It causes less impact on ongoing operations when
adding new locations.
• Distributed database system provides local autonomy.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

3.1 INTRODUCTION 3.3 Entities

An entity-relationship (E-R) model was introduced by P.P An entity is an ‘object’ or a ‘thing’ in the real world with an
Chen in 1976. E-R model is an effective and standard independent existence and that is distinguishable from
method of communication amongst different designers, other objects. Entities are the principle data objects about
programmers and end-users who tend to view data and its which information is to be collected. For example, person,
use in different ways. car, house, employee, company or an event of
informational interest.
It is a nontechnical method, which is free from ambiguities
and provides a standard and a logical way of visualising In E-R modelling, entities are considered as abstract but
the data. It gives precise understanding of the nature of meaningful ‘things’ that exist in the user enterprise. Such
the data and how it is used by the enterprise. It provides things are modelled as entities that may be described by
useful concepts that allow the database designers to move attributes.
from an informal description of what users want from their
database, to a more detailed and precise description that Entity Set and Entity Instance
can be implemented in a database management system.
An entity set (entity type) is a set of entities of the same
Thus, E-R modelling is an important technique for any type that share the same properties or attributes. In E-R
database designer to master. It has found wide acceptance modelling, similar entities are grouped into an entity type.
in database design. It can have objects with physical or real existence or
objects with a conceptual or abstract existence.
3.2 BASIC E-R CONCEPTS
Each entity type is identified by a name and a list of
E-R modelling is a high-level conceptual data model properties. A database normally contains many different
developed to facilitate database design. A conceptual data entity types and not to a single entity occurrence. In other
model is a set of concepts that describe the structure of a words, the word ‘entity’ in the E-R modelling corresponds
database and the associated retrieval and update to a table and not to a row in the relational environment.
transactions on the database.
The E-R model refers to a specific table row as an entity
It is independent of any particular DBMS and hardware instance or entity occurrence. An entity instance is a
platform. E-R model is also defined as a logical uniquely identifiable object of an entity type.
representation of data for an enterprise. It was developed
to facilitate database design by allowing specification of an Classification of Entity Types
enterprise schema, which represents the overall logical
structure of a database. It is a top-down approach to There are two types of entity strong or weak entity.
database design.
Strong entity: An entity type that is not existence-
It is sometimes regarded as a complete approach to dependent on some other entity type is called strong entity
designing a logical database schema. E-R model is one of a type. The strong entity type has a characteristic that each
several semantic data model. It is very useful in mapping entity occurrence is uniquely identifiable using the
the meanings and interactions of real-world enterprise primary key attribute(s) of that entity type.
onto a conceptual schema. Many database design tools
Strong entity types are sometimes referred to as parent,
draw on concepts from the E-R model. E-R model provides
owner or dominant entities.
the following main semantic concepts to the designers:

Entities: which are distinct objects in a user enterprise.

Relationships: which are meaningful interactions among

the objects.

Attributes: which describe the entities and relationships. Weak entity: Weak entity types are sometimes referred to
Each such attribute is associated with a value set (also as child, dependent or subordinate entities. An entity type
called domain) and can take a value from this value set. that is existence- dependent on some other entity type is
called weak entity type. The week entity type has a
Constraints: on the entities, relationships and attributes. characteristic that each entity occurrence cannot be
uniquely identifiable using only the attributes associated
with that entity type.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

Each entity type that participates in a relationship type

plays a particular role in the relationship. Relationships
may be given role names to signify the purpose that each
participating entity type plays in a relationship. Role
names can be important for recursive relationships to
determine the function of each participant.
3.4 Relationship
II. Binary Relationship
A relationship is an association among two or more
entities that is of interest to the enterprise. It represents The association between the two entities is called binary
real world association. Relationship as such, has no relationship. Binary relationship is the most common type
physical or conceptual existence other than that which relationship and its degree of relationship is two (2).
depends upon their entity associations.
III. Ternary Relationship
A particular occurrence of a relationship is called a
A ternary relationship is an association among three
relationship instance or relational occurrence. Relationship
entities and its degree of relationship is three (3). The
occurrence is a uniquely identifiable association, which
construct of ternary relationship is a single diamond
includes one occurrence from each participating entity
connected to three entities.
type. It indicates the particular entity occurrences that are
related. Relationships are also treated as abstract objects.

Relation set

In E-R modelling, similar relationships are grouped into

relationship sets (relationship type). Thus, a relationship
type is a set of meaningful associations between one or
more participating entity types. Each relationship type is
given a name that describes its function. Relationships
with the same attributes fall into one relationship set.

2. Connectivity of a Relationship

The connectivity of a relationship describes a constraint on

the mapping of the associated entity occurrences in the
relationship. Values for occurrences are either ‘one’ or
Relationships are described in the following types: ‘many’.

1. Degree of a relationship The actual count of elements associated with the

2. Connectivity of a relationship connectivity is called cardinality of the relationship
3. Existence of a relationship connectivity. Cardinality is used much less frequently than
4. n-ary Relationship the connectivity constraint because the actual values are
usually variable across relationship instances.
1. Degree of a Relationship

The degree of a relationship is the number of entities

associated or participants in the relationship. Following
are the three degrees of relationships:

I. Recursive Relationship

A recursive (unary) relationship is a relationship between

the instances of a single entity type. It is a relationship
type in which the same entity type is associated more than
once in different roles. Thus, the entity relates only to
another instance of its own type. For example, a recursive
binary relationship ‘manages’ relates an entity PERSON to
another PERSON by management.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

There are four basic constructs of connectivity for binary For example, a course be assigned to many students and a
relationship: student can be assigned to many courses.

i. one to-one (1:1)

In case of one-to-one connection, exactly one PERSON

manages the entity DEPT and each person manages
exactly one DEPT. Therefore, the maximum and minimum
3. n-Array Relationship
connectivities are exactly one for both the entities.
In case of n-ary relationship, a single relationship diamond
For example, a person has only one passport and a
with n connections, one to each entity, represents some
passport is given to one person.
association among n entities.

An n-ary relationship has n+1 possible variations of

connectivity. All n-sides have connectivity ‘one’, n-1 sides
with connectivity ‘one and one side with connectivity
‘many’, n-2 sides with connectivity ‘one’ and two sides with
‘many’ and so on until all sides are ‘many’.
ii. one-to-many (1:N)

In case of one-to-many (1:N), the entity DEPT is associated

to many PERSON, whereas each person works within
exactly one DEPT. The maximum and minimum
connectivities to the PERSON side are of unknown value N,
and one respectively. Both maximum and minimum
connectivities on DEPT side are one only.

For example, a customer can place many orders but a

order cannot be placed by many customers.

4. Existence of a Relationship

In case of existence relationship, the existence of entities of

an enterprise depends on the existence of another entity.
Existence of an entity in a relationship is defined as either
iii. Many to One Relationship (N:1)
mandatory or optional.
When more than one instances of an entity is associated
i. Mandatory existence
with a single instance of another entity then it is called
many to one relationship. In a mandatory existence, an occurrence of either the ‘one’
or ‘many’ sides entity must always exist for the entity to be
For example, many students can study in a single college
included in the relationship. In case of mandatory
but a student cannot study in many colleges at the same
existence there is only line perpendicular to the
time.
connection.

ii. optional existence

In case of optional existence, the occurrence of that entity

need not exist. The optional existence is defined by letter
iv. many-to-many (M:N) 0 (zero) and a line perpendicular to the connection line
between an entity and relationship.
In case of many-to-many (M:N) connectivity, the entity
PERSON may work on many PROJECTS and each project Note:
may be handled by many persons. Therefore, maximum
connectivity for PERSON and PROJECT are M and N If neither a zero nor a perpendicular line is shown on the
respectively, and minimum connectivities are each defined connection line between the relationship and entity, then it
as one. is called the unknown type of existence. In such a case, it
is neither optional nor mandatory and the minimum
connectivity is assumed to be one.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

For example, as shown in Figure, the entity PERSON may 2. Single-valued and multi-valued attribute
or may not be the manager of any DEPT, thus making the
entity DEPT optional in the ‘is-managed-by’ relationship A single-valued attribute is an attribute that holds a single
between PERSON and DEPT. value for each occurrence of an entity type. For example,
Emp Id. The majority of attributes are single-valued.

A multi-valued attribute is an attribute that holds multiple

values for each occurrence of an entity type. That means,
multi-valued attributes can take more than one value. For
example, Mobile number.

3.5 ATTRIBUTES 3. Derived attribute

An attribute is a property of an entity or a relationship A derived attribute is an attribute that represents a value
type. An entity is described using a set of attributes. All that is derivable from the value of a related attribute or set
entities in a given entity type have the same or similar of attributes, not essentially in the same entity set.
attributes. For example, an EMPLOYEE entity type could Therefore, the value held by some attributes are derived
use name (NAME), social security number (SSN), date of from two or more attribute values. For example, Project-
birth (DOB) and so on as attributes. start-date.

A domain of possible values identifies each attribute

associated with an entity type. Each attribute is associated
with a set of values called a domain. The domain defines
the potential values that an attribute may hold and is
4. Key attribute
similar to the domain concept in relational model.
A key attribute can uniquely identify an entity from an
The attributes hold values that describe each entity
entity set. For example, student roll number can uniquely
occurrence and represent the main part of the data stored
identify a student from a set of students. Key attribute is
in the database.
represented by oval same as other attributes however the
Attributes can be classified into the following three text of key attribute is underlined
categories:

1. Simple and Composite attribute

A simple attribute is an attribute composed of a single

component with an independent existence. A simple
attribute cannot be subdivided or broken down into
smaller components. Simple attributes are sometimes
called atomic attributes. Example: EMP-ID, EMP-Salary 5. Descriptive (Relationship) attribute
etc.
The attribute(s) used for relationship rather than entity is
A composite attribute is an attribute composed of multiple called descriptive attributes. They are actually used for
components, each with an independent existence. For storing information about the relationship. A relationship
example, EMP-NAME attribute of EMPLOYEE entity holds can have zero or more attributes.
data as ‘Rahul Sharma. Now this attribute can be further
divided into FIRST-NAME and LAST-NAME attributes such
that they hold data namely ‘Rahul’ and ‘Sharma’
respectively.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

3.6 CONSTRAINTS For example: In the below diagram each college must have
at-least one associated Student.
Relationship types usually have certain constraints that
limit the possible combinations of entities that may
participate in the corresponding relationship set. The
constraints should reflect the restrictions on the
relationships as perceived in the ‘real world’. For example,
there could be a requirement that each department in the
entity DEPT must have a person and each person in the
PERSON entity must have a skill. The main types of
constraints on relationships are multiplicity, cardinality,
participation and so on.

1. Multiplicity Constraints

Multiplicity is the number (or range) of possible ii. Partial participation

occurrences of an entity type that may relate to a single
occurrence of an associated entity type through a Partial participation constraints means that some or the
particular relationship. It constrains the way that entities ‘part of the set of’ an entity are related to another entity via
are related. It is a representation of the policies and a relationship, but not necessarily all.
business rules established by the enterprise or the user. It
is important that all appropriate enterprise constraints are
identified and represented while modelling an enterprise.

2. Cardinality Constraints

A cardinality constraint specifies the number of instances

of one entity that can be associated with each instance of The cardinality ratio and participation constraints are
together known as the structural constraints of a
entity. There are two types of cardinality constraints
namely minimum and maximum cardinality constraints. relationship type.

i. minimum cardinality 3.7 KEY CONCEPT

The minimum cardinality constraint of a relationship is the Key is a concept is DBMS by which we can uniquely
minimum number of instances of an entity that may be identifies each entity or we can remove redundancy from
associated with each instance of another entity. the DBMS.

ii. maximum cardinality 1. Super Key

A super key is an attribute or set of attributes used to

The maximum cardinality constraint of a relationship is the
maximum number of instances of one entity that may be identify the records uniquely in a relation. For example, in
associated with a single occurrence of another entity. relation STUDENT Roll No is a super key so Roll No is
unique for each person. Similarly (Roll No, Name) and (Roll
3. Participation Constraints No, Age) are also supper keys of the relation STUDENT
since their combination is also unique for each record.
The participation constraint specifies whether the
existence of an entity depends on its being related to 2. Candidate keys
another entity via the relationship type. There are two
types of participation constraints namely total and partial Super keys of a relation can contain extra attributes.
participation constraints. Candidate keys are minimal super key i.e. such a key
contains no extraneous attributes. An attribute is called
i. Total participation extraneous if even after removing it from the key, makes
the remaining attributes still has the properties of a key.
A Total participation of an entity set represents that each
entity in entity set must have at least one relationship in a Following properties are satisfied by candidate keys:
relationship set. Total participation is also called existence
dependency. • A candidate key must be unique.

• A candidate key’s value must exist. It cannot be null.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

• A candidate key is a minimal set of attributes. Rule-03: Convert multivalued attributes of strong
entity into table.
• The value of candidate key must be stable.
• Split the relations of multivalued strong entity set into
3. Primary key two tables.
• One table define all the simple attributes with the
A relation can have more than one candidate keys and one primary key and other table define multi valued
of them can be chosen as a primary key. For example, in attributes with primary key.
relation STUDENT the two possible candidate keys are Roll
No and Name. Roll No may be chosen as the primary key.

4. Foreign key

A foreign key is an attribute or combination of attribute in

one base table that points to the candidate key (generally it
Table Schema: Table1(Roll_no, City)
is the primary key) of another table. The purpose of the
foreign key is to ensure referential integrity of the data i.e. Table2(Roll_no, Mobile_no)
only values that are supposed to appear in the database
are permitted. Rule-04: Convert Relationship Set into a Table

• A relationship set will convert into one table.

Converting ER Diagrams to Tables in DBMS • Relationship table have primary keys of connected
entities and own attributes.
Following rules are used for converting an ER diagram into
tables.
• Set of non-descriptive attributes will be the primary
key.
Rule-01: Convert strong entity into a table

• A strong entity set with only simple attributes will

directly convert into one table.

• All Attributes of the entity set will be the attributes of

the table.

• The primary key of the entity set will be the key

attribute of the table.
Table schema: Works in ( Emp_no , Dept_id , since )

Rule-05: Convert Binary Relationships (Cardinality

Ratios) into the table.

The following four cases are possible

Table Schema: Student (Roll_no, Name, Sex)
Case-01: For Binary Relationship with Cardinality
Ratio 1:1
Rule-02: Convert Composite Attributes of a strong
entity

All composite attributes convert as simple attributes in the

table.

Here, two tables will be required. Either combine ‘R’ with

‘A’ or ‘B’

Way-01: AR ( a1 , a2 , b1 ), B ( b1, b2 )
Table Schema: Student (Roll_no, First_name, Last_name,
City, Street, House_no)

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

Way-02: A ( a1 , a2 ), BR ( a1 , b1 , b2 ) Case-01: In Binary Relationship (Cardinality Constraint

and Total Participation Constraint) From One Side
Case-02: For Binary Relationship with Cardinality (cardinality ratio = 1 : n)
Ratio m:1

Here, two tables will be required

AR ( a1 , a2 , b1 ), B ( b1 , b2 ) We will combine entity set B and relationship set R.

Combined table will be drawn for the entity set A and Then, two tables will be required
relationship set R.
A ( a1 , a2 ), BR ( a1, b1 , b2 )
Case-03: For Binary Relationship With Cardinality
Ratio 1:n Case-02: In Binary Relationship (Cardinality Constraint
and Total Participation Constraint) From Both Sides

If a key constraint from both the sides of an entity is set

with total participation, then the binary relationship is
represented only single table.

Here, two tables will be required

A ( a1, a2 ), BR ( a1 , b1 , b2 )

The combined table will be drawn for entity set B and

relationship set R. Here, Only one table is required ARB ( a1, a2, b1,b2 )

Case-04: For Binary Relationship With Cardinality Rule-07: Convert Binary Relationship with weak Entity
Ratio m:n Set into the table

Weak entity sets are always used in association with

identifying relationship and total participation constraints.

Here, three tables will be required

A ( a1 , a2 ), R (a1, b1), B ( b1, b2)

Here, two tables will be required
Rule-06: Convert Binary Relationship With Both
Cardinality Constraints and Participation Constraints
A (a1 , a2 ), BR ( a1 , b1 , b2 )
into the table.

The total participation constraint acquires NOT NULL

constraint foreign key.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

Enchanced Entity- Relationship (EER) Model

There are various abstractions available to capture

semantic features, which cannot be explicitly modelled by
entity and relationships.

Enhanced Entity-Relationship (EER) model uses such

additional semantic concepts incorporated into the original
E-R model to overcome these problems. The EER model
consists of all the concepts of the E-R model together with
the following additional concepts:

• Specialisation/Generalisation
• Categorisation.

SUPERCLASS AND SUBCLASS ENTITY TYPES

Subclasses (or subtypes) and superclasses (or supertypes)

are the special type of entities.

Subclasses are the sub-grouping of occurrences of entities

in an entity type that is meaningful to the organisation
and that shares common attributes or relationships
distinct from other sub-groupings. Subtype is one of the
data-modelling abstractions used in EER. In this case,
objects in one set are grouped or subdivided into one or
more classes in many systems. The objects in each class
may then be treated differently in certain circumstances. The supertype/subtype relationships should be used when
either or both of the following conditions are satisfied:
Superclass is a generic entity type that has a relationship
with one or more subtypes. It includes an entity type with • There are attributes that are common to some (but not
one or more distinct sub-groupings of its occurrences, all) of the instances of an entity type.
which is required to be represented in a data model. Each • The instances of a subtype participate in a
member of the subclass or subtype is also a member of the relationship unique to that subtype.
superclass or supertype. That means, the subclass
member is the same as the entity in the superclass, but SPECIALISATION
has a distinct role. The relationship between a superclass
Specialisation is the process of identifying subsets of an
and subclass is a one-to-one (1:1) relationship. In some
entity set (the superclass or supertype) that share some
cases, a superclass can have overlapping subclasses.
distinguishing characteristic. In other words,
The superclass is connected with a line to a circle. The specialisation maximises the differences between the
circle in turn is connected by line to each subtype that has members of an entity by identifying the distinguishing and
been defined. The U-shaped symbols on each line unique characteristics (attributes) of each member.
connecting a subtype to the circle, indicates that the
Specialisation is a top-down process of defining
subtype is a subset of the supertype. It also indicates the
superclasses and their related subclasses. Typically, the
direction of the supertype/subtype relationship. The
superclass is defined first, the subclasses are defined next
attributes shared by all the entities (including the
and subclass-specific attributes and relationship sets are
identifier) of the supertype (or shared by all the subtypes)
then added.
are associated with the supertype entity. The attributes
that are unique to a particular subtype are associated with
the respective subtype.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 3: E-R DIAGRAM

CATEGORISATION

Categorisation is a process of modelling of a single subtype

(or subclass) with a relationship that involves more than
one distinct supertype (or superclass). Till now all the
relationships that have been discussed, are a single
distinct supertype. However, there could be need for
modelling a single supertype/subtype relationship with
more than one supertype, where the supertypes represent
different entity set.

For example, let us assume that a vehicle is purchased in

a company for transportation of goods from one
department to another. Now, the owner of the vehicle can
be a department, an employee or the company itself. This
is a case of modelling a single supertype/subtype
relationship with more than one supertype, where the
supertypes represent three entity types. In this case, the
subtype represents a collection object that is a subset or
the union of distinct entity types. Thus, a category called
Generalisation OWNER can be created as a subtype of the UNION of the
three entity sets of DEPARTMENT, EMPLOYEE and
Generalisation is the process of identifying some common
COMPANY. The supertype and subtype is connected to the
characteristics of a collection of entity sets and creating a
circle with the ‘U’ symbol.
new entity set that contains entities processing these
common characteristics. In other words, it is the process
of minimising the differences between the entities by
identifying the common features.

Generalisation is a bottom-up process, just opposite to the

specialisation process. It identifies a generalised
superclass from the original subclasses. Typically, these
subclasses are defined first, the superclass is defined next
and any relationship sets that involve the superclass are
then defined.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 4: RELATIONAL MODEL

4.1 INTRODUCTION • Production of various commercial relational DBMS

such as DB2 and SQL/DS from IBM, ORACLE from
The relational database model originated from the Oracle Corporation during 1970s and 1980s
mathematical concept of a relation and set theory. It was
first proposed as an approach to data modelling by Dr. The second development was of a relational DBMS
Edgar F. Codd of IBM Research in 1970 in his paper INGRESS (Interactive Graphics Retrieval System) at the
entitled “A Relational Model of Data for Large Shared Data University of California at Berkeley USA. The INGRES
Banks”. This paper marked the beginning of the field of project involved the development of a prototype RDBMS,
relational database. with the research concentrating on the same overall
objectives as of the System R project.
The relational model uses the concept of a mathematical
relation in the form of a table of values as its building The third development was the Peterlee Relational Test
block. The relational database became operational only in Vehicle at the IBM UK Scientific Centre in Peterlee. The
mid-1980s. Apart from the widespread success of the project had more theoretical orientation than the System R
hierarchical and network database models in commercial and INGRES projects and was significant, principally for
data processing until early-1980s, the main reasons for research into such issues as query processing,
the delay in development and implementation of relational optimisation and functional extension.
model were:
4.3 STRUCTURE OF RELATIONAL DATABASE
• Inadequate capabilities of the contemporary
hardware. The relational model is based on the core concept of
• Need to develop efficient implementation of simple relation. In the relational model, all data is logically
relational operations. structured within relations (table). Informally a relation
• Need for automatic query optimisation. may be viewed as a named two-dimensional table
• Unavailability of efficient software techniques. representing an entity set. A relation has a fixed number of
• Requirement of increased processing power. named columns (attributes) and a variable number of rows
(tuples). Each tuple represents an instance of the entity set
• Requirement of increased input/output (I/O)
and each attribute contains a single value of some
speeds to achieve comparable performance.
recorded property for the particular instance.
4.2 HISTORY OF RELATIONAL MODEL
All members of the entity set have the same attributes. The
While introducing a relational model to the database number of tuples is called cardinality, and the number of
community in 1970, Dr. E.F. Codd stressed on the attributes is called the degree.
independence of the relational representation from
physical computer implementation such as ordering on
physical devices, indexing and using physical access path.
Dr. Codd also proposed criteria for accurately structuring
relational databases and an implementation independent
language to operate on these databases. On the basis of
his proposal, the most significant research towards three
developments resulted into overwhelming interest in the
relational model.

The first development was of prototype relational (DBMS)

System R at IBM’s San Jose Research Laboratory in 4.3.1 Domain
California USA during the late 1970s. System R provided
the practical implementation of its data structures and A domain is a set of atomic values. Atomic means that
operations. It also provided information about transaction each value in the domain is indivisible to the relational
management, concurrency control, recovery techniques, model. Domain is usually specified by name, data type,
query optimisation, data security, integrity, user interface format and constrained range of values. The value of each
and so on. System R led to the following two major attribute within each tuple is atomic, that means it is a
developments: single value drawn from the domain of the attribute.
Multiple or repeating values are not permitted.
• A structured query language called SQL, also
pronounced S-Q-L, or See-Quel.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 4: RELATIONAL MODEL

• Non-redundancy: If an attribute is removed from the

set of attributes, the remaining attributes will not
possess the uniqueness property.
• Validity: No attribute value in the key may be null.

A relation key can be made up of one or many attributes.

Relation keys are logical and bear no relationship to how
the data are to be accessed. It only specifies that a relation
have at most row with a given value of the relation key.

3. Candidate key

Super keys of a relation can contain extra attributes.

Candidate keys are minimal super key, i.e. such a key
contains no extraneous attribute. An attribute is called
The EMPLOYEE relation has six attributes (field items), extraneous if even after removing it from the key, makes
namely EMP-NO, LAST-NAME, FIRST-NAME, DATE-OF- the remaining attributes still has the properties of a key.
BIRTH, SEX, TEL-NO and SALARY. The extension has The following properties must be satisfied by the candidate
seven tuples (records). Each attribute contains values keys:
drawn form a particular domain. Domain is usually
specified by name, data type, format and constrained • A candidate key must be unique.
range of values. • A candidate key’s value must exist. It cannot be
null.
4.3.2 Keys of Relations • A candidate key is a minimal set of attributes.
• The value of a candidate key must be stable. Its
A relation always has a unique identifier, a field or group value cannot change outside the control of the
of fields (attributes) whose values are unique throughout system.
all of the tuples of the relation. Thus, each tuple is
distinct, and can be identified by the values of one or more A relation can have more than one candidate keys and one
of its attributes called key. Keys are always minimal of them can be chosen as a primary key
sequences of attributes that provide the uniqueness
quality. For example, in the relation PERSON the two possible
candidate keys are PERSON-ID and NAME (assuming
1. Super key unique names in the table). PERSON-ID may be chosen as
the primary key.
Superkey is an attribute, or set of attributes, that uniquely
identifies a tuple within a relation. In fact, all the 4. Primary Key
attributes in a relation taken together are a superkey
because only one row in a relation has a given value for all Primary key is a candidate key that is selected to identify
the relation attributes. tuples uniquely within the relation.

Super Key is the superset of a candidate key. The Primary 5. Composite Key
Key of a table is picked from the super key set to be made
the table’s identity attribute. if we use multiple attributes to create a Primary Key then
that Primary key is called Composite key. It is also called a
2. Relation Key compound key or concatenated key.

Relation key is defined as a set of one or more relation E.g. of composite Key, if we have used “Name, Address” as
attributes concatenated. Most of the relational theory a Primary Key then it will be our Composite key.
restricts the relation key to a minimum number of
attributes and excludes any unnecessary one. Such 6. Alternate key
restricted keys are called relation keys. Following three
properties should hold for all time and for any instance of Alternate key can be any of the candidate Keys except for
the relation: the primary key.

e.g. of alternate key is “Name Address” as it is the only

• Uniqueness: A set of attributes has a unique value in
other Candidate key which is not a primary key
the relation for each tuple.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 4: RELATIONAL MODEL

7. Foreign Key I. Entity Integrity Constraint

A foreign key may be defined as an attribute, or set of It states that no primary key value can be null. This is
attributes, within one relation that matches the candidate because the primary key is used to identify individual
key of some (possibly the same) relation. tuple in the relation. So, we will not be able to identify the
records uniquely containing null values for the primary
Thus, as shown in below figure, the foreign key in relation key attributes. This constraint is specified on one
R1 is a set of one or more attributes that is a relation key individual relation.
in another relation R2, but not a relation key of relation
R1. The foreign key is used in regard to database integrity.

II. Referential integrity constraint

It states that the tuple in one relation that refers to

another relation must refer to an existing tuple in that
relation. This constraint is specified on two relations (not
necessarily distinct). It uses a concept of foreign key.

4.5 CONSTRAINT VIOLATIONS

4.4 RELATIONAL CONSTRAINTS There are three basic operations to be performed on

relations:
There are three types of constraints on relational database
that include: 1. Insertion
2. Deletion
1. DOMAIN CONSTRAINT 3. Update
2. PRIMARY KEY CONSTRAINT
3. INTEGRITY CONSTRAINT 1. The Insertion operation

1.Domain Constraint The insert operation allows us to insert a new tuple in a

relation. When we try to insert a new record, then any of
It specifies that each attribute in a relation must contain the following four types of constraints can be violated:
an atomic value only from the corresponding domains.
The data types associated with commercial RDBMS • Domain constraint: If the value given to an
domains include: attribute lies outside the domain of that attribute.
• Key constraint: If the value of the key attribute in
• Standard numeric data types for integer (such as new tuple t is the same as in the existing tuple in
short- integer, integer, long integer) relation R.
• Real numbers (float, double precision floats) • Entity Integrity constraint: If the primary key
• Characters attribute value of new tuple t is given as null.
• Fixed length strings and variable length strings. • Referential Integrity constraint: If the value of
the foreign key in t refers to a tuple that doesn’t
Domain Constraint does not allow, composite or appear in the referenced relation.
multivalued attributes.
Dealing with constraints violation during insertion:
2. Key Constraint
If the insertion violates one or more constraints, then two
This constraint states that the key attribute value in each options are available:
tuple must be unique, i.e., no two tuples contain the same
value for the key attribute. It is also called uniqueness • Default option: - Insertion can be rejected and the
constraint. This is because the value of the primary key is reason of rejection can also be explained to the user
used to identify the tuples in the relation. by DBMS.
• Ask the user to correct the data, resubmit, also
3. Integrity Constraint
give the reason for rejecting the insertion.
There are two types of integrity constraints:
2. The Deletion Operation
I. Entity Integrity Constraint
Using the delete operation some existing records can be
II. Referential Integrity Constraint
deleted from a relation. To delete some specific records

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 4: RELATIONAL MODEL

from the database a condition is also specified based on We will understand these anomalies with the help of the
which records can be selected for deletion. following student table:

Constraints that can be violated during deletion Id Name age dept_id dept_name dept_head
1 Shiva 19 104 IT Jaspreet
Only one type of constraint can be violated during deletion, 2 Khushi 18 102 CS Rahul
3 Harsh 19 104 IT Jaspreet
it is referential integrity constraint. It can occur when
we want to delete a record in the table where it is
referenced by the foreign key of another table. 1. Insertion Anomaly

Dealing with Constraints Violation Insertion anomaly arises when we are trying to insert some
data into the database, but we are not able to insert it.
If the deletion violates referential integrity constraint, then
three options are available: Example: If we want to add the details of the student in
the above table, then we must know the details of the
• Default option: Reject the deletion.
department; otherwise, we will not be able to add the
• Attempt to cascade (propagate): Delete the entry
details because student details are dependent on
from referred table and also delete all the
department details.
corresponding entry from referring table.
• Change the value of referencing attribute that 2. Deletion Anomaly
causes the violation.
Deletion anomaly arises when we delete some data from
3. The Update Operation the database, but some unrelated data is also deleted; that
is, there will be a loss of data due to deletion anomaly.
Update operations are used for modifying database values.
The constraint violations faced by this operation are Example: If we want to delete the student detail, which
logically the same as the problem faced by Insertion and has id 2, we will also lose the unrelated data, i.e., dept_id
Deletion Operations. 102, from the above table.

4.6 REDUNDANCY AND ASSOCIATED PROBLEMS 3. Updating Anomaly

Data redundancy means the occurrence of duplicate An update anomaly arises when we update some data in
copies of similar data. It is done intentionally to keep the the database, but the data is partially updated, which
same piece of data at different places, or it occurs causes data inconsistency.
accidentally. In DBMS, when the same data is stored in
different tables, it causes data redundancy. Example: If we want to update the details of dept_head
from Jaspreet to Ankit for Dept_id 104, then we have to
Sometimes, unintentional duplicity of data causes a update it everywhere else; otherwise, the data will get
problem for the database to work properly, or it may partially updated, which causes data inconsistency.
become harder for the end user to access data. Redundant
data unnecessarily occupy space in the database to save
identical copies, which leads to space constraints, which is
one of the major problems.

Id Name Course Session Fee Department

101 Rahl BCA 2024 40,000 CS
102 Sohan BCA 2024 40,000 CS
103 Varun BCA 2024 40,000 CS
104 Satish BCA 2024 40,000 CS
105 Anuj BCA 2024 40,000 CS

Problems that are caused due to redundancy

Redundancy in DBMS gives rise to anomalies. In DBMS,

the problems that occur while working on data include
inserting, deleting, and updating data in the database.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 5: RELATIONAL DATA DESIGN

5.1 Functional Dependencies Formally, the set of all dependencies that include F as well
as all dependencies that can be inferred from F is called
A functional dependency is a constraint between two sets the closure of F and it is denoted by F+.
of attributes from the database. Suppose that our
relational database schema has n attributes A1 A2,…,An. The inference rule is a type of assertion. It can apply to a
let us think of the whole database as being described by a set of FDs to derive other FDs. Using the inference rule, we
single universal relation schema R = {A1, A 2 ,..., A n } . can derive additional functional dependency from the
initial set.
We do not imply that we will actually store the database as
The Functional dependency has 6 types of inference rule:
a single universal table; we use this concept only in
developing the formal theory of data dependencies.
1. Reflexive Rule (IR1)
Definition: A functional dependency, denoted by X → Y , In the reflexive rule, if Y is a subset of X, then X
between two sets of attributes X and Y that are subsets of determines Y i.e. If X ⊇ Y then X→Y
R specifies a constraint on the possible tuples that can
form a relation state r of R. The constraint is that, for any Example: X={a, b, c, d}, Y={b, d}
two tuples tl and t2 in r that have t1[X] = t2[X], they must
also have t1[Y] = t2[Y]. 2. Augmentation Rule (IR2)

This means the values of the X component of a tuple The augmentation is also called as a partial dependency.
uniquely or functionally determine the values of the Y In augmentation, if X determines Y, then XZ determines
component. The set of attributes X is called the left-hand YZ for any Z i.e. If X→Y then XZ→ YZ
side of the FD, and Y is called the right-hand side.
Example: For R(ABCD), if A→B then AC→BC
A functional dependency (FD) is a relationship between two
attributes, typically between the primary key and other 3. Transitive Rule (IR3)
non-key attributes within a table. A functional dependency
In the transitive rule, if X determines Y and Y determine Z,
denoted by X → Y , is an association between two sets of
then X must also determine Z i.e., If X→Y and Y→Z then
attribute X and Y. Here, X is called the determinant, and Y
X→Z
is called the dependent.
4. Union Rule (IR4)
A functional dependency is a property of the semantics or
meaning of the attributes. The database designers will use Union rule says, if X determines Y and X determines Z,
their understanding of the semantics of the attributes of R then X must also determine Y and Z i.e.,
that is, how they relate to one another to specify the If X →Y and X→Z then X→YZ
functional dependencies that should hold on all relation
states (extensions) r of R. Whenever the semantics of two 5. Decomposition Rule (IR5)
sets of attributes in R indicate that a functional
dependency should hold, we specify the dependency as a Decomposition rule is also known as project rule. It is the
constraint. reverse of union rule. This Rule says, if X determines Y
and Z, then X determines Y and X determines Z separately
For example, Id{Name, Address, Dob} i.e., If X→YZ then X→Y and X→Z.

Here, Id determines Name, Address and Dob. So, Id is the 6. Pseudo transitive Rule (IR6)
determinant and Name, Address and Dob are the
dependents. In Pseudo transitive Rule, if X determines Y and YZ
determines W, then XZ determines W i.e.,
5.2 Inference Rules (Armstrong axioms) If X→Y and YZ→W then XZ→W.

We denote by F the set of FDs that are specified on relation Note:

schema R. Typically, the schema designer specifies the
FDs that are semantically obvious. However, numerous Inference rules IR1 through IR3 are known as Armstrong's
other FDs hold in all legal relation instances that satisfy inference rules.
the dependencies in F. Those other dependencies can be
inferred or deduced from the FDs in F.

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in
CHAPTER 5: RELATIONAL DATA DESIGN

H.O. : B-5, Dev Nagar, opp. Kamal & Company, Gopalpura, Jaipur Tel : 0141-2708941, Mob : 9887755436, 9413223636 Visit us at : www.fluxeducare.in