From Messy Tables to 1NF

From Messy Tables to 1NF

1 / 35
volgende
Slide 1: Slide
newEditorDatabase normalisationSecundair onderwijsAlgemeen secundair onderwijsDomeinoverschrijdendLeerjaar 5

In deze les zitten 35 slides, met interactieve quizzen en tekstslides.

Dit is niet oké

Onderdelen in deze les

From Messy Tables to 1NF

Open with the idea that a database should store data so it stays accurate when values are added, changed or deleted. This sets up normalisation as a practical design tool rather than a rule to memorise.

Learning goal

I can explain why database normalisation matters and apply first normal form (1NF) to simple tables.

Deze slide heeft geen instructies

What words do you associate with a well-structured database table?

Use this to surface prior ideas such as order, accuracy, rows, columns, keys, duplication or errors. Briefly connect student responses to the lesson focus on normalisation and 1NF.

Why normalisation exists

  • In a relational database, table design affects data quality.

  • Poorly structured tables often repeat the same facts many times.

  • Repeated facts increase the risk of inconsistency.

  • Normalisation is a method for organising tables more cleanly.

  • Its aim is to reduce redundancy and avoid update problems.

Link this to everyday intuition: if the same fact appears in several places, changing it everywhere becomes error-prone.

Redundancy in a table

  • Redundancy means storing the same information more than once.

  • Example: a customer's name appears in many order rows.

  • Repetition is not always wrong, but unnecessary repetition is risky.

  • The more copies a fact has, the easier it is for them to disagree.

You can briefly contrast 'same value appears naturally in multiple records' with 'same fact copied because the table is badly structured'.

Update anomalies

Poor table structure can create update anomalies:

  • Update anomaly: one fact must be changed in several rows

  • Insert anomaly: a fact cannot be added without unrelated data

  • Delete anomaly: deleting one row accidentally removes another fact


Normalisation helps prevent these problems.

Keep the examples simple and relational: changing one supplier address in many rows, being unable to add a new course before a student enrols, or deleting the last order and losing customer details.

Normalisation as a design principle

A normalised design aims to:

  • store each fact in an appropriate place

  • make each row represent one record

  • make updates predictable and consistent

  • support reliable searching, sorting and joining


First normal form is the starting point of this process.

Deze slide heeft geen instructies

What makes a table well structured?

  • A table should model one clear type of record.

  • Each column should hold one kind of value.

  • Each row should describe one instance of that record.

  • Rows should be identifiable, usually by a primary key.

  • The structure should make errors less likely, not easier.

Emphasise that 'well structured' is about how data is organised, not about how neat it looks on screen.

What is the main purpose of normalisation?

A

Reducing redundancy

B

Adding more columns

C

Hiding duplicate rows

D

Speeding up typing

The nearby slides stress reducing redundancy and avoiding update, insert and delete anomalies. Distractors reflect superficial but incorrect ideas about table design.

Complete the summary

In a relational database, poor table design can create redundancy and update problems. Normalisation aims to organise tables more cleanly so that each row represents one record and facts are stored in an appropriate place.

redundancy
cleanly
record
uniquely
queries
column
loosely

This echoes slides 2 to 6: redundancy, cleaner organisation and one row per record are the load-bearing ideas.

Why is 'one row, one record' important?

Expected elements: a row should represent one record only; mixing several items in one row makes meaning unclear; it complicates querying and updating; a stable identity for each row supports reliable database operations.

One row, one record

  • A row should represent one record only.

  • If one row mixes several items, the meaning becomes unclear.

  • Queries and updates work best when each row has a single, stable identity.

Useful contrast: one student per row is clear; one row containing several students or several courses is not.

Columns should hold one value type

  • Each field should have a clear purpose.

  • A column such as `PhoneNumbers` or `Modules` often hides several values together.

  • A well-structured table makes each value easy to access directly.

  • This prepares the table for first normal form.

Deze slide heeft geen instructies

Primary keys matter

  • A primary key identifies each row uniquely.

  • It helps distinguish one record from another.

  • Without a unique identifier, updates may affect the wrong row.

  • A primary key is one requirement of a table in 1NF.

You do not need to go deep into candidate keys here; the lesson only needs the idea of a dependable unique identifier.

Defining first normal form

A table is in first normal form (1NF) when:

  • each field contains a single, atomic value

  • there are no repeating groups

  • there are no multi-valued attributes

  • each row represents one record

  • each row can be identified by a primary key

Present 1NF as the first checkpoint for table quality: simple values, clear rows, clear identity.

Atomic values

An atomic value is a single value in one field.


In 1NF, a field should not contain a list such as `red, blue, green` or `0123 / 0456`. If a field stores several separate values together, the table is not in 1NF.

Students may ask whether a full name is atomic. Keep it practical: atomic means 'treated as one value for this design'. The lesson focus is on obvious lists and grouped values.

Repeating groups

  • A repeating group appears when the same kind of data repeats within one row.

  • Examples: `Module1`, `Module2`, `Module3` or several phone number columns.

  • This breaks the idea that one column should hold one consistent attribute.

Explain that repeating groups can appear either as multiple similar columns or as lists inside one field; both signal weak structure.

