Page 1

Module 1 – Part 1
Fundamentals of Database Management Systems
Common Terminologies used
 Data means, known facts that can be recorded and that have implicit meaning.
 A database is a collection of related data.
 A database management system (DBMS) is a collection of programs that enables users to create and
maintain a database effectively and efficiently. The DBMS is a general-purpose software system that
facilitates the processes of defining, constructing, manipulating, and sharing databases among various
users and applications.
 Defining a database involves specifying the data types, structures, and constraints of the data to be
stored in the database.
 Constructing the database is the process of storing the data on some storage medium that is con-
trolled by the DBMS.
 Manipulating a database includes functions such as querying the database to retrieve specific data,
updating the database to reflect changes in the miniworld, and generating reports from the data.
 Sharing a database allows multiple users and programs to access the database simultaneously.
 A query typically causes some data to be retrieved; a transaction may cause some data to be read and
some data to be written into the database.
 An application program accesses the database by sending queries or requests for data to the DBMS.
 Other important functions provided by the DBMS include protecting the database and maintaining it
over a long period of time.
 Protection includes system protection against hardware or software malfunction (or crashes) and
security protection against unauthorized or malicious access.
 A typical large database may have a life cycle of many years, so the DBMS must be able to
maintain the database system by allowing the system to evolve as requirements change over time.

Figure: A simplified DBS environment.

 Page 2

Example: Student Database

Figure: A database that stores student and course information.

Characteristics of the Database Approach

 In traditional file processing, each user defines and implements the files needed for a specific software
application as part of programming the application.
 Redundancy in defining and storing data results in wasted storage space and in redundant efforts to
maintain common up-to-date data.
 Page 3

 In the database approach, a single repository maintains data that is defined once and then accessed by
various users
 The main characteristics of the database approach versus the file processing approach are the following:
1. Self-describing nature of a database system
2. Insulation between programs and data, and data abstraction
3. Support of multiple views of the data
4. Sharing of data and multiuser transaction processing

1. Self-Describing Nature of a Database System

 A fundamental characteristic of the database approach is that the database system contains not only
the database itself but also a complete definition or description of the database structure and
constraints.
 This definition is stored in the DBMS catalog, which contains information such as the structure of
each file, the type and storage format of each data item, and various constraints on the data. The
information stored in the catalog is called meta-data and it describes the structure of the primary
database.

2. Insulation between Programs and Data, and Data Abstraction

 In traditional file processing, the structure of data files is embedded in the application programs, so
any changes to the structure of a file may require changing all programs that access that file.
 DBMS access programs do not require such changes in most cases. The structure of data files is
stored in the DBMS catalog separately from the access programs. This property is called program-
data independence.
 An operation (also called a function or method) is specified in two parts:
 Interface
o The interface (or signature) of an operation includes the operation name and the data
types of its arguments (or parameters).
 Implementation
o The implementation (or method) of the operation is specified separately and can be
changed without affecting the interface.
 User application programs can operate on the data by invoking these operations through their names
and arguments, regardless of how the operations are implemented. This is termed as program-
operation independence.
 The characteristic that allows program-data independence and program operation independence is
called data abstraction.
 A data model is a type of data abstraction that is used to provide conceptual representation. A
DBMS provides users with a conceptual representation of data that does not include many of the
details of how the data is stored or how the operations are implemented.

3. Support of Multiple Views of the Data

 A database has many users, each user may require a different perspective or view of the database. A
view may be a subset of the database or it may contain virtual data that is derived from the database
files but is not explicitly stored.
 A multiuser DBMS whose users have a variety of distinct applications must provide facilities for
defining multiple views.

4. Sharing of Data and Multiuser Transaction Processing

 Page 4

 A multiuser DBMS, must allow multiple users to access the database at the same time. This is
essential if data for multiple applications is to be integrated and maintained in a single database.
 The DBMS must include concurrency control software to ensure that several users trying to update
the same data do so in a controlled manner so that the result of the updates is correct.
 A fundamental role of multiuser DBMS software is to ensure that concurrent transactions operate
correctly and efficiently.
 A transaction is an executing program or process that includes one or more database accesses, such
as reading or updating of database records.
 Each transaction is supposed to execute a logically correct database access if executed in its entirety
without interference from other transactions.
 The DBMS must enforce several transaction properties:
1. Isolation property
 The isolation property ensures that each transaction appears to execute in isolation from other
transactions, even though hundreds of transactions may be executing concurrently.
2. Atomicity property
 The atomicity property ensures that either all the database operations in a transaction are
executed or none are executed.

Actors on the scene

The people whose jobs involve the day-to-day use of a large database are called as the actors on the scene.

1. Database Administrators
2. Database Designers
3. System Analysts and Application Programmers (Software Engineers)
4. End Users

1. Database Administrators
 In a database environment, the primary resource is the database itself, and the secondary resource is
the DBMS and related software. Administering these resources is the responsibility of the database
administrator (DBA).
 The DBA is responsible for authorizing access to the database, coordinating and monitoring its use
and acquiring software and hardware resources as needed. The DBA is accountable for problems
such as security breaches and poor system response time.

2. Database Designers
 Database designers are responsible for identifying the data to be stored in the data-base and for
choosing appropriate structures to represent and store this data.
 It is the responsibility of database designers to communicate with all prospective database users in
