Introduction to Database Management System

(DBMS)

Definition:
1) A Database Management System (DBMS) is a software
system designed to manage and organize data in a
structured manner.
2) It allows users to create, modify, and query a database
and manage security and access controls.
Key Features of DBMS:
1. Data Modeling: Tools for creating and modifying data
models, defining the structure and relationships of data.
2. Data Storage and Retrieval: Storing and retrieving data with
various methods for searching and querying.
3. Concurrency Control: Mechanisms for controlling concurrent
access to ensure multiple users can access data without
conflicts.
4. Data Integrity and Security: Tools for enforcing data integrity,
security constraints, and access controls.
5. Backup and Recovery: Mechanisms for backing up and
recovering data in case of system failure.
Types of DBMS:
1) Relational Database Management System (RDBMS): Data
is organized in tables with rows and columns, related
through primary and foreign keys.
2) Non-Relational Database Management System (NoSQL):
Data is organized in key-value pairs, documents, graphs, or
columns for large-scale, high-performance scenarios.
Database:
1) A collection of interrelated data organized into tables,
views, schemas, reports, etc.
2) Example: A university database organizing data about
students, faculty, admin staff, etc.
Database Languages
1. Data Definition Language (DDL):
1) CREATE: Create a database and its objects (tables,
indexes, views, stored procedures, functions, triggers).
2) ALTER: Alter the structure of an existing database.
3) DROP: Delete objects from the database.
4) TRUNCATE: Remove all records from a table, including
spaces allocated for records.
5) COMMENT: Add comments to the data dictionary.
6) RENAME: Rename an object.
2. Data Manipulation Language (DML):
1) SELECT: Retrieve data from a database.
2) INSERT: Insert data into a table.
3) UPDATE: Update existing data within a table.
4) DELETE: Delete all records from a database table.
5) MERGE: UPSERT operation (insert or update).
6) CALL: Call a PL/SQL or Java subprogram.
7) EXPLAIN PLAN: Interpretation of the data access path.
8) LOCK TABLE: Concurrency control.
3. Data Control Language (DCL):
1) GRANT: Grant permissions to users for running DML
commands on a table.
2) REVOKE: Revoke permissions from users for running
DML commands on a table.
4. Transactional Control Language (TCL):
1) ROLLBACK: Undo changes made in the database.
2) COMMIT: Apply or save changes in the database.
3) SAVEPOINT: Save data temporarily in the database.
5. Data Query Language (DQL):
o SELECT: Retrieve data from a table without modifying
it. Essential for data retrieval.
 Database Management System (DBMS) Tasks
1. Data Definition: Creation, modification, and removal of
definitions that define data organization.
2. Data Updation: Insertion, modification, and deletion of
actual data in the database.
3. Data Retrieval: Retrieval of data from the database for
various applications.
4. User Administration: Registering and monitoring users,
enforcing security, monitoring performance, maintaining
integrity, handling concurrency control, and recovering
corrupted data.
Applications of DBMS
1. Enterprise Information: Sales, accounting, human
resources, manufacturing, online retailers.
2. Banking and Finance Sector: Customer details, accounts,
loans, banking transactions, credit card transactions.
3. University: Student course enrollment information, grades,
staff roles.
4. Airlines: Reservations and schedules.
5. Telecommunications: Prepaid and postpaid bill
maintenance.
Paradigm Shift from File System to DBMS
1. Redundancy of Data: Multiple copies of the same data,
leading to inefficiency.
2. Inconsistency of Data: Conflicting copies of data across
different sections.
3. Difficult Data Access: Tedious process of locating and
accessing specific data.
4. Unauthorized Access: Potential for unauthorized data
modification.
5. No Concurrent Access: Lack of support for multiple users
accessing data simultaneously.
6. No Backup and Recovery: No mechanisms for recovering
lost or corrupted data.
Advantages of DBMS
1) Data Organization: Structured organization and storage of
data for easy retrieval and querying.
2) Data Integrity: Enforcement of integrity constraints and
access controls.
3) Concurrent Access: Control over concurrent data access
for multiple users.
4) Data Security: Tools for managing data security, including
encryption and access control.
5) Backup and Recovery: Mechanisms for data backup and
recovery.
6) Data Sharing: Facilitation of data sharing among multiple
users.
Disadvantages of DBMS
1. Complexity: Requires specialized knowledge and skills for
setup and maintenance.
2. Performance Overhead: Can slow down application
performance, especially with high concurrency.
3. Scalability: May limit the scalability of applications due to
locking and synchronization mechanisms.
4. Cost: High cost of purchasing, maintaining, and upgrading
DBMS.
5. Limited Use Cases: Not suitable for all applications,
especially those not requiring high reliability, consistency,
or security.
Types of DBMS
1. Relational DBMS (RDBMS): Stores data in tables with rows
and columns; uses SQL for data manipulation.
2. Object-Oriented DBMS (OODBMS): Stores data as objects,
manipulated using object-oriented programming
languages.
3. NoSQL DBMS: Stores data in non-relational structures like
key-value pairs, document-based models, or graph models.