IB Computer Science · A3 · Databases
A3.2 Database design
Draw and interpret an entity–relationship diagram, normalise a table to first and second normal form, identify primary and foreign keys, and design a simple relational database.
1/6An ER diagram plans the data before tables exist
- An ER diagram plans the data before tables exist
- Relationships have a type and are stored using keys
- Normalisation reduces repeated data in a database
- First normal form needs single values and unique records
- Second normal form removes partial dependencies
- A design moves from requirements to linked tables
- Check your recall
- Practice: 10 questions
Part 1 of 6: An ER diagram plans the data before tables exist
Good databases start with a plan. Suppose a school library wants to record books, members and loans. An entity is something the system stores data about, such as a book or a member.
Each entity has attributes, such as a title or a surname, which later become fields. An entity–relationship diagram (ER diagram) shows the entities and how they connect.
To create one, follow these steps.
- Read the requirements and list the entities.
- List the attributes of each entity.
- Choose a primary key for each entity.
- Decide how the entities are related.
- Draw each entity and show every relationship.
Key terms · tap one for its definition
entity
An entity is a person, object or event about which a database stores data, and it usually becomes a table.
entity–relationship diagram
An entity–relationship diagram is a diagram showing the entities in a system, their attributes and the relationships between them.
Part 2 of 6: Relationships have a type and are stored using keys
Now consider how the entities connect. A relationship is a link between two entities. It is one-to-one, one-to-many or many-to-many, depending on how many records can match on each side.
The three types of relationship
- Type
- One-to-one
- Meaning
- One record matches at most one record
- Example
- A member and their library card
- Type
- One-to-many
- Meaning
- One record matches many records
- Example
- One member and their loans
- Type
- Many-to-many
- Meaning
- Many records match many records
- Example
- Books and authors
A foreign key is a field in one table that holds the primary key of another table. It creates the link. In a one-to-many relationship, the foreign key sits in the table on the many side.
A database cannot store a many-to-many relationship directly. Instead, a linking table sits between the two tables. It holds a foreign key to each, which makes two one-to-many relationships.
Key terms · tap one for its definition
relationship
A relationship is an association between two entities, classified as one-to-one, one-to-many or many-to-many.
foreign key
A foreign key is a field in one table whose values match the primary key of another table, linking their records.
linking table
A linking table is a table that splits a many-to-many relationship into two one-to-many relationships using foreign keys.

Quick check
One question on the part you just read
Easy
Show answer and explanation
Answer: A · Worked explanation
Part 3 of 6: Normalisation reduces repeated data in a database
An ER diagram is only a plan, and a table can still hold repeated data. Normalisation is the process of organising tables to reduce that repetition. It works through stages called normal forms.
Repetition causes real problems. Changing one fact means editing many rows. Missing one row leaves the data inconsistent. Deleting a row can also remove facts that should have been kept.
Normalising does not lose data. It splits tables so each fact is stored in one place, and keys join them again. This note covers the first two stages.
Key term
normalisation
Normalisation is the process of organising data into tables, following rules called normal forms, to reduce redundancy and prevent inconsistency.
Part 4 of 6: First normal form needs single values and unique records
Start with the simplest rule. A table is in first normal form (1NF) when every field holds one value and no groups of columns repeat. Each record must also be unique.
A field containing ‘pen, ruler’ breaks the rule. So do repeating columns such as Item1 and Item2. The fix is to give each value its own row.
That can leave several rows with the same order number. A composite key then identifies each record. It is a primary key made of two or more fields.
Worked example
Convert a table to 1NF
- Items holds two values, so the table is not in 1NF.
- Give each item its own row: (7, Dev, pen) and (7, Dev, ruler).
- OrderID alone is no longer unique, so use OrderID and Item together as a composite key.
Answer: The 1NF table has two rows and the composite key OrderID with Item.
Key terms · tap one for its definition
first normal form
First normal form is the condition in which every field holds one value, there are no repeating groups and each record is unique.
composite key
A composite key is a primary key made from two or more fields whose values together identify each record uniquely.

