Unit 4: Database Design & Development

Authorised Assignment Brief

Student Name/ID
Number

Unit Number and Unit 4: Database Design & Development

Title
Academic Year
Unit Tutor

Assignment Title Developing A Relational Database System

Issue Date
Submission Date

Submission Format

The assignment submission is in the form of:

● a relational database design document to design a relational database system for a
substantial problem. The recommended word limit is 1,000–1,500 words, although you
will not be penalised for exceeding the total word limit
● a database development document to document the development and testing of a
fully-functional relational database system, based on an existing system design. The
recommended word limit is 1,000–1,500 words, although you will not be penalised for
exceeding the total word limit
● user and technical documentation for a fully-functional system, describing how the
system works. The recommended word limit is 1,000–1,500 words, although you will not
be penalised for exceeding the total word limit.
You are required to make use of headings, paragraphs and sub-sections as appropriate,
and all work must be supported with research and referenced using an appropriate
referencing system such as Harvard. You will also need to provide a bibliography using an
appropriate referencing system such as Harvard. Inaccurate use of referencing may lead
to issues of plagiarism if not applied correctly.

Effective assignment design for Higher Nationals in Computing/Computing for England

67
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
Unit Learning Outcomes

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.

Transferable skills and competences developed

Computing-related cognitive skills

● Demonstrate knowledge and understanding of essential facts, concepts, principles and
theories relating to computing and computer applications
● Use such knowledge and understanding in the modelling and design of computer-based
systems for the purposes of comprehension, communication, prediction and the
understanding of trade-offs
● Recognise and analyse criteria and specifications appropriate to specific problems,
and plan strategies for their solutions
● Critical evaluation and testing: analyse the extent to which a computer-based system
meets the criteria defined for its current use and future development
● Methods and tools: deploy appropriate theory, practices and tools for the design,
implementation and evaluation of computer-based systems
Computing-related practical skills
● The ability to specify, design and construct reliable, secure and usable computer-based
systems
● The ability to evaluate systems in terms of quality attributes and possible trade-offs
presented within the given problem
● The ability to deploy effectively the tools used for the construction and documentation
of computer applications, with particular emphasis on understanding the whole process
involved in the effective deployment of computers to solve practical problems
● The ability to critically evaluate and analyse complex problems, including those with
incomplete information, and devise appropriate solutions, within the constraints of
a budget.
Generic skills for employability
● Intellectual skills: critical thinking; making a case; numeracy and literacy
● Self-management: self-awareness and reflection; goal setting and action planning
Independence and adaptability; acting on initiative; innovation and creativity
● Contextual awareness, e.g. the ability to understand and meet the needs of individuals,
business and the community, and to understand how workplaces and organisations
are governed.

Effective assignment design for Higher Nationals in Computing/Computing for England

68
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
 Vocational scenario

You have been employed as a Junior Database Developer by Apex Technology Systems
(ATS) Limited*. ATS provides a range of bespoke desktop-, web- and cloud-based design,
consultancy and development services to both small- and medium-sized clients from across
a diverse range of business sectors.
ATS has received an enquiry from a new client and your project manager has decided that
this would be ideal for you to handle by yourself as your first solo project.
The client is Manzaneque Limited* (ML), a large real-estate company that specialises in
residential, commercial and industrial properties. It has a large IT department, which
supports a range of business operations including development of raw land, property
management, brokerages, lending and other professional services such as lawyers, interior
designers and construction workers. Owing to further expansion, ML has decided to
establish a dedicated IT Helpdesk Team to handle hardware and software problems
concerning the in-house IT systems.
ML have approached the Chief Executive Officer (CEO) of ATS with their requirements and
the CEO has agreed, along with the Chief Technology Officer (CTO) and your line manager,
that you would be the ideal candidate to design and build the new system. Your role will
include designing, developing and implementing database systems based on ML
requirements. You will also be responsible for optimising the database system for
performance efficiency, as well as testing and troubleshooting and performing bug fixes.
The requirements of the system are as follows:
When someone in the company has a problem, they can contact the helpdesk. One of the
five helpdesk operators will log the details of the job, and then allocate the job to one of
several dedicated IT technicians. ML requires an Information System to log and track the
helpdesk queries. This will enable analysts to see how the equipment is performing overall,
and to assess the overall efficiency of the IT technicians in being able to solve problems.
You have been given the following information about the system requirements:
ML contains 15 offices around the country; each office has a name, address, contact phone
and specialisation (land development, property management, brokerages, lending legal,
interior designers and construction). Each office has between 10 and 20 employees, who
have an Employee ID, Name, Contact Phone and Email, Employment Start Date and End
Date. The system should record information about ex-employees also.
When a new call comes into the helpdesk, the names of the caller and helpdesk operator
are logged, along with the time of the call, the serial number of the computer and,
if relevant, the operating system and software being used.
The caller’s name will be checked against a register of all personnel to retrieve the caller’s
ID number, job title and department.
Their equipment will also be checked against a register of equipment to find the equipment
type and make. Their software will be checked to see if it is under a valid licence. Hardware
will be checked to see if it is still under manufacturer warranty.
The equipment register contains the following:
● office hardware: PCs, laptops, printers, peripherals, interactive whiteboards
● network hardware: routers, switches, wireless access points, firewall, cables

