Database Management Systems

The scope progresses from conceptual modelling to the relational model and SQL, then to integrity, transaction management, and access control. This order keeps the path from the problem domain to the logical model and finally to observable database behaviour explicit. Key and normalization terminology follows the formal definitions; for BCNF, the determinant of every non-trivial functional dependency must be a superkey, not necessarily a candidate key.

Unit 1: Fundamental Concepts

Classical file organization

Before a DBMS manages data, an application can store records directly in files. The file approach is simple for isolated programs but becomes difficult when several applications need the same information, concurrent updates, shared constraints and independent query patterns.

Common classical organizations include:

• sequential files,
• indexed files,
• hash/direct-access files.

A record groups fields describing one logical item; a field contains one attribute value. These terms remain useful even though a relational DBMS exposes rows and columns at a higher abstraction level.

Sequential files

Sequential organization places records in a defined order or in append order. Sequential scans are efficient, but locating one arbitrary record may require scanning unless an auxiliary index is available.

Indexed files

An index stores search keys and references to data locations. It accelerates selected access paths at the cost of storage and maintenance on insert/update/delete operations.

Hash/direct-access files

Hash organization computes a bucket/location from a key. Average equality lookup can be efficient when the hash distribution is good, but collisions must be handled and ordered/range scans are not its strength.

Database systems

A database system separates logical data management from application-specific file code. A DBMS typically provides:

• data definition,
• querying and modification,
• constraints,
• transactions,
• concurrency control,
• recovery,
• authorization,
• metadata/catalog management.

The main advantage is more than that data are stored in tables. It is that common integrity and access semantics can be enforced independently of one application program.

Data models

A data model defines the concepts used to represent data and relationships. Relational, hierarchical, network and object-oriented models are historical examples. Conceptual models such as ER are used during analysis, while a relational schema describes the logical implementation in tables.

Unit 2: Entity-Relationship Model

ER model

The Entity-Relationship model describes a domain through entities, attributes and relationships before implementation details are fixed.

Entity and entity set

An entity is a distinguishable domain object, such as one employee or one course. An entity set groups entities of the same conceptual type.

Relationships

A relationship associates entities. A relationship set describes relationships of the same kind. Relationships can also have attributes when information belongs to the association itself rather than to either participating entity.

Attributes and domains

An attribute represents a property. Its domain specifies the set/type of admissible values.

Attributes may be:

• simple,
• composite,
• single-valued,
• multivalued,
• stored,
• derived.

A derived attribute can be calculated from other data; storing both the source and the derived value creates a consistency obligation.

Roles

When the same entity type participates more than once in a relationship, role names distinguish participation. A self-referential employee-manager relationship is a common example.

Cardinality

Typical mappings are:

1 : 1
1 : N
N : 1
M : N

Cardinality expresses how many entities on one side may relate to entities on the other side. Optional/mandatory participation adds a separate existence constraint.

Keys

A superkey is any attribute set that uniquely identifies an entity/tuple.

A candidate key is a minimal superkey: no proper subset still uniquely identifies the entity.

A chosen candidate key becomes the primary key in the relational schema. Other candidate keys can be enforced as alternate unique keys.

Weak and strong entities

A strong entity can be identified by its own key. A weak entity depends on an owner entity and is typically identified by an owner key plus a partial key/discriminator.

Mapping ER to tables

Common mapping rules are:

• each regular entity set becomes a table,
• simple attributes become columns,
• composite attributes are decomposed as needed,
• multivalued attributes often become a separate table,
• 1:N relationships normally place a foreign key on the N side,
• M:N relationships become an associative table containing foreign keys to both sides,
• weak entities include the owner's key as part of their identifying key.

These are design rules, not substitutes for checking actual business constraints.

Unit 3: The Relational Data Model

Relation

The relational model represents data as relations. In implementation terminology, a relation is commonly presented as a table, a tuple as a row, and an attribute as a column, although the mathematical model has stricter semantics than every SQL table implementation.

Schema and instance

A database schema defines relations, attributes, domains and constraints. A database instance/state is the actual set of stored values at a particular time.

Table properties

In the mathematical relational model:

• tuples are unordered,
• attributes are identified by name,
• duplicate tuples are not part of a relation,
• each attribute draws values from a domain.

