Networks and Databases

🎯 Learning Objectives

• Compare network topologies (star, mesh, bus) and distinguish LAN, WAN, client-server and peer-to-peer models (AQA 4.5.2, OCR 1.3.3)
• Describe the four layers of the TCP/IP stack and the role of each (AQA 4.5.4, OCR 1.3.3)
• Compare TCP and UDP, and trace the TCP three-way handshake and DNS resolution steps (AQA 4.5.2, OCR 1.3.3)
• Explain IP addressing: IPv4 dotted decimal, subnet masks and CIDR notation, and why IPv6 exists (AQA 4.5.2, OCR 1.3.3)
• Design relational databases: entities, attributes, relationships and keys (primary, foreign, candidate), and enforce referential integrity (AQA 4.6.1, OCR 1.3.2, WJEC 1.9)
• Normalise a database to 3NF, showing partial and transitive dependencies at each stage (AQA 4.6.2)
• Write SQL SELECT queries with joins, aggregation and grouping, and INSERT/UPDATE/DELETE statements (AQA 4.6.3, OCR 1.3.2)
• Explain transaction processing and the ACID properties, including concurrency control (AQA 4.6.2, OCR 1.3.2)

📋 Key Definitions and Core Concepts

📌 Key Points

Network fundamentals and the TCP/IP stack

Topologies (AQA 4.5.2, WJEC 1.7): star — every node connects to a central switch; easy to add nodes, one cable fault isolates only that node, but the switch is a single point of failure; bus — all nodes share one backbone cable; cheap but collisions rise with traffic and a backbone break kills the network; mesh — nodes interconnect (full or partial); redundant paths give fault tolerance at the cost of cabling; ring — nodes in a loop with token passing. The exam pattern: given a scenario (school, office, safety-critical), name the best topology and justify with cost, fault tolerance and expansion.

LAN vs WAN — the one-marker pair: a LAN spans one site, is owned by the organisation, and has high bandwidth/low latency; a WAN spans large distances using third-party (telecom) infrastructure — leased lines and the internet. Client-server vs peer-to-peer: client-server centralises services (files, databases, email) on dedicated servers — central control, security and backups, but the server is a cost and a bottleneck; peer-to-peer shares resources directly between equals — cheap for small networks but no central control, and security/backup are per-machine.

The TCP/IP stack (AQA 4.5.4, OCR 1.3.3) has four layers, each using the layer below and serving the layer above:

The two examinable points: (1) layering — each layer's services are independent of the implementation of the others, so a protocol can change (WiFi to Ethernet) without touching the layers above, and the same stack works over any physical medium; (2) encapsulation — data moves down the stack gaining a header at each layer (TCP header with ports and sequence numbers, IP header with addresses, Ethernet frame with MAC addresses) and the headers are stripped back off at the receiver. Exam question pattern: 'Explain the purpose of each layer of the TCP/IP stack.' Answer: application provides user-facing services; transport provides end-to-end delivery with ports and (with TCP) reliability; internet routes packets across networks by IP; link handles transmission over the physical local network.

Devices to know by role: a switch forwards frames within a LAN using MAC addresses; a router forwards packets between networks using IP addresses; a firewall filters traffic by rules; a wireless access point joins wireless clients to the wired LAN.

Key protocols: TCP vs UDP, DNS, HTTP, IP addressing

TCP vs UDP is the highest-frequency comparison in this topic (AQA 4.5.2, OCR 1.3.3). TCP is connection-oriented: a three-way handshake (client SYN → server SYN-ACK → client ACK) establishes the connection; segments are numbered and acknowledged, lost segments are retransmitted, and flow/congestion control pace the sender. UDP is connectionless: no handshake, no ordering, no retransmission — just datagrams with ports. The trade-off sentence to quote: TCP guarantees delivery and order at the cost of overhead and latency; UDP has minimal overhead and latency but no guarantees.

Application matching is the standard follow-up: web pages, file transfer and email need every byte intact and in order — TCP. Live video, voice and online games prefer UDP — a lost packet is a momentary glitch, and retransmitting an outdated frame would arrive too late to be useful; DNS queries also use UDP (single small request-response, retried on failure). The exam pattern: 'Explain why UDP rather than TCP is used for live video streaming.' Answer: streaming tolerates small losses but not delay; TCP's retransmission of lost packets would stall playback with late-arriving data, whereas UDP delivers what arrives immediately — lower latency, and a dropped frame is preferable to a delayed one.

DNS resolution (AQA 4.5.2) — learn the sequence:

