02 entities, attributes and keys

Define relational database entities, attributes, and keys. Differentiate primary, foreign, and secondary keys for schema design.

image-00-big-question.png

How does Spotify link millions of songs to artists, albums, and playlists without ever getting them mixed up? How does your online shopping account securely connect to your orders? The answer lies in a logical and precise 'grammar' for structuring data. Today, you will learn the core vocabulary of this grammar - entities, attributes, and keys. As a future Database Administrator or Data Engineer, mastering these concepts is your first step to becoming a true Data Custodian, capable of building the robust systems that power the digital world.

Learning Outcomes

Factual (Knowing that)

Recall the definitions of entity, attribute, primary key, foreign key, and secondary key.

Conceptual (Knowing why)

Describe the role of a primary key in uniquely identifying records within a table.

Explain how a foreign key establishes a relationship and enforces referential integrity between two tables.

Distinguish between a primary key, a secondary (candidate) key, and a composite primary key.

Procedural (Knowing how)

Identify appropriate entities, attributes, and keys from a given real-world scenario.

Apply this knowledge to document a simple database schema using standard notation.

The Grammar of Data: Entities, Attributes & Keys

Introduction: From Nouns to Blueprints

In the last lesson, we established that relational databases are superior to flat files because they reduce data redundancy and improve data integrity. But how do we actually design the structure of these databases? The process begins with identifying the core components of the system we want to model. Think of it like grammar: to write a sentence, you need to understand nouns, verbs, and adjectives. To design a database, you need to understand entities, attributes, and relationships.

This process is the foundational skill of a Database Administrator (DBA) or a Data Engineer. As a Data Custodian, their first job is to create a logical blueprint, or schema, that accurately represents the real-world information the system needs to store.

Core Components of a Database Schema

A database schema is a formal description of the structure of a database. It's defined by its entities, the attributes that describe them, and the relationships between them.

Entities: The 'Things' We Store Data About

An entity is a person, place, object, event, or concept in the user environment about which the organisation wishes to maintain data. In simple terms, it's a 'thing' or a 'noun'. For a school database, the entities would be things like

Student

Teacher

, and

Course

. For an e-commerce site, they might be

Customer

Product

, and

Order

. Each entity will eventually become a table in our database.

((A simple diagram showing three separate boxes. The first box is titled 'Student', the second 'Course', and the third 'Tutor'.))

Attributes: The Properties of an Entity

An attribute is a property or characteristic of an entity. If an entity is a noun, an attribute is an 'adjective' that describes it. For the

Student

entity, the attributes would be things like

studentID

firstName

lastName

, and

dateOfBirth

. Each attribute will become a field (a column) in our database table, and each will be assigned a specific data type (e.g., Integer, Text, Date).

The Power of Keys: Establishing Uniqueness and Relationships

Keys are special attributes that play a crucial role in how the database functions. They enforce rules, ensure data integrity, and form the links that make a database 'relational'.

The Primary Key: The Unique Identifier

The Primary Key (PK) is an attribute, or a set of attributes, that has one critical job: to uniquely identify every single record (row) in a table. Think of it as a National Insurance number or a student ID number.
A primary key must abide by two strict rules:

It must be unique for every record. No two students can have the same student ID.

It cannot be NULL. Every record must have a primary key value.

Candidate and Secondary Keys: The Alternatives

Sometimes, a table might have more than one attribute that could, in theory, be the primary key. For example, in a

User

table, both the

userID

and the

userEmail

would be unique for every user. Any field that is a candidate to be the primary key is called a candidate key.

Once we choose one candidate key to be our primary key (e.g.,

userID

), any other candidate keys (e.g.,

userEmail

) are referred to as secondary keys or alternate keys.

The Composite Primary Key: Uniqueness in Combination

Sometimes, a single attribute isn't enough to uniquely identify a record. A composite primary key is a primary key that is made up of two or more attributes. For example, in a table that records student enrolments onto university modules, neither `studentID` nor `moduleID` alone could be the primary key. A student can enrol on many modules, and a module can have many students. However, the combination of `studentID` and `moduleID` for each record will always be unique.