SQL tables are bags by default for query results and can contain duplicates unless constraints/query operators prevent them, so SQL and pure relational algebra should not be treated as identical.

Integrity

Integrity constraints keep database states within the rules of the model/domain.

Important types include:

• domain constraints,
• key/uniqueness constraints,
• entity integrity,
• referential integrity,
• application/business constraints.

Primary and foreign keys

A primary key identifies a row and cannot contain null values under normal SQL semantics for a primary-key constraint.

A foreign key requires referencing values to match a candidate/unique key in the referenced table, except for nullable cases permitted by the constraint definition. Referential actions determine what happens on parent changes/deletion where the DBMS supports them.

Business rules and dependencies

Not every rule is naturally a simple column constraint. Dependencies among attributes drive normalization, while cross-row/cross-table domain rules may require additional declarative constraints, application logic or carefully designed procedural enforcement.

Unit 4: Relational Database Design

Normalization

Normalization decomposes relations based on dependencies to reduce redundancy and update anomalies while preserving information and, where possible, dependencies.

The goal is not "more tables at any cost" but a schema in which facts have clear ownership and integrity is maintainable.

Functional dependency

For attributes sets X and Y:

X -> Y

means that tuples agreeing on X must agree on Y in every valid relation state satisfying the dependency.

A dependency is full when no proper subset of the determinant is sufficient. It is partial when part of a composite determinant already determines the dependent attributes.

First normal form

1NF requires attribute values to be atomic with respect to the chosen relational design and removes repeating groups from a single tuple structure. "Atomic" is relative to the data model: a value should be treated as one domain value rather than encoded as an ad-hoc repeating list that the DBMS must parse to recover relations.

Second normal form

2NF requires 1NF and removes partial dependency of non-prime attributes on a proper subset of a candidate key. It is relevant when composite keys exist.

Third normal form

A common formulation of 3NF says that for every functional dependency X -> A, at least one of the following holds:

• the dependency is trivial,
• X is a superkey,
• A is a prime attribute (part of some candidate key).

The practical effect is to remove many transitive dependency anomalies among non-key facts.

Boyce-Codd normal form

BCNF is stronger:

For every non-trivial functional dependency X -> Y, X must be a superkey.

The determinant does not need to be a minimal candidate key. Every candidate key is a superkey, but not every superkey is a candidate key.

BCNF resolves some anomalies that can remain in 3NF, particularly in schemas with overlapping candidate keys. A lossless decomposition may sometimes sacrifice dependency preservation, so design tradeoffs should be explicit.

Fourth and fifth normal forms

4NF addresses non-trivial multivalued dependencies where the determinant is not a superkey.

5NF addresses join dependencies that cannot be explained by candidate keys alone. These forms are important theoretically but appear less frequently in ordinary application schemas than 1NF through BCNF.

Normalization and performance

Denormalization can be justified for measured performance or reporting requirements, but it introduces duplicated facts and therefore synchronization/integrity responsibilities. It should be a controlled optimization, not a substitute for understanding dependencies.

Normalization anomalies and dependency structure

Normalization is driven by dependencies, not by a target table count. Partial dependency can create 2NF violations, transitive dependency can create 3NF violations, and a determinant that is not a candidate key can violate BCNF.

The practical symptoms are insertion, update, and deletion anomalies: the same fact must be repeated, cannot be recorded independently, or disappears when an unrelated row is removed.

Higher normal forms are useful when their dependency assumptions match the data. They should not be applied mechanically when the workload, constraints, or legacy schema make another representation intentional.

NULL and three-valued logic

SQL predicates evaluate to TRUE, FALSE, or UNKNOWN. Comparisons with NULL therefore do not behave like comparisons with an ordinary value.

WHERE keeps rows whose predicate is TRUE; FALSE and UNKNOWN are both filtered. Correct SQL should use IS NULL/IS NOT NULL and reason explicitly about nullable columns in joins, NOT IN, aggregates, and constraints.

Unit 5: Relational Algebra

Relational algebra is a formal collection of operators that transform relations.

Selection

Selection filters tuples:

sigma_condition(R)

It corresponds conceptually to a SQL WHERE predicate.

Projection

Projection selects attributes:

pi_A,B(R)