1. The client asks its configured recursive resolver (ISP's DNS server) for www.example.co.uk.
2. The resolver asks a root server, which replies with the address of the .co.uk TLD server.
3. The resolver asks the TLD server, which replies with the authoritative name server for example.co.uk.
4. The resolver asks the authoritative server, which returns the IP address (an A record for IPv4, AAAA for IPv6).
5. The resolver caches the answer (with a TTL) and returns it to the client, which connects to that IP.

Records to name: A (name → IPv4), AAAA (name → IPv6), MX (mail server), CNAME (alias). HTTP/HTTPS: HTTP is a request-response application protocol (GET, POST, status codes); HTTPS is HTTP over TLS — the handshake exchanges certificates and establishes session keys before any data flows, giving confidentiality and server authentication. Exam one-liner: HTTPS encrypts the traffic and proves the server's identity via a CA-signed certificate, protecting against eavesdropping and man-in-the-middle attacks.

IP addressing: IPv4 is 32-bit, written dotted decimal (192.168.1.0), with a subnet mask splitting network bits from host bits. CIDR notation appends the network-bit count: /24 = 255.255.255.0 = 24 network bits, 8 host bits, 254 usable hosts. Worked subnetting (the exact exam pattern):

Subnet 192.168.1.0/24 into 4 equal subnets:
  4 subnets need 2 more network bits: /24 -> /26
  New mask: /26 = 255.255.255.192
  Subnets: 192.168.1.0/26    (hosts .1 - .62)
           192.168.1.64/26   (hosts .65 - .126)
           192.168.1.128/26  (hosts .129 - .190)
           192.168.1.192/26  (hosts .193 - .254)

Method: doubling the number of subnets adds one bit to the mask; each subnet's size halves (here 64 addresses, 62 usable — subtract the network and broadcast addresses). IPv6 exists because 32-bit IPv4 exhausted its ~4.3 billion addresses; IPv6 is 128-bit hex, eliminating the need for NAT, with hierarchical routing and built-in support for autoconfiguration.

Relational databases: entities, keys and integrity

A relational database stores data in tables (relations): each table is an entity, each column an attribute, each row a record. Relationships between tables are 1:1, 1:N or M:N, and the examinable design rule: an M:N relationship must be resolved with a link (junction) table — e.g. Student ↔ Course is M:N, so create Enrolment(StudentID, CourseID) referencing both.

Keys (AQA 4.6.1, OCR 1.3.2): a primary key uniquely identifies each record (never null, never duplicated); a candidate key is any attribute that could serve as the primary key; a foreign key in one table references the primary key of another, forming the link; a composite key combines two or more attributes (link tables commonly use (StudentID, CourseID) as a composite PK). Referential integrity is the constraint that every foreign key value must match an existing primary key in the referenced table — you cannot enrol a student who does not exist, and deleting a course must be restricted or cascade to its enrolments. The exam pattern: 'Explain how the design enforces referential integrity.' Answer: the FK in the child table references the PK of the parent; the DBMS rejects inserts/updates with FK values that have no matching PK, and deletion of a referenced parent is restricted or cascades.

Why relational at all — the 4-marker: data is stored once (no redundancy), so updates happen in one place and cannot disagree; integrity is enforced by keys and constraints; SQL gives a declarative query language independent of storage; and tables scale to complex many-to-many structures via link tables. Contrast with flat files worth one line: a flat file repeats data across records, so it suffers update anomalies (change a name in one record, disagree with another) and has no built-in integrity.

Normalisation to 3NF

Normalisation (AQA 4.6.2, WJEC 1.9) structures tables to eliminate redundancy. The stages, in mark-scheme wording:

• 1NF (first normal form): all attributes contain atomic (indivisible) values; no repeating groups. Fix a 'phone numbers' column holding '0121 555, 07700 900' by splitting into a separate Phone table.
• 2NF: in 1NF and no non-key attribute depends on only part of a composite primary key (partial dependency). Only bites when the key is composite.
• 3NF: in 2NF and no non-key attribute depends on another non-key attribute (transitive dependency).

Worked example — normalise Student(StudentID, Name, CourseID, CourseName, TutorID, TutorName):

Functional dependencies:
  StudentID  -> Name, CourseID
  CourseID   -> CourseName, TutorID      // course determines its tutor
  TutorID    -> TutorName

1NF: all values atomic - already satisfied.

2NF: key is StudentID alone (no composite key), so no partial
     dependencies are possible - already in 2NF.

3NF: CourseName and TutorID depend on CourseID, a non-key attribute
     -> transitive dependency. TutorName depends on TutorID, another
     non-key attribute -> transitive dependency. Split:

  Student(StudentID, Name, CourseID)
  Course(CourseID, CourseName, TutorID)
  Tutor(TutorID, TutorName)

Step-by-step reasoning to reproduce: (1) list the functional dependencies; (2) check 1NF — atomic values; (3) check 2NF — only relevant when a composite key exists, so with a single-attribute key the table is automatically in 2NF (a point that earns the 'explain why 2NF was trivially satisfied' mark); (4) check 3NF — any non-key attribute determined by another non-key attribute must move out with its determinant; (5) verify the final tables: each stores one entity, linked by foreign keys, with zero redundancy — CourseName appears once however many students take the course.

The benefits to quote in the 4-marker: no redundancy (each fact stored once), so no update anomalies (changing a tutor's name updates one row); no insertion anomalies (a new course can be added with no students yet); no deletion anomalies (deleting the last student does not erase the course). A second worked pattern — a composite key case: OrderLine(OrderID, ProductID, ProductName, Quantity) — ProductName depends on ProductID alone (part of the composite key OrderID+ProductID), a partial dependency, so 2NF splits out Product(ProductID, ProductName). Exam question pattern: 'Explain the difference between 2NF and 3NF.' Answer: 2NF removes partial dependencies — non-key attributes depending on part of a composite key; 3NF additionally removes transitive dependencies — non-key attributes depending on other non-key attributes; both only apply to tables already in the previous normal form.

SQL and transactions

SQL (AQA 4.6.3, OCR 1.3.2) is declarative — you specify what data, not how to retrieve it. The SELECT skeleton, in evaluation order:

SELECT attributes          // 5. project the columns
FROM tables                // 1. source tables
WHERE condition            // 2. filter rows
GROUP BY attributes        // 3. aggregate rows into groups
HAVING condition           // 4. filter groups
ORDER BY attributes        // 6. sort the result

Worked query 1 — students with an average grade above 70 (the exact existing exam pattern):

SELECT s.Name
FROM Student s
JOIN Grade g ON s.StudentID = g.StudentID
GROUP BY s.StudentID, s.Name
HAVING AVG(g.Mark) > 70;

Step-by-step: JOIN pairs each student with their grades via the foreign key; GROUP BY collapses to one row per student; AVG computes each student's mean; HAVING filters groups — the marking subtlety is that WHERE cannot contain AVG because WHERE runs before grouping; HAVING runs after.

Worked query 2 — multi-table join returning details:

SELECT s.Name, c.CourseName, t.TutorName
FROM Student s
JOIN Course c ON s.CourseID = c.CourseID
JOIN Tutor t ON c.TutorID = t.TutorID
WHERE s.Name LIKE 'A%'
ORDER BY c.CourseName, s.Name;

Each JOIN follows one FK link (Student→Course→Tutor); LIKE 'A%' matches names starting with A (% is the wildcard); ORDER BY sorts by course then name. Worked query 3 — DML:

INSERT INTO Student (StudentID, Name, CourseID)
VALUES (2214, 'Ada', 'CS01');

UPDATE Student SET CourseID = 'CS02' WHERE StudentID = 2214;

DELETE FROM Student WHERE StudentID = 2214;

Marking points: UPDATE/DELETE without WHERE affects every row — the classic catastrophic one-marker; INSERT must supply every non-null column and respect FK constraints.

Transactions and ACID (AQA 4.6.2, OCR 1.3.2): a transaction is a group of operations treated as indivisible. The four properties, each with its example:

• Atomicity — all operations complete or none do: a bank transfer debits one account and credits another; a crash between the two would corrupt the data, so rollback restores the original state.
• Consistency — a transaction moves the database from one valid state to another: constraints (keys, check rules) hold before and after.
• Isolation — concurrent transactions do not interfere: two simultaneous bookings of the last seat cannot both succeed; the DBMS serialises access (locks) so one sees the other's effect.
• Durability — once committed, changes survive a crash: a write-ahead log lets the DBMS redo committed work after failure.

Concurrency failure modes to name: a lost update (two transactions read the same value, both write, one overwrites the other), an inconsistent read, and deadlock (two transactions each hold a lock the other needs — neither proceeds; the DBMS detects and aborts one). Exam pattern: 'Explain why a ticket-booking system must use transactions.' Answer: booking decrements availability and creates the booking — if the system crashes between them the data is inconsistent, so atomicity requires both to succeed or both to roll back; concurrent bookings of the last seat must be isolated so only one succeeds; committed bookings must be durable.

💡 Worked Example

❓ Practice Questions & Mark Schemes

🎬 Video Resources

• YouTube: A-Level Computer Science SQL joins normalisation 3NF TCP IP stack

📄 Past Papers & Exam Resources

• AQA A-Level Computer Science (7517) past papers and mark schemes
• OCR A-Level Computer Science (H446) — qualifications and assessment materials
• Physics & Maths Tutor — Computer Science revision hub

🔗 Further Reading & Resources

• Physics & Maths Tutor — Computer Science revision (notes and topic questions)
• AQA — find past papers and mark schemes (7517)
• OCR — A-Level Computer Science H446 qualifications page

🧠 Flashcards (Spaced Repetition)

📝 Exam Questions by Topic

🎯 Target Tests (Auto-Graded)

🎓 Smart Lesson (Guided)