postUpdated Apr 25, 2026

DBMS – Database Management System Complete Notes for IBPS, SSC, RRB & Govt Exams

Database Management System (DBMS) is a high-yield chapter in IBPS, SSC CGL, RRB, and all government job exams. This post covers everything — what is a database, types of databases, DBMS vs RDBMS, ANSI/SPARC three-level architecture, database components, all types of keys (Primary, Foreign, Candidate, Composite), SQL commands (DDL, DML, DCL, TCL), SQL constraints, the Entity-Relationship (ER) model, normalisation, and key database terms — with memory tricks, one-liners, and 10 exam-focused FAQs.

DBMS – Database Management System Complete Notes for IBPS, SSC, RRB & Govt Exams

Jump to section

Introduction: Why DBMS is a Must-Prepare Topic for Banking Exams

Every bank is fundamentally a data management organisation. Customer accounts, transaction records, loan databases, employee records, branch data — all of this runs on Database Management Systems. Understanding DBMS is not just academic — it is the technical foundation of everything banks do digitally.

In government job exams, DBMS is tested with increasing frequency and depth:

  • "Which key uniquely identifies each record in a table?" → Primary Key
  • "SQL stands for ___?" → Structured Query Language
  • "DROP command belongs to which SQL category?" → DDL
  • "Which is NOT a constraint in SQL?" → Common trick question
  • "E.F. Codd is associated with ___?" → RDBMS (presented 12 rules in 1970)
  • "Which relationship is represented by a Diamond in ER diagrams?" → Relationship

DBMS also connects with Big Data, Data Mining, Cloud Computing (databases as a service), and Cyber Security (database security). For aspirants targeting IBPS SO (IT Officer) positions, this chapter requires even deeper preparation.

This post gives you the complete DBMS picture — from basic definitions to SQL commands to ER models — with everything organised for exam success.


What is a Database?

database is an organised, structured collection of related information (data) that is stored and managed so it can be easily accessed, managed, and updated.

Real-World Examples:

  • A bank's customer database (account holders, balances, transaction history)
  • A school's student database (student records, marks, attendance)
  • Amazon's product database (products, prices, inventory, reviews)
  • IRCTC's railway database (trains, schedules, seats, bookings)

Key Properties of a Database:

  • Organised — data is structured logically
  • Related — data items are connected and meaningful together
  • Persistent — data survives beyond the program that created it
  • Shared — multiple users can access simultaneously
  • Controlled — access is managed through permissions

Data vs Information - Recap

AspectDataInformation
DefinitionRaw, unprocessed factsProcessed, meaningful output
ExampleA student's test score: 85Class average score: 78.5
NatureInput to processingOutput of processing

Exam Key: A bank stores raw data (transactions, account numbers, balances); the DBMS processes it to produce meaningful information (monthly statements, interest calculations, audit reports).


What is DBMS (Database Management System)?

DBMS (Database Management System) is a collection of programs (software) that enables users to create, access, maintain, and manipulate a database. It serves as the interface between the database and its users or application programs.

Key Scientist: Dr. Edgar F. (E.F.) Codd — presented the 12 rules for RDBMS in 1970, laying the theoretical foundation for relational databases. He is called the Father of the Relational Database.

Purpose of DBMS:

  1. Bridge the gap between raw data and meaningful information
  2. Reduce data redundancy (same data stored in multiple places)
  3. Ensure data integrity (accuracy and consistency)
  4. Provide data security and access control
  5. Enable concurrent access by multiple users
  6. Provide backup and recovery mechanisms

Popular DBMS Software:

SoftwareTypeNotes
MySQLRDBMSOpen-source; most popular web database
OracleRDBMSEnterprise-grade; banking and large organisations
Microsoft SQL ServerRDBMSMicrosoft's enterprise database
PostgreSQLRDBMSAdvanced open-source; powerful features
MS AccessRDBMSDesktop RDBMS; part of Microsoft Office
SQLiteRDBMSLightweight; embedded in mobile apps
MongoDBNoSQLDocument-based NoSQL
dBASEDBMSOne of the earliest PC DBMS
FoxProDBMSEarly PC DBMS; now discontinued

