The Whole Data System in One Look

1.1.1 PLAIN — in simple words#

1. Selling an item and recording the sale are different actions. A notebook can leave the shelf without the computer learning about it. A computer can also contain a sale that never actually happened. A useful data system connects the work people do with records that other people can interpret and check.

2. For our order, Mira needs answers to several separate questions: What was requested? What price was agreed? What was handed over? What stock should remain? A later payment record may answer another question, but the presence of an order alone does not answer it.

3. The first job is therefore not “put everything in a database.” It is to decide which facts the shop needs, what each fact means, and how an action will produce the right record.

1.1.2 PLAIN — a picture in your head#

1. Imagine a counter with four trays. One holds requests, one holds checked order slips, one holds stock movements and one holds reports. Moving a slip into the order tray does not magically move a physical notebook or update every other tray.

2. The trays make responsibilities visible. They do not prescribe four separate databases. Where the comparison stops: software may store several kinds of records together and change some of them in one controlled operation. The important separation is meaning, not furniture.

1.1.3 PLAIN — a worked example#

1. First multiply 2 by 75.50 to obtain 151.00. Then multiply 1 by 20.00 to obtain 20.00. Add the two line amounts: 151.00 + 20.00 = 171.00.

2. If the notebook count starts at 12 and both notebooks are handed over, the expected count becomes 10. If the pen count starts at 25 and one pen is handed over, it becomes 24. These are consequences of the stated example, not claims that every inventory system changes stock at the same business moment.

3. Notice three different counts: one order, two order lines and three physical units. They are all correct. They answer different questions.

1.1.4 PLAIN — what is really happening inside#

1. Our proposed application receives the chosen item identifiers and quantities. It checks them against the shop’s rules, determines the agreed prices, and prepares the records that should result. A storage component accepts or rejects the changes it is asked to make. The application then presents an outcome to Dev.

2. Each arrow can introduce a mistake. The wrong item can be selected. A correct item can be paired with an outdated price. A response can disappear after the records have changed. A report can add the wrong field even when the underlying order is correct.

3. A system diagram is valuable because it gives those possible mistakes a location. “The computer is wrong” becomes “the price selected before saving was wrong” or “the report counted lines rather than orders.” Those are problems somebody can investigate.

Figure 1.1. The teaching system: actions become records; questions produce derived answers. The diagram is a logical model, not a deployment design.

1.1.5 TECHNICAL — the engineer’s version#

1. We will call the screen and workflow code the application, the accepted stored facts the database, and the software responsible for database operations the database management system, or DBMS. An application can use an embedded engine inside its process or communicate with a server. Python’s SQLite interface is an example of access to an embedded SQL engine. [S20]

2. The toy order has two related structures. An order header identifies the overall order. Its lines identify the individual pricing decisions within that order. The tuple (order_id, line_no) will identify a line in our examples; the same product could legitimately appear on two different lines.

3. Our initial invariant is that the order total equals the sum of quantity multiplied by agreed unit price across its lines. An invariant is a rule we intend every accepted state to satisfy. Naming the rule does not enforce it. Later chapters will examine how to enforce it, including when requests overlap.

4. The boundary matters: a database transaction can group its participating database changes. It does not, merely by existing, turn handing over goods, sending a message and charging an external service into the same atomic action. [S01]

1.1.6 WORDS — remember these#

1. Application: the program people use; technically, the software that implements a workflow and requests operations from other components.

2. Database: the organised records; technically, a managed collection of data interpreted under a model and its rules.

3. DBMS: the record-management machinery; technically, the engine providing database operations and its documented guarantees.

4. Invariant: a rule that must keep holding; technically, a predicate required of accepted states or permitted transitions.

1.2.1 PLAIN — in simple words#

1. A record is a statement we have preserved. It may describe an observation, a request, an estimate, a plan or a decision. It is not automatically proof that the described event occurred.

2. If Dev types “12 notebooks,” the system can preserve that statement perfectly while there are actually 11 notebooks on the shelf. Perfect storage and accurate observation are different achievements.

