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

1.1. 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.

1.2. 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.

1.3. 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.

1.4. 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.

1.5. 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.

1.6. 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.

1.7. Functional DBMS Layers, the Catalog, and Data Independence

A database management system is not merely a file layer that stores tables. Several functional components cooperate between a declarative request and physical storage:

DDL / schema definitions
        ↓
system catalog / data dictionary
        ↓
query resolution and authorization
        ↓
logical plan → optimizer → physical plan
        ↓
storage / buffer / transaction management
        ↓
physical data and log records

The catalog/data dictionary may contain not only table and column definitions, but also keys, constraints, users and privileges, indexes, and statistics used by the optimizer. Data definitions therefore become centralized and enforceable metadata rather than being duplicated throughout application code.

Conceptually, the major DBMS responsibilities can be separated as follows:

• DDL processing: validate schema and constraint definitions and persist them in the catalog.
• Query processing: perform syntactic/semantic checks and resolve object and column references.
• Query optimization: choose a physical plan among equivalent execution alternatives using a cost model.
• Storage management: manage pages, files, and access structures on persistent storage.
• Buffer management: manage page movement between persistent storage and memory.
• Transaction management: coordinate concurrency, atomicity, and recovery.

A central purpose of this layering is data independence. Separating the logical schema from the physical storage representation supports physical data independence; insulating external/user views from selected conceptual-schema changes supports logical data independence. Neither is an unlimited guarantee: the engineering goal is to reduce unnecessary coupling between applications and implementation details.

User roles are separated for the same reason. A DBA manages schema, security, capacity, and operational concerns; application developers consume a defined data contract; ad-hoc query users and end users running predefined operations need not receive the same privileges or tools.

Historical hierarchical and network data models largely represent navigational access through predefined paths. They are not the primary focus of modern relational design, but they provide useful historical context for understanding why data independence and declarative querying became important.

Unit 2: Entity-Relationship Model

Entity-relationship modeling begins by testing business rules against concrete example records. After entities and candidate keys are identified, validate relationship cardinality and optional participation separately. The decision to introduce an association table depends on many-to-many structure and whether the relationship has attributes of its own. Once the schema is drafted, show which constraints handle missing records, duplicate relationships, and deletions.

2.1. ER Model

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

2.2. 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.

2.3. 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.

2.4. 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.

2.5. 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.

2.6. 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.

2.7. 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.

2.8. 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.

2.9. 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.

2.10. Generalization, Specialization, Aggregation, and higher-degree Relationships

Entity, attribute, and binary relationship constructs are not always sufficient to preserve domain semantics in an ER model.

Generalization/specialization moves common properties into a supertype while preserving subtype-specific properties. Two independent constraints should be stated explicitly:

• disjoint / overlapping: may one supertype instance belong to more than one subtype?
• total / partial: must every supertype instance belong to a subtype?

When mapped to relations, several physical representations are possible: a single table, a supertype table plus subtype tables, or only concrete subtype tables. The choice affects both integrity enforcement and query/write cost.

Aggregation allows a relationship itself to be treated as a higher-level modeling object. If an approval concerns a specific Employee — AssignedTo — Project fact, for example, the approval belongs to the assignment as a whole rather than independently to the employee or project.

A ternary or higher-degree relationship is appropriate when three or more entities participate in one indivisible fact. Mechanically replacing it with several binary relationships can lose which combination of participants actually occurred together. A common relational mapping creates a separate relation containing participant keys plus the relationship's own attributes.

Typical mappings include:

strong entity      → table with its own key
weak entity        → owner key + discriminator
1:N relationship   → usually a foreign key on the N side
M:N relationship   → relationship table carrying both keys
n-ary relationship → relationship table carrying participant keys
generalization     → selected inheritance-mapping strategy

The objective is not merely to produce tables, but to preserve cardinality, existence, and identity rules from the conceptual model using relational constraints where possible.

Unit 3: The Relational Data Model

3.1. 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.

3.2. 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.

3.3. 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.

3.4. 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.

3.5. 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.

3.6. 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.

A table's primary key does not by itself establish the unit that an analysis intends to count. The key of a transaction table may identify transactions, while a report concerns customers; one customer may have many transactions. COUNT(*) and COUNT(DISTINCT customer_id) answer different questions. One-to-many joins can duplicate measures and corrupt totals. A sound reporting model first declares the grain of an output row, then specifies the keys, cardinalities and permitted joins of each source. These constraints form a structural defense against silently counting the same entity twice.

Unit 4: Relational Database Design

Normalization is not simply the mechanical splitting of tables. Identify functional dependencies and candidate keys, then determine which dependencies cause insertion, update, or deletion anomalies. After decomposition, verify lossless joining and, when feasible, dependency preservation. Deliberate denormalization for read performance does not replace these checks; it requires a separately justified design decision.

4.1. 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.

4.2. 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.

4.3. 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.

4.4. 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.

4.5. 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.

4.6. 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.

4.7. 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.

4.8. 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.

Do not treat logical integrity and physical access as opposing goals. Normalization reduces update anomalies; an expensive query is not by itself a reason to denormalize. Examine predicate and join selectivity, projected rows and columns, index suitability, and statistics first. If redundant fields are introduced, the design must specify how they are maintained, which transaction preserves consistency, and how historical rows will be backfilled.

