Topic

Database Design and Development (DDD) – Higher (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 exemplify entity-relationship diagrams with three or more entities, indicating:

  • entity name
  • attributes
  • name of relationship
  • cardinality of relationship (one-to-one, one-to-many, many-to-many)

Describe and exemplify an instance using an entity-occurrence diagram.

Describe and exemplify a compound key.

Describe and exemplify a data dictionary with three or more entities:

  • 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 a query:

  • tables and queries
  • fields
  • search criteria
  • sort order
  • calculations
  • grouping

Implementation

 Describe, exemplify and use SQL operations for pre-populated relational databases, with three or more linked tables:

  • UPDATE, SELECT, DELETE, INSERT statements making use of:
    • wildcards
    • aggregate functions (MIN, MAX, AVG, SUM, COUNT)
    • computed values, alias
    • GROUP BY
    • ORDER BY
    • WHERE

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 at this level in terms of:

  • fitness for purpose
  • accuracy of output