3. This is not a reason to distrust every record. It is a reason to ask what evidence supports the particular claim you intend to make from it.

1.2.2 PLAIN — a picture in your head#

1. A photograph of a shelf is not the shelf. It captures one view at one time. It may hide an item behind another, and it cannot show what happened after the photograph was taken.

2. A stock record has a similar boundary. Where the comparison stops: records can describe things no photograph captures well, such as a reservation or an intended delivery. Some records create an administrative state rather than merely observe a physical scene.

1.2.3 PLAIN — a worked example#

1. At 09:00 Dev counts 12 notebooks and records the count. At 09:30 two notebooks are sold under O-1042. At 10:00 a new count finds only nine.

2. The expected count is 12 - 2 = 10. The observed count is nine. The discrepancy is one notebook. The arithmetic identifies a disagreement; it does not identify the cause.

3. Possible explanations include a counting mistake, an unrecorded sale, damage or an incorrectly recorded starting count. In the story we do not yet know which explanation is true. Changing the computer’s count to nine may reconcile the current number, but it does not establish what happened.

1.2.4 PLAIN — what is really happening inside#

1. The system receives values through a boundary: a form, a scanner, a file or another program. It can check some properties of those values. For example, it can require a count to be a non-negative whole number. That rule rejects “twelve-ish” but still accepts an incorrect count of 12.

2. We therefore separate validation from verification against evidence. Validation asks whether a record obeys declared rules. Verification asks whether the claim is supported by an appropriate observation or other evidence. Our usage is practical; a particular discipline may use these terms more formally.

3. W3C’s data-publication guidance treats quality information and provenance as things that should accompany data, not as properties readers should infer merely from a file being available. [S06]

1.2.5 TECHNICAL — the engineer’s version#

1. For the shop, distinguish three predicates. well_formed(record) means the record can be interpreted under the format. valid(record) means it satisfies the declared domain rules. supported(record, evidence) means the available evidence supports a stated claim. These are teaching predicates, not functions supplied automatically by a database.

2. An integer stock count may satisfy a type rule and a range rule while failing the third predicate. Conversely, a physically observed count can arrive in a malformed file and fail the first predicate. Different defects need different remedies.

3. In PostgreSQL, a check constraint can enforce an expression over the row being checked. That mechanism does not independently observe a shelf. The difference between an enforceable row rule and an external fact is essential to the design review. [S02]

4. A useful discrepancy record would identify the affected item, the expected quantity, the observed quantity, the relevant time, the source of the observation and the disposition. Recording “investigation pending” is more accurate than inventing a cause to make the record look complete.

1.2.6 WORDS — remember these#

1. Observation: something someone or something noticed; technically, an acquisition of information under a stated method and context.

2. Validation: checking the stated rules; here, testing whether a representation meets declared structural or domain requirements.

3. Evidence: what supports a claim; technically, information whose relevance and limitations must be assessed for that claim.

4. Reconciliation: comparing related accounts of the same work; technically, identifying and resolving or explicitly retaining discrepancies under a defined procedure.

1.3.1 PLAIN — in simple words#

1. A file is a place where a program can keep a sequence of bytes. A spreadsheet file can hold a useful small dataset. A database engine provides a different level of service: programs ask it to manage records according to its rules rather than each inventing their own way to edit the underlying storage.

2. The distinction is not “files are bad, databases are good.” A database itself uses storage representations. The question is who coordinates the work and which guarantees that component supplies.

3. For Mira alone, a carefully maintained file may be enough. Once several tills change the same stock at once, the coordination requirement becomes much more demanding.

1.3.2 PLAIN — a picture in your head#

1. A shared notebook lets people read and write. A record office adds an agreed process for accepting forms, identifying records, answering requests and controlling changes. The office represents the service provided around the records.

2. Where the comparison stops: a database engine is not a clerk who understands your intentions. It applies the rules and operations you have actually defined. It can execute a mistaken instruction correctly.

1.3.3 PLAIN — a worked example#

