Database concepts

Modelling

Core info and definitions

When creating a database, you must identify the entities. An entity is a "category of object, person, event or thing of interest to an organisation about which data to be recorded". These entities are presented as tables. Column headings are fields/attributes, and each row is a record.

Entities are described in written form as:
EntityName(Attr1, Attr2, Attr3, ...)

Referential integrity ensures that a foreign key in one table always refers to the primary key of another table. This prevents orphan records, maintains data consistency across tables, and enforces logical relationships between entities.

Keys

A primary key is a unique identifier for each row.

A composite primary key is a primary key made up of two fields. These two fields do not need to be unique on their own, but when combined, they must be unique.

A secondary key is another field of a table that is indexed and used to search it faster - e.g. searching by surname. They do not need to be unique for each record. However, the indexing table uses up extra storage space.

A foreign key is a field that contains the primary key of another table. These are used to draw relationships between tables.

Entity Relationship Diagrams

One to one is when one record of a table is related to exactly one record of another table.

One to many (or many to one if reversed) is when one record of a table can be related to multiple records of another table.

Many to many is when many records of a table can be related to many records of another table. This cannot be achieved without significant redundancy normally, so an intermediate linking table with composite primary keys from both tables is used to minimise data redundancy.