CCEA A-Level Software Systems Development A2 1 Systems Approaches and Database Concepts: a complete overview of the SDLC, modelling and relational databases

What this unit demands

A2 1 Systems Approaches and Database Concepts is the theory and design half of the A2 year. It covers how systems are built (the lifecycle and the methodologies that organise it), how they are modelled (data flow diagrams and UML), and how their data is structured and queried (relational databases, entity relationship modelling, normalisation and SQL). The examiners test conceptual understanding (stages, methodologies, modelling notation) alongside concrete design and SQL skills, especially drawing ERDs, normalising to third normal form and writing accurate SQL.

This guide walks through the six dot points of the unit, then sets out the exam patterns CCEA repeats. Each topic has a matching dot-point page with practice questions; this overview ties them together.

The lifecycle and methodologies

The systems development lifecycle (SDLC) is the structured sequence of stages - analysis (gather requirements), design (plan the solution), implementation, testing, installation and maintenance - used to build a system. Methodologies organise this work: the linear waterfall model suits stable requirements, while iterative approaches (prototyping, rapid application development and agile) suit changing requirements through feedback-driven cycles. A feasibility study judges whether a project is worth doing, and fact-finding (interviews, questionnaires, observation, document analysis) gathers requirements.

Modelling a system

A data flow diagram (DFD) models how data moves, using external entities, processes, data stores and data flows. UML models object oriented systems: a use case diagram captures requirements (actors and use cases), a class diagram shows the static structure (classes with attributes, methods and relationships), and an activity diagram shows a workflow. A data dictionary records each data item's name, type, size, format and validation, keeping the design consistent.

Relational databases and design

A relational database stores data in tables linked by keys: a primary key uniquely identifies records, and a foreign key references another table's primary key to form a relationship, with referential integrity keeping the links valid. An entity relationship diagram models entities and relationships with their cardinality (1:1, 1:M, M:N, the last resolved with a link table). Normalisation to third normal form removes redundancy and anomalies by progressively eliminating repeating groups (1NF), partial dependencies (2NF) and transitive dependencies (3NF).

Structured Query Language

SQL defines and queries the database. Data definition (CREATE TABLE) sets up tables, fields, types and keys. Data manipulation retrieves data with SELECT (choosing columns, filtering with WHERE, sorting with ORDER BY, combining tables with joins, summarising with aggregate functions and GROUP BY, filtering groups with HAVING) and changes data with INSERT, UPDATE and DELETE.

How this unit is examined

A typical CCEA profile for A2 1:

• Lifecycle and methodologies. Describing the SDLC stages and comparing waterfall with iterative methods.
• Modelling. Drawing or interpreting DFDs and UML diagrams.
• Database design. Drawing ERDs, resolving many-to-many relationships, and normalising to 3NF.
• SQL. Writing SELECT queries with WHERE, ORDER BY, joins and aggregates, and the INSERT/UPDATE/DELETE statements.

Check your knowledge

A mix of recall and applied questions covering the unit. Attempt them under timed conditions, then check against the solutions.

1. Name the stages of the systems development lifecycle in order. (3 marks)
2. State one project type that suits the waterfall model and one that suits an agile approach. (2 marks)
3. Name the four components of a data flow diagram. (4 marks)
4. Define a primary key and a foreign key. (2 marks)
5. State the condition for a table to be in third normal form. (1 mark)
6. Name the three relationship cardinalities. (3 marks)
7. Write SQL to list the name and price of every Product costing more than 20, sorted by price ascending. (3 marks)
8. State the difference between the WHERE and HAVING clauses in SQL. (2 marks)