Comparing plans with and without an index requires more than elapsed time. Inspect pages or buffers touched, actual output rows, cardinality-estimation errors, and the extra work imposed on every INSERT, UPDATE, or DELETE. A faster isolated SELECT can make a write-heavy system slower overall. Derive the optimization decision from the read/write workload mix rather than one SQL statement.

4.9. 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.

4.10. 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.

4.11. Inferring Functional Dependencies, Closure, and Canonical Covers

Observing dependencies only in a current sample of data is not sufficient for normalization. Design should represent stable domain rules as dependencies and determine systematically what additional dependencies follow from them.

For a dependency set F, **F+** denotes all functional dependencies logically implied by F. In practice it is usually unnecessary to enumerate the entire set; attribute-set closure is more useful:

X+ = the attributes functionally determined by X under F

If X+ contains every attribute of the relation, X is a superkey. If no proper subset of X has that property, it is a candidate key.

The classic inference foundation is given by Armstrong's axioms:

1. Reflexivity: if Y ⊆ X, then X → Y.
2. Augmentation: if X → Y, then XZ → YZ.
3. Transitivity: if X → Y and Y → Z, then X → Z.

Union, decomposition, and pseudotransitivity can be derived from these axioms.

A canonical/minimal cover is a smaller dependency set with the same closure but without redundant dependencies or redundant attributes. It makes algorithms such as 3NF synthesis more systematic.

4.12. Decomposition Correctness: Lossless Join and Dependency Preservation

Normalization is not the act of splitting a table into as many pieces as possible. At least two independent correctness properties must be considered.

A lossless-join decomposition allows the original relation to be reconstructed without generating spurious tuples. For a binary decomposition R → R1, R2, a commonly used criterion is that the common attributes functionally determine at least one side:

(R1 ∩ R2) → R1
or
(R1 ∩ R2) → R2

Dependency preservation asks whether the original functional dependencies can be enforced on the decomposed relations without joining them back together. This is different from losslessness: a decomposition may be lossless while making some original dependencies expensive to enforce.

This distinction exposes the real engineering tradeoff between 3NF and BCNF:

• BCNF imposes a stronger determinant condition and can remove anomalies that remain under 3NF.
• A lossless BCNF decomposition does not guarantee dependency preservation in every case.
• Proper 3NF synthesis can provide both losslessness and dependency preservation, while allowing schemas that are not BCNF.

The design question is therefore not simply “use the highest normal form.” The invariants that must be enforced, where they can be checked, and the cost of production access paths must be considered together.

Multivalued dependencies are also useful beyond memorizing the 4NF definition. They explain why placing independent multivalued facts in one relation creates unnecessary cross-combinations. 4NF restricts meaningful multivalued dependencies by requiring the determinant to be a superkey.

Moving from functional dependencies to a normalization decision. Consider OrderLine(OrderId, ProductId, CustomerId, CustomerName, Quantity). Assume each order belongs to one customer and each product appears at most once per order. The candidate key is (OrderId, ProductId), with dependencies OrderId → CustomerId, CustomerId → CustomerName, and (OrderId, ProductId) → Quantity. Repeating customer names across order lines creates update anomalies and makes independent customer records awkward. A decomposition is Customer(CustomerId, CustomerName), Order(OrderId, CustomerId), and OrderLine(OrderId, ProductId, Quantity). Verification is not simply about reducing column count: check keys, necessary foreign keys, lossless joins, and preservation of business constraints. If the same product may occur on several lines in one order, the candidate-key assumption must change accordingly.

Unit 5: Relational Algebra

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

5.1. Selection

Selection filters tuples:

sigma_condition(R)

It corresponds conceptually to a SQL WHERE predicate.

5.2. Projection

Projection selects attributes:

pi_A,B(R)

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

5.3. 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.

5.4. 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.

5.5. 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.

5.6. 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.

5.7. The Logical Foundation of Relational Calculus

Relational algebra expresses a query as a composition of operations, whereas relational calculus defines the required result using a logical predicate. This distinction helps explain the declarative nature of SQL.

Core logical tools include:

• ∃ — existential quantifier: at least one value satisfies the predicate.
• ∀ — universal quantifier: the predicate holds for all relevant values.
• ¬, ∧, ∨ — negation, conjunction, and disjunction.
• bound variable: occurs within the scope of a quantifier.
• free variable: may represent a value returned by the query.

“Customers with at least one open order” is naturally existential. “Employees who completed every mandatory course” is universal and is often represented in SQL by stating that no counterexample exists with NOT EXISTS.

Relational algebra and relational calculus use different representations; a foundational result of relational theory is that, for appropriate safe query classes, the same relational questions can be expressed in both forms. Practically, algebraic operators help explain optimization and physical plans, while logical quantifiers help clarify what a declarative query actually means.

Historical teaching languages using constructs such as RANGE and GET are useful for making relational calculus concrete, but they should not be treated as modern portable replacements for SQL.

Unit 6: SQL

6.1. 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.

6.2. 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.

6.3. Expressions and Precedence

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

6.4. 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.

