Relational Database Systems

The candidate must demonstrate knowledge and understanding, practical skills and problem solving based on the following content statements:

Database Fundamentals
♦ Description of need for and methods of achieving good database design, including consideration of the following: data duplication, data insertion, data deletion, data modification and problems associated with meaningful identifiers.

Entities and Data Relationships
♦ Description and exemplification of a data entity, in terms of the following attributes: name, multi-valued attributes, single valued attributes, data type (text, integer, real, object, link, boolean, date, time).

♦ Definition of data domain including domain constraints.

♦ Description of methods of achieving good database design, including appropriate choice of entities and entity relationships.

♦ Description, exemplification and identification of entity relationships, including: cardinality (one-to-one, one-to-many, many-to-many), use of entity occurrence modeling, use of entity relationship diagrams.

Data Modelling Concepts
♦ Definition and exemplification of data relationships using consistent notation and appropriate nomenclature (defining relation names, primary keys, foreign keys and non-key column names).

♦ Design and creation of linked data tables, including consideration of: table names, uniquely named columns, choice of primary key with one or two attributes including non-meaningful identifiers, compound key, surrogate keys, foreign keys (domain constraints and null values).

♦ Definition of referential integrity.

♦ Definition of Entity Integrity: non-null primary key, no multi-valued attributes.

♦ Exemplification of data retrieval, including consideration of: user views and queries, answer tables.

♦ Description of need for, and exemplification of data dictionaries including name, type, size, validation, index/key.

Normalisation

♦ Definitions of normal forms: UNF, first normal form (1NF), second normal form (2NF), third normal form (3NF).

♦ Creation of UNF from source documents.

♦ Normalisation to 1NF, by identifying and eliminating repeating groups.

♦ Description of problems of 1NF.

♦ Normalisation to 2NF by identifying partial key dependency.

♦ Description of problems of 2NF.

♦ Normalisation to 3NF by identifying non-key dependency.

Implementation

♦ Implementation of database system based on a data model, including entity/relationship diagram and data dictionary.

♦ Description and implementation of complex queries including: sorting (multiple fields, ascending/descending), searching (multiple fields, across linked/related tables), calculating and summarising (including count, sum and average).

♦ Use of related tables as sources for data entry (including lookups).

♦ Enforcement of data integrity through validation. Implementation of simple macros and scripting for navigation.