Home/ Assessment Hub/ Paper 2 — Solution Design Revision
NIS 2026–2027 · Grade 12 · Paper 2 · Solution Design

Paper 2 — Solution Design Revision

A focused revision and exam-practice hub for Paper 2: System Life Cycle, engineering and solution design, Boolean logic, relational databases and SQL. Learn the core theory, then practise the drawing, writing, completing and explaining skills required in a structured written paper.

How to use this guide

  • ⚠️ WARNING Common mistake — marks are often lost here
  • 📌 DEFINITION Technical vocabulary to know precisely
  • 📋 METHOD A procedure, drawing rule or construction technique
  • ✅ EXAM TIP How to turn knowledge into marks
  • ✍️ PRACTICE Paper-2-style application, design or construction task

Paper 2 — Solution Design: Exam Map

Based on the NIS Grade 12 Computer Science 2026–2027 test specification. Paper 2 is a structured written paper where practical design skills matter as much as remembering definitions.

90:00
90minutes
70marks
35%overall grade
15AO1 marks
25AO2 marks
30AO3 marks
AO1 · 15AO2 · 25AO3 · 30

What this means

More than three quarters of Paper 2 marks are AO2 + AO3. Reading definitions is not enough: you must apply ideas to scenarios, construct diagrams, design interfaces, write SQL/pseudocode, complete tables and justify choices.

DRAW / DESIGNDFD · flowchart · GUI · ER relationship · logic circuit
WRITESQL · pseudocode · definitions with scenario detail
COMPLETEtruth table · trace table · database definition
EXPLAIN / COMPARESDLC · prototypes · requirements · advantages / limitations

Practice Mode shows tips and marking points. Exam Mode hides them so you can attempt tasks independently.

 Contents

Chapter 1 — Problem Solving & SDLC

1.1 SDLC Models

📌 SDLC (System Development Life Cycle)

A structured process for planning, creating, testing, and delivering software systems. Defines the stages a development team follows from initial idea to finished product and beyond.

📌 SDLC Stages

Analysis → Design → Implementation → Testing → Evaluation → Maintenance. Each stage has defined inputs, activities, and outputs.

SDLC models — visual memory map

Waterfall — sequential
Analysis→Design→Build→Test
Spiral — risk-driven cycles
plan → risk → build → evaluate
Agile — short iterations
Sprint 1
working incrementSprint 2
feedbackSprint 3
improve
Model Structure Advantages Disadvantages
Waterfall Linear sequential stages — each stage completed before the next begins; no going back Simple to manage; clear milestones; good documentation; easy to understand for client Inflexible — changes mid-project are expensive; client only sees product at the end; errors discovered late are costly
Spiral Iterative — risk analysis at each loop; prototype built and evaluated before next cycle Risk is identified early at each phase; good for large/complex systems; flexible to changes Expensive; requires experienced risk analysts; can spiral out of control without strict management
Agile Iterative short sprints (1–4 weeks); working software delivered frequently; continuous client feedback Highly flexible — changes welcome at any point; client involved throughout; early delivery of working software Difficult to predict final cost/time; requires constant client involvement; documentation often neglected

⚠️ Warning — Compare, Don't Just Describe

Questions asking to "compare" models require both advantages AND disadvantages addressed for EACH model in the same answer. Don't just list features of one — link them: "Unlike Waterfall which requires all requirements upfront, Agile allows requirements to change between sprints."

1.2 Data Collection Methods

Method Definition Advantages Disadvantages
Interview Direct questioning of stakeholders face-to-face or remotely In-depth information; clarify answers immediately; flexible follow-up Time-consuming; interviewer bias; limited number of people
Questionnaire Written list of questions distributed to many respondents Reaches many people quickly; cheap; anonymous responses possible Low response rate; cannot clarify ambiguous answers; leading questions possible
Observation Watching how users actually interact with current system Shows real behaviour; uncovers undocumented processes; no reliance on memory Hawthorne effect (people behave differently when watched); time-consuming
Document analysis Examining existing forms, reports, manuals, and records Reveals current data flows and formats; no disruption to users Documents may be outdated; may not reflect actual practice

1.3 Data Flow Diagrams (DFD)

📌 Data Flow Diagram (DFD)

A graphical representation showing how data flows through a system — what data enters, how it is processed, where it is stored, and what data leaves. DFDs show the FLOW of data, not the sequence of events.

DFD reading example

Member
external entity
Borrow request →
1.0 Process Loan
Loan record →
D1 Loans
data store
▭
External Entity

Source or destination of data outside the system (person, org, other system)

→
Data Flow

Movement of data between elements; labelled with the data name

⊏ ⊐
Data Store

Repository where data is held (file, database, table)

◯
Process

Transformation or manipulation of data; numbered and named (verb + noun)

📋 Rules for Drawing a DFD

  1. Every data flow must connect two elements (entity→process, process→store, etc.).
  2. Data flows CANNOT go directly between two external entities or two data stores — must pass through a process.
  3. Each process must have at least one input AND one output data flow.
  4. Label every arrow with the name of the data flowing (not "data" — be specific).
  5. Number each process (1.0, 2.0, etc.) for reference.

⚠️ Common DFD Mistakes

  • Data flowing directly from one external entity to another (no process in between) — invalid.
  • Data flowing directly between two data stores — invalid.
  • Unlabelled arrows — always label with the data name.
  • Confusing DFD with flowchart — DFD shows data movement, not procedure steps.

★ Example — Library Book Borrowing System (Level 0 / Context Diagram)

