Welcome to the Design Stage (Unit A2 2: Application Development)
Welcome to one of the most exciting and rewarding parts of your CCEA A Level Digital Technology coursework! In Unit A2 2 (Application Development), you take on the role of a systems analyst and software developer. The controlled assessment portfolio is worth 20% of your total A Level award (50 marks, representing around 60 hours of guided development based on the pre-released CCEA client case study).
The Design stage is the second formal phase of your portfolio. In the Analysis stage, you identified what the client's business problems are. In Design, you create the complete technical blueprint showing exactly how your software solution will be built before you touch any database software. Think of it like building a house: you would never lay bricks without an architect’s blueprint. A detailed design ensures your final database is robust, secure, and perfectly tailored to your client’s needs!
1. Architectural & Process Design
Process design visualises how data enters, moves through, transforms within, and leaves your software system.
Data Flow Diagrams (DFDs)
A Data Flow Diagram (DFD) tracks the flow of data through your system without worrying about hardware or technical programming details. For your CCEA portfolio, you must produce balanced, hierarchical DFDs:
• Level 0 (Context Diagram): Provides a high-level overview. The entire system is represented as a single central process circle/box, surrounded by external entities (or terminators, such as a Customer, Manager, or Supplier) that send data into or receive data from the system.
• Level 1 Diagram: Decomposes (breaks down) the Level 0 process into the core sub-processes (such as 1.0 Process Booking, 2.0 Calculate Subtotal, 3.0 Generate Invoice). It also shows internal data stores (tables/files) and named data flows (arrows indicating the exact data being transferred).
Standard DFD Symbols to Use:
• External Entity: Solid rectangle (represents an external person, organization, or system interacting with your software).
• Process: Rounded rectangle or circle (represents an action that transforms incoming data into outgoing data).
• Data Store: Open-ended rectangle or parallel horizontal lines (represents where data is held, such as a database table).
• Data Flow: Arrow labelled with a descriptive noun phrase showing what data is moving (e.g., Payment Details, Booking Confirmation).
Top Tip on DFD Balancing: Ensure consistency between levels! Any input data flow entering Level 0 must also appear entering the sub-processes in Level 1. Do not magically create new external inputs in Level 1 that were missing from Level 0.
System Process Specification & Algorithms
Every automated calculation and business rule in the case study needs a clear, unambiguous logic specification. You should express these using pseudocode or structured flowcharts.
Common logic you must specify includes:
• Subtotal & VAT Calculations: e.g., calculating item totals, applying standard VAT rates (such as \( \text{Total} = \text{Subtotal} \times 1.20 \)), and formatting currency.
• Discount & Loyalty Point Logic: Conditional checks (e.g., IF LoyaltyPoints >= 100 THEN ApplyDiscount()).
• Booking Validation: Checking date availability before confirming a reservation.
Key Takeaway: Process design maps out data journeys (DFDs) and exact calculation logic (algorithms) before building the database.
2. Relational Database Design & Normalisation
A relational database avoids duplicate data, ensures data integrity, and makes querying fast and reliable. This section is heavily weighted in your portfolio.
Entity-Relationship (ER) Modelling
An Entity-Relationship Diagram (ERD) models the tables (entities) in your database and the relationships between them.
• Entity: A real-world object or concept about which data is stored (e.g., tblCustomer, tblBooking, tblStaff).
• Primary Key (PK): A field (or combination of fields) that uniquely identifies each record in a table (e.g., CustomerID).
• Foreign Key (FK): A primary key from one table placed into another table to establish a relationship.
• Degrees of Relationship:
- One-to-One (1:1): Rarely used (e.g., one employee to one company car).
- One-to-Many (1:M): The most common relationship (e.g., one Customer can make many Bookings).
- Many-to-Many (M:N): Not permitted in a finalized relational database (e.g., many Students take many Courses). You must resolve M:N relationships by creating a linking entity (associative table) in the middle, turning one M:N into two 1:M relationships.
The Normalisation Process (0NF to 3NF)
Normalisation is a formal, step-by-step mathematical technique used to organise database fields, eliminating data redundancy and preventing update, insertion, and deletion anomalies.
Memory Trick: To reach 3NF, remember this classic database phrase: "The Key (1NF), the Whole Key (2NF), and Nothing but the Key (3NF)."
Step 0: Unnormalised Form (0NF)
A flat-file data structure containing repeating groups of data (e.g., multiple items listed on a single invoice line) or non-atomic values.
Step 1: First Normal Form (1NF)
• Eliminate all repeating groups.
• Ensure all data attributes are atomic (indivisible, e.g., splitting a full name into FirstName and Surname).
• Identify a suitable Primary Key (often a Compound Key made of two or more fields at this stage).
Step 2: Second Normal Form (2NF)
• The data must already be in 1NF.
• Remove all partial key dependencies: any non-key field that depends on only part of a compound primary key must be moved into its own separate table.
• In 2NF, every non-key field must be fully functionally dependent on the entire primary key.
Step 3: Third Normal Form (3NF)
• The data must already be in 2NF.
• Remove all transitive dependencies: no non-key field can depend on another non-key field (e.g., SupplierAddress depends on SupplierID, not the primary key ProductID).
• Non-key fields must depend on nothing but the primary key.
Data Dictionary (Table Definitions)
You must produce a comprehensive Data Dictionary for every single table in your normalised database. For each table, specify the following technical metadata:
• Field Name: Standard naming conventions (e.g., custID, bookingDate).
• Data Type: Short Text, Long Text, Number/Integer, Currency, Date/Time, AutoNumber, Boolean/Yes/No.
• Field Size / Length: Specific allocated storage (e.g., Short Text size 30 for Surname; Integer for quantities).
• Validation Rule & Validation Text: Exact expressions and user-friendly error messages:
- Range check: >= 1 AND <= 100 ("Quantity must be between 1 and 100")
- Format / Input Mask check: >LL00\ 0LL (for UK postcodes)
- Presence / Required check: Required = Yes ("Field cannot be left blank")
- Lookup check: Restricting input to specific preset values.
• Key Type: Clearly state whether each field is a Primary Key (PK), Foreign Key (FK), Compound Key, or non-key attribute.
• Description / Purpose: A clear explanation of what the field represents within the business context.
Key Takeaway: Normalisation systematically refines data from 0NF to 3NF to eliminate redundancy, and data dictionaries define the strict rules and types for every field.
3. Human-Computer Interface (HCI) Design & Storyboarding
The client needs an interface that is intuitive, accessible, and error-free. In your portfolio, you must show clear wireframes and screen layouts.
Screen Wireframes & Form Layouts
Wireframes act as visual sketches for every input screen, dialog box, and main navigation switchboard.
When designing forms, explicitly show:
• Control Types: Clearly distinguish between text boxes, combo boxes (drop-down lists), radio buttons, check boxes, and command buttons.
• UI Conventions & Aesthetics: Consistent colour schemes, readable font styles/sizes, appropriate white space, and clear form titles.
• Standard Action Buttons: Consistent button placement across all screens (e.g., Save Record, Delete, Previous, Next, Search, Close Form).
• Accessibility Features: High contrast colour options, sensible tab ordering (moving logically from field to field using the Tab key), and clear labelling for screen readers.
Output & Report Designs
Software is only as good as the information it outputs. You must design wireframes for all printed or on-screen reports (such as customer invoices, receipts, and management audit summaries).
Make sure your report wireframes clearly indicate:
• Header & Footer Bands: Report Header (title/logo), Page Header (column headings), Page Footer (page numbers: "Page " & [Page] & " of " & [Pages]), and Report Footer (grand totals).
• Group Headers/Footers: How data is grouped (e.g., grouping bookings by CustomerID) and subtotal calculations.
• Calculated Controls: Explicitly identifying where summary totals, counts, and VAT figures are displayed.
Navigation Structure Chart / Storyboard
A Navigation Structure Chart (or Storyboard map) shows the high-level hierarchy of the entire application. It illustrates how a user starts at the Main Switchboard/Login Screen and transitions seamlessly between sub-menus, data entry forms, search queries, and reports using button triggers.
Key Takeaway: Wireframes, report layouts, and storyboards prove that you have planned an accessible, user-friendly, and logical interface before building it.
4. Query and Automation Design
Your database must extract meaningful information and automate repetitive tasks for the client.
Query Specifications
In the design stage, detail every query your system requires, specifying the tables used, fields displayed, search criteria, and sort orders.
• Select Queries: Used to retrieve and display data meeting specific criteria.
- Parameterised Queries: Prompt the user to enter criteria at runtime (e.g., [Enter Start Date:]).
- Calculated Fields: Generating new values on the fly (e.g., LineTotal: [Quantity] * [UnitPrice]).
• Action Queries: Used to alter database data in bulk:
- Append Query: Adds records from one table to another (e.g., archiving old completed bookings).
- Update Query: Modifies existing data across multiple records (e.g., increasing all product prices by \(5\%\)).
- Delete Query: Permanently removes specific records meeting criteria.
- Make-Table Query: Creates an entirely new table based on query results.
Automation Logic & Event Triggers
Automation makes the application seamless for the end-user by linking interface events directly to macros or code.
In your design, specify:
• Event Triggers: What user action fires the automation? Examples include OnClick (clicking a button), AfterUpdate (after data is entered into a field), or OnLoad (when a form opens).
• Macro / Code Actions: The exact sequence of operations performed (e.g., OpenReport, ApplyFilter, Requery, CloseForm, or SendEmailNotification).
Key Takeaway: Designing queries and automated event triggers beforehand ensures your database answers every business question and automates client workflows.
5. Common Pitfalls & Success Checklist
Don't worry if this stage feels detailed—following this quick review checklist will keep your portfolio on track for the highest mark band!
Common Mistakes to Avoid:
• Generic Mock-ups: Drawing empty boxes for UI designs without showing exact field names, control types (combo boxes vs text boxes), or button triggers.
• Vague Normalisation Explanations: Simply claiming a table is in 3NF without clearly demonstrating how partial key dependencies were eliminated at 2NF and transitive dependencies at 3NF.
• Unbalanced DFDs: Introducing new data flows or entities in Level 1 that were never declared in your Level 0 Context Diagram.
• Incomplete Data Dictionaries: Leaving out validation rules, input masks, or foreign key markers.
• Ignoring the Case Study: Designing a generic system rather than directly solving the specific business problems described in the CCEA pre-released case study.
Design Stage Quick Review Checklist:
1. Are my Level 0 and Level 1 DFDs fully balanced and clear?
2. Have I fully documented normalisation from 0NF to 1NF, 2NF, and 3NF with written justifications?
3. Does my ERD have primary keys, foreign keys, and resolved M:N relationships?
4. Is every field fully detailed in the Data Dictionary (including data types, sizes, and validation expressions)?
5. Are wireframes created for all forms and reports with navigation flow mapped out?
6. Are all select queries, action queries, and event-driven macros fully planned?