Database Design Development

Unit Content

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 UK General Data Protection Regulation (UK GDPR) 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

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