Member ──[Borrow request]──► [1.0 Process Loan] ──[Loan record]──► D1 Loans │ [Available?]│[Book details] ▼ D2 Book Catalogue │ [Unavailable notice] ▼ Member

1.4 Flowcharts

📌 Flowchart

A diagram representing the sequence of steps and decisions in an algorithm or process. Flowcharts show HOW a process works step by step — unlike DFDs which show WHERE data flows.

Flowchart symbol sequence

START
→
INPUT age
→
age ≥ 18?
→
DISPLAY result
→
END
⬭
Terminal (Oval)

Start or End of the algorithm

▬
Process (Rectangle)

A computation or action (e.g., total = total + price)

⬧
Decision (Diamond)

A Yes/No question — two exits: YES and NO

▱
Input/Output (Parallelogram)

Data entered by user or displayed to user

📋 Rules for Drawing Flowcharts

  1. Always start and end with a Terminal (oval).
  2. Decision diamonds always have exactly TWO exits labelled YES and NO (or True/False).
  3. Use arrows to show direction of flow — never leave flow ambiguous.
  4. Loops must have a clear entry and exit point controlled by a decision.
  5. Use parallelograms for all user input (READ/INPUT) and output (PRINT/DISPLAY).

★ Worked Example — Count even numbers from 1 to 10

START count = 0, n = 1 WHILE n ≤ 10: [Decision] n MOD 2 = 0? YES → count = count + 1 NO → (continue) n = n + 1 OUTPUT count END

⚠️ DFD vs Flowchart — Know the Difference

DFD = shows WHAT data moves and WHERE (system perspective). Flowchart = shows HOW steps happen in sequence (algorithm perspective). Never use flowchart symbols in a DFD or vice versa.

1.5 Prototypes

📌 Prototype

An early working model of a system or software that demonstrates key features and allows users to interact with and evaluate the system before full development begins.

Advantages of Prototyping
  • Client can see and interact with the system early — feedback gathered before full build
  • Misunderstandings identified early — cheaper to fix at prototype stage
  • Reduces risk of final product not meeting requirements
  • Users can clarify requirements by reacting to what they see
  • Can be used as part of the final system (evolutionary prototyping)
Disadvantages of Prototyping
  • Client may think prototype IS the final product — unrealistic expectations
  • Prototype code may be poor quality and used in final product inadvertently
  • Scope creep — clients keep requesting changes ("just one more thing")
  • Additional time and cost to build prototypes before real development
  • May lead to incomplete documentation if focus shifts to prototyping

✅ Exam Tip — "Advantages/disadvantages of using prototypes"

Always link to a specific context. "The client can interact with the prototype and provide feedback, reducing the risk that the final system does not meet their requirements." Generic answers without context score fewer marks.

1.6 System Implementation Methods

📌 System Implementation

The process of transitioning from an old system to a new system. The method chosen affects risk, cost, continuity, and the impact on users.

Method Definition Advantages Disadvantages
Direct (Big Bang) Changeover Old system switched off; new system switched on immediately on a set date Cheap — no parallel running costs; simple; immediate benefits Very high risk — if new system fails, no fallback; staff need training beforehand
Parallel Running Old and new systems run simultaneously until new system is trusted Very safe — old system available as fallback; results compared to verify new system Expensive — twice the workload for staff; duplication of effort; confusion over which system to use
Phased Implementation New system introduced in stages — one module or department at a time Lower risk — problems in one phase don't affect others; feedback informs next phase Slow process; two systems must interface during transition; staff in different phases use different systems
Pilot Implementation New system introduced in one location or department first; rolled out if successful Real-world testing with limited risk; issues resolved before full rollout Pilot site may not be representative; delays full implementation; staff in non-pilot areas wait

✅ Exam Tip — "Compare advantages and disadvantages of implementation methods"