6.5. Aliases and Concatenation

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

FIRST_NAME || ' ' || LAST_NAME

6.6. Duplicate Rows

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

6.7. 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.

6.8. 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

7.1. 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.

7.2. Numeric Functions

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

7.3. 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.

7.4. 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.

Aggregation deliberately trades detail for a summary. If one group contains 10 observations with mean 90 and another contains 90 observations with mean 50, averaging the two means yields 70, while the pooled mean is (10×90 + 90×50)/100 = 54. Without retaining group sizes or sufficient summary statistics, a correct pooled result may be impossible to recover. Collapsing NULL, zero and not-applicable values into the same code can likewise change denominator semantics. Each analytical measure should therefore declare its aggregation rule, missingness semantics and the conditions under which it can be aggregated again.

Unit 9: Multiple Tables

9.1. 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.

9.2. 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.

9.3. Self Join

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

9.4. 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

10.1. 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.

10.2. 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.

10.3. 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

11.1. 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.

11.2. 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

12.1. 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.

12.2. 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.

12.3. 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.

12.4. 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.

13.1. Layers of Database Security

Authorization is more than the syntax of GRANT and REVOKE. Database security should distinguish at least the following questions:

1. Authentication: who is the connecting principal?
2. Authorization: which operations may that principal perform on which objects?
3. Least privilege: have unnecessary privileges been granted?
4. Visibility: can views or row/column policies reduce unnecessary exposure?
5. Auditability: can sensitive access and changes be reconstructed later?
6. Confidentiality versus integrity: protecting secrecy and protecting correctness are different concerns.

Role-based authorization makes privileges more manageable than scattering rights across individual accounts; views can narrow exposed query surfaces. Neither roles, views, nor encryption alone satisfy the complete security model. Identity, privilege, integrity, auditing, and operational controls must be designed together.

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.

14.1. 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.

14.1.1. 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.

14.1.2. 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.

14.1.3. 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.

14.1.4. 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.

14.1.5. 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.

14.1.6. 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.”

14.2. The Theoretical query-processing Path: from Equivalence to a Physical Plan

SQL execution is not a single-step operation. Conceptually, the path is:

parse / name and type resolution
        ↓
logical query representation
        ↓
algebraic-equivalence transformations
        ↓
physical-operator candidates
        ↓
cardinality + cost estimation
        ↓
execution plan

A classic goal of heuristic transformations is to reduce intermediate results early. When semantically valid, predicate pushdown, early removal of unnecessary columns (projection pushdown), replacement of Cartesian-product-plus-filter patterns by joins, and join reordering are examples of this idea.

Logical equivalence does not guarantee equal physical cost. The same logical join may be executed by nested-loop, hash, or merge algorithms. An optimizer estimates relation/intermediate sizes, selectivity, access paths, memory pressure, I/O, and in some systems parallel or communication costs. “Algebraically equivalent” and “physically equal-cost” must therefore remain distinct concepts.

Query-plan cost depends on selectivity; the mere existence of an index does not make an index scan optimal. For a million-row table, a predicate returning nearly every row may favor a sequential scan over many random index lookups. A highly selective predicate can instead benefit from an appropriately ordered index. EXPLAIN describes the chosen plan, while elapsed time, rows actually read, and cache effects still require measurement. Index-maintenance costs on writes and freshness of optimizer statistics also belong in the decision.

14.3. Recovery Models: UNDO/REDO, Checkpoints, and Shadow Paging

Crash recovery reconciles the state on persistent storage with the contract that committed transactions must survive while incomplete work must not appear as committed state.

• UNDO removes persistent effects of an uncommitted transaction.
• REDO reapplies effects of a committed transaction that are not fully reflected in data pages.
• Checkpointing helps bound how much log history must be considered during restart.
• Shadow paging is an alternative family of recovery techniques that writes new page versions separately and makes them visible by switching page/root mappings rather than modifying current pages in place.

These ideas describe product-independent recovery theory. Modern engines differ in their exact WAL, MVCC, and checkpoint implementations, so the general UNDO/REDO model should not be read as the literal internal algorithm of every DBMS.

14.4. 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.

14.5. 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.

14.5.1. 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.

14.5.2. 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.

14.5.3. 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.

14.5.4. 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.

14.5.5. 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.

14.5.6. 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.

14.5.7. 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.

14.6. 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.

14.7. 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.

14.8. 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.

14.9. 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.

14.10. 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.

14.10.1. 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.

14.10.2. 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

14.10.3. 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.

14.10.4. 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.

14.10.5. 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.

14.11. Deep Dive: MVCC, VACUUM, WAL, Indexes, and Query Plans Through PostgreSQL 18

The relational model is DBMS-independent, while transaction visibility, vacuuming, physical index methods, and optimizer details are implementation-specific. PostgreSQL 18 is a useful current engine for making this distinction concrete.

The goal is not PostgreSQL command memorization. It is to see how general database concepts produce specific physical consequences inside a real engine.

14.11.1. MVCC: Multiple Versions of One Logical Row

PostgreSQL manages much of its concurrency through Multiversion Concurrency Control (MVCC).

An update can conceptually leave:

old row version
        +
new row version

coexisting for a period of time.