Pure relational projection removes duplicates; SQL SELECT does not unless DISTINCT is specified.

Cartesian product

R x S

pairs every tuple of R with every tuple of S. Join operations can be understood as product plus restriction, although DBMS engines do not need to physically materialize the full product.

Union, intersection and difference

Set operations require compatible relation headings/domains. SQL provides UNION, INTERSECT and product-specific difference operators such as Oracle MINUS or standard EXCEPT in other systems.

Natural join

Natural join matches same-named attributes automatically. It is concise in theory but can be risky in evolving SQL schemas because adding a same-named column may silently change join semantics. Explicit join predicates are usually clearer in production SQL.

Division

Relational division models "for all" queries, such as finding entities related to every member of another set. SQL normally expresses it using nested NOT EXISTS, grouping/counting or equivalent formulations.

Unit 6: SQL

Role of SQL

SQL combines data definition, querying, manipulation, access control and transaction-related facilities. It is declarative: the query specifies the required result, while the optimizer chooses an execution plan subject to DBMS semantics.

SELECT structure

A typical query is:

SELECT column_list
FROM table_source
WHERE row_condition
GROUP BY grouping_columns
HAVING group_condition
ORDER BY sort_expression;

Logical query processing order is not identical to the textual order, which explains why aliases visible in ORDER BY may not be available in every other clause.

Expressions and precedence

Arithmetic expressions can combine columns and literals. Parentheses should make intended precedence explicit when an expression is not obvious.

NULL

NULL represents missing/unknown/inapplicable information according to context; it is not zero or an empty string in the relational-theory sense. SQL comparisons with null participate in three-valued logic.

Use:

IS NULL
IS NOT NULL

rather than = NULL.

Aliases and concatenation

Column aliases improve result readability. Oracle uses || for string concatenation:

FIRST_NAME || ' ' || LAST_NAME

Duplicate rows

SELECT DISTINCT removes duplicate result rows. Without it, SQL generally retains duplicate rows produced by the query.

Predicates

Common predicates include:

= <> < > <= >=
BETWEEN
IN
LIKE
IS NULL

BETWEEN is inclusive at both bounds. LIKE commonly uses % for any sequence and _ for one character, subject to collation/character semantics of the DBMS.

Logical operators are NOT, AND, OR; parentheses should be used when mixing them to avoid precedence mistakes.

Ordering

ORDER BY is what gives a query result a defined presentation order. Storage order or an index should not be treated as an implicit row-order guarantee.

Unit 7: SQL Functions

Character functions

Functions such as LOWER, UPPER and LENGTH transform or inspect text. Exact length semantics can differ between characters and bytes for multibyte encodings; Oracle also provides byte-oriented variants where required.

Numeric functions

ROUND, TRUNC and MOD perform common numeric operations. Rounding and truncation semantics should be distinguished, especially for negative values and decimal positions.

Date functions and conversion

Dates should be handled as date/time types, not formatted strings, whenever possible.

Explicit conversion makes format contracts visible:

SELECT TO_DATE('2014-11-09', 'YYYY-MM-DD')
FROM DUAL;

TO_CHAR formats values as text; TO_NUMBER and TO_DATE parse text according to format/NLS rules. Implicit conversion can make behavior dependent on session settings and should not be relied upon for stable application interfaces.

General functions

Oracle's NVL replaces null with another expression under its type-conversion rules. COALESCE is a standard alternative that selects the first non-null expression.

DECODE is an Oracle-specific conditional function retained in many legacy queries. CASE is generally more expressive and portable.

Functions can be nested; type conversion and null propagation should be checked at each layer rather than inferred from final syntax alone.

Unit 8: Grouped Analysis

Aggregate functions include:

• AVG,
• SUM,
• STDDEV,
• VARIANCE,
• MAX,
• MIN,
• COUNT.

Most aggregates ignore null input values, with details depending on the expression. COUNT(*) counts rows; COUNT(column) counts non-null values of that expression.

GROUP BY partitions rows into groups before aggregates are calculated. Every selected non-aggregate expression must be compatible with the grouping rules of the DBMS.

HAVING filters groups after grouping, whereas WHERE filters rows before grouping.

Grouping by several columns forms groups for each distinct combination.

Unit 9: Multiple Tables

