Databases

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

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

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.

Describe and exemplify testing:

• SQL operations work correctly at this level

Evaluate solution in terms of:

• fitness for purpose

• accuracy of output