Scribe logoScribeNotes & practice
Sign inStart free
  1. Notes
  2. ›IB Computer Science
  3. ›A3 Databases
  4. ›A3.2 Database design

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.

  • 5 min read
  • 6 parts
  • 10 questions
  • Draft
Start a Quick 5 on this topic→Jump to the 10 questions
1/6An ER diagram plans the data before tables exist
  1. 1An ER diagram plans the data before tables exist
  2. 2Relationships have a type and are stored using keys
  3. 3Normalisation reduces repeated data in a database
  4. 4First normal form needs single values and unique records
  5. 5Second normal form removes partial dependencies
  6. 6A design moves from requirements to linked tables
  7. Check your recall
  8. Practice: 10 questions
1

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 1 done

2

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

TypeMeaningExample
One-to-oneOne record matches at most one recordA member and their library card
One-to-manyOne record matches many recordsOne member and their loans
Many-to-manyMany records match many recordsBooks and authors
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.

Figure 1. Loan sits between Member and Book and holds a foreign key to each, so it links them.

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.

Part 2 done

Quick check

One question on the part you just read

Easy

Identify what an entity is in an ER diagram.
Show answer and explanation

Answer: A · Worked explanation

An entity is a thing the system needs to store data about, and it usually becomes a table. A link between entities is a relationship, not an entity. A field that identifies each record is a primary key, and a rule that removes repeated data is part of normalisation.
3

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 3 done

4

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

A table holds OrderID, Customer and Items. Order 7 for Dev has Items ‘pen, ruler’. Convert it to 1NF.
  1. 1
    Items holds two values, so the table is not in 1NF.
  2. 2
    Give each item its own row: (7, Dev, pen) and (7, Dev, ruler).
  3. 3
    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.

Part 4 done

Quick check

One question on the part you just read

Easy

Identify the type of relationship when one form tutor looks after many students and each student has one form tutor.
Show answer and explanation

Answer: C · Worked explanation

One tutor matches many students, but each student matches only one tutor, so the relationship is one-to-many. One-to-one would need each tutor to have exactly one student. Many-to-many would need each student to have several tutors. Different entities are exactly what relationships connect.
5

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

OrderLine(OrderID, ProductID, ProductName, Quantity) has the composite key OrderID with ProductID. Convert it to 2NF.
  1. 1
    Ask which fields depend on only part of the key.
  2. 2
    ProductName depends only on ProductID, so it is a partial dependency.
  3. 3
    Move it into a new table, Product(ProductID, ProductName), with the key ProductID.
  4. 4
    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).

Exam tip

A 1NF table whose primary key is a single field is already in 2NF. A partial dependency needs a composite key.

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 5 done

6

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.

Common mistake

Do not place the foreign key in the table on the ‘one’ side. It belongs on the ‘many’ side.

Note read

Check your recall[14 marks]

6 written questions. Answer each one in your head or on paper, then open the mark scheme.

  1. 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. 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. 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. 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. 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. 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.

3.

Easy·State[1 mark]

State what a foreign key is.
Show answer and explanation

Answer: D · Worked explanation

A foreign key points to the primary key of another table and creates the link between them. A field that identifies records in its own table is a primary key. A key made from two or more fields is a composite key. A table linking two others is a linking table.
4.

Medium·Identify[1 mark]

A Customer table and an Order table have a one-to-many relationship, because each customer places many orders. Identify where the foreign key is placed.
Show answer and explanation

Answer: B · Worked explanation

The foreign key goes on the many side, so each order record holds a CustomerID that points to one customer. A single customer record cannot hold one OrderID for many orders without repeating itself. Using keys in both tables is unnecessary, and a relationship still needs a key to link the records.
5.

Medium·Identify[1 mark]

A field named Phones holds values such as ‘0711, 0722’. Identify the normal form that this table breaks, and the reason.
Show answer and explanation

Answer: C · Worked explanation