Always justify based on the scenario. A hospital patient record system should use Parallel Running (safety critical — can't risk failure). A small shop update can use Direct Changeover (cheap, simple, low stakes).

★ System Implementation Plan — Key Elements

1. Pre-implementation tasks (hardware/software installation, data migration, staff training) 1. Changeover method (which of the four above, and why) 1. Timeline / Gantt chart with milestones 1. Contingency plan (what happens if the new system fails) 1. Testing plan (who tests, what is tested, acceptance criteria) 1. Post-implementation review (evaluation date, KPIs to measure success)

1.7 Project Documentation

📌 Technical Documentation

Documentation created during the SDLC for developers: system design, data structures, algorithms, API references, installation guides.

📌 User Documentation

Documentation created for end-users: user manuals, quick-start guides, FAQs, training materials.

Formatting Element (LO 12.2.1.x) Purpose
Table of contents (12.2.1.1) Allows reader to navigate to specific sections; generated automatically from headings in MS Word
Headers and footers (12.2.1.2) Repeating information on every page: document title, author, date, page number, logo
Tables (12.2.1.3) Organise structured data into rows and columns for comparison or reference
Page parameters (12.2.1.4) Margins, paper size, orientation (portrait/landscape) — set in Page Setup
Page numbers (12.2.1.5) Inserted via Insert → Page Number; can start from any number; placed in header/footer
Indents and line spacing (12.2.1.6) Indents set paragraph distance from margin; line spacing (single/1.5/double) improves readability

1.8 Engineering & Design Skills

📌 Why this section matters

Paper 2 can require you to produce a solution, not merely describe one. The 2026–2027 specimen includes flowchart drawing, interface design, pseudocode validation and trace-table explanation in one extended scenario.

Flowchart

Translate conditions, repetition and input/output into correct symbols and connected control flow.

GUI Design

Draw the required controls, labels and layout so every stated requirement is visible.

Pseudocode

Use variables, loops, conditions and validation systematically; show enough structure to earn method marks.

Trace Table

Follow the algorithm manually and record variable values after each relevant step.

GUI Design — specimen-style checklist

Taxi Login

Exam rule

Read the bullet list in the question and treat each requested component as a possible mark. Do not rely on decoration: labels and required controls matter.

  • Username input
  • Password input
  • Submit / authorize button
  • City drop-down list
  • Taxi driver radio button
  • Passenger radio button
  • Clear labels

Pseudocode validation — build marks step by step

★ Example — check whether an email contains @ and a later dot

hasAt ← FALSE hasDotAfterAt ← FALSE FOR i ← 1 TO LENGTH(email) IF email[i] = '@' THEN hasAt ← TRUE FOR j ← i + 1 TO LENGTH(email) IF email[j] = '.' THEN hasDotAfterAt ← TRUE ENDIF NEXT j ENDIF NEXT i IF hasAt = TRUE AND hasDotAfterAt = TRUE THEN OUTPUT "valid" ELSE OUTPUT "invalid" ENDIF

How method marks are created

Initialise flags/counters → iterate through the data → test required characters/conditions → combine the checks → output the result. Even if one detail is imperfect, a clearly structured solution can still show multiple valid steps.

Interactive Trace Table

attempt ← 1 password ← "wrong" WHILE password ≠ "nis123" AND attempt < 3 attempt ← attempt + 1 password ← nextInput ENDWHILE authorized ← (password = "nis123")
Step attempt password authorized

Chapter 2 — Boolean Logic

2.1 Truth Tables

📌 Truth Table

A table showing all possible input combinations and their corresponding output for a Boolean expression or logic gate. For n inputs: 2ⁿ rows required.

Logic gates — visual quick recall

ANDA · B
ORA + B
NOTĀ
NAND¬(A·B)
NOR¬(A+B)
XORA ⊕ B

All Six Basic Gates — Truth Tables

AND — Output 1 only if ALL inputs are 1
A B A AND B
0 0 0
0 1 0
1 0 0
1 1 1
OR — Output 1 if ANY input is 1
A B A OR B
0 0 0
0 1 1
1 0 1
1 1 1
NOT — Inverts the input
A NOT A
0 1
1 0
NAND — NOT AND (inverted AND)
A B A NAND B
0 0 1
0 1 1
1 0 1
1 1 0
NOR — NOT OR (inverted OR)
A B A NOR B
0 0 1
0 1 0
1 0 0
1 1 0
XOR — Output 1 if inputs are DIFFERENT
A B A XOR B
0 0 0
0 1 1
1 0 1
1 1 0

📋 How to Build a Truth Table for any Expression

  1. Count inputs (n) → table needs 2ⁿ rows.
  2. Create columns: one per input variable, then one per sub-expression, then the final output.
  3. Fill input columns with systematic 0/1 patterns (rightmost column alternates 0,1; next alternates 0,0,1,1; etc.).
  4. Evaluate each sub-expression left to right using the gate rules above.
  5. Complete the final output column.

★ Worked Example — Truth table for: Q = (A AND B) OR (NOT C)

A B C A AND B NOT C Q = (A AND B) OR (NOT C)
0 0 0 0 1 1
0 0 1 0 0 0
0 1 0 0 1 1
0 1 1 0 0 0
1 0 0 0 1 1
1 0 1 0 0 0
1 1 0 1 1 1
1 1 1 1 0 1

2.2 Boolean Laws

📌 Boolean Laws

Mathematical rules governing the behaviour of Boolean (True/False, 1/0) expressions. Used to simplify logic circuits and reduce the number of gates required.

Law AND form OR form
Identity A AND 1 = A A OR 0 = A
Null / Annihilation A AND 0 = 0 A OR 1 = 1
Idempotent A AND A = A A OR A = A
Complement A AND NOT A = 0 A OR NOT A = 1
Double Negation NOT(NOT A) = A
Commutative A AND B = B AND A A OR B = B OR A
Associative (A AND B) AND C = A AND (B AND C) (A OR B) OR C = A OR (B OR C)
Distributive A AND (B OR C) = (A AND B) OR (A AND C) A OR (B AND C) = (A OR B) AND (A OR C)
Absorption A AND (A OR B) = A A OR (A AND B) = A
De Morgan's NOT(A AND B) = NOT A OR NOT B NOT(A OR B) = NOT A AND NOT B

✅ De Morgan's Laws — Most Examined

To apply De Morgan's: flip the operator (AND↔OR) and negate each term individually.

  • NOT(A AND B) = NOT A OR NOT B
  • NOT(A OR B) = NOT A AND NOT B

⚠️ De Morgan's Common Mistake

Students write NOT(A AND B) = NOT A AND NOT B — this is WRONG. The AND must flip to OR. Memory aid: "Break the line, change the sign."

2.3 Simplifying Boolean Expressions

📋 Simplification Strategy

  1. Look for complement pairs: A AND NOT A = 0 (eliminates term), A OR NOT A = 1 (whole expression = 1).
  2. Apply Absorption: A OR (A AND B) = A — removes the inner term.
  3. Apply Distributive law to factor out common variables.
  4. Apply De Morgan's to remove double negations or distribute NOT.
  5. Use Idempotent: A AND A = A, A OR A = A — eliminate duplicates.
  6. Check if the result can be further reduced.

★ Example 1 — Simplify: (A AND B) OR (A AND NOT B)

= A AND (B OR NOT B) [Distributive law: factor out A] = A AND 1 [Complement law: B OR NOT B = 1] = A [Identity: A AND 1 = A] Result: A

★ Example 2 — Simplify: A OR (A AND B)

= A [Absorption law: A OR (A AND B) = A] Result: A

★ Example 3 — Apply De Morgan's: NOT(A AND B)

= NOT A OR NOT B [De Morgan's: flip AND→OR, negate each] Result: NOT A OR NOT B

★ Example 4 — Reduce to normal form: (A OR B) AND (A OR NOT B)

= A OR (B AND NOT B) [Distributive: A OR (B AND NOT B)] = A OR 0 [Complement: B AND NOT B = 0] = A [Identity: A OR 0 = A] Result: A

2.4 Logic Gates & Circuits

📌 Logic Gate

A physical electronic component implementing a Boolean function. Gates are combined to build logic circuits that perform computations in hardware.

Circuit → expression example

A
B
→
AND
→
OR with ¬C

Read from inputs to output: intermediate = A AND B; output Q = (A AND B) OR (NOT C).

📋 Reading a Logic Circuit → Boolean Expression

  1. Start from the inputs (left side).
  2. For each gate, write the expression for its output based on its inputs.
  3. Feed those outputs as inputs to the next gates.
  4. The final gate's output expression = the Boolean expression for Q.

★ Circuit → Expression Example

Inputs: A, B, C Gate 1: G1 = A AND B (AND gate with A, B) Gate 2: G2 = NOT C (NOT gate with C) Gate 3: Q = G1 OR G2 (OR gate with G1, G2) Final expression: Q = (A AND B) OR (NOT C)

📋 Expression → Circuit

  1. Identify all distinct variables — these are your inputs.
  2. Find the innermost sub-expressions first (most brackets).
  3. Draw a gate for each operation, connecting outputs as inputs to the next gate.
  4. The final gate produces output Q.

✅ Exam Tip — Verify your circuit with truth table

After drawing the circuit, pick one row of the truth table (e.g., A=1, B=0, C=1) and trace the signal through each gate. Confirm the output matches the expected value from the truth table. This catches wiring errors.

Chapter 3 — Databases & SQL

3.1 Relational Database Concepts

📌 Relational Database

A database that organises data into one or more tables (relations), where each table consists of rows and columns. Tables are linked by relationships using keys, eliminating redundancy.

Term Definition Example
Table (Relation) A collection of related data organised in rows and columns Students table, Courses table
Attribute (Field/Column) A named characteristic of an entity — a single piece of information StudentID, FirstName, DateOfBirth
Record (Row / Tuple) A single complete entry in a table — one instance of the entity One student's complete data row
Entity A real-world object or concept about which data is stored Student, Course, Teacher, Book
Index A database structure that speeds up data retrieval on specified columns Index on StudentID for fast lookups
Data type The classification of values an attribute can hold INTEGER, VARCHAR(50), DATE, BOOLEAN

★ Example Table Structure — Students

StudentID FirstName LastName DateOfBirth ClassID
1001 Asel Nurova 2008-05-12 12A
1002 Daniil Petrov 2008-09-03 12B
1003 Zarina Bekova 2007-11-21 12A

Each row = 1 record (tuple) | Each column = 1 attribute | StudentID = Primary Key | ClassID = Foreign Key

3.2 Keys & Relationships

📌 Primary Key

An attribute (or combination of attributes) that uniquely identifies every record in a table. Must be unique, cannot be NULL, and must not change over time.

📌 Composite Key

A primary key consisting of two or more attributes combined. Used when no single attribute is sufficient to uniquely identify a record.

📌 Foreign Key

An attribute in one table that references the primary key of another table, creating a link (relationship) between the two tables. Enforces referential integrity.

Primary and foreign keys create relationships

Students
PK StudentID
Name
FK ClassID
M → 1
Classes
PK ClassID
ClassName
FK TeacherID
M → 1
Teachers
PK TeacherID
Name

⚠️ Primary vs Foreign Key Confusion

A Primary Key uniquely identifies a record in its OWN table. A Foreign Key appears in another table to create a LINK — it refers to the primary key of a different table. The same attribute can be a primary key in one table and a foreign key in another.

Relationship Types

Type Description Example
One-to-One (1:1) One record in Table A relates to exactly one record in Table B One student → one passport record
One-to-Many (1:M) One record in Table A relates to many records in Table B; each B record relates to only one A One class → many students
Many-to-Many (M:M) Many records in A relate to many in B; requires a junction/link table Students ↔ Courses (one student takes many courses; one course has many students)

3.3 Normalisation (1NF → 2NF → 3NF)

📌 Normalisation

The process of organising a database to reduce data redundancy (repetition) and improve data integrity by structuring tables according to normal forms.

Normalisation progression

UNFrepeating groups / non-atomic values
→
1NFatomic values, no repeating groups
→
2NFremove partial dependency
→
3NFremove transitive dependency
Normal Form Requirement What problem it fixes
1NF
First Normal Form		 All attributes contain only atomic (single) values; no repeating groups; each row uniquely identifiable by a primary key Repeating groups — e.g., storing multiple phone numbers in one cell
2NF
Second Normal Form		 Already in 1NF AND every non-key attribute is fully functionally dependent on the ENTIRE primary key (no partial dependencies) Partial dependencies — only applies when primary key is composite; non-key attribute depends on only PART of the key
3NF
Third Normal Form		 Already in 2NF AND no non-key attribute is transitively dependent on the primary key via another non-key attribute Transitive dependencies — e.g., StudentID → ClassID → ClassName (ClassName depends on ClassID, not directly on StudentID)

★ Normalisation Walkthrough

UNNORMALISED — one table with everything: OrderID | CustomerName | CustomerCity | ProductName | Quantity | Price 1NF — ensure atomic values and identify PK: Orders(OrderID, CustomerID, CustomerName, CustomerCity, ProductID, ProductName, Quantity, Price) → Problem: CustomerName depends on CustomerID (not full key if composite) 2NF — remove partial dependencies (split into separate tables): Orders(OrderID, CustomerID, ProductID, Quantity) Customers(CustomerID, CustomerName, CustomerCity) Products(ProductID, ProductName, Price) 3NF — check for transitive dependencies: If CustomerCity → PostalCode then PostalCode depends on City, not CustomerID → Further split: Cities(CustomerCity, PostalCode) Final: Customers(CustomerID, CustomerName, CustomerCity)

⚠️ 2NF only applies when PK is composite

If the primary key is a single attribute, partial dependency cannot exist — so 1NF → 3NF is checked directly. 2NF is only relevant when you have a composite primary key (two or more attributes forming the PK).

3.4 Entity-Relationship (ER) Diagrams

📌 ER Diagram

A graphical representation of the entities in a database and the relationships between them, showing cardinality (1:1, 1:M, M:M) and the attributes of each entity.

Resolving a many-to-many relationship

STUDENT
1 ─── M
ENROLMENT
StudentID + CourseID
M ─── 1
COURSE

The junction entity ENROLMENT resolves STUDENT ↔ COURSE many-to-many into two one-to-many relationships.

▭
Entity

Real-world object (rectangle) — e.g., Student, Course, Teacher

⬧
Relationship

Association between entities (diamond) — e.g., ENROLS, TEACHES

⬭
Attribute

Property of an entity (ellipse) — e.g., StudentID, Name

1 — M
Cardinality

Shows how many of each entity participates — 1, M (many), or * (many)

📋 Steps to Draw an ER Diagram

  1. Identify all entities from the scenario (nouns: Student, Course, Teacher…).
  2. Identify the relationships between entities (verbs: ENROLS, TEACHES…).
  3. Determine cardinality for each relationship (1:1, 1:M, M:M).
  4. Identify attributes for each entity (what data is stored about it?).
  5. Mark the primary key attribute (underlined in Chen notation).
  6. For M:M relationships, create a junction/link entity to resolve into 1:M.

★ Example — School Database ER

Entities: Student, Course, Teacher, Department Relationships: Student ——[ENROLS]——> Course (M:M → needs junction table: Enrolment) Teacher ——[TEACHES]——> Course (1:M — one teacher teaches many courses) Department ——[EMPLOYS]——> Teacher (1:M — one dept has many teachers) Junction table: Enrolment(StudentID FK, CourseID FK, EnrolmentDate, Grade)

3.5 Data Dictionary & DDL vs DML

📌 Data Dictionary

A centralised repository of metadata describing every table, attribute, data type, constraint, and relationship in a database. Acts as a reference for developers and database administrators.

★ Example Data Dictionary Entry

Attribute Data Type Size Constraint Description
StudentID INTEGER — PRIMARY KEY, NOT NULL Unique identifier for each student
FirstName VARCHAR 50 NOT NULL Student's first name
DateOfBirth DATE — NOT NULL Format: YYYY-MM-DD
ClassID INTEGER — FOREIGN KEY → Classes(ClassID) Links to the Classes table
IsActive BOOLEAN — DEFAULT TRUE Whether the student is currently enrolled
DDL — Data Definition Language
  • Defines and modifies the STRUCTURE of database objects
  • Commands: CREATE, ALTER, DROP
  • Used to create tables, add/remove columns, delete tables
  • Changes are permanent and auto-committed
DML — Data Manipulation Language
  • Manipulates the DATA stored within the database
  • Commands: SELECT, INSERT, UPDATE, DELETE
  • Used to query, add, modify, or remove records
  • Changes can be rolled back (in transactions)

3.6 SQL — DDL (CREATE / ALTER / DROP)

★ CREATE TABLE — with all common constraints

CREATE TABLE Students ( StudentID INTEGER PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DateOfBirth DATE, Grade INTEGER CHECK (Grade BETWEEN 1 AND 12), Email VARCHAR(100) UNIQUE, ClassID INTEGER REFERENCES Classes(ClassID), IsActive BOOLEAN DEFAULT TRUE );

★ ALTER TABLE — add, modify, drop columns

-- Add a new column ALTER TABLE Students ADD PhoneNumber VARCHAR(20); – Change a column’s data type ALTER TABLE Students MODIFY Grade DECIMAL(4,2); – Remove a column ALTER TABLE Students DROP COLUMN PhoneNumber; – Rename a table ALTER TABLE Students RENAME TO Pupils;

★ DROP TABLE — permanently delete a table

DROP TABLE Students; -- deletes the table AND all its data permanently

⚠️ DROP vs DELETE

DROP TABLE removes the entire table structure AND all data — DDL command, cannot be undone. DELETE removes only records from within a table — DML command, can be rolled back. Confusing these is a common error.

✅ Common SQL Data Types

Data Type Usage Example
INTEGER / INT Whole numbers StudentID, Age, Quantity
VARCHAR(n) Variable-length text up to n characters Name VARCHAR(50)
CHAR(n) Fixed-length text, always n characters GenderCode CHAR(1)
DATE Date values (YYYY-MM-DD) DateOfBirth, EnrolmentDate
DECIMAL(p,s) Exact decimal with p digits, s after point Price DECIMAL(8,2)
BOOLEAN TRUE or FALSE values IsActive, HasPaid
TEXT Long variable-length text Notes, Description

3.7 SQL — DML (SELECT / INSERT / UPDATE / DELETE)

★ SELECT — query data from a table

-- Select all columns SELECT * FROM Students; – Select specific columns SELECT FirstName, LastName, Grade FROM Students; – Filter with WHERE SELECT * FROM Students WHERE Grade = 12; – Multiple conditions SELECT FirstName, LastName FROM Students WHERE Grade = 12 AND IsActive = TRUE; – Sort results SELECT * FROM Students ORDER BY LastName ASC; – Pattern matching SELECT * FROM Students WHERE LastName LIKE ‘Ali%’; – starts with “Ali”

★ INSERT — add new records

-- Insert one record (all columns) INSERT INTO Students VALUES (1004, ‘Amir’, ‘Seitkali’, ‘2008-03-15’, 12, ‘amir@nis.kz’, ‘12A’, TRUE); – Insert specifying columns (safer) INSERT INTO Students (StudentID, FirstName, LastName, Grade) VALUES (1005, ‘Dina’, ‘Ospanova’, 11);

★ UPDATE — modify existing records

-- Update one field for specific records UPDATE Students SET Grade = 12 WHERE Grade = 11 AND IsActive = TRUE; – Update multiple fields UPDATE Students SET Email = ‘amir.new@nis.kz’, ClassID = ‘12B’ WHERE StudentID = 1004;

★ DELETE — remove records

-- Delete specific records DELETE FROM Students WHERE IsActive = FALSE; – Delete one record DELETE FROM Students WHERE StudentID = 1003;

⚠️ Always use WHERE with UPDATE and DELETE

An UPDATE or DELETE without a WHERE clause affects ALL records in the table. This is one of the most destructive mistakes in SQL. Always verify your WHERE condition with a SELECT first.

✅ SELECT Clause Reference

Clause Purpose Example
WHERE Filter rows by condition WHERE Grade = 12
ORDER BY Sort results ASC or DESC ORDER BY LastName ASC
LIKE Pattern match: % = any chars, _ = one char LIKE 'A%', LIKE '_li'
BETWEEN Range condition (inclusive) WHERE Age BETWEEN 15 AND 18
IN Match any value in a list WHERE Grade IN (11, 12)
AND / OR / NOT Combine conditions WHERE Grade=12 AND IsActive=TRUE
COUNT(*) Count number of rows SELECT COUNT(*) FROM Students
GROUP BY Group rows for aggregate functions GROUP BY ClassID
HAVING Filter groups (like WHERE for GROUP BY) HAVING COUNT(*) > 5

3.8 SQL — SELECT with Multiple Tables (JOIN)

📌 JOIN

A SQL operation that combines rows from two or more tables based on a related column (usually a foreign key matching a primary key).

JOIN types — visual memory aid

INNER JOIN

Only matching rows.

LEFT JOIN

All left rows + matches.

RIGHT JOIN

All right rows + matches.

★ INNER JOIN — most common in NIS exams

-- Get student names with their class names SELECT s.FirstName, s.LastName, c.ClassName FROM Students s INNER JOIN Classes c ON s.ClassID = c.ClassID; – Three-table JOIN: student + enrolment + course SELECT s.FirstName, c.CourseName, e.Grade FROM Students s INNER JOIN Enrolment e ON s.StudentID = e.StudentID INNER JOIN Courses c ON e.CourseID = c.CourseID WHERE e.Grade > 70 ORDER BY s.LastName;

★ Using table aliases for readability

-- Aliases: 's' for Students, 'c' for Classes SELECT s.StudentID, s.FirstName, c.ClassName, c.TeacherName FROM Students s JOIN Classes c ON s.ClassID = c.ClassID WHERE c.ClassName = ‘12A’ ORDER BY s.LastName ASC;

📋 How to Write a Multi-Table SELECT

  1. Identify which tables contain the data you need.
  2. Write: SELECT [columns] FROM [main table] [alias]
  3. For each additional table: JOIN [table] [alias] ON [foreign key] = [primary key]
  4. Add WHERE for any filtering conditions.
  5. Add ORDER BY to sort results if needed.
  6. Use table aliases (e.g., s.FirstName) whenever column names exist in multiple tables.

⚠️ Common SQL SELECT Mistakes

  • Forgetting table alias when the same column name exists in multiple tables — SQL will be ambiguous.
  • Writing JOIN ON with the wrong key — always link FK in one table to PK in the other.
  • Putting column aliases in WHERE clause — use the original column name in WHERE, not the alias.
  • Using = instead of LIKE for pattern matching — = requires exact match.

★ Complete SQL Worked Scenario

Given: Students(StudentID, FirstName, LastName, ClassID) | Classes(ClassID, ClassName) | Grades(StudentID, Subject, Score)

-- Q: List first and last names of Grade 12A students with score above 80 in Computer Science SELECT s.FirstName, s.LastName, g.Score FROM Students s JOIN Classes c ON s.ClassID = c.ClassID JOIN Grades g ON s.StudentID = g.StudentID WHERE c.ClassName = ‘12A’ AND g.Subject = ‘Computer Science’ AND g.Score > 80 ORDER BY g.Score DESC;

Paper 2 — Exam Practice Lab

What the 2026–2027 Specimen Looks Like

The specimen contains five compulsory structured questions. The exact future paper may differ, but the specimen is useful for understanding the kind of work expected.

Q18SDLC · Agile vs Waterfall · data collection
Q213requirements · prototype · DFD
Q315databases · keys · SQL · table design
Q422flowchart · GUI · pseudocode · trace table
Q512logic circuit · truth table · simplification

Skill Practice

DFD Construction [7]

A school canteen system allows a Student to submit an order. The system checks the Menu data store, records valid orders in the Orders data store, and sends an order confirmation to the student. A Canteen Manager can request a daily order list.

Plan first: identify external entities, processes, data stores and named data flows before drawing.
Draw your DFD on paper before revealing the marking points.
Reveal marking points
Look for both external entities (Student, Canteen Manager); suitable processes such as Place/Validate Order and Produce Daily List; Menu and Orders data stores; and clearly labelled flows such as order details, menu data, confirmation and daily order list.

Suggested 90-Minute Strategy

Stage Approx. time Action
Scan 3–5 min Read all questions, identify high-mark construction tasks and familiar topics.
Main work 75–80 min Work roughly in question order; show working and use scenario vocabulary. Do not spend too long polishing one diagram.
Review 5–10 min Check SQL syntax, diagram labels, missing rows in truth/trace tables and whether each command word was answered fully.

Pace guide

70 marks in 90 minutes is about 1.3 minutes per mark on average. Use this only as a rough control: drawing and pseudocode questions can need longer, while short definitions should be fast.

Appendix — Quick Reference

A — SDLC & Implementation Quick Reference

SDLC Model Structure Best for Key weakness
Waterfall Linear, sequential stages, no going back Well-defined, stable requirements Inflexible — changes mid-project are expensive
Spiral Iterative loops with risk analysis each cycle Large, complex, high-risk systems Expensive; needs experienced risk management
Agile Short sprints; continuous client feedback Changing requirements; fast delivery needed Hard to predict final cost/time; needs constant client involvement
Implementation Method Risk Cost Use when…
Direct (Big Bang) 🔴 Very High Low Small, non-critical system; rollback not needed
Parallel Running 🟢 Very Low High Critical system; results must be verified (e.g., bank)
Phased 🟡 Medium Medium Large system that can be split into independent modules
Pilot 🟡 Medium Medium Testing real-world conditions before full rollout
Data Collection Method Advantage Disadvantage
Interview In-depth; clarify answers immediately Time-consuming; limited reach
Questionnaire Reaches many people; cheap Low response rate; can't clarify answers
Observation Shows real behaviour; uncovers hidden processes Hawthorne effect; time-consuming
Document analysis No disruption to users Documents may be outdated

B — Boolean Laws Quick Reference

Law AND form OR form
Identity A AND 1 = A A OR 0 = A
Null A AND 0 = 0 A OR 1 = 1
Idempotent A AND A = A A OR A = A
Complement A AND NOT A = 0 A OR NOT A = 1
Double Negation NOT(NOT A) = A
Commutative A AND B = B AND A A OR B = B OR A
Distributive A AND (B OR C) = (A AND B) OR (A AND C) A OR (B AND C) = (A OR B) AND (A OR C)
Absorption A AND (A OR B) = A A OR (A AND B) = A
De Morgan's NOT(A AND B) = NOT A OR NOT B NOT(A OR B) = NOT A AND NOT B

✅ Simplification Strategy — Always try in this order

  • Step 1: Look for A AND NOT A = 0 or A OR NOT A = 1 — eliminates terms immediately
  • Step 2: Apply Absorption: A OR (A AND B) = A — removes the inner term
  • Step 3: Apply Distributive to factor out a common variable
  • Step 4: Apply De Morgan's to remove double NOT or distribute negation
  • Step 5: Apply Idempotent: A AND A = A, A OR A = A

Truth Tables — All 6 Gates at a Glance

A B AND OR NOT A NAND NOR XOR
0 0 0 0 1 1 1 0
0 1 0 1 1 1 0 1
1 0 0 1 0 1 0 1
1 1 1 1 0 0 0 0

C — SQL Quick Reference

Category Command Purpose Example
DDL CREATE TABLE Create a new table CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50));
ALTER TABLE Modify table structure ALTER TABLE Students ADD Email VARCHAR(100);
DROP TABLE Delete table + all data permanently DROP TABLE Students;
DML SELECT Query data from tables SELECT Name, Grade FROM Students WHERE Grade=12;
INSERT INTO Add new records INSERT INTO Students VALUES (1001, 'Asel', 12);
UPDATE Modify existing records UPDATE Students SET Grade=12 WHERE ID=1001;
DELETE FROM Remove records DELETE FROM Students WHERE Grade=11;
SELECT Clause Purpose Example
WHERE Filter rows by condition WHERE Score > 80
AND / OR / NOT Combine conditions WHERE Grade=12 AND IsActive=TRUE
ORDER BY … ASC/DESC Sort results ORDER BY LastName ASC
LIKE Pattern match (% = any, _ = one char) WHERE Name LIKE 'A%'
BETWEEN Range (inclusive) WHERE Score BETWEEN 60 AND 100
IN Match from a list WHERE Grade IN (11, 12)
INNER JOIN … ON Combine rows from two tables JOIN Classes c ON s.ClassID = c.ClassID
COUNT(*) Count rows SELECT COUNT(*) FROM Students
GROUP BY Group for aggregation GROUP BY ClassID