order to understand their requirements and to create a design that meets these requirements.
 Database designers typically interact with each potential group of users and develop views of the
database that meet the data and processing requirements of these groups.
 Each view is then analyzed and integrated with the views of other user groups. The final database
design must be capable of supporting the requirements of all user groups.

3. System Analysts and Application Programmers (Software Engineers )

 Page 5

 System analysts determine the requirements of end users, especially naive and parametric end users,
and develop specifications for standard canned transactions that meet these requirements.
 Application programmers implement these specifications as programs, then they test, debug,
document, and maintain these canned transactions. Such analysts and programmers commonly
referred to as software developers or software engineers.

4. End Users
 End users are the people whose jobs require access to the database for querying, updating, and
generating reports.
 There are several categories of end users:
a) Casual end users
o Casual end users occasionally access the database, but they may need different information
each time.
o They use a sophisticated database query language to specify their requests and are typically
middle or high level managers or other occasional browsers.
b) Naive or parametric end users
o Naive or parametric end users make up a sizable portion of database end users.
o Their main job function revolves around constantly querying and updating the database,
using standard types of queries and updates called canned transactions that have been
carefully programmed and tested.
o The tasks that such users perform are varied.
c) Sophisticated end users
o Sophisticated end users include engineers, scientists, business analysts, and others who
thoroughly familiarize themselves with the facilities of the DBMS in order to implement their
own applications to meet their complex requirements.
d) Standalone users
o Standalone users maintain personal databases by using readymade program packages that
provide easy-to-use menu based or graphics based interfaces.
o An example is the user of a tax package that stores a variety of personal financial data for tax
purposes.

Workers behind the scene

The people who work to maintain the database system environment but who are not actively interested in the
database contents as part of their daily job are called as the workers behind the scene.
1. DBMS system designers and implementers
2. Tool developers
3. Operators and maintenance personnel (system administration personnel)

1. DBMS system designers and implementers

 DBMS system designers and implementers design and implement the DBMS modules and interfaces
as a software package.
 A DBMS is a very complex software system that consists of many components, or modules,
including modules for implementing the catalog, query language processing, interface processing,
accessing and buffering data, controlling concurrency, and handling data recovery and security.
 Page 6

2. Tool developers
 Tool developers design and implement tools, the software packages that facilitate database modeling
and design, database system design, and improved performance.
 Tools are optional packages that are often purchased separately. They include packages for database
design, performance monitoring, natural language or graphical interfaces, prototyping, simulation,
and test data generation.

3. Operators and maintenance personnel (system administration personnel)

 Operators and maintenance personnel (system administration personnel) are responsible for the
actual running and maintenance of the hardware and software environment for the database system.

Advantages of using DBMS

1. Controlling Redundancy
 Redundancy in storing the same data multiple times leads to several problems.
a) Duplication of effort
b) Storage space is wasted
c) Files that represent the same data may become inconsistent.

2. Restricting Unauthorized Access

 A DBMS should provide a security and authorization subsystem, which the DBA uses to create
accounts and to specify account restrictions.

3. Providing Persistent Storage for Program Objects

 Databases can be used to provide persistent storage for program objects and data structures.

4. Providing Storage Structures and Search Techniques for Efficient Query Processing
 Database systems must provide capabilities for efficiently executing queries and updates.
 The database is typically stored on disk, the DBMS must provide specialized data structures and
search techniques to speed up disk search for the desired records. Auxiliary files called indexes are
used for this purpose.

5. Providing Backup and Recovery

 A DBMS must provide facilities for recovering from hardware or software failures. The backup and
recovery subsystem of the DBMS is responsible for recovery.
 For example, if the computer system fails in the middle of a complex update transaction, the
recovery subsystem is responsible for making sure that the database is restored to the state it was in
before the transaction started executing.

6. Providing Multiple User Interfaces

 Users with varying levels of technical knowledge use a database, a DBMS should provide a variety
of user interfaces.
 Page 7

 These include query languages for casual users, programming language interfaces for application
programmers, forms and command codes for parametric users, and menu-driven interfaces and
natural language interfaces for standalone users.
 Both forms-style interfaces and menu-driven interfaces are commonly known as graphical user
interfaces (GUIs).

7. Representing Complex Relationships among Data

 A DBMS must have the capability to represent a variety of complex relationships among the data, to
define new relationships as they arise, and to retrieve and update related data easily and efficiently.

8. Enforcing Integrity Constraints

 Integrity constraints are used to ensure accuracy and consistency of data in Database.
o Referential integrity constraint
o Key or uniqueness constraint
 A DBMS should provide capabilities for defining and enforcing these constraints. The simplest type
of integrity constraint involves specifying a data type for each data item.

9. Permitting Inferencing and Actions Using Rules and Triggers

 Some database systems provide capabilities for defining deduction rules for inferencing new
information from the stored database facts. Such systems are called deductive database systems.
 A trigger is a form of a rule activated by updates to the table, which results in performing some
additional operations.
 Procedures to enforce rules are popularly called stored procedures
 The improved functionality of triggers and stored procedures are used by databases called active
databases.

10. Additional Implications of Using the Database Approach

 Potential for Enforcing Standards.
 Reduced Application Development Time
 Flexibility.
 Availability of Up-to-Date Information
 Economies of Scale.