IB COMPUTER SCIENCE | A3 ASSESSMENT

DATABASES: END OF TOPIC REVIEW

Check relational design, normalisation, SQL and the labelled HL material through recall, applied tasks and developed written responses.

BEFORE YOU BEGIN

Split this review into two sessions if needed: first the automatic checks and applied tasks, then the written work. As a flexible guide, allow 75–100 minutes for the common review and additional time for the HL extension; adjust to the detail of your written answers.

Sections A and B award one point for each fully correct automatic item, with no partial credit within an item. Section C has separately suggested content marks. These are original practice questions, not an official IB paper, grade boundary or prediction.

Choose your level below. Write calculations, diagrams and Section C answers in your exercise book before revealing guidance. Answers and selections last while the page remains open.

Automatic total: 36 points. Suggested written total: 72 marks, assessed separately.

A | QUICK CHECKS

Answer without notes. The common questions are A1–A12; A13–A18 are HL-only.

A1 | Which describe a relational database accurately?

Select all correct answers. One point requires the complete correct set.

A2 | Which schema level primarily describes indexes and physical placement?
A3 | A student may have zero or many enrolments; every enrolment needs exactly one student. Which statements follow?

Select all correct answers. One point requires the complete correct set.

A4 | Which type choices are justified?

Select all correct answers. One point requires the complete correct set.

A5 | Which rules apply to Enrolment with primary key (StudentID, ClubID) and required FKs to its parent tables?

Select all correct answers. One point requires the complete correct set.

A6 | A non-prime attribute belongs to which candidate keys?
A7 | Which identify the intended dependency problems?

Select all correct answers. One point requires the complete correct set.

A8 | Which make a 3NF construction stronger?

Select all correct answers. One point requires the complete correct set.

A9 | Which are balanced denormalisation claims?

Select all correct answers. One point requires the complete correct set.

A10 | Which statements change database structure rather than particular row values?

Select all correct answers. One point requires the complete correct set.

A11 | Which describe query clauses correctly?

Select all correct answers. One point requires the complete correct set.

A12 | Which describe modification and indexes accurately?

Select all correct answers. One point requires the complete correct set.

A13 | HL: Which hold for Score values 80, 0 and NULL?

Select all correct answers. One point requires the complete correct set.

A14 | HL: A stored report result remains unchanged after a source correction until it refreshes. What is illustrated?
A15 | HL: Which distinguish transaction properties?

Select all correct answers. One point requires the complete correct set.

A16 | HL: Which database-approach statements are accurate?

Select all correct answers. One point requires the complete correct set.

A17 | HL: Which describe warehouse characteristics accurately?

Select all correct answers. One point requires the complete correct set.

A18 | HL: Which mining-method descriptions are accurate?

Select all correct answers. One point requires the complete correct set.

B | APPLY YOUR KNOWLEDGE

Each applied item gives its own assumptions. Show calculations, a sketch or a short explanation in your book before entering a final answer. Each item is self-contained. Evaluate its stated dataset and rules rather than carrying changed data from another question.

