Paper 2 — Theory Crash Course
Complete study guide for Paper 2: System Life Cycle & SDLC models, Data Flow Diagrams, Flowcharts, Prototyping, Implementation methods, Boolean Logic & Gates, Relational Databases, Normalisation, ER Diagrams, and SQL (DDL + DML).
How to use this guide
- ⚠️ WARNING Common mistake — marks lost here
- 📌 DEFINITION Technical term — learn exactly as written
- 📋 RULE Step-by-step procedure
- ✅ EXAM TIP Ready-made examiner answer
- 🖼️ IMAGE Placeholder — insert this diagram
Contents
- Ch.1 — Problem Solving & SDLC
- 1.1 SDLC Models
- 1.2 Data Collection Methods
- 1.3 Data Flow Diagrams (DFD)
- 1.4 Flowcharts
- 1.5 Prototypes
- 1.6 Implementation Methods
- 1.7 Project Documentation
- Ch.2 — Boolean Logic
- 2.1 Truth Tables (AND/OR/NOT/NAND/NOR/XOR)
- 2.2 Boolean Laws
- 2.3 Simplifying Expressions
- 2.4 Logic Gates & Circuits
- Ch.3 — Databases & SQL
- 3.1 Relational DB Concepts
- 3.2 Keys & Relationships
- 3.3 Normalisation (1NF–3NF)
- 3.4 ER Diagrams
- 3.5 Data Dictionary & DDL vs DML
- 3.6 SQL — DDL (CREATE/ALTER/DROP)
- 3.7 SQL — DML (SELECT/INSERT/UPDATE/DELETE)
- 3.8 SQL — SELECT with JOIN
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.
Image to insert
Три диаграммы рядом: (1) Waterfall — линейные стрелки вниз; (2) Spiral — спираль с 4 квадрантами; (3) Agile — короткие итерации/спринты. Для каждой: название + ключевое слово.
Поиск: "waterfall spiral agile SDLC models comparison diagram"| 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.
Image to insert
Таблица символов DFD с примерами: (1) Прямоугольник = External Entity; (2) Стрелка = Data Flow; (3) Прямоугольник с открытой стороной = Data Store; (4) Овал/прямоугольник со скруглёнными углами = Process. Для каждого: символ + название + что представляет.
Поиск: "DFD symbols data flow diagram external entity process 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
- Every data flow must connect two elements (entity→process, process→store, etc.).
- Data flows CANNOT go directly between two external entities or two data stores — must pass through a process.
- Each process must have at least one input AND one output data flow.
- Label every arrow with the name of the data flowing (not "data" — be specific).
- 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]
▼
Member1.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.
Image to insert
Таблица символов: Овал (Start/End), Прямоугольник (Process/Action), Ромб (Decision — Yes/No), Параллелограмм (Input/Output), Стрелка (Flow). Каждый символ с рисунком и названием.
Поиск: "flowchart symbols shapes meaning oval rectangle diamond parallelogram"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
- Always start and end with a Terminal (oval).
- Decision diamonds always have exactly TWO exits labelled YES and NO (or True/False).
- Use arrows to show direction of flow — never leave flow ambiguous.
- Loops must have a clear entry and exit point controlled by a decision.
- 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 |
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.
Image to insert
Шесть символов логических вентилей с их стандартными обозначениями: AND, OR, NOT, NAND, NOR, XOR. Каждый: символ + название + краткое описание.
Поиск: "logic gates symbols AND OR NOT NAND NOR XOR standard"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
- Count inputs (n) → table needs 2ⁿ rows.
- Create columns: one per input variable, then one per sub-expression, then the final output.
- Fill input columns with systematic 0/1 patterns (rightmost column alternates 0,1; next alternates 0,0,1,1; etc.).
- Evaluate each sub-expression left to right using the gate rules above.
- 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
- Look for complement pairs: A AND NOT A = 0 (eliminates term), A OR NOT A = 1 (whole expression = 1).
- Apply Absorption: A OR (A AND B) = A — removes the inner term.
- Apply Distributive law to factor out common variables.
- Apply De Morgan's to remove double negations or distribute NOT.
- Use Idempotent: A AND A = A, A OR A = A — eliminate duplicates.
- 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: A2.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.
Image to insert
Примеры логических схем: (1) Half adder (XOR + AND); (2) Сложная схема с несколькими вентилями, входами A, B, C и выходом Q. Для каждой — схема + соответствующее булево выражение.
Поиск: "logic gate circuit diagram AND OR NOT combined example" / mr-tea.net → pages/12-cs-theory/boolean-logic.html📋 Reading a Logic Circuit → Boolean Expression
- Start from the inputs (left side).
- For each gate, write the expression for its output based on its inputs.
- Feed those outputs as inputs to the next gates.
- 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
- Identify all distinct variables — these are your inputs.
- Find the innermost sub-expressions first (most brackets).
- Draw a gate for each operation, connecting outputs as inputs to the next gate.
- 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.
Image to insert
Две таблицы с линиями связи: Students (StudentID PK, Name, ClassID FK) ↔ Classes (ClassID PK, ClassName, TeacherID FK) ↔ Teachers (TeacherID PK, Name). Подчёркнуты PK, подчёркнуты пунктиром FK.
Поиск: "relational database primary key foreign key relationship diagram tables"⚠️ 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.
Image to insert
Три этапа нормализации с примером таблицы: Unnormalised → 1NF (убрать повторяющиеся группы) → 2NF (убрать частичные зависимости) → 3NF (убрать транзитивные зависимости). Стрелки между этапами.
Поиск: "database normalisation 1NF 2NF 3NF stages example diagram"| 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.
Image to insert
ER диаграмма с тремя сущностями: Student ↔ Enrolment ↔ Course. Прямоугольники = entities; ромбы = relationships; овалы = attributes; линии с "1" и "M" показывают кардинальность. Crow's foot notation или Chen notation.
Поиск: "ER diagram entity relationship one-to-many many-to-many example database"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
Cardinality
Shows how many of each entity participates — 1, M (many), or * (many)
📋 Steps to Draw an ER Diagram
- Identify all entities from the scenario (nouns: Student, Course, Teacher…).
- Identify the relationships between entities (verbs: ENROLS, TEACHES…).
- Determine cardinality for each relationship (1:1, 1:M, M:M).
- Identify attributes for each entity (what data is stored about it?).
- Mark the primary key attribute (underlined in Chen notation).
- 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).
Image to insert
Диаграмма Венна: INNER JOIN (пересечение), LEFT JOIN (вся левая + пересечение), RIGHT JOIN (вся правая + пересечение). Для каждого: название + цвет + что возвращает.
Поиск: "SQL JOIN types INNER LEFT RIGHT OUTER Venn diagram"★ 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
- Identify which tables contain the data you need.
- Write:
SELECT [columns] FROM [main table] [alias] - For each additional table:
JOIN [table] [alias] ON [foreign key] = [primary key] - Add
WHEREfor any filtering conditions. - Add
ORDER BYto sort results if needed. - 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 ONwith 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;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 = 0orA 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.