Joins

Joins combine rows according to a relationship:

SELECT ...
FROM EMPLOYEE e
JOIN DEPARTMENT d
  ON d.DEPARTMENT_ID = e.DEPARTMENT_ID;

A missing join predicate can create a Cartesian product.

Qualified column names remove ambiguity when different tables contain the same column name.

Non-equality and outer joins

A join predicate need not use equality; range and inequality joins are possible where the model calls for them.

Outer joins preserve unmatched rows from one or both sides and fill missing counterpart columns with null. Predicate placement matters because a WHERE condition can unintentionally eliminate the null-extended rows and change the effective semantics.

Self join

A table can be joined to itself through aliases, for example employee-to-manager relationships.

Set operations

UNION combines compatible results and removes duplicates. UNION ALL retains duplicates and avoids the duplicate-elimination requirement.

INTERSECT keeps common rows. Oracle MINUS returns rows in the first result not present in the second.

Unit 10: Complex Queries

Subqueries

A subquery supplies a value, row or relation to another query. It can appear in predicates, expressions, FROM, DML and other supported contexts.

A scalar/single-row subquery must return at most one row where a scalar value is required. Multiple rows cause an error in such a context.

Multi-row operators

IN tests membership. ANY/SOME compares against at least one subquery value. ALL requires the comparison to hold for all returned values.

Nulls can make NOT IN especially surprising: if the subquery contains null, three-valued logic may prevent expected matches. NOT EXISTS often expresses anti-join intent more safely when nullability is possible.

Correlation and inline views

A correlated subquery references values from the outer query and is conceptually reevaluated per outer row, although the optimizer may transform execution.

A subquery in FROM creates an inline view/derived table that can be joined or aggregated like another relation.

Unit 11: DML and Transaction Processing

INSERT, UPDATE and DELETE

INSERT adds rows, UPDATE changes matching rows and DELETE removes matching rows. Every DML statement should be checked for its predicate scope; an omitted WHERE can intentionally or accidentally affect all rows.

Data can be inserted explicitly or from another query. Functions and explicit conversions can be used as part of inserted expressions.

Transaction

A transaction groups operations into a logical unit of work. COMMIT makes the transaction's changes durable/visible according to DBMS isolation rules. ROLLBACK cancels uncommitted work back to the transaction or savepoint scope supported by the DBMS.

Atomicity does not mean every transaction is isolated from every concurrent effect; isolation level determines which interleavings are visible.

Application design should define transaction boundaries around business invariants rather than commit arbitrarily inside reusable low-level routines.

Unit 12: Database Objects

Tables and data types

CREATE TABLE defines columns and constraints. Names should be stable, consistent and meaningful within the schema conventions.

A table can also be created from a query where the DBMS syntax supports it, but constraints, defaults, comments and other metadata may not be copied automatically.

Schema evolution can add/modify columns subject to existing data and dependency rules. DROP TABLE removes a table object; TRUNCATE removes rows through DDL-like semantics that differ from row-by-row DELETE, including transaction/logging behavior depending on the DBMS.

Constraints

Common constraints are:

• NOT NULL,
• UNIQUE,
• PRIMARY KEY,
• FOREIGN KEY,
• CHECK.

Constraints should encode stable data invariants in the database whenever feasible so every writer is subject to the same rules.

Views

A view stores a query definition and presents it as a virtual relation. Views can:

• simplify complex queries,
• restrict exposed columns/rows,
• stabilize an interface,
• support security boundaries.

Whether a view is directly updatable depends on its query structure and DBMS rules.

Indexes

An index is an access structure, not a logical requirement for a query. It can improve equality/range/join/order access patterns but adds storage and write-maintenance cost.

An index is most useful when its key order/selectivity and the workload allow the optimizer to avoid substantial work. Very small tables or predicates returning most rows may favor scans.

Dropping an index changes performance possibilities but should not change correct logical query results unless application code incorrectly depended on implicit ordering.

Unit 13: User Access Control

Database security separates authentication from authorization. Users/principals receive system privileges, object privileges and roles according to DBMS facilities.

Privileges should follow least privilege. Granting broad schema/system authority because it is convenient during development creates a larger failure and compromise domain.