Transactions apply visibility rules to determine which version they may observe.

An important consequence is that UPDATE and DELETE do not necessarily remove the old physical version immediately. Logical visibility and physical space reclamation are separate mechanisms.

14.11.2. Snapshot Semantics and Isolation

The visible version set depends on transaction isolation.

Read Committed, Repeatable Read, and Serializable do not use identical snapshot semantics.

Conceptually:

Read Committed
 -> later statements may see newer committed state

Repeatable Read
 -> more stable transaction-level snapshot

Serializable
 -> aims for results equivalent to serial execution

Serializable does not mean one global lock that runs transactions one by one. PostgreSQL can use Serializable Snapshot Isolation and reject dangerous concurrency patterns with serialization failures.

Applications using high isolation may therefore need a safe transaction-retry contract.

14.11.3. Dead Tuples and VACUUM

When an old row version is no longer visible to any relevant transaction, its space should eventually become reusable.

Standard VACUUM helps reclaim dead row versions for reuse, maintains visibility information, and participates in transaction-ID age management.

VACUUM and VACUUM FULL are different:

VACUUM
 -> routine maintenance
 -> usually makes space reusable inside the relation

VACUUM FULL
 -> rewrites/compacts the table
 -> heavier locking and disk behavior

Expecting ordinary VACUUM to immediately shrink the operating-system file is therefore incorrect.

14.11.4. Autovacuum Is More Than Cleanup

Autovacuum contributes to statistics maintenance, dead-tuple management, transaction-ID freezing, and wraparound prevention.

Disabling it simply because maintenance consumes resources can create a larger correctness and operational problem later.

The right question is whether it runs with appropriate frequency and resources for the workload.

14.11.5. Visibility Maps and index-only Scans

If PostgreSQL knows that all tuples on a heap page are visible to all transactions, it can record that information in the visibility map.

This matters for some index-only scans. Even when the index contains the requested columns, a heap visit may still be required to verify MVCC visibility unless the page is known to be all-visible.

Vacuum behavior and query performance are therefore directly connected.

14.11.6. WAL, Commit, and Checkpoints

Write-ahead logging requires recovery information to become durable before the corresponding dirty data page is written.

PostgreSQL identifies WAL positions through Log Sequence Numbers (LSNs).

transaction change
  ↓
WAL record
  ↓
WAL durability
  ↓
data page can be written later

A commit does not require every dirty heap and index page to be written to its final file immediately.

Checkpoints help bound recovery work and coordinate dirty-page writeback. Checkpoints that are too frequent can increase write I/O; very infrequent checkpoints can increase recovery work and WAL pressure.

14.11.7. B-tree Is Not the Only Index Model

PostgreSQL provides several access methods.

B-tree is the general choice for equality, ordering, range conditions, and ordered access.

GIN is an inverted-index model useful when one row contains multiple searchable items, such as full-text tokens, arrays, or selected JSONB operators. It can be read-efficient while carrying substantial write cost.

GiST is an extensible search-tree framework for operator classes involving geometric, range, nearest-neighbor, and other specialized relationships.

SP-GiST supports space-partitioned structures such as tries or partitioning schemes that do not naturally fit a balanced B-tree.

BRIN stores summaries for ranges of heap blocks. It can be extremely small and effective for very large tables whose physical order correlates with the indexed value, such as append-heavy time columns.

Index selection should consider data type, operators, distribution, physical correlation, read/write ratio, and table size.

14.11.8. Operator Classes Matter

Saying “there is a GIN index” is incomplete. An index access method uses operator classes to determine which operators are supported and how they map to the structure.

For JSONB and other extensible types, query operators and selected operator class directly affect index usability.

14.11.9. HOT Updates

Heap-Only Tuple (HOT) optimization can reduce the need for new index entries under specific conditions.

The broad idea is:

• indexed columns remain unchanged,
• suitable space exists on the same heap page.

When those conditions hold, index write amplification can be reduced.

HOT is not a guarantee that updates are cheap. Fill factor, page space, index count, and changed columns still matter.

14.11.10. Planner Estimates versus Actual Execution

EXPLAIN displays the planner's chosen plan and estimates.

EXPLAIN ANALYZE actually executes the statement and measures runtime behavior.

A large difference such as:

estimated rows = 100
actual rows    = 100000

can explain poor join order or scan choice.

Useful evidence includes estimated versus actual rows, loop counts, scan and join methods, sort spills, buffer hits/reads, and WAL production where relevant.

Because EXPLAIN ANALYZE executes the statement, it must be used carefully with data-modifying statements. Controlled transactions and rollback can be appropriate when the intent is diagnostic.

14.11.11. Statistics Are the Optimizer's Model of the Data

The planner does not rescan every table to understand distributions on every query. It relies on statistics for cardinality, value distribution, null fraction, distinct values, and selected cross-column relationships.

When data distribution changes and statistics become stale, correct SQL can receive a poor physical plan.

Performance diagnosis should therefore ask not only whether an index exists, but what the real cardinality is, what the optimizer estimated, and why those differ.

14.11.12. Partitioning Is Not Sharding

Table partitioning can divide one logical table into physical partitions and enable partition pruning and lifecycle management.

