Database Design

Lessons

Senior 4 · ICT

Database Design

5 lessons

Lesson 4 of 5

The Relational Model and Keys

25 min

From a diagram to tables

An ERD says what to store. The next step is to decide how: as tables linked by keys. Nearly every business, bank, school system and government service today stores its data this way, following a model published in 1970 by Edgar F. Codd, a British mathematician working for IBM, in his paper "A Relational Model of Data for Large Shared Data Banks". Codd received the ACM Turing Award, computing's highest prize, in 1981. His idea was simple and powerful: store all data as tables of values, and link tables by matching values, not by hidden pointers.

Definition

Relation: a table with named columns and unordered, unique rows. Tuple: one row of a relation. Attribute: one named column.

Domain: the set of allowed values of an attribute (for Class: the codes of real classes; for Mark: whole numbers from 0 to 100).

Degree: the number of attributes of a relation. Cardinality of a relation: the number of tuples it holds. (Do not confuse it with the cardinality of a relationship, 1:1, 1:N or M:N.)

Relation schema: a relation's name and attributes, written as Student(StudentID, FullName, Email, ClassID*), with the primary key underlined.

Properties of a relation. (1) Each cell holds a single, atomic value. (2) All values in a column come from the same domain. (3) Every attribute has a different name. (4) No two tuples are identical. (5) The order of the rows and of the columns carries no meaning.

Keys

A key is how a database finds exactly one row, and how one table points to another.

Key	Definition	Example
Super key	Any set of attributes whose values are different for every tuple	{StudentID}, {StudentID, FullName}, {Email}
Candidate key	A minimal super key: remove any attribute and it no longer identifies a tuple	{StudentID} and {Email}
Primary key (PK)	The candidate key chosen to identify tuples. It must be unique and never NULL, and should not change	StudentID
Alternate key	A candidate key not chosen as primary key	Email
Composite key	A key made of two or more attributes together	(StudentID, ISBN, DateBorrowed) in Loan
Foreign key (FK)	An attribute whose values must match the primary key of another (or the same) relation; it implements a relationship	ClassID in Student, referring to Class
Surrogate key	An artificial identifier with no meaning outside the database, often a number given by the DBMS	StudentID 1001, 1002, …
Natural key	An identifier that exists in the real world	A book's ISBN

Integrity rules. Two rules hold in every relational database. Entity integrity: no part of a primary key may be NULL, so every tuple can be identified. Referential integrity: every foreign-key value must match an existing primary-key value in the table it refers to (or be NULL, if the relationship is optional), so there is never a loan for a student who does not exist.

Mapping an ERD to relations

In the ERD	In the relational model
Entity	A relation; its key attribute becomes the primary key
Composite attribute	Its parts become separate attributes (FirstName, LastName)
Multi-valued attribute	A new relation holding the owner's key and one value per row: StudentPhone(StudentID, Phone)
1:N relationship	Put the primary key of the "one" side as a foreign key in the "many" side: ClassID goes into Student
1:1 relationship	Put a foreign key on either side, usually the optional side, and declare it UNIQUE
M:N relationship	A new junction (linking) relation whose primary key combines the two foreign keys, plus the relationship's own attributes

sql

PRAGMA foreign_keys = ON;  -- SQLite checks foreign keys only when asked

CREATE TABLE Class (
  ClassID   TEXT NOT NULL PRIMARY KEY,   -- e.g. 'S4A'
  ClassName TEXT NOT NULL
);

CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  FullName  TEXT NOT NULL,
  Email     TEXT NOT NULL UNIQUE,         -- an alternate key
  ClassID   TEXT NOT NULL REFERENCES Class(ClassID)   -- foreign key: 1:N
);

CREATE TABLE Book (
  ISBN  TEXT NOT NULL PRIMARY KEY,
  Title TEXT NOT NULL
);

-- The M:N relationship "borrows" becomes a table of its own
CREATE TABLE Loan (
  StudentID    INTEGER NOT NULL REFERENCES Student(StudentID),
  ISBN         TEXT    NOT NULL REFERENCES Book(ISBN),
  DateBorrowed TEXT    NOT NULL,
  PRIMARY KEY (StudentID, ISBN, DateBorrowed)   -- a composite key
);