First normal form requires every field to hold one value, and Phones holds two. Second normal form concerns partial dependencies on a composite key, which this problem is not. A missing foreign key does not break first normal form. A text field is allowed to hold only one value in each record.
6.

Medium·Deduce[1 mark]

Enrolment(StudentID, CourseID, StudentName, Grade) has the composite key StudentID with CourseID. Deduce which field causes a partial dependency.
Show answer and explanation

Answer: D · Worked explanation

StudentName is fixed by StudentID alone, so it depends on only part of the key. Grade depends on both the student and the course, so it needs the whole key. CourseID and StudentID are the key fields themselves, and key fields are not described as partial dependencies.
7.

Medium·Identify[1 mark]

Students can join many clubs, and each club has many students. Identify how this many-to-many relationship is stored.
Show answer and explanation

Answer: A · Worked explanation

A linking table with two foreign keys turns the many-to-many relationship into two one-to-many relationships. A single ClubID in Student allows only one club per student. Storing several club names in one field breaks first normal form. Relational databases handle many-to-many relationships routinely in this way.
8.

Hard·Determine[2 marks]

OrderLine(OrderID, ProductID, ProductName, Quantity) has the composite key OrderID with ProductID. Determine the tables that remove the partial dependency.
Show answer and explanation

Answer: C · Worked explanation

ProductName moves to Product, keyed by ProductID, and OrderLine keeps both key fields plus Quantity. Dropping ProductID from OrderLine loses the link between orders and products. Leaving ProductName in OrderLine keeps the partial dependency. Removing ProductID but keeping ProductName also leaves no key to join the tables.
9.

Hard·Explain[2 marks]

A table is in 1NF and its primary key is the single field StudentID. Which statement is correct?
Show answer and explanation

Answer: B · Worked explanation

A partial dependency means depending on part of a composite key, and a single-field key has no parts. So a 1NF table with such a key is already in 2NF. Claiming every 1NF table breaks 2NF is false. Having a composite key is a source of the problem, not a requirement, and foreign keys play no part in the test.
10.

Hard·Determine[2 marks]

A school database has the entities Student, Teacher and Subject. Each student takes many subjects, and each subject has many students. Each subject has one teacher, who teaches many subjects. Determine the minimum number of tables needed.
Show answer and explanation

Answer: A · Worked explanation

There are 3 entity tables. Student to Subject is many-to-many, so it needs a linking table, making 4. Teacher to Subject is one-to-many, so a TeacherID foreign key in Subject is enough and needs no extra table. The value 3 forgets the linking table, and 5 or more adds tables that are not needed.

Keep going

Next topicA3.3 Database programmingPrevious topicA3.1 Database fundamentals

More in A3 · Databases

  1. A3.1Database fundamentalsNotes · 10 questions
  2. A3.2Database designNotes · 10 questions
  3. A3.3Database programmingNotes · 10 questions
  4. A3.4Alternative databases and warehousesNotes · 10 questions· HL only

All IB Computer Science topics →

Draft: written by Scribe and checked automatically, not yet by a subject reviewer.

Practice questions are original, written by Scribe and checked automatically for correctness.

Topic codes follow the official numbering. Not endorsed by IB Diploma Programme or any exam board.

On this page

0/6 read

  1. 1An ER diagram plans the data before tables exist
  2. 2Relationships have a type and are stored using keys
  3. 3Normalisation reduces repeated data in a database
  4. 4First normal form needs single values and unique records
  5. 5Second normal form removes partial dependencies
  6. 6A design moves from requirements to linked tables
  7. Check your recall
  8. Practice: 10 questions
All coursesScribeHow it worksPricingPrivacyTerms

Notes and questions are written by Scribe in our own words. Topic codes follow the official course numbering so you can find your place. Not produced or endorsed by the IB, College Board, AQA, OCR, Pearson Edexcel or any exam board.

© 2026 Scribe