All subjects · Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development

Designing a logical data model

Edexcel IAL Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development

After normalising, you show the design as a logical data model: an extended entity relationship diagram showing each normalised relation, its keys and the relationships between them (spec 12.2.4).

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

What a logical data model shows

A logical data model describes the data structure independently of any particular DBMS. It shows:

The scheme of work describes it as an extended form of entity relationship diagram (ERD).

Relationships and notation

Common notations: a line with a crow's foot at the 'many' end, or lines labelled 1 and M (or 1 and *). Use one notation consistently and label clearly.

Building the model step by step

1. Normalise the data to 3NF.

2. Draw a box per relation; list attributes; mark PK and FK.

3. For each foreign key, draw a relationship line from the table where it is the PK (the 'one' end) to the table where it is the FK (the 'many' end).

4. Check: every FK has a matching PK; no M:N lines remain; every relationship has its degree.

Exam warning (June 2025 examiners' report): candidates lost marks by confusing notation and by adding unnecessary foreign keys. A foreign key belongs only on the 'many' side of a relationship; do not put OrderID in CUSTOMER as well as CustomerID in ORDER.

Example model

EntityAttributes (PK underlined as PK, FK marked)Relationship
CUSTOMERCustomerID PK, CustomerName, CustomerTownCUSTOMER 1 to M ORDER
ORDEROrderID PK, OrderDate, CustomerID FKORDER 1 to M ORDER_ITEM
PRODUCTProductID PK, Description, PricePRODUCT 1 to M ORDER_ITEM
ORDER_ITEMOrderID PK FK, ProductID PK FK, QuantityLink table resolving ORDER M to N PRODUCT

Worked example

A library: members borrow many books over time; each book can be borrowed by many members over time. Design the logical model.

  1. Entities: MEMBER(MemberID PK, Name, Email), BOOK(BookID PK, Title, Author)
  2. MEMBER to BOOK is many-to-many, so add a link table LOAN(LoanID PK, MemberID FK, BookID FK, DateOut, DateDue, DateReturned)
  3. Relationships: MEMBER 1 to M LOAN; BOOK 1 to M LOAN (crow's feet at LOAN)

Answer: MEMBER 1:M LOAN M:1 BOOK, with foreign keys only in LOAN

Key idea: Logical data model = normalised relations with PKs, FKs and relationships. FK goes on the many side. Resolve M:N with a link table. Use one clear notation.

Check yourself

  1. In a one-to-many relationship between DEPARTMENT and EMPLOYEE, where is the foreign key placed?
    • In DEPARTMENT, holding EmployeeID
    • In EMPLOYEE, holding DepartmentID
    • In a third link table
    • In neither table
    Show answer

    In EMPLOYEE, holding DepartmentID. The FK goes in the 'many' table.

  2. How is a many-to-many relationship between STUDENT and CLUB implemented in a relational database?
    • Put ClubID in STUDENT and StudentID in CLUB
    • Merge the two tables into one
    • Use a one-to-one relationship
    • Add a link table such as MEMBERSHIP(StudentID, ClubID) with two one-to-many relationships
    Show answer

    Add a link table such as MEMBERSHIP(StudentID, ClubID) with two one-to-many relationships. A junction table resolves M:N.

  3. A hospital ward has many patients; each patient is on one ward at a time. What is the degree of WARD to PATIENT?
    • One-to-one
    • One-to-many
    • Many-to-many
    • Many-to-one from WARD to PATIENT
    Show answer

    One-to-many. One ward, many patients.

  4. A student's model puts CustomerID in ORDER and also OrderID in CUSTOMER. What is wrong?
    • CustomerID should not be in ORDER
    • There should be no keys in the model
    • OrderID in CUSTOMER is an unnecessary foreign key; it would need a repeating group for many orders
    • Nothing is wrong
    Show answer

    OrderID in CUSTOMER is an unnecessary foreign key; it would need a repeating group for many orders. The examiners' report flagged unnecessary foreign keys.

  5. A model has entities STUDENT, COURSE and TEACHER. STUDENT to COURSE is M:N and TEACHER to COURSE is 1:M. After resolving, how many tables are there?
    Show answer

    4. STUDENT, COURSE, TEACHER plus one link table (ENROLMENT): 4.

  6. On an entity relationship diagram, what does a crow's foot at one end of a relationship line show?
    Show answer

    It marks the many end of the relationship: one record at the other end can be linked to many records at this end.. Crow's foot = many.

Practise with instant feedback

Sources

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