INSERT INTO Class VALUES ('S4A', 'Senior 4 A'), ('S4B', 'Senior 4 B');
INSERT INTO Student VALUES
  (1001, 'Aline Uwase',   'aline.uwase@school.example',   'S4A'),
  (1002, 'Eric Mugisha',  'eric.mugisha@school.example',  'S4B'),
  (1003, 'Grace Ingabire','grace.ingabire@school.example','S4A');
INSERT INTO Book VALUES
  ('978-0-00-000001-9', 'Introduction to Databases'),
  ('978-0-00-000002-6', 'Web Pages with HTML');
INSERT INTO Loan VALUES
  (1001, '978-0-00-000001-9', '2026-09-01'),
  (1001, '978-0-00-000002-6', '2026-09-08'),
  (1003, '978-0-00-000001-9', '2026-09-15');

-- Follow the foreign keys back to the names and titles
SELECT s.FullName, s.ClassID, b.Title, l.DateBorrowed
FROM Loan AS l
JOIN Student AS s ON l.StudentID = s.StudentID
JOIN Book    AS b ON l.ISBN = b.ISBN
ORDER BY l.DateBorrowed;

Code Explanation

Press Run. The four CREATE TABLE statements are the relational model of the school ERD: PRIMARY KEY enforces entity integrity, REFERENCES declares each foreign key, UNIQUE makes Email an alternate key, and Loan has a composite key. The final query follows the foreign keys back to the names and titles. (Names, emails and ISBNs are invented.)

sql

PRAGMA foreign_keys = ON;

CREATE TABLE Class (
  ClassID   TEXT NOT NULL PRIMARY KEY,
  ClassName TEXT NOT NULL
);
CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  FullName  TEXT NOT NULL,
  ClassID   TEXT NOT NULL REFERENCES Class(ClassID)
);

INSERT INTO Class VALUES ('S4A', 'Senior 4 A');
INSERT INTO Student VALUES (1001, 'Aline Uwase', 'S4A');   -- accepted

-- Referential integrity: there is no class 'S9Z', so the DBMS refuses this row
INSERT INTO Student VALUES (1002, 'Eric Mugisha', 'S9Z');

Code Explanation

Run this one and read the last card: FOREIGN KEY constraint failed. Referential integrity has stopped a student being placed in a class that does not exist. Now change the last line to INSERT INTO Student VALUES (1001, 'Eric Mugisha', 'S4A'); and run again: UNIQUE constraint failed, because 1001 is already used and a primary key must be unique (entity integrity).

Worked example 1: name every key

Relation: Student(StudentID, FullName, Email, Phone, ClassID). Every student has a different StudentID and a different school email; two students can share a name; phone numbers can change and some students have none.

Super keys include {StudentID}, {Email}, {StudentID, Email}, {StudentID, FullName}, and any set that contains StudentID or Email.
Candidate keys: {StudentID} and {Email}. {StudentID, FullName} is not a candidate key, because it is not minimal: StudentID alone is enough.
Primary key: StudentID. It is short, never changes and is never empty. Email is unique too, but a student can change email address.
Alternate key: Email. Foreign key: ClassID (refers to Class). Not keys: FullName (not unique) and Phone (may be missing and may change).

Worked example 2: map an ERD to relation schemas

Take the coffee cooperative from Lesson 3, and add one rule: a member may grow several varieties of coffee, and each variety is grown by many members.

Entities become relations: Member(MemberID, FullName, Village), WashingStation(StationID, StationName), Variety(VarietyCode, VarietyName).
Delivery is on the "many" side of two 1:N relationships, so it receives both foreign keys: Delivery(DeliveryID, DeliveryDate, Kilograms, MemberID*, StationID*).
Member–Variety is M:N, so it becomes a junction relation with a composite key: Grows(MemberID*, VarietyCode*).

Check: every relationship is now a foreign key, every relation has a primary key, and no attribute holds a list.

Worked example 3: choosing a good primary key

Candidate for Student's PK	Verdict
FullName	Rejected: two students can have the same name.
Phone	Rejected: can change, can be shared, can be missing (NULL breaks entity integrity).
(ClassID, RollNumber)	Unique today, but changes every year when the student moves class, and every foreign key pointing to it would have to change too.
StudentID given by the school	Chosen: unique, never empty, never changes, short.