Object privileges can govern operations such as SELECT, INSERT, UPDATE, DELETE or execution of program units. REVOKE removes previously granted rights subject to dependency/cascade semantics.

Roles group privileges so policy can be managed at a meaningful organizational level rather than as many individual grants.

Password management, external identity integration and privileged administrative accounts require stronger controls than ordinary application schemas.

Unit 14: Integrating the Advanced SQL Topics

The relational model, normalization, SQL and physical access structures belong to different layers:

domain facts
   ↓
conceptual ER model
   ↓
relational schema + constraints
   ↓
SQL queries and transactions
   ↓
indexes / execution plans / storage

A normalization problem is not solved by adding an index. A slow query is not automatically evidence that the schema should be denormalized. A transaction problem is not solved by hiding a COMMIT inside every procedure.

The useful discipline is to identify which layer owns the problem: model the facts correctly, enforce their invariants, express the required set operation in SQL, and only then optimize the physical execution path with measured evidence.

Storage Engines, Buffer Pools, and Recovery

SQL exposes a relational interface, while the physical cost of a query is paid through pages, indexes, memory, logs, and storage. Keeping the relational model separate from its physical implementation makes both performance and durability decisions easier to reason about.

Pages and the buffer pool

Database systems usually perform I/O in pages rather than reading one logical row at a time. Frequently accessed pages can remain resident in a buffer pool.

SQL
 ↓
executor
 ↓
buffer pool
 ↓ cache miss
storage page

A query that "uses an index" is not automatically inexpensive. Selectivity, data clustering, number of pages touched, and current cache state all affect the physical work.

B+ trees

The B+ tree family is widely used because it supports point and range access with a small number of page traversals. Internal nodes contain routing keys, while leaf nodes contain records or record references.

root
 ├─ internal
 │   ├─ leaf
 │   └─ leaf
 └─ internal
     ├─ leaf
     └─ leaf

High fan-out keeps tree height limited even for large datasets. Random insertion can nevertheless introduce page splits and fragmentation cost.

Write-ahead logging

A durable transaction system commonly separates modified data pages from the log records that describe those modifications. Under write-ahead logging, the log record must become durable before the corresponding dirty data page is allowed to reach persistent storage.

transaction change
      ↓
log record
      ↓
durable log
      ↓
data page writeback

Returning success from commit therefore does not necessarily require every modified data page to be written immediately. The exact durability boundary depends on the product's logging and flush policy.

Crash recovery

After an unexpected shutdown, a database can use checkpoints and the transaction log to determine which work must be redone and which incomplete effects must be undone or ignored. Recovery algorithms differ between products, but the objective is the same: preserve transactional atomicity and durability across process or machine failure.

Logical and physical plans

Relational algebra permits multiple equivalent evaluation orders. The optimizer chooses a physical plan using statistics, cardinality estimates, available access paths, and a cost model.

Poor cardinality estimates can produce a bad join order or access strategy even when useful indexes exist. Performance analysis should therefore inspect the actual execution plan, estimated-versus-actual row counts, and physical I/O, not only the SQL text.

WAL, page flushing, and crash recovery

A buffer pool lets frequently used pages remain in memory, but modified pages cannot be written to storage in arbitrary order if crash recovery must be correct.

Write-ahead logging requires the log information describing a change to become durable before the corresponding dirty data page is allowed to reach stable storage. After a crash, the recovery algorithm can use the log to redo committed work and, depending on the design, undo incomplete work.

This is why transaction commit, page flush, and storage write are different events. Durability is a protocol across memory, log, database cache, filesystem, controller, and device.

The exact implementation differs across database products, but the ordering principle explains why recovery cannot be reduced to “write the row to disk.”

The Difference Between an Ideal Schema and a Legacy Production Data Model

Database education naturally explains candidate keys, primary keys, foreign keys, unique constraints, and normalization through an idealized design. Long-lived production systems do not always match that ideal. Historical duplicates, missing constraints, several physical rows for the same logical entity, or integration tables that cannot be changed may exist.

The application must therefore distinguish two models:

the data model we would like to design
                  !=
the data behavior that actually exists in production

A field can appear unique from a business perspective without having a database constraint that guarantees uniqueness. In that case, an application should not safely assume that a findOne()-style query can return only one row. Multiple rows may indicate a defect, historical reality, or separate life-cycle states. The actual data behavior should be measured first, followed by a deterministic selection or explicit conflict policy.