Effective assignment design for Higher Nationals in Computing/Computing for England

69
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
● software: spreadsheets, word processors, presentation software, operating systems,
time management, project management
● all relevant serial numbers, licence numbers, manufacturer, make and model details.
Every call is logged, and each problem is given a problem number, which is supplied to the
caller so it can be quoted on any subsequent calls about the same problem. The date and
time of the call are also logged.
The helpdesk operator will also record notes and descriptions of the problem. A reason for
each call is always recorded even if it is just a note to indicate the urgency or distress of the
caller, (e.g. in the case of a follow-up call).
When a problem is first reported, the helpdesk operator will also allocate the problem to an
available IT technician who has a specialism in that area. Different IT technicians can have
different specialisms e.g. Technician A can specialise in routers, laptops, printers and Office
software, Technician B is a specialist in printers only.
Once a problem type has been identified, the system will display a list of only those IT
technicians with the skills to solve that problem.
The system will also list how many problems the technician is currently addressing. This
allows for load balancing, so if there is more than one technician for a given problem type,
the one with the lightest workload can be assigned.
When a problem is eventually resolved, the helpdesk operator or the technician will log the
date and time it is resolved, and record some indication of how the problem was resolved
and the time taken (in hours) to resolve the problem.
The system will then need to provide the following reports to the management of ML,
to see how well the technicians and equipment are functioning:
● a summary report of the total calls logged in a given month, showing the number of
open and closed cases, as well as the total time spent on each closed case.
● a summary of all the technicians’ job sheets in a given month, showing the technician’s
name, number of jobs worked, number of open and closed cases and total time spent
● a summary of the total jobs logged by each office, showing the office name, number of
hardware faults, number of software faults, time spent and number of different IT
technicians assigned
● a summary of the total jobs assigned to each item in the equipment register, to identify
any items that need replacing or repaired under warranty/licence
● a report for a selected technician to show detailed information on the jobs they have
worked on each month, including the office, job number and equipment details; the
report should also show the total number of open and closed jobs and how long they
have spent in total and on average on a job
● a summary report for a selected office to show detailed information on the jobs they
have logged each month, showing the total number of open and closed jobs as well as
summary counts of the number of jobs per register item to identify items of equipment
that are regularly failing and will need replacing.
The system initially does not need to model all of this data but it should contain the basic
relational database design to store a core level of pilot data and new data can be added at
a later date.

Effective assignment design for Higher Nationals in Computing/Computing for England

70
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
 The system should implement some form of application security measures to ensure that:
● helpdesk operators can log jobs but cannot open any reports
● office managers cannot log jobs but can view all the reports.
The system should use either inbuilt security functions in the software of your choosing or it
can be implemented using your own security functionality.
The system should also implement some form of database maintenance routine, that
includes:
● backing up the database
● restoring the database from the backups
● compacting the database.
The maintenance backup operations should run from a command on the main control page
and should be accessible by everyone. Restoration and compaction of a database should be
performed only by helpdesk operators.
Before beginning the implementation of the system, the boards of both ML and ATS are
interested in your designs for a relational database system, including any user interface that
you intend to produce.
Once you have implemented the database system based on your designs, the boards of
both ML and ATS are keen that the system be thoroughly tested to make sure that it meets
the ML client requirements.
Finally, ML would like to examine any user and technical documentation that you produce
so that when the database application is implemented by the ML helpdesk team, they have
a thorough understanding of not only how the system works but also how it was designed
and implemented so that they can identify any issues or further requirements. The user
documentation should clearly show how to use the system, menu operations and system
outputs, as well as other functions. Technical documentation should clearly show the
movement of data through the system.
*ATS and ML are not real companies. Real or fictitious companies can be chosen to meet
local assessment needs.