B1 | Student IDs S1 and S2 and club IDs C1 and C2 exist. Enrolment has pair PK (StudentID,ClubID), required FKs, and already contains S1,C1. Of S1,C2; S2,C1; S9,C1; S1,C1, how many proposed single inserts pass these key/reference rules? Evaluate each against the original table.
B2 | A 1NF OrderLine has sole candidate key (OrderID,ProductID). ProductID determines the non-prime ProductName. Which move addresses this partial dependency?
B3 | Order has OrderID PK, CustomerID and CustomerName. OrderID determines CustomerID; CustomerID determines the non-prime CustomerName. Which new table removes the demonstrated transitive description from Order?
B4 | Student has IDs 1, 2, 3. Loan borrower IDs are 1, 1, 2. Joining Student.StudentID = Loan.StudentID with INNER JOIN, how many result rows are produced with no DISTINCT or extra filter?
B5 | Using the same Student IDs 1,2,3 and Loan borrower IDs 1,1,2, how many rows does SELECT DISTINCT Student.StudentID over that inner join return?
B6 | Joined loan day values are 8, 12 and 13. How many pass WHERE DaysLoaned BETWEEN 8 AND 12?
B7 | Titles are Python Basics, Python, Pythonic Design and Web Design, with matching letter case. How many pass LIKE 'Python%'?
B8 | The requirement is Year 12 AND either a title beginning Python or a title containing Design. Which condition groups it correctly?
B9 | Borrower 1 has loans of 12 and 8 days; borrower 2 has loans of 20 and 5. After WHERE DaysLoaned >= 7, GROUP BY borrower and HAVING COUNT(LoanID) >= 2, how many borrower groups remain? LoanID is never NULL.
B10 | ItemID 304 currently has Quantity 6. UPDATE Equipment SET Quantity = Quantity + 2 WHERE ItemID = 304 executes once. What Quantity remains for that record?
B11 | Four Equipment rows have different ItemIDs. UPDATE Equipment SET Quantity = 0 has no WHERE condition and all rows permit zero. How many records are targeted?
B12 | Category is indexed. An item moves from Electronics to Sensors. Which explanation is best?
B13 | HL: Scores are 10, 0, NULL and 20. What numeric result does AVG(Score) return in a suitable decimal calculation?
B14 | HL: A snapshot stores Class A mean 70 from scores 80 and 60. The 80 is corrected to 100 and committed. Before refresh, a new virtual query sees the change. Which pair is correct: virtual mean / stored snapshot mean?
B15 | HL: A 200-credit transfer starts from balances 1000 and 300. The debit succeeds, the destination is missing, and the application fully rolls back. What is the final combined committed balance?
B16 | HL: A load contains valid source lines Web:W1 = 100 CHF, Shop:T1 = 50 CHF and Web:W2 = 24 CHF, plus an unchanged replay of Web:W1. Source identity is correctly deduplicated. What accepted sales value in CHF is loaded?
B17 | HL: Eight baskets include five with Kit, six with Battery and four with both. What is the confidence of Kit → Battery as a percentage? Enter the number without a percent sign.
B18 | HL: North stock is 5, South stock is 3, and a North replica also stores 5. South becomes unreachable with no suitable accessible replica. Which national-result claim is justified?

CHECK SECTIONS A AND B

Unanswered items receive zero. Each checkbox item requires all correct choices and no incorrect extras. Written marks are separate. Changing an answer or level requires resubmission.

C | WRITTEN COMMUNICATION AND SUBJECT KNOWLEDGE

Write in your exercise book before revealing the guidance. The table gives practical guidance based on the IB command terms supplied in the guide; it is not the Cambridge command-word table.

In particular, IB compare focuses on similarities, while distinguish asks for differences. If you need to address both, the question can request each separately. All main commands below are highlighted.

IB command term What to provide in this review Writing approach
Calculate A numerical result with relevant working stages Show the calculation and units
Describe A detailed account Give features or an ordered process, not only a name
Explain A detailed account including reasons or causes Link ideas with because, therefore, resulting in or leading to
Compare Similarities, referring to both items throughout Both…; each…; in both approaches…
Compare and contrast Similarities and differences, referring to both throughout Use both for similarities and whereas for differences
Discuss A considered, balanced review of relevant arguments or factors Develop strengths and limits in context and reach a supported judgement
Distinguish Clear differences between the concepts Whereas…; unlike…; this holds a value while that holds an address…
Construct Information displayed in diagrammatic or logical form Supply the requested table, map or connected diagram
Deduce A conclusion reached from the supplied information Use the map or data as evidence
Justify Valid reasons or evidence for a choice Connect the choice to the stated requirement
Evaluate An appraisal weighing strengths and limitations Balance relevant factors and reach a supported judgement

A connective earns no automatic mark by itself: it must express an accurate relationship. For example, “a foreign key rejects the specified missing-parent reference, therefore a stored membership cannot point to a nonexistent student under that rule” explains a mechanism and consequence. Suggested marks reward subject content, not extra unofficial grammar marks.

C1 | SHARED RELATIONAL FACTS

6 suggested marks | COMMON SL/HL

Explain how Student, Club and Enrolment can reduce inconsistent student details while preserving membership relationships. Include a primary key, a foreign key and one limitation.

C2 | FROM REQUIREMENTS TO ERD

6 suggested marks | COMMON SL/HL

