IB COMPUTER SCIENCE | A3

DATABASES REVISION

Connect relational design, normalisation and SQL with labelled HL views, transactions, analytics and distributed databases. Use worked summaries and select cards for your revision list.

Selections are retained, including cards hidden by filters. All selected cards remain in your revision list.

YOUR A3 REVISION ROUTE

Revise the topic as a connected process: model the facts, define their rules, reduce unnecessary duplication, query and change the records, then choose analytical and distributed approaches where appropriate.

Common SL/HL material is A3.1, A3.2 and A3.3.1–3. HL additions are A3.3.4–6 and all of A3.4. The level switch hides labelled HL notes and cards; it retains any cards you already selected for your revision list.

Use the worked examples actively: cover a result, reconstruct the tables or SQL, then check the explanation. The separate Flashcards tab contains 216 cards, including 132 common cards and 84 HL cards. Your selections last while the page remains open.

A3.1.1 | RELATIONAL STRUCTURE AND KEYS

A relational database organises data in related tables. A row represents one instance; a column represents an attribute. A primary key identifies a row uniquely without NULL, while a foreign key declares a reference to an appropriate key. A composite key combines attributes into one identifying combination.

In Student, Club and Enrolment, store the student’s details once and connect each membership through IDs. One StudentID may appear in several Enrolment rows, because the student can join several clubs. The pair (StudentID, ClubID) can identify one membership if the rule is one membership per pair.

Repeated reference IDs are not automatically harmful redundancy. Repeating a student’s email in every membership creates duplicated descriptive facts that can disagree; repeating the StudentID records distinct relationships.

A3.1.1 | BENEFITS, LIMITATIONS AND CONDITIONS

Feature or benefit Why it helps Limit to remember
Constraints and transactions Enforce defined rules and reliable related changes. Correct business logic and configuration still matter.
Shared consistent facts Reduces contradictory copies and update anomalies. A poor relational design can still duplicate data.
SQL retrieval Joins and filtering answer related questions. Indexes and query design affect performance.
Concurrency and security features Control conflicting work and access. They must be chosen and configured appropriately.
Scalability and ecosystem Mature tools and support can aid growth and maintenance. Large, distributed or unusual workloads still create trade-offs.

Limitations can include design complexity, schema changes, object-relational mismatch, awkward hierarchical or unstructured data and difficult scaling for some big-data workloads. Relational does not mean incapable of scale or unable to store any JSON; describe a workload-dependent limitation rather than an absolute ban.

A constraint cannot prove a valid-looking email belongs to the intended person. Data integrity rules, input validation, verification and authorisation have complementary roles.

A3.2.1 | CONCEPTUAL, LOGICAL AND PHYSICAL SCHEMAS

Level Focus Example
Conceptual Business entities, relationships and rules. Students join clubs through memberships.
Logical Data structures, attributes, keys and constraints in the chosen model. Student, Club and Enrolment tables with their key roles.
Physical Implementation and storage/access details. Indexes, partition placement and DBMS-specific storage choices.

A schema describes organisation and rules, not merely the current sample rows. The three levels connect requirements to implementation while keeping their purposes distinct. A physical change need not change the conceptual meaning, but dependent structures must still be managed.

A3.2.2 | ERDS, CARDINALITY AND MODALITY

An ERD represents entity types and their relationships. Cardinality describes the maximum multiplicity, such as one or many; modality describes minimum participation, often zero or one. Read each direction separately.

A student may have zero or many enrolments, while each enrolment must refer to exactly one student. A club may have zero or many enrolments, while each enrolment must refer to exactly one club. An empty club is therefore allowed without permitting an enrolment with no club.

Resolve the Student–Club many-to-many relationship through Enrolment. Store membership-specific attributes such as JoinedDate in that linking entity, because they describe the association rather than the student or club alone. State assumptions before choosing minimums and maximums.

WORKED CHECK | MAP RELATIONSHIPS INTO TABLES

