A3.2 Database design: notes and practice

What you need to know

Our summary of the topic in our own words, not the IB guide.

• Explain what a database schema is and why structure is described separately from data
• Distinguish the conceptual, logical and physical levels of a schema
• Read an entity relationship diagram and state the entities, attributes and relationships it describes
• Draw an entity relationship diagram for a described scenario using crow's foot notation
• Show cardinality and optionality correctly on an entity relationship diagram
• Choose appropriate SQL data types such as INTEGER, REAL, BOOLEAN, CHAR, VARCHAR and DATE for given attributes
• Explain the difference between fixed-length and variable-length character types and when each is better
• Turn an entity relationship diagram into a set of table definitions with keys
• Resolve a many-to-many relationship into two one-to-many relationships using a link table
• State the conditions for first, second and third normal form
• Identify partial and transitive dependencies in a table
• Normalise a given set of data to third normal form, showing the intermediate steps
• Explain what denormalisation is and why a designer might deliberately reintroduce redundancy
• Evaluate a trade-off between normalisation and query performance for a scenario

Practice questions

Original questions written for the 2027 course, marked as you go. Easiest first.

• Choosing data types for weather readingsWritten · Core
• Many-to-many tables for a music schoolWritten · Stretch
• Normalising a cafe order table to 3NFWritten · Stretch

Exam tips for A3.2

• Normalise one step at a time and show each form. Showing each step makes your working easy to follow.
• A many-to-many relationship always becomes a link table with two one-to-many relationships.
• Know when denormalising makes sense: faster reads at the cost of repeated data.