★ SQL Cheat Sheet — Full Query Template

SELECT [table_alias].[column], ... -- what to show FROM [main_table] [alias] -- primary table JOIN [other_table] [alias] -- link second table ON [alias1].[FK] = [alias2].[PK] -- join condition WHERE [condition1] AND [condition2] -- filter rows ORDER BY [column] ASC / DESC; -- sort result -- Example: SELECT s.FirstName, s.LastName, c.ClassName, g.Score FROM Students s JOIN Classes c ON s.ClassID = c.ClassID JOIN Grades g ON s.StudentID = g.StudentID WHERE c.ClassName = '12A' AND g.Score > 70 ORDER BY g.Score DESC;
SQL Data Type Use Example
INTEGER Whole numbers StudentID, Age, Quantity
VARCHAR(n) Variable text up to n chars Name VARCHAR(50)
CHAR(n) Fixed-length text Code CHAR(3)
DATE Date (YYYY-MM-DD) DateOfBirth, EnrolDate
DECIMAL(p,s) Decimal: p digits, s after point Price DECIMAL(8,2)
BOOLEAN TRUE or FALSE IsActive, HasPaid

D — Database Concepts Quick Reference

Term Definition Example
Table (Relation) Collection of related data in rows and columns Students, Classes, Courses
Attribute (Field) A named property of an entity — one column StudentID, FirstName, Grade
Record (Tuple) One complete row — one instance of the entity One student's full data row
Primary Key (PK) Uniquely identifies every record; NOT NULL; must not change StudentID, CourseID
Composite Key PK made of two or more attributes combined (StudentID + CourseID) in Enrolment
Foreign Key (FK) References the PK of another table; creates a relationship ClassID in Students → Classes.ClassID
1:1 Relationship One record in A ↔ exactly one in B Student ↔ Passport
1:M Relationship One record in A ↔ many in B Class → many Students
M:M Relationship Many in A ↔ many in B; needs junction table Students ↔ Courses (via Enrolment)
Normal Form Rule Fixes
1NF All values atomic; no repeating groups; each row has a PK Repeating groups (e.g., multiple phones in one cell)
2NF 1NF + every non-key attribute fully depends on the ENTIRE composite PK Partial dependencies (only applies with composite PK)
3NF 2NF + no non-key attribute depends on another non-key attribute Transitive dependencies (A→B→C where B is not a key)