Assignment activity and guidance

Relational Database Design Document

You will create a design document for the boards of ML and ATS in which you will design a
relational database system for the given problem. You will:
● design a relational database system using appropriate design tools and techniques
● include at least four interrelated tables
● provide clear statements of user requirements
● provide clear statements of system requirements.
The comprehensive design for a fully-functional system will include:
● interface designs
● output designs
● data validation designs
● data normalisation designs.

Effective assignment design for Higher Nationals in Computing/Computing for England

71
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
You should then go on to evaluate the effectiveness of the design in relation to:
● user requirements
● system requirements.
Database Development Document
You will create a database development document for the boards of ML and ATS in which
you will develop a fully-functional relational database system, based on an existing system
design. Your document will show:
● the development of the database system
● evidence of the following:
o user interface
o output
o data validations
o querying across multiple tables
● evidence of the implementation of a query language into the relational database system
● implementation of a fully-functional database, which includes:
o system security
o database maintenance
● an assessment of whether meaningful data has been extracted through the use of
query tools to produce appropriate management information
● testing of the system against:
o user requirements
o system requirements
● an assessment of the effectiveness of the testing
● an explanation of the choice of test data used.
Finally, your development document will evaluate the effectiveness of the database solution
in relation to user and system requirements and suggest improvements.
User and Technical Documentation
You will create user and technical documentation for the boards of ML and ATS. The
documentation of the fully functional system will describe how the system works and
should include:
● data flow diagrams
● flowcharts.
At the end of the documentation, you will evaluate the database in terms of improvements
needed to ensure the continued effectiveness of the system.

Effective assignment design for Higher Nationals in Computing/Computing for England

72
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
 Recommended resources
Please note this is not a definitive list of resources but it will help you begin your research
by acting as a starting point of reference.

Websites
[Link] (2023) How to design an effective relational database
[online].Available at: [Link]
database/ [Accessed 10 June 2024]
[Link] (n.d.) Data Modeling: Conceptual vs Logical vs Physical
Data Model [online]. Available at: [Link]
modeling/conceptual-vs-logical-vs-physical-data-model [Accessed 10 June 2024]
[Link] (2022) Database design basics [online].
Available at: [Link]
1e30-401a-8084-bd4f9c9ca1f5 [Accessed 10 June 2024]
[Link] (2022) Database (Data) Testing Tutorial with Sample Test Cases
[online]. Available at: [Link] [Accessed 10 June 2024]
[Link] (2022) Database Design in DBMS Tutorial: Learn Data
Modeling [online]. Available at: [Link]
[Accessed 10 June 2024]
[Link] (n.d.) What is a relational database? [online]. Available at:
[Link] [Accessed 10 June 2024]
[Link] (2021) Complete Guide to Database Schema Design [online].
Available at: [Link]
guide/ [Accessed 10 August 2024]
[Link] (2022) Database Structure and Design Tutorial [online].
Available at: [Link]
[Accessed 10 June 2024]
[Link] (n.d.) What is a relational database (RDBMS)? [online].
Available at: [Link]
[Accessed 10 June 2024]
[Link] (2022) Database Testing Complete Guide (Why, What,
And How To Test Data) [online]. Available at: [Link]
testing-process/ [Accessed 10 June 2024]
[Link] (n.d.) SQL Tutorial [online].
Available at: [Link] [Accessed 10 June 2024]
Journals and articles
Batra, D. & Davis, J. (1992). Conceptual data modelling in database design: similarities and
differences between expert and novice designers. International Journal of Man-Machine
Studies, Volume 37, Issue 1, 1992, pp. 83–101. Available at: [Link]
7373(92)90092-Y [Accessed 10 June 2024]
Bertino, E. and Sandhu, R., 2005. Database security-concepts, approaches, and challenges.
IEEE Transactions on Dependable and secure computing, 2(1), pp.2–19. Available at:
[Link] [Accessed 10 June 2024]

Effective assignment design for Higher Nationals in Computing/Computing for England