DBMS vs RDBMS

FeatureDBMSRDBMS
StructureStores data as filesStores data in tables (rows and columns)
RelationshipsNo formal relationship between dataData is related through keys (Primary Key, Foreign Key)
NormalisationNot mandatoryFollows normalisation rules
Data redundancyHighLow (minimised through normalisation)
SecurityLowHigh — row-level and column-level security
Query languageLimitedFull SQL support
ACID complianceNoYes — Atomicity, Consistency, Isolation, Durability
ExamplesFile system, dBASE, FoxProOracle, MySQL, PostgreSQL, SQL Server, MS Access

Types of Databases

Hierarchical Database

FeatureDetails
StructureTree structure — data organised in parent-child relationships; each child has only one parent
NavigationFrom root to child nodes only (top-down)
AdvantageFast for hierarchical data; simple structure
DisadvantageInflexible — difficult to query non-hierarchical relationships
ExamplesIBM IMS (Information Management System); early banking databases

Network Database

FeatureDetails
StructureData organised as a graph — records connected by links; a child can have multiple parents
NavigationMore flexible than hierarchical
AdvantageHandles complex many-to-many relationships better than hierarchical
DisadvantageComplex structure; difficult to maintain
StandardCODASYL model

Relational Database

FeatureDetails
StructureData stored in tables (relations) — rows (records/tuples) and columns (fields/attributes)
RelationshipsTables related through keys (Primary Key, Foreign Key)
LanguageSQL (Structured Query Language)
AdvantageMost flexible; easy to query; widely supported; good data integrity
FoundationE.F. Codd's 12 rules (1970)
ExamplesOracle, MySQL, PostgreSQL, SQL Server, MS Access
Most widely usedDominant database model for 50+ years

Object-Oriented Database

FeatureDetails
StructureData stored as objects — similar to OOP concepts
AdvantageHandles complex data types (images, audio, video) that relational databases struggle with
Examplesdb4o, ObjectDB
Used inCAD/CAM, multimedia applications

NoSQL Database

FeatureDetails
DefinitionNon-relational databases designed for unstructured, semi-structured, or highly variable data at massive scale
No fixed schemaSchema-less — data structure can vary between records
ScalabilityDesigned for horizontal scaling — adding more servers
AdvantageHandles big data, real-time web apps, social media data
DisadvantageLess ACID compliance; complex queries harder

NoSQL Database Types

TypeDescriptionExamples
Document StoreStores data as JSON/XML documents; each document can have different structureMongoDB, CouchDB
Key-Value StoreSimplest NoSQL; stores data as key-value pairs; extremely fast lookupsRedis, DynamoDB, Memcached
Column-FamilyStores data in column families; optimised for read/write of large datasetsApache Cassandra, HBase
Graph DatabaseStores data as nodes and edges; optimised for relationship-heavy dataNeo4j, Amazon Neptune

DBMS Architecture - ANSI/SPARC Three-Level Model

The ANSI/SPARC (American National Standards Institute / Standards Planning and Requirements Committee) model defines three levels of abstraction in a DBMS — ensuring that changes at one level don't affect other levels. This is called Data Independence.

External Level (View Level)

FeatureDetails
Also calledView Level; User Level
PositionHighest level — closest to the user
DescriptionDefines how individual users or user groups see the data — each user sees only the data relevant to them
ExampleA bank teller sees only customer account data; a branch manager sees all accounts in the branch; an audit officer sees transaction logs
Key conceptMultiple different external views from the same underlying data

Conceptual Level (Logical Level)

FeatureDetails
Also calledLogical Level; Community Level
PositionMiddle level
DescriptionDefines what data is stored in the database and the relationships between data items — the overall logical structure without physical implementation details
ExampleDefines that there is a Customer table with CustomerID, Name, Account Number, Balance — but not HOW it's physically stored on disk
Key conceptThe complete logical map of the entire database visible to DBAs

Internal Level (Physical Level)