1. Suppose two tills both read a file saying there are 10 notebooks. Till A sells two and prepares a replacement count of eight. Till B sells one and prepares a replacement count of nine. If A saves eight and B later overwrites it with nine, the final file says nine. The expected count is seven.

2. The failure is not that subtraction stopped working. Both tills subtracted from the same old starting point and one result replaced the other. Putting the same unprotected pattern behind a database connection is not, on its own, a complete correction.

3. We will later write the operation as a controlled change to the current value, with conditions and a concurrency policy. For now, recognising the overlapping sequence is more important than memorising a particular command.

1.3.4 PLAIN — what is really happening inside#

1. A request to a database engine is expressed through an interface. The engine interprets the request, checks applicable rules, locates or changes records, and returns a result or an error. The physical arrangement can differ substantially between engines.

2. A CSV file describes a tabular exchange format, not a shared-editing protocol. Its commas and quotation rules do not specify how two tills coordinate updates or how an interrupted operation recovers. RFC 4180 documents common CSV conventions as an informational RFC. [S08]

3. Similarly, JSON provides a way to represent structured values. A JSON object containing an order does not by itself specify authority, concurrency, retention or whether an amount has been paid. Those are contracts above the representation. [S07]

1.3.5 TECHNICAL — the engineer’s version#

1. Separate four layers in a design: the data model, the operations, the engine and the storage format. The model says what entities and relationships mean. Operations specify the permitted questions and changes. The engine implements those operations. The format represents data at a storage or exchange boundary.

2. SQL means Structured Query Language. It is a language used by many database engines, not the name of one engine. Product-specific behaviour still matters. SQLite, for example, uses dynamic typing in ordinary tables, and its documented type-affinity rules differ from the rigid assumptions readers sometimes bring from other products. [S21]

3. The foundations laboratory uses Python and an in-memory SQLite database. It demonstrates stated examples only. PostgreSQL references explain PostgreSQL behaviour; running the SQLite laboratory is not a PostgreSQL compatibility test.

4. That separation is a habit worth developing early: name the product, the relevant configuration, the operation and the observed result before generalising.

1.3.6 WORDS — remember these#

1. File format: how bytes are arranged; technically, the syntax and interpretation rules for a representation.

2. Interface: how one component asks another to do something; technically, the exposed operations and their input, output and error contracts.

3. SQL: a language for working with data; technically, Structured Query Language and its standardised and product-specific features.

4. Concurrency: work that overlaps in time; technically, the execution of operations whose interleaving can affect their observed results.

1.4.1 PLAIN — in simple words#

1. A report is an answer calculated from records under a definition. It can be wrong even when every source record was stored correctly. The mistake may be the question, the selection of records or the calculation.

2. “How busy was the shop?” is not yet a precise question. It could mean the number of orders, the number of people served, the number of items sold or the total recorded order value. A single large order makes those measures behave differently.

3. The useful habit is to finish the sentence: “This number counts or measures , from , during , excluding .”

1.4.2 PLAIN — a picture in your head#

1. Think of a recipe made from measured ingredients. The ingredients can be correct while the recipe is wrong for the dish you intended. A spoonful of sugar and a spoonful of salt are both real measurements; swapping their role ruins the result.

2. Where the comparison stops: a data calculation often leaves its inputs unchanged. Several different reports can legitimately be made from the same records. The issue is whether each one matches its stated definition.

1.4.3 PLAIN — a worked example#

1. Our second fictional order, O-1043, contains one notebook at INR 75.50 and two pens at INR 20.00 each. Its total is 75.50 + 40.00 = INR 115.50.

2. Across O-1042 and O-1043, we have two orders, four order lines and six physical units. Their total recorded order value is 171.00 + 115.50 = INR 286.50. The average recorded value per order is 286.50 / 2 = INR 143.25.

3. Dividing by four would produce INR 71.625 per line, which is a different measure. None of these calculations establishes recognised revenue, settled receipts or profit. We have not introduced the records or definitions needed for those claims.