E — Command Words

Command Word Expected Response Typical Marks
STATE / NAME / IDENTIFY Specific fact only. Zero explanation needed. 1
DESCRIBE What it is + how it works. Do NOT explain why. 2–3
EXPLAIN Fact + reason/consequence. Use "because…", "therefore…", "which means…" 2–4
COMPARE / CONTRAST Address BOTH items in the same sentence. "Unlike X which A, Y does B." 2–4
EVALUATE Advantages + disadvantages + concluding judgement for the scenario. 4–6
DEFINE Precise technical meaning. No analogies. 1–2
JUSTIFY State the decision + defend with technical reasons from the scenario. 2–3
DRAW / CONSTRUCT Produce an accurate diagram (DFD, flowchart, ER diagram, logic circuit, BST) with correct symbols labelled. 3–6
COMPLETE Fill in missing parts of a given diagram or table — follow the existing style exactly. 1–4
WRITE (SQL query) Produce working SQL — correct syntax, correct table/column names from the scenario, WHERE clause if filtering needed. 3–5

F — All Warnings (Pre-exam Review)

All ⚠️ WARNING blocks from the guide, consolidated for rapid pre-exam review.

W1 — Compare models, don't just describe one

Questions asking to "compare" SDLC or implementation models require both advantages AND disadvantages for EACH model, linked in the same answer. "Unlike Waterfall which is linear, Agile uses short sprints allowing changes between iterations."