When the schema cannot be changed, some integrity responsibility can move into the application layer. This does not make database constraints unimportant; it means that an application without schema ownership must not assume guarantees the source does not provide. Write paths may therefore need stronger safeguards than read paths: identifying the exact physical row, revalidating state inside the transaction, and checking the expected affected-row count.

Physical locators must also remain separate from domain identity. Oracle ROWID can be useful for reaching a specific row but is not a business key. Its controlled use is discussed further in Oracle Database and PL/SQL.

The broader lesson is to design against the guarantees that the real data source can actually provide, not against an idealized schema that exists only in documentation. Safe legacy-data handling often requires fewer assumptions, not more.

From SQL Queries to Transaction Design

Knowing SQL syntax and designing a correct database transaction are different skills. The same SELECT can have very different cost and concurrency behavior depending on data volume, indexes, isolation, and concurrent writes.

Oracle Database, PostgreSQL, Microsoft SQL Server, MySQL, and IBM Db2 are examples of relational database management system families. They share relational and SQL concepts but differ in data types, DDL and transaction behavior, optimizer implementation, procedural extensions, locking/MVCC details, and administration. Product-specific syntax and performance assumptions should not be transferred blindly between systems.

RDBMS is the common abbreviation for relational database management system. Older material may also use MSSQL informally for Microsoft SQL Server; the current product name is Microsoft SQL Server. The abbreviation itself does not denote a different relational model.

SQL command categories

DDL, DML, DCL, and TCL are useful conceptual categories. CREATE/ALTER/DROP operate on schema objects; SELECT/INSERT/UPDATE/DELETE access or modify data; GRANT/REVOKE manage privileges; and COMMIT/ROLLBACK/SAVEPOINT control transactions. Exact classification and DDL transaction behavior are product dependent.

JOIN and subquery semantics

INNER, LEFT, RIGHT, and where supported FULL OUTER JOIN differ first in row-preservation semantics. The correct choice begins with required results rather than a universal claim that one join type is faster. The optimizer separately chooses physical join algorithms.

EXISTS, IN, correlated subqueries, and joins can sometimes express equivalent questions, but NULL behavior and row multiplication make blind rewrites unsafe. Cardinality and result semantics come before performance rewriting.

GROUP BY, aggregates, and HAVING

WHERE filters rows before grouping, while HAVING filters groups after aggregation. Aggregate functions also have important NULL rules: COUNT(*) counts rows, whereas COUNT(column) counts non-NULL values of that column.

The real cost of an index

A B-tree index can reduce read cost but adds write maintenance, storage, and cache/memory pressure. Indexing every column is not a sound rule. Column order in composite indexes, selectivity, predicates, ordering, and optimizer behavior all matter.

Even when an index is available, using it is not necessarily cheaper. A sequential/full scan can be preferable when a large fraction of a table must be read.

ACID and isolation

Atomicity concerns all-or-nothing transaction effects; consistency concerns preservation of defined invariants; isolation controls interaction among concurrent transactions; durability concerns persistence of committed state through failures.

A higher isolation level does not reduce every system to the simple formula “safer but slower.” Locking versus MVCC, workload shape, and contention determine behavior. Lost update, dirty read, non-repeatable read, and phantom anomalies should be evaluated against the actual business invariant.

Constraints, triggers, and application code

PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and NOT NULL constraints express integrity close to the data. Triggers can enforce automatic behavior on changes but also create hidden side effects and operational complexity. Choosing between database-side rules and application logic depends on transaction boundaries, multiple writers, and the need for transparent operations.

The NoSQL distinction

NoSQL is not one data model. Key-value systems such as Redis, document stores, wide-column systems such as Apache Cassandra, and graph databases optimize for different access patterns. Cassandra is not a direct substitute for a relational table model; its distributed wide-column model and query constraints push data modeling closer to access patterns. Claims such as “NoSQL is faster” or “relational databases do not scale” are not generally valid. The choice follows query, transaction, consistency, distribution, and operational requirements.

Isolation anomalies and idempotent transactions

