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).
What a logical data model shows
A logical data model describes the data structure independently of any particular DBMS. It shows:
- each entity (relation) as a box with its name;
- its attributes, with the primary key underlined or marked PK, and foreign keys marked FK or with *;
- the relationships between entities, with their degree (cardinality).
The scheme of work describes it as an extended form of entity relationship diagram (ERD).
Relationships and notation
- One-to-one (1:1): each record in A links to at most one in B (an employee and their company car).
- One-to-many (1:M): one record in A links to many in B (one customer places many orders). The foreign key goes in the 'many' table.
- Many-to-many (M:N): many students take many courses. A relational database cannot implement M:N directly, so it is resolved with a link (junction) table such as ENROLMENT(StudentID, CourseID), giving two 1:M relationships.
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
| Entity | Attributes (PK underlined as PK, FK marked) | Relationship |
|---|---|---|
| CUSTOMER | CustomerID PK, CustomerName, CustomerTown | CUSTOMER 1 to M ORDER |
| ORDER | OrderID PK, OrderDate, CustomerID FK | ORDER 1 to M ORDER_ITEM |
| PRODUCT | ProductID PK, Description, Price | PRODUCT 1 to M ORDER_ITEM |
| ORDER_ITEM | OrderID PK FK, ProductID PK FK, Quantity | Link 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.
- Entities: MEMBER(MemberID PK, Name, Email), BOOK(BookID PK, Title, Author)
- MEMBER to BOOK is many-to-many, so add a link table LOAN(LoanID PK, MemberID FK, BookID FK, DateOut, DateDue, DateReturned)
- 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
Check yourself
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (first teaching 2018), Issue 2, February 2021
- Pearson Edexcel IAL Information Technology: scheme of work for Unit 3
- Pearson Edexcel IAL Information Technology WIT13/01 examiners' report, June 2025 series
Last updated 8 November 2026. Spotted a mistake? Tell us.