Multi-valued attributes

  • A multi-valued attribute stores more than one value for the same attribute in one row.

  • Example: one student record with several email addresses in the same field.

  • In 1NF, those values must not be bundled together inside a single field.

This is close to atomicity, but keep the distinction: the problem is not just formatting, but the fact that one attribute holds several values for one record.

What 1NF improves

1NF makes data easier to:

  • search with precise conditions

  • sort and filter consistently

  • update without hidden duplicates in one field

  • process with relational operations


It does not solve every design issue, but it removes common structural problems.

Good moment to note that 1NF is a foundation, not the end of normalisation.

Which field breaks the idea of an atomic value?

A

StudentID

B

OrderDate

C

PhoneNumbers

D

Surname

PhoneNumbers suggests several values bundled in one field, matching the warning on slide 8 and the 1NF definition on slides 10 to 13.

Complete the 1NF definition

A table is in 1NF when each field contains a single, atomic value, there are no repeating groups and each row can be identified by a primary key.

1NF
atomic
repeating groups
primary key
redundancy
record
multi-valued
foreign key

All four blanks come directly from the defining criteria on slides 10 to 12 and 9.

Which 1NF issue do students usually miss first?

1

Lists in one field

2

Repeating columns

3

Missing primary key

4

Several items per row

Deze slide heeft geen instructies

Spotting a 1NF violation

Typical signs that a table is not in 1NF:

  • a field contains a list of values

  • columns repeat the same attribute pattern

  • one row describes several items at once

  • rows cannot be uniquely identified

  • values are grouped in one field for convenience

Frame this as a checklist students can apply quickly before attempting a conversion.

Example: list in one field

`StudentID | Name | Modules`


`102 | Amina | SQL, Python, Networks`


The `Modules` field contains several values, so the table is not in 1NF.

Read the row aloud and ask students to notice that one student record contains multiple module values in one field.

Converting the example to 1NF

To convert the table:

  • keep one module value per row

  • repeat the student identifier where needed

  • let each row represent one student–module record


`102 | Amina | SQL`

`102 | Amina | Python`

`102 | Amina | Networks`

Point out that repeating the student data across rows is acceptable here because the immediate goal is 1NF. Later normal forms deal with further refinement.

Example: repeating columns

`OrderID | Customer | Item1 | Item2 | Item3`


This structure is not in 1NF because:

  • the same attribute repeats across columns

  • some rows may leave item columns empty

  • the number of items is limited by the design

This is a strong example of why repeating groups are both inflexible and hard to query cleanly.

Converting repeating columns

A 1NF version stores one item per row:


`OrderID | Customer | Item`

`501 | Noor | Keyboard`

`501 | Noor | Mouse`

`501 | Noor | Headset`


Now each field is atomic and the repeating group is removed.

If useful, mention that the row identity may then depend on more than one column, but keep the focus on 1NF compliance rather than key design theory.

A simple method for applying 1NF

When checking a table for 1NF:

  1. identify what one row is meant to represent

  2. check whether each field holds one atomic value

  3. look for lists or repeating columns

  4. ensure rows can be identified by a primary key

  5. split repeated values into separate rows or tables where needed

This slide consolidates the lesson into a procedure students can use on straightforward exam-style examples.

How should `SQL, Python, Networks` be converted for 1NF?

A

Keep one row

B

Add more module columns

C

Split into rows

D

Merge all fields

The correct move is to store one module per row, as shown on slides 16 and 17. A common misconception is to add Module1, Module2, Module3 columns, which still violates 1NF.

Complete the method

To apply 1NF, first identify what one row is meant to represent. Then check whether each field holds one atomic value, look for lists or repeating columns, and ensure rows can be identified by a primary key.

represent
atomic value
primary key
calculate
duplicate
foreign key
many values

This is a direct application of the step-by-step method on slide 20, using the exact procedural language students need.

Why is `Item1 | Item2 | Item3` not in 1NF?

Expected elements: Item1, Item2 and Item3 repeat the same attribute across columns; the design limits the number of items; empty cells may appear; one item per row removes the repeating group and restores atomic values.

What idea about 1NF became clearest today?

Invite short reflective responses about understanding, examples, or misconceptions corrected during the lesson.

What part of applying 1NF still feels hardest?

Use answers to identify where students still need support: atomic values, repeating groups, row meaning, or primary keys.

Glossary

database normalisation - organising tables to reduce redundancy and prevent structural problems

redundancy - unnecessary repetition of the same fact in a database

update anomaly - a problem caused when a fact must be changed in multiple places

insert anomaly - a problem where adding one fact requires unrelated data

delete anomaly - a problem where deleting a row removes useful information as well

well-structured table - a table whose rows, columns and identifiers are organised clearly

primary key - a field, or combination of fields, that uniquely identifies each row

Deze slide heeft geen instructies

Glossary

first normal form (1NF) - a table structure in which each field is atomic, repeating groups are removed and each row represents one record

atomic value - a single value stored in one field

repeating group - repeated columns or grouped values representing the same attribute

multi-valued attribute - an attribute that stores several values for one record

record - one complete row of data about a single item in the table

field - a column that stores one type of value

Deze slide heeft geen instructies