BTEC Level 4 Unit 4 Database Design and Development Assignment Answer Guide

This guide covers BTEC Level 4 Database Design and Development, Unit 4 (code A/618/7400) of the Pearson BTEC Higher Nationals in Computing. It explains every learning outcome and Pass, Merit and Distinction criterion, shows how to structure each part of your report, and works through a normalisation and SQL example so you can design, build, test and document your own database with confidence.

What the Unit 4 Database Design and Development assignment asks you to do

You are usually placed in the role of a database developer for an organisation that needs to replace spreadsheets or paper records with a relational database. Scenarios include a food delivery company tracking customers, orders, food items, riders and motorbikes, or a sports organisation tracking teams, players and matches. Depending on your centre, the work is split into one or two assignments that ask you to:

• identify user and system requirements from the scenario;
• produce an entity relationship diagram (ERD) with keys and cardinalities, then a normalised logical design;
• design interfaces, outputs and data validations;
• build the database in a DBMS such as MS SQL Server or MySQL and write SQL queries that produce management information;
• test the system against the requirements;
• produce technical and user documentation, including data flow diagrams and flowcharts.

Some centres set a design-only assignment first (LO1 plus a design document) and a development, testing and documentation assignment second. Check which criteria your own brief targets.

Learning outcomes for Unit 4 Database Design and Development

Pass, Merit and Distinction criteria explained

How to answer LO1: designing the relational database

1. Write user requirements (what staff need to do) and system requirements (what the software and hardware must support) as numbered lists so you can refer back to them.
2. Identify entities and attributes from the scenario nouns.
3. Draw the conceptual ERD with crow’s foot notation; resolve many-to-many relationships with a link entity.
4. Normalise to 3NF, showing each stage.
5. Produce a data dictionary: field name, data type, size, key, validation rule.
6. Add wireframes for input forms and layouts for key reports.

How to answer LO2: developing the database and writing SQL

Build the tables exactly as designed, with primary keys, foreign keys and constraints. Then show that the system works through annotated screenshots and SQL.

• DDL: CREATE TABLE statements with data types, NOT NULL, CHECK and FOREIGN KEY constraints.
• DML: INSERT enough realistic records to make queries meaningful (at least 10 to 15 rows in main tables).
• Queries: joins across three or more tables, aggregates with GROUP BY, filters with WHERE and HAVING.
• Security and maintenance (M2): logins and roles with GRANT and REVOKE, backup schedule, index on frequently searched fields.

How to answer LO3: testing against requirements

Link every test to a numbered requirement. Use a table with columns for test number, requirement, description, test data, expected result, actual result, pass/fail and action taken. Include three types of data:

• Normal: valid data the system should accept (a quantity of 3).
• Boundary: values at the edge of a rule (quantity 1 and quantity 20 if the rule is 1 to 20).
• Erroneous: data that should be rejected (quantity 0, a letter in a phone number field).

For M4, explain why you chose each type and admit any gaps, such as not testing with many users at once.

How to answer LO4: technical and user documentation

The user guide is for staff with no database knowledge: step-by-step instructions with screenshots for adding a customer, placing an order and running a report. The technical guide is for a future developer: ERD, data dictionary, SQL scripts, security set-up and backup procedure. For M5 add a context DFD and a Level 1 DFD (in the notation your brief specifies, for example Gane and Sarson) and flowcharts for the main processes.

Worked example: normalising to 3NF and querying

A music school records lesson bookings on a spreadsheet:

BookingID, LessonDate, StudentID, StudentName, StudentPhone, TutorID, TutorName, Instrument, RoomNo

1NF. Each field already holds a single value and there are no repeating groups, so the data is in 1NF with BookingID as the primary key.

2NF. The key is a single field, so there are no partial dependencies; the table is in 2NF.

3NF. Some non-key fields depend on other non-key fields (transitive dependencies): StudentName and StudentPhone depend on StudentID; TutorName and Instrument depend on TutorID. Move them out:

• Student (StudentID, StudentName, StudentPhone)
• Tutor (TutorID, TutorName, Instrument)
• Booking (BookingID, LessonDate, RoomNo, StudentID*, TutorID*)

Each student and tutor detail is now stored once, which removes update anomalies (changing a phone number in one place only) and deletion anomalies (cancelling a new tutor’s only booking no longer loses that tutor’s details).

SQL for the Booking table:

CREATE TABLE Booking (
  BookingID   INT PRIMARY KEY,
  LessonDate  DATE NOT NULL,
  RoomNo      VARCHAR(5) NOT NULL,
  StudentID   INT NOT NULL,
  TutorID     INT NOT NULL,
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
  FOREIGN KEY (TutorID) REFERENCES Tutor(TutorID)
);

Management information query: number of lessons per tutor in March 2026, busiest first.

SELECT t.TutorName, t.Instrument, COUNT(b.BookingID) AS Lessons
FROM Tutor t
JOIN Booking b ON b.TutorID = t.TutorID
WHERE b.LessonDate BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY t.TutorName, t.Instrument
ORDER BY Lessons DESC;

For M3, follow the output with a sentence on what it means: for example, if one tutor teaches twice as many lessons as the others, the school may need to recruit a second tutor for that instrument.

Common mistakes that cost marks

• Drawing an ERD with unresolved many-to-many relationships or missing cardinalities.
• Claiming tables are in 3NF without showing the UNF, 1NF and 2NF stages.
• Building tables that do not match the design, with no explanation of the changes.
• Screenshots with no annotation explaining what they prove.
• Test plans that only use normal data, or that are not linked to requirements.
• Writing evaluation as a description of what you did rather than a judgement of how well it meets requirements.
• Forgetting security and maintenance, which M2 needs.

How to move from Merit to Distinction

All three Distinction criteria are evaluations, so they need judgement backed by evidence. For D1, take each numbered requirement and state whether the design meets it fully, partly or not at all, and why. For D2, do the same for the finished system using your test results, then suggest specific improvements, such as storing delivery timestamps or adding a stored procedure to automate order totals. For D3, look ahead: what happens when the organisation opens more branches, adds online ordering or needs to comply with UK GDPR and the Data Protection Act 2018? Recommend changes to performance (indexing), security (role-based access, encryption of personal data) and maintenance (scheduled backups) and explain why each matters.

FAQs

How many tables do I need?

The Pearson P1 criterion asks for at least four interrelated tables, but many centre briefs ask for six or more. Follow your brief.

Which DBMS should I use for Unit 4?

Use the one your centre specifies, often MS SQL Server or MySQL. Whatever you choose, include your SQL code as well as screenshots of the interface.

What is the difference between user and system requirements?

User requirements describe what people need to do (record an order, view daily sales). System requirements describe what the system must provide to support that (response time, storage, security, the DBMS and hardware).

Do I need DFDs and flowcharts?

They are needed for M5, and some briefs ask for them in a design document. Include at least a context DFD, a Level 1 DFD and flowcharts for the main processes.

What counts as management information?

Query results that help a manager decide something, such as sales by item, busiest times or staff workload, rather than a simple list of all records.

Need one-to-one support from a subject specialist? Our BTEC Level 4 (HNC) assignment help service covers planning, feedback on drafts and referencing.

If you need support with your database design or SQL, you can get expert help with your BTEC assignment. You may also find our guides to Unit 30 Application Development and HND Computing and Software Development useful.