Quick check
One question on the part you just read
Easy
Show answer and explanation
Answer: C · Worked explanation
Part 5 of 6: Second normal form removes partial dependencies
A 1NF table with a composite key can still repeat data. Consider an order line table with the key OrderID and ProductID. The product name depends only on ProductID, not on the whole key.
This is a partial dependency. A table is in second normal form (2NF) when it is in 1NF and has no partial dependencies. The fix is to move the offending field into its own table.
Worked example
Convert a table to 2NF
- Ask which fields depend on only part of the key.
- ProductName depends only on ProductID, so it is a partial dependency.
- Move it into a new table, Product(ProductID, ProductName), with the key ProductID.
- Keep OrderLine(OrderID, ProductID, Quantity). Quantity depends on both key fields, and ProductID is now also a foreign key.
Answer: The 2NF design has two tables: OrderLine(OrderID, ProductID, Quantity) and Product(ProductID, ProductName).
Key terms · tap one for its definition
partial dependency
A partial dependency is a non-key field that depends on only part of a composite key rather than on the whole key.
second normal form
Second normal form is the condition in which a table is in first normal form and has no partial dependencies.
Part 6 of 6: A design moves from requirements to linked tables
Now combine these ideas into one design process. Take a clinic that records patients, doctors and appointments. Each appointment involves one patient and one doctor, but a patient can have many appointments.
- List the entities from the requirements.
- Draw the ER diagram with its relationships.
- Make a table for each entity, with a primary key.
- Add foreign keys on the many side of each relationship.
- Normalise each table to 2NF.
- Choose a data type for every field.
For the clinic, the Appointment table holds PatientID and DoctorID as foreign keys, plus the date and time.
Check your recall[14 marks]
6 written questions. Answer each one in your head or on paper, then open the mark scheme.
- 1.Describe[3 marks]
Describe the steps in creating an ER diagram.
Show mark schemeHide mark scheme
Model answer
List the entities from the requirements and the attributes of each. Choose a primary key for each entity. Decide how the entities are related, then draw each entity and show every relationship with its type.
- 1 mark for describing listing the entities and their attributes
- 1 mark for describing choosing a primary key for each entity
- 1 mark for describing identifying and drawing the relationships with their types
- 2.Distinguish[2 marks]
Distinguish between a primary key and a foreign key.
Show mark schemeHide mark scheme
Model answer
A primary key identifies each record in its own table uniquely. A foreign key is a field that holds the primary key of another table, which links the two tables.
- 1 mark for stating that a primary key identifies each record uniquely in its own table
- 1 mark for stating that a foreign key holds a primary key from another table to create a link
- 3.Explain[2 marks]
Explain how a many-to-many relationship is stored in a relational database.
Show mark schemeHide mark scheme
Model answer
It is split using a linking table. The linking table holds a foreign key to each of the two tables, which gives two one-to-many relationships.
- 1 mark for stating that a linking table is used between the two tables
- 1 mark for explaining that it holds foreign keys, giving two one-to-many relationships
- 4.State[2 marks]
State two conditions that a table must meet to be in first normal form.
Show mark schemeHide mark scheme
Model answer
Every field must hold a single value, and there must be no repeating groups of columns. Each record must also be unique.
- 1 mark for stating that every field holds one value
- 1 mark for stating a second condition such as no repeating groups or unique records
- 5.Deduce[2 marks]
Deduce which field in OrderLine(OrderID, ProductID, ProductName, Quantity) breaks second normal form when the composite key is OrderID with ProductID. Give a reason.
Show mark schemeHide mark scheme
Model answer
ProductName breaks it. It depends only on ProductID, which is part of the key, so it is a partial dependency.
- 1 mark for identifying ProductName
- 1 mark for stating that it depends on only part of the composite key
- 6.Outline[3 marks]
Outline how a simple relational database could be designed for a clinic that records patients, doctors and appointments.
Show mark schemeHide mark scheme
Model answer
List the entities Patient, Doctor and Appointment and give each table a primary key. Appointment holds PatientID and DoctorID as foreign keys on the many side. Normalise each table to second normal form and choose a data type for every field.
- 1 mark for listing the entities and giving each table a primary key
- 1 mark for placing foreign keys on the many side, in Appointment
- 1 mark for normalising the tables and choosing data types

Practice this topic
10 quick-fire multiple-choice questions, easy to hard. Tap an answer to see if you're right and the worked explanation. (The written questions with mark schemes are under “Check your recall” above.)
0 right0 of 10 answered
Includes the 2 quick checks in the note above.
EasyState[1 mark]
Show answer and explanation
Answer: D · Worked explanation
MediumIdentify[1 mark]
Show answer and explanation
Answer: B · Worked explanation
MediumIdentify[1 mark]
Show answer and explanation
Answer: C · Worked explanation
MediumDeduce[1 mark]
Show answer and explanation
Answer: D · Worked explanation
MediumIdentify[1 mark]
Show answer and explanation
Answer: A · Worked explanation
HardDetermine[2 marks]
Show answer and explanation
Answer: C · Worked explanation
HardExplain[2 marks]
Show answer and explanation
Answer: B · Worked explanation
HardDetermine[2 marks]
Show answer and explanation
Answer: A · Worked explanation