Database Design and Development

Overview

Framework:
RQF
Level:
Level 2
Unit No:
H/618/3678
Credits:
6
Guided learning hours:
48 hours

Aim

Learners will learn how databases are used. They will design, develop and test a relational database and analyse and present their findings. This unit also allows learners to obtain feedback on their work for review and further development.

Unit Learning Outcomes

1

Know about the structure and principles of databases.

AC 1.1:

A database is a collection of data that is organised in a table consisting of columns (fields) and rows (records) so that it can be easily accessed, managed and updated. Organisations use databases to perform calculations, manage large data sets, present information etc.

Uses: including:

  • Health centres and hospitals, e.g. doctors, patients, appointments
  • Employment, e.g. staff names, payroll, departments
  • Libraries, e.g. members, books, loans

AC 1.2:

Tools and techniques include: table structures, field characteristics, validation rules, indexing, records, relationships, forms,sorts, queries, reports

AC 1.3:

Qualitative and/or quantitative data.

Primary and secondary sources such as: the internet, existing customers, general public, social media, phone use.

Gathering data: questionnaire: online survey, focus groups, observation.

Constraints on data collection: legal implications, data confidentiality, costs of collecting and maintaining the data.

Assessment Criteria

  • 1.1

    Describe the uses and features of databases.

  • 1.2

    Describe the tools and techniques used in databases.

  • 1.3

    Identify data collection methods to gather data from a range of sources.

2

Be able to use database tools to design,develop and test a relational database to meet requirements.

AC 2.1:

Design documentation, to include:

Data dictionary: field names, data types, e.g. alphabetic, numeric, alphanumeric, logical, web, lookup wizards, field sizes, e.g. byte, integer, long integer, single, double and decimal, field formats, e.g. fixed and decimal places, default values, primary and foreign keys.

Data validation: lists, rules, text, presence check, range check, format check.

Entity relationship diagram (ERD), input forms, output screens/reports, test plan with test data.

AC 2.2:

The relational database must use a realistic data set and include at least three tables, queries, data entry-forms, and reports.

Tools and techniques, to include: creating tables, creating fields, validation rules, importing data from external sources, relationships (one-to- many, many-to-many), creating, editing and deleting relationships data entry forms (simple forms, e.g. data entry and main menu), customise forms (e.g. add a new record, print a record, delete a record and navigation).

AC 2.3:

Purpose of testing: functionality, purpose, usability, to include feedback from others, testing data, provide onscreen user navigation and instructions making amendments following testing.

Assessment Criteria

  • 2.1

    Design a relational database system using database design techniques.

  • 2.2

    Develop a relational database.

  • 2.3

    Create a test plan to test a relational database obtaining feedback from others.

3

Be able to use database techniques to analyse and present data.

  • Sorting data: single or multiple fields.
  • Queries: single criterion, multiple criteria using linked tables and making use of logical operators, e.g. AND, OR, NOT and wildcards.
  • Calculations: totals, averages, max, min.
  • Data cleansing and reasons for: manually, using software, saves storage space, removes unnecessary data, removal of inaccurate data.
  • Data mining: extracting patterns from data, automatic collection of data to be analysed for trends and patterns.
  • Reporting: creating and editing reports, customising report templates, customising output forms, presentations, diagrammatic displays.
  • Presenting to different audiences: peers, tutors, managers, customers etc.

Assessment Criteria

  • 3.1

    Apply techniques to analyse and present data in a way that is appropriate for audience and purpose.

4

Be able to review a relational database.

AC 4.1:

Review the finished relational database in relation to: user requirements and user experience (e.g. usability and reliability), fitness for purpose, any constraints (resources, time etc), strengths and improvements.

Assessment Criteria

  • 4.1

    Review how the database meets requirements, making suggestions for further improvements.