73
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
Chandra, A.K., 1988, March. Theory of database queries. In Proceedings of the seventh
ACM SIGACT-SIGMOD-SIGART symposium on Principles of database systems (pp. 1–9).
Available at: [Link] [Accessed 10 June 2024]
Gunjal, B. (2003). Database System: Concepts and Design. Proceedings of 24th IASLIC–SIG-
2003. Available at:
[Link]
sign [Accessed 10 June 2024]
Kanellakis, P. C., 1990. Elements of relational database theory. In Formal models and
semantics (pp. 1073–1156). Elsevier. Available at:
[Link]
[Accessed 10 June 2024]
Kaur, Taranpreet & Sehra, Sumeet Kaur. (2015). Designing and Development of Database
Testing Tool. International Journal of Computer Applications (0975 – 8887) Volume 120 –
No.19. 14. doi:10.5120/21334-4330. Available at:
[Link]
[Accessed 10 June 2024]
Kaur, T. & Singh B. (2003). Testing of Databases. IJISET – International Journal of Innovative
Science, Engineering & Technology, Vol. 1 Issue 6. ISSN 2348 – 7968. Available at:
[Link] [Accessed 10 June 2024]
Kraleva, Radoslava & Kralev, Velin & Sinyagina, Nina & Koprinkova-Hristova, Petia &
Bocheva, Nadejda. (2018). Design and Analysis of a Relational Database for Behavioral
Experiments Data Processing. International Journal of Online Engineering (iJOE). 14. 117.
Available at: [Link] [Accessed 10 June 2024]
Kumar, K. and Azad, S.K., 2017, October. Database normalization design pattern. In 2017
4th IEEE Uttar Pradesh Section International Conference on Electrical, Computer and
Electronics (UPCON) (pp. 318–322). IEEE. Available at:
[Link] [Accessed 10 June 2024]
Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology Research.
Volume 6. Available at: [Link]
[Accessed 10 June 2024]
Pernul, G., 1994. Database security. In advances in Computers (Vol. 38, pp. 1-72). Elsevier.
Available at: [Link] E. and Martino, L.,
1991. Object-oriented database management systems: concepts and issues. Computer,
24(4), pp.33–47. Available at: [Link]
[Accessed 10 June 2024]
Rolik, O., Amons, O., Ulianytska, K. and Kolesnik, V., 2021. Modernization of the Second
Normal Form and Boyce-Codd Normal Form for Relational Theory. In Advances in Computer
Science for Engineering and Education III 3 (pp. 296–305). Springer International Publishing.
Available at: [Link]
[Accessed 10 June 2024]
Textbooks
Beynon-Davies, P., 2004. Normalisation. In Database Systems (pp. 269–291). Palgrave,
London. [Link]
Captain, F. (2013) Six-Step Relational Database Design™: A step by step approach to relational
database design and development, 2nd edn, CreateSpace Independent Publishing Platform.

Effective assignment design for Higher Nationals in Computing/Computing for England

74
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
 Harrington, J. L., 2016. Relational database design and implementation. Morgan Kaufmann.
Harrington, J. L., 2002. Relational database design clearly explained. Elsevier.
Hernandez, M. (2003) Database Design for Mere Mortals: A Hands-On Guide to Relational
Database Design, 2nd edn, Addison Wesley.
Stephens, R. (2008) Begin Database Design W / WS (Wrox Programmer to Programmer), 1st edn,
Jossey-Bass.
Walters, R., Fritchey, G. (2012). Common Database Maintenance Tasks. In: Beginning SQL
Server 2012 Administration. Apress, Berkeley, CA. [Link]
6_10
HN Global
Reading lists. Available at:
[Link]
Student Resource Library. Available at:
[Link]

Effective assignment design for Higher Nationals in Computing/Computing for England

75
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024
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, containing which includes interface requirements.
at least four interrelated and output designs, data
tables, with clear validations and data
statements of user and normalisation.
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 system, of the database solution in
user interface, output which includes system relation to user and system
and data validations, and security and database requirements and suggest
querying across multiple 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 against M4 Assess the effectiveness
user and system of the testing, including an
requirements. explanation of the choice
of test data used.
LO4 Produce technical and user documentation.
P5 Produce technical and M5 Produce technical and D3 Evaluate the database
user documentation. user documentation for a in terms of improvements
fully-functional system, needed to ensure the
including data flow diagrams continued effectiveness
and flowcharts, describing of the system.
how the system works.

Effective assignment design for Higher Nationals in Computing/Computing for England

76
Authorised Assignment Briefs – Issue 2 – July 2024 © Pearson Education Limited 2024