A committed transaction can still violate a business invariant when concurrent transactions interact. Lost updates, write skew, phantoms, and non-repeatable reads depend on the actual isolation semantics of the database engine.

Rules spanning multiple rows may require constraints, stronger isolation, atomic DML, or explicit locking rather than a single row lock.

Retries from distributed clients also matter. If the same command can arrive twice, an idempotency key or uniqueness rule can prevent duplicated side effects. Transaction design therefore includes replay behavior as well as rollback.

Verifying database behavior through explicit contracts

SQL-standard semantics and the behavior of a concrete database engine should not be treated as the same layer. Isolation-level names can hide materially different MVCC and locking implementations.

Query correctness is judged by the result set; performance is evaluated separately through plans and representative workloads. A query that is fast on a small table may choose a different plan under another data distribution. Index benefits should be weighed against write, storage, and maintenance costs.

Data integrity should be expressed in the schema where practical. Primary/unique keys, foreign keys, check constraints, and transaction boundaries reduce dependence on duplicated client-side rules.

From Database Systems to the AI Data Layer

A machine-learning system is not only an algorithm. The origin of training examples, their version, feature transformations, and the relationship between inference output and business records all influence behavior. The strongest connection between database systems and AI appears in this data life cycle.

Relational design is useful for making provenance explicit: where an example came from, who produced its label, which time interval it belongs to, and which transformation version was applied. Once this information is lost, reproducibility is weakened even if the model file is preserved.

The following entities should not be collapsed conceptually:

raw record
≠
feature
≠
model input
≠
model output
≠
human-validated result

Normalization, constraints, keys, and transactions provide value here because they make these boundaries enforceable rather than documentary.

Time semantics are equally important. A training query can be valid SQL yet leak future information into historical features. Data leakage then produces an unrealistically strong evaluation. Event time, ingestion time, and label-availability time must therefore be distinguished when features are reconstructed.

Vector representations add a different access path. An embedding represents text, images, or other objects in a high-dimensional space and can support similarity search. It does not replace relational identity:

primary / foreign key → exact identity and relation
vector similarity      → representation-dependent proximity

These answer different questions. Vector search complements, rather than replaces, transactional integrity.

Retrieval-augmented systems create another database responsibility. A generative model does not independently guarantee that retrieved context is current, authorized, or scoped to the correct tenant. Filtering, version selection, authorization, and provenance remain properties of the retrieval and storage layer.

Transaction design also survives unchanged. If a model suggestion and a human-approved decision are different business events, they should not be stored as one ambiguous state. Retries need idempotency; repeated inference requests should not accidentally create repeated business actions.

Database systems do not determine how an AI model learns. They determine whether the data used for learning, validation, retrieval, and production decisions remain identifiable, consistent, time-correct, and auditable.

From query results to transaction semantics

Producing the correct rows and producing correct concurrent behaviour are different problems. A SELECT statement can be logically correct while the surrounding workflow still permits lost updates, dirty reads, or inconsistent repeated reads. Query logic and transaction boundaries therefore have to be considered together.

A primary key identifies a row logically; an index is an access structure. Many database systems create an index for a primary key, but the concepts are not synonymous. Indexes are not free either: they may reduce read cost while increasing storage, insertion, update, and maintenance work.

Normalisation reduces redundancy and update anomalies. Analytical or read-heavy systems may still keep derived or duplicated structures deliberately for performance. In that case, the mechanism maintaining consistency has to be explicit. Normalisation and performance are not absolute opposites.

WHERE filters rows before grouping, whereas HAVING filters groups after aggregation. With a LEFT JOIN, placing a condition on a right-side column in the WHERE clause can remove the NULL-extended rows and make the result behave much like an inner join. Join type and filter placement must therefore be read together.

The ACID properties should also remain separate. Atomicity is all-or-nothing application of a transaction; consistency concerns declared invariants; isolation controls interaction and visibility among concurrent transactions; durability concerns persistence after commit. Satisfying one of these properties does not automatically imply the other three.

The Physical Layer Between File Organization and the Database Engine

The relational model lets us reason in terms of tables, rows, columns, and keys, while storage devices ultimately move pages, blocks, and bytes. File organization is the layer between these abstractions. Some computer-engineering curricula teach it as a separate course because the logical result of an SQL query and the physical I/O required to produce that result are not the same thing.

