N5 - Database Design and Development

Identify the end-user and functional requirements of a database problem that relates to the implementation at this level.

Describe and identify the implications for individuals and businesses of the General Data Protection Regulation (GDPR) — Regulation (EU) 2016/679 that data must be:

• processed lawfully, fairly and in a transparent manner in relation to individuals

• used for the declared purpose only

• limited to the data needed for the declared purpose

• accurate

• not kept for longer than necessary

• held securely

Describe and exemplify entity relationship diagrams with two entities indicating:

• entity name

• attributes

• relationship (one to many)

Describe and exemplify a data dictionary:

• entity name

• attribute name

• primary and foreign key

• attribute type:

  • text

  • number

  • date

  • time

  • Boolean

• attribute size

• validation:

  • presence check

  • restricted choice

  • field length

  • range

Exemplify a design of a solution to the query:

• multiple tables

• fields

• search criteria

• sort order

• database structure: field, record, file

• field types

  1. text

  2. number

  3. date/time

  4. graphic

  5. calculated

Implement relational databases with two linked tables, to match the design with referential integrity.

Describe, exemplify and implement SQL operations for prepopulated relational databases, with a maximum of two linked tables:

• SELECT

• FROM

• WHERE

• AND, OR, <, >, =

• ORDER BY with a maximum of two fields

• INSERT

• UPDATE

• DELETE

• equi-join between tables

Read and explain code that makes use of the above SQL.

Describe and exemplify testing:

• SQL operations work correctly at this level

Evaluate solution in terms of:

• fitness for purpose

• accuracy of output