Database Management System (DBMS)

UNIT 1
Overview of Database Management System (DBMS)
What is data:
Data is the known facts or figures that have implicit meaning. It can also be
defined as it is the representation of facts ,concepts or instruction in a formal
manner, which is suitable for understanding and processing. Data can be
represented in alphabets(A-Z, a-z),in digits(0-9) and using special characters(+,-
.#,$, etc) e.g: 25, “ajit” etc.

Information: Information is the processed data on which decisions and actions

are based. Information can be defined as the organized and classified data to
provide meaningful values. Eg: “The age of Ravi is 25” .

Data in the database may be persistent, integrated and shared.

• Persistent: If data is removed from database due to some explicit request

from user to remove.
• Integrated: A database can be a collection of data from different files and
when any redundancy among those files are removed from database is said
to be integrated data.
• Sharing Data: The data stored in the database can be shared by multiple
users simultaneously with out affecting the correctness of data.

Why Database: In order to overcome the limitation of a file system, a new

approach was required. Hence a database approach emerged. A database is a
persistent collection of logically related data. The initial attempts were to
provide a centralized collection of data. A database has a self describing
nature. It contains not only the data sharing and integration of data of an
organization in a single database.
A small database can be handled manually but for a large database and having
multiple users it is difficult to maintain it, In that case a computerized database is
useful. The advantages of database system over traditional, paper based
methods of record keeping are:

• Compactness: No need for large amount of paper files

• speed: The machine can retrieve and modify the data more faster way
then human being
• Less drudgery: Much of the maintenance of files by hand is eliminated
• Accuracy: Accurate, up-to-date information is fetched as per requirement
of the user at any time.

A Database Management System (DBMS) is software that enables users to

create, store, retrieve, and manage data efficiently. It provides a systematic
approach to managing databases and ensures consistency, integrity, and security
of data.

Importance of DBMS

• Organizes data systematically.

• Enhances data security and integrity.

• Supports multiple users simultaneously.

• Facilitates easy access and manipulation of data.

Function of DBMS:
1. Defining database schema: it must give facility for defining the database
structure also specifies access rights to authorized users.
2. Manipulation of the database: The dbms must have functions like insertion of
record into database updation of data, deletion of data, retrieval of data
3. Sharing of database: The DBMS must share data items for multiple users by
maintaining consistency of data.
4. Protection of database: It must protect the database against unauthorized
users.
5. Database recovery: If for any reason the system fails DBMS must facilitate data
base recovery.
Examples of DBMS

• Relational DBMS (RDBMS): MySQL, PostgreSQL, Oracle, MS SQL Server

• NoSQL DBMS: MongoDB, Cassandra

• Hierarchical DBMS: IBM Information Management System (IMS)

• Network DBMS: Integrated Data Store (IDS)

2. Advantages and Disadvantages of DBMS

Advantages of DBMS

1. Data Redundancy Control: Eliminates duplicate data storage.

2. Data Consistency: Ensures that all copies of data remain consistent.

3. Data Security: Prevents unauthorized access using authentication

techniques.

4. Data Integrity: Ensures valid, accurate, and reliable data.

5. Data Independence: Application programs are independent of data storage

structures.

6. Backup and Recovery: Automates data backup and recovery processes.

7. Multi-user Access: Supports multiple users accessing the database

concurrently.

8. Query Processing: Allows efficient data retrieval using SQL.

Disadvantages of DBMS

1. High Cost: Requires expensive hardware and software.

2. Complexity: Requires skilled personnel to manage and maintain the

system.

3. Performance Issues: May be slower due to concurrent access.

4. Security Risks: Data is vulnerable to cyber threats.

 5. Hardware Dependency: Requires high processing power and storage
resources.

3. Characteristics of DBMS
1. Real-world Entity Representation: Data is modeled based on real-world
entities.

2. Relation-based Tables: Uses tables to store related data.

3. Data Abstraction: Hides complexity from users.

4. Multi-user Access: Allows concurrent access to data.

5. Data Security and Integrity: Ensures data privacy and accuracy.

6. Backup and Recovery Mechanisms: Ensures data protection against

failures.

7. Data Independence: Separates data from application programs.

8. Transaction Management: Ensures ACID (Atomicity, Consistency, Isolation,

Durability) properties.

Components of Database Management System

1 Storage Engine
2 Query Language
3 Query Processor
4 Optimization Engine
5 Metadata Catalog
6 Log Manager
7 Reporting and Monitoring Tools
8 Data Utilities
Database Architectures –
• Database architecture focuses on the design, development, implementation and
maintenance of computer programs that store and organize information for
businesses, agencies and institutions.
• A database architect develops and implements software to meet the needs of users.
• The design of a DBMS depends on its architecture. It can be centralized or
decentralized or hierarchical.
1-tier architecture:
• One-tier architecture involves putting all of the required components for a software
application or technology on a single server or platform.
• One-tier architecture keeps all of the elements of an application, including the
interface, Middleware and back-end data, in one place.

2-tier architecture:
• The two-tier architecture is like client server application.
• The direct communication takes place between client and server. There is no
intermediate between client and server.

3-tier architecture:
• The 3-Tier architecture contains another layer between the client and server. In this
architecture, client can't directly communicate with the server.
• The application on the client-end interacts with an application server which further
communicates with the database system.
Database Management System vs File Management System

File Management System –

1 A file system is a software that manages and organizes the files in a storage medium.
2 The file system provides the details of data representation and storage of data.

3 Storing and retrieving of data can’t be done efficiently in a file system.

4 The file system doesn’t have a crash recovery mechanism.

5 In a file management system, the redundancy of data is greater.

6 The file system offers lesser security.

7 The centralization process is hard in File Management System.

8 There is no efficient query processing in the file system.

9 Protecting a file system is very difficult.

10 It does not offer data recovery processes.

Database Management System –

1 DBMS is a software application. It is used for accessing, creating, and managing databases.

2 DBMS gives an abstract view of data that hides the details

3 DBMS is efficient to use as there are a wide variety of methods to store and retrieve data.

4 DBMS provides a crash recovery mechanism.

 5 The redundancy of data is low in the DBMS system.

6 Database Management System offers high security.

7 Centralization is easy to achieve in the DBMS system.

8 You can easily query data in a database using the SQL language.

9 DBMS offers good protection mechanism.

10 There is a backup recovery for data in DBMS.

----------------------------------------------------------------------------

Data Models
A Data Model in a Database Management System (DBMS) is a conceptual
framework that defines how data is structured, stored, and manipulated within a
database. It provides an abstract representation of the organization of data,
relationships between different data entities, and constraints applied to ensure
data integrity.

Types of Data Models in DBMS

There are several types of data models used in DBMS, each with its unique way of
representing data:
1. Hierarchical Data Model
• The hierarchical model represents data in a tree-like structure with a
parent-child relationship.
• Data is organized into a hierarchy where a parent node can have multiple
child nodes, but a child node can have only one parent.
• It follows a one-to-many relationship (1:M).
Example: An organizational structure, where a company (root) has multiple
departments (child nodes), and each department has employees (sub-child
nodes).
Advantages:
✔ Efficient for one-to-many relationships.
✔ Fast retrieval of hierarchical data.
 Disadvantages:
✖ Complex to modify if hierarchy changes.
✖ Requires knowledge of data paths.

Diagram of Hierarchical Model:

2. Network Data Model

• The network model uses graph-based structure where multiple
relationships (many-to-many) are allowed.
• Data is represented as records connected by links.
Example: A university database, where students can enroll in multiple courses,
and each course can have multiple students.
Advantages:
✔ More flexible than hierarchical models.
✔ Supports complex relationships.
Disadvantages:
✖ Complex structure due to many relationships.
✖ Difficult to implement.
Diagram of Network Model:
 3. Relational Data Model
The relational model organizes data into tables (relations) consisting of rows
(tuples) and columns (attributes).

Each table has a primary key to uniquely identify records.

Example: A bank database where customers, accounts, and transactions are

stored in separate tables and linked through relationships.

Advantages:
✔ Easy to understand and implement.
✔ Supports SQL for data manipulation.

Disadvantages:
✖ Requires powerful hardware for large databases.
✖ Slower for complex queries.

Diagram of Relational Model:

 4. Object-Oriented Data Model -
This model combines the concepts of object-oriented programming (OOP) with
databases.

Data is represented as objects containing attributes (properties) and methods

(functions).

Example: A multimedia database where videos, images, and text are stored as
objects.

Advantages:
✔ Best for complex data like images and videos.
✔ Supports inheritance and encapsulation.

Disadvantages:
✖ Not widely used compared to relational models.
✖ Complex structure.

Diagram of Object-Oriented Model:

 5. Entity-Relationship (E-R) Model
The E-R Model represents data as entities (objects) and relationships.

It is used in database design phase to visualize relationships between different

data entities.

Example: A hospital management system where doctors treat patients, and

each patient is assigned to a room.

Advantages:
✔ Easy to design and understand.
✔ Helps in database structuring.

Disadvantages:
✖ Needs conversion to a relational model for implementation.
✖ Complex for large systems.

Diagram of ER Model:
 Comparison of Data Models

Why Choose a Specific Data Model?

• Hierarchical Model: Best for applications with a fixed structure, such as
file systems.
• Network Model: Used when relationships between data elements are
complex.
• Relational Model: Preferred for most modern applications because of its
simplicity and efficiency.
• Object-Oriented Model: Useful when dealing with complex data, such
as multimedia or CAD applications.
 Schema and Instances
A schema is the blueprint or design of a database, while an instance is the
actual data stored at a particular moment.
Schema
• Defines the structure of a database, including tables, attributes, and
relationships.
• Is static and does not change frequently.
• Example: A student database schema includes tables like Students (ID,
Name, Age, Course).
Instances
• Represent the actual content of the database at a specific time.
• Changes frequently as data is modified, inserted, or deleted.
• Example: A Students table instance might have data such as (101, John Doe,
20, Computer Science).

Types of Schema -:
A database management system that provides three level of data is said to
follow three level architecture .
• External Level -:
The external level is at the highest level of database abstraction . At this
level, there will be many views define for different users requirement. A
view will describe only a subset of the database. Any number of user views
may exist for a given global or subschema .Thus this level of abstraction is
concerned with different categories of users. Each external view is
described by means of a schema called schema or schema.
• Conceptual level : At this level of database abstraction all the
database entities and the relationships among them are included . One
conceptual view represents the entire database . This conceptual view is
defined by the conceptual schema. The conceptual schema hides the
details of physical storage structures and concentrate on describing entities
, data types, relationships, user operations and constraints. It describes all
 the records and relationships included in the conceptual view . There is
only one conceptual schema per database . It includes feature that specify
the checks to relation data consistency and integrity.
• Internal level : It is the lowest level of abstraction closest to the
physical storage method used . It indicates how the data will be stored and
describes the data structures and access methods to be used by the
database . The internal view is expressed by internal schema. The following
aspects are considered at this level: 1. Storage allocation e.g: B-tree,
hashing 2. access paths eg. specification of primary and secondary keys,
indexes etc 3. Miscellaneous eg. Data compression and encryption
techniques, optimization of the internal structures.
 Data Independence
Introduction to Data Independence
Data independence is a key feature of DBMS that allows the modification of the
database schema without requiring changes in the application programs. It ensures
flexibility in managing database structures and storage mechanisms.

Types of Data Independence

1. Logical Data Independence:

o Refers to the ability to change the logical schema without affecting the
external schema or application programs.

o Example: Adding a new column (PhoneNumber) to a Students table

should not impact how applications retrieve student data.
2. Physical Data Independence:

o Refers to the ability to change physical storage structures without

affecting the logical schema.

o Example: Changing the storage method from sequential storage to

indexed storage should not impact the structure of tables and their
relationships.

Importance of Data Independence

• Flexibility: Allows changes in database structure without modifying
applications.

• Reduced Maintenance Cost: Minimizes the effort required to update

applications.

• Improved Security: Ensures controlled access to different levels of data.

• Scalability: Supports future modifications without disrupting the database

environment.

Benefits of Data Independence

• Enhances flexibility in database management.

• Reduces maintenance effort.

• Facilitates data security and integrity.

 Key Insights:
✔ Logical Data Independence: Changes at the logical level (e.g., adding a new
column) do not affect users.

✔ Physical Data Independence: Changes in physical storage (e.g., moving to cloud) do

not impact the logical structure.

Database Languages and Interfaces

Database Languages
1. Data Definition Language (DDL)
DDL is used to define and manage database schema.

Commands:

• CREATE – Used to create database objects.

o Example: CREATE TABLE Students (ID INT, Name VARCHAR(50), Age

INT);

• ALTER – Modifies the structure of existing tables.

o Example: ALTER TABLE Students ADD PhoneNumber VARCHAR(15);

• DROP – Deletes tables or databases.

o Example: DROP TABLE Students;

2. Data Manipulation Language (DML)
DML is used for retrieving and manipulating data in a database.

Commands:

• INSERT – Adds new records.

o Example: INSERT INTO Students VALUES (101, 'John Doe', 20);

• UPDATE – Modifies existing records.

o Example: UPDATE Students SET Age = 21 WHERE ID = 101;

• DELETE – Removes records.

o Example: DELETE FROM Students WHERE ID = 101;

• SELECT – Retrieves data.

o Example: SELECT * FROM Students;

3. Data Control Language (DCL)

DCL manages user permissions and access control.

Commands:

• GRANT – Gives privileges to users.

o Example: GRANT SELECT ON Students TO user1;

• REVOKE – Removes privileges from users.

o Example: REVOKE SELECT ON Students FROM user1;

4. Transaction Control Language (TCL)
TCL ensures database consistency during transactions.

Commands:

• COMMIT – Saves all changes.

o Example: COMMIT;

• ROLLBACK – Reverts changes.

o Example: ROLLBACK;

• SAVEPOINT – Creates a save point.

o Example: SAVEPOINT save1;

Database Interfaces
1. Graphical User Interface (GUI)
• Provides easy access using buttons, forms, and visual elements.

• Example: phpMyAdmin for MySQL.

2. Command-Line Interface (CLI)

• Allows database interaction using SQL queries.

• Example: MySQL CLI, PostgreSQL psql.

3. Web-Based Interfaces
• Provides access via browsers.

• Example: AWS RDS Console, Firebase.

4. Application Programming Interface (API)

• Allows applications to interact with databases programmatically.

• Example: JDBC for Java, ODBC for Windows.

5. Embedded Database Interfaces

• Integrates databases into applications.

• Example: SQLite in mobile apps.

Conclusion
• Database Languages provide commands to define, manipulate, and secure
data.

• Database Interfaces offer various ways for users and applications to interact
with databases.

-------------------------------------------------------------------------------------------------------