It is not automatic horizontal scaling, does not inherently distribute data across servers, and can introduce planning overhead when partition counts become excessive.

Partitioning and sharding solve different distribution problems.

14.11.13. JSONB Does Not Replace Relational Modeling

JSONB is useful for genuinely flexible or document-like attributes, but storing an entire domain inside one JSON column can weaken constraints, joins, type guarantees, migration clarity, and optimizer visibility.

Use it where the data shape and query pattern justify it.

14.11.14. Replication Does Not Repair the Data Model

Physical and logical replication can support availability, read scaling, disaster recovery, and distribution.

A replica does not automatically fix poor transaction semantics, inefficient queries, unsuitable indexing, or unclear data ownership.

The larger PostgreSQL lesson is to connect:

relational semantics
  ↓
transaction visibility
  ↓
optimizer and plan
  ↓
index / heap
  ↓
WAL / vacuum / storage

so database performance becomes a measurable systems problem rather than a reflex to “add an index.”

Unit 15: Concurrent Transactions, Locks, and Database Programming Boundaries

Transaction design is more than COMMIT and ROLLBACK. When transactions overlap, execution schedules, visibility, locking, and retry behavior must be considered together.

15.1. Schedules and Serializability

A schedule is the real interleaving of operations from concurrent transactions.

Two individually valid transactions can still conflict under an unsafe interleaving.

Serializable execution aims for an outcome equivalent to some serial ordering. A database does not need one global lock to provide this property; MVCC, predicate locking, or conflict detection can be used.

15.2. Pessimistic and Optimistic Concurrency

Pessimistic concurrency attempts to prevent conflicts while work is in progress.

Optimistic concurrency allows work to proceed and validates a version or predicate before writing:

read version = 7
compute
UPDATE ... WHERE id = ? AND version = 7

If no row is updated, the assumption was stale and the operation must be reconsidered.

Conflict frequency, transaction length, human interaction, and retry cost determine the appropriate strategy.

15.3. Lock Granularity and Deadlock

Locks can exist at database, table, page, row, or application-defined resource levels.

A deadlock can occur when two transactions acquire resources in opposite order:

T1: hold A -> wait B
T2: hold B -> wait A

Useful controls include stable resource ordering, short transactions, avoiding user/I/O waits inside transactions, and explicit retry handling for deadlock or serialization failure.

Two transactions may deadlock even when their individual SQL statements are correct if they acquire shared resources in different orders. One transaction locks record A then requests B while another locks B then requests A. A consistent resource-acquisition order across update paths reduces this class of cycle, though index-range locking and isolation semantics may introduce other conflicts. Database deadlock detection belongs to the normal failure model: retry must be bounded, performed at the transaction boundary, and safe with respect to any external side effects.

15.4. View versus Materialized View

A normal view is generally a stored query definition evaluated against current base data.

A materialized view stores query results and requires a refresh policy.

view:
  fresher semantics
  query cost at read time

materialized view:
  potentially cheaper reads
  refresh and staleness management

15.5. Stored Procedures, Functions, Triggers, and Cursors

These are server-side database programming tools but have different responsibilities.

Functions produce reusable values or relations; procedures support operation-oriented server logic; triggers execute automatically on database events; cursors provide controlled traversal of result sets.

Syntax is engine-specific. Oracle-specific detail belongs in PL/SQL Programming Fundamentals.

A trigger should not automatically replace a declarative constraint. Hidden side effects and ordering can increase maintenance cost.

15.6. Place Integrity at the Strongest Appropriate Layer

A practical ordering is:

type / NOT NULL / CHECK / PK / FK / UNIQUE
        ↓ when possible
declarative database constraint

cross-row transactional rule
        ↓
transaction + isolation/locking

mandatory event-driven behavior
        ↓ when justified
trigger / stored program

interaction-level validation
        ↓
application validation

Application validation improves usability but should not be the only integrity barrier when multiple writers exist.

15.7. Match Index Structure to Query Semantics

An index does not guarantee a fast query. Operator semantics, ordering, collation, selectivity, data distribution, join shape, and projected columns all matter.

Terms such as clustered and nonclustered are engine-specific physical concepts rather than universal relational properties.

15.8. Transaction Design Questions

Ask:

1. Which invariant is being protected?
2. Who can write the same data?
3. Which version must readers observe?
4. Are conflicts common or rare?
5. Can the transaction safely retry?
6. Could an external side effect run twice after retry?
7. Are lock order and lock duration controlled?

This connects ACID theory to practical concurrent data engineering.

15.9. Transaction States, Recoverability, and two-phase Locking

A transaction is not merely in a binary “started/committed” state. In the theoretical model it may be active, partially committed after its final statement but before durability is certain, committed, failed, or aborted. These states help reason about recovery and the point at which external effects may safely be considered final.

Conflict serializability is not the only schedule property relevant to recovery. If a transaction reads a value written by another transaction, commit ordering matters:

• recoverable schedule: a transaction does not commit before the transaction whose value it read has committed.
• cascadeless schedule: transactions read only committed values, reducing cascading rollback.
• strict schedule: data written by a transaction is not read or overwritten by other transactions until the writer completes, simplifying recovery further.