Table Selected key Reference / rule
Student(StudentID, StudentName) StudentID One student per ID.
Club(ClubID, ClubName) ClubID One club per ID.
Enrolment(StudentID, ClubID, JoinedDate) (StudentID, ClubID) Each part is also an FK to its parent table.

S01 can appear with C01 and C02, but a second S01,C01 pair violates this selected key. S99,C01 violates the reference if S99 has no parent student. The rules detect different problems.

Putting a foreign key on the many side permits multiple child references. A suitable UNIQUE rule on that reference can enforce at-most-one participation in an appropriate one-to-one design; minimum participation needs separate reasoning.

A3.2.3 | CHOOSE TYPES BY MEANING

Data Suitable approach Reason
Count of items Integer Whole-number quantities.
Measured approximate value Appropriate floating-point type Range and approximate numeric representation.
Exact decimal amount Suitable fixed/exact decimal Avoid inappropriate approximation for exact decimal requirements.
Identifier 00127 Character/text when that format is meaningful Preserves leading zeros; it is not necessarily a quantity.
Date or timestamp Appropriate temporal type Supports meaningful date/time operations.
Yes/no flag Boolean where supported Represents the intended logical domain.

Types should be consistent across related key definitions and appropriate for comparisons. Text sorting can put 10 before 2; numeric sorting does not. Type declarations alone do not establish every range, format or truth rule. Exact names, precision and enforcement differ by DBMS.

A3.2.4 | TABLE DEFINITIONS AND INTEGRITY

