Unit 4: Database Design &

Development

Unit code A/618/7400

Unit type Core

Unit level 4

Credit value 15

Introduction
Organisations depend on their databases for providing information that is essential
for their day-to-day operations and to help them take advantage of today’s rapidly
growing and maturing e-commerce opportunities. An understanding of database
tools and technologies is an essential skill for designing and developing systems to
support them.
As applications get increasingly more sophisticated, database systems continue to
demand more complex data structures and interfaces. Most organisations collect and
store large volumes of data, either on their own systems or in the cloud, and this data
is used not just for the operational running of their business but is also mined for
other more intelligent and complex applications. Databases stand as the back-end of
most systems used by organisations for their operations.
Database design and development is a fundamental and highly beneficial skill for
computing students to master, regardless of their specialism.
The aim of this unit is to give students opportunities to develop an understanding of
the concepts and issues relating to database design and development. It will also
provide the practical skills needed to be able to translate that understanding into the
design and creation of complex databases.
Topics covered in this unit are: examination of different design tools and techniques;
examination of different development software options; consideration of the
development features of a fully-functional robust solution covering data integrity, data
validation, data consistency, data security and advanced database querying facilities
across multiple tables; appropriate user interfaces for databases and for other
externally linked systems; creating complex reports/dashboards, testing the system
against the user and system requirements; and elements of complete system
documentation.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

106 Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
On successful completion of the unit, students will be able to use appropriate tools to
design and develop a relational database system for a substantial problem. They will
be able to test the system to ensure that it meets user and system requirements, and
fully document the system by providing technical and user documentation. For
practical purposes, this unit covers relational databases and related tools and
techniques. A brief overview of object-oriented databases will also be covered. As a
result, students will develop skills such as communication literacy, critical thinking,
analysis, reasoning and interpretation, which are crucial for gaining employment and
developing academic competence.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
107
Learning Outcomes
By the end of this unit students will be able to:
LO1 Use an appropriate design tool to design a relational database system for a
substantial problem
LO2 Develop a fully-functional relational database system, based on an existing
system design
LO3 Test the system against user and system requirements
LO4 Produce technical and user documentation.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

108 Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
Essential Content

LO1 Use an appropriate design tool to design a relational database system for a
substantial problem

Database design:
Principles and uses of relational and non-relational databases.
The role of database systems, e.g. as back-end systems, in e-commerce, for data
mining applications, blockchain.
Determining user and system requirements.
Design tools and techniques for a relational database system.
Logical design for relational databases, including structured data in tables, data
elements, data types, indexes, primary and foreign keys, entity relationship
modelling, referential integrity, data normalisation to third normal form.
Designs for data integrity, data validations, data security and data controls. User
interface design.
Output designs for user requirements.
Overview of object-oriented databases and their design tools.

LO2 Develop a fully-functional relational database system, based on an existing

system design

Implementation:
Consideration of database and platform options for system development.
Examination of different software development options for developing the
relational database system.
Implementation of the physical data model based on the logical model and
linking code to data sets.
Data stores, internal storage and external storage, e.g. the cloud.
Implementation of security elements in databases.
Relational databases with controls like data validation using; input masks,
dropdown lists, option buttons.
Consideration of user interface requirements looking at functionality, reliability,
consistency, performance and accessibility for a range of different users.
Develop effective user interfaces linked with other systems, e.g. internet-based
applications.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
109
 Data manipulation using appropriate query tools, including complex queries to
query across multiple tables and using functions and formulae.
Database maintenance and data manipulation: inserts, updates, amendments,
deletions, data backup and recovery.
System reports using report-writing tools and report generators, dashboards.
Implementation of security elements in a database, including consideration of
permissions, access rights, network vulnerabilities, physical location of data,
multi-tenancy and data separation, encryption.
Consideration of GDPR issues, including data crossing borders and other nations’
data protection regulations.

LO3 Test the system against user and system requirements

Testing methodologies:
Identify elements of the system that need to be tested. Consider data that
should be used to fully test the system.
Match tests against user and system requirements.
Test procedures to be used: test plans, test models, e.g. structural testing,
functional testing; testing documentation.
Functional and system testing and testing the robustness of the system,
including help menus, pop-ups, hot-spots, data validation checks.

LO4 Produce technical and user documentation

Structure and functionality documentation:

Technical and user documentation and their contents.
Technical documentation to include diagrams showing movement of data
through the system and flowcharts describing how the system works.
User documentation, including how to use the system, outputs produced by the
system, menu operations and other functions.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

110 Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
Learning Outcomes and Assessment Criteria

Pass Merit Distinction

LO1 Use an appropriate design tool to design a
relational database system for a substantial problem

P1 Design a relational M1 Produce a D1 Evaluate the effectiveness

database system using comprehensive design for of the design in relation to
appropriate design tools a fully-functional system, user and system
and techniques, which includes interface requirements.
containing at least four and output designs, data
interrelated tables, with validations and data
clear statements of user normalisation.
and system
requirements.

LO2 Develop a fully-functional relational database

system, based on an existing system design LO2 and LO3

P2 Develop the database M2 Implement a fully- D2 Evaluate the effectiveness

system with evidence of functional database of the database solution in
user interface, output system, which includes relation to user and system
and data validations, and system security and requirements and suggest
querying across multiple database maintenance. improvements.
tables.
M3 Assess whether
P3 Implement a query meaningful data has been
language into the extracted through the use
relational database of query tools to produce
system. appropriate management
information.

LO3 Test the system against user and system

requirements

P4 Test the system M4 Assess the

against user and system effectiveness of the testing,
requirements. including an explanation of
the choice of test data
used.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
111
Pass Merit Distinction
LO4 Produce technical and user documentation

P5 Produce technical and M5 Produce technical and D3 Evaluate the database in

user documentation. user documentation for a terms of improvements
fully-functional system, needed to ensure the
including data flow continued effectiveness of
diagrams and flowcharts, the system.
describing how the system
works.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

112 Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
Recommended Resources

Textbooks
Churcher, C. (2012) Beginning Database Design: From Novice to Professional.
2nd edn. Apress.
Connolly, T. and Begg, C. (2014) Database Systems: A Practical Approach to Design,
Implementation and Management. 6th edn. Global Edition. Pearson.
Flejoles, R. P. (2018) Database Theory and Application. Arcler Press.
Karwin, B. (2017) SQL Antipatterns: Avoiding the Pitfalls of Database Programming
Pragmatic Programmers, LLC, The.
Kroemke, D. and Auer, D. (2012) Database Concepts: International Edition.
6th edn. Pearson.

Journals
The Computer Journal – Oxford Academic
International Journal of Database Management (IJDMS)
Journal of Emerging Trends in Computing and Information Sciences
Journal of Systems Analysis and Software Engineering
Systems Journal of Database Management

Web
[Link] Microsoft Virtual Academy Database Development
(Training)
[Link]/ebooks Microsoft Virtual Academy
Microsoft Press
(E-books)
[Link] Database Training
(Tutorials)

Links

This unit links to the following related units:

Unit 11: Strategic Information Systems
Unit 41: Database Management Systems.

Pearson BTEC Levels 4 and 5 Higher Nationals in Computing

Specification – Issue 4 – May 2023 © Pearson Education Limited 2023
113