Two-Phase Locking (2PL) divides lock acquisition and release into two phases:

growing phase:   acquire new S/X locks; release none
shrinking phase: release locks; acquire no new locks

Shared (S) locks permit compatible readers; exclusive (X) locks restrict conflicting reads/writes. Basic 2PL targets serializability; strict 2PL retains write locks until transaction end and simplifies recovery; conservative 2PL attempts to acquire required locks up front to avoid deadlock at the cost of reduced concurrency.

2PL does not automatically eliminate deadlocks. Serializability, deadlock freedom, and high concurrency are separate properties.

Separate pool acquisition delay from lock waiting. A slow SQL response does not prove that the query spent the time consuming CPU. A request may wait in an application connection pool, then on another transaction's database lock, or on storage I/O. Collapsing those intervals into one “SQL latency” metric encourages incorrect remedies. Correlate application connection acquisition, active database execution, and wait events over consistent observation windows.

A larger pool does not reduce SQL operations per transaction or shorten the lock scope. It may instead admit more simultaneous contenders and increase lock waits. Investigate why locks remain held, whether remote I/O occurs inside transactions, and how the access path behaves before using a controlled capacity experiment to size the pool. The principle applies across PostgreSQL, Oracle, and MariaDB, although lock semantics and diagnostic interfaces differ by engine.

15.10. Multi-row Invariants under Snapshot Isolation

A transaction may observe a consistent snapshot without preserving a business invariant spanning multiple rows. Suppose a table contains two on-call operators and requires at least one to remain available. Two concurrent transactions each read both operators as available, then independently deactivate different rows. Because the writes touch different rows, row-level write conflicts may not occur, yet the invariant is violated. This is a write skew anomaly. The actual isolation guarantees must be established for the database engine in use.

Checking the count in an application query is insufficient to preserve the rule. Prefer a database constraint when the invariant can be represented directly. Otherwise, design a locking, serializable execution, or atomic-update strategy that covers every participant in the decision. Its behavior under predicate reads, phantom rows, and lock granularity must be tested on the target engine. A simple application sequence of SELECT followed by UPDATE is not a concurrency guarantee.

A reproducible test uses two connections and coordinated barriers so that both transactions act upon the same initial state. Vary the order of reads, writes, and commits; then inspect whether the committed database state still satisfies the invariant. Merely checking for exceptions is inadequate: both commits may succeed and produce a logically invalid outcome. An isolation-level label is not a substitute for this experiment.

15.11. MVCC, Isolation Anomalies, and Write Skew

Multiversion concurrency control (MVCC) enables reads over appropriate visible versions; it does not thereby eliminate all transaction anomalies. Under snapshot isolation, two transactions may read the same initial state and update different rows, each making a locally valid decision while violating a shared business invariant. Write skew illustrates this outcome without a direct same-row write conflict.

When an invariant spans rows or tables, correctness is not reducible to atomic modification of one row. Isolation level, predicate or range locking, available unique/check constraints, serializable execution, and application-level conflict detection must be considered together. A condition observed by SELECT cannot safely be assumed to remain true during a later UPDATE without explicit protection or revalidation.

Unit 16: Distributed and Extended Database Approaches

16.1. Distributed Databases: Location, Fragmentation, and Replication

A distributed database stores logically related data at multiple sites/nodes while attempting to present a coherent database abstraction. The design problem is not simply placing tables on different machines; it is deciding which aspects of distribution are hidden from applications.

Important transparency dimensions include:

• location transparency: applications need not know which node stores the data,
• fragmentation transparency: applications need not know how a logical relation is physically split,
• replication transparency: multiple physical copies are managed as one logical value set,
• naming transparency: distribution does not force location-specific object naming into the application contract.

Horizontal fragmentation divides rows by predicates. Vertical fragmentation divides attributes while retaining enough key information for reconstruction; hybrid forms combine both. A sound fragmentation design must permit reconstruction without accidental loss or duplication of the logical relation.

Replication can improve read locality, availability, or redundancy, but writes must then be coordinated across copies. The resulting tradeoff is real:

read locality / availability ↑
            ↕
write coordination / consistency cost ↑

16.2. Distributed Query Processing

A distributed system adds communication cost and data locality to the CPU and storage costs already present on one node. The same logical query may require decisions about:

• where to apply filters,
• which columns/intermediate results cross the network,
• where a join executes,
• whether to ship a small input or repartition both inputs,
• whether partial aggregates can be computed locally and merged.

The objective is not merely to execute SQL, but to avoid moving large intermediate results unnecessarily. This is the distributed analogue of predicate and projection pushdown.

16.3. Publishing Events Consistently with a Transaction

Changing a database row and publishing an event to an external broker touch different durability domains. Committing the row before publishing leaves a crash window in which the event is lost; publishing first can announce a change that is subsequently rolled back. A database transaction does not automatically make an external network send atomic.

With a transactional outbox, the business change and a durable event record are written in one local transaction. After commit, a separate dispatcher reads pending events, publishes them, and records delivery progress. If it crashes after publishing but before recording that progress, it may publish the same event again. Consumers therefore need event-identity deduplication or idempotent handling of the associated side effect.