1.4.4 PLAIN — what is really happening inside#

1. A report chooses records, combines related records when necessary, performs calculations and formats the result. Each step needs a defined meaning. “Today” requires a time boundary. “Completed” requires a business status. “Customer” requires an identity policy.

2. A dashboard may store a previously calculated answer so it can show it quickly. In that case the displayed value also has an “as of” boundary. The presence of a current-looking screen does not tell you when its underlying calculation was last refreshed.

3. For our teaching system, every derived report will have a name, an input definition, an inclusion rule and a calculation. Where a value is cached or refreshed periodically, the report will also state that timing policy.

1.4.5 TECHNICAL — the engineer’s version#

1. A query is a specified request for data or a calculation over it. A projection, in the broader application-design sense used here, is a derived representation built for a particular use. Do not confuse that usage with relational algebra’s narrower projection operator, which selects attributes.

2. The cardinality of an input is its number of rows. A join can change that cardinality. A line-level dataset is not an order-level dataset simply because every line carries an order identifier. The case study’s four rows require two distinct order identifiers to answer the order-count question.

3. Ordering is also part of the contract. PostgreSQL does not promise a particular output order unless sorting is requested. A query that happens to return O-1042 before O-1043 in a small demonstration does not establish a permanent ordering rule. [S03]

4. A report specification should distinguish its logical definition from the plan used to execute it. We will defer optimisation until the question itself is stable.

1.4.6 WORDS — remember these#

1. Query: a precise question; technically, an operation expressing a requested result over data.

2. Derived data: an answer made from other data; technically, a representation produced by a transformation or calculation over inputs.

3. Grain: what one record stands for; technically, the observation or business unit represented by each row.

4. Cardinality: how many; here, the number of rows in a dataset or intermediate result.

1.5.1 PLAIN — in simple words#

1. “Saved” can mean several things. The screen may have accepted your typing. The application may have sent a request. The database may have accepted the change. The information may also have reached storage expected to survive a particular failure.

2. Those are different milestones. A useful system tells the user which outcome it knows rather than using one reassuring word for all of them.

3. A timeout is especially important. It means an expected response did not arrive within the allowed time. From that fact alone, the caller cannot always know whether the change happened.

1.5.2 PLAIN — a picture in your head#

1. You post a signed form to an office. The office records it, then sends you a receipt. The receipt is lost on the way back. You have no receipt, but the office may already have accepted the form.

2. Where the comparison stops: software can attach a stable request identifier and expose a status lookup. A well-designed retry protocol can use those records to avoid treating every resend as a brand-new request. The analogy describes uncertainty, not an unavoidable absence of solutions.

1.5.3 PLAIN — a worked example#

1. Imagine a controlled database operation that creates O-1042 and records its associated stock reduction. If an error interrupts the operation between those changes, our intended outcome is either that all participating changes are accepted or that none are.

2. Now consider a different sequence: the database accepts the complete operation, but the network response to Dev is lost. Dev sees a timeout. Retrying as a completely new order could record the sale twice. Immediately claiming that no order exists could also be wrong.

3. The next safe question is about the original operation’s identity and status. A useful request identifier makes that question possible; its existence alone still does not prove that duplicate handling was implemented correctly.

1.5.4 PLAIN — what is really happening inside#

1. A transaction groups participating database changes into a controlled unit. In the model used by PostgreSQL’s transaction tutorial, a successful commit accepts the unit, while rollback cancels the transaction’s changes. [S01]

2. That solves a specific problem. It does not by itself explain when a response reaches a screen, how a notification is delivered, or how an already-completed transaction should be reversed as a business matter.

3. Storage promises also have conditions. A database may allow a mode that acknowledges a transaction before the corresponding recovery information has been durably written. PostgreSQL documents that an asynchronous commit can therefore be lost after a crash even though it was acknowledged. [S04]

1.5.5 TECHNICAL — the engineer’s version#

