Topic

Database Design and Development (DDD) – National 5 (v2.1)

Analysis

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

Design

 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

Implementation
  • database structure: field, record, file
  • field types
    • text
    • number
    • date/time
    • graphic
    • 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.

Testing

  

Describe and exemplify testing:

  • SQL operations work correctly at this level

Evaluation

  

Evaluate solution in terms of:

  • fitness for purpose
  • accuracy of output

* Taken from National 4 Specifications