FeatureDetails
Also calledPhysical Level; Storage Level
PositionLowest level — closest to physical storage
DescriptionDefines how data is physically stored on disk — file organisation, indexing methods, storage structures, access paths
ExampleData is stored in B-tree indexed files; accounts table uses hash indexing on AccountID
Key conceptCompletely hidden from users; only DB administrators and the DBMS itself deal with this level

Three Levels Summary:

LevelAlso CalledWhat It DescribesWho Sees It
ExternalView LevelIndividual user viewsEnd users, application programs
ConceptualLogical LevelOverall logical structureDBAs, database designers
InternalPhysical LevelPhysical storage detailsDBMS engine, DBAs only

Data Independence:

  • Logical Data Independence — changing the conceptual level doesn't affect external views
  • Physical Data Independence — changing the internal level doesn't affect the conceptual level

Components of a Database

ComponentDescription
TableThe fundamental building block — stores data in rows and columns; also called a relation
Field (Column / Attribute)A single category of data in a table; represents one characteristic of an entity (e.g., Name, AccountNumber, Balance)
Record (Row / Tuple)One complete entry in a table; represents one entity (e.g., one customer's complete information)
QueryA request to retrieve, add, modify, or delete data from the database; written in SQL
FormA user-friendly graphical interface for data entry and viewing — makes it easy to interact with tables
ReportA formatted printable or viewable output of queried data — organised for presentation
ViewA virtual table created from a query — appears as a table but contains data from one or more real tables
IndexA data structure that speeds up data retrieval — like a book index; avoids scanning entire table
Stored ProcedurePre-written SQL code stored in the database that can be executed repeatedly
TriggerAutomatic action executed in response to a database event (INSERT, UPDATE, DELETE)

Key Measures:

  • Cardinality — Number of rows (tuples) in a table
  • Degree — Number of columns (attributes) in a table

Keys in a Database - Complete Guide

Keys are one or more attributes (columns) that uniquely identify rows in a table and establish relationships between tables. Understanding all key types is essential for banking exams.

Primary Key

FeatureDetails
DefinitionAn attribute (or set of attributes) that uniquely identifies each record in a table
RulesMust be UNIQUE (no two rows have the same value); cannot be NULL (must have a value); only ONE primary key per table
ExamplesCustomerID in a Customers table; AccountNumber in an Accounts table; EmployeeID in an Employees table
Exam KeyPrimary Key = Unique + Not Null + One per table

Candidate Key

FeatureDetails
DefinitionAny attribute (or minimal set of attributes) that could serve as a Primary Key — uniquely identifies records with no redundancy
Relationship to PKAll Primary Keys are Candidate Keys; but not all Candidate Keys become the Primary Key
ExampleIn a Students table: StudentID, Email, and AadharNumber are all candidate keys (each uniquely identifies a student)
Exam KeyCandidate Key = All possible Primary Keys; "candidates" for the primary key role

Alternate Key

FeatureDetails
DefinitionCandidate Keys that were NOT chosen as the Primary Key
ExampleIf StudentID is chosen as Primary Key, then Email and AadharNumber become Alternate Keys
Exam KeyAlternate Key = Candidate Key − Primary Key

Foreign Key

FeatureDetails
DefinitionAn attribute in one table that is the Primary Key in another table — it establishes a link between two tables
PurposeEnforces Referential Integrity — ensures values in the foreign key column actually exist in the referenced table
ExampleCustomerID in the Accounts table is a Foreign Key referencing CustomerID (Primary Key) in the Customers table
Exam KeyForeign Key = links tables; references Primary Key in another table; enables joins

Composite Key

FeatureDetails
DefinitionA Primary Key made of two or more columns combined — no single column can uniquely identify a row, but the combination can
ExampleIn a Course_Enrollment table: (StudentID + CourseID) together form the composite key — one student can enroll in many courses; one course has many students; but the combination is unique
Exam KeyComposite Key = multiple columns together form the unique identifier

Super Key

FeatureDetails
DefinitionAny set of attributes that can uniquely identify a record — may include unnecessary attributes
RelationshipEvery Primary Key is a Super Key; not all Super Keys are Candidate Keys (may have extra attributes)
Example{StudentID}, {StudentID, Name}, {StudentID, Email}, {Email}, {AadharNumber} are all Super Keys; but {StudentID, Name} has redundant "Name" — so it's a Super Key but not a Candidate Key
Exam KeySuper Key = any combination that uniquely identifies; Candidate Key = minimal Super Key

Keys Summary Table

Key TypeDescriptionNull Allowed?Multiple per Table?
Primary KeyUniquely identifies each record; chosen as the main identifierNoNo (only one)
Candidate KeyAny attribute that could be Primary KeyNoYes (multiple possible)
Alternate KeyCandidate keys not chosen as Primary KeyNoYes
Foreign KeyReferences Primary Key in another tableYes (usually)Yes
Composite KeyPrimary key made of two or more columnsNoNo
Super KeyAny set of attributes that uniquely identifies a rowNoYes

SQL (Structured Query Language)

SQL (Structured Query Language) is the standard language for creating, managing, and querying relational databases. It is an ANSI (American National Standards Institute) standard.

Key Fact: SQL was developed by IBM (Donald Chamberlin and Raymond Boyce) in the early 1970s.

SQL Constraints

Constraints are rules enforced on table columns to maintain data integrity:

ConstraintDescriptionExample
NOT NULLThe column cannot store a NULL (empty) valueAccountNumber NOT NULL
UNIQUEAll values in the column must be differentEmail UNIQUE
CHECKValues must satisfy a specific conditionAge CHECK (Age >= 18)
DEFAULTSpecifies a default value if none is providedStatus DEFAULT 'Active'
PRIMARY KEYUniquely identifies each record; combines NOT NULL + UNIQUECustomerID PRIMARY KEY
FOREIGN KEYLinks to Primary Key in another table; enforces referential integrityCustomerID FOREIGN KEY

Database Languages - DDL, DML, DCL, TCL

SQL is divided into four sub-languages based on function:

LanguageFull FormPurposeCommands
DDLData Definition LanguageDefines and modifies the structure (schema) of the databaseCREATE, ALTER, DROP, TRUNCATE, RENAME
DMLData Manipulation LanguageAccesses and manipulates the actual data inside tablesSELECT, INSERT, UPDATE, DELETE
DCLData Control LanguageControls access to data — grants and revokes permissionsGRANT, REVOKE
TCLTransaction Control LanguageManages transactions — controls commit and rollbackCOMMIT, ROLLBACK, SAVEPOINT

Important SQL Commands

DDL Commands:

CommandSyntax ExamplePurpose
CREATECREATE TABLE Customers (ID INT, Name VARCHAR(100));Creates a new table, database, or other object
ALTERALTER TABLE Customers ADD COLUMN Email VARCHAR(200);Modifies an existing table structure (add/modify/drop column)
DROPDROP TABLE Customers;Permanently deletes a table and all its data
TRUNCATETRUNCATE TABLE Customers;Deletes all rows from a table but keeps the table structure

DML Commands:

CommandSyntax ExamplePurpose
SELECTSELECT Name, Balance FROM Accounts WHERE Balance > 10000;Retrieves data from one or more tables
INSERTINSERT INTO Customers VALUES (1, 'Rahul', 'rahul@email.com');Adds new records to a table
UPDATEUPDATE Accounts SET Balance = 15000 WHERE AccountID = 101;Modifies existing records
DELETEDELETE FROM Customers WHERE CustomerID = 5;Removes specific records from a table

DCL Commands:

CommandPurpose
GRANTGives a user permission to perform specific actions (SELECT, INSERT, etc.)
REVOKERemoves previously granted permissions from a user

TCL Commands:

CommandPurpose
COMMITPermanently saves all changes made in the current transaction
ROLLBACKUndoes all changes made in the current transaction (back to last COMMIT)
SAVEPOINTCreates a checkpoint within a transaction to which you can rollback

Entity-Relationship (ER) Model

The ER (Entity-Relationship) Model is a conceptual data model used to design a database before implementing it. It provides a visual representation of data and relationships using a diagram called an ER Diagram.

ER Model Elements

ElementSymbolDescription
EntityRectangleA real-world object or thing about which data is stored — e.g., Customer, Account, Employee
AttributeEllipse (Oval)A property or characteristic of an entity — e.g., Name, Age, AccountNumber
RelationshipDiamondAn association between two entities — e.g., "Customer HOLDS Account"
Primary Key AttributeEllipse with underlined textThe attribute that uniquely identifies an entity
Multivalued AttributeDouble EllipseAn attribute that can have multiple values — e.g., Phone Numbers
Derived AttributeDashed EllipseAn attribute derived from another attribute — e.g., Age derived from Date of Birth

Entity Types:

  • Strong Entity — Has its own Primary Key; can exist independently — e.g., Customer
  • Weak Entity — Cannot be uniquely identified without a related Strong Entity — e.g., Dependent (family member) of an Employee

Relationship Types

TypeDescriptionExample
One-to-One (1:1)Each record in Table A relates to exactly one record in Table B and vice versaOne Person has one Passport
One-to-Many (1:N)One record in Table A relates to multiple records in Table BOne Customer has many Accounts
Many-to-Many (M:N)Multiple records in Table A relate to multiple records in Table BMany Students enroll in many Courses

Normalisation

Normalisation is the process of organising a database to reduce data redundancy and improve data integrity — by dividing large tables into smaller, related tables and defining relationships between them.

Goals of Normalisation:

  1. Eliminate data redundancy (same data stored in multiple places)
  2. Ensure data integrity and consistency
  3. Remove data anomalies (insertion, update, deletion anomalies)
  4. Make the database more efficient

Normal Forms:

Normal FormRequirement
1NF (First Normal Form)All columns contain atomic (indivisible) values; no repeating groups; each column has a unique name
2NF (Second Normal Form)Must be in 1NF + all non-key attributes must be fully dependent on the ENTIRE primary key (no partial dependencies)
3NF (Third Normal Form)Must be in 2NF + no transitive dependencies (non-key attributes should not depend on other non-key attributes)
BCNF (Boyce-Codd Normal Form)Stricter version of 3NF; every determinant must be a candidate key

Key Database Terms

TermMeaning
SchemaThe logical structure/blueprint of the database — table names, column names, data types, and relationships
InstanceThe actual data stored in the database at a specific point in time — the snapshot
Data RedundancyThe same data stored in multiple places — wastes storage; causes inconsistency
Data IntegrityThe accuracy, consistency, and reliability of data throughout its lifecycle
Data IndependenceThe ability to change one level of the DBMS architecture without affecting other levels
Validation RuleA condition that data must satisfy before being accepted into the database
TransactionA single logical unit of work — may involve multiple SQL operations that must all succeed or all fail together
ACID PropertiesAtomicity, Consistency, Isolation, Durability — guarantees of reliable transaction processing
Metadata"Data about data" — describes the structure of the database (column names, data types, sizes)
Data WarehousingCentralised repository that consolidates data from multiple sources for analysis and reporting
OLTPOnline Transaction Processing — systems for day-to-day transactions (banking, retail)
OLAPOnline Analytical Processing — systems for complex queries and analysis
Stored ProcedurePre-compiled SQL code stored in the database; executed on demand; improves performance
CursorA database object used to process individual rows returned by a SQL query one at a time

ACID Properties Explained:

PropertyMeaning
AtomicityA transaction is all-or-nothing — either all operations complete successfully or none do (no partial transactions)
ConsistencyA transaction brings the database from one valid state to another valid state — integrity constraints are always maintained
IsolationConcurrent transactions execute as if they were serial — each transaction is isolated from others in progress
DurabilityOnce a transaction is committed, it is permanently saved — even system failure cannot undo it

Memory Tricks

🔑 Keys Summary - "PCAFC S":

Primary Key (unique, not null, one per table) Candidate Key (all possible PKs) Alternate Key (unused candidate keys) Foreign Key (links tables) Composite Key (multiple columns = one key) Super Key (any unique combination) Mnemonic: "Please Choose A Foreign Country Slowly"

🔑 SQL Sub-languages - "DDL DML DCL TCL":

Definition (CREATE ALTER DROP) = Defines STRUCTURE Manipulation (SELECT INSERT UPDATE DELETE) = Touches DATA Control (GRANT REVOKE) = Controls ACCESS Transaction (COMMIT ROLLBACK) = Manages TRANSACTIONS Mnemonic: "Designers Make Creative Things" = DDL, DML, DCL, TCL

🔑 ACID Properties:

Atomicity = All or Nothing Consistency = Always Valid Isolation = Independent of others Durability = Permanent after commit Mnemonic: "A Computer Is Dependable"

🔑 ER Diagram Shapes:

Entity = Rectangle (both start with straight lines) Attribute = Ellipse (oval, round like 'A') Relationship = Diamond (RE-lationship = DIamond = R+D... different) Trick: "Rectangles hold Entities, Ovals hold Attributes, Diamonds hold Relations"

🔑 DBMS Three Levels:

External = Users Experience (views) Conceptual = Complete Chart (logical) Internal = Inside storage (physical) Order: External (top) → Conceptual (middle) → Internal (bottom)

🔑 TRUNCATE vs DROP vs DELETE:

DELETE = Removes specific rows; can rollback; DML TRUNCATE = Removes ALL rows; keeps table; faster; DDL DROP = Removes ENTIRE table; DDL


One-Liner Recap (Quick Revision)

  1. A database is an organised collection of related data that can be easily accessed, managed, and updated — banks use databases to store all customer, account, and transaction data.
  2. DBMS (Database Management System) is software that provides an interface between users and the database, enabling creation, access, manipulation, and management of data.
  3. Dr. E.F. Codd presented 12 rules for RDBMS in 1970 and is called the Father of the Relational Database — his work forms the theoretical foundation of all modern relational databases.
  4. RDBMS stores data in tables (rows and columns) with relationships enforced through keys (Primary Key, Foreign Key), supports full SQL, and ensures ACID compliance.
  5. The ANSI/SPARC three-level architecture has External Level (user views), Conceptual Level (logical structure), and Internal Level (physical storage) — ensuring data independence.
  6. A Primary Key uniquely identifies each record in a table — it must be unique, cannot be NULL, and only one Primary Key is allowed per table.
  7. A Foreign Key in one table references the Primary Key in another table, establishing a link between tables and enforcing referential integrity.
  8. A Candidate Key is any attribute that could serve as a Primary Key; an Alternate Key is a Candidate Key that was not chosen as the Primary Key.
  9. A Composite Key is a Primary Key made of two or more columns combined — used when no single column can uniquely identify a record.
  10. SQL (Structured Query Language) is the ANSI standard language for relational databases, divided into DDL (structure), DML (data), DCL (access control), and TCL (transactions).
  11. DDL commands include CREATE, ALTER, DROP, and TRUNCATE — they define or modify the structure of the database objects.
  12. DML commands include SELECT, INSERT, UPDATE, and DELETE — they access and manipulate the actual data stored in tables.
  13. In the ER Model, Entities are represented by Rectangles, Attributes by Ellipses, and Relationships by Diamonds — strong entities have their own primary key while weak entities depend on another entity.
  14. Normalisation organises a database to reduce redundancy and improve integrity — 1NF removes repeating groups, 2NF removes partial dependencies, and 3NF removes transitive dependencies.
  15. ACID properties (Atomicity, Consistency, Isolation, Durability) are the four guarantees that RDBMS provides for reliable transaction processing — critical for banking systems.

Preparing for competitive exams requires consistent revision. Platforms like JobsMe simplify preparation through:

Stay updated, revise regularly, and attempt quizzes for better accuracy in UPSC, SSC CGL, IBPS PO/Clerk, SBI, RBI Grade B, RRB NTPC, Defence, and State PSC exams.

Free quiz • No signup required

Put this topic into practice with Current Affairs MCQ Quiz 25 April 2026 | SSC Banking UPSC Railways Defence. It is the quickest way to reinforce what you just learned.

vetri

About the author

vetri