The Foreign Key: The Link Between Tables

The Foreign Key (FK) is the concept that makes relational databases 'relational'. A foreign key is an attribute, or a collection of attributes, in one table that refers to the primary key in another table.
Its purpose is to link the two tables together and enforce a principle called referential integrity. This principle guarantees that a value in the foreign key column can only exist if that same value already exists in the primary key column of the linked table. You cannot, for example, create an order for a customer who doesn't exist. The database would reject the new order because the `customerID` (the foreign key in the `Orders` table) does not match any `customerID` (the primary key in the `Customers` table).

((A clear diagram showing two tables. 'Customers' has a column 'CustomerID' with a 'PK' icon. 'Orders' has a column 'CustomerID' with an 'FK' icon. A curved arrow connects the FK in 'Orders' back to the PK in 'Customers'.))

Active Learning Directive
Take the opposing view. The primary key rule states it must be unique and never change. Argue for a scenario where changing a primary key might seem necessary, and then explain the catastrophic data integrity issues this would cause, referencing foreign keys.

Task 2.1 Deconstructing a VET's Digital Record System

A local veterinary surgery's paper records are a mess. They've hired you to act as a database consultant to design the blueprint for their new digital system. Your first task is to identify the core 'things' they track and how they're all linked. Let's bring some logical order to the animal chaos!

Get Organised!

Read the scenario below carefully. Your task is to extract the entities, attributes, and keys needed to model this system.

Scenario: The veterinary surgery needs a system to manage pet healthcare. The system must store details for each Pet Owner, including their name, address, and phone number. Each owner can have one or more Pets. The system must track each pet's name, date of birth, and species (e.g., dog, cat). Every pet must be registered to a single owner. The surgery employs several Vets, and the system must store their name and qualification. A pet can have many Appointments over its lifetime, but each appointment relates to only one pet and is conducted by a specific vet. The date and reason for each appointment must be recorded.

Identify the Entities

Based on the scenario, identify the four main 'things' or 'nouns' that the surgery needs to store distinct information about. List them in your booklet.

List the Attributes

For each of the four entities you identified, list all the attributes (properties) mentioned in the scenario. For each attribute, suggest a suitable data type (e.g., Integer, Text, Date, Boolean).

Assign the Keys

For each entity, you must now assign keys to ensure uniqueness and create relationships.

First, create a new, suitable primary key for each entity (e.g., `ownerID`, `petID`). Underline this attribute in your list.

Second, identify where you need to add foreign keys to link the entities together based on the relationships described (e.g., "Each pet must be registered to a single owner"). Add these foreign keys to the relevant entity's attribute list and mark them with `(FK)`.

Document the Final Schema

Write out your final design using the standard notation shown below. List each entity and its attributes inside parentheses, with the primary key underlined and foreign keys marked.

Example Notation:

ENTITY_NAME ([d]primaryKey[/d], attribute1, attribute2, foreignKey_FK)

Hungry for more?

Search the web: database primary key vs candidate key vs superkey

Ask an AI: Click on the carefully written prompt below to learn more...

As an A-Level Computer Science examiner, explain the concept of 'referential integrity' in a relational database. Use an analogy involving a library's booking system. Limit your response to 150 words. Ensure you define the role of primary and foreign keys. NO intro, NO outro, NO deviation from the topic, NO follow-up questions.

Extend the Model: Add a new entity called `TREATMENT` to your vet database schema. What attributes would it need? How would it link to the `APPOINTMENT` entity?

Outcome: A documented database schema for the vet scenario, with all entities, attributes, primary keys, and foreign keys correctly identified.

Sticky Note Challenge

On a sticky note, define a 'Composite Primary Key' and give a real-world example of when you would need one (that isn't the one from the notes!).

Today you have learned the fundamental grammar of database design by identifying the entities, attributes, and keys needed to structure relational data.

Last modified: September 22nd, 2026