Entity-relationship modelling: entities, keys and relationships

Entities, attributes and keys

• An entity is a thing about which data is stored, such as Student, Course or Teacher. Each becomes a table.
• An attribute is a property of an entity, such as StudentID, FirstName or DateOfBirth.
• A primary key is an attribute that uniquely identifies each record (for example StudentID).
• A composite key is a primary key made of two or more attributes together, when no single attribute is unique, for example (StudentID, CourseID) in an Enrolment table.
• A foreign key is an attribute in one table that is the primary key of another table, used to create the link between them.

Relationships and cardinality

• One-to-one (1:1): each record in A links to one record in B and vice versa (for example Country and CapitalCity).
• One-to-many / many-to-one (1:M or M:1): one record in A links to many in B, but each record in B links to only one in A (one Form has many Students; each Student is in one Form). The foreign key goes on the many side.
• Many-to-many (M:M): many records in A link to many in B (Students and Courses). Relational databases cannot implement M:M directly, so it is resolved with a link (junction) entity, for example Enrolment, giving two 1:M relationships.

In an entity-relationship diagram (ERD) entities are drawn as boxes and relationships as lines; the 'many' end is usually shown with a crow's foot. Use the symbols in Appendix 2 of the specification.

Parenthetical notation

A schema can be written as: ENTITY(PrimaryKey, Attribute, Attribute, ForeignKey). By convention the primary key is underlined and a foreign key is marked (often with an asterisk or in italics); check the convention used in the question. For example:

FORM(FormID, Room, TutorName)

STUDENT(StudentID, FirstName, LastName, FormID*)

COURSE(CourseID, Title)

ENROLMENT(StudentID*, CourseID*, StartDate)

In ENROLMENT the primary key is the composite (StudentID, CourseID), and each part is also a foreign key. Common errors to spot: a foreign key on the wrong side of a 1:M link, an M:M relationship not resolved, a primary key that is not unique (such as a surname).

Worked example

A library records Members, Books and Loans. A member can borrow many books over time and a book can be borrowed by many members. Write a schema in parenthetical notation.

1. Members and Books are M:M, so add a link entity LOAN.
2. MEMBER(MemberID, Name, Email)
3. BOOK(ISBN, Title, Author)
4. LOAN(LoanID, MemberID*, ISBN*, DateOut, DateDue)
5. MEMBER to LOAN is 1:M and BOOK to LOAN is 1:M.

Answer: MEMBER(MemberID, ...), BOOK(ISBN, ...), LOAN(LoanID, MemberID*, ISBN*, DateOut, DateDue)

Check yourself

1. Which attribute is most suitable as a primary key for a Student table?
   • StudentID
   • LastName
   • DateOfBirth
   • FormName
   Show answer

   StudentID. Only a unique identifier is suitable; names and dates can repeat.

2. What is a foreign key?
   • A key used to encrypt the database
   • An attribute that is always unique in its own table
   • An attribute in one table that is the primary key of another table
   • The first column of every table
   Show answer

   An attribute in one table that is the primary key of another table. It creates the relationship between tables.

3. One teacher teaches many classes and each class has one teacher. Where should the foreign key go?
   • TeacherID in the Class table
   • ClassID in the Teacher table
   • In a new link table only
   • In neither table
   Show answer

   TeacherID in the Class table. The foreign key goes on the many side.

4. How is a many-to-many relationship implemented in a relational database?
   • By putting a foreign key in each table pointing at the other
   • By merging the two tables into one
   • It is implemented directly with no changes
   • By adding a link entity that has a one-to-many relationship with each original entity
   Show answer

   By adding a link entity that has a one-to-many relationship with each original entity. The link entity's composite or own key holds the pairs.

5. In ENROLMENT(StudentID, CourseID, Grade), with StudentID and CourseID together as the primary key, what type of key is it?
   • Foreign key only
   • Composite key
   • Candidate key for Grade
   • Secondary key
   Show answer

   Composite key. A primary key made of two attributes is composite.

6. A country has exactly one capital city and each capital city belongs to exactly one country. What is the cardinality?
   • One-to-many
   • Many-to-one
   • Many-to-many
   • One-to-one
   Show answer

   One-to-one. Each side links to exactly one record.

7. A schema is written as FORM(FormID, Room, StudentID*) and STUDENT(StudentID, Name). Each form has many students. Explain the error.
   Show answer

   The foreign key is on the wrong side: a form has many students, so FormID should be a foreign key in STUDENT rather than StudentID in FORM, which could hold only one student per form.. The foreign key belongs on the many side.

Practise with instant feedback Print this lesson

Sources

• Pearson Edexcel International Advanced Subsidiary/Advanced Level in Computer Science specification (2026)

Last updated 8 November 2026. Spotted a mistake? Tell us.