W2 — DFD: no direct entity-to-entity or store-to-store data flows

Data CANNOT flow directly between two external entities or between two data stores. All data must pass through a process. Unlabelled arrows also lose marks — always name the data being transferred.

W3 — DFD ≠ Flowchart

DFD shows WHERE data flows (system perspective). Flowchart shows HOW steps happen sequentially (algorithm perspective). Never mix their symbols. DFD has no decision diamonds; flowcharts have no data stores or external entities.

W4 — De Morgan's: flip the operator AND negate each term

NOT(A AND B) = NOT A OR NOT B. NOT(A OR B) = NOT A AND NOT B. The most common mistake: NOT(A AND B) = NOT A AND NOT B — WRONG. Memory aid: "Break the line, change the sign."

W5 — Simplification: show every step with law name

In exams, writing each step with the name of the law applied earns method marks even if the final answer is wrong. Never skip steps or jump to the answer without showing working.

W6 — Primary Key vs Foreign Key

PK uniquely identifies a record in its OWN table. FK appears in ANOTHER table to link to the PK. The same attribute can be a PK in one table and a FK in another. Writing "the primary key links two tables" is incorrect — the foreign key creates the link.

W7 — 2NF only applies when PK is composite

If the primary key is a single attribute, there can be no partial dependency. 2NF is only relevant when you have a composite primary key (two or more attributes forming the PK together).

W8 — DROP TABLE vs DELETE FROM

DROP TABLE removes the entire table structure AND all data permanently (DDL — cannot be undone). DELETE FROM removes only records from within the table (DML — can be rolled back). Confusing these in an answer loses the mark.

W9 — Always use WHERE with UPDATE and DELETE

An UPDATE or DELETE without a WHERE clause affects ALL records in the table — one of the most destructive SQL mistakes. Always verify with a SELECT first.

W10 — SQL JOIN: link FK to PK, not column to column by name

The ON clause must link the foreign key in one table to the primary key in the referenced table. Using the wrong key pair gives incorrect results — no error message, just wrong data.

W11 — Radio buttons need the same name attribute

Radio buttons in a group MUST share the same name attribute. Different names = independent selections = behave like checkboxes. Only the value differs between options in the same group.

W12 — Normalisation: describe what you're removing, not just what you're doing

Saying "I split the table" is insufficient. Explain WHY: "Attribute X has a partial dependency on only part of the composite key, so it is moved to a separate table." The examiner marks the reasoning, not just the action.