CREATE TABLE Enrolment (
    StudentID VARCHAR(5) NOT NULL,
    ClubID VARCHAR(5) NOT NULL,
    JoinedDate DATE NOT NULL,
    PRIMARY KEY (StudentID, ClubID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
);

Construct a definition with names, types, the complete primary key, foreign-key targets and required constraints. Parent tables must have appropriate referenced definitions. One composite primary key is not two independent primary keys.

NOT NULL forbids NULL but is not universally a nonblank-text check. DEFAULT supplies a value under omission rules, not a universal replacement for explicit NULL. A positive-capacity CHECK does not count concurrent memberships. Test valid inserts as well as duplicate, missing-parent and missing-value cases.

A3.2.4 | COMBINED KEYS AND CONCATENATION

A composite key uses several columns together. Some materials call a combined key concatenated; if values are literally joined into one text value, their encoding must be unambiguous. The pairs (1,23) and (12,3) both become 123 under a naive string join.

A multi-column primary key compares the components as a combination, without needing a new joined string. A surrogate EnrolmentID is another possible identifier, but UNIQUE(StudentID,ClubID) with appropriate non-null/reference rules is still needed if duplicate membership pairs are forbidden.

A separator works only under suitable component/escaping rules. Naming a field ID or joining two strings does not by itself enforce uniqueness.

A3.2.5 | CANDIDATE KEYS AND DEPENDENCIES

A candidate key is a minimal combination that uniquely identifies a row: removing a component would lose that guarantee. The primary key is the chosen candidate key. A prime attribute belongs to at least one candidate key; a non-prime attribute belongs to none. “Non-key” can be ambiguous if it only means outside the selected primary key.

A functional dependency X → Y means each permitted X value determines one Y value. CustomerID → CustomerName is a rule of the model, not a claim that names are unique. The reverse dependency need not hold.

A partial-key dependency occurs when a non-prime attribute depends on a proper subset of a candidate key. A transitive dependency links an identifier to another attribute through an intervening dependency. Identify dependencies from business rules, not accidental uniqueness in three sample rows.

WORKED NORMALISATION | STATE THE ASSUMPTIONS

OrderID ProductID OrderDate CustomerID CustomerName ProductName UnitPrice Quantity
501 P1 2026-09-02 C1 Maya Kit 50 2
501 P2 2026-09-02 C1 Maya Glasses 8 1
502 P1 2026-09-03 C2 Leon Kit 50 1

Assume one line per product per order, so (OrderID,ProductID) is the candidate key. OrderID determines OrderDate and CustomerID; CustomerID determines CustomerName; ProductID determines ProductName and, for this simplified exercise, one fixed UnitPrice. The full pair determines Quantity.

In a real shop, the charged unit price may vary by sale or discount. It would then belong to the sale line or an appropriate history structure rather than being determined only by ProductID. Normalise the stated rules, not an unexamined generic template.

A3.2.5 | FIRST NORMAL FORM

For this model, 1NF means each field contains one value from its intended domain, with no repeating product groups or list of products inside one cell. Represent each order-product line as a separate row with a unique identifying combination.

The worked flat table is in 1NF, but order details repeat on every line and product details repeat across orders. Therefore 1NF alone does not eliminate insertion, update or deletion anomalies.

Atomicity here concerns field/domain values. Transaction atomicity concerns an all-or-nothing unit of work; these are different uses of the word.

A3.2.5–6 | SECOND NORMAL FORM

2NF table Attributes Dependency addressed
Order OrderID PK, OrderDate, CustomerID, CustomerName Order details depend on OrderID.
Product ProductID PK, ProductName, UnitPrice Product details depend on ProductID under the stated assumptions.
OrderLine OrderID PK/FK part, ProductID PK/FK part, Quantity Quantity depends on the full pair.

2NF requires 1NF and every non-prime attribute to depend on the whole of each candidate key, not a proper subset. Move the partial dependencies out of the composite-key relation.

After this step, CustomerName still depends on CustomerID inside Order, so a transitive dependency remains. Do not claim that reaching 2NF automatically gives 3NF.

A3.2.5–6 | THIRD NORMAL FORM

3NF table Attributes and references
Customer CustomerID PK, CustomerName
Order OrderID PK, OrderDate, CustomerID FK → Customer
Product ProductID PK, ProductName, UnitPrice
OrderLine OrderID PK/FK → Order; ProductID PK/FK → Product; Quantity

Move CustomerName into Customer, because OrderID determines CustomerID and CustomerID determines CustomerName. The Order table keeps the reference, not another independent copy of the customer’s name.

In the straightforward design, non-key descriptive attributes depend on the key rather than another non-key determinant. More formally, 3NF requires every non-trivial dependency X → A to have X as a superkey or A as a prime attribute. This formal condition handles cases the simplified mnemonic misses.

Preserve the necessary dependencies and relationships when decomposing. Splitting without meaningful keys can lose the ability to reconstruct the facts correctly.

A3.2.5–6 | ANOMALIES, MULTI-VALUED FACTS AND SCENARIOS

An update anomaly arises when repeated facts are changed inconsistently. An insertion anomaly prevents recording a fact without an unrelated fact. A deletion anomaly removes the only copy of a useful fact when another record is deleted. Separating facts according to dependencies can reduce these problems.

If a person independently has several skills and several languages, combining every skill-language pair repeats independent multi-valued facts. These concerns can require design beyond a simple 3NF explanation; do not claim 3NF removes every possible multi-valued dependency.

For a library use Book/Copy/Borrower/Loan as requirements demand; for a hospital distinguish Patient, Clinician and Appointment; for inventory distinguish Item and Movement. The grain, keys, cardinalities and dependencies determine the tables. A plausible table name is not a complete construction.

A3.2.7 | EVALUATE DENORMALISATION

Denormalisation deliberately introduces selected redundancy or precomputed values to support a workload, often repeated read-heavy reporting. A stored order total or joined reporting structure can reduce repeated joins or aggregation.

The cost is extra storage and consistency work when source facts change. A total that is quick to read but wrong after an update is not a successful optimisation. Define how redundant values are maintained, validated and refreshed.

Evaluate in context: measure the query cost, consider indexes or views, state acceptable freshness and compare maintenance cost with read benefit. Normalisation improves integrity design; it is not a promise of fastest reporting, and denormalisation is not simply “poor design”.

A3.3.1 | DDL AND DML

Purpose Examples Key distinction
Define or change structures: DDL CREATE TABLE, ALTER TABLE, DROP TABLE A column or object definition changes.
Access or manipulate records: broad DML SELECT, INSERT, UPDATE, DELETE Read or modify data in existing structures.

Adding a Room column uses ALTER TABLE; assigning Lab 2 to a row’s Room uses UPDATE. DELETE removes rows but leaves the table definition; DROP TABLE removes the object if permitted.

SELECT is included in broad DML here, although some references call retrieval DQL. It ordinarily reads a result rather than changing stored rows. INTEGER and VARCHAR are data types, not alternative language categories.

A3.3.2 | CONSTRUCT A TWO-TABLE QUERY

SELECT s.StudentName, l.BookTitle, l.DaysLoaned
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE s.YearGroup = 12
  AND l.DaysLoaned BETWEEN 8 AND 12
  AND (l.BookTitle LIKE 'Python%' OR l.BookTitle LIKE '%Design%')
ORDER BY l.DaysLoaned DESC, s.StudentName ASC, l.LoanID ASC;

The join connects the student key to the loan’s borrower reference, not to LoanID or visible row positions. The inner join returns matching pairs, so one student can occur several times and a student with no loan is absent.

BETWEEN includes both endpoints; % matches zero or more characters in LIKE. Parentheses make the year/range restrictions apply to both title alternatives. Each comparison must be complete. Text case behaviour depends on DBMS/collation. ORDER BY specifies the returned order and later expressions break ties.

A3.3.2 | DISTINCT, GROUP BY AND HAVING

SELECT s.StudentID, s.StudentName, COUNT(l.LoanID) AS LoanCount
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE l.DaysLoaned >= 7
GROUP BY s.StudentID, s.StudentName
HAVING COUNT(l.LoanID) >= 2
ORDER BY LoanCount DESC, s.StudentID ASC;

WHERE removes individual loan rows before grouping; HAVING then keeps groups with at least two surviving loans. The count is not the student’s total over excluded rows. This minimal COUNT example supports the common query-construction requirement for GROUP BY and HAVING; the fuller aggregate calculation treatment is HL.

DISTINCT applies to the whole selected output combination. DISTINCT StudentName alone can merge different people sharing a name. It does not repair an incorrect join. A LEFT JOIN can retain unmatched students, but conditions on loan-side columns in WHERE can remove their NULL-extended rows.

WORKED SQL CHECK | PREDICT THE RESULT

StudentID StudentName YearGroup
1 Maya 12
2 Leon 13
3 Aisha 12
LoanID StudentID BookTitle DaysLoaned
101 1 Python Basics 12
102 1 Robot Design 8
103 2 Python Projects 20
104 3 Web Design 12
Result name Title Days
Aisha Web Design 12
Maya Python Basics 12
Maya Robot Design 8

This is the result of the filtered query above for this small dataset. Leon fails the Year 12 condition. Both twelve-day rows remain, with Aisha before Maya by the requested name tie-breaker. The eight-day row is included because BETWEEN includes 8.

A3.3.3 | INSERT, UPDATE AND DELETE

INSERT INTO Equipment (ItemID, ItemName, Quantity)
VALUES (304, 'Raspberry Pi', 6);

UPDATE Equipment
SET Quantity = Quantity + 2
WHERE ItemID = 304;

DELETE FROM Equipment
WHERE ItemID = 304;

Assuming the required schema and constraints permit the insert, it adds one row with Quantity 6. UPDATE reads that row’s current quantity, adds two and stores 8. DELETE removes the whole row. No matching UPDATE row means zero changes, not automatic insertion.

Use commas between multiple SET assignments. Omitting WHERE can legitimately target every row, so valid syntax is not proof of the intended scope. Check affected records and constraint behaviour.

A3.3.3 | INDEXED CHANGES AND MAINTENANCE

An index can help locate target rows, but inserts, deletes and indexed-value changes also require relevant index maintenance. Changing Category from Electronics to Sensors must make the logical association reflect the new key.

The DBMS normally maintains affected index information automatically. This is not the same as rebuilding every index after every UPDATE. Physical work depends on the engine and index design, including some cases where an unchanged key still incurs work.

Significant modifications can leave inefficient or sparse layouts. A measured need may justify supported rebuilding or reorganisation, with time, I/O, storage and availability costs. Updating planner statistics helps plan selection and is not identical to rebuilding an access structure.

A3.3.4 | AGGREGATES, NULL AND ZERO HL ONLY

Known input and missing data COUNT(*) COUNT(Score) SUM AVG MIN MAX
80, 60, NULL, 90, 0, 70, 80 7 6 380 380/6 = 63.333… 0 90

The syllabus names the average operation AVERAGE; executable SQL normally uses AVG. AVG, SUM, MIN and MAX use non-null input values. Zero is known and included, while NULL is absent/unknown. COUNT(*) counts rows; COUNT(Score) counts non-null scores.

COUNT(DISTINCT Score) is 5 here, not 6. Removing repeated equal scores changes the statistic; AVG(DISTINCT Score) is not the mean of all recorded students.

With an overall empty input, these COUNT forms return 0 and these SUM/AVG/MIN/MAX forms return NULL. An all-null input can have COUNT(*) greater than zero but no known score. A grouped empty input creates no groups.

A3.3.4 | CONSTRUCT AND INTERPRET GROUPED CALCULATIONS HL ONLY

SELECT ClassGroup, COUNT(Score) AS RecordedCount,
       MIN(Score) AS LowestScore, MAX(Score) AS HighestScore,
       AVG(Score) AS MeanScore
FROM Assessment
GROUP BY ClassGroup
HAVING COUNT(Score) >= 3
ORDER BY MeanScore DESC, ClassGroup ASC;

One group is formed per class; HAVING retains classes with at least three known scores. WHERE would change which rows enter each group. Include non-aggregated outputs in appropriate grouping and interpret the denominator.

Averaging class means without weights is not generally the pooled student mean. If Class A has mean 70 from two known scores and B has mean 60 from four, their equal-class mean is 65, but the pooled score mean is (70×2 + 60×4)/6 = 63.333….

Check grain after a join: duplicating one assessment through several comments can inflate its contribution. DISTINCT score values cannot generally repair that because different assessments may legitimately have equal scores.

A3.3.5 | VIRTUAL AND MATERIALIZED VIEWS HL ONLY

Feature Virtual Materialized
Stored object Query definition. Definition plus derived result.
Source change New queries use source data visible to them. Stored result changes according to maintenance policy.
Performance trade-off Simplifies an interface; derivation may still be costly. Can reuse expensive results; adds storage and refresh work.
Updatability Supported simple views may map changes to base records. DBMS-specific; PostgreSQL materialized views do not accept direct DML changes.

A view can hide joins, groupings, rows or columns, and a maintained interface can shield some logical schema changes. Correct permissions and security semantics are essential; creating a restricted view does not revoke an existing base-table access route.

A read-only virtual view can show new results after base changes. Read-only describes the modification route, not frozen values. Grouped averages are generally not automatically updatable because no unique underlying change is specified. A virtual view is not an automatically updating browser, and a refreshed snapshot is not a complete backup history.

A3.3.6 | ACID AND TRANSACTION BOUNDARIES HL ONLY

BEGIN TRANSACTION;
-- Debit the eligible source; application checks one row changed.
-- Credit the intended destination; application checks one row changed.
COMMIT;
-- On failure, stop and ROLLBACK instead of committing.

Atomicity means the included work has an all-or-nothing committed outcome. Consistency means correct logic and constraints preserve defined rules. Isolation governs concurrent visibility and interference. Durability retains confirmed committed work through covered failure.

For a 200-credit transfer, 1000/300 becomes 800/500 on success or remains 1000/300 after abandonment. A transaction’s own working state can contain intermediate changes without a half-transfer becoming the committed final outcome.

Zero changed rows need not cause an SQL exception. The application must check that each intended step succeeded, rather than crediting a destination after no debit. SQL comments do not execute these checks. A later ROLLBACK does not undo earlier confirmed commits.

A3.3.6 | CONCURRENCY, FAILURE AND LIMITS HL ONLY

A stale read followed by an unconditional write can violate a booking rule even inside a transaction at some isolation levels. Use suitable conditional updates, constraints, locks or stronger isolation, with supported retries where required.

Serializable committed effects must match some serial order, not necessarily physical one-at-a-time execution. Lower isolation levels allow specified anomalies. Error handling differs by DBMS, so do not assume every failed statement automatically rolls back the whole unit.

If a commit reply is lost, the application may not know whether the server committed. Blind retries can duplicate the action; an appropriate unique operation identity can help resolve the outcome. Transactions do not unsend an independently delivered email or replace backups and correct business logic.

A3.4.1 | ALTERNATIVE APPROACHES CAN OVERLAP HL ONLY

Approach Defining feature Example and limitation
NoSQL Non-relational model families. Documents for varied product attributes; validation still matters.
Cloud Hosting and operational deployment. Managed service reduces selected infrastructure tasks; configuration and costs remain.
Spatial Location/shape types, indexes and operations. Nearby collection points; coordinates and distance units matter.
In-memory Main working dataset primarily in RAM. Fast counters or state; capacity and persistence need planning.

Document, key-value, graph and wide-column models suit different access patterns. A graph can traverse social connections; key-value can retrieve a known basket. NoSQL does not universally mean no structure, no constraints or no transactions.

Cloud can be relational or non-relational. Spatial can extend a relational database. In-memory systems may use durable logs or snapshots. A cache can be reconstructible, while authoritative records need a suitable durability design. Managed databases are components, not identical to complete SaaS applications.

A3.4.2 | WAREHOUSE OBJECTIVES HL ONLY

Characteristic How it supports BI
Subject-oriented Organise relevant facts around subjects such as sales.
Integrated Reconcile labels, units, identifiers and measure meanings across sources.
Time-variant Retain event dates and history for comparisons.
Append-oriented Add events or versions without routinely erasing their story.
Non-volatile Keep loaded analytical history relatively stable.
Query-optimised Use suitable schema/storage/access design for broad analysis.

ETL or ELT prepares source data for reporting. Source-aware identity can prevent replayed events being counted twice, while invalid data should be flagged rather than guessed. Lineage makes sources and transformations traceable.

Append-only is a design approach, not a ban on controlled corrections or retention. Non-volatile is about historical stability, not hardware memory. A warehouse is neither a simple backup nor a guarantee that missing source facts can be recovered. Event time and load time can differ.

A3.4.3 | OLAP OPERATIONS HL ONLY

Operation Effect
Slice Fix a dimension member, such as October.
Dice Select a subset across dimensions, such as October/South/Robotics.
Drill-down Move to available finer detail.
Roll-up Aggregate to a higher level.
Pivot Rearrange displayed axes without changing the selected facts.

OLAP explores measures such as sales value across dimensions such as time, region, product and channel. A conceptual cube is not limited to three dimensions and need not physically precompute every cell.

The two-month teaching dataset totals September 372 and October 712 CHF, selected total 1084. Rolling them into 2026 does not establish a full twelve-month total. Analysis can locate contributions without proving a cause.

A3.4.3 | SIX DATA-MINING TECHNIQUES HL ONLY

Technique Output / useful question
Classification Predefined category or probability, such as likely return.
Clustering Discovered similarity groups, such as purchasing segments.
Regression Numeric estimate, such as delivery minutes.
Association rule discovery Co-occurring items, such as Kit and Battery in baskets.
Sequential pattern discovery Recurring ordered events, such as tutorial then purchase.
Anomaly detection Unusual cases relative to a baseline, for investigation.

Classification typically learns from labelled categories; clustering derives groups without a supplied target label. Regression differs by numeric output. An association arrow does not itself imply time order or causation. An unusual order is not proven fraud.

Evaluate predictions on suitable held-out data, avoid leakage from information unavailable at prediction time and monitor change. Feature choices, sample bias and missing data can distort results.

WORKED CHECK | ASSOCIATION IS NOT CAUSATION HL ONLY

In eight baskets, five contain Kit, six contain Battery and four contain both. Kit → Battery has support 4/8 = 50% and confidence 4/5 = 80%. Battery’s baseline is already 6/8 = 75%, so the improvement is modest: lift ≈ 1.07.

Reverse the rule and the shared-basket count stays four, but confidence becomes 4/6 ≈ 66.67%. Conditional direction changes the denominator; it does not establish which item was bought first. A small observed association is not proof of a robust commercial or causal rule.

A3.4.4 | PARTITIONING, REPLICATION AND CONSISTENCY HL ONLY

Partitioning divides logical data portions; replication copies the same logical records. A system can partition branch stock and replicate each partition. North 5 plus South 3 is eight kits, not thirteen just because North has another physical copy.

A synchronous policy waits for required coordination, not universally every replica. Asynchronous propagation can leave stale followers. Eventual consistency describes convergence when updates stop and propagation/reconciliation proceeds; the actual read and write guarantees must be stated.

Concurrency control protects conflicting operations; replication alone does not prevent both users reserving the last item. Distributed transactions need appropriate coordination to preserve required ACID outcomes. Two independent local commits do not automatically form one global atomic transfer.

A3.4.4 | GLOBAL QUERIES, TRANSPARENCY AND RESILIENCE HL ONLY

Global query processing locates relevant logical partitions, executes work and combines results without counting replicas twice. A missing partition is unknown, not zero; a complete query may need to wait, fail or use an appropriate replica.

Distribution transparency hides selected fragmentation and replication details. Location transparency specifically lets logical names survive supported changes in physical placement. Neither removes network latency or every visible failure.

Fault tolerance depends on suitable surviving resources, safe role changes and the protocol’s requirements. Scalability depends on balance and divisibility of work; hotspots and coordination can limit gains. More nodes do not guarantee lower latency.

Security must cover users, nodes, inter-node traffic, stored replicas, logs and backups. Encryption does not repair consistency errors, and replication can propagate a mistaken deletion.

COMMON MISTAKES | KEEP THE DISTINCTIONS

Confusion Correction
Primary key / row number Stored logical identity / current display position.
Foreign key / unique child value A reference can repeat on the many side.
Non-prime / outside the primary key Non-prime means in no candidate key.
2NF / 3NF Remove partial dependencies / address the required transitive conditions.
ALTER / UPDATE Definition / stored values.
DELETE / DROP Rows / table object.
WHERE / HAVING Rows before groups / group condition.

HL COMMON MISTAKES | DISTINCT MECHANISMS HL ONLY

Confusion Correction
COUNT(*) / COUNT(column) Rows / non-null expression values.
Virtual / materialized Definition-derived query / stored derived result.
Atomicity / correct logic All-or-nothing / preservation of intended business rules.
Partition / replica Different logical portion / another physical copy.
Association / sequence / cause Co-occurrence / ordered events / causal explanation.

EXAM COMMUNICATION | MATCH THE COMMAND

Outline: main features and a relevant example. Describe: what it is and how it behaves. Explain: mechanism and consequence, using because, therefore or resulting in. Construct: produce the ERD, tables or query with its rules. Evaluate: weigh context-specific benefits and limitations and reach a justified judgement.

For normalisation, state dependencies and key assumptions before splitting. For SQL, show the exact requested columns and scope, then predict a boundary or excluded case. For denormalisation, views and distribution, avoid absolute performance or security guarantees.

Use your selected-card list to revisit weak points. The separate end-of-topic review is for checking performance after revision; the workbook will provide further structured practice when available.