Database Design

Lessons

Senior 4 · ICT

Database Design

5 lessons

Lesson 2 of 5

Entities, Attributes and Types of Relationships

25 min

Data is about connections

A list of students is useful. A list of books is useful. But the library's real question is who has borrowed which book, and that is a connection between the two lists. Before anyone builds tables, a database designer writes down the things to be stored, their properties and, above all, how they are connected, and asks precise questions: can a student borrow more than one book? Can a book be borrowed by more than one student at the same time? The answers decide the design.

Definition

Entity (entity type): a kind of thing, real or abstract, about which data is stored: Student, Book, Class, Payment. Entity instance: one particular example, such as the student Aline Uwase.

Attribute: a property that describes an entity: a student's BirthDate.

Relationship: an association between entities, named with a verb: a Student borrows a Book; a Class has Students.

Cardinality: the number of instances of one entity that can be linked to one instance of the other: one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N).

Participation (optionality): whether every instance must take part in the relationship (mandatory) or may (optional).

Kinds of attribute

Kind	Meaning	Example
Simple (atomic)	Cannot be divided further in a useful way	BirthDate, Class
Composite	Made of smaller parts that are useful on their own	FullName = FirstName + LastName; Address = Province + District + Sector
Single-valued	One value per instance	StudentID
Multi-valued	May have several values for one instance	Phone numbers, subjects taught by a teacher
Stored	Kept in the database	BirthDate
Derived	Calculated from other data whenever needed, so not stored	Age (from BirthDate), a class's number of students
Key (identifier)	Its value is different for every instance, so it identifies one instance	StudentID, ISBN, a national ID number

Why store BirthDate and not Age? Age changes every year by itself; a stored age would quietly become wrong, while an age calculated from the birth date is always right.

Three types of relationship

Most relationships join two entities (a binary relationship). The picture shows real instances, so you can see what each type means.

Type	Rule, read both ways	More examples
One-to-one (1:1)	One A is linked to at most one B, and one B to at most one A	A school has one head teacher, who heads one school; a citizen has one national ID card
One-to-many (1:N)	One A is linked to many Bs, but each B to only one A	A province has many districts, each district is in one province; a customer makes many orders
Many-to-many (M:N)	One A is linked to many Bs, and one B to many As	Students and subjects; books and authors; teachers and classes

Always read a relationship in both directions. "A class has many students" alone does not tell 1:N from M:N; you also need "each student is in one class".

A relationship can also link an entity to itself (a unary or recursive relationship): a student mentors other students; a teacher heads a department of other teachers. A relationship among three entities at once (a ternary one) also exists: a teacher teaches a subject to a class.

A database diagram for a small website, drawn with the (min, max) notation. Beside each line, pairs such as (1,1) and (0,n) give the smallest and largest number of linked instances: a user writes (0,n) pages, so from zero to many, while each page has (1,1) user, exactly one. Image: Wooptoo, public domain, via Wikimedia Commons.

Participation: must or may?

Cardinality gives the maximum; participation gives the minimum. Designers often write both as a (min, max) pair, as in the diagram above:

Every student must belong to a class: Student participates in "belongs to" with (1, 1), mandatory.
A student may have borrowed no books at all: Student participates in "borrows" with (0, N), optional.
A class may exist before any student is placed in it: (0, N) on the Class side.

Participation turns into rules the database enforces: a mandatory link becomes a field that may not be left empty (NOT NULL).

sql

-- One province has many districts; each district is in one province.
CREATE TABLE Province (
  ProvinceID INTEGER PRIMARY KEY,
  Name       TEXT NOT NULL
);

CREATE TABLE District (
  DistrictID INTEGER PRIMARY KEY,
  Name       TEXT NOT NULL,
  ProvinceID INTEGER NOT NULL REFERENCES Province(ProvinceID)
);

INSERT INTO Province VALUES
  (1, 'City of Kigali'), (2, 'Northern Province'), (3, 'Southern Province'),
  (4, 'Eastern Province'), (5, 'Western Province');

INSERT INTO District (Name, ProvinceID) VALUES
  ('Gasabo', 1), ('Kicukiro', 1), ('Nyarugenge', 1),
  ('Burera', 2), ('Gakenke', 2), ('Gicumbi', 2), ('Musanze', 2), ('Rulindo', 2),
  ('Gisagara', 3), ('Huye', 3), ('Kamonyi', 3), ('Muhanga', 3),
  ('Nyamagabe', 3), ('Nyanza', 3), ('Nyaruguru', 3), ('Ruhango', 3),
  ('Bugesera', 4), ('Gatsibo', 4), ('Kayonza', 4), ('Kirehe', 4),
  ('Ngoma', 4), ('Nyagatare', 4), ('Rwamagana', 4),
  ('Karongi', 5), ('Ngororero', 5), ('Nyabihu', 5), ('Nyamasheke', 5),
  ('Rubavu', 5), ('Rusizi', 5), ('Rutsiro', 5);

-- The "many" side: how many districts does each province have?
SELECT p.Name AS Province, COUNT(*) AS Districts
FROM District AS d
JOIN Province AS p ON d.ProvinceID = p.ProvinceID
GROUP BY p.ProvinceID
ORDER BY p.ProvinceID;

-- The "one" side: which province is Huye in?
SELECT d.Name AS District, p.Name AS Province
FROM District AS d
JOIN Province AS p ON d.ProvinceID = p.ProvinceID
WHERE d.Name = 'Huye';