BEGIN -> update business data -> insert outbox event -> COMMIT
asynchronous dispatcher -> publish event -> record progress

The outbox guarantees that the business change and its intent to publish are stored atomically. It does not, on its own, guarantee exactly-once side effects at the message broker or consumer. This distinction defines the recovery and consistency boundary.

16.4. 2PL and 2PC Solve Different Problems

Despite similar names, the mechanisms have different purposes:

2PL — Two-Phase Locking
purpose: concurrency control / serializability

2PC — Two-Phase Commit
purpose: atomic commit across multiple participants

In Two-Phase Commit (2PC), a coordinator first asks participants to prepare/vote. If every participant durably records that it is ready to commit, the second phase issues a global COMMIT; otherwise the outcome is ABORT.

phase 1: PREPARE / VOTE
phase 2: COMMIT or ABORT

2PC provides a distributed atomic-commit decision; it is not a concurrency-control or high-availability mechanism. Coordinator/participant failures and uncertain decision windows are important operational costs of the protocol.

16.5. Active Databases and the ECA Model

An active database can execute rules automatically when defined events occur, rather than reacting only to explicit user statements. The classic model is:

Event → Condition → Action

SQL triggers are a practical expression of this ECA idea. Automatic behavior can also create cascading triggers, ordering dependencies, recursive effects, and hidden side effects. Simple integrity rules should therefore prefer declarative constraints when possible; truly event-driven mandatory behavior may justify controlled trigger/stored-program logic.

16.6. Deductive Databases

Deductive databases combine stored facts with rules that derive new results. Recursive queries and rule dependencies are central ideas in this model family.

Datalog is a common theoretical vehicle for explaining this approach. The purpose here is not to reteach logic programming, but to distinguish persisted tuples from results derived by rules.

16.7. Object-oriented Databases and Their Modern Boundary

The object-oriented database approach attempts to bring object identity, encapsulation, inheritance, complex objects, and persistent object state directly into the database model. The model remains historically and conceptually important, but several modern terms must not be conflated:

object-oriented database ≠ ORM
object-oriented database ≠ object-relational DBMS
object-oriented database ≠ document database

An ORM maps programming-language objects to a relational schema; it does not turn that schema into an object-oriented database. Object-relational systems instead extend a relational core with richer/user-defined types.

16.8. Data Warehouses and the OLTP/OLAP Distinction

Operational databases typically focus on current business transactions with selective access and transactional consistency. A data warehouse integrates historical data for analytical workloads.

OLTP: short, selective, frequent transactions
OLAP: broad scans, aggregates, historical analysis

Fact/dimension modeling and star schemas simplify common analytical access patterns. ETL/ELT describes extracting source data, transforming it as required, and loading/organizing it for analysis. Separating operational and analytical workloads avoids assuming that one physical design is ideal for both.

16.9. The Role of Data Mining in Database Systems

Data mining seeks patterns, relationships, or predictive structure in large datasets. In a DBMS course, the critical issue is less the internal details of learning algorithms than the data pipeline:

source data
   ↓
cleaning / integration
   ↓
analytical dataset / warehouse
   ↓
mining / statistical learning
   ↓
validation and interpretation

Types, integrity, provenance, reproducible queries, and consistent snapshots provided by the data layer directly affect the reliability of analytical conclusions.

16.10. Web, Multimedia, Mobile, Spatial Databases, and Digital Libraries

These application families remain useful examples of how data-management requirements change with data type and access pattern:

• Web database access: client → application/API → connection pool → DB; identity, authorization, parameterized access, and transaction boundaries belong at controlled application/database interfaces.
• Multimedia data: distinguish large binary objects from searchable metadata; choose between in-database BLOB storage and external object storage based on size, transaction, and access requirements.
• Mobile/offline data: intermittent connectivity, local copies, synchronization, stale state, and conflict resolution.
• Spatial/GIS data: geometry types, spatial predicates, coordinate/reference semantics, and index structures beyond ordinary B-trees.
• Digital libraries: document identity, bibliographic/semantic metadata, full-text retrieval, version/provenance, and long-term preservation.

These families do not replace relational theory. They demonstrate how the same data-management principles must be adapted to different data types, consistency expectations, and access patterns.

16.11. Choosing Among the Relational Model, Property Graphs, and RDF

A "graph database" is not a single data model. The relational model, property graphs, and RDF represent relationships under different identity, semantic, and query assumptions.

The relational model organizes tuples into relations and uses keys and constraints for integrity. Relationships are expressed through keys and joins. The presence of many relationships is not by itself a reason to leave a relational DBMS; bounded, well-indexed join paths can be extremely efficient.

A property graph treats vertices and edges as first-class records, and both can carry properties. Variable-length traversal, adjacency, and path queries are central:

(node)-[edge {properties}]->(node)

RDF represents information as subject-predicate-object triples and uses globally nameable IRIs:

subject  predicate  object

Its distinction is not merely that it is a graph. RDF is designed for semantic integration across sources, shared vocabularies, and a standards ecosystem that can include entailment regimes. A local property-graph identifier and an RDF IRI are therefore different identity contracts.

Start model selection from access patterns:

