Relational databases, flat files, keys, entity relationship modelling, normalisation and indexing

Relational database, flat file, primary key, foreign key, secondary key, entity relationship modelling, normalisation and indexing. See appendix 5f

1.3.2 Databases

Databases — Note 1 of 6

Candidates need to understand what is meant by a database. Candidates should be familiar with basic database terminology such as fields, records and tables. Candidates should know the difference between a flat file and a relational database, and be able to explain the benefits and limitations of each approach. Candidates should know what is meant by a primary key, foreign key and secondary key and how each are used in a database. Candidates should be able produce and follow Entity relationship diagrams which include 1:1, 1:M and M:M relationships. They should be able to identify how tables should be linked.

What you need to know

A database is a structured, persistent store of data. A relational database splits data into linked tables to reduce duplication, using keys to create the links. You need to know the key terms, the difference between flat file and relational databases, and how to draw and read ER diagrams.

What to know

ER diagrams show entities as boxes and relationships as lines: one-to-one (plain line), one-to-many (crow's foot at the 'many' end), many-to-many (crow's foot at both ends).
A many-to-many relationship cannot be implemented directly; it is resolved with a link (junction) table that has a one-to-many relationship with each entity.

Key terms

Exam tipCommon mistake

Database: An organised, persistent collection of data. Data is stored in tables made of records (rows) and fields (columns).
Flat file database: All data is stored in a single table. Simple, but leads to redundant (repeated) data and inconsistencies when data is updated.
Relational database: Data is stored in several tables, each about one entity, linked together through keys. This reduces redundancy and improves consistency.
Primary key: A field (or combination of fields) that uniquely identifies each record in a table.
Composite key: A primary key made from two or more fields combined.
Foreign key: A field in one table that is the primary key of another table, used to link the two tables.
Common mistake: Saying a foreign key must be unique. It is unique in its OWN table (as a primary key), but can appear many times in the linked table.
Secondary key: A field that is indexed so records can be searched for quickly by that field, e.g. surname.
Index: A data structure that stores the position of each record by key value, allowing fast searching and sorting. Indexes speed up searches but take up space and slow down inserts and updates.

Exam technique

Tip:When drawing ER diagrams use the OCR symbols from appendix 5f, and label each entity clearly.