Code Explanation

Press Run. The 1:N relationship "a province has districts" is stored by putting the ProvinceID of its province in every District record. The first query counts the "many" side for each province (3, 5, 8, 7 and 7, a total of 30 districts); the second follows a district to its "one" province. JOIN matches the records whose ProvinceID values are equal.

Worked example 1: decide the type from the rules

For each pair, ask the question in both directions.

Pair and rule	A to B	B to A	Type
Class and class teacher: "each class has one class teacher, and a teacher is class teacher of at most one class"	one	one	1:1
Teacher and subject: "a teacher may teach several subjects, and a subject is taught by several teachers"	many	many	M:N
Book and copy: "the library owns several copies of a book; each copy is of one book"	many	one	1:N
Student and fee payment: "a student makes many payments; each payment is from one student"	many	one	1:N

The same two entities can have different types in different schools. If a school allows each student only one club, Student–Club is 1:N there; if it allows several, it is M:N. The business rule decides, not the names of the entities.

Worked example 2: read the type from the data

A sample of the school's club register:

Student	Club
Aline	ICT
Aline	Debate
Eric	ICT
Grace	Debate

Aline appears with two clubs, so one student can have many clubs. ICT appears with two students, so one club has many students. Both directions are "many": the relationship is M:N. A sample can prove that a relationship is "many" (one counter-example is enough), but it can never prove "one": that must come from the rules.

Code Darasa: Database Relationships Explained. Watch one-to-one, one-to-many and many-to-many relationships with everyday examples, and notice how each is read in both directions.

Warning

Read both directions. Most wrong answers come from reading a relationship one way only.
Do not store derived data such as age or a total; it goes out of date.
A multi-valued attribute is a warning sign. Several phone numbers in one field cannot be searched or checked properly; Lesson 5 shows how to fix it.

Tip

The two-question test. "Can one A have more than one B?" and "Can one B have more than one A?" Yes–no or no–yes gives 1:N, no–no gives 1:1, yes–yes gives M:N.

✦

Practice

Name the entities and one relationship in: "Each patient is registered at one health centre; a health centre registers many patients."
Classify each attribute of a Teacher as simple or composite, single- or multi-valued, stored or derived: TeacherID, FullName, PhoneNumbers, DateHired, YearsOfService.
Give the relationship type, with your reasoning in both directions: (a) Country and capital city, if each country has one official capital; (b) Bus and route, if a bus serves one route a day and a route is served by several buses; (c) Customer and product in a shop's sales.
Draw instance diagrams like the one in this lesson for (a) a 1:1 and (b) an M:N relationship of your own choice.
A student must be in exactly one class, and a class may have no students yet. Write the (min, max) pair for each side.
A sample of hospital data shows each nurse working in one ward. A colleague concludes that Nurse–Ward is 1:N. Explain why the sample alone cannot prove this, and what you would ask.
Explain why "Age" should not be stored in a Student table.

Show answersHide answers

Entities: Patient, HealthCentre. Relationship: a health centre registers patients (1:N, from health centre to patients).
TeacherID: simple, single-valued, stored (key). FullName: composite, single-valued, stored. PhoneNumbers: multi-valued, stored. DateHired: simple, single-valued, stored. YearsOfService: derived (from DateHired).
(a) 1:1: one country, one capital; one capital belongs to one country. (b) 1:N from route to bus: a route has many buses; a bus serves one route. (c) M:N: a customer buys many products; a product is bought by many customers.
(a) Two sets with each element joined to exactly one element of the other. (b) Two sets in which at least one element on each side is joined to two or more on the other side.
Student side: (1, 1), exactly one class. Class side: (0, N), from none to many students.
A sample can show that something happens but not that it can never happen: the data may simply not include a nurse who works in two wards. Ask the hospital the rule: "May a nurse be assigned to more than one ward?"
Age changes every year without anyone editing it, so a stored age becomes wrong; it can always be calculated from BirthDate when needed.

Quick check

Each copy in a library is a copy of one book, and the library owns several copies of some books. What is the Book–Copy relationship?

Key points

Entities are things we store data about; attributes describe them (simple or composite, single- or multi-valued, stored or derived; a key attribute identifies each instance).
Relationships link entities and are named with verbs. Their cardinality is 1:1, 1:N or M:N; read every relationship in both directions.
Participation says whether taking part is mandatory or optional; (min, max) pairs such as (0, N) and (1, 1) give both.
Business rules, not sample data, decide the type of a relationship.

Sources and credits

Entity–relationship model and Cardinality (data modeling), Wikipedia contributors, Wikipedia, licensed under CC BY-SA 4.0. Adapted by Ganzaa.
District data from Districts of Rwanda, Wikipedia, CC BY-SA 4.0. SQL checked against SQLite: CREATE TABLE, sqlite.org, public domain.
Image: Entity-Relationship Model (diagram), Wooptoo, Wikimedia Commons, public domain.
Video: Database Relationships Explained, Code Darasa, embedded from YouTube.
Diagrams, SQL example, worked examples and exercises (student and club data illustrative): written by Ganzaa.

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

1Data is about connections

2Kinds of attribute

3Three types of relationship

4Participation: must or may?

Data is about connections

Kinds of attribute

Three types of relationship

Participation: must or may?