Home › CSE Hub › Database Management Systems (DBMS)

Database Management Systems (DBMS)

Databases, the relational model, keys, ER diagrams, SQL, normalization, transactions and ACID properties, indexing, and an introduction to NoSQL.

हिंदी में: डेटा को सुरक्षित, व्यवस्थित और तेज़ी से खोजने लायक़ रखने का विज्ञान — SQL, नॉर्मलाइज़ेशन और ट्रांज़ैक्शन।

What is a DBMS?

A database is an organised collection of related data. A Database Management System (DBMS) is software that stores, retrieves and manages that data safely for many users at once. Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite, MongoDB.

Why not just files? A DBMS reduces redundancy and inconsistency, enforces integrity rules, supports concurrent users, controls access and recovers from crashes.

Three-level architecture

  • External (view) level – what individual users see.
  • Conceptual (logical) level – the whole database structure: tables, relationships, constraints.
  • Internal (physical) level – how data is actually stored on disk.

This separation gives data independence: the storage can change without changing applications.

The relational model

Data is stored in relations (tables) made of tuples (rows) and attributes (columns).

Key Meaning
Super key Any set of columns that uniquely identifies a row
Candidate key A minimal super key
Primary key The candidate key chosen to identify rows; cannot be NULL
Alternate key Candidate keys not chosen as primary
Foreign key A column that refers to the primary key of another table

ER model

An Entity–Relationship diagram designs a database before creating tables: entities (Student, Course), attributes (name, roll_no) and relationships (Student enrols in Course) with cardinality 1:1, 1:N or M:N.

SQL essentials

CREATE TABLE student (
  roll_no  INT PRIMARY KEY,
  name     VARCHAR(60) NOT NULL,
  branch   VARCHAR(10),
  cgpa     DECIMAL(3,2)
);

INSERT INTO student VALUES (101, 'Ravi', 'CSE', 8.40);

SELECT branch, COUNT(*) AS students, AVG(cgpa) AS avg_cgpa
FROM student
WHERE cgpa >= 6
GROUP BY branch
HAVING COUNT(*) > 5
ORDER BY avg_cgpa DESC;
  • DDL: CREATE, ALTER, DROP, TRUNCATE.
  • DML: SELECT, INSERT, UPDATE, DELETE.
  • DCL: GRANT, REVOKE. TCL: COMMIT, ROLLBACK, SAVEPOINT.
  • Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF.
  • WHERE filters rows before grouping; HAVING filters groups after GROUP BY.

Normalization

Normalization removes redundancy and update anomalies by splitting tables based on functional dependencies.

Normal form Rule
1NF Every column holds atomic (single) values
2NF 1NF + no partial dependency on part of a composite key
3NF 2NF + no transitive dependency (non-key → non-key)
BCNF For every dependency X → Y, X is a super key

Transactions and ACID

A transaction is a unit of work that must happen completely or not at all (e.g. transferring money).

  • Atomicity – all or nothing.
  • Consistency – the database moves from one valid state to another.
  • Isolation – concurrent transactions do not interfere.
  • Durability – committed changes survive crashes.

Concurrency is controlled with locks (shared/exclusive, two-phase locking) or timestamps. Problems it prevents include lost updates, dirty reads and non-repeatable reads.

Indexing

An index (usually a B+ tree) speeds up searches the way a book index does, at the cost of extra storage and slower writes. Index columns that are searched or joined often.

NoSQL in brief

NoSQL databases relax the fixed-table model for scale and flexibility: – Document (MongoDB), Key–value (Redis), Column-family (Cassandra), Graph (Neo4j). The CAP theorem says a distributed system cannot guarantee Consistency, Availability and Partition tolerance all at once during a network partition.

Quick revision

  • Primary key: unique + not NULL. Foreign key links tables.
  • WHERE before grouping, HAVING after.
  • ACID = Atomicity, Consistency, Isolation, Durability.
  • BCNF is stricter than 3NF.