Database Management System (DBMS)

Abstract

A DBMS is software that lets you define, store, retrieve, and manipulate data safely and efficiently while enforcing constraints, access control, and durability.

DBMS vs. Database

• Database - the organized collection of data.
• DBMS - the software layer that provides APIs, query engines, storage management, and security on top of the raw data.

DBMS Models

Model	Structure	Highlights	Examples
Hierarchical	Tree (parent -> children)	Fast traversals for fixed hierarchies	IMS
Network	Graph (nodes/edges)	Supports many-to-many relationships	IDMS
Relational (RDBMS)	Tables (rows/columns)	SQL, strong integrity constraints	PostgreSQL, MySQL
Object-Oriented	Objects with methods	Blends OOP with persistence	ObjectDB
NoSQL (document, key-value, column, graph)	Flexible schemas	Scale-out, event-driven apps	MongoDB, Cassandra

ER Modeling

The Entity-Relationship (ER) model captures data requirements at a conceptual level.

Core Components

Note

Entities map to tables, attributes map to columns, and relationships map to foreign keys or associative tables.

Attribute Types

• Simple: indivisible values
  
• Primary key: uniquely identifies each entity instance
  
• Derived: computed from other attributes
  
• Multivalued: multiple values per entity (requires junction table in RDBMS)
  
• Composite: attributes with subparts (e.g., address)
  

Relationship Cardinalities

1. One-to-One: rare; use when splitting sensitive or optional data.
2. One-to-Many: most common; foreign key lives on the “many” side.
3. Many-to-Many: resolve via a junction table with two foreign keys.

Data Integrity in RDBMS

Integrity Type	Enforced By	Purpose
Entity	Primary key (PRIMARY KEY)	Ensures every row is identifiable and non-null
Referential	Foreign key (FOREIGN KEY)	Keeps relationships valid between tables
Domain	Data types, CHECK, NOT NULL, defaults	Restricts column values to valid ranges/formats
User-defined	Triggers, stored procedures, application logic	Encodes business-specific rules

Transaction Lifecycle

A transaction is a logical unit of work that respects ACID properties.

State	Description
Active	Executing operations (reads/writes)
Partially committed	In-memory changes done; waiting to flush/commit
Committed	Changes durably persisted; cannot roll back
Failed	Error detected; rollback initiated
Terminated	Transaction ends after commit or abort

Tip

Pair this note with ACID for isolation levels and serialization strategies, and Distributed transaction handling for cross-service coordination.