1. Keep four questions separate. Atomicity: which changes take effect together? Isolation: how can overlapping operations observe or affect one another? Durability: which accepted changes survive which failures under which configuration? Knowledge at the caller: what has the caller actually learned from the messages it received?

2. These questions interact, but none is a replacement for the others. A local rollback demonstration is evidence about that demonstration. It is not proof of power-loss durability, multi-machine failover, or correctness under concurrent requests.

3. Our laboratory deliberately creates a table in memory, makes a temporary change and rolls it back. It verifies that the demonstrated rollback restores the queried row count. Because the database is in memory, the laboratory makes no claim that its data survives process exit or a machine failure.

4. Later chapters will add explicit request identity, bounded retries and external-effect handling. The important lesson now is to put the guarantee next to its boundary.

1.5.6 WORDS — remember these#

1. Transaction: a controlled unit of database work; technically, operations managed together under an engine’s atomicity and related semantics.

2. Commit: accepting the transaction; technically, the transaction-completion operation under the engine’s documented configuration.

3. Rollback: cancelling uncommitted work; technically, discarding the transaction changes within the requested rollback scope.

4. Durability: surviving specified failures; technically, the persistence guarantee for accepted changes under stated assumptions.

1.6.1 PLAIN — in simple words#

1. The rest of the book expands this one shop without treating scale as magic. We first learn what records mean. Then we organise them, ask questions, control changes, survive failures and distribute work. Finally we build useful analysis and operate the system responsibly.

2. This order matters. Making an ambiguous record travel faster does not make it clearer. Copying an incorrect calculation to more machines does not make it more correct.

1.6.2 PLAIN — a picture in your head#

1. Think of learning to run a workshop. Before buying faster machines, you learn what you are making, how parts fit, how to measure the result and what makes it safe to use.

2. Where the comparison stops: data systems can be reorganised without moving physical walls, but changes still have compatibility and recovery costs. “Software can change” does not mean “software can change without consequences.”

1.6.3 PLAIN — a worked example#

1. For O-1042, the early chapters ask whether the quantity means units or boxes. The database chapters ask where the order and its lines belong. The query chapters ask how to find the order quickly. The transaction chapters ask what happens when a request repeats.

2. The recovery chapters ask how the order can be restored after a failure. The distributed chapters ask which copy a reader is seeing. The analytical chapters ask what the order contributes to a report. The operational chapters ask who may inspect it and what happens when it is no longer needed.

3. It is the same order throughout. New machinery earns its place by answering a new question.

1.6.4 PLAIN — what is really happening inside#

1. Each major section will use the same six reading blocks. First comes the idea in ordinary words. Then comes a mental picture, a worked example and a closer account of the mechanism. The technical block adds formal names, implementation details and limits. The final block connects new terms to the explanation you have already read.

2. You can read the plain blocks first and return for the technical blocks. You should not have to invent missing definitions to make that route work.

1.6.5 TECHNICAL — the engineer’s version#

1. The planned sequence has eight parts and 52 chapters. Parts A and B establish semantics and relational foundations. Parts C, D and E develop query execution, concurrency and storage. Parts F, G and H address distribution, analysis and operations.

2. Claims will be labelled by their status: a definition used in the book, a rule selected for the fictional case study, a documented product behaviour, or an observed result from a specified experiment. Those categories are not interchangeable.

3. This complete edition contains all fifty-two chapters. The companion-lab guide distinguishes executable exercises from conceptual discussions. A chapter's presence does not mean that every system it describes has been implemented, simulated or independently reviewed. Consult the recorded test scope before making a stronger claim.

1.6.6 WORDS — remember these#

1. Model: a deliberately limited representation; technically, a set of concepts and rules chosen to answer particular questions.

2. Contract: what participants can rely on; technically, specified inputs, outputs, invariants, errors and relevant guarantees.

3. Implementation detail: how a particular system works; technically, behaviour that must not be assumed to apply to every conforming or competing system.

4. Reproducible example: an example another reader can check; technically, one supplied with sufficient inputs, procedure and environment information to repeat its stated observation.