DATABASE MANAGEMENT
SYSTEMS (DBMS)
TECHNICAL REFERENCE MANUAL
A Comprehensive Study on Architecture, Relational Models, Transactions,
and Indexing
Subject: Core Computer Science & Information Engineering
Version: 1.0
Status: Reference Compendium
1. Foundations of Database Systems
A Database Management System (DBMS) is a software package designed to define,
manipulate, retrieve, and manage data within a centralized or distributed digital
repository. It serves as an interface between end-user applications and the underlying
physical storage, replacing traditional file-processing architectures which suffered from
data redundancy, inconsistency, and lack of structural isolation.
1.1 The Three-Schema Architecture
To achieve absolute data independence, a modern DBMS partitions its conceptual view
into three distinct abstraction layers (defined by the ANSI-SPARC framework):
External Level (View Schema): The top layer, describing how individual end-users
or specific application interfaces perceive and interact with the data. It isolates users
from knowing about other parts of the database.
Conceptual Level (Logical Schema): The middle layer, describing what data is
stored in the database and outlining the relationships, constraints, and security
structures across the whole database. It maps entities and attributes.
Internal Level (Physical Schema): The lowest abstraction layer, detailing how
data is physically stored on hardware blocks, managing storage paths, indexing
layouts, block sizes, and record compressions.
2. Relational Database Architecture &
Integrity Constraints
The Relational Model, formalized by E.F. Codd, represents data as interconnected tables
consisting of rows (tuples) and columns (attributes).
2.1 Database Key Taxonomy
Super Key: Any set of one or more attributes that collectively identify unique tuples
within a relation.
Candidate Key: A minimal Super Key containing no redundant attributes. It acts as
a baseline qualifier for primary key selection.
Primary Key: The specific Candidate Key explicitly chosen by the database
designer to uniquely identify records across a relation. It cannot contain null values.
Foreign Key: An attribute block in a child table that references the Primary Key of a
parent table, establishing a secure relational link.
2.2 Structural Integrity Rules
Relational safety is maintained by enforcing two mandatory integrity rules:
Entity Integrity: No attribute participating in a primary key definition can accept a
NULL or empty assignment.
Referential Integrity: If a foreign key exists in a relation, its value must match an
existing primary key value in the referenced parent relation, or be set entirely to
NULL.
3. Transaction Management & ACID
Properties
A transaction is a logical unit of database processing consisting of a sequence of read or
write database operations executed as a single block.
3.1 The Four ACID Constraints
To maintain data accuracy during system crashes or concurrent user access,
transactions must guarantee the four ACID properties:
Atomicity: The 'All or Nothing' property. If any individual statement within a
transaction fails, the entire transaction is aborted, and the database is rolled back to
its pre-transaction state.
Consistency: A transaction must move the database from one valid state satisfying
all structural constraints and business rules to another valid state.
Isolation: The execution states of concurrent transactions must be completely
isolated from one another. Uncommitted changes from one transaction cannot be
seen by others (preventing issues like Dirty Reads).
Durability: Once a transaction successfully commits, its changes are permanently
written to non-volatile storage and will survive subsequent system crashes.
4. Database Architecture Reference Matrix
The following index matrix provides a comparative breakdown between Relational (SQL)
and Non-Relational (NoSQL) Database Systems:
Architectural Feature Relational Databases Non-Relational
(RDBMS / SQL) Databases (NoSQL)
Data Storage Model Structured rows and tables Unstructured documents, key-
with predefined schemas value stores, column-families,
graphs
Scaling Strategy Vertical Scaling (Upgrading Horizontal Scaling
physical machine hardware (Distributing workloads across
RAM/CPU) thousands of commodity
shards)
Transaction Safety Strict ACID compliance BASE properties (Basically
Available, Soft-state, Eventual
consistency)
Common Implementations PostgreSQL, MySQL, Oracle MongoDB, Cassandra, Redis,
DB, MS SQL Server Neo4j