high transactionality + strong tabular integrity + bounded joins -> relational model
variable-length paths + edge properties + traversal              -> property graph
cross-source semantic integration + shared vocabularies           -> RDF

Moving to a graph model does not automatically improve performance. If traversal depth is bounded, data is naturally tabular, and transaction/constraint semantics dominate, another database model can add operational cost without reducing complexity. Conversely, repeated recursive joins for path discovery can indicate that the relational representation is hiding the natural structure of the problem.

In multi-source systems, provenance is as important as connectivity. source, valid_time, observed_at, version, and confidence metadata answer a different question from the edge itself: not only whether a relation exists, but according to which evidence and during which interval it is valid.

16.12. Co-designing Correctness and Performance in Data Systems

In data-intensive systems, reliability, scalability, and maintainability are different consequences of the same design decisions. A DBMS query is not explained by SQL text alone; physical storage and access paths, index selection, data encoding, partition pruning, replication lag, and transaction boundaries together determine why an optimizer choice is fast, slow, or fragile under failure conditions. Derived data should have an explicit source of truth, reproducibility path, and latency budget.

• Measure index read gains together with write-amplification cost.
• Tie replication lag explicitly to consistency expectations.
• Define the source from which batch- and stream-derived data can be reproduced.

Define the freshness contract for replica reads. A successful commit on the primary does not guarantee immediate visibility on an asynchronously replicated secondary. Workflows requiring read-your-writes semantics need primary routing for the subsequent read, replication-position tracking, or an application-level freshness check. Additional replica read capacity matters only within an explicitly accepted staleness bound.

Distributed query design must consider correctness as well as transport cost. A non-idempotent retry after a network failure may apply an update twice. Publishing an external event is not automatically part of the local database commit. Specify consistency expectations, latency targets, failure modes, and retry behavior as one coherent protocol.

16.13. Stable Ordering and Snapshot Consistency in Paginated Queries

Pagination using an order such as ORDER BY timestamp without tie-breaking can duplicate or skip records across pages. A stable order includes an immutable unique key after equal timestamps. Offset pagination does not preserve a fixed result slice automatically when new rows are inserted before the current offset. Keyset pagination advances from the last observed ordering key, but does not by itself guarantee a database snapshot under concurrent updates.

A claim that results are complete requires a defined visibility point, filter set, upper limit and continuation state. If multiple pages must reflect the same database version, a suitable transaction isolation level or explicit snapshot mechanism should be evaluated. Long-running transactions have resource costs that must also be measured.

A synthetic example reads ten rows sharing the same timestamp over three fixed-size pages. The invariant is no duplicate identifier and exact equality with the reference result under a fixed snapshot. With concurrent writes, expected behavior must instead follow the explicitly chosen consistency contract.

16.14. Source Completeness and Temporal Scope of Analytic Results

Receiving N rows does not establish that all matching data consist of exactly N rows. Authorization filters, time ranges, row caps and failed page requests change the observed population. An analytic result should logically separate its value from observed row count, truncation status, temporal bounds and data version.

When centrality, ratios or percentiles are computed over sampled or LIMIT-truncated data, the denominator and scope must be explicit. Presenting such results as certain is a data-engineering defect as well as a UI defect. Communicating incomplete coverage is often safer and less expensive than silently launching an unbounded query.

16.15. Progress Markers and Replayable Backfills

A large backfill must coordinate transaction boundaries, pagination keys, and restart behavior. Using OFFSET alone can introduce duplicates or omissions during concurrent inserts and deletes; progressing over a stable ordering key and key ranges is often more predictable. Updates to the source row or relocation of a partition key require separate consideration.

The progress marker must remain consistent with commit of the processed range. Idempotent target writes make retrying a failed range safe; without idempotence, replay can itself corrupt data. Work-rate limits, short transactions, row-lock waits, and partial-failure indicators help prevent a long-running backfill from destabilizing transactional workloads.

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/

• Brendan Gregg. Systems Performance: Enterprise and the Cloud. Pearson Education, 2014.

• 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

• Martin Kleppmann. Designing Data-Intensive Applications. 1st ed. O’Reilly Media, 2016.

• 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

• PostgreSQL Global Development Group. PostgreSQL 18 Documentation — Concurrency Control (MVCC). https://www.postgresql.org/docs/18/mvcc-intro.html

• PostgreSQL Global Development Group. PostgreSQL 18 Documentation — EXPLAIN. https://www.postgresql.org/docs/18/sql-explain.html

• PostgreSQL Global Development Group. PostgreSQL 18 Documentation — Routine Vacuuming. https://www.postgresql.org/docs/18/routine-vacuuming.html

• PostgreSQL Global Development Group. PostgreSQL 18 Documentation — WAL Internals. https://www.postgresql.org/docs/18/wal-internals.html

• PostgreSQL Global Development Group. PostgreSQL 18 Documentation: Concurrency Control. https://www.postgresql.org/docs/18/mvcc.html

• 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

• Ünal Yarımağan. Veri Tabanı Sistemleri, 2nd ed. Akademi Yayıncılık, 2010.

• World Wide Web Consortium. RDF 1.2 Concepts and Abstract Data Model. Candidate Recommendation Snapshot, 7 April 2026. https://www.w3.org/TR/rdf12-concepts/