Decomplexify: Database Keys Made Easy. Watch primary, candidate, alternate, composite, foreign and surrogate keys explained with tables, and why a good primary key must never change.

Warning

SQLite and NULL keys. Because of an old bug kept for compatibility, SQLite allows NULL in a PRIMARY KEY column that is not an INTEGER PRIMARY KEY. Write NOT NULL PRIMARY KEY for text keys, as in the code above.
Foreign keys are off by default in SQLite. Start with PRAGMA foreign_keys = ON; or referential integrity is not checked.
Relationship cardinality is not relation cardinality. 1:N describes a relationship; "cardinality = 3" counts the rows of one table.

Tip

"The foreign key goes to the many side." In a 1:N relationship, many students point to one class, so each Student row carries the ClassID. An M:N relationship has two "many" sides, so it needs a table of its own.

✦

Practice

A relation has 6 attributes and 250 tuples. Give its degree and its cardinality.
Define candidate key and explain how it differs from a super key, with an example.
Book(ISBN, Title, Author, LibraryCode, Year): every book has a unique ISBN and a unique LibraryCode. List the candidate keys, choose a primary key and name the alternate key.
Map to relation schemas: "A department has many teachers; each teacher belongs to one department. Each department has a code and name; each teacher an ID, name and phone."
Map to relation schemas: "A patient can see many doctors and a doctor sees many patients; each visit has a date and a diagnosis."
A Loan row has StudentID 2005, but no student 2005 exists. Which integrity rule is broken, and how does a DBMS prevent it?
A school proposes using students' phone numbers as the primary key "because everyone has a phone". Evaluate this proposal.

Show answersHide answers

Degree 6; cardinality 250.
A super key is any set of attributes that identifies each tuple; a candidate key is a minimal super key (nothing can be removed). {ISBN, Title} is a super key of Book but not a candidate key, because {ISBN} alone is enough.
Candidate keys: {ISBN} and {LibraryCode}. Primary key: ISBN (for example). Alternate key: LibraryCode.
Department(DeptCode, DeptName); Teacher(TeacherID, FullName, Phone, DeptCode*): the FK goes on the many side.
Patient(PatientID, …), Doctor(DoctorID, …), Visit(PatientID*, DoctorID*, VisitDate, Diagnosis). The date is part of the key so the same patient can see the same doctor again.
Referential integrity. With the foreign key declared (and, in SQLite, PRAGMA foreign_keys = ON), the DBMS refuses the insert with "FOREIGN KEY constraint failed".
A poor choice: not every student has a phone (NULL is not allowed in a PK), numbers are shared by siblings, and numbers change, which would force changes in every table that refers to the student. A school-issued StudentID is unique, permanent and always present.

Quick check

A relation has 5 attributes and 40 tuples. What is its degree?

Key points

Codd's relational model (1970) stores data as relations: tuples (rows) of attributes (columns) with values from domains; degree counts attributes and cardinality counts tuples.
Keys: super key → candidate key (minimal) → primary key (chosen; unique, not NULL, stable) and alternate keys; composite, surrogate and natural keys; foreign keys link relations.
Entity integrity (no NULL in a PK) and referential integrity (every FK matches an existing PK).
Mapping: entity → relation; 1:N → FK on the many side; 1:1 → UNIQUE FK; M:N and multi-valued attributes → new relations with composite keys.

Sources and credits

Relational model and Edgar F. Codd, Wikipedia contributors, Wikipedia, licensed under CC BY-SA 4.0. Adapted by Ganzaa.
CREATE TABLE and SQLite Foreign Key Support, SQLite developers, sqlite.org, public domain.
Image: EAN-13-ISBN-13, Wikimedia Commons, public domain.
Video: Database Keys Made Easy, Decomplexify, embedded from YouTube.
Diagrams, SQL examples (all data invented), worked examples and exercises: written by Ganzaa.

This lesson adapts CC BY-SA material and is shared under CC BY-SA 4.0.

1From a diagram to tables

2Keys

3Mapping an ERD to relations

From a diagram to tables

Keys

Mapping an ERD to relations