4.10.1–4.10.2 - Relational data models, keys and entity relationships

Entities, attributes and relations

A data model describes what data a system must store and how those data items relate. Begin with the things the requirements distinguish: a student, a club and a membership are different kinds of thing.

An entity is a distinguishable thing about which data are recorded. An entity type, such as STUDENT, describes a collection of similar entities. An attribute is a named property, such as StudentID or StudentName. An entity instance supplies values for those attributes: student S17 might have the name Amina.

In a relation, a row is a record or tuple and a column represents an attribute. A table's structure defines which attributes every row has. Each cell contains one value from the attribute's permitted domain: for example, an integer age or a date. Row order does not define identity. Sorting a table does not change which student a record represents.

An entity description lists an entity type and its attributes:

STUDENT(StudentID, StudentName, DateOfBirth)

CLUB(ClubID, ClubName)

Use consistent attribute names. The description is a schema, not a list of actual student names. A relational database can combine related tables when a query needs information from both; it need not repeat every student's details in every club record.

Choosing identifiers and linking records

A key lets a system identify a record even when other attribute values change or are shared. Two students can have the same name, so StudentName is a poor identifier when uniqueness is not guaranteed.

A candidate key is a minimal set of attributes that uniquely identifies a record. Minimal means no attribute can be removed while retaining guaranteed uniqueness. A primary key is the candidate key chosen as the main identifier. Its values are unique and not null. A generated StudentID may be chosen instead of a changeable name.

A composite primary key consists of two or more attributes whose combined values identify a record. Suppose each student may join each club only once. In ENROLMENT(StudentID, ClubID, JoinDate), neither StudentID nor ClubID alone is unique: a student may join several clubs, and a club may have several students. The pair (StudentID, ClubID) is unique under this stated rule. JoinDate describes that membership and is not needed in its identifier.

In ENROLMENT, StudentID references STUDENT's primary key and ClubID references CLUB's primary key. The two foreign keys also form ENROLMENT's composite primary key. A foreign key need not be unique: many enrolments can reference the same student. A valid non-null reference must match an existing referenced key; this is referential integrity. Whether a foreign key may be null depends on whether the relationship is optional.

Record identifiers explicitly in written descriptions, for example:

• STUDENT(StudentID [PK], StudentName)
• CLUB(ClubID [PK], ClubName)
• ENROLMENT(StudentID [PK, FK], ClubID [PK, FK], JoinDate)

Underlining all the attributes that form the identifier is another common notation. Whatever notation you use, state which relation each foreign key references.

Relationships and their cardinalities

A relationship states how entities are associated. Its cardinality describes how many instances on one side may relate to instances on the other. Read each direction separately before choosing the symbol.

An entity relationship diagram (ER diagram) places entity types in boxes and uses connecting lines with cardinality labels or crow's-foot symbols. The crow's foot marks the many end. A minimum, such as zero or one, additionally states optionality. Do not invent a minimum that the requirements do not establish.

To implement a one-to-many relationship, put the foreign key on the many side. Employee's DepartmentID references DEPARTMENT; a Department record does not contain a growing list of employee IDs in one cell.

A many-to-many relationship is resolved using a link entity. ENROLMENT turns the student–club relationship into two one-to-many relationships. Each enrolment belongs to exactly one student and exactly one club. A student or club can have many enrolments. Attributes of the relationship, such as JoinDate, belong in ENROLMENT.

[DIAGRAM: asset_slug: er_bridge; description: STUDENT and CLUB each have a one-to-many relationship with ENROLMENT; the two foreign keys form the enrolment identifier.]

Practise drawing this diagram in your notebook: put “1” next to STUDENT and “many” next to ENROLMENT, then “1” next to CLUB and “many” next to ENROLMENT. The lines express association, not the direction in which data flow. In a written answer, unambiguous textual cardinalities can explain the same design.

Building a model from requirements

Data modelling starts with the rules the system must satisfy, rather than with a convenient-looking table. Consider a workshop service: each session has a SessionID, title and date; each learner has a LearnerID and email. A learner can book many sessions, a session can have many learners, and a learner can book a particular session only once. The service records the booking date.

First identify LEARNER and SESSION. Then recognise BOOKING as the association with its own BookingDate. Choose identifiers using the stated uniqueness rules:

• LEARNER(LearnerID [PK], Email)
• SESSION(SessionID [PK], Title, SessionDate)
• BOOKING(LearnerID [PK, FK], SessionID [PK, FK], BookingDate)

BOOKING.LearnerID references LEARNER.LearnerID; BOOKING.SessionID references SESSION.SessionID. LEARNER to BOOKING and SESSION to BOOKING are both one-to-many. Each booking must reference one existing learner and one existing session. A learner who has not booked yet can exist in LEARNER without a BOOKING record.

Check the model by walking through examples. Can two learners book the same session? Yes: their LearnerID values differ. Can one learner book two sessions? Yes: SessionID differs. Can the same pair occur twice? The composite primary key prevents that. Can the session title be changed once rather than on every booking? Yes: it is stored in SESSION.

If the rules instead permit cancellation and rebooking as separate historical events, the pair alone may no longer identify a booking event. An additional BookingID, or a suitable larger identifier, may be needed. Keys are justified by requirements, not by a universal rule that every link table must have the same key.