Database management system
What is a DBMS?
A Database Management System (DBMS) is software that allows users to create,
store, manage, and retrieve data from a database efficiently and securely.
A database is an organized collection of data that can be easily accessed, managed,
and updated.
DBMS removes the data redundancy that means there is no duplication of data in
database.
DBMS allows to retrieve the desired data in required format.
Data can be isolated in separate tables for convenient and efficient use.
Data can be accessed efficiently using a simple query language.
The data integrity can be maintained. That means – the constraints can be applied
on data and it should be in some specific range.
The atomicity of data can be maintained. That means, if some operation is
performed on one particular table of the database, then the change must be reflected
for the entire database.
The DBMS allows concurrent access to multiple users by using the synchronization
technique.
The security policies can be applied to DBMS to allow the user to access only
desired part of the database system
Example:
Your university stores student records (name, ID, courses, marks) in a database.
Why Do We Need DBMS?
Before DBMS, data was stored in file-based systems, which had many problems:
1. Data redundancy (duplicate data)
2. Inconsistency
3. Difficult data access
4. No security
5. Concurrent access issues
DBMS solves these problems by:
1. Centralizing data
2. Reducing redundancy
3. Providing security and access control
4. Supporting multi-user access
5. Ensuring data integrity
Types of DBMS
1. Hierarchical DBMS – Tree-like structure (e.g., IBM IMS)
2. Network DBMS – More flexible, graph-like (e.g., IDMS)
3. Relational DBMS (RDBMS) – Uses tables (e.g., MySQL, Oracle)
4. Object-Oriented DBMS – Stores data as objects (e.g., db4o)
Popular DBMS Software
1. MySQL
2. Oracle Database
3. PostgreSQL
4. Microsoft SQL Server
5. MongoDB (NoSQL)
Real-World Applications
1. Banking systems
2. E-commerce platforms
3. Hospital management systems
4. Student information systems
5. Social media platforms
Purpose of Database System
The main purpose of a Database System is to help manage data efficiently, securely,
and reliably. Instead of relying on manual file storage or spreadsheets, a Database
Management System (DBMS) automates and optimizes how data is stored, accessed,
and maintained.
Key Purposes of a Database System:
1. Data Storage and Retrieval
I. Provides a systematic way to store large volumes of data.
II. Allows quick retrieval using query languages like SQL.
III. Example: Get all students who scored above 90%
2. Data Integrity and Accuracy
I. Ensures that data is valid, consistent, and accurate.
II. Uses constraints (like primary keys, foreign keys) to enforce rules.
3. Data Security
I. Controls who can view or modify data.
II. Supports authentication and authorization mechanisms.
4. Minimizing Data Redundancy
I. Avoids storing the same data in multiple places.
II. Reduces storage cost and ensures consistency.
5. Data Consistency
I. Ensures that changes made by one user do not conflict with changes
made by another.
II. Maintains consistent data across multiple tables and users.
6. Concurrent Access
I. Allows multiple users to access and update data at the same time
without conflicts.
II. Manages transactions to avoid data corruption.
7. Backup and Recovery
I. Protects data from system failures or crashes.
II. Supports automatic backups and data recovery mechanisms.
8. Data Abstraction
I. Hides the complexity of data storage from users.
II. Users interact with a logical view, not the actual physical data.
9. Efficient Query Processing
I. Optimizes queries for fast results.
II. Uses indexing and query optimization techniques.
10. Support for Data Relationships
I. Easily manages relationships between entities (e.g., students and
courses).
II. Uses foreign keys to connect tables logically.
Advantages of DBMS :
1) DBMS removes the data redundancy that means there is no duplication of data in
database.
2) DBMS allows to retrieve the desired data in required format.
3) Data can be isolated in separate tables for convenient and efficient use.
4) Data can be accessed efficiently using a simple query language.
5) The data integrity can be maintained. That means – the constraints can be applied
on data and it should be in some specific range.
6) The atomicity of data can be maintained. That means, if some operation is
Performed on one particular table of the database, then the change must be
reflected for the entire database.
7) The DBMS allows concurrent access to multiple users by using the
synchronization technique.
8) The security policies can be applied to DBMS to allow the user to access only
desired part of the database system.
Views in DBMS
A view in a Database Management System (DBMS) is a virtual table that is based on
the result of an SQL query. It doesn't store data physically but provides a way to
present data in a customized format by querying underlying base tables.
Syntax to Create
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Database is a collection of interrelated data and set of programs that allow users to
access or modify the data.
Abstract view of the system is a view in which the system hides certain details of
how the data are stored and maintained.
The main purpose of database systems is to provide users with abstract view of the
data.
The view of the system help the user to retrieve data efficiently.
For simplifying the user interaction with the system there are several levels of
abstraction - these levels are - Physical level, logical level and view level.
Example
🎯 Original Table: Employees
ID Name Salary Department
1 Anil 60000 HR
2 Vinod 75000 IT
3 Chetan 50000 Sales
👁 Create a View (hide salary)
sql
CREATE VIEW EmployeeNames ASSELECT ID, Name, DepartmentFROM
Employees;
Now, querying EmployeeNames:
sql
SELECT * FROM EmployeeNames;
Results in:
ID Name Department
1 Anil HR
2 Vinod IT
3 Chetan Sales
Data Abstraction
SPPU : Dec.-17, May-19, Marks 5
Definition: Data abstraction means retrieving only the required amount of
information about the system and hiding background details.
There are several levels of abstraction that simplify user interactions with the system.
These are :
1) Physical level :
o This is the lowest level.
o This level describes how the data are stored.
o The database administrators decide how to store data at the physical level.
o This level describes complex low-level data structures.
2) Logical level :
o This is the next higher level, which describes what data are stored in the
database?.
o This level also describes the relationship between the data.
o The logical level thus describes the entire database in terms of a small number of
relatively simple structures.
o The database administrators use a logical level of abstraction for deciding what
information to keep in the database.
3) View level :
o This is the highest level of abstraction that describes only part of the entire
database.
o The view level can provide access to only part of the database.
o This level helps in simplifying the interaction with the system.
o It can provide multiple views of the same system.
o For example - A Clerk at the reservation system can see only part of the
database and access the passenger's required information.
Database-System Applications
There are wide range of applications that make use of database systems. Some of the
applications are
1) Accounting : Database systems are used in maintaining information employees,
salaries, and payroll taxes.
2) Manufacturing : For management of supply chain and tracking production of items
in factories database systems are maintained.
3) For maintaining customer, product and purchase information the databases are
used.
4) Banking : In banking sector, for customer information, accounts and loan and for
performing banking applications the DBMS is used.
5) For purchase on credit cards and generation of monthly statements database
systems are useful.
6) Universities : The database systems are used in universities for maintaining
student information, course registration, and accounting.
7) Reservation systems : In airline / railway reservation systems, the database is used
to maintain the reservation and schedule information.
8) Telecommunication : In telecommunications for keeping records of the calls made,
generating monthly bills, maintaining balances on prepaid calling cards, and storing
information about communication networks the database systems are used.
Database Languages
There are three types of languages supported by database systems.
(1) DDL
Data Definition Language (DDL) is a specialized language used to specify a
database schema by a set of definitions.
It is a language used for creating and modifying the structures of tables, views,
indexes, etc.
DDL is also used to specify additional properties of data.
Some of the common commands used in DDL are -CREATE, ALTER, DROP.
The primary use of CREATE command is to build a new table. Using ALTER
command, the users can add up some additional column and drop existing columns.
Using DROP command, the user can delete table or view.
(2) DML
DML stands for Data Manipulation Language.
This language enables users to access or manipulate data as organized by
appropriate data model.
The types of access are –
Retrieval of information stored in the database
Insertion of new information into the database.
Deletion of information from the database.
Modification of information stored in database.
There are two types of DML –
Procedural DML - Require a user to specify what data are needed and how to get
those data.
Declarative DML - Require a user to specify what data are needed without specifying
how to get those data.
Query is a statement used for requesting the retrieval of information. This retrieval
of information using some specific language is called query language.
(3) DCL
The Data Control Language (DCL) is used to control access to data stored in the
database. This is also called as authorization.
The typical command used in DCL are GRANT and REVOKE.
GRANT : This command is used to give access rights or privileges to the
database. REVOKE : The revoke command removes user access rights or
privileges to the database objects