Construct an ERD for students joining clubs through Enrolment, then describe its relationship to conceptual, logical and physical schemas. A student or club may exist with no memberships; each enrolment needs one student and one club.

C3 | CONSTRUCT A 3NF DESIGN

6 suggested marks | COMMON SL/HL

The following 1NF table has sole candidate key (OrderID,ProductID). OrderID determines OrderDate and CustomerID; CustomerID determines CustomerName; ProductID determines ProductName; the full pair determines Quantity.
OrderID ProductID OrderDate CustomerID CustomerName ProductName Quantity
501 P1 2026-09-02 C1 Maya Kit 2
501 P2 2026-09-02 C1 Maya Glasses 1
502 P1 2026-09-03 C2 Leon Kit 1
Construct a 3NF design, showing primary and foreign keys. Assume one line per product per order.

C4 | NORMAL FORMS AND DEPENDENCIES

6 suggested marks | COMMON SL/HL

Explain the difference between 1NF, 2NF and 3NF using the OrderLine scenario above. Include the meaning of non-prime and one anomaly.

C5 | DENORMALISATION FOR REPORTING

6 suggested marks | COMMON SL/HL

Evaluate storing an order total in addition to its underlying line data for a read-intensive dashboard. The lines can change after an initial save.

C6 | CONSTRUCT THE REQUESTED QUERY

6 suggested marks | COMMON SL/HL

Using the following tables, construct a query returning student name, title and days loaned for Year 12 borrowers with loans between 8 and 12 days inclusive and a title starting Python or containing Design. Order days descending, then names ascending; add a stable final tie-breaker.
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

C7 | MODIFY RECORDS AND MAINTAIN AN INDEX

6 suggested marks | COMMON SL/HL

Equipment contains ItemID 304, Quantity 6 and indexed Category Electronics. Explain how SQL would increase its quantity by two, change its category to Sensors, and remove a mistaken record with ID 305. Include the relevant index work and one maintenance qualification.

C8 | STRUCTURE, VALUES AND TYPES

6 suggested marks | COMMON SL/HL

A club system needs a Room column and a room value for ClubID 1. It also stores code 00127 and an exact decimal fee. Distinguish DDL from DML, then justify appropriate types and compatible reference definitions.

C9 | HL: AGGREGATES AND VIEW FRESHNESS

6 suggested marks | HL ONLY

Assessment has scores 80, 60 and NULL in Class A. A virtual class summary and a materialized snapshot initially show the same mean. Calculate COUNT(*), COUNT(Score) and AVG(Score), then describe the results after 80 is corrected to 100 and becomes visible to a new query, before the snapshot refreshes.

C10 | HL: A RELIABLE TRANSFER

6 suggested marks | HL ONLY

A 200-credit transfer moves balances from 1000/300 to 800/500 across two records. Describe how BEGIN TRANSACTION, COMMIT and ROLLBACK support the action, linking all four ACID properties and the need to check that the intended records changed.

C11 | HL: WAREHOUSE AND ANALYTICAL METHODS

6 suggested marks | HL ONLY

A shop integrates web and counter history, explores monthly category totals, finds products often bought together and estimates delivery minutes. Explain how a warehouse, OLAP and suitable mining techniques support these tasks, with an evidence limitation.

C12 | HL: DISTRIBUTED STOCK

6 suggested marks | HL ONLY

North has 5 kits, South 3, and a North replica also stores 5. Describe partitioning, replication, global query processing and transparency in this system. Include what happens when South is unreachable and why security or fault tolerance needs more than another copy.

CHECK THE QUALITY OF YOUR WRITING

Did you answer the command? For explain, check the reason or cause; for compare, address both items through similarities; for compare and contrast, include differences too; for discuss, balance relevant factors in context; for distinguish, make the differences explicit; for evaluate, weigh strengths and limitations and support your conclusion.

For construct, check labels, connections and completeness. For calculate, show enough working to follow your method and label units. Select two weak responses and rewrite them after revising. Keep the written total separate from your automatic result.

YOUR NEXT REVISION STEP

Submit the automatic sections for targeted revision links.

Record your own written marks using the suggested points. Choose two weaknesses and attempt new examples after revising. Use the A3 revision page for worked content and a personal flashcard list, or return to the IB course index.