Records, pages, and free space

Database engines usually manage rows inside pages or blocks that are larger than individual disk sectors. For variable-length rows, a common design keeps a slot directory in the page header. Slots identify record locations, allowing a row to move within the page without forcing higher layers to depend on a raw byte offset.

page
┌─────────────────────────────┐
│ header / free-space data    │
│ slot 0 → record location    │
│ slot 1 → record location    │
│ ...                         │
│          free space         │
│ records grow from the end ← │
└─────────────────────────────┘

Row length, page occupancy, and row growth during updates are not merely storage details. They affect how many rows fit in a page, how many pages must be read, how effective the buffer pool is, and how much additional I/O an index lookup produces.

Heap, sequential, and hash organization

A heap file places records in available space without maintaining a key order. Inserts can be inexpensive, while finding a particular row may require a broad scan when no index is available.

A sequential or ordered organization attempts to preserve records in key order. This can benefit range scans but introduces maintenance costs for insertion, page splitting, and reorganization.

A hash-based organization maps a key to a bucket and can make equality lookup efficient. It does not naturally preserve an order for range scans. The access method should therefore be selected together with the type of query operator.

WHERE id = ?             → hash or B+ tree may fit
WHERE date BETWEEN ...   → ordered/B+ tree access is natural
ORDER BY date            → existing order may reduce extra work

B+ trees, clustering, and secondary access

In a B+ tree, internal nodes contain routing keys while leaf nodes contain keys and row-access information. High fan-out keeps tree height small. Yet the statement “an index exists” is still incomplete. When leaf order corresponds closely to physical data-page order, sequential access can preserve locality. A secondary index that identifies many scattered rows may instead turn each match into a separate page access.

This is the essential performance distinction between clustered and unclustered access. Product terminology and implementation details vary, but the invariant remains: logical key order and physical row placement are not identical concepts.

External sorting and large intermediate results

When data does not fit in memory, sorting is typically performed by sorting memory-sized runs, writing those runs to storage, and merging them in multiple passes. External merge sort is a useful model for understanding the I/O cost of operations such as ORDER BY, GROUP BY, DISTINCT, sort-merge join, and index construction.

large input
   ↓
memory-sized runs
   ↓
sort and write each run
   ↓
multi-way merge
   ↓
sorted result

A hash join has a similar boundary: when its build side no longer fits in memory, partitioning and spill to storage can change latency dramatically even though the SQL text remains unchanged.

Why file organization still matters

Modern DBMS products hide most physical placement decisions, yet query optimization, index selection, vacuum or compaction behavior, write amplification, buffer-cache efficiency, and external-sort cost are built on those physical facts. This creates a direct bridge between the B+ tree and hashing material in Data Structures and Algorithm Analysis and the behavior of a database query plan.

It is useful to separate the layers of a database problem:

relational model      → what result is correct?
query plan            → which operators will be used?
access path           → which index or scan is selected?
file organization     → which pages contain the rows?
storage               → how much actual I/O and writing occurs?

This distinction turns performance work from a reflexive “add an index” response into a measurable data-access problem.

References

• Abraham Silberschatz; Henry F. Korth; S. Sudarshan. Database System Concepts. McGraw-Hill, 2010.

• Apache Software Foundation. Apache Cassandra Documentation. https://cassandra.apache.org/doc/stable/

• E. F. Codd. A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 1970. DOI

• IBM. Db2 Documentation. https://www.ibm.com/docs/en/db2

• Patrick Lewis et al. “Retrieval-Augmented Generation for Knowledge-Intensive NLP Tasks.” Advances in Neural Information Processing Systems, 33, 2020.

• Peter P. Chen. The Entity-Relationship Model: Toward a Unified View of Data. ACM Transactions on Database Systems, 1976. DOI

• Ramez Elmasri; Shamkant B. Navathe. Fundamentals of Database Systems. Addison-Wesley, 2010.

• Yu. A. Malkov, D. A. Yashunin. “Efficient and Robust Approximate Nearest Neighbor Search Using Hierarchical Navigable Small World Graphs.” IEEE Transactions on Pattern Analysis and Machine Intelligence, 42(4), 2020. https://doi.org/10.1109/TPAMI.2018.2889473